PL/SQL程序设计-学习笔记5--存储函数和过程

六、存储函数和过程
ORACLE提供可以吧PL/SQL程序存储在数据库中，并可以在任何地方来运行它。这样就叫存储过程或函数。
过程和函数的唯一区别就是函数总向调用者返回数据，而过程则不返回数据。
例：获取某部门的工资总和
create or replace function get_salary(
dep_id employees.department_id%type,
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 = dep_id;
return v_sum;
exception
when no_data_found then
dbms_output.put_line('您需要的数据不存在');
when others then
dbms_output.put_line(sqlcode||':'||sqlerrm);
end;
执行该函数：
declare
v_num number;
v_sum number;
begin
v_sum := get_salary(80,v_num);
dbms_output.put_line('80号部门的工资总和：'||v_sum||', 人数：'||v_num);
end;
函数声明时定义的参数成为形式参数，应用程序调用时为函数传递的参数为实际参数。
函数传递参数方法：位置表式法、名称表式法、混合表式法。
实际参数和形式参数之间的数据传递有两种方法：传址法和传值法。
传址法：指在调用函数时，将实际参数的地址指针传递给形式参数，使形式参数和实际参数指向内存中的同一区域，从而实现参数数据的传递。这种方法又称作参照法，即形式参数参照实际参数数据。输入参数均采用传址法传递参数。
传值法：指将实际参数的数据拷贝到形式参数，而不是传递实际参数的地址。默认时，输出参数和输入/输出参数均采用传值法。在函数调用时，ORACLE将实际参数数据拷贝到输入/输出参数，而当函数正常运行退出时，又将输出形式参数和输入/输出形式参数数据拷贝到实际参数变量中。
create or replace procedure query_emp(
v_empid employees.employee_id%type,
v_name out employees.last_name%type,
v_sal out employees.salary%type)
is
begin
select last_name,salary into v_name,v_sal
from employees
where employee_id = v_empid;
dbms_output.put_line('员工号为：'||v_empid||'的员工已经找到');
exception
when no_data_found then
dbms_output.put_line('您需要的数据不存在');
when others then
dbms_output.put_line(sqlcode||':'||sqlerrm);
end;
调用方法：
declare
v1 employee.last_name%type;
v2 employee.salary%typ;
begin
query_emp(200,v1,v2);
dbms_output.put_line('姓名：'||v1||',工资：'||v2);
query_emp(201,v1,v2);
dbms_output.put_line('姓名：'||v1||',工资：'||v2);
end;
在创建存储工程时，可使用authid current_user或authid definer选项，以表明在执行该过程时oracle使用的权限：
1）、如果使用authid current_user选项创建一个过程，则oracle用调用该过程的用户权限执行该过程。为了成功执行该过程，调用者必须具有访问该存储过程体中引用的所有数据库对象所必须得权限。
2）、如果用默认的authid definer选项创建过程，则oracle使用过程所有者的特权执行该过程。为了成功执行该过程，过程的所有者必须具有访问该存储过程体中引用的所有数据库对象所必须得权限。想要简化应用程序用户的特权管理，在创建存储工程时，一般选择authid definer选项---这样就不必授权给需要调用的此过程的所有用户了。

开发存储过程步骤：
1）、使用文字编辑处理软件编辑存储过程源码；
2）、在SQLPLUS或用调试工具将存储过程程序进行解释；如：SQL>START c:\stat1.sql
3）、调试源码直到正确；（使用show error命令来提示源码错误的位置；使用user_errors数据字典来查看各存储过程的错误位置）
4）、授权执行权给相关的用户或角色；（grant execute on dbms_job to public with grant option）
5）、与过程相关数据字典；（user_source,all_source,dba_source,user_errors）(describe procedure_name查看过程的名字及其参数)
6）、删除过程和函数；（drop procedure [user.]procedure_name; drop function [user].function_name）