Differences between creating a stored procedure in oracle and sybase

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