13.连接查询结果： 表a 列 a1 a2 记录 1 a 1 b 2 x 2 y 2 z 用select能选成以下结果: 1 ab 2 xyz
下面有两个例子： 1.使用pl/sql代码实现，但要求你组合后的长度不能超出oracle varchar2长度的限制 create or replace type strings_table is table of varchar2(20); / create or replace function merge (pv in strings_table) return varchar2 is ls varchar2(4000); begin for i in 1..pv.count loop ls := ls || pv(i); end loop; return ls; end; / create table t (id number,name varchar2(10)); insert into t values(1,''''Joan''''); insert into t values(1,''''Jack''''); insert into t values(1,''''Tom''''); insert into t values(2,''''Rose''''); insert into t values(2,''''Jenny'''');
column names format a80; select t0.id,merge(cast(multiset(select name from t where t.id = t0.id) as strings_table)) names from (select distinct id from t) t0;
drop type strings_table; drop function merge; drop table t;
This example uses a max of 6, and would need more cut n pasting to do more than that：
SQL>; select deptno, dname, emps 2 from ( 3 select d.deptno, d.dname, rtrim(e.ename ||'''', ''''|| 4 lead(e.ename,1) over (partition by d.deptno 5 order by e.ename) ||'''', ''''|| 6 lead(e.ename,2) over (partition by d.deptno 7 order by e.ename) ||'''', ''''|| 8 lead(e.ename,3) over (partition by d.deptno 9 order by e.ename) ||'''', ''''|| 10 lead(e.ename,4) over (partition by d.deptno 11 order by e.ename) ||'''', ''''|| 12 lead(e.ename,5) over (partition by d.deptno 13 order by e.ename),'''', '''') emps, 14 row_number () over (partition by d.deptno 15 order by e.ename) x 16 from emp e, dept d 17 where d.deptno = e.deptno 18 ) 19 where x = 1 20 /
DEPTNO DNAME EMPS ------- ----------- ------------------------------------------ 10 ACCOUNTING CLARK, KING, MILLER 20 RESEARCH ADAMS, FORD, JONES, ROONEY, SCOTT, SMITH 30 SALES ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD
14.在Oracle中建一个编号会自动增加的字段,以利于查询
1、建立序列： CREATE SEQUENCE checkup_no_seq NOCYCLE MAXVALUE 9999999999 START WITH 2;
2、建立触发器： CREATE OR REPLACE TRIGGER set_checkup_no BEFORE INSERT ON checkup_history FOR EACH ROW DECLARE next_checkup_no NUMBER; BEGIN --Get the next checkup number from the sequence SELECT checkup_no_seq.NEXTVAL INTO next_checkup_no FROM dual;
--use the sequence number as the primary key --for the record being inserted :new.checkup_no := next_checkup_no; END;
15.查看对象的依赖关系(比如视图与表的引用)
查看视图：dba_dependencies 记录了相关的依赖关系 查东西不知道要查看哪个视图时，可以在DBA_Objects里看， select object_name from dba_objects where object_name like ''''%ROLE%''''(假如查看ROLE相关) 然后DESC一下就大体上知道了。
16.要找到某月中所有周五的具体日期 select to_char(t.d,''''YY-MM-DD'''') from ( select trunc(sysdate, ''''MM'''')+rownum-1 as d from dba_objects where rownum < 32) t where to_char(t.d, ''''MM'''') = to_char(sysdate, ''''MM'''') --找出当前月份的周五的日期 and trim(to_char(t.d, ''''Day'''')) = ''''星期五'''' -------- 03-05-02 03-05-09 03-05-16 03-05-23 03-05-30
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 
