Statement comparison between sybase and oracle stored procedures

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:
Create or replace function MY_FUNC
(
P1 IN MY_TABLE.YY % TYPE,
P2 IN MY_TABLE.NN % TYPE,
P3 VARCHAR (100)
)
RETURN VARCHAR2
/* 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;

Attached oracle functions:
NVL (AAA, BBB) null value judgment function. AAA is the variable to be judged, and the value must be replaced when BBB is null.
DECODE (ID, P1, S1, P2, S2, S3) condition judgment function. If ID is P1, S1 is returned. If ID is P2, S2 is returned. Otherwise, S3 is returned.
RPAD (AAA, COUNT, '0') character fill empty function, after the AAA variable fill zero, a total of String Length COUNT.
SUBSTR (AAA, M, N) takes the sub-string function, AAA starts from the M character, and N characters.
LENGTH (STR) is a string of characters.
LENGTHB (STR) takes the length of characters in bytes.
TO_CHAR ()
TO_NUMBER () type Conversion Function
--------------------------------------------------
SYBASE:
Create procedure PRO_MY
(
@ P_YY CHAR (4 ),
@ P_NN CHAR (2) = '01 ',
@ P_OUT VARCHAR (255) = NULL OUTPUT
)
AS
BEGIN
DECLARE @ V_CPDM NUMERIC (9, 0 ),
@ V_CPCB NUMERIC (22, 2 ),
@ V_ID INT,
@ V_JE FLOAT

DECLARE CUR_TEST CURSOR
Select cpcb, JE
FROM TEST
Where yy = @ P_YY and nn = @ P_NN and cpdm = @ V_CPDM
FOR READ ONLY

SELECT @ V_CPCB = CPCB
FROM TEST
Where yy = @ P_YY and nn = @ P_NN and cpdm = @ V_CPDM
IF @ ROWCOUNT = 0
PRINT 'not found'
ELSE
PRINT 'found'

OPEN CUR_TEST
FETCH CUR_TEST INTO @ V_CPCB, @ V_JE
IF @ SQLSTATUS = 2 -- the returned result set is empty.
IF @ SQLSTATUS = 1 -- cursor execution error
BEGIN
RAISERROR 20000 -- return the custom error number
-- RAISERROR 20000, 'error information' -- return the custom error number.
ROLLBACK
RETURN 10
END
WHILE @ SQLSTATUS = 0 -- the result set returns normal results
BEGIN
/* Exception */
FETCH CUR_TEST INTO @ V_CPCB, @ V_JE
END

SELECT @ V_ID = @ V_ID + 1

IF (@ V_STR not like "[0-9]")
SELECT @ V_ID = 1

EXECUTE @ V_ID = DELETE_CB @ P_YY, @ P_NN

Update test set cpcb = 100 where yy = @ P_YY and nn = @ P_NN
IF @ ERROR! = 0
PRINT 'Update failed'

RETURN 0

END

Appendix:
CONVERT (INT, @ V_JE) -- type Conversion Function
LTRIM (@ V_STR) -- remove the left space
RTRIM (@ V_STR) -- remove the right space
ROUND (@ V_JE, 2) -- number of decimal places
SUBSTRING (@ V_STR, M, N) -- Take the SUBSTRING
STR (@ V_JE, M, N) -- set the number of digits and decimal places of the value.
CHAR_LENGTH (@ V_STR) -- String Length
PATINDEX ("% [0-9] %", @ V_STR) -- obtains the starting position of the forward character in the string
Patindex ("% [kKmMgGpP] %", @ v_str)
-- Case statement
CASE @ V_ID WHEN 0 THEN 'no' WHEN 1 then' YES 'else' OTHER 'end
ISNULL (@ V_JE, 0) -- non-null value judgment
-------------------------
In addition:
About transaction processing:
In ORACLE, you do not need to open the transaction explicitly. You can directly commit the transaction, and you can submit the transaction multiple times in a step-by-step manner without the need to appear in pairs.
SYBASE must open and commit transactions explicitly and appear in pairs. In normal times, the execution of a single update statement is committed by sybase by default as an implicit transaction.