Stored Procedure Basics

I think it is necessary to write down the basic things to remember.

• Statement block definition:
  

SQL code

decalre--Variable declaration var1 number (2);          --Declare only var2 char (2): = ' 11 '; --Initialize begin at the same time as declaration--statement end; --End of statement block

• If statement
  

SQL code if a = 1 or b = 2 Thenelsif c = 3 thenelseend 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

Declarenum Number: = 1;begin case If num = 0 Then dbms_output.put_line (' zero ');        When num = 1 then dbms_output.put_line (' one ');    else Dbms_output.put_line (' default ');        End case;        Case num When 0 then dbms_output.put_line (' zero ');        When 1 then dbms_output.put_line (' one ');    else Dbms_output.put_line (' default '); End Case;end;

• For loop

There are two main uses for a for loop.

Loop a range format: For i in [start. end] loop ... end loop;   SQL code for I in 0..9 loop dbms_output.put_line (' I: ' | | i);    End Loop;  For i in 0..9 loop dbms_output.put_line (' I: ' | | i); 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

for currow in  (      select  t.col1, t.col2      from tableName t       where ...   )  loop       if  currow.col1 = 0 then         return;     --  Abort sp, return       end if;   end  loop;  for currow in  (   select t.col1, t.col2    FROM TABLENAME T   WHERE&NBSP, ...)  loop    if currow.col1 = 0 then        return;    --  Abort sp, return    end if;end loop; 

isok := 9;   while isok >= 0 loop          isok := isok - 1;                  if isok = 8 then            continue;                 --  As with the  continue  semantics of programming languages, skips the remaining statements of the current loop, Back to loop start          end if;                  if isok = 4 then            exit;                     --   break  of programming languages Like semantics, jump out of the loop     &NBSp;    end if;           dbms_ Output.put_line (' isOK: '  | |  isok);    end loop;     dbms_output.put_line (' outside  while loop . ');       isok := 9;    while isok >=  0 loop          isok := isok -  1;                    if isok = 8 then              continue;                 --  like the  continue  semantics of programming languages, skip the remaining statements of the current loop and go back to the start of the loop            end if;       &Nbsp;           if isok = 4 then              exit;                     --  and programming languages  break  like semantics, jumping out of loops           end if;           dbms_output.put_line (' isOK: '  | |  isok);     end loop;       dbms_output.put_ Line (' outside while loop . ');

• Stored Procedure Definitions

SQL code

create or replace procedure sp_name  (         --  the entry, argument list,  comma separated.         uid in varchar2,                            --  cannot take length information         startDate in date,                          --  a second input parameter         defaultVar  in varchar2 default  "",        --  default parameter, if not passed, Be aware of the order of the parameters         isok out number,                           --  Output Parameters         result out  varchar2                        --  Second output parameter) as--  variable declaration, with each declaration ending with a semicolon. VAR1 VARCHAR2 (one) can be initialized at the same time as the Declaration; Var2 number (2)  := 123;begin         --  string stitching with  | |         dbms_output.put_line (' isOK: '  | |   ' abc ');                --   Call other stored procedure         sub_sp_name (PARAM1, PRARM2, OUTPARAM1,  OUTPARAM2);end;        --  the end of the stored procedure

• function definition

SQL code

create or replace function func   (         --  Enter the parameter list,  comma separated.         uid in varchar2,                            --  cannot take length information         startDate in date,                          --  a second input parameter         defaultVar  in varchar2 default  "",        --  default parameter, if not passed, Be aware of the order of the parameters         isok out number,                           --  Output Parameters         result out  varchar2                        --  Second output parameter) return number      --   Defines the return type as--  variable declaration, with each declaration ending with a semicolon. VAR1 VARCHAR2 (one) can be initialized at the same time as the Declaration; Var2 number (2)  := 123;begin         --  string stitching with  | |         dbms_output.put_line (' isOK: '  | |   ' abc ');                return  ret_val;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

For Currow in (select T.col1, t.col2 from TableName t where ...) loop if Currow.col1 = 0 then return; --Abort SP, return end If;end loop;

an explicit cursor

SQL code

Declareisok integer;v_event_id number, V_isagain number (2); V_rate number (2); V_sender  char (one)  :=  ' 13800138000 ';cursor cursorvar is select event_id,  isagain, rate from call_event where sender = v_sender; --  Declaring cursors begin    open cursorvar;    --  opening cursors      loop         fetch cursorvar into v_event_id , v_isagain, v_rate;       --  value           exit when cursorVar%notfound;                               --exiting a loop when no records are recorded          dbms_ Output.put_line (v_event_id | |   ',  ' | |  v_isagain | |   ',  '  | |  v_rate);    end loop;       close  cursorvar;   --  the properties of the        --cursor for the close cursor are:%FOUND,%NOTFOUNRD, %isopen,%rowcount;     --%found: Returned true     --% when a record has been retrieved NOTFOUNRD: Return True     --%isopen When a record is not retrieved: returns true     --% when the cursor is open ROWCOUNT: Represents the number of records retrieved, starting from 1  end;

Cursors with Parameters

SQL code

Declare  isok integer;   v_event_id number (   v_isagain);  number (2);    v_rate number (2);      v_sender char (11)  :=  ' 13800138000 ';      cursor cursorvar (P_SENDER VARCHAR2)   is select event_id, isagain, rate from call_event where sender =  p_sender; --  declaring Cursors      begin      open  cursorvar (V_sender);    --  open the cursor and pass the argument in parentheses.        loop             fetch cursorVar into v_event_id, v_isagain, v_rate;        --  value             exit  when cursorVar%notfound;                              --exiting the loop when no records are recorded              dbms_output.put_line (v_event_id | |   ',  '  | |  v_isagain | |   ',  '  | |  v_rate);       end loop;              close cursorVar;   --  Close Cursors     end;

This article is from the "Mr_dai" blog, make sure to keep this source http://8551527.blog.51cto.com/8541527/1606184

Stored Procedure Basics