存储过程

1.简介

存储过程不同于sql语句，它相当于一个function(…)可以有参数、流控制语句（IF，WHILE，LOOP，REPEAT，和CASE语句）、返回值，当然核心还是sql语句。这样的程序存储在数据的数据字典中。

存储过程的典型用途包括数据验证（集成到数据库中）或权限控制机制。此外，存储过程可以整合和集中最初在应用程序中实现的逻辑。为了节省时间和内存，需要执行多个SQL语句的广泛或复杂的处理可以保存到存储过程中，所有应用程序都调用该过程。可以通过从另一个执行一个存储过程来使用嵌套存储过程。

基本语法

各数据库语法会有所不同，下面介绍Oracle和Sql Server。

Oracle

 create or replace procedure 存储过程名（param1 in type，param2 out type）is--定义变量变量 类型（值范围）; --vs_msg VARCHAR2(4000); --定义游标(简单的说就是一个可以遍历的结果集) CURSOR cur_1 IS SELECT ... FROM ...  WHERE ...  GROUP BY ... ; BEGIN --用输入参数给变量赋初值，用到了Oralce的SUBSTR TO_CHAR ADD_MONTHS TO_DATE 等很常用的函数。vs_ym_beg := SUBSTR(is_ym,1,6); vs_ym_end := SUBSTR(is_ym,7,6); vs_ym_sn_beg := TO_CHAR(ADD_MONTHS(TO_DATE(vs_ym_beg,'yyyymm'), -12),'yyyymm'); vs_ym_sn_end := TO_CHAR(ADD_MONTHS(TO_DATE(vs_ym_end,'yyyymm'), -12),'yyyymm'); --先删除表中特定条件的数据。 DELETE FROM 表名 WHERE ym = is_ym; --然后用内置的DBMS_OUTPUT对象的put_line方法打印出影响的记录行数，其中用到一个系统变量SQL%rowcount DBMS_OUTPUT.put_line('del上月记录='||SQL%rowcount||'条'); INSERT INTO表名(area_code,ym,CMCODE,rmb_amt,usd_amt) SELECT area_code,is_ym,CMCODE,SUM(rmb_amt)/10000,SUM(usd_amt)/10000 FROM BGD_AREA_CM_M_BASE_T WHERE ym >= vs_ym_beg AND ym <= vs_ym_end GROUP BY area_code,CMCODE; DBMS_OUTPUT.put_line('ins当月记录='||SQL%rowcount||'条'); --遍历游标处理后更新到表。遍历游标有几种方法，用for语句是其中比较直观的一种。 FOR rec IN cur_1 LOOP UPDATE 表名 SET rmb_amt_sn = rec.rmb_amt_sn,usd_amt_sn = rec.usd_amt_sn WHERE area_code = rec.area_code AND CMCODE = rec.CMCODE AND ym = is_ym; END LOOP; COMMIT; --错误处理部分。OTHERS表示除了声明外的任意错误。SQLERRM是系统内置变量保存了当前错误的详细信息。 EXCEPTION WHEN OTHERS THEN vs_msg := 'ERROR IN xxxxxxxxxxx_p('||is_ym||'):'||SUBSTR(SQLERRM,1,500); ROLLBACK; --把当前错误记录进日志表。 INSERT INTO LOG_INFO(proc_name,error_info,op_date) VALUES('xxxxxxxxxxx_p',vs_msg,SYSDATE); COMMIT; RETURN; END 存储过程名; 

注意事项：

1. 存储过程参数不带取值范围，in表示传入，out表示输出

2. 变量带取值范围，后面接分号

3. 在判断语句前最好先用count（*）函数判断是否存在该条操作记录

4. 用select…into…给变量赋值

5. 在代码中抛异常用 raise+异常名

已命名的异常

