PL/SQL详解之存储过程

创建存储过程

CREATE OR REPLACE PROCEDURE UpdateAuths(

    p_AuthsCode auths.author_code%TYPE,

    p_AuthsSalary auths.salary%TYPE)

AS

BEGIN

    UPDATE auths

        SET salary=p_AuthsSalary

        WHERE author_code=p_AuthsCode;

    COMMIT;

END UpdateAuths;

调用存储过程

DECLARE

    v_authorcode auths.author_code%TYPE:='A0001';

    v_salary auths.salary%TYPE:=350;

BEGIN

    UpdateAuths(v_authorcode,v_salary);

END;

形参和实参

p_AuthsSalary,p_AuthsCode为形式参数

v_authorcode,v_salary为实际参数

关键字代表参数的三种不同模式

IN：当调用存储过程的时候，该模式的形参接收对应实参的值，并且该形参是只读的，即不能被修改。如果在创建存储过程时没有指定参数的模式，则默认为IN。

OUT：在存储过程中，该形参被认为是只能写，即只能为其赋值。在存储过程中不能读它的值。返回时，将该形参值传给相应的实参。 

IN OUT：该模式是前两种模式的合并。

2)存储过程的处理部分

    一个PL/SQL块，该块包含定义部分、可执行部分以及异常处理部分，其中可执行部分是必须有的。

CREATE OR REPLACE 过程名

(

    --参数定义部分

)    

IS

    --局部变量定义部分

BEGIN

    --可执行部分

EXCEPTION

    --异常处理部分

END 过程名;

在END后加上存储过程名是可选的，只是为了增加程序的可读性。如果加上，则必须要和前面的存储过程名相同。

1.2存储过程的参数

1)参数的数据类型

    在定义一个存储过程参数时，不能指定CHAR类型和VARCHAR2类型形参的长度，也不能指定NUMBER形参的精度和标度。这些约束有实参来传递。  

CREATE OR REPLACE PROCEDURE proc_auths(

    --参数定义了类型长度，这将产生编译错误。

    p_code IN OUT VARCHAR2(6),

    P_salary OUT NUMBER(8,2)) AS

BEGIN

    ...

END proc_auths;

应修改为：

CREATE OR REPLACE PROCEDURE proc_auths(

    p_code IN OUT VARCHAR2,

    P_salary OUT NUMBER) AS

BEGIN

    SELECT salary INTO p_salary

        FROM auths

        WHERE author_code=p_code;

END proc_auths; 

调用存储过程：

DECLARE

    v_code VARCHAR2(6);

    v_salary NUMBER(8,2);

BEGIN

    v_code:='A0001';

    proc_auths(v_code,v_salary);

END;

如果使用%TYPE为参数定义类型，那么该参数将具有定义在形参上而不是通过实参传递的数据长度。

CREATE OR REPLACE PROCEDURE query_salary(

    p_code IN OUT auths.author_code%TYPE,

    P_salary OUT auths.salary%TYPE) AS

BEGIN

    ...

END query_salary; 

如上面的存储过程，由于auths表中的author_code字段长度为6，因此p_code的长度也为6。

2)参数的传值方式

位置表示法

    实参通过位置与形参进行联系。

名称表示法

    实参是与形参的名称进行联系。

定义一个存储过程

CREATE OR REPLACE PROCEDURE insert_auths(

    p_code auths.author_code%TYPE,

    p_name auths.name%TYPE) AS

BEGIN

    INSERT INTO auths(author_code,name) VALUES(p_code,p_name);

END insert_auths;

DECLARE

    v_code VARCHAR2(6);

    v_name VARCHAR2(12);

BEGIN

    v_code:='A0001';

    v_name:='张三';

    --使用位置表示法调用

    insert_auths(v_code,v_name);

    --使用命名表示法调用

    insert_auths(p_name-->v_name,p_code-->v_code);

END;

两种表示法可以混合使用。但是，当调用存储过程中出现了第一个命名表示法的参数时，后面的参数也必须使用命名表示法传值。

3)参数的缺省值

类似于变量的声明，一个过程或函数的形参可以有缺省值。如果参数有缺省值，那么在调用时就可以不用给它传值，只使用缺省值。如果给它传值，则实参的值代替缺省的值。

参数缺省值声明如下：

parameter [mode] datatype {:=|DEFAULT} initial_value

parameter是形参名称

mode是参数模式

datatype是参数的类型

initial_value用来为形参指定缺省值

使用关键字DEFAULT或":="来指定一个缺省值。

CREATE OR REPLACE PROCEDURE insert_auths(

    p_code auths.author_code%TYPE :='A0001',

    p_name auths.name%TYPE DEFAULT '张三'

) AS

BEGIN

    ...

END insert_auths;