Less_14 2 PLSQL实例.docVIP

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%rowtype v1_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.hiredatee2.hiredate)) loop -- I=I+1; insert into name values(v_name); end loop; end;*/ 1) declare cursor c1 is select ename,mgr,hiredate from emp where JOB=CLERK; edate DATE; mgrname emp.ename%TYPE; emp_rec C1%ROWTYPE; begin OPEN c1; FETCH C1 INTO emp_rec; WHILE C1%FOUND LOOP SELECT ENAME,HIREDATE INTO mgrname,edate FROM emp WHERE empno=emp_rec.mgr; IF edateemp_rec.hiredate then INSERT INTO name VALUES(emp_rec.ename,mgrname); END IF; FETCH C1 INTO emp_rec; END LOOP; CLOSE c1; EN