命名的系统异常 产生原因 ACCESS_INTO_NULL 未定义对象 CASE_NOT_FOUND CASE 中若未包含相应的 WHEN ，并且没有设置 ELSE 时 COLLECTION_IS_NULL 集合元素未初始化 CURSER_ALREADY_OPEN 游标已经打开 DUP_VAL_ON_INDEX 唯一索引对应的列上有重复的值 INVALID_CURSOR 在不合法的游标上进行操作 INVALID_NUMBER 内嵌的 SQL 语句不能将字符转换为数字 NO_DATA_FOUND 使用 select into 未返回行，或应用索引表未初始化的 TOO_MANY_ROWS 执行 select into 时，结果集超过一行 ZERO_DIVIDE 除数为 0 SUBSCRIPT_BEYOND_COUNT 元素下标超过嵌套表或 VARRAY 的最大值 SUBSCRIPT_OUTSIDE_LIMIT 使用嵌套表或 VARRAY 时，将下标指定为负数 VALUE_ERROR 赋值时，变量长度不足以容纳实际数据 LOGIN_DENIED PL/SQL 应用程序连接到 oracle 数据库时，提供了不 正确的用户名或密码 NOT_LOGGED_ON PL/SQL 应用程序在没有连接 oralce 数据库的情况下 访问数据 PROGRAM_ERROR PL/SQL 内部问题，可能需要重装数据字典＆ pl./SQL 系统包 ROWTYPE_MISMATCH 宿主游标变量与 PL/SQL 游标变量的返回类型不兼容 SELF_IS_NULL 使用对象类型时，在 null 对象上调用对象方法 STORAGE_ERROR 运行 PL/SQL 时，超出内存空间 SYS_INVALID_ID 无效的 ROWID 字符串 TIMEOUT_ON_RESOURCE Oracle 在等待资源时超时

判断语句:

if 比较式 then begin end; end if;

create or replace procedure test_if(v_outlook in varchar2, c out varchar2) isbegin  if v_outlook = 'rain' then    begin      select count(*)        into c        from golfproblemdatarecord g       where g.outlook = v_outlook; --变量赋值    end;  else    Dbms_output.put_line('输入的outlook不对');  end if;end test_if;

For 循环

For … in … LOOP
– 执行语句
end LOOP;
(1) 循环遍历游标

create or replace procedure test_for is  cursor cu is    select outlook from golfproblemdatarecord;begin  for outlook in cu loop    begin      dbms_output.put_line(outlook.outlook);    end;  end loop;end test_for;

(2) 循环遍历数组

create or replace procedure test(varArray in myPackage.TestArray) as --(输入参数varArray 是自定义的数组类型，具体见数组一节) i number; begin i := 1; -- 存储过程数组是起始位置是从1 开始的，与java 、C 、C++ 等语言不同。因为在Oracle 中本是没有数组的概念的，数组其实就是一张-- 表(Table), 每个数组元素就是表中的一个记录，所以遍历数组时就相当于从表中的第一条记录开始遍历 for i in 1..varArray.count LOOP dbms_output.putline('The No.'|| i || 'record in varArray is:'||varArray(i)); end LOOP; end test; 

While 循环

while 条件语句 LOOP

begin end; end LOOP; E.g create or replace procedure test(i in number) as begin while i < 10 LOOP begin i:= i + 1; end; end LOOP; end test; 

数组

首先明确一个概念：Oracle 中本是没有数组的概念的，数组其实就是一张表(Table), 每个数组元素就是表中的一个记录。

使用数组时，用户可以使用Oracle 已经定义好的数组类型，或可根据自己的需要定义数组类型。

(1) 使用Oracle 自带的数组类型

x array; – 使用时需要需要进行初始化

e.g:

create or replace procedure test(y out array) is x array; begin x := new array(); y := x; end test; 

(2) 自定义的数组类型 ( 自定义数据类型时，建议通过创建Package 的方式实现，以便于管理)

create or replace package myPackage is Public type declarations type info is record( name varchar(20), y number); type TestArray is table of info index by binary_integer;-- 此处声明了一个TestArray 的类型数据，其实其为一张存储Info 数据类型的Table 而已，及TestArray 就是一张表，有两个字段，一个是name ，一个是y 。需要注意的是此处使用了Index by binary_integer 编制该Table 的索引项，也可以不写，直接写成：type TestArray is table of info ，如果不写的话使用数组时就需要进行初始化：varArraymyPackage.TestArray; varArray := new myPackage.TestArray(); end TestArray; 

