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] [系统软件]EXP-00008: ORACLE error 904 encountered的解决方… [常用软件]PB7 连接 Oracle 的配置方法 [Web开发]oracle Export and Import 简介 [Web开发]ADO访问Oracle结果集的心得 [JAVA开发]JDBC+Hibernate将Blob数据写入Oracle [JAVA开发]J2EE应用中与Oracle数据库的连接 [JAVA开发]Oracle Application Serve_ [其他]HP-UXrx2600B.11.22Uia64安装oracle9i9.2foria64手… [其他]在RedhatEnterpriseserver3上安装oracle9iR2的注意… [其他]PROC++批量导入导出ORACLE数据库表
|