PL/SQL Summary

first,. Basic Syntax
the structure block for PL/SQL is as follows:

Declare
--declaring variables, types, cursors
Begin
--part of the program execution, similar to the Main method
exception
--a mechanism for how to deal with exceptions
--when ....
End
Note that there is no use of the part, there is no need to write, such as the program is really no exception to deal with, then exception will not write

recommended naming methods:
Identifier Naming rules Example
Program variables V_name V_name
Program Constants C_name C_company_name
Cursor variable Name_cursor Emp_cursor
Exception ID E_name E_too_many
Table type Name_table_typeEmp_record_type
Table Name_table Emp
Record type Name_recordEmp_record
Sql*plus substitution Variable P_name P_sal
Binding variables G_name G_year_sal

HelloWorld Demo:

Begin  Dbms_output.put_line (' HelloWorld '); end;

output 100 Employee's salary:

DECLARE  v_sal number: =0;    --declaring variables, note that the data type and the data type in the table to be queried correspond  --with a default value: =   v_email varchar2 (20);--another notation, v_email employees.email% type  v_hire_date date;    --v_hire_date employees.hire_date% Type   //v_hire_date is the same as Hire_date type in Employees table--%type, if the precision of the custom type is not enough, then% Type is the dynamic acquisition of types and precision in a table begin   select Salary,email, hire_date into V_sal,v_email,v_hire_date from   employees where employee_id = +;  --Implement Select operation, note is select INTO  dbms_output.put_line (v_sal| | ', ' | | v_email| | ', ' | | V_hire_date);--print end;

Modifying Data

DECLARE     v_emp_id employees.employee_id%type;begin     v_emp_id: =100;     Update Employees     Set salary = salary +     where employee_id = v_emp_id;     

using%type
Defines a variable whose data type is the same as the type of a data variable that has already been defined, or a column of a database table with a data type
Same, you can use%type.
The advantage of using the%type feature is that:
The data type of the referenced database column can be unnecessary to know;
The data type of the referenced database column can be changed in real time.
using%rowtype
PL/SQL provides the%rowtype operator, which returns a record type whose data type is consistent with the data structure of the database table.
The advantage of using the%rowtype feature is that:
The number of columns in the referenced database and the data type can be unnecessary to know;
The number of columns and data types in the referenced database can be changed in real time.
second, record type
Type ... is record (,,);

DECLARE  -Declares a record type, similar to the class in Java type Emp_record is record (      v_sal employees.salary% type,      v_email Employees.email% type,--comma-linked      v_hire_date employees.hire_date% type);--semicolon   --defines a record variable   v_emp_record emp_ Record BEGIN      SELECT salary, email, hire_date into V_emp_record from      employees WHERE employee_id = 101;      Dbms_output.put_line (v_emp_record.v_sal| | ', ' | | v_emp_record.v_email| | ', ' | | V_emp_record.v_hire_date); END;

gets the record type of all columns in a table

DECLARE     V_emp_record Employees%rowtype;     V_EMP_ID number (ten); Begin     v_emp_id  : =;     SELECT * into V_emp_record from     employees     where employee_id = v_emp_id;     

third, Process Control:
Condition judgment: (Two kinds)
Way one: If. Then ElseIf then...else...end if;
Way two: Case...when...then. End
Cycle structure: (Three kinds)
Mode one: Loop...exit when ... end loop;
Way two: while. Loop...end;
Way three: For I in.. Loop.. End Loop;
Keywords: goto (similar to break in Java followed by a label, jump out of that loop), exit
Condition Judgment:
--Query the salary of Employee No. 150th, if its salary is greater than or equal to 10000, print ' salary >= 10000 ';
--If between 5000 and 10000, print ' 5000<= salary < 10000 '; Otherwise print ' Salary < 5000 '
Way One

DECLARE     v_sal Employees.salary%type;     V_result varchar2 (); Begin     Select salary into  v_sal from employees where employee_id =;     If V_sal >= 10000 then V_result: = ' salary >= 10000 ';     elsif v_sal >=5000 then v_result: = ' salary >= ';     else V_result: = ' salary < ';     End If;     Dbms_output.put_line (v_sal| | ', ' | | V_result); end;

Mode two

