1. create or replace procedure GetRecords(name_out out varchar2,age_in in varchar2) as   
2. begin   
3.    select NAME into name_out from test where AGE = age_in;   
4. end;   
5.   
6. create or replace procedure insertRecord(UserID in varchar2, UserName in varchar2,UserAge in varchar2) is   
7. begin   
8.    insert into test values (UserID, UserName, UserAge);   
9. end;   

1. create sequence TEST_SEQ   
2. minvalue 100   
3. maxvalue 999   
4. start with 102   
5. increment by 1   
6. nocache;   

语法应该是比较易懂的，最小最大值分别用minvalue,maxvalue表示，初始值是102（这个数字是动态变化的，我创建的时候设的是100，后因插入了2条数据后就自动增加了2），increment当然就是步长了。在PL/SQL中可以用test_seq.nextval访问下一个序列号，用test_seq.currval访问当前的序列号。

定义完了Sequence，接下来就是创建一个存储过程InsertRecordWithSequence：

--这次我修改了test表的定义，和前面的示例不同。其中，UserID是PK。

1. create or replace procedure InsertRecordWithSequence(UserID    out number,UserName in varchar2,UserAge   in number)   
2. is   
3. begin insert into test(id, name, age) --插入一条记录，PK值从Sequece获取   
4. values(test_seq.nextval, UserName, UserAge);   
5.   
6. select test_seq.currval into UserID from dual;       
7. end InsertRecordWithSequence;   

为了让存储过程返回结果集，必须定义一个游标变量作为输出参数。这和Sql Server中有着很大的不同！并且还要用到Oracle中“包”（Package）的概念，似乎有点繁琐，但熟悉后也会觉得很方便。

关于“包”的概念，有很多内容可以参考，在此就不赘述了。首先，我创建了一个名为TestPackage的包，包头是这么定义的：

1. create or replace package TestPackage is   
2.      type mycursor is ref cursor; -- 定义游标变量   
3.       procedure GetRecords(ret_cursor out mycursor); -- 定义过程，用游标变量作为返回参数   
4. end TestPackage;      
5. 包体是这么定义的：   
6. create or replace package body TestPackage is   
7.   
8.            procedure GetRecords(ret_cursor out mycursor) as   
9.            begin   
10.                open ret_cursor for select * from test;   
11.            end GetRecords;   
12. end TestPackage;   

小结：

    包是Oracle特有的概念，Sql Server中找不到相匹配的东西。在我看来，包有点像VC++的类，包头就是.h文件，包体就是.cpp文件。包头只负责定义，包体则负责具体实现。如果包返回多个游标，则DataReader会按照您向参数集合中添加它们的顺序来访问这些游标，而不是按照它们在过程中出现的顺序来访问。可使用DataReader的NextResult()方法前进到下一个游标。

1.       type mycursor is ref cursor;   
2.       procedure UpdateRecords(id_in in number,newName in varchar2,newAge in number);   
3.       procedure SelectRecords(ret_cursor out mycursor);   
4.       procedure DeleteRecords(id_in in number);   
5.       procedure InsertRecords(name_in in varchar2, age_in in number);   
6. end TestPackage;   

包体如下： 

1. create or replace package body TestPackage is   
2.      procedure UpdateRecords(id_in in number, newName in varchar2, newAge   in number) as   
3.      begin   
4.       update test set age = newAge, name = newName where id = id_in;   
5.      end UpdateRecords;   
6.   
7.      procedure SelectRecords(ret_cursor out mycursor) as   
8.      begin   
9.         open ret_cursor for select * from test;   
10.      end SelectRecords;   
11.   
12.      procedure DeleteRecords(id_in in number) as   
13.      begin   
14.         delete from test where id = id_in;   
15.      end DeleteRecords;
16.      procedure InsertRecords(name_in in varchar2, age_in in number) as   
17.      begin   
18.         insert into test values (test_seq.nextval, name_in, age_in);   
19.     --test_seq是一个已建的Sequence对象，请参照前面的示例   
20.     end InsertRecords;   
21.      end TestPackage;   

TestPackage.SelectRecords

-------------------------------------------------------------------------------------------------------------------------------------------------------------

1.基本结构

CREATE OR REPLACE PROCEDURE 存储过程名字

(

    参数1 IN NUMBER,

    参数2 IN NUMBER

) IS

变量1 INTEGER :=0;

变量2 DATE;

BEGIN

END 存储过程名字

2.SELECT INTO STATEMENT

将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条

记录，否则抛出异常(如果没有记录抛出NO_DATA_FOUND)

例子：

BEGIN

SELECT col1,col2 into 变量1,变量2 FROM typestruct where xxx;

EXCEPTION

