Oracle 学习笔记13 —— 存储过程

存储过程是一种命名的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 onSQL> 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', hire_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 nameNAME----------------------------------------------------------------ALLENSQL> print sal       SAL----------      1600SQL> print nameNAME----------------------------------------------------------------ALLENSQL> print sal       SAL----------      1600

也可以通过select语句来查看检索结果。

SQL> select :name, :sal from dual;:NAME                                                                  :SAL---------------------------------------------------------------- ----------ALLEN                                                                  1600

下面使用匿名程序块调用存储过程

SQL> set serveroutput onSQL> 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薪水：1600PL/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  /10PL/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----------         1SQL> select * from temp2;         N----------         2

事务提交，数据正常保存。

SQL> exec proc_transaction2(1,2);PL/SQL 过程已成功完成。SQL> select count(1) from temp1;  COUNT(1)----------         0SQL> select count(1) from temp2;  COUNT(1)----------         0

事务回滚，数据被撤销。