软件测试要会oracle吗,Oracle 新手注意事项

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：

1 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

如果把where to_char(t.d, 'MM') = to_char(sysdate, 'MM')改成sysdate-90，即为查找当前月份的前三个月中的每周五的日期。

44/4<1234