WHEN NO_DATA_FOUND THEN

      xxxx;

END;

...

3.IF 判断

IF V_TEST=1 THEN

    BEGIN

       do something

    END;

END IF;

4.while 循环

WHILE V_TEST=1 LOOP

BEGIN

XXXX

END;

END LOOP;

5.变量赋值

V_TEST := 123;

6.用for in 使用cursor

...

IS

CURSOR cur IS SELECT * FROM xxx;

BEGIN

FOR cur_result in cur LOOP

BEGIN

   V_SUM :=cur_result.列名1+cur_result.列名2

END;

END LOOP;

END;

7.带参数的cursor

CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID;

OPEN C_USER(变量值);

LOOP

FETCH C_USER INTO V_NAME;

EXIT FETCH C_USER%NOTFOUND;

    do something

END LOOP;

CLOSE C_USER;

8.用pl/sql developer debug

连接数据库后建立一个Test WINDOW

在窗口输入调用SP的代码,F9开始debug,CTRL+N单步调试

-------------------------------------------------------------------------------------------------------------------------------------------------------------

By 凌云志发表于 2007-4-18 17:01:00  

最近换了一个项目组，晕，要写oracle的存储过程，幸亏写过一些db2的存储过程，尚且有些经验，不过oralce的pl/sql不大一样，花费了一下午的时间写了一个出来，测试编译通过了，是为记，以备以后查阅。

