PL/SQL之存储过程和函数

210次阅读

共计 5632 个字符，预计需要花费 15 分钟才能阅读完成。

1、创建存储过程

1.1 语法：
CREATE[OR REPLACE] PROCEDURE [schema.] procedure_name[(argument[{IN|OUT|IN OUT}] datatype[,…])] {IS|AS} pl/sql_body;

procedure_name 为存储过程的名称，
argument 是参数名，
datatype 是对应参数的数据类型，
pl/sql_body 是该存储过程真正进行的处理操作的 PL/SQL 块，
OR REPLACE 是可选项，如果存在一个同名的存储过程，则先删除后创建，
关键字 IS|AS 是等价的，用来引出过程体。

1)存储过程的参数
创建存储过程
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;

2、创建函数

函数和存储过程非常类型，都有三种模式的参数。他们都可以被存储在数据库中，并且在快中调用。

存储过程只能作为一个 PL/SQL 语句调用，而函数作为表达式的一部分调用。并且它们的定义部分、可执行部分和异常处理部分都是不同的。

2.1 创建函数的语法

CREATE [OR REPLACE] FUNCTION schema.function
[(argument[{IN|OUT|IN OUT}]datatype[,…])]
RETURN return_datatype {IS|AS}
PL/SQL_body;

function 是函数名
argument 是参数名
datatype 是参数的类型
return_datatype 是函数返回值的类型
PL/SQL_body 是函数的处理部分

参数列表时可选的。在没有参数的情况下, 函数的定义与调用都没有圆括号。但返回值类型是必需的，因为函数是作为表达式的一部分调用，必须返回一个值。

CREATE OR REPLACE FUNCTION SalaryStat(
p_Sex auths.sex%TYPE)
RETURN BOOLEAN IS
v_MaxAuthors NUMBER;
v_ReturnValue BOOLEAN;
BEGIN
SELECT COUNT(author_code)
INTO v_MaxAuthors
FROM auths;
IF v_MaxAuthors> 10 THEN
v_ReturnValue:=TRUE;
ELSE
v_ReturnValue:=FALSE;
END IF;
RETURN v_ReturnValue;
END SalaryStat;

DECLARE CURSOR c_Auths IS
SELECT distinet sex
FROM auths;
BEGIN
FOR v_AuthsRecord IN c_Auths LOOP
IF SalaryStat(v_AuthsRecord.sex) THEN
UPDATE auths
SET salary = salary-50
WHERE sex=v_AuthsRecord.sex;
END IF;
END LOOP;
END;

2.2 函数的返回值
在函数内，是通过 RETURN 语句来返回值的。
RETURN expression;

expression 是准备返回的值。如果 expression 的类型和函数头中指定的类型不符，expression 类型会自动转换。RETURN 语句被执行后，控制权立刻返回给调用环境。

在函数体重可以有多条 RETURN 语句，但还只能有一条被执行。在函数结束的时候，如果没有执行 RETURN 语句会产生错误。

CREATE OR REPLACE FUNCTION SalaryStat(
p_Sex auths.sex%TYPE)
RETURN VARCHAR2 IS
v_MaxAuthors NUMBER;
BEGIN
SELECT COUNT(author_code)
INTO v_MaxAuthors
FROM auths;
IF v_MaxAuthors> 10 THEN
RETURN v_ReturnValue;
ELSE
RETURN v_ReturnValue;
END IF;
END SalaryStat;

RETURN 也可以用在存储过程中。在这种情况下，它没有参数。当执行了不带参数的 RETURN 语句后，立刻将控制权返回到调用环境，并将 OUT 和 IN OUT 模式的形参的当前值传给实参，然后继续执行调用存储过程后的语句。

函数和存储过程的相同点：
都可以通过 OUT 模式的参数返回一个活多个值。
代码都有定义部分、可执行部分和异常部分。
都可以使用缺省值。
都可以用位置表示法和命名表示法调用。

一般情况下，如果只有一个返回值，使用函数；如果有多个返回值则使用存储过程。

3、删除过程与函数

删除过程
DROP PROCEDURE procedure_name;

删除函数
DROP FUNCTION function_name;

4、库存子程序和局部子程序

前面所讲的子程序都是存储在数据库中的子程序，即库存子程序。这些子程序都是有 CREATE 命令创建的，并可在其它的 PL/SQL 快中调用。他们在创建时要要进行编译，并将编译后的代码存储在数据库中。当子程序被调用时，编译后的代码从数据库中读出并执行。

一个子程序也可以在块的定义部分创建，这样的子程序被叫作局部子程序。

DECLARE
CURSOR c_AllAuthors IS
SELECT name,sex
FROM auths;
v_FormattedName VARCHAR2(60);
/* 函数将返回带性别的作家名，性别用括号括起放在名字的后面 */
FUNCTION FormatName(p_Name IN VARCHAR2,p_Sex IN NUMBER) RETURN VARCHAR2 IS
v_Sex VARCHAR2(16);
BEGIN
IF p_Sex=1 THEN
v_Sex:=’ 男 ’;
ELSE
v_Sex:=’ 女 ’;
END IF;
RETURN p_Name ||'(‘||v_Sex||’)’;
END FormatName;
– 块的执行部分开始
BEGIN
FOR v_AuthsRecord IN c_AllAuthors LOOP
v_FormattedName:=FormatName(v_AuthsRecord.name,v_AuthsRecord.sex);
DBMS_OUTPUT.PUT_LINE(v_FormattedName);– 显示结果
END LOOP;
END;