经典SQL学习笔记 (八) - pl/sql编程二

pl/sql编程

例子:查询部门表的三种循环 --loop循环----------------------------------------------------------------------- declare cursor curl is select * from dept; lrow dept%rowtype; begin open curl; loop fetch curl into lrow; exit when curl%notfound; dbms_output.put_line(lrow.deptno||'--'||lrow.dname||'--'||lrow.loc); end loop; close curl; end; show error; --while循环---------------------------------------------------------------------- declare cursor curl is select * from dept; lrow dept%rowtype; begin open curl; fetch curl into lrow; while curl%found loop dbms_output.put_line(lrow.deptno||'--'||lrow.dname||'--'||lrow.loc); fetch curl into lrow; end loop; close curl; end; --for循环------------------------------------------------------------------------ for 变量名 in 游标 loop ..... end loop; 注意: 1、隐藏了打开游标，所以打开游标不需要写 2、隐藏了一个fetch； 3、隐藏了检测退出循环的属性 4、关闭 declare cursor curl is select * from dept; begin for lrow in curl loop dbms_output.put_line(lrow.deptno||'--'||lrow.dname||'--'||lrow.loc); end loop; end; -------------------------------------------------------------------------------- 隐式游标 对 insert update delete操作 sql--隐式游标的名字，Oracle 系统定义 sql%属性 begin insert into emp_wzl (empno,ename,job) values(9001,'kack','clerk'); dbms_output.put_line(sql%rowcount); delete from emp_wzl; dbms_output.put_line(sql%rowcount); end; insert into emp_wzl (empno,ename,job) values(9004,'kack','clerk'); select * from emp_wzl; -------------------------------------------------------------------------------- 例子：查询出emp表中的前10条记录，并输出（用 for ，while，loop 三种循环） declare cursor curl is select * from emp where rownum<=10; begin for lrow in curl loop dbms_output.put_line(lrow.empno||'---'||lrow.ename); end loop; end; -------------------------------------------------------------------------------- declare cursor curl is select * from emp; begin for lrow in curl loop dbms_output.put_line(lrow.empno||'---'||lrow.ename); if curl%rowcount=10 then exit; end if; end loop; end; 动态游标 -------------------------------------------------------------------------------- 包（package） 组成： 包说明、包体 包说明部分： create package 包名 is|as <公共变量> <存储过程，函数的声明> end; 包体部分： create package body 包名 is|as <私有变量> <函数体> <存储过程体> end； drop package 包名； --删除包 create package sal_wpac is increment emp.sal%type; reduce emp.sal%type; procedure update_sal(p_no number,p_sal number); procedure reduce_sal(p_no number,p_sal number); end; create package body sal_wpac is procedure update_sal(p_no number,p_sal number); is begin update emp_wzl set sal=sal+p_sal where empno=p_no; end; procedure reduce_sal(p_no number,p_sal number); is begin update emp_wzl set sal=sal+p_sal where empno=p_no; end; end; select * from emp_wzl; insert into emp_wzl (empno,sal) values(1001,2500); drop package sal_wpac; --查询相关信息，名字要大写 select * from user_procedures where object_name='SAL_WPAC' type='PACKAGE'; --查看过程函数过程的代码，名字要大写 select text from user_source where name='F_NAME'; select text from user_source where name='F_NAME' and type='PACKAGE'; --异常（exception） --1、预定义异常------------------------------------------------------------------- ora-xxxxx; exception when 异常名 then ...... when 异常名 then ...... when others then ...... end; 例子1：根据雇员姓名，求出雇员的工作和工资，并对异常进行处理 对no_data_found、too_many_rows捕捉异常 declare v_job emp.job%type; v_sal emp.sal%type; begin select job,sal into v_job, v_sal from emp where ename='&name'; exception when no_data_found then dbms_output.put_line('没有发现数据'); when too_many_rows then dbms_output.put_line('返回太多条数据'); end; 例子2:更新指定的员工工资，如果工资小于1500，则加100 对no_data_found、too_many_rows捕捉异常 select * from emp_wzl; insert into emp_wzl (empno,ename,sal) values(1002,'smith',600); declare v_sal emp.sal%type; begin select sal into v_sal from emp where ename='&name'; if v_sal<1500 then update emp_wzl set sal=sal+100 where sal<1500; else dbms_output.put_line('不需要更新'); end； end； --2、非预定义异常----------------------------------------------------------------- 1、再定义部分，定义异常 异常名 exception; 2、把定义好的异常和标准的oracle错误关联 pragma exception_init(异常名，错误代码); (要写在定义部分) 3、在exception中对异常处理 ora-02292 例子：删除指定部门的记录，以确保该部门没有员工 declare e_deptno exception; pragma exception_init(e_deptno,-02290); begin delete from dept where deptno=&no; exception when d_deptno then dbms_output.put_line('违反了外键约束，不能删除数据'); end; --3、自定义异常------------------------------------------------------------------- 1、定义异常 异常名 exception 2、抛出异常 raise 异常名 3、exception 中对抛出的异常进行处理 空的代码段： ora-20000到ora-20999； 例子：创建一个函数，查询出指定部门的工资总和，其中要捕捉部门为空或者部门为负数情况进行处理 create or replace function get_sal(spno number) return number is e_notnull exception; e_error exception; v_income emp.sal%type; begin if spno is null then raise e_null; elsif spno<0 then raise e_error; else select sum(sal) into v_income from emp where deptno=spno; end if; return v_income; exception when e_null then dbms_output.put_line('部门为空'); return 0; when e_error then dbms_output.put_line('部门编号为负数'); return -1; end; / declare v1 emp.sal%type; begin v1:=get_sal(-10); end; -------------------------------------------------------------------------------- raise_application_error(异常的代码，异常信息); create or replace function get_sal1(spno number) return number is v_income emp.sal%type; begin if spno is null then raise_application_error(-20000，'部门为空'); elsif spno<0 then raise_application_error(-20001，'部门号为负数'); else select sum(sal) into v_income from emp where deptno=spno; end if; return v_income; end; declare v1 emp.sal%type; begin v1:=get_sal1(null); end; --练习------------------------------------------------------------------------------ 1、编写一个存储过程，用于雇员姓名的验证，如果在emp表中存在，返回Y，否则返回N； create or replace procedure logincheck(spname varchar2,flag varchar2) is v_count number; begin select count(*) into v_count from emp where ename=spname; if v_count=0 then flag:='N'; else flag:='Y'; end if; end; declare v1 varchar2(2); begin logincheck('dd',v1); dbms_output.put_line(v1); end; 2、编写一个函数，输入雇员的编号，如果该雇员的工作是经理，而且工资大于5000，返回（工资上涨20%），否则返回工资上涨40% 并编写一个块，来遍历emp表 create or replace function addsal(spno number) return number is v_job emp.job%type; v_sal emp.sal%type; begin select job.sal into v_job,v_sal from emp where empno=span; if v_job='MANAGER' then if v_sal>5000 then v_sal:=v_sal*(1+0.2); else v_sal:=v_sal*(1+0.4); end if; end if; return v_sal; end; declare cursor curl is select * from emp; begin for lrow in curl loop dbms_output.put_line(addsal(lrow.empno)); end loop; end; --触发器（trigger）--------------------------------------------------------------- 语法： create trigger 触发器名字 {before|after} {insert|update|delete[of column[,[cloumn]]]} on 表名 [for each row] [when 条件] begin 触发体 end； 触发器的分类： --dml 触发器：触发表-------------------------------------------------------------- 条件谓词: inserting、updating、deleting 属性： : old 和: new ：old ：访问操作前的值 ：new insert update delete ：访问操作后的值 : old null 有 有 : new 有 有 null create trigger tr1 after delete on emp_cxp for each row begin dbms_output.put_line('delete emp_cxp'); end; ---------------------------------------- create trigger tr1 after insert or update or delete on emp_cxp for each row begin if inserting then dbms_output.put_line('insert'); elsif updating then dbms_output.put_line('update'); else dbms_output.put_line('delete emp_cxp'); end if; end; create trigger tr1 after insert or update or delete on emp_cxp for each row begin if inserting then dbms_output.put_line('insert'||'----'||:new.empno||'----'||:new.ename); elsif updating then dbms_output.put_line('update'||'----'||'修改前的值'||'----'||:old.ename); dbms_output.put_line('update'||'----'||'修改后的值'||'----'||:new.ename); else dbms_output.put_line('delete emp_cxp'||'----'||'修改前的值'||'----'||:old.ename); end if; end; 例子：编写一个触发器，修改emp_xxx表的雇员的薪水，显示修改前和修改后的值，确保修改后的工资不低于原工资 select * from emp_wzl; drop table emp_wzl; CREATE TABLE emp_wzl AS SELECT * FROM EMP; create trigger tr_wzl before update of sal on emp_wzl for each row begin dbms_output.put_line('修改前的值'||'----'||:old.sal); dbms_output.put_line('修改后的值'||'----'||:new.sal); if :old.new>:new.sal then raise_application_error(-20011,'只涨不降'); end if; end; select * from emp_wzl; insert into emp_wzl(empno,ename,sal)values(1001,'ss',1000); --替代触发器：触发视图------------------------------------------------------------- create trigger 触发器名字 instead of insert or update or delete on 视图 begin 触发体 end; --系统触发器：触发整个数据库-------------------------------------------------------- create trigger 触发器名字 {before|after} 系统事件 on database begin 触发体 end； logon logoff startup shutdown --嵌套------------------------------------------------------------------------------ declare begin ........ declare exception end; ..... exception end; --------------------------------------------------------------------------------