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
- Create or replace procedure GetRecords (name_out out varchar2, age_in in varchar2)
- Begin
- Select NAME into name_out from test where AGE = age_in;
- End;
-
- Create or replace procedure insertRecord (UserID in varchar2, UserName in varchar2, UserAge in varchar2) is
- Begin
- Insert into test values (UserID, UserName, UserAge );
- End;
First, a Sequence object named TEST_SEQ is created in Oracle. The SQL statement is as follows:
Java code
- Create sequence TEST_SEQ
- Min value 100
- Max value 999
- Start with 102
- Increment by 1
- 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
- Create or replace procedure InsertRecordWithSequence (UserID out number, UserName in varchar2, UserAge in number)
- Is
- Begin insert into test (id, name, age) -- inserts a record and obtains the pkvalue from Sequece.
- Values (test_seq.nextval, UserName, UserAge );
- /* Returns the pkvalue. Note Dual table usage */
- Select test_seq.currval into UserID from dual;
- 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
- Create or replace package TestPackage is
- Type mycursor is ref cursor; -- defines the cursor variable
- Procedure GetRecords (ret_cursor out mycursor); -- defines the process and uses the cursor variable as the return parameter.
- End TestPackage;
- The package body is defined as follows:
- Create or replace package body TestPackage is
- /* Process body */
- Procedure GetRecords (ret_cursor out mycursor)
- Begin
- Open ret_cursor for select * from test;
- End GetRecords;
- 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
- Create or replace package TestPackage is
- Type mycursor is ref cursor;
- Procedure UpdateRecords (id_in number, newName in varchar2, newAge in number );
- Procedure SelectRecords (ret_cursor out mycursor );
- Procedure DeleteRecords (id_in number );
- Procedure InsertRecords (name_in varchar2, age_in number );
- End TestPackage;
The package body is as follows:
Java code
- Create or replace package body TestPackage is
- Procedure UpdateRecords (id_in number, newName in varchar2, newAge in number)
- Begin
- Update test set age = newAge, name = newName where id = id_in;
- End UpdateRecords;
-
- Procedure SelectRecords (ret_cursor out mycursor)
- Begin
- Open ret_cursor for select * from test;
- End SelectRecords;
-
- Procedure DeleteRecords (id_in number)
- Begin
- Delete from test where id = id_in;
- End DeleteRecords;
-
- Procedure InsertRecords (name_in varchar2, age_in number)
- Begin
- Insert into test values (test_seq.nextval, name_in, age_in );
- -- Test_seq is an existing Sequence object. See the preceding example.
- End InsertRecords;
- 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
- Create or replace package PY_PCKG_REFUND2
- ------------------------------------------------------------------------
- -- Oracle package
- --- VISA refund on Air China Payment Platform
- -- Cursor definition:
- --
- -- Stored Procedure Definition:
- -- PY_WEBREFUND_VISA_PREPARE: VISA refund preparation
- -- Last modified by: baiq
- -- Last modification date: 2007.4.17
- ------------------------------------------------------------------------
-
- PROCEDURE PY_WEBREFUND_VISA_PREPARE (
- In_serialNoStr IN VARCHAR2, -- a serial number for refund application on a network separated by "|"
- In_session_operatorid IN VARCHAR2, -- business operator
- Out_return_coDe OUT VARCHAR2, -- stored procedure return code
- Out_visaInfoStr OUT VARCHAR2
- );
-
- END PY_PCKG_REFUND2;
- /
-
-
- Create or replace package body PY_PCKG_REFUND2
-
- PROCEDURE PY_WEBREFUND_VISA_PREPARE (
- In_serialNoStr IN VARCHAR2, -- a serial number for refund application on a network separated by "|"
- In_session_operatorid IN VARCHAR2, -- business operator
- Out_return_coDe OUT VARCHAR2, -- stored procedure return code
- Out_visaInfoStr OUT VARCHAR2
- ) IS
- -- Variable Declaration
- V_serialno VARCHAR2 (20); -- online refund application serial number
- V_refserialno VARCHAR2 (20); -- payment transaction serial number
- V_tobankOrderNo VARCHAR2 (30); -- Order Number of the bank to be sent
- V_orderDate VARCHAR2 (8); -- order date
- V_businessType VARCHAR2 (10); -- business type
- V_currType VARCHAR2 (3); -- order type (ET-e-ticket)
- V_merno VARCHAR2 (15); -- Merchant ID
- V_orderNo VARCHAR2 (20); -- Merchant Order Number
- V_orderState VARCHAR2 (2 );
- V_refAmount NUMBER (15, 2); -- refund amount
- V_tranType VARCHAR (2); -- transaction type
- V_bank VARCHAR2 (10); -- acquiring bank
- V_date VARCHAR2 (8); -- Transaction date
- V_time VARCHAR2 (6); -- transaction time
- V_datetime VARCHAR2 (14); -- system time obtained
- V_index_start NUMBER;
- V_index_end NUMBER;
- V_ I NUMBER;
- BEGIN
- -- Initialize Parameters
- Out_visaInfoStr: = '';
- V_ I: = 1;
- V_index_start: = 1;
- V_index_end: = INSTR (in_serialNoStr, '|', 1, 1 );
- V_refserialno: = SUBSTR (in_serialNoStr, v_index_start, v_index_end-1 );
- V_datetime: = TO_CHAR (SYSDATE, 'yyyymmddhh24mis ');
- V_date: = SUBSTR (v_datetime, 1, 8 );
- V_time: = SUBSTR (v_datetime, 9, 14 );
-
- -- Query order information (Merchant number, Merchant Order number, and refund amount) from the refund request table)
- WHILE v_index_end> 0 LOOP
- SELECT
- WEBR_MERNO,
- WEBR_ORDERNO,
- WEBR_AMOUNT,
- WEBR_SERIALNO,
- WEBR_REFUNDTYPE
- INTO
- V_merno,
- V_orderNo,
- V_refAmount,
- V_serialno,
- V_tranType
- FROM
- PY_WEB_REFUND
- WHERE
- WEBR_REFREQNO = v_refserialno;
-
- -- A string of queried data
- Out_visaInfoStr: = out_visaInfoStr | v_merno | '~ '| V_orderNo | '~ '| V_refAmount +' | ';
-
- -- Prepare data for the next cycle
- V_ I: = v_ I + 1;
- V_index_start: = v_index_end + 1;
- V_index_end: = INSTR (in_serialNoStr, '|', 1, v_ I );
- IF v_index_end> 0 THEN
- V_refserialno: = SUBSTR (in_serialNoStr, v_index_start, v_index_end-1 );
- End if;
-
- -- 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
- SELECT
- WTRN_TOBANKORDERNO,
- WTRN_ORDERNO,
- WTRN_ORDERDATE,
- WTRN_BUSINESSTYPE,
- WTRN_ACCPBANK,
- WTRN_TRANCURRTYPE
- INTO
- V_tobankOrderNo,
- V_orderNo,
- V_orderDate,
- V_businessType,
- V_bank,
- V_currType
- FROM PY_WEBPAY_VIEW
- WHERE WTRN_SERIALNO = v_serialno;
-
- -- Record the streaming water meter (refund)
- Insert into PY_WEBPAY_TRAN (
- WTRN_SERIALNO,
- WTRN_TRANTYPE,
- WTRN_ORIGSERIALNO,
- WTRN_ORDERNO,
- WTRN_ORDERDATE,
- WTRN_BUSINESSTYPE,
- WTRN_TRANCURRTYPE,
- WTRN_TRANAMOUNT,
- WTRN_ACCPBANK,
- WTRN_TRANSTATE,
- WTRN_TRANTIME,
- WTRN_TRANDATE,
- WTRN_MERNO,
- WTRN_TOBANKORDERNO
- ) VALUES (
- V_refserialno, -- same as the serial number of the application form, passed as a parameter
- V_tranType,
- V_serialno, -- original transaction serial number. query the refund application form to obtain
- V_orderNo,
- V_orderDate,
- V_businessType,
- V_currType,
- V_refAmount,
- V_bank,
- '1 ',
- V_time,
- V_date,
- V_merno,
- V_tobankOrderNo -- the order number of the upstream bank, which is obtained by querying the streaming water meter.
- );
-
- -- Update online Refund Application Form
- UPDATE PY_WEB_REFUND
- SET
- WEBR_IFDISPOSED = '1 ',
- WEBR_DISPOSEDOPR = in_session_operatorid,
- WEBR_DISPOSEDDATE = v_datetime
- WHERE
- WEBR_REFREQNO = v_refserialno;
-
- -- Update the order table
- IF v_tranType = '2' THEN
- V_orderState: = '7 ';
- ELSE
- V_orderState: = '10 ';
- End if;
-
- UPDATE PY_ORDER
- SET
- ORD_ORDERSTATE = v_orderState
- WHERE
- ORD_ORDERNO = v_orderNo
- AND ORD_ORDERDATE = v_orderDate
- AND ORD_BUSINESSTYPE = v_businessType;
- End loop;
-
- -- Exception Handling
- EXCEPTION
- WHEN OTHERS THEN
- ROLLBACK;
- Out_return_coDe: = '000000 ';
- RETURN;
- END;
-
- END PY_PCKG_REFUND2;
- /