存储过程是一种命名的PL/SQL程序块,存储过程是已经编译好的代码,所有在调用的时候不必再次进行编译,从而提高性能。创建存储过程的语法如下:
create procedure procedure_name [(parameter[, parameter, ...])] is
[local declarations]
begin
execute statements
[exception
exception handlers]
end [procedure _name]
为了重新定义存储过程,可以在create 语句中使用 or replace选项,使新版本覆盖旧版本。
SQL> create or replace procedure proc_hello is
2 begin
3 dbms_output.put_line('Hello, world!');
4 end proc_hello;
5 /
过程已创建。
SQL> set serveroutput on
SQL> begin
2 proc_hello;
3 end;
4 /
Hello, world!
PL/SQL 过程已成功完成。
SQL> execute proc_hello;
Hello, world!
PL/SQL 过程已成功完成。
SQL> exec proc_hello;
Hello, world!
PL/SQL 过程已成功完成。
如果编译发生错误,为了查看错误的详细信息,可以随后输入show error命令来显示错误信息。
存储过程参数
在创建存储过程时,提供适量参数可以使参数更灵活。Oracle存储过程的参数有:输入参数,输出参数和输入输出参数三种。
1、in输入参数
输入参数的参数值由调用者传入,并且只能被存储过程读取。这种参数模式是最常用的,也是默认的参数模式。
下面定义一个创建员工的存储过程。
SQL> create or replace procedure proc_create_emp (
2 id_param in number,
3 name_param in varchar2,
4 job_param in varchar2,
5 hire_param in date,
6 salary_param in number) is
7 begin
8 insert into scott.emp(empno, ename, job, hiredate, sal)
9 values(id_param,name_param, job_param, hire_param, salary_param);
10 end proc_create_emp;
11 /
过程已创建。
SQL> begin
2 proc_create_emp(id_param=>8000, name_param=>'ATG',job_param=>'CLERK', hir
e_param=>'2013-01-01', salary_param=>10000);
3 end;
4 /
PL/SQL 过程已成功完成。
SQL> exec proc_create_emp(id_param=>8001, name_param=>'ATG',job_param=>'CLERK',
hire_param=>'2013-01-01', salary_param=>10000);
PL/SQL 过程已成功完成。
SQL> exec proc_create_emp(8002, 'Tiny', 'SALESMAN', '2010-01-01', 3000);
PL/SQL 过程已成功完成。
上边是两种传递参数的方式。第一种方式称为名称表示法。指定了具体的参数名,因此对参数的输入顺序没有要求,可以任意打乱,因此输入更方便。但是当参数比较多少,调用过程时会比较长。第二种称为位置表示法,由于没有指定具体的参数名,调用时必须和声明参数的顺序一致。还有将这两种方式混合起来使用,称为混合表示法。混合标示法相对来说,结构会比较混乱,不建议使用。
SQL> select empno from emp where empno in (8000, 8001, 8002);
EMPNO
----------
8000
8001
8002
2、out输出参数
OUT类型的参数由存储过程传入值,然后由用户接受参数值。下面通过scott.emp表创建一个搜索过程,该过程将根据提供的empno列的值检索雇员的ename和sal。
SQL> create or replace procedure proc_search_employee (
2 empno_param in number,
3 name_param out emp.ename%type,
4 salary_param out emp.sal%type) is
5 begin
6 select ename, sal
7 into name_param, salary_param
8 from scott.emp
9 where empno = empno_param;
10 exception
11 when no_data_found then
12 name_param := 'NULL';
13 salary_param := -1;
14 dbms_output.put_line('未找到指定编号的员工信息。');
15 end proc_search_employee;
16 /
过程已创建。
SQL>
存储过程要通过out参数返回值,所以在调用它时必须提供能够接受返回值的变量。调用时需要定义变量接收参数。
SQL> variable name varchar2(10);
SQL> variable sal number;
SQL> exec proc_search_employee(7499, :name, :sal);
PL/SQL 过程已成功完成。
为了查看执行结果可以使用print命令显示变量。
SQL> print name
NAME
----------------------------------------------------------------
ALLEN
SQL> print sal
SAL
----------
1600
SQL> print name
NAME
----------------------------------------------------------------
ALLEN
SQL> print sal
SAL
----------
1600
也可以通过select语句来查看检索结果。
SQL> select :name, :sal from dual;
:NAME :SAL
---------------------------------------------------------------- ----------
ALLEN 1600
下面使用匿名程序块调用存储过程
SQL> set serveroutput on
SQL> declare
2 name emp.ename%type;
3 sal emp.sal%type;
4 begin
5 proc_search_employee(7499, name, sal);
6 dbms_output.put_line('姓名:' || name);
7 dbms_output.put_line('薪水:' || sal);
8 end;
9 /
姓名:ALLEN
薪水:1600
PL/SQL 过程已成功完成。
3、in out 输入输出参数
对于in参数而言,它可以接收一个值,但是不能过程中修改这个值。而对于out参数,它在调用时为空,在过程执行中将为这参数指定一个值,并在执行结束后返回。而in out类型的参数同时具有in参数和out 参数的特性,在调用过程时既可以向该类型传入值,也可以从参数接收值;而在过程的执行中既可以读取又写入该类型参数。
SQL> create or replace procedure proc_toupper(
2 var_param1 in out varchar2,
3 var_param2 in out varchar2) is
4 begin
5 var_param1 := upper(var_param1);
6 var_param2 := upper(var_param2);
7 end proc_toupper;
8 /
过程已创建。
参数默认值
存储过程的参数也可以是默认值,这样当调用该过程时,如果未向参数传入值,则该参数将使用定义的默认值。
SQL> create or replace procedure proc_power(
2 var_num1 in number,
3 var_result out number,
4 var_num2 in number default 1 ) is
5 begin
6 var_result := power(var_num1, var_num2);
7 end proc_power;
8 /
过程已创建。
SQL>
SQL> declare
2 var_num number := 10;
3 var_result number;
4 begin
5 proc_power(var_num, var_result);
6 dbms_output.put_line(var_result);
7 end;
8 /
10
PL/SQL 过程已成功完成。
SQL> create table temp1 (n number);
表已创建。
SQL> create table temp2 (n number);
表已创建。
SQL>
SQL> create or replace procedure proc_transaction1(
2 var1 in number,
3 var2 in number) is
4 begin
5 insert into temp1 values(var1);
6 insert into temp2 values(var2);
7 commit;
8 end proc_transaction1;
9 /
过程已创建。
SQL>
SQL> create or replace procedure proc_transaction2(
2 var1 in number,
3 var2 in number) is
4 begin
5 insert into temp1 values(var1);
6 insert into temp2 values(var2);
7 rollback;
8 end proc_transaction2;
9 /
过程已创建。
SQL> exec proc_transaction1(1,2);
PL/SQL 过程已成功完成。
SQL> select * from temp1;
N
----------
1
SQL> select * from temp2;
N
----------
2
事务提交,数据正常保存。
SQL> exec proc_transaction2(1,2);
PL/SQL 过程已成功完成。
SQL> select count(1) from temp1;
COUNT(1)
----------
0
SQL> select count(1) from temp2;
COUNT(1)
----------
0
事务回滚,数据被撤销。