PL/SQL notes (1)-process control, loop, exception, block, plsql

PL/SQL notes (1)-process control, loop, exception, block, plsql
Process control 1.If, then, else, elsif (not elseif)

 
if a='1' then  null;endif;
 
2. Simple Case expression: search-type case expression:
 

3. goto statement

beginif true then  goto label2;end if;<<label1>>  SYS.DBMS_OUTPUT.PUT_LINE('label1');  <<label2>>  SYS.DBMS_OUTPUT.PUT_LINE('label2');end;

 

Loop Control simple loop: Exit, Exit when condition Exit Loop

While loop:

 

For Loop: two types: for and for (...) for the cursor: for the range operator, 1 .. 5 indicates 1 to 5.

beginFOR j IN 1..5 LOOP    dbms_output.Put_line(j);  END LOOP;END;end;

Cursor-oriented:

declarecursor myCursor is select * from ouser;beginFOR s IN myCursor LOOP    dbms_output.Put_line(s.userid);  END LOOP;END;

The continue and continue when statements end the current cycle;

-- Output only an even number of beginFOR j IN 1 .. 100 LOOP Continue when Mod (j, 2) = 1; dbms_output.Put_line (j); end loop; end;

Exception Handling 1. Naming and anonymous exceptions

A naming exception has a name. An anonymous exception only contains the Exception Code and message.

SQLCODE function can get the Exception Code of the last exception, SQLERRM: exception message

Declaremyexception exception; -- declare a naming exception v_row Sys_ACC_User % RowType; Pragma EXCEPTION_INIT (myexception,-20002 ); -- bind a naming exception and an exception code to begin select * into v_row from Sys_ACC_User where rownum = 1; raise myexception; -- manually throw an exception RAISE_APPLICATION_ERROR (-20001, ', which is an anonymous exception, I have no name '); -- manually throw an anonymous Exception when no_data_Found then -- catch the Exception dbms_output.Put_line (not data found' | 'Exception Code: '| SQLCODE | 'exception message' | SQLERRM); when myexception then -- catch the exception dbms_output.Put_line ('myexception' |' Exception Code: '| SQLCODE | 'exception message' | SQLERRM); when others then -- Other naming exceptions and anonymous exceptions are caught here dbms_output.Put_line ('exception code: '| SQLCODE | 'exception message' | SQLERRM); end;

 

Oracle block: Block Composition: block header, Declaration unit, execution unit, Exception Handling Unit function. The stored procedure is a block structure and named block.

Create or replace function WordCount (str in varchar2) return number -- block header is numCount number default: = 0; -- declare unit begin -- Execution Unit return Length (LTrim (str, '0'); Exception -- Exception Handling Unit when others then: SYS. DBMS_OUTPUT.PUT_LINE ('error'); end;

Anonymous block anonymous Block No block Header

Declare -- Declaration unit v_n1 varchar2 (100); begin -- Execution Unit v_n1: = '20'; SYS. DBMS_OUTPUT.PUT_LINE (v_n1); exception -- exception Processing Unit when others then SYS. DBMS_OUTPUT.PUT_LINE ('error'); end;

 

Others:

1. Escape: Q' <s 'd> ', which indicates: s 'd. <and> must appear in pairs and can be replaced by (), {}, [], and so on.

2. The Function must return a value. Procedure is not required for returning a value.

3. If Procedure has a parameter (In/Out), the call method is ProcedureName (param1, param2). If procedure does not have a parameter, ProcedureName or ProcedureName (). The Function is similar...

4. The Null Character ''is Null => true

 

 

Http://www.cnblogs.com/zhxj/: zhxjdwh