存储进程写法ITeye - 威尼斯人

存储进程写法ITeye

2019年03月13日08时57分26秒 | 作者: 绮晴 | 标签: 进程,存储,变量 | 浏览: 2654

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_SEQ的Sequence目标,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表的界说,和前面的示例不同。其间,UserID是PK。

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中有着很大的不同!而且还要用到Oracle中“包”(Package)的概念,好像有点繁琐,但了解后也会觉得很便利。

关于“包”的概念,有许多内容能够参阅,在此就不赘述了。首要,我创立了一个名为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会依照您向参数调集中增加它们的次序来拜访这些游标,而不是依照它们在进程中呈现的次序来拜访。可运用DataReader的NextResult()办法前进到下一个游标。

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的存储进程,姑且有些经历,不过oralce的pl/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./  

 

版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表威尼斯人立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章