第六章存储函数和过程

引言

ORACLE 提供可以把 PL/SQL 程序存储在数据库中，并可以在任何地方来运行它。这样就叫存储过程或函数。过程和函数统称为 PL/SQL 子程序，他们是被命名的 PL/SQL 块，均存储在数据库中，并通过输入、输出参数或输入/输出参数与其调用者交换信息。过程和函数的唯一区别是函数总向调用者返回数据，而过程则不返回数据。

创建函数

建立内嵌函数

基本语法

说明:
1.OR REPLACE 为可选. 有了它, 可以或者创建一个新函数或者替换相同名字的函数, 而不会出现冲突。
2.函数名后面是一个可选的参数列表, 其中包含 IN, OUT 或 IN OUT 标记. 参数之间用逗号隔开. IN 参数标记表示传递给函数的值在该函数执行中不改变; OUT 标记表示一个值在函数中进行计算并通过该参数传递给调用语句; IN OUT 标记表示传递给函数的值可以变化并传递给调用语句. 若省略标记, 则参数隐含为 IN。
3.因为函数需要返回一个值, 所以 RETURN 包含返回结果的数据类型。

例1. 不带参数的函数
create or replace function test_fun
  return date
  is
  v_date date;
begin
  select sysdate into v_date
  from dual;
  
  dbms_output.put_line('我是函数哈^^');
  
  return v_date;
end;
执行该函数
declare
  v_date date;
begin
  v_date := test_fun();
  dbms_output.put_line(v_date);
end;
例2. 获取某部门的工资总和：
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_date_found then
      dbms_output.put_line('您需要的数据不存在');
  when others then 
      dbms_output.put_line(sqlcode || ':' || sqlerrm);
end;

内嵌函数的调用

函数声明时所定义的参数称为形式参数，应用程序调用时为函数传递的参数称为实际参数。应用程序在调用函数时，可以使用以下三种方法向函数传递参数：

第一种参数传递格式称为位置表示法，格式为：
argument_value1[,argument_value2 …]
例 3：计算某部门的工资总和：
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;
第二种参数传递格式称为名称表示法，格式为：
argument => parameter [,…]
其中：argument 为形式参数，它必须与函数定义时所声明的形式参数名称相同。Parameter 为实际参数。在这种格式中，形势参数与实际参数成对出现，相互间关系唯一确定，所以参数的顺序可以任意排列。
例 4：计算某部门的工资总和：
declare
  v_num number;
  v_sum number;
begin
  v_sum := get_salary(emp_count => v_num,dep_id => 80);
  dbms_output.put_line('80号部门的工资总和：' || v_sum || ',人数' || v_num);
end;
第三种参数传递格式称为混合表示法：
即在调用一个函数时，同时使用位置表示法和名称表示法为函数传递参数。采用这种参数传递方法时，使用位置表示法所传递的参数必须放在名称表示法所传递的参数前面。也就是说，无论函数具有多少个参数，只要其中有一个参数使用名称表示法，其后所有的参数都必须使用名称表示法。
例5：
declare
  v_num number;
  v_sum number;
begin
  v_sum := get_salary(80,emp_count => v_num);
  dbms_output.put_line('80号部门的工资总和：' || v_sum || ',人数' || v_num);
end;
无论采用哪一种参数传递方法，实际参数和形式参数之间的数据传递只有两种方法：传址法和传值法。所谓传址法是指在调用函数时，将实际参数的地址指针传递给形式参数，使形式参数和实际参数指向内存中的同一区域，从而实现参数数据的传递。这种方法又称作参照法，即形式参数参照实际参数数据。输入参数均采用传址法传递数据。
传值法是指将实际参数的数据拷贝到形式参数，而不是传递实际参数的地址。默认时，输出参数和输入/输出参数均采用传值法。在函数调用时，ORACLE 将实际参数数据拷贝到输入/输出参数，而当函数正常运行退出时，又将输出形式参数和输入/输出形式参数数据拷贝到实际参数变量中。

参数默认值

在CREATE OR REPLACE FUNCTION 语句中声明函数参数时可以使用DEFAULT关键字为输入参数指定默认值。

