oracle ——存储过程

存储过程是指用于特定操作的PL/SQL块，是由流控制和SQL语句书写的过程。

存储过程经编译和SQL优化后存储在数据库服务器中，使用时只要调用即可。在Oracle数据库中，若干个有联系的存储过程，可以组合在一起构成包。

存储过程具有如下特点：

1）存储过程是预编译过的，并且经优化后存储于SQL内存中，使用时无需再次编译，提高了工作效率；

2）存储过程的代码直接存放于数据库中，一般由客户端直接通过存储过程的名字进行调用，减少了网络流量，加快了系统执行速度，例如在进行百万以上的大批量数据查询时，使用存储过程分页要比其他方式分页快得多；

3）使用存储过程可以减少SQL注入式攻击，提高了系统的安全性，执行存储过程的用户要具有一定的权限才能使用存储过程，没有数据操作权限的用户只能在其控制下间接地存取数据；

4）在同时进行主、从表及多表间的数据维护及有效性验证时，使用存储过程比较方便，而且可以有效利用SQL中的事务处理的机制；

5）使用存储过程，可以实现存储过程设计和编码工作分开进行，只要将存储过程名、参数、及返回信息告诉编码人员即可；

6）但使用存储过程封装业务逻辑将限制应用程序的可移植性；另外，如果更改存储过程的参数或者其返回的数据及类型的话，需要修改应用程序的相关代码，比较繁琐。

存储过程的定义：

CREATE [ORREPLACE] PROCEDURE procedure_name
[(parameter1 [model] datatype1,parameter2 [model] datatype2 ...)]
IS [AS]
BEGIN
      PL/SQL Block;
END [procedure_name];

其中：procedure_name是存储过程的名字，parameter用于指定参数，model用于指定参数模式，datatype用于指定参数类型， IS [AS]用于开始PL/SQL代码块。

注：当定义存储过程的参数时，只能指定数据类型，不能指定数据长度

1）建立存储过程时，既可以指定存储过程的参数，也可以不提供任何参数。

2）存储过程的参数主要有三种类型：输入参数(IN)、输出参数(OUT)、输入输出参数(IN OUT)，其中IN用于接收调用环境的输入参数，OUT用于将输出数据传递到调用环境，IN OUT不仅要接收数据，而且要输出数据到调用环境。

3）在建立存储过程时，输入参数的IN可以省略。

CREATE OR REPLACE PROCEDURE USP_OutTime

IS

BEGIN

  DBMS_OUTPUT.PUT_LINE(SYSDATE);

END USP_OutTime;

CREATE OR REPLACE PROCEDURE USP_Learing

(

  p_para1  varchar2 := '参数一',

  p_para2  nvarchar2 default '参数二',

  p_para3      out  varchar2,

  p_para4      in out   varchar2

)

IS

BEGIN

DECLARE

  v_para5  varchar2(20);

BEGIN

  v_para5 := '输入输出：'||p_para4;

  p_para3 := '输出：'||p_para1||p_para2;

  p_para4 := v_para5;

END;

END USP_Learing;

存储过程的维护：

1）删除存储过程

DROPPROCEDURE procedure_name;

2）编译存储过程

ALTER PROCEDURE procedure_name COMPILE;

3）与存储过程相关的几个查询

--查看无效的存储过程

SELECT     object_name

FROM      USER_OBJECTS

WHERE     STATUS='INVALID'

AND     OBJECT_TYPE='PROCEDURE'

--查看存储过程的代码

SELECT   TEXT

FROM       USER_SOURCE

WHERE    NAME= procedure_name

其中：procedure_name是存储过程的名字

当在SQL*PLUS中调用存储过程时，需要使用CALL或EXECUTE命令，而在PL/SQL块中可以直接引用。当调用存储过程时，如果无参数，那么直接引用存储过程名；如果存储过程带有输入参数，那么需要为输入参数提供数据值；如果存储过程带有输出参数，那么需要使用变量接收输出结果；如果存储过程带有输入输出参数，那么在调用时需要使用具有输入值的变量。

