Less_14(2) PLSQL实例.doc

PL/SQL实例1./*用PL/SQL将EMP表中职员为SMITH的工资增加100元，并提交给数据库*/declare v_ename varchar2(10):=SMITH; - char(10) is not right.- v_ename char(10):=SMITH; begin update emp set sal=sal+100 where ename=v_ename; commit;end;/- select sal from emp where ename=SMITH;2./*若MARTIN的佣金不足1500元，则将其工资提升为1500元*/declare v_ename varchar2(10):=MARTIN;v_comm m%type;begin create table name1 ( ename varchar2(10), mgrname varchar2(10) ); select comm into v_comm from emp where ename=v_ename; if v_comm1500 then update emp set sal=1500 where ename=v_ename; end if ; -; can not be ignored!-commit;end;/3.查询职业为CLERK,且比他的上司受雇时间早的职工的名字及其上司的名字，并将结果存入新建的表name 中，用两种方法来解答：(1)不用光标FOR循环； (2)用光标FOR循环create table name ( ename varchar2(10), mgrname varchar2(10) );1)/*declare-I binary_integer:=0;v1_ename emp.ename%type;v2_ename emp.ename%type;begin for rec in ( select e1.ename a,e2.ename b from emp e1, emp e2 where (e1.job=CLERK) and (e1.mgr=e2.empno) and ( e1.hiredatee2.hiredate) loop - I=I+1; insert into name values(a,b ); end loop;end;*/*declare-I binary_integer:=0;v_name name%rowtypev1_ename emp.ename%type;v2_ename emp.ename%type;begin for rec in ( select e1.ename,e2.ename into v_name from emp e1, emp e2 where (e1.job=CLERK) and (e1.mgr=e2.empno) and ( e1.hiredateemp_rec.hiredate then INSERT INTO name VALUES(emp_rec.ename,mgrname);END IF;FETCH C1 INTO emp_rec;END LOOP;CLOSE c1;END;2)declare cursor c1 is select ename,mgr,hiredate from emp where JOB=CLERK order by hiredate; cursor c2 is select ename,empno,hiredate from emp;begin for r1 in c1 loop for r2 in c2 loop if (r1.mgr=r2.empno) and (r1.hiredater2.hiredate) then insert into name values(r1.ename, r2.ename); end if; end loop; end loop;end;/ 4./*上题中，设职为CLERK的职员不能是公司中最先受聘的员工，若发生这种情况，将该职员提升为经理(MANAGER)*/declare cursor c1 is select ename,mgr,hiredate from emp where JOB=CLERK order by hiredate; cursor c2 is select ename,empno,hiredate from emp; v_pridate emp.hiredate%type;begin select min(hiredate) into v_pridate from emp; for r1 in c1 loop for r2 in c2 loop if (r1.mgr=r2.empno) and (r1.hiredater2.hiredate) and (r1.hiredate=v_pridate) then update emp set job=manager where (r1.mgr=r2.empno) and (r1.hiredater2.hiredate) and (r1.hiredate=v_pridate) ; end if; end loop; end loop;end;/ 5/*在EMP表上编写一个触发器，审计任何对sal和comm列的修改（将每次修改此两列的数据库用户，修改时间、修改的用户和改前与该后此两列的值存入新建的一个审计表中），然后测试其正常。create table audit2 ( time DATE, username varchar2(10), befor_sal number(7,2), befor_comm number(7,2), after_sal number(7,2), after_comm number(7,2); create or replace trigger emp_checkbefore update of sal,common emp for each rowbegin insert into audit2 values(sysdate,user,:old.sal,:m,:new.sal,:m);end; - update emp set sal=2000 where empno=7654;6./*编写一个存储过程，给职工加工资，过程有两个参数，参数1为要加薪的部门号，参数2为要加薪幅度，部门号为0表示要给所有部门的职工都加。*/create or replace procedure raise_salary (deptno_id emp.deptno%type, increase real)is begin if(deptno_id=0) then update emp set sal=sal+increase; else update emp set sal=sal+increase where deptno=deptno_id; end if;end;/execute raise_salary(20,100) execute raise_salary(0,100)7./*将上题中存储过程放到一个包里去*/-*package specification*create or replace package T_package as procedure raise_salary(deptno_id emp.deptno%type, increase real);end T_package;-*package body*create or replace package body T_packageas procedure raise_salary (deptno_id emp.deptno%type, increase real)is begin if(deptno_id=0) then update emp set sal=sal+increase; else update emp set sal=sal+