1. CREATE OR REPLACE PACKAGE PY_PCKG_REFUND2 AS   
2. ------------------------------------------------------------------------   
3. -- Oracle 包   
4. ---国航支付平台VISA退款   
5. -- 游标定义：   
6. --   
7. -- 存储过程定义：   
8. -- PY_WEBREFUND_VISA_PREPARE   ： VISA退款准备   
9. -- 最后修改人：dougq   
10. -- 最后修改日期：2007.4.17  
11. ------------------------------------------------------------------------   
12.   
13. PROCEDURE PY_WEBREFUND_VISA_PREPARE (   
14.    in_serialNoStr    IN   VARCHAR2, --用"|"隔开的一组网上退款申请流水号   
15.    in_session_operatorid IN VARCHAR2, --业务操作员   
16.    out_return_code      OUT VARCHAR2, --存储过程返回码   
17.    out_visaInfoStr      OUT VARCHAR2   
18. );   
19.   
20. END PY_PCKG_REFUND2;   
21. /   
22.   
23.   
24. CREATE OR REPLACE PACKAGE BODY PY_PCKG_REFUND2 AS   
25.   
26. PROCEDURE PY_WEBREFUND_VISA_PREPARE (   
27.    in_serialNoStr       IN   VARCHAR2, --用"|"隔开的一组网上退款申请流水号   
28.    in_session_operatorid IN VARCHAR2,--业务操作员   
29.    out_return_code      OUT VARCHAR2, --存储过程返回码   
30.    out_visaInfoStr      OUT VARCHAR2   
31. ) IS   
32.    --变量声明   
33.    v_serialno   VARCHAR2(20);--网上退款申请流水号   
34.    v_refserialno VARCHAR2(20);--支付交易流水号   
35.    v_tobankOrderNo VARCHAR2(30);--上送银行的订单号   
36.    v_orderDate   VARCHAR2(8);--订单日期   
37.    v_businessType VARCHAR2(10);--业务类型   
38.    v_currType   VARCHAR2(3);--订单类型（ET－电子机票）   
39.    v_merno    VARCHAR2(15);--商户号   
40.    v_orderNo   VARCHAR2(20);--商户订单号   
41.    v_orderState VARCHAR2(2);   
42.    v_refAmount      NUMBER(15,2);--退款金额   
43.    v_tranType   VARCHAR(2);--交易类型   
44.    v_bank    VARCHAR2(10);--收单银行   
45.    v_date    VARCHAR2 (8);--交易日期   
46.        v_time    VARCHAR2 (6);--交易时间   
47.        v_datetime   VARCHAR2 (14);--获取的系统时间   
48.    v_index_start NUMBER;   
49.    v_index_end   NUMBER;   
50.    v_i     NUMBER;   
51. BEGIN   
52.    -- 初始化参数   
53.    out_visaInfoStr := '';   
54.    v_i := 1;   
55.    v_index_start := 1;   
56.    v_index_end := INSTR(in_serialNoStr,'|',1,1);   
57.    v_refserialno := SUBSTR(in_serialNoStr, v_index_start, v_index_end-1);   
58.    v_datetime := TO_CHAR (SYSDATE, 'yyyymmddhh24miss');   
59.    v_date := SUBSTR (v_datetime, 1, 8);   
60.    v_time := SUBSTR (v_datetime, 9, 14);   
61.   
62.    --从退款请求表中查询定单信息（商户号、商户订单号、退款金额）   
63.    WHILE v_index_end > 0 LOOP   
64.     SELECT   
65.      WEBR_MERNO,   
66.      WEBR_ORDERNO,   
67.      WEBR_AMOUNT,   
68.      WEBR_SERIALNO,   
69.      WEBR_REFUNDTYPE   
70.     INTO   
71.      v_merno,   
72.      v_orderNo,   
73.      v_refAmount,   
74.      v_serialno,   
75.      v_tranType   
76.        FROM   
77.      PY_WEB_REFUND   
78.        WHERE   
79.      WEBR_REFREQNO = v_refserialno;   
80.       
81.     --将查询到的数据组成串   
82.     out_visaInfoStr := out_visaInfoStr || v_merno || '~' || v_orderNo || '~' || v_refAmount + '|';   
83.      
84.     --为下次循环做数据准备   
85.        v_i := v_i + 1;   
86.        v_index_start := v_index_end + 1;   
87.        v_index_end := INSTR(in_serialNoStr,'|',1,v_i);   
88.        IF v_index_end > 0 THEN   
89.          v_refserialno := SUBSTR(in_serialNoStr, v_index_start, v_index_end - 1);         
90.        END IF;   
91.          
92.     --根据原支付流水号在流水表中查询该订单的信息，包括原上送银行或第三方的订单号：WTRN_TOBANKORDERNO   
93.     SELECT   
94.      WTRN_TOBANKORDERNO,   
95.      WTRN_ORDERNO,   
96.        WTRN_ORDERDATE,   
97.        WTRN_BUSINESSTYPE,   
98.      WTRN_ACCPBANK,   
99.      WTRN_TRANCURRTYPE   
100.     INTO   
101.      v_tobankOrderNo,   
102.      v_orderNo,   
103.      v_orderDate,   
104.      v_businessType,   
105.      v_bank,   
106.      v_currType   
107.     FROM PY_WEBPAY_VIEW   
108.      WHERE WTRN_SERIALNO = v_serialno;   
109.        
110.     --记录流水表(退款)   
111.        INSERT INTO PY_WEBPAY_TRAN(   
112.      WTRN_SERIALNO,   
113.      WTRN_TRANTYPE,   
114.      WTRN_ORIGSERIALNO,   
115.      WTRN_ORDERNO,   
116.      WTRN_ORDERDATE,   
117.      WTRN_BUSINESSTYPE,   
118.      WTRN_TRANCURRTYPE,   
119.      WTRN_TRANAMOUNT,   
120.      WTRN_ACCPBANK,   
121.      WTRN_TRANSTATE,   
122.      WTRN_TRANTIME,   
123.      WTRN_TRANDATE,   
124.      WTRN_MERNO,   
125.      WTRN_TOBANKORDERNO   
126.     )VALUES(   
127.      v_refserialno, --和申请表的流水号相同，作为参数传人   
128.      v_tranType,   
129.      v_serialno, --原交易流水号，查询退款申请表得到   
130.      v_orderNo,   
131.      v_orderDate,   
132.      v_businessType,   
133.      v_currType,   
134.      v_refAmount,   
135.      v_bank,   
136.      '1',   
137.      v_time,   
138.      v_date,   
139.      v_merno,   
140.      v_tobankOrderNo --上送银行的订单号，查询流水表得到   
141.     );   
142.   
143.     --更新网上退款申请表   
144.     UPDATE PY_WEB_REFUND   
145.     SET   
146.      WEBR_IFDISPOSED = '1',   
147.      WEBR_DISPOSEDOPR = in_session_operatorid,   
148.      WEBR_DISPOSEDDATE = v_datetime   
149.     WHERE   
150.      WEBR_REFREQNO = v_refserialno;   
151.       
152.     --更新定单表   
153.     IF v_tranType = '2' THEN   
154.      v_orderState := '7';   
155.     ELSE   
156.      v_orderState := '10';   
157.     END IF;   
158.   
159.     UPDATE PY_ORDER   
160.     SET   
161.      ORD_ORDERSTATE = v_orderState   
162.     WHERE   
163.       ORD_ORDERNO = v_orderNo   
164.      AND ORD_ORDERDATE = v_orderDate   
165.      AND ORD_BUSINESSTYPE = v_businessType;   
166.    END LOOP;   
167.   
168.    -- 异常处理   
169.    EXCEPTION   
170.     WHEN OTHERS THEN   
171.     ROLLBACK;   
172.     out_return_code := '14001';   
173.     RETURN;   
174. END;   
175.   
176. END PY_PCKG_REFUND2;   
177. /