`

存储过程写法

 
阅读更多

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

 

分享到：
| 导入式与链接式的区别
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    存储过程写法，存储过程

    根据提供的文档标题、描述、标签以及部分内容，我们可以总结出以下关于存储过程的创建与使用的相关知识点。 ### 一、存储过程的基本概念 存储过程是一种在数据库中存储并编译好的SQL程序，它能够接受输入参数，...

    DB2存储过程写法介绍

    DB2存储过程是一种预编译的SQL代码集合，它封装了复杂的数据库操作，可以在需要时被应用程序调用。存储过程的使用有多个显著优势。首先，它可以减少客户端和服务器之间的网络通信，因为处理过程在服务器端执行，减少...

    oracle存储过程写法

    根据提供的Oracle存储过程示例，我们可以详细解析其中的关键知识点，包括存储过程的创建、游标的使用、临时表的创建及数据处理等。 ### 存储过程的创建与使用 存储过程是在数据库中编写的SQL代码块，它可以接受...

    存储过程的写法大全

    ### 存储过程的写法大全 #### 一、存储过程概述 存储过程是一种预编译的SQL代码块，它可以包含一系列SQL语句，并在数据库服务器上存储为一个对象。当需要执行这些语句时，只需调用该存储过程的名字即可。这种方式...

    oracle存储过程各种写法

    此文档对oracle存储过程做了详细讲解，游标用法 变量定义，触发器，序列，DML DCL DDL

    高斯数据库存储过程模板

    包括存过日志加载，分区创建，已经存过的使用

    SQL Server存储过程基本语法

    ### SQL Server 存储过程基本语法知识点解析 #### 一、定义变量 在 SQL Server 中，我们可以使用 `DECLARE` 语句来定义变量，并通过 `SET` 或 `SELECT` 来给变量赋值。 ##### 1. 简单赋值 ```sql DECLARE @a int; ...

    C#调用存储过程简单实例

    在编程领域，数据库操作是不可或缺的一部分，而C#作为.NET框架的主要编程语言，与SQL Server数据库的交互常常通过存储过程来实现。存储过程是预编译的SQL语句集合，可以提高性能，增强安全性，并提供封装和重用的...

    mysql存储过程

    存储过程的简单介绍，通过一个具体的案例，实现了存储过程的主要写法。

    SQL存储过程

    CREATE DEFINER=`us`@`192.168.1.9` PROCEDURE `GSP_GP_AccountBind`( IN `dwUserID` int, -- 用户 I D ...`strClientIP` varchar(15),-- 连接地址 `strMachineID` varchar(32),-- 机器标识 `strBindAccounts` varchar...

    INFORMIX最新实用存储过程编写.doc

    Informix存储过程是数据库管理系统中的一种重要特性，它允许开发者编写一系列的SQL和控制流程语句，打包成一个可重复使用的单元，以便在需要时调用。在INFORMIX环境下，存储过程的编写涉及多个方面，包括环境配置、...

    JAVA&C#调用存储过程代码

    在IT领域，数据库操作是应用程序开发中的重要环节，而存储过程是数据库中预编译的SQL语句集合，可以提高数据处理效率并提供更高级的功能。本文将详细讲解如何使用Java和C#这两种广泛使用的编程语言来调用数据库中的...

    SupplyAGSlnProcedure(MSSQLserver存储过程的写法)

    SupplyAGSlnProcedure(MSSQLserver存储过程的写法),写储存过程一定要标准一点，要和不写，要写就要写好注释，还有测试到位。

    SQL Server存储过程的写法以及应用

    ### SQL Server 存储过程的写法及应用详解 #### 一、存储过程概述 存储过程（Stored Procedure）是在数据库中存储的一组预编译的 SQL 语句和控制流语句的集合，它们作为一个单元存储在数据库中，并可以通过一个...

    ASP中调用存储过程、语法、写法

    本文旨在深入探讨如何在ASP中调用SQL Server数据库中的存储过程，并详细介绍相关的语法和写法。 #### 二、存储过程简介 存储过程是一种预编译的SQL脚本，存储在数据库服务器上。它可以接收参数，并返回结果集或...

    数据库存储过程的写法\\连接各种数据库写法

    ### 数据库存储过程的写法及连接各种数据库的方法 #### 概述 本文将详细介绍如何在不同的数据库系统中编写存储过程以及如何建立与这些数据库的连接。存储过程是一种预编译的SQL代码块，它可以存储在数据库服务器上...

    .Net的存储过程

    在描述中提到的"存储过程写法.txt"文件，可能包含了创建存储过程的SQL脚本。例如，一个简单的增删改查的存储过程可能如下： ```sql -- 创建添加数据的存储过程 CREATE PROCEDURE dbo.sp_InsertData @param1 INT, ...

    C# winform调用SQL存储过程-菜鸟入门 详细注释

    内容概要：简单的C# winform调用存储过程实例，创建存储过程入参，通过SqlConnection对象和SqlCommand对象调用存储过程，获取存储过程的出参并显示出来，详细代码注释，希望对用到C#调用存储过程的小伙伴有帮助 ...

    oracle存储过程和函数写法

    oracle 的存储过程和函数的语法 如下

    MYSQL的存储过程和函数简单写法

    【MySQL存储过程与函数简介】 MySQL的存储过程和函数是数据库管理中强大的工具，它们允许开发者将一组SQL语句组织在一起，形成一个可重用的模块，以执行复杂的业务逻辑。这种概念类似于高级编程语言（如Java）中的...

Magicbox
Global site tag (gtag.js) - Google Analytics