当为参数传递变量或者数据时，可以采用位置传递、名称传递和组合传递三种方法。

调用无参存储过程

  EXECUSP_OutTime;

调用带有输入输出参数的存储过程

declare
  v_para1  varchar2(10);
  v_para2  nvarchar2(10);
  v_para3                                       varchar2(30);
  v_para4                                       varchar2(30);

begin
  -- Call the procedure
  v_para1 := '123';
  v_para2 := '456';
  v_para4 := '789';
  -- 位置传递
  USP_Learing(v_para1,v_para2,v_para3,v_para4);
  -- 值传递
  USP_Learing(p_para1=>v_para1,p_para2=>v_para2,p_para3=>v_para3,p_para4=>v_para4);
  -- 组合传递
  USP_Learing(v_para1,v_para2,p_para3=>v_para3,p_para4=>v_para4);
  dbms_output.put_line(v_para3);
  dbms_output.put_line(v_para4);

end;

PL/SQL记录(RECORD)，单行多列

PL/SQL 表(TABLE)，多行多列

PL/SQL嵌套表(TABLE)，多行多列

变长数组(VARRY)，多行单列

Common Table Expression（CTE）

PL/SQL记录(record)主要用于处理单行多列数据。当使用RECORD时，既可以自定义记录的类型和变量，也可以使用%ROWTYPE属性定义记录变量。

自定义记录变量

        TYPE type_name IS RECORD
(
   field_declaration,...
);
identifier type_name;

使用%ROWTYPE属性定义记录变量

identifier [table_name |view_name]%ROWTYPE;

type_name用于指定记录类型的名称；field_declaration用于定义记录成员； identifier用于指定记录变量的名称；table_name用于指定表名；view_name用于指定视图名。

declare
type type_dz_record is record
(
 v_xhrx_dz_nc.xh%type,--序号
 v_dzrx_dz_nc.dz%type,--地址串
 v_xsbj char(1)       --虚实标记
);
dz_recordtype_dz_record;
begin
  select xh,dz,xsbj
  into dz_record
  from rx_dz_nc
  where xh = &xh;
  dbms_output.put_line(dz_record.v_xh); 
  dbms_output.put_line(dz_record.v_dz);
end;

declare
  dz_recordrx_dz_nc%rowtype;
begin
  select xh,dz,xsbj
  into dz_record
  from rx_dz_nc
  where xh = &xh;
  dbms_output.put_line(dz_record.XH); 
  dbms_output.put_line(dz_record.DZ);
end;

PL/SQL 表是Oracle早期版本用于处理PL/SQL集合的数据类型，表的下标可以为负值，并且元素个数无限制，不可以作为表列的数据类型使用。

TYPE type_name IS TABLEOFelement_type
[NOT NULL] INDEX BY key_type;
identifier type_name;

 type_name用于指定表类型的名称；element_type用于指定表的数据类型；NOT NULL表示不允许引用NULL元素；key_type用于指定表下标的数据类型(BINARY_INTEGER、PLS_INTEGER或VARCHAR2); identifier用于定义表变量的名称。

declare     
type dz_table_type is table ofrx_dz_nc%rowtype
        index by binary_integer;
dz_tabledz_table_type;
begin
     select xh,dz,xsbj bulk collect intodz_table
     from rx_dz_nc;
    dbms_output.put_line('地址：'||dz_table(1).dz);
end;

从Oracle DataTabse9i开始，允许使用varchar2定义表的下标。当使用varchar2定义下标时，会按照下标值的升序方式确定元素顺序。

declare type dz_table_type is tableof nvarchar2(30)
        index by varchar2(20);
