able 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;
2.纯粹用sql：
表dept, emp
要得到如下结果
deptno, dname, employees
---------------------------------
10, accounting, clark;king;miller
20, research, smith;adams;ford;scott;jones
30, sales, allen;blake;martin;james;turners
每个dept的employee串起来作为一条记录返回
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
16．在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;
17．查看对象的依赖关系(比如视图与表的引用)
查看视图：dba_dependencies 记录了相关的依赖关系
查东西不知道要查看哪个视图时，可以在DBA_Objects里看，
select object_name from dba_objects where object_name like ''''%ROLE%''''(假如查看ROLE相关)
然后DESC一下就大体上知道了。
18．要找到某月中所有周五的具体日期
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
如果把where to_char(t.d, ''''MM'''') = to_char(sysdate, ''''MM'''')改成sysdate-90，即为查找当前月份的前三个月中的每周五的日期。
上一页 [1] [2] |
