Oracle Stored Procedure instance

Values (name_outoutvarchar2, age_ininvarchar2) values; end; createorreplaceprocedureinsertRecord (UserIDinvarchar2, UserNameinvarchar2, UserAgeinvarchar2) isbegininsert

Alias (name_outoutvarchar2, age_ininvarchar2) as begin records = age_in; end; createorreplaceprocedureinsertRecord (UserIDinvarchar2, UserNameinvarchar2, UserAgeinvarchar2) is begin insert

1. Create or replace procedure GetRecords (name_out out varchar2, age_in in varchar2)
2. Begin
3. Select NAME into name_out from test where AGE = age_in;
4. End;
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;

First, a Sequence object named TEST_SEQ is created in Oracle. The SQL statement is as follows:

Java code

1. Create sequence TEST_SEQ
2. Min value 100
3. Max value 999
4. Start with 102
5. Increment by 1
6. Nocache;

The syntax should be easy to understand. The minimum and maximum values are represented by minvalue and maxvalue, respectively. The initial value is 102 (This number is dynamically changed and I set it to 100 when I create it, after two data entries are inserted, the increment is automatically increased by 2). The increment is of course the step size. In PL/SQL, you can use test_seq.nextval to access the next serial number and use test_seq.currval to access the current serial number.

After the Sequence is defined, a stored procedure InsertRecordWithSequence is created:

-- This time I modified the definition of the test table, which is different from the previous example. Here, UserID is PK.

Java code

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) -- inserts a record and obtains the pkvalue from Sequece.
4. Values (test_seq.nextval, UserName, UserAge );
5. /* Returns the pkvalue. Note Dual table usage */
6. Select test_seq.currval into UserID from dual;
7. End InsertRecordWithSequence;

In order for the stored procedure to return a result set, a cursor variable must be defined as an output parameter. This is quite different from SQL Server! In addition, the concept of "Package" in Oracle seems to be a little complicated, but it will be very convenient after you are familiar with it.

There is a lot of reference to the concept of "package". I will not go into details here. First, I created a package named TestPackage, which is defined as follows:

Java code

1. Create or replace package TestPackage is
2. Type mycursor is ref cursor; -- defines the cursor variable
3. Procedure GetRecords (ret_cursor out mycursor); -- defines the process and uses the cursor variable as the return parameter.
4. End TestPackage;
5. The package body is defined as follows:
6. Create or replace package body TestPackage is
7. /* Process body */
8. Procedure GetRecords (ret_cursor out mycursor)
9. Begin
10. Open ret_cursor for select * from test;
11. End GetRecords;
12. End TestPackage;

Summary:

A package is a concept unique to Oracle and cannot be found in SQL Server. In my opinion, the package is a bit like a VC ++ class. The header is a. h file, and the package body is a. cpp file. Baotou is only responsible for definition, while the package is responsible for specific implementation. If the package returns multiple cursors, DataReader accesses these cursors in the order you add them to the parameter set, rather than in the order they appear during the process. You can use the NextResult () method of DataReader to forward to the next cursor.

Java code

1. Create or replace package TestPackage is
2. Type mycursor is ref cursor;
3. Procedure UpdateRecords (id_in number, newName in varchar2, newAge in number );
4. Procedure SelectRecords (ret_cursor out mycursor );
5. Procedure DeleteRecords (id_in number );
6. Procedure InsertRecords (name_in varchar2, age_in number );
7. End TestPackage;

The package body is as follows:

Java code

1. Create or replace package body TestPackage is
2. Procedure UpdateRecords (id_in number, newName in varchar2, newAge in number)
3. Begin
4. Update test set age = newAge, name = newName where id = id_in;
5. End UpdateRecords;
7. Procedure SelectRecords (ret_cursor out mycursor)
8. Begin
9. Open ret_cursor for select * from test;
10. End SelectRecords;
12. Procedure DeleteRecords (id_in number)
13. Begin
14. Delete from test where id = id_in;
15. End DeleteRecords;
17. Procedure InsertRecords (name_in varchar2, age_in number)
18. Begin
19. Insert into test values (test_seq.nextval, name_in, age_in );
20. -- Test_seq is an existing Sequence object. See the preceding example.
21. End InsertRecords;
22. End TestPackage;

TestPackage. SelectRecords

Certificate -------------------------------------------------------------------------------------------------------------------------------------------------------------

Basic oracle Stored Procedure syntax

1. Basic Structure

CREATE OR REPLACE PROCEDURE

(

Parameter 1 in number,

Parameter 2 IN NUMBER

) IS

Variable 1 INTEGER: = 0;

Variable 2 DATE;

BEGIN

END stored procedure name

2. SELECT INTO STATEMENT

Save the result of the select query to a variable. Multiple columns can be stored in multiple variables at the same time. One

Record; otherwise, an exception is thrown (if no record exists, NO_DA is thrown ).TA_FOUND)

Example:

BEGIN

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

EXCEPTION

WHEN NO_DATA_FOUND THEN

Xxxx;

END;

...

3. IF judgment

IF V_TEST = 1 THEN

BEGIN

Do something

END;

End if;

4. while Loop

WHILE V_TEST = 1 LOOP

BEGIN

