Oracle Stored Procedure Fundamentals

• Statement block definition:

SQL code

1. Decalre
2. --Variable declaration
3. VAR1 number (2); --declaration only
4. Var2 char (2): = ' 11 '; --Initialize at the same time as the declaration
6. Begin
7. --Statement
8. End --end of statement block

• If statement

SQL code

1. If a = 1 or b = 2 Then
3. elsif c = 3 Then
5. Else
7. End If;

• Case statement

If the case statement is a branch control statement, the last closing sentence is the end case, and if it is a control statement in the SELECT statement, only the end is required.

SQL code

2. Declare
4. Num Number (10): = 1;
6. Begin
8. Case
10. When num = 0 Then dbms_output.put_line ( ' zero ');
12. When num = 1 then dbms_output.put_line ( ' one ');
14. Else Dbms_output.put_line ( ' default ');
16. End Case ;
20. Case Num
22. When 0 then dbms_output.put_line ( ' zero ');
24. When 1 then dbms_output.put_line ( ' one ');
26. Else Dbms_output.put_line ( ' default ');
28. End Case ;
30. End

• For loop

There are two main uses for a for loop.

1. Cycle a Range
Format: For i in [start. end] loop ... end loop;

SQL code

1. For i in 0..9 loop
2. Dbms_output.put_line (' I: ' | | i);
3. End Loop;

2. Traversing an implicit cursor

The benefit of an implicit cursor is that it does not need to be closed manually, facilitating

SQL code

1. For Currow in (
2. Select T.col1, T.col2
3. From tableName T
4. where ...
5. ) loop
6. If currow.col1 = 0 Then
7. return; --Abort SP, return
8. End If;
9. End Loop;

• While loop

SQL code

2. isOK: = 9;
4. While isOK >= 0 loop
6. isOK: = isok-1;
10. If isOK = 8 Then
12. continue; --like the continue semantics of the programming language, skip the remaining statements of the current loop and return to the beginning of the loop
14. End If;
18. If isOK = 4 Then
20. Exit --like the break semantics of programming languages, jump out of the loop
22. End If;
26. Dbms_output.put_line (' isOK: ' | | isok);
28. End Loop;
32. Dbms_output.put_line (' outside while loop. ');

• Stored Procedure Definitions

SQL code

2. Create or replace procedure sp_name (
4. --entry, parameter list, comma delimited.
6. UID in Varchar2, --not with length information
8. StartDate in date, --second input parameter
10. Defaultvar in varchar2 Default "", --defaults, if not passed, note the order of the parameters
12. isOK out number, --Output parameter
14. Result out VARCHAR2 --Second output parameter
16. )
18. As
20. --variable declaration, with each declaration ending with a semicolon. Can be initialized at the same time as the declaration
22. Var1 VARCHAR2 (11);
24. VAR2 Number (2): = 123;
28. Begin
30. --string splicing with | |
32. Dbms_output.put_line (' isOK: ' | |  ' abc ');
36. --Call other stored procedures
38. Sub_sp_name (param1, Prarm2, outParam1, outParam2);
42. End --end of stored procedure

• function definition

SQL code

2. Create or replace function func (
4. --entry, parameter list, comma delimited.
6. UID in Varchar2, --not with length information
8. StartDate in date, --second input parameter
10. Defaultvar in varchar2 Default "", --defaults, if not passed, note the order of the parameters
12. isOK out number, --Output parameter
14. Result out VARCHAR2 --Second output parameter
16. )
18. Return number --Define the return type
20. As
22. --variable declaration, with each declaration ending with a semicolon. Can be initialized at the same time as the declaration
24. Var1 VARCHAR2 (11);
26. VAR2 Number (2): = 123;
30. Begin
32. --string splicing with | |
34. Dbms_output.put_line (' isOK: ' | |  ' abc ');
40. return ret_val;
42. End

• Similarities and differences between stored procedures and functions

1, the two definitions are similar, can be with input and output parameters.
2, the function has a return value, the stored procedure does not.
3, the function calls in the SELECT statement, and the stored procedure is not, can be called independently.

• Cursor

an implicit cursor
The benefit of an implicit cursor is that it does not need to be closed manually, facilitating

SQL code

1. For Currow in (
2. Select T.col1, T.col2
3. From tableName T
4. where ...
5. ) loop
6. If currow.col1 = 0 Then
7. return; --Abort SP, return
8. End If;
9. End Loop;

An explicit cursor

SQL code

2. Declare
4. isOK Integer;
6. V_EVENT_ID number (10);
8. V_isagain number (2);
10. V_rate number (2);
14. V_sender Char (one): = ' 13800138000 ';
18. Cursor Cursorvar is a select event_id, Isagain, rate from call_event where sender = V_sender; --Declaring cursors
24. Begin
26. Open Cursorvar; --Open cursor
28. Loop
30. fetch Cursorvar into v_event_id, V_isagain, v_rate; --Take value
32. Exit when Cursorvar%notfound; --Exits the loop when no records are recorded
34. Dbms_output.put_line (v_event_id | | ', ' | | | v_isagain | |  ', ' | | | v_rate);
36. end Loop;
40. Close Cursorvar; --Close cursor
44. --The properties of the cursor are:%found,%notfounrd,%isopen,%rowcount;
46. --%found: Returns True when records have been retrieved
48. --%NOTFOUNRD: Returns True when records are not retrieved
50. --%isopen: Returns True when the cursor is open
52. --%rowcount: Represents the number of records retrieved, starting from 1
54. End

Cursors with parameters

SQL code

2. Declare
4. isOK Integer;
6. V_EVENT_ID number (10);
8. V_isagain number (2);
10. V_rate number (2);
14. V_sender Char (one): = ' 13800138000 ';
18. Cursor Cursorvar (p_sender varchar2) is a select event_id, Isagain, rate from call_event where sender = P_sen Der --Declaring cursors
22. Begin
24. Open Cursorvar (V_sender);  --Open the cursor and pass the argument in parentheses.
26. Loop
28. fetch Cursorvar into v_event_id, V_isagain, v_rate; --Take value
30. Exit when Cursorvar%notfound; --Exits the loop when no records are recorded
32. Dbms_output.put_line (v_event_id | | ', ' | | | v_isagain | |  ', ' | | | v_rate);
34. end Loop;
38. Close Cursorvar; --Close cursor
40. End

Oracle Stored Procedure Fundamentals