Oracle Stored Procedure instance

Source: Internet
Author: User
Tags stored procedure example
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;
  5. Create or replace procedure insertRecord (UserID in varchar2, UserName in varchar2, UserAge in varchar2) is
  6. Begin
  7. Insert into test values (UserID, UserName, UserAge );
  8. 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;
  6. Procedure SelectRecords (ret_cursor out mycursor)
  7. Begin
  8. Open ret_cursor for select * from test;
  9. End SelectRecords;
  10. Procedure DeleteRecords (id_in number)
  11. Begin
  12. Delete from test where id = id_in;
  13. End DeleteRecords;
  14. Procedure InsertRecords (name_in varchar2, age_in number)
  15. Begin
  16. Insert into test values (test_seq.nextval, name_in, age_in );
  17. -- Test_seq is an existing Sequence object. See the preceding example.
  18. End InsertRecords;
  19. 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. ------------------------------------------------------------------------
  12. PROCEDURE PY_WEBREFUND_VISA_PREPARE (
  13. In_serialNoStr IN VARCHAR2, -- a serial number for refund application on a network separated by "|"
  14. In_session_operatorid IN VARCHAR2, -- business operator
  15. Out_return_coDe OUT VARCHAR2, -- stored procedure return code
  16. Out_visaInfoStr OUT VARCHAR2
  17. );
  18. END PY_PCKG_REFUND2;
  19. /
  20. Create or replace package body PY_PCKG_REFUND2
  21. PROCEDURE PY_WEBREFUND_VISA_PREPARE (
  22. In_serialNoStr IN VARCHAR2, -- a serial number for refund application on a network separated by "|"
  23. In_session_operatorid IN VARCHAR2, -- business operator
  24. Out_return_coDe OUT VARCHAR2, -- stored procedure return code
  25. Out_visaInfoStr OUT VARCHAR2
  26. ) IS
  27. -- Variable Declaration
  28. V_serialno VARCHAR2 (20); -- online refund application serial number
  29. V_refserialno VARCHAR2 (20); -- payment transaction serial number
  30. V_tobankOrderNo VARCHAR2 (30); -- Order Number of the bank to be sent
  31. V_orderDate VARCHAR2 (8); -- order date
  32. V_businessType VARCHAR2 (10); -- business type
  33. V_currType VARCHAR2 (3); -- order type (ET-e-ticket)
  34. V_merno VARCHAR2 (15); -- Merchant ID
  35. V_orderNo VARCHAR2 (20); -- Merchant Order Number
  36. V_orderState VARCHAR2 (2 );
  37. V_refAmount NUMBER (15, 2); -- refund amount
  38. V_tranType VARCHAR (2); -- transaction type
  39. V_bank VARCHAR2 (10); -- acquiring bank
  40. V_date VARCHAR2 (8); -- Transaction date
  41. V_time VARCHAR2 (6); -- transaction time
  42. V_datetime VARCHAR2 (14); -- system time obtained
  43. V_index_start NUMBER;
  44. V_index_end NUMBER;
  45. V_ I NUMBER;
  46. BEGIN
  47. -- Initialize Parameters
  48. Out_visaInfoStr: = '';
  49. V_ I: = 1;
  50. V_index_start: = 1;
  51. V_index_end: = INSTR (in_serialNoStr, '|', 1, 1 );
  52. V_refserialno: = SUBSTR (in_serialNoStr, v_index_start, v_index_end-1 );
  53. V_datetime: = TO_CHAR (SYSDATE, 'yyyymmddhh24mis ');
  54. V_date: = SUBSTR (v_datetime, 1, 8 );
  55. V_time: = SUBSTR (v_datetime, 9, 14 );
  56. -- Query order information (Merchant number, Merchant Order number, and refund amount) from the refund request table)
  57. WHILE v_index_end> 0 LOOP
  58. SELECT
  59. WEBR_MERNO,
  60. WEBR_ORDERNO,
  61. WEBR_AMOUNT,
  62. WEBR_SERIALNO,
  63. WEBR_REFUNDTYPE
  64. INTO
  65. V_merno,
  66. V_orderNo,
  67. V_refAmount,
  68. V_serialno,
  69. V_tranType
  70. FROM
  71. PY_WEB_REFUND
  72. WHERE
  73. WEBR_REFREQNO = v_refserialno;
  74. -- A string of queried data
  75. Out_visaInfoStr: = out_visaInfoStr | v_merno | '~ '| V_orderNo | '~ '| V_refAmount +' | ';
  76. -- Prepare data for the next cycle
  77. V_ I: = v_ I + 1;
  78. V_index_start: = v_index_end + 1;
  79. V_index_end: = INSTR (in_serialNoStr, '|', 1, v_ I );
  80. IF v_index_end> 0 THEN
  81. V_refserialno: = SUBSTR (in_serialNoStr, v_index_start, v_index_end-1 );
  82. End if;
  83. -- 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
  84. SELECT
  85. WTRN_TOBANKORDERNO,
  86. WTRN_ORDERNO,
  87. WTRN_ORDERDATE,
  88. WTRN_BUSINESSTYPE,
  89. WTRN_ACCPBANK,
  90. WTRN_TRANCURRTYPE
  91. INTO
  92. V_tobankOrderNo,
  93. V_orderNo,
  94. V_orderDate,
  95. V_businessType,
  96. V_bank,
  97. V_currType
  98. FROM PY_WEBPAY_VIEW
  99. WHERE WTRN_SERIALNO = v_serialno;
  100. -- Record the streaming water meter (refund)
  101. Insert into PY_WEBPAY_TRAN (
  102. WTRN_SERIALNO,
  103. WTRN_TRANTYPE,
  104. WTRN_ORIGSERIALNO,
  105. WTRN_ORDERNO,
  106. WTRN_ORDERDATE,
  107. WTRN_BUSINESSTYPE,
  108. WTRN_TRANCURRTYPE,
  109. WTRN_TRANAMOUNT,
  110. WTRN_ACCPBANK,
  111. WTRN_TRANSTATE,
  112. WTRN_TRANTIME,
  113. WTRN_TRANDATE,
  114. WTRN_MERNO,
  115. WTRN_TOBANKORDERNO
  116. ) VALUES (
  117. V_refserialno, -- same as the serial number of the application form, passed as a parameter
  118. V_tranType,
  119. V_serialno, -- original transaction serial number. query the refund application form to obtain
  120. V_orderNo,
  121. V_orderDate,
  122. V_businessType,
  123. V_currType,
  124. V_refAmount,
  125. V_bank,
  126. '1 ',
  127. V_time,
  128. V_date,
  129. V_merno,
  130. V_tobankOrderNo -- the order number of the upstream bank, which is obtained by querying the streaming water meter.
  131. );
  132. -- Update online Refund Application Form
  133. UPDATE PY_WEB_REFUND
  134. SET
  135. WEBR_IFDISPOSED = '1 ',
  136. WEBR_DISPOSEDOPR = in_session_operatorid,
  137. WEBR_DISPOSEDDATE = v_datetime
  138. WHERE
  139. WEBR_REFREQNO = v_refserialno;
  140. -- Update the order table
  141. IF v_tranType = '2' THEN
  142. V_orderState: = '7 ';
  143. ELSE
  144. V_orderState: = '10 ';
  145. End if;
  146. UPDATE PY_ORDER
  147. SET
  148. ORD_ORDERSTATE = v_orderState
  149. WHERE
  150. ORD_ORDERNO = v_orderNo
  151. AND ORD_ORDERDATE = v_orderDate
  152. AND ORD_BUSINESSTYPE = v_businessType;
  153. End loop;
  154. -- Exception Handling
  155. EXCEPTION
  156. WHEN OTHERS THEN
  157. ROLLBACK;
  158. Out_return_coDe: = '000000 ';
  159. RETURN;
  160. END;
  161. END PY_PCKG_REFUND2;
  162. /

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.