Differences between creating a stored procedure in oracle and sybase

Source: Internet
Author: User

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:

 
 
  1. CREATE OR REPLACE FUNCTION MY_FUNC  
  2. (  
  3. P1 IN MY_TABLE.YY%TYPE,  
  4. P2 IN MY_TABLE.NN%TYPE,  
  5. P3 VARCHAR(100)  
  6. )  
  7. RETURN VARCHAR2 AS  

/* Define a cursor with parameters and a cursor without parameters */

 
 
  1. CURSOR MY_CURSOR1 IS
  2. Select yy, NN, DECODE (FYYSDM, 0, 'Total', 1, 'processing payby', 'other billing method ')
  3. FROM MY_TABLE
  4. Where yy = P1
  5. Group by yy, NN
  6. Order by yy, NN;

/* Define the cursor variable to store records in the cursor dataset */

 
 
  1. V_CURSOR1 MY_CURSOR1 % ROWTYPE;
  2.  
  3. CURSOR MY_CURSOR2 (V_ZYDM MY_TABLE.ZYDM % TYPE, V_FYYSDM NUMBER)
  4. Select yy, NN, ZYDM, NVL (ZYCB, 0)/* convert the NVL function to a null value */
  5. FROM MY_TABLE
  6. Where yy = P1 and nn = P2 and zydm = V_ZYDM and fyysdm = V_FYYSDM
  7. Group by yy, NN;
  8.  

/* Define the cursor variable to store records in the cursor dataset */

 
 
  1. V_CURSOR2 MY_CURSOR2 % ROWTYPE;
  2.  
  3. V_CPDM MY_TABLE.CPDM % TYPE;
  4. V_COUNT NUMBER;
  5. V_BZ VARCHAR2 (2 );
  6. V_CPCB NUMBER (22, 2 );
  7.  
  8. BEGIN
  9. V_BZ: = 1;
  10. Select cpdm into V_CPDM FROM MY_TABLE;
  11. Select cpcb into V_CPCB FROM MY_TABLE where rownum = 1;
  12. IF MY_CURSOR1 % isopen then/* determine whether the cursor has been opened */
  13. CLOSE MY_CURSOR1;
  14. End if;
  15. OPEN MY_CURSOR1;
  16. FETCH MY_CURSOR1 INTO V_CURSOR1;
  17. IF MY_CURSOR1 % notfound then/* The cursor return result is blank */
  18. CLOSE MY_CURSOR1;
  19. RETURN (V_BZ );
  20. End if;
  21. WHILE MY_CURSOR1 % found loop/* The result returned by the cursor is not blank */
  22. V_CPDM: = V_CURSOR1.CPDM;
  23. V_CPCB: = V_CURSOR1.CPCB;
  24. V_COUNT: = 100;
  25. IF V_COUNT = 100 THEN
  26. V_COUNT: = 99;
  27. End if;
  28. FETCH MY_CURSOR1 INTO V_CURSOR1;
  29. End loop;
  30. CLOSE MY_CURSOR1;
  31.  

/* Explicitly open a parameter-based cursor */

 
 
  1. Select cpdm into V_CPDM FROM MY_TABLE;
  2. OPEN MY_CURSOR2;
  3. FETCH MY_CURSOR2 INTO V_CURSOR2;
  4. WHILE MY_CURSOR2 % found loop/* The result returned by the cursor is not blank */
  5. V_CPDM: = V_CURSOR2.CPDM;
  6. V_CPCB: = V_CURSOR2.CPCB;
  7. V_COUNT: = 100;
  8. IF V_COUNT = 100 THEN
  9. V_COUNT: = 99;
  10. ELSE
  11. V_COUNT: = 88;
  12. End if;
  13. FETCH MY_CURSOR2 INTO V_CURSOR2;
  14. End loop;
  15. CLOSE MYCURSOR2;

/* Open the cursor implicitly */

 
 
  1. FOR V_CURSOR2 IN MY_CURSOR2 (V_CPDM, V_CURSOR1.FYYSDM) LOOP
  2. IF V_CURSOR2.CPCB IS NULL THEN
  3. PRINT 'invalid! ';
  4. ROLLBACK;
  5. End if;
  6.  
  7. UPDATE MY_TABLE
  8. Set cpcb = V_CPCB
  9. Where yy = P1 and nn = P2 and cpdm = V_CURSOR2.CPDM;
  10. If SQL % NOTFOUND THEN/* determine whether the execution result of the first sentence exists */
  11. /* Program segment */
  12. End if;
  13. End loop;
  14. V_BZ: = MY_DELETE_CB (P_YY, P_NN );
  15. IF V_BZ <> 0 THEN
  16. PRINT 'failed! ';
  17. End if;
  18. For I INT 1 .. V_COUNT LOOP
  19. /**/
  20. End loop;
  21.  
  22. COMMIT;/* submit a transaction */
  23. RETURN (0);/* RETURN value */
  24. END MY_FUNC;
  25.  
  26. Create or replace procedure SP_MY
  27. (
  28. P_YY IN MY_TABLE.YY % TYPE;
  29. P_NN NUMBER;
  30. )
  31. IS
  32.  
  33. CURSOR MY_CURSOR IS
  34. SELECT CPCB
  35. FROM MY_TABLE
  36. Where yy = P_YY and nn = P_NN;
  37.  
  38. V_ZYCB NUMBER (22, 2 );
  39.  
  40. BEGIN
  41. /**/
  42. /* No return value */
  43. END;
  44.  

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

Related Article

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.