pl sql练习（3）

　　1.s树形结构查询表中的数据：比如emp表中每个员工都有自己的头，即公司中的职位是按层次划分的，类似一个树，因此有时需要按层次显示查询的结果。

　　

 1 select empno,mgr,ename,job from emp
 2           start with empno = 7839
 3           connect by prior empno = mgr;
 4 
 5 
 6         EMPNO        MGR ENAME      JOB
 7     ---------- ---------- ---------- ---------
 8          7839            KING       PRESIDENT
 9          7566       7839 JONES      MANAGER
10          7788       7566 SCOTT      ANALYST
11          7876       7788 ADAMS      CLERK
12          7902       7566 FORD       ANALYST
13          7369       7902 SMITH      CLERK
14          7698       7839 BLAKE      MANAGER
15          7499       7698 ALLEN      SALESMAN
16          7521       7698 WARD       SALESMAN
17          7654       7698 MARTIN     SALESMAN
18          7844       7698 TURNER     SALESMAN
19  
20         EMPNO        MGR ENAME      JOB
21     ---------- ---------- ---------- ---------
22          7900       7698 JAMES      CLERK
23          7782       7839 CLARK      MANAGER
24          7934       7782 MILLER     CLERK
25  
26     14 rows selected.
View Code

 　　2.使用level显示数据所在的层并格式化数据。

 1 select level,
 2           lpad(' ',2 * level - 1) || ename as "Ename",
 3           job
 4           from emp
 5           start with ename = 'KING'
 6           connect by prior empno = mgr;
 7 
 8         LEVEL Ename                          JOB
 9     ---------- ------------------------------ ---------
10             1  KING                          PRESIDENT
11             2    JONES                       MANAGER
12             3      SCOTT                     ANALYST
13             4        ADAMS                   CLERK
14             3      FORD                      ANALYST
15             4        SMITH                   CLERK
16             2    BLAKE                       MANAGER
17             3      ALLEN                     SALESMAN
18             3      WARD                      SALESMAN
19             3      MARTIN                    SALESMAN
20             3      TURNER                    SALESMAN
21  
22         LEVEL Ename                          JOB
23     ---------- ------------------------------ ---------
24             3      JAMES                     CLERK
25             2    CLARK                       MANAGER
26             3      MILLER                    CLERK
27  
28     14 rows selected.
View Code

 

　　3.写一个存储过程，向表中随机插入1000条记录
SerialNo：使用序列方式，自增长。
Filepath：使用随机插入6个字母
Partid： 使用随机4位数字
StaffNo：从YTCZ060001……. YTCZ060020 中随机抽取
RecordTime：从2011年8月1日之前的6个月中随机抽取。

 1 create or replace procedure p_random_corder
 2 is
 3   MAXCORDERS constant int :=1000;
 4   i int :=2;
 5   day varchar2(100);
 6   random int;
 7 begin
 8 
 9   for i in 2..MAXCORDERS loop
10     random :=trunc(dbms_random.value(2,8));
11     day :='2011'||
12       when  random=7 then '07'||lpad(floor(abs(trunc(dbms_random.value(1,31)))),2,'0')
13      end);
14     insert into recordfile values('2011'||seq_class.nextval,
15     upper(chr(trunc(dbms_random.value(97,122))))||
16     upper(chr(trunc(dbms_random.value(97,122))))||
17     upper(chr(trunc(dbms_random.value(97,122))))||
18     upper(chr(trunc(dbms_random.value(97,122))))||
19     upper(chr(trunc(dbms_random.value(97,122))))||
20     upper(chr(trunc(dbms_random.value(97,122)))),
21     trunc(dbms_random.value(1000,9999)),
22     'YTCZ0'||trunc(dbms_random.value(60001,60020)),
23     to_date(day,'yyyymmdd'));
24   end loop;
25   dbms_output.put_line('sucess');
26   commit;
27 end;
View Code

 　　4.写一个存储过程，删除3个月前的数据

 1 create or replace procedure p_delete_threedata
 2  is
 3  begin
 4      delete from recordfile where  RecordTime = trunc(add_months(sysdate,-3));
 5      commit;
 6      
 7  exception 
 8      when others then
 9      rollback;    
10  end ;
11 /
View Code

　　5.写一个job，每隔30天凌晨2点整，系统执行“删除3个月前数据”的存储过程。

 

 1 DECLARE 
 2         JobNum NUMBER(5) := 0;
 3         JobID  NUMBER(5);
 4 BEGIN
 5   SELECT COUNT(*) INTO JobNum FROM DBA_JOBS WHERE UPPER(SUBSTRB(WHAT,1,30)) = UPPER('p_delete_threedata');
 6   IF JobNum > 0 THEN
 7     SELECT NVL(JOB,0) INTO JobID FROM DBA_JOBS WHERE UPPER(SUBSTRB(WHAT,1,30)) = UPPER('p_delete_threedata');
 8     IF JobID > 0 THEN
 9       DBMS_JOB.REMOVE(JobID);
10     END IF;
11   END IF;
12   DBMS_JOB.SUBMIT(JobID, 'p_delete_threedata;', trunc(sysdate,'DD')+1+2/24, 'sysdate+30');
13   COMMIT;
14 END;
15 /
View Code

 　　6.创建一个触发器tri_emp_sal，要求当向员工表emp中插入记录时，同时更新部门工资统计表（dept_sal）中相应字段的值：如果插入的工资低于1500，则低收入人数(lownum)加1；如果插入的工资高于3000，则高收入人数(highnum)加1。

 1 先执行下列语句创建dept_sal表并向表中插入实验数据：
 2 CREATE TABLE dept_sal
 3 (deptno number(2),
 4 lownum  number(4),
 5 highnum number(4));
 6 insert into dept_sal
 7 values(10,1,1);
 8 insert into dept_sal
 9 values(20,1,3);
10 insert into dept_sal
11 values(30,1,1);
12 
13 
14 CREATE OR REPLACE TRIGGER tri_emp_sal
15  AFTER  INSERT  ON EMP
16  FOR  EACH  ROW
17  BEGIN
18  IF :NEW.SAL<1500
19  THEN
20  UPDATE DEPT_SAL
21  SET LOWNUM=LOWNUM+1
22  WHERE deptno=:NEW.deptno;
23  ELSIF :NEW.SAL>3000
24  THEN
25  UPDATE DEPT_SAL
26  SET HIGHNUM=HIGHNUM+1
27  WHERE deptno=:NEW.deptno;
28  END IF;
29  END;
30 /
31 
32 
33 
34  
35 测试代码:
36 INSERT INTO EMP VALUES(7841,'JOHN','CLERK',7698,'12-dec-87',1000,0,20);
37 INSERT INTO EMP VALUES(7912,'LILEI','ANALYST',7839,'05-apr-82',3500,0,10);
View Code

 　　7.变量作用域

 1 declare
 2 v_num number(5,2):=1.23;
 3     begin
 4       declare v_num char(10);
 5       begin
 6          v_num:=12345;
 7          dbms_output.put_line(v_num);
 8       end;
 9     dbms_output.put_line(v_num);
10    end;
11    /
12 
13 首先会输出12345
14 然后输出1.23
View Code

 

posted on 2013-08-25 15:58  a_badegg  阅读(380)  评论(0)    收藏  举报

导航