Oracle数据库之PL/SQL过程与函数

hailai

Oracle数据库之PL/SQL过程与函数
　　PL/SQL块分为匿名块与命名块，命名块又包含子程序、包和触发器。
　　过程和函数统称为PL/SQL子程序，我们可以将商业逻辑、企业规则写成过程或函数保存到数据库中，以便共享。
　　过程和函数均存储在数据库中，并通过参数与其调用者交换信息。过程和函数的唯一区别是函数总向调用者返回数据，而过程不返回数据。
　　1. 存储过程概念
　　存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL语句集，存储在数据库中。经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。
　　存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。
　　2. 创建过程
　　语法：



CREATE [ OR REPLACE ] PROCEDURE [ schema. ] procedure_name
[ ( parameter_declaration [, parameter_declaration ]... ) ]
[ invoker_rights_clause ]
{ IS | AS }
{ [ declare_section ] body | call_spec | EXTERNAL} ;
　　说明：
　　procedure_name：过程名称。
　　parameter_declaration：参数声明，格式如下：



parameter_name [ [ IN ] datatype [ { := | DEFAULT } expression ]
| { OUT | IN OUT } [ NOCOPY ] datatype
　　IN：输入参数。
　　OUT：输出参数。
　　IN OUT：输入输出参数。
　　invoker_rights_clause：这个过程使用谁的权限运行，格式：



AUTHID { CURRENT_USER | DEFINER }
　　declare_section：声明部分。
　　body：过程块主体，执行部分。
　　一般只有在确认procedure_name过程是新过程或是要更新的过程时，才使用OR REPALCE关键字，否则容易删除有用的过程。
　　示例1：



CREATE PROCEDURE remove_emp (employee_id NUMBER) AS
tot_emps NUMBER;
BEGIN
DELETE FROM employees
WHERE employees.employee_id = remove_emp.employee_id;
tot_emps := tot_emps - 1;
END;
　　示例2：



CREATE OR REPLACE PROCEDURE insert_emp(
v_empno     in employees.employee_id%TYPE,
v_firstname in employees.first_name%TYPE,
v_lastname  in employees.last_name%TYPE,
v_deptno    in employees.department_id%TYPE
)
AS
empno_remaining EXCEPTION;
PRAGMA EXCEPTION_INIT(empno_remaining, -1);
BEGIN
INSERT INTO EMPLOYEES(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, HIRE_DATE,DEPARTMENT_ID)
VALUES(v_empno, v_firstname,v_lastname, sysdate, v_deptno);
DBMS_OUTPUT.PUT_LINE('插入成功！');
EXCEPTION
WHEN empno_remaining THEN
DBMS_OUTPUT.PUT_LINE('违反数据完整性约束!');
DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END;
　　3. 使用过程参数
　　当建立过程时，既可以指定过程参数，也可以不提供任何参数。
　　过程参数包括输入参数、输出参数和输入输出参数，其中输入参数（IN）用于接收调用环境的输入数据，输出参数（OUT）用于将输出数据传递到调用环境，而输入输出参数（IN OUT）不仅要接收输入数据，而且还要输出数据到调用环境。
　　3.1 带有输入参数的过程
　　通过使用输入参数，可以将应用程序数据传递到过程。当定义过程参数时，默认参数模式是输入参数，另外可以使用IN关键字显式定义输入参数。
　　示例：



CREATE OR REPLACE PROCEDURE insert_emp(
empno employee.empno%TYPE,
ename employee.ename%TYPE,
job employee.job%TYPE,
sal employee.sal%TYPE,
comm IN employee.comm%TYPE,
deptno IN employee.deptno%TYPE
)
IS
BEGIN
INSERT INTO employee VALUES(empno, ename, job, sal, comm, depno);
END;
　　3.2 带有输出参数的过程
　　通过在过程中使用输出参数，可以将数据或消息传递到调用环境和应用程序。当定义输出参数时，需要指定参数模式OUT。
　　示例：



CREATE OR REPLACE PROCEDURE update_sal(
eno NUMBER,
salary NUMBER,
name out VARCHAR2)
IS
BEGIN
UPDATE employee SET sal=salary WHERE empno=eno
RETURNING ename INTO name;
END;
　　3.3 带有输入输出参数的过程
　　通过在过程中使用输入输出参数，可以在调用过程时输入数据到过程，在调用结束后输出数据到调用环境和应用程序。当定义输入输出参数时，需要指定参数模式为IN OUT。
　　示例：



CREATE OR REPLACE PROCEDURE divide(
num1 IN OUT NUMBER,
num2 IN OUT NUMBER)
IS
v1 NUMBER;
v2 NUMBER;
BEGIN
v1 := trunc(num1 / num2);
v2 := mod(num1,num2);
num1 := v1;
num2 := v2;
END;
　　4. 调用过程
　　当在SQL*PLUS中调用过程时，需要使用CALL或者EXECUTE命令，而在PL/SQL块中过程可以直接引用。
　　ORACLE使用EXECUTE语句来调用存储过程语法：



EXEC[UTE] procedure_name(parameter1, parameter2, …);
　　示例1：



-- 调用删除员工的过程
EXEC remove_emp(1);
-- 调用插入员工的过程
EXECUTE insert_emp(1, 'tommy', 'lin', 2);
　　示例2：



DECLARE
v_name employee.ename%type;
BEGIN
update_sal(&eno,&salary,v_name);
dbms_output.put_line('姓名:'||v_name);
END;
　　5. 函数介绍
　　函数是一段独立的PL/SQL程序代码，它执行某个特定的、明确的任务。通常，函数将处理从程序的调用部分传递给它的信息，然后返回单个值。信息通过称为参数的特殊标识符传递给函数，然后通过RETURN语句返回。
　　6. 创建函数
　　语法：



CREATE [ OR REPLACE ] FUNCTION [ schema. ] function_name
[ ( parameter_declaration [, parameter_declaration]... )
]
RETURN datatype
[ { invoker_rights_clause
| DETERMINISTIC
| parallel_enable_clause
| RESULT_CACHE  [ relies_on_clause ]
}...
]
{ { AGGREGATE | PIPELINED }  USING [ schema. ] implementation_type
| [ PIPELINED ] { IS | AS } { [ declare_section ] body
| call_spec
| EXTERNAL
}
} ;
　　示例1：



CREATE FUNCTION get_bal(acc_no IN NUMBER)
RETURN NUMBER
IS
acc_bal NUMBER(11,2);
BEGIN
SELECT order_total INTO acc_bal FROM orders
WHERE customer_id = acc_no;
RETURN(acc_bal);
END;
　　函数参数也有输入、输出、输入输出三种模式：IN、OUT、IN OUT是形参的模式。若省略，则为IN模式。
　　和过程一样，IN模式的形参只能将实参传递给形参，进入函数内部，但只能读不能写，函数返回时实参的值不变。
　　OUT模式的形参会忽略调用时的实参值（或说该形参的初始值总是NULL），但在函数内部可以被读或写，函数返回时形参的值会赋予给实参。
　　IN OUT具有前两种模式的特性，即调用时，实参的值总是传递给形参，结束时，形参的值传递给实参。
　　调用时，对于IN模式的实参可以是常量或变量，但对于OUT和IN OUT模式的实参必须是变量。
　　示例2：



CREATE OR REPLACE FUNCTION get_salary(
dept_no IN NUMBER DEFAULT 1,
emp_count OUT NUMBER)
RETURN NUMBER
IS
V_sum NUMBER;
BEGIN
SELECT SUM(SALARY), count(*) INTO V_sum, emp_count FROM EMPLOYEES
WHERE DEPARTMENT_ID=dept_no;
RETURN v_sum;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('数据不存在');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('其它异常：');
DBMS_OUTPUT.PUT_LINE('错误号：' || SQLCODE||'，错误消息：'||SQLERRM);
END get_salary;
　　7. 函数调用
　　语法：



function_name([[parameter_name1 =>] value1[, [parameter_name2 =>] value2, ...]]);
　　示例1：



DECLARE
v_num NUMBER;
v_sum NUMBER;
BEGIN
v_sum := get_salary(27, v_num);
DBMS_OUTPUT.PUT_LINE('部门27的工资总和：'||v_sum||'，人数为：'||v_num);
END;
　　示例2：



DECLARE
v_num NUMBER;
v_sum NUMBER;
BEGIN
v_sum := get_salary(dept_no => 27, emp_count => v_num);
DBMS_OUTPUT.PUT_LINE('部门27的工资总和：'||v_sum||'，人数为：'||v_num);
END;
　　示例3：



DECLARE
v_num NUMBER;
v_sum NUMBER;
BEGIN
v_sum := get_salary(emp_count => v_num);
DBMS_OUTPUT.PUT_LINE('部门27的工资总和：'||v_sum||'，人数为：'||v_num);
END;
　　8. 删除过程或函数
　　删除过程语法：



DROP PROCEDURE [schema.]procudure_name;
　　删除函数语法：



DROP FUNCTION [schema.]function_name;
　　9. 过程与函数比较

过程函数



作为PL/SQL语句执行
作为表达式的一部分执行


在规范中不包含RETURN子句
必须在规范中包含RETURN子句


不返回任何值
必须返回单个值


可以RETURN语句，但是与函数不同，它不能用于返回值
必须包含至少一条RETURN语句
　　过程与函数的相同功能有：

• 都使用IN模式的参数传入数据、OUT模式的参数返回数据。
  
• 输入参数都可以接受默认值，都可以传值或传引导。
  
• 调用时的实际参数都可以使用位置表示法、名称表示法或组合方法。
  
• 都有声明部分、执行部分和异常处理部分。
  
• 其管理过程都有创建、编译、授权、删除、显示依赖关系等。