dz_tabledz_table_type;
begin
     dz_table('张三'):=1;
     dz_table('李四'):=2;
     dz_table('王五'):=3;
     dz_table('赵六'):=4;
     dbms_output.put_line('第一个元素：'||dz_table.first);
     dbms_output.put_line('王五的前一个元素：'||dz_table.prior('王五'));  
     dbms_output.put_line('李四的后一个元素：'||dz_table.next('李四'));
     dbms_output.put_line('最后一个元素：'||dz_table.last);
end;

PL/SQL 嵌套表用于处理PL/SQL集合的数据类型，表的下标以1开始，并且元素个数无限制，可以作为表列的数据类型使用。

TYPE type_name IS TABLEOFelement_type;
identifier type_name;

 type_name用于指定嵌套表类型的名称；element_type用于指定嵌套表的数据类型；identifier用于定义嵌套表变量的名称。

使用嵌套表时，需要使用其构造方法初始化嵌套表变量。

declare     
type dz_table_type is table ofrx_dz_nc%rowtype;
dz_tabledz_table_type;
begin
     select xh,dz,xsbj bulk collect intodz_table
     from rx_dz_nc;
    dbms_output.put_line('地址：'||dz_table(1).dz);
end;

PL/SQL 嵌套表用于处理PL/SQL集合的数据类型，表的下标以1开始，并且元素个数无限制，可以作为表列的数据类型使用。

TYPE type_name IS TABLEOFelement_type;
identifier type_name;

 type_name用于指定嵌套表类型的名称；element_type用于指定嵌套表的数据类型；identifier用于定义嵌套表变量的名称。

使用嵌套表时，需要使用其构造方法初始化嵌套表变量。

declare     
type dz_table_type is table ofrx_dz_nc%rowtype;
dz_tabledz_table_type;
begin
     select xh,dz,xsbj bulk collect intodz_table
     from rx_dz_nc;
    dbms_output.put_line('地址：'||dz_table(1).dz);
end;

declare type dz_array_type isvarray(20) ofrx_dz_nc.dz%type;
dz_arraydz_array_type:=dz_array_type('123','12321');
begin
     select dz intodz_array(1)
     from rx_dz_nc
     where xh=&xh;
     dbms_output.put_line('地址1：'||dz_array(1));
     dbms_output.put_line('地址2：'||dz_array(2));
end;

declare type dz_array_type isvarray(20) ofrx_dz_nc.dz%type;
dz_arraydz_array_type;
begin
     select dz bulk collect intodz_array
     from rx_dz_nc
     where rownum<=20;
     dbms_output.put_line('地址1：'||dz_array(1));
     dbms_output.put_line('地址2：'||dz_array(2));
end;

Common Table Expression（CTE）兼具视图（view）和派生数据表（derivedtable）的能力，可以称为临时的视图，或是在同一批子查询语法中可重复使用的派生数据表。

WITH <CTE 名称>
AS
(
      <SQL 查询语句>
)
SELECT <列名称列表> FROM <CTE名称>;

举例：WITH tempDZ
AS
(
 SELECT XH,DZ
 FROM RX_DZ_NC
)
SELECT XH,DZ FROM tempDZ;

为了提高存储过程的健壮性，避免运行错误，当建立存储过程时应包含异常处理部分。

异常(EXCEPTION)是一种PL/SQL标识符，包括预定义异常、非预定义异常和自定义异常；

预定义异常是指由PL/SQL提供的系统异常；非预定义异常用于处理与预定义异常无关的Oracle错误(如完整性约束等)；自定义异常用于处理与Oracle错误的其他异常情况。

RAISE_APPLICATION_ERROR用于自定义错误消息，并且消息号必须在－20000～－20999之间。

CREATE OR REPLACE PROCEDURE USP_Exception
(
 p_pcid       integer,      --批次ID
 p_fm        number,  --分母
 p_fz        number,  --分子
 p_result out number  --结果
)
IS
  v_raise EXCEPTION;   --异常处理
  type type_table_pcmx is table oft_bl_pcmx%rowtype;
  table_pcmxtype_table_pcmx;