DECLARE     v_sal Employees.salary%type;     V_result varchar2 (); Begin     Select salary into  v_sal from employees where employee_id =;     V_result: = Case     trunc (v_sal/5000) while 2 then  ' salary >= 10000 ' when                            1 then  ' <= salary < 10 ' When                            0 then  ' salary < '     end;     Dbms_output.put_line (v_sal| | ', ' | | V_result); end;

Case...when. Then.. The equivalent of a switch in Java, with limitations, or if. Then.. Elsif...then is better than that.
Note that if: Then each time after the "semicolon" is added, and case. When: Then can not add, semicolon problem in pl/dql comparison pit
Loop structure:
--use circular statements to print 1-100. (Three ways)
--(1) Initialize (2) loop body (3) Cycle condition (4) Iteration condition

DECLARE       --(1)       v_i number (3): = 1;/* mode A begin       Loop       -(2)           dbms_output.put_line (v_i);           --(4)          v_i: = v_i + 1;       --(3)       exit when V_i >100;       End  loop;--Note that each line must have a "semicolon" end;*/Way Two: (recommended) Begin       while v_i <= loop             dbms_output.put_line (v_i);             V_i: = v_i + 1;        End loop;end;/* mode Three begin for        i in 1..100 loop--for i in reverse 1..100 loop, this is reversed, from 100-1, also note that in the back is two "."            Dbms_output.put_line (i);        End loop;end;*/

all prime numbers for output 1-100

DECLARE  v_i number (3): = 2;  V_j Number (3): = 2;  V_flag Number (3): = 1;begin  /*while v_i<=100 Loop while          v_j <= sqrt (v_i) loop              if mod (v_i,v_j) =0 then V_f Lag: = 0;              Goto OK;              End If;          V_j: = V_j + 1;              End Loop;  <<OK>>  If V_flag = 1 then dbms_output.put_line (v_i);  End If;  V_j: = 2;  V_i: = v_i + 1;  V_flag: = 1;  End Loop;  */For  I in 2..100 loop for      J in 2..sqrt (i) Loop          if mod (i,j) =0 then V_flag: = 0;          Goto OK;          End If;      End Loop;      <<OK>>                  If V_flag = 1 then dbms_output.put_line (i);      End If;      V_flag: = 1;  End Loop;      End

For nesting of while loops. In particular, be aware of the initialization conditions

Iv. use of cursors (similar to Java iterator)
It is primarily convenient for processing multiple rows of data.

Declare  --Define a record type  V_record is record (       v_sal employees.salary%type,       v_last_name employees.last _name%type  );  --Declaring a record type  V_emp_record V_record;  --Define a cursor  emp_sal_cursor is select Salary,last_name from employees where department_id = 80;begin  --Open cursor C10/>open Emp_sal_cursor;  --Extracting cursor  fetch emp_sal_cursor into V_emp_record;  While Emp_sal_cursor%found Loop        dbms_output.put_line (v_emp_record.v_last_name| | ': ' | | V_emp_record.v_sal);        Fetch emp_sal_cursor into V_emp_record;  End Loop;  Close emp_sal_cursor;end;

Use of cursors, with for a relatively simple

Declare  cursor emp_sal_cursor is select Salary,last_name from employees where department_id = 80;begin for  c in Emp_sal_cursor Loop       dbms_output.put_line (c.last_name| | c.salary);      End Loop;end;

Cursors with Parameters

Declare    --definition cursor cursor    emp_sal_cursor (dept_id number, sal number)            is select Salary + sal, employee_id id< C3/>from employees            where department_id = dept_id and Salary > sal;        --Define the cardinality variable    temp number (4, 2); Begin    -handles the loop operation of the cursor for the    C in emp_sal_cursor (sal = 4000, dept_id =) loop
   --Judge Employee's salary, perform update operation          --dbms_output.put_line (C.id | | ': ' || C.sal);                    If C.sal <= and             temp: = 0.05;          elsif c.sal <= 10000 then             temp: = 0.03;             elsif c.sal <= 15000 then             temp: = 0.02;          else             Temp: = 0.01;          End If;                    Dbms_output.put_line (C.sal | | ': ' || C.id | | ', ' || temp);          --update Employees Set Salary = Salary * (1 + temp) where employee_id = C.id;    End Loop;end;

an implicit cursor
Implicitly-cursor Properties
Sql%found Boolean property, TRUE if the record was last read successfully;
Sql%notfound boolean attribute, opposite to%found;
SQL%rowcount Numeric property that returns the number of records that have been read from the cursor;
SQL%isopen Boolean property, always evaluates to FALSE. The SQL command closes an implicit cursor immediately after execution.