XXXX

END;

End loop;

5. Variable assignment

V_TEST: = 123;

6. Use cursor with for in

...

IS

CURSOR cur is select * FROM xxx;

BEGIN

FOR cur_result in cur LOOP

BEGIN

V_SUM: = cur_result. Column name 1 + cur_result. Column name 2

END;

End loop;

END;

7. cursor with Parameters

CURSOR C_USER (C_ID NUMBER) is select name from user where typeid = C_ID;

OPEN C_USER (variable value );

LOOP

FETCH C_USER INTO V_NAME;

Exit fetch C_USER % NOTFOUND;

Do something

End loop;

CLOSE C_USER;

8. Use pl/SQL developer debug

Create a Test WINDOW after connecting to the database

Enter the SP call code in the window, F9 start debug, CTRL + N single-step debugging

Certificate -------------------------------------------------------------------------------------------------------------------------------------------------------------

Oracle Stored Procedure example

Published By Ling yunzhi on 17:01:00

I recently switched to a project team and was dizzy. I want to write the stored procedure of oracle. Thanks to some db2 stored procedures, I still have some experience, but the pl/SQL of oralce is not the same, it took me one afternoon to write it out, and the test compilation was passed. It was recorded for future reference.

Java code

1. Create or replace package PY_PCKG_REFUND2
2. ------------------------------------------------------------------------
3. -- Oracle package
4. --- VISA refund on Air China Payment Platform
5. -- Cursor definition:
6. --
7. -- Stored Procedure Definition:
8. -- PY_WEBREFUND_VISA_PREPARE: VISA refund preparation
9. -- Last modified by: baiq
10. -- Last modification date: 2007.4.17
11. ------------------------------------------------------------------------
13. PROCEDURE PY_WEBREFUND_VISA_PREPARE (
14. In_serialNoStr IN VARCHAR2, -- a serial number for refund application on a network separated by "|"
15. In_session_operatorid IN VARCHAR2, -- business operator
16. Out_return_coDe OUT VARCHAR2, -- stored procedure return code
17. Out_visaInfoStr OUT VARCHAR2
18. );
20. END PY_PCKG_REFUND2;
21. /
24. Create or replace package body PY_PCKG_REFUND2
26. PROCEDURE PY_WEBREFUND_VISA_PREPARE (
27. In_serialNoStr IN VARCHAR2, -- a serial number for refund application on a network separated by "|"
28. In_session_operatorid IN VARCHAR2, -- business operator
29. Out_return_coDe OUT VARCHAR2, -- stored procedure return code
30. Out_visaInfoStr OUT VARCHAR2
31. ) IS
32. -- Variable Declaration
33. V_serialno VARCHAR2 (20); -- online refund application serial number
34. V_refserialno VARCHAR2 (20); -- payment transaction serial number
35. V_tobankOrderNo VARCHAR2 (30); -- Order Number of the bank to be sent
36. V_orderDate VARCHAR2 (8); -- order date
37. V_businessType VARCHAR2 (10); -- business type
38. V_currType VARCHAR2 (3); -- order type (ET-e-ticket)
39. V_merno VARCHAR2 (15); -- Merchant ID
40. V_orderNo VARCHAR2 (20); -- Merchant Order Number
41. V_orderState VARCHAR2 (2 );
42. V_refAmount NUMBER (15, 2); -- refund amount
43. V_tranType VARCHAR (2); -- transaction type
44. V_bank VARCHAR2 (10); -- acquiring bank
45. V_date VARCHAR2 (8); -- Transaction date
46. V_time VARCHAR2 (6); -- transaction time
47. V_datetime VARCHAR2 (14); -- system time obtained
48. V_index_start NUMBER;
49. V_index_end NUMBER;
50. V_ I NUMBER;
51. BEGIN
52. -- Initialize Parameters
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, 'yyyymmddhh24mis ');
59. V_date: = SUBSTR (v_datetime, 1, 8 );
60. V_time: = SUBSTR (v_datetime, 9, 14 );
62. -- Query order information (Merchant number, Merchant Order number, and refund amount) from the refund request table)
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;
81. -- A string of queried data
82. Out_visaInfoStr: = out_visaInfoStr | v_merno | '~ '| V_orderNo | '~ '| V_refAmount +' | ';
84. -- Prepare data for the next cycle
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;
92. -- Query the order information in the flow table based on the original payment sequential number, including the original order number sent to the bank or a third party: 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;
110. -- Record the streaming water meter (refund)
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, -- same as the serial number of the application form, passed as a parameter
128. V_tranType,
129. V_serialno, -- original transaction serial number. query the refund application form to obtain
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 -- the order number of the upstream bank, which is obtained by querying the streaming water meter.
141. );
143. -- Update online Refund Application Form
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;
152. -- Update the order table
153. IF v_tranType = '2' THEN
154. V_orderState: = '7 ';
155. ELSE
156. V_orderState: = '10 ';
157. End if;
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;
168. -- Exception Handling
169. EXCEPTION
170. WHEN OTHERS THEN
171. ROLLBACK;
172. Out_return_coDe: = '000000 ';
173. RETURN;
174. END;
176. END PY_PCKG_REFUND2;
177. /