BEGIN
  if p_fz = 0 then
     RAISE v_raise;
  end if;
  p_result := p_fm/p_fz;
  selectID,PCID,XMID,ZJXMID,BZ,CZSJ,CJSJ
  bulk collect into table_pcmx
  from t_bl_pcmx
  where pcid = p_pcid;
  EXCEPTION
  WHEN v_raise THEN
      RAISE_APPLICATION_ERROR(-20010,'ERROR：分子为零！');
  WHEN NO_DATA_FOUND THEN
      RAISE_APPLICATION_ERROR(-20011,'ERROR：批次明细不存在！');
  WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR(-20012,'ERROR：数据错误！');
END;

事务用于确保数据的一致性，由一组相关的DML语句组成，该组DML语句所执行的操作要么全部确认，要么全部取消。

当执行事务操作(DML)时，Oracle会在被作用的表上加锁，以防止其他用户改变表结构，同时也会在被作用的行上加行锁，以防止其他事务在相应行上执行DML操作。

当执行事务提交或事务回滚时，Oracle会确认事务变化或回滚事务、结束事务、删除保存点、释放锁。

提交事务（COMMIT）确认事务变化，结束当前事务、删除保存点，释放锁，使得当前事务中所有未决的数据永久改变。

保存点（SAVEPOINT）在当前事务中，标记事务的保存点。

回滚事务（ROLLBACK）回滚整个事务，删除该事务所定义的所有保存点，释放锁，丢弃所有未决的数据改变。

回滚事务到指定的保存点（ROLLBACKTO SAVEPOINT）回滚当前事务到指定的保存点，丢弃该保存点创建后的任何改变，释放锁。

当执行DDL、DCL语句，或退出SQL*PLUS时，会自动提交事务；

事务期间应避免与使用者互动；

查询数据期间，尽量不要启动事务；

尽可能让事务持续地越短越好；

在事务中尽可能存取最少的数据量。

create or replace procedure usp_shiwu
is
begin
    INSERT INTO table_test
   VALUES(1,'2009042201','2009042201');
    COMMIT;
    SAVEPOINTsavepoint1;
    INSERT INTO table_test
   VALUES(2,'2009042201','2009042202');
    DBMS_TRANSACTION.savepoint('savepoint2');
    UPDATE table_test
    SET vCode ='2009042202'
    WHERE iID=2;
    COMMIT;
    EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
         ROLLBACK TO SAVEPOINTsavepoint1;

                 --DBMS_TRANSACTION.rollback_savepoint(savepoint1);
        RAISE_APPLICATION_ERROR(-20010,'ERROR：违反唯一索引约束！');
    WHEN OTHERS THEN
         ROLLBACK;

                     --DBMS_TRANSACTION.rollback;
end usp_shiwu;

SQL语句的优化

索引的优化

游标的优化

SELECT语句的执行顺序                      (8)SELECT  (9) [DISTINCT]
(11) 传回结果列表[INTO 新数据表名称]
(1) FROM 数据表
(3) [INNER | LFT | RIGHT]JOIN 数据表
(2) ON <数据表JOIN的条件>
(4) [WHERE <过滤条件>]
(5) [GROUP BY <群组语法>]
(6) [WITH {CUBE | ROLLUP}
(7) [HAVING <过滤条件>]
(10) ORDER BY <排序列表> [ASC | DESC]]

当使用SELECT子句查询数据时，应尽量避免使用万用字符(*)，传回所有数据行。尽量利用WHERE子句进一步限制查询结果，以确保所得的数据是有用的数据，降低传送过多数据所造成的负荷；

尽量避免反复访问同一张或几张表，尤其是数据量较大的表，可以考虑先根据条件提取数据到临时表中，然后再做连接；

尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该改写；如果使用了游标，就要尽量避免在游标循环中再进行表连接的操作；

