Oracle数库实验-PLSQL游标、过程、函数、包的使用.docVIP

Oracle数据库基础 实验5 PL/SQL游标、过程、函数、包的使用 【实验学时】 2学时 【】 【】 1 declare 2 cursor c_dept is select * from dept order by deptno; 3 cursor c_emp(p_dept emp.deptno%type)is select ename,sal from emp where deptno=p_dept 4 order by ename; 5 r_dept dept%rowtype; 6 v_ename emp.ename%type; 7 v_salary emp.sal%type; 8 v_tot_salary emp.sal%type; 9 begin 10 open c_dept; 11 loop 12 fetch c_dept into r_dept; 13 exit when c_dept%notfound; 14 dbms_output.put_line(department:||r_dept.deptno||-||r_dept.dname); 15 v_tot_salary:=0; 16 open c_emp(r_dept.deptno); 17 loop 18 fetch c_emp into v_ename,v_salary; 19 exit when c_emp%notfound; 20 dbms_output.put_line(name:||v_ename||salary:||v_salary); 21 v_tot_salary:=v_tot_salary+v_salary; 22 end loop; 23 close c_emp; 24 dbms_output.put_line(total salary for dept:||v_tot_salary); 25 end loop; 26 close c_dept; 27* end; SQL / PL/SQL 过程已成功完成。 用cursor for编写上题的程序。 1 declare 2 cursor c_dept is select deptno,dname from dept order by deptno; 3 cursor c_emp(p_dept emp.deptno%type)is 4 select ename,sal 5 from emp 6 where deptno=p_dept 7 order by ename; 8 v_tot_salary emp.sal%type; 9 begin 10 for r_dept in c_dept loop 11 dbms_output.putline(department:||r_dept.deptno||-||r_dept.ename); 12 v_tot_salary:=0; 13 for r_emp in c_emp(r_dept.deptno)loop 14 dbms_output.put_line(name:||r_emp.ename||salary:||r_emp.sal); 15 v_tot_salary:=v_tot_salary+r_emp.sal; 16 end loop; 17 dbms_output.put_line(total salary for dept:||v_tot_salary); 18 end loop; 19* end; QL / dbms_output.putline(department:||r_dept.deptno||-||r_dept.ename); 创建两个表格，myemp和mybonus表，分别与员工emp表和津贴bonus表的结构及数据相同。要求用游标完成操作：取出myemp表中工资大于1000元的记录，将其工资的30%作为奖金插入到mybonus表中。 create or replace procedure insertjintie() isCURSOR emp IS SELECT 工号，姓名，工资 FROM 职工表 where 工资1000；beginfor v_emp in emp loop insert into 津贴表(工号，工资) values(v_emp.工号，v_emp.工资*0.3);end loop;