--Updates the specified employee information and prints the "No this person" message if the employee is not found.

DECLARE   v_sal Employees.salary%type;  V_empid Employees.employee_id%type: = 101;begin  Update Employees  Set last_name = ' ABC '  where employee_id = V _empid;  If Sql%notfound then--     dbms_output.put_line (' No this person ');  End If;end;

v. Exception handling (three types)
Exception Handling Concepts
Exception Handling (EXCEPTION) is used to handle unexpected events during normal execution, and program block exceptions handle pre-defined errors
and custom errors, the program automatically terminates the entire program as soon as the PL/SQL block generates an exception and does not indicate what to do with it
There are three types of:
1. Pre-defined (predefined) errors
There are approximately 24 pre-defined exception cases for ORACLE. The handling of this anomaly is not defined in the program, and is automatically by ORACLE
to throw it.
2. Non-pre-defined (predefined) errors
That is, other standard ORACLE errors. The handling of this exception requires the user to define it in the program, and then the ORACLE automatically
The trigger.
3. User-defined (user_define) error
During the execution of the program, it appears that the programmer considers the abnormal situation. The handling of this anomaly requires the user to define it in the program,
It is then explicitly raised in the program.

the basic structure of exception handling:

Exceptionwhen First_exception then <code-handle first exception >when second_exception then <code to handle SE Cond exception >when OTHERS then <code to handle OTHERS exception >END;

Exception handling can be arranged in any order, but OTHERS must be placed at the end.

pre-defined exceptions

DECLARE  v_sal employees.salary%type;begin  Select salary into v_sal from employees  where employee_id > ;  Dbms_output.put_line (v_sal); exception when  too_many_rows then Dbms_output.put_line (' Too many lines to output! ');  When others then Dbms_output.put_line (' other error! '); End

non-pre-defined exception

The steps are as follows:
1. Define the exception condition in the definition section of the PL/SQL block:
< abnormal situation > EXCEPTION;
2. Use the PRAGMA Exception_init statement to associate its defined anomalies with standard ORACLE errors:
PRAGMA Exception_init (< exception;, < error code >);
3. In the Exception handling section of the PL/SQL block, the exception is dealt with accordingly.

Sql>delete Employeeswhere employee_id = 100ora-02292: Violates the full constraint (SCOTT. DEPT_MGR_FK)-Child records have been found because their own table has manager_id also point to their own table employee_id, and delete emp_id, because manager_id point, so or can not delete declare  E_deleteid _exception exception;  pragma exception_init (e_deleteid_exception,-2292);--Associate 2292 This error with your own defined exception name begin  Delete employees  where employee_id = 100;exception when  e_deleteid_exception then Dbms_output.put_line (' Violation of integrity constraint exception! '); End

User-defined exception handling

When an error related to an exception error occurs, it is implicitly triggered by the exception error. User-defined exception errors are caused by explicitly
Use the RAISE statement to trigger. When an exception error is thrown, the control is shifted to the EXCEPTION block exception error section, where the execution error
Management code.

The steps are as follows:
1. Define exception conditions in the definition section of the PL/SQL block:
< abnormal situation > EXCEPTION;
2. RAISE < abnormal conditions >;
3. Handling exception cases in the exception handling section of PL/SQL blocks

On the basis of non-prophecy definition anomalies

declare  e_deleteid_exception exception;  pragma exception_init (e_deleteid_exception,-2292);  E_too_high_sal_exception exception; --Custom exception  v_sal employees.salary% type;begin   Delete from employees where employee_id =;  Select salary into V_sal from employees where employee_id = +;  If V_sal > 10000 then   raise e_too_high_sal_exception;   --Throw exception  end if;exception when  e_too_high_sal_exception then Dbms_output.put_line (' pay too high ');  When E_deleteid_exception and then dbms_output.put_line (' Violation of integrity constraint exception ');  When others and then dbms_output.put_line (' other anomalies '); end;

1-5 basis in the foundation

vi. storage functions and stored procedures

The only difference between a procedure and a function is that the function always returns data to the caller, and the procedure does not return data.
Store function (with return value), stored procedure (no return value)

-----Storage Functions:

Create the function 1.  Create the inline function syntax as follows: Create [OR REPLACE] Function function_name[(argment [{in | In Out}] type,argment [{in | Out | In Out}] Type [AUTHID definer | Current_User]return return_type{is | As}< type. Description of the variable >beginfunction_bodyexception other statement end;

Description:
1) OR REPLACE is optional. With it, you can either create a new function or replace a function with the same name without any conflict
2) The function name is followed by an optional parameter list that contains in, out, or out tags. The parameters are separated by commas. In Parameter
The token indicates that the value passed to the function does not change in the execution of the function; The out tag indicates that a value is evaluated in the function and passed through the parameter
The number is passed to the calling statement;  The in out token indicates that the value passed to the function can vary and be passed to the calling statement. If the tag is omitted, the parameter
Implied as in.
3) because the function needs to return a value, return contains the data type that returns the result.

--Storage function structure

Create or Replase function Func_name (depart_id number, salary number) return number--returns the salary of a department is            --function in the process of use, The variables that need to be declared, the record type records, the cursor Cursorbegin       --The execution body of the function exception       --the exception end in the process of executing the function;

Hello,world Demo

Create or Replace function Hellodemoreturn varchar2isbegin       return ' hello,world '; end;

Displays the function created, stating that the functions have been created

calling Functions
Way One:

Begin  Dbms_output.put_line (Hellodemo); end;

Way two:

Sql>select Hellodemo from dual; /*create or Replace function Hellodemo (V_world varchar2) return varchar2isbegin       return ' Hello ' | | V_world;end;*//*select Hellodemo (' Worrld ') from dual*/

Get system Time function

Create or Replace function Get_sysdatereturn Dateis   v_date date;begin       v_date: = sysdate;       Return V_date;end;select get_date from  dual;

Defines a function that adds two numbers

Create or Replace function Add_func (v_num1 number,v_num2 number) return Numberis   v_sum number (10);--Here you need to specify the length begin       v_sum: = V_num1 + v_num2;       Return v_sum;end;select Add_func from dual;

Gets the sum of the wages for the specified department

Create or Replace function Get_all_sal (dept_id number) return Numberis   v_sumsal number (20,2): = 0;      Cursor Salary_cursor is select salary from employees where department_id = Dept_id;begin for       c in Salary_cursor loop
   v_sumsal: = V_sumsal + c.salary;       End Loop;              return v_sumsal;end;

about parameters of out shape
Because a function can have only one return value, the PL/SQL program can implement multiple return values through an out parameter

Gets the sum of the wages of the specified department, the sum of personnel

Create or Replace function get_all_sal (dept_id number,emp_num out number)--note out here
return number
Is
V_sumsal number (20,2): = 0;

Cursor Salary_cursor is a select salary from employees where department_id = dept_id;
Begin
Emp_num: = 0;
For C in Salary_cursor Loop
V_sumsal: = V_sumsal + c.salary;
Emp_num: = Emp_num + 1;
End Loop;

return v_sumsal;
End


Output


Declare
V_num Number (3): = 0;
Begin
Dbms_output.put_line (Get_all_sal (80,v_num));
Dbms_output.put_line (V_num); --v_num is the number of departments
End

-----Stored procedures:


--Define a stored procedure that implements the payroll (through out parameters) of the specified department, requiring the department ID and the total payroll as parameters


Create or Replace procedure Get_sal (dept_id number,v_sal out number)--No return value required
Is
Cursor Salary_cursor is a select salary from employees where department_id = dept_id;
Begin
V_sal: = 0;
For C in Salary_cursor Loop
V_sal: = V_sal + c.salary;
End Loop;
Dbms_output.put_line (v_sal);
End




Declare
V_sal Number (10): = 0;
Begin
Get_sal (80,v_sal); --Direct call can
End


Because the stored procedure does not need to return a value, you can define the stored procedure by adding or deleting the operation






Seven, Trigger


Triggers are a technique that is provided by many relational database systems. In ORACLE systems, trigger-like processes and functions have
PL/SQL for declaration, execution, and exception handling procedures


A HelloWorld-level trigger
Create or Replace Trigger Hello_trigger
After
Update on employees
--for each row
Begin
Dbms_output.put_line (' Hello ... ');
--dbms_output.put_line (' old.salary: ' | |: old.salary| | ', New.salary ' | |:new.salary);
End
Then execute: Update employees Set salary = salary + 1000;


6-7 Highlights

PL/SQL Summary