例 6：
create or replace function get_salary(
  dep_id employees.department_id%type default 50,
  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_date_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(emp_count => v_num);
  dbms_output.put_line('50号部门的工资总和：' || v_sum || ',人数' || v_num);
end;

存储过程

创建过程

建立存储过程
在 ORACLE SERVER 上建立存储过程,可以被多个应用程序调用,可以向存储过程传递参数,也可以向存储过程传回参数。
创建过程语法:
例 7．删除指定员工记录；

create or replace procedure del_emp(
  v_empid in employees.employee_id%type)
  is
  
  no_result exception;
begin
  delete from employees
  where employee_id = v_empid;
  
  if sql%notfound then
      raise no_result;
  end if;
  
  dbms_output.put_line('编号为：' || v_empid || '的员工已被移除');
exception
  when no_result then
      dbms_output.put_line('您需要删除的数据不存在');
  when others then
      dbms_output.put_line(sqlcode || '--' || sqlerrm);
end;

例 8．插入员工记录；

create or replace procedure insert_emp(
  v_empno emp.empno%type,
  v_name emp.ename%type,
  v_deptno emp.deptno%type)
  
  is
  
  empno_remaining exception;
  
  pragma exception_init(empno_remaining,-1);
begin
  insert into emp(empno,ename,deptno)
      values(v_empno,v_name,v_deptno);
  dbms_output.put_line('插入数据成功');
exception
  when empno_remaining then
      dbms_output.put_line('违反完整性约束！');
  when others then
      dbms_output.put_line(sqlcode || '--' || sqlerrm);
end;

调用存储过程

ORACLE 使用 EXECUTE 语句来实现对存储过程的调用：
EXEC[UTE] Procedure_name( parameter1, parameter2…);

例 9：查询指定员工记录；

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_date_found then
      dbms_output.put_line('你要查询的数据不存在');
  when others then
      dbms_output.put_line(sqlcode || '--' || sqlerrm);
end;

调用方法

declare
  v1 employees.last_name%type;
  v2 employees.salary%type;
begin
  query_emp(200,v1,v2);
  dbms_output.put_line('姓名：' || v1 || '，工资' || v2);
  
  query_emp(201,v1,v2);
  dbms_output.put_line('姓名：' || v1 || '，工资' || v2);
end;

例 10．计算指定部门的工资总和，并统计其中的职工数量。

create or replace procedure proc_demo(
  v_depid employees.department_id%type default 10,
  v_salsum out employees.salary%type,
  v_empcount out number)
  
  is
  
begin
  select sum(salary),count(*) into v_salsum,v_empcount
  from employees
  where department_id = v_dep_id;
  
exception
  when no_date_found then
      dbms_output.put_line('你需要的数据不存在');
  when others then
      dbms_output.put_line(sqlcode || '--' || sqlerrm);
end;

调用方法

declare
  v_num number;
  v_sum number;
begin
  proc_demo(v_salsum => v_sum,v_empcount => v_num);
  dbms_output.put_line('10号部门的工资总额为：' || v_num || ',人数为：' || v_num);
end;

AUTHID

在创建存储过程时, 可使用 AUTHID CURRENT_USER 或 AUTHID DEFINER 选项,以表明在执行该过程时 Oracle 使用的权限。

如果使用 AUTHID CURRENT_USER 选项创建一个过程, 则 Oracle 用调用该过程的用户权限执行该过程. 为了成功执行该过程, 调用者必须具有访问该存储过程体中引用的所有数据库对象所必须的权限。

如果用默认的 AUTHID DEFINER 选项创建过程, 则 Oracle 使用过程所有者的特权执行该过程. 为了成功执行该过程, 过程的所有者必须具有访问该存储过程体中引用的所有数据库对象所必须的权限. 想要简化应用程序用户的特权管理, 在创建存储过程时, 一般选择 AUTHID DEFINER 选项 –-- 这样就不必授权给需要调用的此过程的所有用户了。

开发存储过程步骤

开发存储过程、函数、包及触发器的步骤如下：

使用文字编辑处理软件编辑存储过程源码
使用文字编辑处理软件编辑存储过程源码，需将源码存为文本格式。

在 SQLPLUS 或用调试工具将存储过程程序进行解释
在 SQLPLUS 或用调试工具将存储过程程序进行解释；
在 SQL>下调试，可用 START 或 GET 等 ORACLE 命令来启动解释。如： SQL>START c:\stat1.sql

调试源码直到正确我
们不能保证所写的存储过程达到一次就正确。所以这里的调式是每个程序员必须进行的工作之一。在 SQLPLUS 下来调式主要用的方法是：
1、使用 SHOW ERROR 命令来提示源码的错误位置；
2、使用 user_errors 数据字典来查看各存储过程的错误位置。

授权执行权给相关的用户或角色
如果调式正确的存储过程没有进行授权，那就只有建立者本人才可以运行。所以作为应用系统的一部分的存储过程也必须进行授权才能达到要求。在 SQL*PLUS 下可以用 GRANT 命令来进行存储过程的运行授权。
GRANT EXECUTE ON dbms_job TO PUBLIC WITH GRANT OPTION

与过程相关数据字典
USER_SOURCE, ALL_SOURCE, DBA_SOURCE, USER_ERRORS
相关的权限:
CREATE ANY PROCEDURE
DROP ANY PROCEDURE
在 SQL*PLUS 中，可以用 DESCRIBE 命令查看过程的名字及其参数表。
DESCRIBE Procedure_name;

删除过程和函数

删除过程可以使用 DROP PROCEDURE 命令对不需要的过程进行删除，语法如下：
DROP PROCEDURE [user.]Procudure_name;

删除函数可以使用 DROP FUNCTION 命令对不需要的函数进行删除，语法如下：
DROP FUNCTION [user.]Function_name;

第六章 存储函数和过程

引言

创建函数

存储过程

推荐阅读更多精彩内容

第六章存储函数和过程