安徽理工大学oracle编程.docxVIP

使用if语句与case语句举例。 set serveroutput on declare grade char:=b; begin --使用if实现，代码形式上较长 if grade = a then dbms_output.put_line(excellent); elseif grade = b then dbms_output.put_line(very good); elseif grade = c then dbms_output.put_line(good); elseif grade = d then dbms_output.put_line(fair); elseif grade = e then dbms_output.put_line(poor); else dbms_output.put_line(no such grade); end if; --将case的实现形式与if的实现形式进行比较 case语句更加简洁。 case grade when a then dbms_output.put_line(excellent); when b then dbms_output.put_line(very good); when c then dbms_output.put_line(good); when d then dbms_output.put_line(fair); when e then dbms_output.put_line(poor); else dbms_output.put_line(no such grade); end case; end; /  游标loop循环 把hr用户中employees表中工资超过10000的记录插入到sal_history表中，将经理编号超过200的记录插入到mgr_history表中。 sql conn hr/hr@orcl; --连接到hr用户中，口令：hr 已连接。 declare cursor cur_employees(p_dept number) is --定义游标 select employee_id empid, hire_date hiredate, salary sal, manager_id mgr from employees --数据来自employees表 where employee_id p_dept; emp_rec cur_employees%rowtype; --用于接收游标返回数据的记录型变量 begin delete from sal_history; delete from mgr_history; commit; open cur_employees(100); --仅处理员工编号超过100的员工数据 loop fetch cur_employees into emp_rec; --取出游标指向的记录数据 exit when cur_employees%notfound; if emp_rec.sal 10000 then insert into sal_history values(emp_rec.empid,emp_rec.hiredate,emp_rec.sal) ; end if; if emp_rec.mgr 200 then insert into mgr_history values(emp_rec.empid,emp_rec.mgr,emp_rec.sal) ; end if; end loop; close cur_employees; end; / --提交 --游标for循环 按emp表中工资和奖金的比例，建立bonus表。 sql conn scott/tiger@orcl; declare v_bonus number; cursor c1 is select ename,sal,comm from emp; begin delete from bonus; for c1_rec in c1 loop v_bonus:= (c1_rec.sal*0.05)+(c1_m*0.25); insert into bonus(ename,sal,comm) values(c1_rec.ename,c1_rec.sal,v_bonus