Section III, PL/SQL programming

1. PL/SQL combines the language of the process language and the query language, which is to add the features of the programming language to SQL, and to realize complex functions or computations through the operation of logic judgment and loop.

[declare] -- declaration section: Declaration of variables, types, cursors, and local stored procedures and functions begin    -- Execution Part: Procedure SQL statement [exception] -- Execute Exception Section End The execution part cannot be omitted. 　　　　

PL/SQL block classification: nameless blocks, named blocks, subroutines, triggers, packages

PL/SQL notation: Assignment: = Connection | | Single-line Comment-Multiline comment/**/range operation.

1.1pl/sql Variable Declaration

V_ename VARCHAR2 (20); --Declaring the type and length of a variable

V_ename VARCHAR2: = ' Tom '; --Declaring and assigning values

Program variables	V_name
Program Constants	C_name
Cursor variables	Cursor_name
Exception flags	E_name
Table type	Name_table_type
Table	Name_table
Record type	Name_record
Binding variables	G_name

Common variable types: char, varchar, binary_integer, number (p,s), Long, date, Boolean

Common reference types:%type,%rowtype

2.1 Process Control

if  <An expression>  Then    --Executive SectionEnd if;if  <An expression>  Then    --Executive Sectionelsif<An expression>  Then    --Executive SectionEnd if; Case       when <Expression 1>  Then        --     when <Expression 2>  Then       --    [Else]End; Case  <An expression>     when <Expression result 1>  Then        --     when <Expression Result 2>  Then       --    [Else]End;

--LoopsLoop--the executed statementExit  when <Conditional statements> --meet conditional Exit loopsEndLoop; forLoop counterinch【Reverse"Lower limit ... Upper Loop--EndLoop;--Example:1. for inchLoop FormDECLARE    　　 CURSORC_sal is SELECTemployee_id, first_name||last_name ename, Salary fromemployees; BEGIN    　　 --Implicitly open Cursors    　　  forV_salinchc_sal LOOP--implicitly executes a FETCH statementDbms_output. Put_Line (To_char (v_sal.employee_id)||'---'||V_sal.ename||'---'||To_char (v_sal.salary)); --Implicit monitoring C_sal%notfound    　　 ENDLOOP; --Suppress cursor closure 　　 END;2. Normal cursor LoopsDeclare  --defines a cursor and assigns a value (is cannot be used separately from the cursor)  cursorStus_cur is Select *  fromstudents; --define RowTypeCur_stu Students%RowType; /*Start Execution*/  begin  --Open Cursor  Openstus_cur; --Loop LoopLoop--Cycle Conditions   Exit  whenStus_cur%NotFound; --cursor value assignment to RowType   FetchStus_cur intoCur_stu; --OutputDbms_output.put_line (cur_stu.name); --End Loop   EndLoop; --Close Cursors  Closestus_cur; /*End Execution*/ End;3. Efficient cursor LoopingDeclare 　　 cursorMyemp_cur is Select *  frommyemp; Type Myemp_tab is Table  ofMyemp%RowType;　　Myemp_rd Myemp_tab; begin 　　  Openmyemp_cur; LoopFetchMyemp_curBulkCollect intoMYEMP_RD limit -;  forIinch 1.. Myemp_rd.CountLoop Dbms_output.put_line ('Name:'||myemp_rd (i). ename); EndLoop; Exit  whenMyemp_cur%NotFound; EndLoop; End;

--use of Goto, NULLDecllare V_counter Number:=1;beginLoop Dbms_output.put_line (v_counter); V_conuter:=V_counter+1; ifV_conuter>Ten  Then            GotoLabeloffloop;--jump to a row labeled Lableoffloop    Else        NULL;--in order for the syntax to become meaningful    End if;EndLoop;<<Labeloffloop>>Dbms_output.put_line (v_counter);End;

3, exception handling, common exception handling methods

Common Oracle Pre-defined exceptions

Dup_val_on_index: A database column that stores a unique index repeatedly appears

Invalid_number: Occurs when a string is converted to a number

No_data_found: There are no request rows in the table

Too_many_rows: Occurs when multiple rows are returned after a SELECT INTO statement is executed

Value_error: The column value of a variable exceeds the size of the variable

Zero_divide: Occurs when the divisor is 0

OTHERS: Ensure processing does not leak any exceptions, you can use functions Sqlcode and SQLERRM to return error codes and error text messages.

Custom Exception Usage

Declare     v_name emp.name%type;    E_name_null exception; begin    if  is NULL  Then         raise e_name_null;     End if ; exception      when  Then          dbms_output.line (' name cannot be empty '); end;--Note: Raise_application_error stored procedure, You can redefine the exception error message--raise_application_error (error_number,error_message);

Section III, PL/SQL Programming