注意where字句写法，必须考虑语句顺序，应该根据索引顺序、范围大小来确定条件子句的前后顺序，尽可能的让字段顺序与索引顺序相一致，范围从大到小；

不要在where子句的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引；

注意存储过程中参数和数据类型的关系，并注意表之间连接的数据类型，避免不同数据类型之间的连接；

尽可能的使用索引字段作为查询条件，尤其是聚簇索引。

: 存储过程与函数的对比

1.6.2 1.6.2 1.6.2 1.6.2 存储过程使用示例 存储过程使用示例
1.6.2. 1. 存储过程格式 存储过程格式 存储过程格式 存储过程格式
/* Formatted on 2011/1/17 13:20:44 (QP5 v5.115.810.9015) */
CREATE OR REPLACE procedure proc_trade(
v_tradeid in number, --交易id
v_third_ip in varchar2, --第三方ip
v_third_time in date , --第三方完成时间
v_thire_state in number , --第三方状态
o_result out number, --返回值
o_detail out varchar2 --详细描述
)
as
-- 定义变量
v_error varchar2(500);
begin
--对变量赋值
o_result:=0;
o_detail:='验证失败';
--业务逻辑处理
if v_tradeid >100 then
insert into table_name(...) values(...);
commit;
elsif v_tradeid < 100 and v_tradeid>50 then
insert into table_name(...) values(...);
commit;
else
goto log;
end if;
--跳转标志符，名称自己指定
<<log>>
o_result:=1;
--捕获异常
exception
when no_data_found
then
result := 2;
when dup_val_on_index
then
result := 3;
when others
then
result := -1;
end proc_trade;
在上面这个存储过程中使用了输入参数，并返回出里的类型 在上面这个存储过程中使用了输入参数，并返回出里的类型 在上面这个存储过程中使用了输入参数，并返回出里的类型 是我们自己指定的。 是我们自己指定的。 是我们自己指定的。 是我们自己指定的。 是我们自己指定的。 是我们自己指定的。 是我们自己指定的。 是我们自己指定的。 是我们自己指定的。 这种写法可行，但是最好使用 这种写法可行，但是最好使用 这种写法可行，但是最好使用 这种写法可行，但是最好使用 这种写法可行，但是最好使用 这种写法可行，但是最好使用 这种写法可行，但是最好使用 这种写法可行，但是最好使用 这种写法可行，但是最好使用 这种写法可行，但是最好使用 这种写法可行，但是最好使用 这种写法可行，但是最好使用 这种写法可行，但是最好使用 %type %type %type %type %type 来获取参数的类型 来获取参数的类型 来获取参数的类型 来获取参数的类型 来获取参数的类型 来获取参数的类型 来获取参数的类型 来获取参数的类型 (table_name (table_name (table_name .column_namecolumn_name column_name column_name %TYPE%TYPE )。 这样就不会出现参数类型的错误。 这样就不会出现参数类型的错误。
如：
CREATE OR REPLACE PROCEDURE spdispsms (
aempid IN otherinfo.empid%TYPE,
amsg IN otherinfo.msg%TYPE,
abillno IN otherinfo.billno%TYPE,
ainfotype IN otherinfo.infotype%TYPE,
aopid IN otherinfo.OPERATOR%TYPE,
ainfoid OUT otherinfo.infoid%TYPE,
RESULT OUT INTEGER
)
1.6.2. 2. 存储过程中的循环 存储过程中的循环 存储过程中的循环 存储过程中的循环 存储过程中的循环
存储过程写的是业务逻辑，循环常用处理方法之一。
1.6.2. 2.1 for ... in loop 2.1 for ... in loop 2.1 for ... in loop 2.1 for ... in loop 2.1 for ... in loop 2.1 for ... in loop 2.1 for ... in loop 循环
1：循环遍历游标
示例 1：
CREATE OR REPLACE PROCEDURE proc_test
AS
CURSOR c1
IS
SELECT * FROM dat_trade;
BEGIN
FOR x IN c1
LOOP
DBMS_OUTPUT.put_line (x.id);
END LOOP;
END proc_test;
示例 2：
CREATE OR REPLACE PROCEDURE proc_test
AS
BEGIN
FOR x IN (SELECT power_id FROM sys_power)
LOOP
DBMS_OUTPUT.put_line (x.power_id);
END LOOP;
END proc_test;
2：根据数值进行循环
示例 1：
CREATE OR REPLACE PROCEDURE proc_test (v_num IN NUMBER)
AS
BEGIN
for x in 1..100 loop
dbms_output.put_line(x);
end loop;
END proc_test;
示例 2：在过程里指定输入参数 v_num. 在调用过程时指定循环次数。
CREATE OR REPLACE PROCEDURE proc_test (v_num IN NUMBER)
AS
BEGIN
FOR x IN 1 .. v_num
LOOP
DBMS_OUTPUT.put_line (x);
END LOOP;
END proc_test;
1.6.2. 2.2 loop 2.2 loop 2.2 loop 2.2 loop 循环
LOOP
DELETE FROM orders
WHERE senddate < TO_CHAR (ADD_MONTHS (SYSDATE, -3),
'yyyy-mm-dd')
AND ROWNUM < 1000;
EXIT WHEN SQL%ROWCOUNT < 1;
COMMIT;
END LOOP;
这里的 SQL%ROWCOUNTSQL%ROWCOUNTSQL%ROWCOUNTSQL%ROWCOUNTSQL%ROWCOUNTSQL%ROWCOUNT SQL%ROWCOUNTSQL%ROWCOUNT SQL%ROWCOUNT 是隐士游标。 是隐士游标。 是隐士游标。 是隐士游标。 是隐士游标。 是隐士游标。 除了这个，还有其他几 除了这个，还有其他几 除了这个，还有其他几 除了这个，还有其他几 除了这个，还有其他几 除了这个，还有其他几 除了这个，还有其他几 除了这个，还有其他几 除了这个，还有其他几 除了这个，还有其他几 个： %found%found ，%notfound%notfound %notfound， %isopen%isopen 。
1.6.2. 2.3 while 2.3 while 2.3 while 2.3 while 2.3 while 2.3 while 2.3 while 循环
CREATE OR REPLACE PROCEDURE proc_test (v_num IN NUMBER)
AS
i NUMBER := 1;
BEGIN
WHILE i < v_num
LOOP
BEGIN
i := i + 1;
DBMS_OUTPUT.put_line (i);
END;
END LOOP;
END proc_test;
1.6.2. 3. 存储过程中的判断 存储过程中的判断 存储过程中的判断 存储过程中的判断 存储过程中的判断
判断也是存储过程中最常用的方法之一。
1.6.2. 3.1 if ... elsif else 3.1 if ... elsif else 3.1 if ... elsif else 3.1 if ... elsif else 3.1 if ... elsif else 3.1 if ... elsif else 3.1 if ... elsif else 3.1 if ... elsif else 3.1 if ... elsif else 3.1 if ... elsif else 判断
CREATE OR REPLACE PROCEDURE proc_test (v_num IN NUMBER)
AS
BEGIN
IF v_num < 10
THEN
DBMS_OUTPUT.put_line (v_num);
ELSIF v_num > 10 AND v_num < 50
THEN
DBMS_OUTPUT.put_line (v_num - 10);
ELSE
DBMS_OUTPUT.put_line (v_num - 50);
END IF;
END proc_test;
1.6.2. 3.2 case ... when end case ... when end case ... when end case ... when end case ... when end case ... when end case ... when end case ... when end case ... when end case ... when end case case 判断
CREATE OR REPLACE PROCEDURE proc_test (v_num IN NUMBER)
AS
BEGIN
case v_num
when 1 then
DBMS_OUTPUT.put_line (v_num);
when 2 then
DBMS_OUTPUT.put_line (v_num);
when 3 then
DBMS_OUTPUT.put_line (v_num);
else null;
end case;
END proc_test;
1.6.2. 4. 游标
存储过程中使用游标也是很常见的。 这里的游标分两种：
1.6.2. 4.1 4.1 Cursor 型游标 (不能用于参数传递 不能用于参数传递 不能用于参数传递 不能用于参数传递 )
这种方法具体参考 1.6.2. 2.1 ：循环遍历游标 中的示例：
1.6.2. 4.2 4.2 SYS_REFCURSORSYS_REFCURSOR SYS_REFCURSORSYS_REFCURSORSYS_REFCURSOR SYS_REFCURSORSYS_REFCURSOR 型游标 型游标
该游标是 Oracle OracleOracleOracleOracle以预先定义的游标，可作出参数进行传递 以预先定义的游标，可作出参数进行传递 。
注意一点： SYS_REFCURSORSYS_REFCURSOR SYS_REFCURSOR SYS_REFCURSORSYS_REFCURSOR SYS_REFCURSOR 只能通过 OPEN OPEN方法来打开和赋值
我们可以使用这种类似的游标来返回一个结果集：
CREATE OR REPLACE procedure CREATE OR REPLACE procedure CREATE OR REPLACE procedure CREATE OR REPLACE procedure CREATE OR REPLACE procedure CREATE OR REPLACE procedure CREATE OR REPLACE procedure CREATE OR REPLACE procedure CREATE OR REPLACE procedure CREATE OR REPLACE procedure CREATE OR REPLACE procedure CREATE OR REPLACE procedure CREATE OR REPLACE procedure proc_ proc_ proc_test (
checknum in number, checknum in number, checknum in number, checknum in number, checknum in number, checknum in number, checknum in number, -- 每次返回的数据量
ref_cursor out sys_refcursor ref_cursor out sys_refcursor ref_cursor out sys_refcursor ref_cursor out sys_refcursor ref_cursor out sys_refcursor ref_cursor out sys_refcursor ref_cursor out sys_refcursor ref_cursor out sys_refcursor ref_cursor out sys_refcursor ref_cursor out sys_refcursor ref_cursor out sys_refcursor -- 返回的结果集，游标 返回的结果集，游标
)
as as
begin beginbegin
open ref_cursor for select * from (dat_trade where state=41 order open ref_cursor for select * from (dat_trade where state=41 order open ref_cursor for select * from (dat_trade where state=41 order open ref_cursor for select * from (dat_trade where state=41 order open ref_cursor for select * from (dat_trade where state=41 order open ref_cursor for select * from (dat_trade where state=41 order open ref_cursor for select * from (dat_trade where state=41 order open ref_cursor for select * from (dat_trade where state=41 order open ref_cursor for select * from (dat_trade where state=41 order open ref_cursor for select * from (dat_trade where state=41 order open ref_cursor for select * from (dat_trade where state=41 order open ref_cursor for select * from (dat_trade where state=41 order open ref_cursor for select * from (dat_trade where state=41 order open ref_cursor for select * from (dat_trade where state=41 order open ref_cursor for select * from (dat_trade where state=41 order open ref_cursor for select * from (dat_trade where state=41 order open ref_cursor for select * from (dat_trade where state=41 order open ref_cursor for select * from (dat_trade where state=41 order open ref_cursor for select * from (dat_trade where state=41 order open ref_cursor for select * from (dat_trade where state=41 order open ref_cursor for select * from (dat_trade where state=41 order open ref_cursor for select * from (dat_trade where state=41 order open ref_cursor for select * from (dat_trade where state=41 order open ref_cursor for select * from (dat_trade where state=41 order open ref_cursor for select * from (dat_trade where state=41 order open ref_cursor for select * from (dat_trade where state=41 order open ref_cursor for select * from (dat_trade where state=41 order open ref_cursor for select * from (dat_trade where state=41 order open ref_cursor for select * from (dat_trade where state=41 order open ref_cursor for select * from (dat_trade where state=41 order open ref_cursor for select * from (dat_trade where state=41 order open ref_cursor for select * from (dat_trade where state=41 order open ref_cursor for select * from (dat_trade where state=41 order open ref_cursor for select * from (dat_trade where state=41 order open ref_cursor for select * from (dat_trade where state=41 order open ref_cursor for select * from (dat_trade where state=41 order open ref_cursor for select * from (dat_trade where state=41 order open ref_cursor for select * from (dat_trade where state=41 order by iby iby i d) where rownum<checknum; d) where rownum<checknum; d) where rownum<checknum;d) where rownum<checknum; d) where rownum<checknum;d) where rownum<checknum; d) where rownum<checknum; d) where rownum<checknum;d) where rownum<checknum;d) where rownum<checknum;d) where rownum<checknum;d) where rownum<checknum;
end proc_end proc_ end proc_ end proc_test ;
/
SYS_REFCURSORSYS_REFCURSOR SYS_REFCURSOR SYS_REFCURSORSYS_REFCURSOR SYS_REFCURSOR 中可使用三个状态属性： 中可使用三个状态属性：
（1）. %NOTFOUND(%NOTFOUND( %NOTFOUND( %NOTFOUND(%NOTFOUND(未找到记录信息 未找到记录信息 )
（2）. %FOUND(%FOUND(%FOUND( %FOUND( %FOUND(找到记录信息 找到记录信息 )
（3）. %ROWCOUNT(%ROWCOUNT(%ROWCOUNT( %ROWCOUNT(%ROWCOUNT( %ROWCOUNT( 然后当前游标所指向的行位置 )
CREATE OR REPLACE PROCEDURE proc_test (
checknum IN NUMBER, --每次返回的数据量
ref_cursor OUT sys_refcursor --返回的结果集，游标
)
AS
t_tmp table_name%ROWTYPE;
BEGIN
OPEN ref_cursor FOR
SELECT *
FROM ( SELECT *
FROM table_name
WHERE state = 41
ORDER BY id)
WHERE ROWNUM < checknum;
--循环游标
LOOP
FETCH ref_cursor INTO t_tmp;
EXIT WHEN ref_cursor%NOTFOUND;
-- DBMS_OUTPUT.put_line (t_tmp.id);
UPDATE table_name
SET state = 53
WHERE id = t_tmp.id;
COMMIT;
END LOOP;
CLOSE ref_cursor;
END proc_test;
1.6.2.5 1.6.2.5 1.6.2.5 1.6.2.5. 存储过程的调试 存储过程的调试 存储过程的调试 存储过程的调试
如果使用 PL/SQL Developer PL/SQL Developer PL/SQL Developer PL/SQL Developer PL/SQL Developer PL/SQL Developer PL/SQL Developer PL/SQL Developer PL/SQL Developer PL/SQL Developer PL/SQL Developer 或者 TOAD TOAD TOAD 工具的话，调试还是很方便。 工具的话，调试还是很方便。 工具的话，调试还是很方便。 如 果是在 SqlplusSqlplus Sqlplus 里，我们可以使用： 里，我们可以使用：
SQL>show SQL>show SQL>show SQL>show SQL>show errorserrors errorserrors
来查看错误。不过在开发中估计也很少有人直接使用 sqlplus sqlplus 来写存储过程。 来写存储过程。 效率低，调试又麻烦。 还是 使用工具方便点。我一直的ToadToad Toad的。
如果想在某处退出存储过程，直接使用 Return;Return;Return; 就可以了。 与存储过程编写 相关的数组和游标， 相关的数组和游标， 相关的数组和游标， 这两块说起来还是有很多东西。 这两块说起来还是有很多东西。 这两块说起来还是有很多东西。 这两块说起来还是有很多东西。 在上面的示例中， 在上面的示例中， 在上面的示例中， 也简 单的举了几个有关游标与存储过 程。