PL/SQL编程 - 存储过程 - 《Oracle》

6.3 存储过程
6.3.1 创建过程
建立存储过程
在 ORACLE SERVER上建立存储过程,可以被多个应用程序调用,可以向存储过程传递参数,也可以向存储过程传回参数.
创建过程语法:

 CREATE [OR REPLACE] PROCEDURE procedure_name
 ([arg1 [ IN | OUT | IN OUT ]] type1 [DEFAULT value1],
 [arg2 [ IN | OUT | IN OUT ]] type2 [DEFAULT value1]],
 ......
 [argn [ IN | OUT | IN OUT ]] typen [DEFAULT valuen])
 [ AUTHID DEFINER | CURRENT_USER ]
 { IS | AS }
--<声明部分>
 BEGIN
--<执行部分>
 EXCEPTION
-- <可选的异常错误处理程序>
 END procedure_name;

相关参数说明参见函数的语法说明。

--流水号过程
create or replace procedure max_bh(sasettleid in number) as 
v_old_num    NUMBER;--上一条记录中的编号数
  v_new_num    VARCHAR2(10);--新记录的编号数
  v_old_no     VARCHAR2(50);--原流水号
  v_maked_no   VARCHAR2(50);--新流水号
  v_date_no    VARCHAR2(20);--当前日期
  v_sql        VARCHAR2(4000);
  v_number_col VARCHAR2(20);--记录最大编号字段
  v_table_name VARCHAR2(20);--记录最大编号的表

begin 
v_number_col：='nzdbh';
v_table_name:='zddjbh';

v_sql:='select max(nzdbh) from' || v_table_name;
execute imeediate v_sql into v_old_no;

v_sql:='select substr(to_char(sysdate,''YYYYMMDD''),1,8) from dual';
execute immediate v_sql into v_date_no;

v_old_num:=to_number(substr(v_date_no,9,4));
v_new_num:=to_char(v_old_num+1);

while(length(v_new_num)<4) loop
v_new_num:='0'||v_new_num;
end loop;

if v_old_no is null or substr(v_old_no,1,8)<>v_date_no
then v_maked_no:=v_date_no||'0001';
else v_maked_no:=v_date_no||v_new_num;
end if;

insert into ZDDJBH (NZDBH,sasettleid) values (v_maked_no,V_SASETTLEID);

end;

6.3.2 调用存储过程
存储过程建立完成后，只要通过授权，用户就可以在SQLPLUS 、ORACLE开发工具或第三方开发工具中来调用运行。对于参数的传递也有三种：按位置传递、按名称传递和组合传递，传递方法与函数的一样。ORACLE 使用EXECUTE 语句来实现对存储过程的调用：
EXEC[UTE] procedure_name( parameter1, parameter2…);
例10：
EXECUTE logexecution;
例12．计算指定部门的工资总和，并统计其中的职工数量。

 CREATE OR REPLACE PROCEDURE proc_demo (dept_no NUMBER DEFAULT 10,sal_sum OUT NUMBER,emp_count OUT NUMBER)
 IS
 BEGIN
 SELECT SUM(salary), COUNT(*) INTO sal_sum, emp_count
 FROM employees WHERE department_id = dept_no;
 EXCEPTION
 WHEN NO_DATA_FOUND THEN
       DBMS_OUTPUT.PUT_LINE('温馨提示:你需要的数据不存在!');
 WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
 END proc_demo;
  DECLARE
 V_num NUMBER;
 V_sum NUMBER(8, 2);
 BEGIN
   Proc_demo(30, v_sum, v_num);
 DBMS_OUTPUT.PUT_LINE('温馨提示:30号部门工资总和：'||v_sum||',人数：'||v_num);
   Proc_demo(sal_sum => v_sum, emp_count => v_num);
 DBMS_OUTPUT.PUT_LINE('温馨提示:10号部门工资总和：'||v_sum||',人数：'||v_num);
 END;

在PL/SQL 程序中还可以在块内建立本地函数和过程，这些函数和过程不存储在数据库中，但可以在创建它们的PL/SQL 程序中被重复调用。本地函数和过程在PL/SQL 块的声明部分定义，它们的语法格式与存储函数和过程相同，但不能使用CREATE OR REPLACE 关键字。<br />    ORACLE8i 可以支持事务处理中的事务处理的概念．这种子事务处理可以完成它自己的工作，独立于父事务处理进行提交或者回滚．通过使用这种方法，开发者就能够这样的过程，无论父事务处理是提交还是回滚，它都可以成功执行．<br />6.3.7 过程与函数的比较<br />使用过程与函数具有如下优点：<br />1、共同使用的代码可以只需要被编写和测试一次，而被需要该代码的任何应用程序（如：.NET、C++、JAVA、VB程序，也可以是DLL库）调用。<br />2、这种集中编写、集中维护更新、大家共享（或重用）的方法，简化了应用程序的开发和维护，提高了效率与性能。<br />3、这种模块化的方法，使得可以将一个复杂的问题、大的程序逐步简化成几个简单的、小的程序部分，进行分别编写、调试。因此使程序的结构清晰、简单，也容易实现。<br />4、可以在各个开发者之间提供处理数据、控制流程、提示信息等方面的一致性。<br />5、节省内存空间。它们以一种压缩的形式被存储在外存中，当被调用时才被放入内存进行处理。并且，如果多个用户要执行相同的过程或函数时，就只需要在内存中加载一个该过程或函数。<br />6、提高数据的安全性与完整性。通过把一些对数据的操作放到过程或函数中，就可以通过是否授予用户有执行该过程或的权限，来限制某些用户对数据进行这些操作。<br />过程与函数的相同功能有：<br />1、 都使用IN模式的参数传入数据、OUT模式的参数返回数据。<br />2、 输入参数都可以接受默认值，都可以传值或传引导。<br />3、 调用时的实际参数都可以使用位置表示法、名称表示法或组合方法。<br />4、 都有声明部分、执行部分和异常处理部分。<br />5、 其管理过程都有创建、编译、授权、删除、显示依赖关系等。<br />使用过程与函数的原则：<br />1、如果需要返回多个值和不返回值，就使用过程；如果只需要返回一个值，就使用函数。<br />2、过程一般用于执行一个指定的动作，函数一般用于计算和返回一个值。<br />3、可以SQL语句内部（如表达式）调用函数来完成复杂的计算问题，但不能调用过程。所以这是函数的特色。<br />注：Oracle 的存储过程里没有declare的语法，触发器和plsql块才是declare，如果要定义变量，后面直接跟is的。