pl sql练习(3)
1.s树形结构查询表中的数据:比如emp表中每个员工都有自己的头,即公司中的职位是按层次划分的,类似一个树,因此有时需要按层次显示查询的结果。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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.
2.使用level显示数据所在的层并格式化数据。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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.
3.写一个存储过程,向表中随机插入1000条记录
SerialNo:使用序列方式,自增长。
Filepath:使用随机插入6个字母
Partid: 使用随机4位数字
StaffNo:从YTCZ060001……. YTCZ060020 中随机抽取
RecordTime:从2011年8月1日之前的6个月中随机抽取。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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;
4.写一个存储过程,删除3个月前的数据
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 /
5.写一个job,每隔30天凌晨2点整,系统执行“删除3个月前数据”的存储过程。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 /
6.创建一个触发器tri_emp_sal,要求当向员工表emp中插入记录时,同时更新部门工资统计表(dept_sal)中相应字段的值:如果插入的工资低于1500,则低收入人数(lownum)加1;如果插入的工资高于3000,则高收入人数(highnum)加1。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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);
7.变量作用域
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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