Creating a stored procedure in oracle is a frequently used function. The following describes the differences between the stored procedure in oracle and other databases. If you are interested, take a look.
The stored procedure created in oracle is inconsistent with the syntax in sybase and SQL server.
The following example shows how to use stored procedures in different databases.
---------------------------
Oracle storage creation process:
- CREATE OR REPLACE FUNCTION MY_FUNC
- (
- P1 IN MY_TABLE.YY%TYPE,
- P2 IN MY_TABLE.NN%TYPE,
- P3 VARCHAR(100)
- )
- RETURN VARCHAR2 AS
/* Define a cursor with parameters and a cursor without parameters */
- CURSOR MY_CURSOR1 IS
- Select yy, NN, DECODE (FYYSDM, 0, 'Total', 1, 'processing payby', 'other billing method ')
- FROM MY_TABLE
- Where yy = P1
- Group by yy, NN
- Order by yy, NN;
/* Define the cursor variable to store records in the cursor dataset */
- V_CURSOR1 MY_CURSOR1 % ROWTYPE;
-
- CURSOR MY_CURSOR2 (V_ZYDM MY_TABLE.ZYDM % TYPE, V_FYYSDM NUMBER)
- Select yy, NN, ZYDM, NVL (ZYCB, 0)/* convert the NVL function to a null value */
- FROM MY_TABLE
- Where yy = P1 and nn = P2 and zydm = V_ZYDM and fyysdm = V_FYYSDM
- Group by yy, NN;
-
/* Define the cursor variable to store records in the cursor dataset */
- V_CURSOR2 MY_CURSOR2 % ROWTYPE;
-
- V_CPDM MY_TABLE.CPDM % TYPE;
- V_COUNT NUMBER;
- V_BZ VARCHAR2 (2 );
- V_CPCB NUMBER (22, 2 );
-
- BEGIN
- V_BZ: = 1;
- Select cpdm into V_CPDM FROM MY_TABLE;
- Select cpcb into V_CPCB FROM MY_TABLE where rownum = 1;
- IF MY_CURSOR1 % isopen then/* determine whether the cursor has been opened */
- CLOSE MY_CURSOR1;
- End if;
- OPEN MY_CURSOR1;
- FETCH MY_CURSOR1 INTO V_CURSOR1;
- IF MY_CURSOR1 % notfound then/* The cursor return result is blank */
- CLOSE MY_CURSOR1;
- RETURN (V_BZ );
- End if;
- WHILE MY_CURSOR1 % found loop/* The result returned by the cursor is not blank */
- V_CPDM: = V_CURSOR1.CPDM;
- V_CPCB: = V_CURSOR1.CPCB;
- V_COUNT: = 100;
- IF V_COUNT = 100 THEN
- V_COUNT: = 99;
- End if;
- FETCH MY_CURSOR1 INTO V_CURSOR1;
- End loop;
- CLOSE MY_CURSOR1;
-
/* Explicitly open a parameter-based cursor */
- Select cpdm into V_CPDM FROM MY_TABLE;
- OPEN MY_CURSOR2;
- FETCH MY_CURSOR2 INTO V_CURSOR2;
- WHILE MY_CURSOR2 % found loop/* The result returned by the cursor is not blank */
- V_CPDM: = V_CURSOR2.CPDM;
- V_CPCB: = V_CURSOR2.CPCB;
- V_COUNT: = 100;
- IF V_COUNT = 100 THEN
- V_COUNT: = 99;
- ELSE
- V_COUNT: = 88;
- End if;
- FETCH MY_CURSOR2 INTO V_CURSOR2;
- End loop;
- CLOSE MYCURSOR2;
/* Open the cursor implicitly */
- FOR V_CURSOR2 IN MY_CURSOR2 (V_CPDM, V_CURSOR1.FYYSDM) LOOP
- IF V_CURSOR2.CPCB IS NULL THEN
- PRINT 'invalid! ';
- ROLLBACK;
- End if;
-
- UPDATE MY_TABLE
- Set cpcb = V_CPCB
- Where yy = P1 and nn = P2 and cpdm = V_CURSOR2.CPDM;
- If SQL % NOTFOUND THEN/* determine whether the execution result of the first sentence exists */
- /* Program segment */
- End if;
- End loop;
- V_BZ: = MY_DELETE_CB (P_YY, P_NN );
- IF V_BZ <> 0 THEN
- PRINT 'failed! ';
- End if;
- For I INT 1 .. V_COUNT LOOP
- /**/
- End loop;
-
- COMMIT;/* submit a transaction */
- RETURN (0);/* RETURN value */
- END MY_FUNC;
-
- Create or replace procedure SP_MY
- (
- P_YY IN MY_TABLE.YY % TYPE;
- P_NN NUMBER;
- )
- IS
-
- CURSOR MY_CURSOR IS
- SELECT CPCB
- FROM MY_TABLE
- Where yy = P_YY and nn = P_NN;
-
- V_ZYCB NUMBER (22, 2 );
-
- BEGIN
- /**/
- /* No return value */
- END;
-
How to deal with oracle service loss
How to start Oracle databases in Linux
Implementation of Oracle redo log
How to modify the size of Oracle archive logs
How to delete archive logs in Oracle