游标的使用

Oracle 中Cursor 是非常有用的，用于遍历临时表中的查询结果。其相关方法和属性也很多，现仅就常用的用法做一二介绍：

(1)Cursor 型游标( 不能用于参数传递)

create or replace procedure test() is cusor_1 Cursor is select std_name from student where ...;--Cursor 的使用方式1 cursor_2 Cursor; begin select class_name into cursor_2 from class where ...; --Cursor 的使用方式2 可使用For x in cursor LOOP .... end LOOP; 来实现对Cursor 的遍历 end test;

(2)SYS_REFCURSOR 型游标，该游标是Oracle 以预先定义的游标，可用于参数进行传递

create or replace procedure test_SYS_REFCURSOR(rsCursor out SYS_REFCURSOR) is  l_cur   SYS_REFCURSOR;  l_empno VARCHAR2(20);begin  OPEN l_cur FOR    select outlook from golfproblemdatarecord;  loop    fetch l_cur      into l_empno;    exit when l_cur%notfound;    dbms_output.put_line(l_empno);  end loop;  rsCursor := l_cur;end test_SYS_REFCURSOR;

Sql Server

--------------创建存储过程-----------------CREATE PROC [ EDURE ] procedure_name [ ; number ]    [ { @parameter data_type }        [ VARYING ] [ = default ] [ OUTPUT ]    ] [ ,...n ][ WITH    { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ][ FOR REPLICATION ]AS sql_statement [ ...n ]--------------调用存储过程-----------------EXECUTE Procedure_name '' --存储过程如果有参数，后面加参数格式为：@参数名=value，也可直接为参数值value--------------删除存储过程-----------------drop procedure procedure_name    --在存储过程中能调用另外一个存储过程，而不能删除另外一个存储过程

创建存储过程的参数

1.procedure_name ：存储过程的名称，在前面加#为局部临时存储过程，加##为全局临时存储过程。

2.; number：是可选的整数，用来对同名的过程分组，以便用一条 DROP PROCEDURE 语句即可将同组的过程一起除去。例如，名为 orders 的应用程序使用的过程可以命名为 orderproc;1、orderproc;2 等。DROP PROCEDURE orderproc 语句将除去整个组。如果名称中包含定界标识符，则数字不应包含在标识符中，只应在 procedure_name 前后使用适当的定界符。

3.@parameter： 存储过程的参数。可以有一个或多个。用户必须在执行过程时提供每个所声明参数的值（除非定义了该参数的默认值）。存储过程最多可以有 2.100 个参数。
使用 @ 符号作为第一个字符来指定参数名称。参数名称必须符合标识符的规则。每个过程的参数仅用于该过程本身；相同的参数名称可以用在其它过程中。默认情况下，参数只能代替常量，而不能用于代替表名、列名或其它数据库对象的名称。有关更多信息，请参见 EXECUTE。

4.data_type：参数的数据类型。所有数据类型（包括 text、ntext 和 image）均可以用作存储过程的参数。不过，cursor 数据类型只能用于 OUTPUT 参数。如果指定的数据类型为 cursor，也必须同时指定 VARYING 和 OUTPUT 关键字。有关 SQL Server 提供的数据类型及其语法的更多信息，请参见数据类型。
说明 对于可以是 cursor 数据类型的输出参数，没有最大数目的限制。

5.VARYING： 指定作为输出参数支持的结果集（由存储过程动态构造，内容可以变化）。仅适用于游标参数。

6.default： 参数的默认值。如果定义了默认值，不必指定该参数的值即可执行过程。默认值必须是常量或 NULL。如果过程将对该参数使用 LIKE 关键字，那么默认值中可以包含通配符（%、_、[] 和 [^]）。

7.OUTPUT ：表明参数是返回参数。该选项的值可以返回给 EXEC[UTE]。使用 OUTPUT 参数可将信息返回给调用过程。Text、ntext 和 image 参数可用作 OUTPUT 参数。使用 OUTPUT 关键字的输出参数可以是游标占位符。

8.RECOMPILE: 表明 SQL Server 不会缓存该过程的计划，该过程将在运行时重新编译。在使用非典型值或临时值而不希望覆盖缓存在内存中的执行计划时，请使用 RECOMPILE 选项。

9.ENCRYPTION: 表示 SQL Server 加密 syscomments 表中包含 CREATE PROCEDURE 语句文本的条目。使用 ENCRYPTION 可防止将过程作为 SQL Server 复制的一部分发布。 说明 在升级过程中，SQL Server 利用存储在 syscomments 中的加密注释来重新创建加密过程。

10.FOR REPLICATION :指定不能在订阅服务器上执行为复制创建的存储过程。.使用 FOR REPLICATION 选项创建的存储过程可用作存储过程筛选，且只能在复制过程中执行。本选项不能和 WITH RECOMPILE 选项一起使用。

11.AS :指定过程要执行的操作。

12.sql_statement :过程中要包含的任意数目和类型的 Transact-SQL 语句。但有一些限制。

小结：看过这些基本语法后，下面我就根据语法创建各式的存储过程。

UserID UserName PassWord RegisterTime RegisterIP 12 6 6 2012-12-31 4 18 5 5 2013-12-31 4 19 1 1 2014-12-31 2

针对上面的表，我使用存储过程对它做一些操作：

简单的增删查改

create Procedure SimpleCRUDUserAccountasselect * from UserAccount/insert into UserAccount (UserName,[PassWord],RegisterTime,RegisterIP) values(9,9,'2013-01-02',9)/......go-------------执行上面的存储过程----------------exec GetUserAccount

有返回值的存储过程

-------------创建名为GetUserAccount的存储过程----------------create Procedure inUserAccountReasinsert into UserAccount (UserName,[PassWord],RegisterTime,RegisterIP) values(10,10,'2013-01-02',10)return @@rowcountgo-------------执行上面的存储过程----------------exec inUserAccountRe

解释：这里的@@rowcount为执行存储过程影响的行数，执行的结果是不仅插入了一条数据，还返回了一个值即 return value =1 ，这个可以在程序中获取，稍后在c#调用存储过程中会有说到。

有输入参数和输出参数的存储过程

————-创建名为GetUserAccount的存储过程—————-

create Procedure GetUserAccountRe@UserName nchar(20),@UserID int outputasif(@UserName>5)select @UserID=COUNT(*) from UserAccount where UserID>25elseset @UserID=1000go-------------执行上面的存储过程----------------exec GetUserAccountRe '7',null

解释：@UserName为输入参数，@UserID为输出参数。 运行结果为@userID为COOUT（*）即 =1。

同时具有返回值、输入参数、输出参数的存储过程

————-创建名为GetUserAccount的存储过程—————-

create Procedure GetUserAccountRe1@UserName nchar(20),@UserID int outputasif(@UserName>5)select @UserID=COUNT(*) from UserAccount where UserID>25elseset @UserID=1000return @@rowcountgo-------------执行上面的存储过程----------------exec GetUserAccountRe1 '7',null

结果：@userID为COOUT（*）即 =1，Retun Value=1。

同时返回参数和记录集的存储过程

-------------创建名为GetUserAccount的存储过程----------------create Procedure GetUserAccountRe2@UserName nchar(20),@UserID int outputasif(@UserName>5)select @UserID=COUNT(*) from UserAccount where UserID>25elseset @UserID=1000select * from UserAccountreturn @@rowcountgo-------------执行上面的存储过程----------------exec GetUserAccountRe2 '7',null

结果：返回执行 select * from UserAccount 这句代码的结果集，同时@userID为COOUT（*）即 =1，Retun Value=9。

返回多个记录集的存储过程

-------------创建名为GetUserAccount的存储过程----------------create Procedure GetUserAccountRe3asselect * from UserAccountselect * from UserAccount where UserID>5go-------------执行上面的存储过程----------------exec GetUserAccountRe3

结果：返回两个结果集，一个为 select * from UserAccount，另一个为 select * from UserAccount where UserID>5 。