Oracle存储过程及举例

张兴康

存储过程
存储过程可以有多个或者零个输入输出参数，但通常没有返回值。存储过程是已经编译好的代码，所以执行效率非常高
存储过程创建语句

1. create [or replace] procedure 存储过程名 [(输入、输出参数、in/out模式参数)]  
   
2. is/as  
   
3. begin  
   
4. sentences;  
   
5. [exception  
   
6. sentences;]  
   
7. end 存储过程名;

复制代码

一个没有参数的存储过程

1. create or replace procedure pro_no_par is  
   
2. begin  
   
3. update employees set salary=salary+100 where employee_id=100;  
   
4. commit;  
   
5. dbms_output.put_line('工资已经调整！！');  
   
6. end pro_no_par;  
   
7. --执行,使用execute  
   
8. execute pro_no_par;

复制代码

执行结果：

1. 匿名块已完成  
   
2. 工资已经调整！！  
   
3. 执行使用匿名块  
   
4. begin  
   
5. pro_no_par;  
   
6. end;  
   
7. 匿名块已完成  
   
8. 工资已经调整！！

复制代码

一个带in模式参数的存储过程
参数的方式有三种：指定名称传递；按位置传递；混合方式传递
注意混合方式传递从使用【指定名称传递】的位置开始，后面的参数必须使用【指定名称传递】

1. create or replace procedure pro_in_par  
   
2. ( var_1 in nvarchar2,  
   
3.   var_2 in number)   is  
   
4. begin  
   
5. update employees set salary=salary+var_2 where first_name=var_1;  
   
6. commit;  
   
7. dbms_output.put_line(var_1||'的工资已经增加了'||var_2||'!');  
   
8. end pro_in_par;  
   
9. --执行  
   
10. execute pro_in_par('Steven',100);  

复制代码

执行结果：

1. 匿名块已完成  
   
2. Steven的工资已经增加了100!  
   
3. execute pro_in_par(var_1=>'Steven',var_2=>100);  
   
4. 匿名块已完成  
   
5. Steven的工资已经增加了100!  
   
6. execute pro_in_par('Steven',var_2=>100);  
   
7. 匿名块已完成  
   
8. Steven的工资已经增加了100!  

复制代码

1. --举例混合参数输入，后面的参数没有使用【指定名称传递】  
   
2. execute pro_in_par(var_1=>'Steven',100);  
   
3. 在行 42 上开始执行命令时出错:  
   
4. execute pro_in_par(var_1=>'Steven',100)  
   
5. 错误报告:  
   
6. ORA-06550: 第 1 行, 第 34 列:   
   
7. PLS-00312: 一个定位相关参数没有说明其相关性  
   
8. ORA-06550: 第 1 行, 第 7 列:   
   
9. PL/SQL: Statement ignored  
   
10. 06550. 00000 -  "line %s, column %s:\n%s"  
    
11. *Cause:    Usually a PL/SQL compilation error.  
    
12. *Action:  

复制代码

一个带out模式和in out模式参数存储过程

1. create procedure pro_out_par  
   
2. ( var_1 in out number,  
   
3.   var_2 out employees.first_name%type,  
   
4.   var_3 out employees.phone_number%type) is  
   
5. begin  
   
6. select first_name,phone_number into var_2,var_3 from employees where employee_id=var_1;  
   
7. end pro_out_par;  
   
8. --执行  
   
9. declare   
   
10. ex_var_1 number;  
    
11. ex_var_2  employees.first_name%type;  
    
12. ex_var_3  employees.phone_number%type;  
    
13. begin  
    
14. ex_var_1:=102;  
    
15. pro_out_par(ex_var_1,ex_var_2,ex_var_3);  
    
16. dbms_output.put_line('员工号为'||ex_var_1||'的员工姓名是;'||ex_var_2||',联系电话是：'||ex_var_3);  
    
17. end;  

复制代码

1. 匿名块已完成  
   
2. 员工号为102的员工姓名是;Lex,联系电话是：515.123.4569  

复制代码

张兴康

fteair

fteair

fteair

fteair

张兴康