8、存储过程

分类: IT文章 • 2022-04-25 12:30:10

一、存储过程介绍和案例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

存储过程案例2

三、存储过程案例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;

存储过程案例3

8、存储过程

相关推荐