一、存储过程介绍和案例1
1 ---修改表字段 2 alter table emp modify deptno not null; 3 4 --注意：存储过程不会报出具体哪行报错，所以要仔细仔细再仔细 5 --1.创建存储过程，完成添加员工的信息。包括编号、名称、薪水、工种和部门编号。 6 --or replace表示如果有已经存在的存储过程则要覆盖 7 create or replace procedure add_emp( 8 v_eno number, --参数，雇员编号 9 v_ename varchar2, --参数，雇员姓名 10 v_job varchar2 default 'clerk', --参数，工作 11 v_mgr number, --参数，领导编号 12 v_hiredate date, --参数，雇佣日期 13 v_sal number, --参数，基本工资 14 v_comm number, --参数，奖金 15 v_dno number --参数，部门编号 16 ) 17 is 18 emp_null_error exception; --定义异常变量 19 20 --非预定义异常，前提是：deptno列非空，插入控制会报错 21 22 pragma exception_init(emp_null_error, -1400); --定义异常变量 23 24 --非预定义异常，前提是：deptno列非空，插入控制会报错 25 emp_no_deptno exception; 26 pragma exception_init(emp_no_deptno, -2291); 27 begin 28 insert into emp values(v_eno,v_ename,v_job,v_mgr,v_hiredate,v_sal,v_comm,v_dno); 29 exception 30 when dup_val_on_index then 31 raise_application_error(-20000,'该雇员已经存在'); 32 when emp_null_error then 33 raise_application_error(-20001,'部门编号不能为空'); 34 when emp_no_deptno then 35 raise_application_error(-20002,'不存在该部门编号'); 36 end; 37 / 38 39 --2.用命令调用存储过程 40 --(1)按照位置传递参数，参数位置固定 41 --正确调用 42 exec add_emp(1113,'zzy','mary',7799,to_date('2015-5-6','yyyy-mm-dd'),2300,300,10); 43 44 --①写不存在的部门编号 45 SQL> exec add_emp(1114,'zzy','mary',7799,to_date('2015-5-6','yyyy-mm-dd'),2300,300,33); 46 47 --输出内容 48 BEGIN add_emp(1114,'zzy','mary',7799,to_date('2015-5-6','yyyy-mm-dd'),2300,300,33); END; 49 50 * 51 第 1 行出现错误: 52 ORA-20002: 不存在该部门编号 53 ORA-06512: 在 "SCOTT.ADD_EMP", line 29 54 ORA-06512: 在 line 1 55 56 --②写一个已存在的员工 57 exec add_emp(1113,'zzy','mary',7799,to_date('2015-5-6','yyyy-mm-dd'),2300,300,10); 58 59 --输出内容 60 BEGIN add_emp(1113,'zzy','mary',7799,to_date('2015-5-6','yyyy-mm-dd'),2300,300,10); END; 61 62 * 63 第 1 行出现错误: 64 ORA-20000: 该雇员已经存在 65 ORA-06512: 在 "SCOTT.ADD_EMP", line 25 66 ORA-06512: 在 line 1 67 68 69 70 --(2)按照名称传递参数,参数位置不固定 71 exec add_emp(v_dno=>20,v_eno=>1113,v_ename=>'holly',v_job=>'mary',v_mgr=>7799,v_hiredate=>to_date('2015-5-6','yyyy-mm-dd'),v_sal=>2300, v_comm=>300 ); 72 73 --(3)混合方式传递参数，参数位置不固定 74 exec add_emp(1114,v_ename=>'holly',v_job=>'mary',v_mgr=>7799,v_hiredate=>to_date('2015-5-6','yyyy-mm-dd'),v_sal=>2300,v_comm=>300, v_dno=>20); 75 76 77 78 --（4）.在plsql块中调用 79 --（4）.1在pl/sql块中按位置调用存储雇员的存错过程 80 set serverout on 81 declare 82 83 emp_20001 exception; 84 pragma exception_init(emp_20001,-20001); 85 86 emp_20000 exception; 87 pragma exception_init(emp_20000,-20000); 88 89 emp_20002 exception; 90 pragma exception_init(emp_20002,-20002); 91 92 begin 93 --异常，部门不存在 94 add_emp(2111,'zzy','mary',7799,to_date('2015-5-6','yyyy-mm-dd'),2300,300,66); 95 96 --正确，按位置传递参数 97 add_emp(2111,'zzy','mary',7799,to_date('2015-5-6','yyyy-mm-dd'),2300,300,10); 98 99 --异常，雇员编号重复 100 add_emp(2111,'zzy','mary',7799,to_date('2015-5-6','yyyy-mm-dd'),2300,300,10); 101 102 --异常，部门为空 103 add_emp(2111,'zzy','mary',7799,to_date('2015-5-6','yyyy-mm-dd'),2300,300,null); 104 105 exception 106 107 when emp_20000 then 108 dbms_output.put_line('emp_20000的雇员编号不能重复'); 109 110 when emp_20001 then 111 dbms_output.put_line('emp_20001的部门编号不能空'); 112 113 when emp_20002 then 114 dbms_output.put_line('emp_20002不存在该部门编号'); 115 116 when others then 117 dbms_output.put_line('出现了其他异常错误'); 118 end; 119 / 120 raise emp_20001; 121 122 --（4）.2在pl/sql块中按名字调用存储雇员的存错过程 123 set serverout on 124 declare 125 emp_20000 exception; 126 pragma exception_init(emp_20000,-20000); 127 emp_20001 exception; 128 pragma exception_init(emp_20001,-20001); 129 emp_20002 exception; 130 pragma exception_init(emp_20002,-20002); 131 132 begin 133 --正确，按位置传递参数 134 add_emp(v_dno>=10,v_eno>=2111,v_ename>='zzy',v_job>='mary',v_mgr>=7799,v_hiredate>=to_date('2015-5-6','yyyy-mm-dd'),v_sal>=2300,v_comm>=300); 135 136 --异常，雇员编号重复 137 add_emp(v_dno>=10,v_eno>=2111,v_ename>='zzy',v_job>='mary',v_mgr>=7799,v_hiredate>=to_date('2015-5-6','yyyy-mm-dd'),v_sal>=2300,v_comm>=300); 138 139 --异常，部门为空 140 add_emp(v_dno>=null,v_eno>=2111,v_ename>='zzy',v_job>='mary',v_mgr>=7799,v_hiredate>=to_date('2015-5-6','yyyy-mm-dd'),v_sal>=2300,v_comm>=300); 141 142 --异常，部门不存在 143 add_emp(v_dno>=66,v_eno>=2111,v_ename>='zzy',v_job>='mary',v_mgr>=7799,v_hiredate>=to_date('2015-5-6','yyyy-mm-dd'),v_sal>=2300,v_comm>=300); 144 exception 145 when emp_20000 then 146 dbms_output.put_line('emp_20000的雇员编号不能重复'); 147 when emp_20001 then 148 dbms_output.put_line('emp_20001的部门编号不能空'); 149 when emp_20002 then 150 dbms_output.put_line('emp_20002不存在该部门编号'); 151 when others then 152 dbms_output.put_line('出现了其他异常错误'); 153 end; 154 / 155 156 --3.存储过程的参数模式in，out 157 --创建存储过程 158 create or replace procedure QueryEmp 159 (v_empno in emp.empno%type, 160 v_ename out emp.ename%type, 161 v_sal out emp.sal%type) 162 as 163 begin 164 select ename,sal into v_ename,v_sal 165 from emp 166 where empno=v_empno; 167 dbms_output.put_line('温馨提示：编码为'||v_empno||'的员工已经查到！'); 168 exception 169 when no_data_found then 170 dbms_output.put_line('温馨提示：雇员不存在！'); 171 when others then 172 dbms_output.put_line('出现了其他异常错误'); 173 end QueryEmp; 174 / 175 176 --调用存储过程 177 declare 178 v1 emp.ename%type; 179 v2 emp.sal%type; 180 begin 181 QueryEmp(7788,v1,v2); 182 dbms_output.put_line('姓名:'||v1); 183 dbms_output.put_line('工资:'||v2); 184 185 QueryEmp(7900,v1,v2); 186 dbms_output.put_line('姓名:'||v1); 187 dbms_output.put_line('工资:'||v2); 188 189 QueryEmp(1111,v1,v2); 190 dbms_output.put_line('姓名:'||v1); 191 dbms_output.put_line('工资:'||v2); 192 end; 193 / 194 195 --输出结果 196 温馨提示：编码为7788的员工已经查到！ 197 姓名:SCOTT 198 工资:3000 199 温馨提示：编码为7900的员工已经查到！ 200 姓名:JAMES 201 工资:950 202 温馨提示：雇员不存在！ 203 姓名: 204 工资: 205 PL/SQL 过程已成功完成。 206 207 --4.创建带in out 参数过程 208 --创建存储过程 209 create or replace procedure swap(p1 in out number,p2 in out number) 210 is 211 v_temp number; 212 begin 213 v_tem:=p1; 214 p1:=p2; 215 p2:=v_temp; 216 end; 217 / 218 219 --调用存储过程 220 set serverout on 221 declare 222 num1 number:=100; 223 num2 number:=200; 224 begin 225 swap(num1,num2); 226 dbms_output.put_line('num1='||num1); 227 dbms_output.put_line('num2='||num2); 228 end; 229 /
二、存储过程案例2
1 1.更新指定员工工资，如工资小于1500，则加100 2 set serverout on 3 declare 4 --定义存储员工编号的变量 5 v_empno emp.empno%type :=&empno; 6 7 --定义存储员工工资的变量 8 v_sal emp.sal%type ; 9 10 begin 11 select sal into v_sal from emp where empno=v_empno; 12 if v_sal <=1500 then 13 update emp set sal=sal+100 where empno=v_empno; dbms_output.put_line('员工编号为:'||v_empno||'员工工资已经更新!'); 14 else 15 dbms_output.put_line('员工编号为:'||v_empno||'员工工资已经超过1500!'); 16 17 end if; 18 exception 19 when no_data_found then 20 dbms_output.put_line('没有找到该用户!'); 21 when too_many_rows then 22 dbms_output.put_line('多行结果集请使用游标!'); 23 when others then 24 dbms_output.put_line('其他错误!'); 25 end; 26 / 27 28 2.定义有in输入参数存储过程 29 定义有输入参数的存储过 30 -- 31 create or replace procedure p_box 32 (v_num in number,v_str in varchar2) 33 is 34 begin 35 dbms_output.put_line('第一个:'||v_num); 36 dbms_output.put_line('第二个:'||v_str); 37 end; 38 39 --调用存储过程 40 exec p_box(1,'holly'); 41 42 --3.定义有in输入和out输出参数的存储过程 43 create or replace procedure p_box 44 (v_num1 in number,v_num2 out number) 45 is 46 begin 47 v_num2:=v_num1; 48 end; 49 / 50 51 --调用存储过程 52 set serverout on 53 declare 54 v_num number; 55 begin 56 p_box(1,v_num); 57 dbms_output.put_line(v_num); 58 end; 59 / 60 61 --4.定义输入参数和输出参数是同一个变量的存储过程 62 create or replace procedure p_box 63 (v_num in out number) 64 is 65 begin 66 v_num:=v_num; 67 end; 68 / 69 70 --调用存储过程 71 set serverout on 72 declare 73 v_num number; 74 begin 75 v_num:=2; 76 p_box(v_num); 77 dbms_output.put_line(v_num); 78 end; 79 / 80 81 --5.定义存储过程进行循环插入10条数据 82 (1)插入数据 83 (2)提交事务 84 create or replace procedure p_teacher 85 (v_min in number,v_max in number) 86 is 87 begin 88 for i in v_min..v_max loop 89 insert into teacher values(i,'holly1'); 90 dbms_output.put_line('第'||i||'次添加数据'); 91 end loop; 92 commit; 93 end; 94 / 95 96 --6.调用存储过程,执行插入数据操作 97 --(1)查询出tid的最大值 98 --(2)循环tid的最大值到tid+10的范围 99 set serverout on 100 declare 101 v_min teacher.tid%type; 102 v_max teacher.tid%type; 103 begin 104 select max(tid) into v_min from teacher; 105 v_min:=v_min+1; 106 v_max:=v_min+2; 107 p_teacher(v_min,v_max); 108 end; 109 / 110 111 112
三、存储过程案例3
1 --1.创建表 2 create table logtable (userid varchar2(10), logdate date); 3 4 --2.定义存储过程 5 create or replace procedure logexecution 6 is 7 begin 8 insert into logtable (userid, logdate) values (user, sysdate); 9 end; 10 / 11 12 --3.如果存储过程发生错误,调试方法如下 13 show errors procedure 存储过程名或函数名; 14 15 --例8．插入员工记录: 16 create or replace 17 procedure insertEmp( 18 v_empno in emp.empno%TYPE, 19 v_firstname in emp.ename%TYPE, 20 v_deptno in emp.deptno%TYPE 21 ) 22 as 23 --定义异常类型 24 empno_remaining exception; 25 pragma exception_init(empno_remaining, -1); 26 /* -1 是违反唯一约束条件的错误代码 */ 27 begin 28 insert into emp(empno, ename, hiredate,deptno) 29 values(v_empno, v_firstname, sysdate, v_deptno); 30 dbms_output.put_line('温馨提示:插入数据记录成功!'); 31 exception 32 when empno_remaining then 33 dbms_output.put_line('温馨提示:违反数据完整性约束!'); 34 when others then 35 dbms_output.put_line(sqlcode||'---'||sqlerrm); 36 end insertEmp; 37 / 38 --调错 39 show errors procedure insertEmp; 40 41 --例9．使用存储过程向dept表中插入数据。 42 create or replace 43 procedure insert_dept 44 (v_dept_id in dept.deptno%TYPE, 45 v_dept_name in dept.dname%TYPE, 46 v_loc in dept.loc%TYPE) 47 is 48 --定义异常类型 49 ept_null_error exception; 50 pragma exception_init(ept_null_error, -1400); 51 begin 52 insert into dept 53 (deptno, dname, loc) 54 values 55 (v_dept_id, v_dept_name, v_loc); 56 dbms_output.put_line('插入部门'||v_dept_id||'成功'); 57 exception 58 when dup_val_on_index then 59 raise_application_error(-20000, '部门编码不能重复'); 60 when ept_null_error then 61 raise_application_error(-20001, '部门编码、部门名称不能为空'); 62 end insert_dept; 63 / 64 65 --调用示例9存过过程方法如下--------------------------------------------- 66 --使用命令调用存储过程 67 --(1)调用存储过程时按照位置传参数 68 set serverout on 69 exec insert_dept(60,'学术部','安德门'); 70 / 71 72 --(2)调用存储过程按照参数名传递参数 73 set serverout on 74 exec insert_dept(v_dept_id=>60,v_dept_name=>'学术部',v_loc=>'安德门'); 75 / 76 77 --(3)调用存储过程按照混用方式传递参数 78 set serverout on 79 exec insert_dept(null,v_loc=>'安德门',v_dept_name=>'学术部'); 80 / 81 82 --在pl/sql块中调用存储过程 83 set serverout on 84 declare 85 --定义异常类型 86 ept_20000 exception; 87 pragma exception_init(ept_20000, -20000); 88 ept_20001 exception; 89 pragma exception_init(ept_20001, -20001); 90 begin 91 --调用存储过程 92 --正确插入,按位置传递参数 93 insert_dept(70, '部门300', '安德门'); 94 --部门标号不能为空 95 insert_dept(null, '部门300', '安德门'); 96 --违反唯一约束 97 insert_dept(70, '部门310', '新街口'); 98 EXCEPTION 99 WHEN ept_20000 THEN 100 DBMS_OUTPUT.PUT_LINE('ept_20000部门编码不能重复'); 101 WHEN ept_20001 THEN 102 DBMS_OUTPUT.PUT_LINE('ept_20001部门编码不能为空'); 103 WHEN OTHERS THEN 104 DBMS_OUTPUT.PUT_LINE('others出现了其他异常错误'); 105 END; 106 / 107 108 109 调用实例二： 110 set serverout on 111 DECLARE 112 ept_20000 EXCEPTION; 113 PRAGMA EXCEPTION_INIT(ept_20000, -20000); 114 ept_20001 EXCEPTION; 115 PRAGMA EXCEPTION_INIT(ept_20001, -20001); 116 BEGIN 117 --按参数名称调用 118 insert_dept(v_dept_name => '部门310', v_dept_id =>61, v_loc=> '学术部'); 119 --按混用方式调用 120 insert_dept(null, '部门320', v_loc => '学术部'); 121 122 EXCEPTION 123 WHEN ept_20000 THEN 124 DBMS_OUTPUT.PUT_LINE('ept_20000部门编码不能重复'); 125 WHEN ept_20001 THEN 126 DBMS_OUTPUT.PUT_LINE('ept_20001部门编码、部门名称不能为空'); 127 WHEN OTHERS THEN 128 DBMS_OUTPUT.PUT_LINE('others出现了其他异常错误'); 129 END; 130 / 131 132 133 --例11：查询指定员工记录； 134 create or replace 135 procedure queryEmp 136 (v_empno in emp.empno%TYPE, 137 v_ename out emp.ename%TYPE, 138 v_sal out emp.sal%TYPE) 139 as 140 begin 141 select ename, sal into v_ename, v_sal from emp where empno = v_empno; 142 dbms_output.put_line('温馨提示:编码为'||v_empno||'的员工已经查到!'); 143 exception 144 when no_data_found then 145 dbms_output.put_line('温馨提示:你需要的数据不存在!'); 146 when others then 147 dbms_output.put_line(sqlcode||'---'||sqlerrm); 148 end queryEmp; 149 / 150 151 152 --调用 153 set serverout on 154 declare 155 --定义变量用来接收存储过程输出的值 156 v1 emp.ename%TYPE; 157 v2 emp.sal%TYPE; 158 begin 159 queryEmp(7369, v1, v2); 160 dbms_output.put_line('姓名:'||v1); 161 dbms_output.put_line('工资:'||v2); 162 queryEmp(7499, v1, v2); 163 dbms_output.put_line('姓名:'||v1); 164 dbms_output.put_line('工资:'||v2); 165 queryEmp(7788, v1, v2); 166 dbms_output.put_line('姓名:'||v1); 167 dbms_output.put_line('工资:'||v2); 168 end; 169 / 170 171 --删除存储过程 172 drop procedure logexecution;
四、存储过程案例4
1 --1.创建输入输出使用同一个参数的存储过程 2 create or replace procedure swap( 3 p1 in out number, 4 p2 in out number 5 ) 6 is 7 v_temp number; --中间变量 8 begin 9 v_temp:=p1; 10 p1:=p2; 11 p2:=v_temp; 12 end; 13 / 14 --调用存储过程 15 set serverout on 16 declare 17 --定义存储过程的实参 18 num1 number:=100; 19 num2 number:=200; 20 begin 21 --按位置调用存储过程 22 swap(num1,num2); 23 dbms_output.put_line('num1:'||num1); 24 dbms_output.put_line('num2:'||num2); 25 end; 26 / 27 28 --2.存储过程和游标一起使用 29 --游标作为输出参数 30 create or replace procedure proc_emp(empsal out sys_refcursor) 31 as 32 begin 33 open empsal for 34 select empno,sal from emp; 35 end proc_emp; 36 / 37 --调用存储过程 38 set serverout on 39 declare 40 v_empno emp.empno%type; 41 v_sal emp.sal%type; 42 empsal sys_refcursor; --定义系统游标 43 begin 44 proc_emp(empsal); 45 loop 46 fetch empsal into v_empno,v_sal; 47 exit when empsal%notfound; 48 dbms_output.put_line(v_empno||'工资为:'||v_sal); 49 end loop; 50 close empsal ; 51 end; 52 / 53 54 --2.游标作为输入参数 55 create or replace procedure proc_emp(empsal in sys_refcursor) 56 as 57 --声明变量接受循环游标的得到的值 58 v_empno emp.empno%type; 59 v_sal emp.sal%type; 60 begin 61 loop 62 fetch empsal into v_empno,v_sal; 63 exit when empsal%notfound; 64 dbms_output.put_line(v_empno||'工资为:'||v_sal); 65 end loop; 66 end proc_emp; 67 / 68 --调用存储过程 69 set serverout on 70 declare 71 empsal sys_refcursor; --定义系统游标 72 begin 73 --打开游标 74 open empsal for 75 select empno,sal from emp; 76 --传入游标实参 77 proc_emp(empsal); 78 close empsal ; 79 end; 80 / 81 82 --3.利用函数获取员工工资 83 create function getsal(eno number) return number 84 is 85 v_sal emp.sal%type; 86 begin 87 select sal into v_sal from emp where empno=eno; 88 return v_sal; 89 exception 90 when no_data_found then 91 dbms_output.put_line('没有找到'); 92 end; 93 / 94 --调用函数 95 set serverout on 96 declare 97 v_sal number; 98 begin 99 v_sal:=getsal(7788); 100 dbms_output.put_line(v_sal); 101 end; 102 103 --4.利用函数获取某个部门的员工工资总和 104 105 106