`

存储过程写法

 
阅读更多

Java代码

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;   

首先,在Oracle中创建了一个名为TEST_SEQSequence对象,SQL语句如下:

Java代码

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表的定义,和前面的示例不同。其中,UserIDPK

Java代码

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.   /*返回PK值。注意Dual表的用法*/    

6.   select test_seq.currval into UserID from dual;       

7.   end InsertRecordWithSequence;   

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

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

Java代码

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会按照您向参数集合中添加它们的顺序来访问这些游标,而不是按照它们在过程中出现的顺序来访问。可使用DataReaderNextResult()方法前进到下一个游标。

Java代码

1.   create or replace package TestPackage is    

2.        type mycursor is ref cursor;    

3.        procedure UpdateRecords(id_in in number,newName in varchar2,newAge in number);    

4.        procedure SelectRecords(ret_cursor out mycursor);    

5.        procedure DeleteRecords(id_in in number);    

6.        procedure InsertRecords(name_in in varchar2, age_in in number);    

7.   end TestPackage;   

包体如下:

Java代码

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.  

17.     procedure InsertRecords(name_in in varchar2, age_in in number) as   

18.     begin   

19.        insert into test values (test_seq.nextval, name_in, age_in);    

20.     --test_seq是一个已建的Sequence对象,请参照前面的示例    

21.     end InsertRecords;   

22.     end TestPackage;   

TestPackage.SelectRecords

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

oracle 存储过程的基本语法

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单步调试

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

oracle存储过程一例

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

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

Java代码

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./  

 

分享到:
评论

相关推荐

Global site tag (gtag.js) - Google Analytics