PL/SQL summary, plsql Summary

PL/SQL summary, plsql Summary
I. Basic syntax
The structure blocks of PL/SQL are as follows:
Declare
-- Declare variables, types, and cursors
Begin
-- The execution part of the program, similar to the main method
Exception
-- Provides a mechanism for handling exceptions.
-- When... then ....
End;
Note that you do not need to write the useless part. For example, if the program does not have any exceptions to handle, the exception will not be written.
Recommended naming method:
Example of an identifier naming rule
Program variable V_name
Program constant C_Name C_company_name
Cursor variable Name_cursor Emp_cursor
Exception identifier E_name e_too_timeout
Table type Name_table_typeEmp_record_type
Table Name_table Emp
Record type Name_recordEmp_record
SQL * Plus substitution variable P_name P_sal
Bind the variable G_name G_year_sal
HelloWorld Demo:

begin  dbms_output.put_line('HelloWorld');end;

Output the salary of 100 employees:

Declare v_sal number (20): = 0; -- declare a variable. Note that the data type corresponds to the Data Type in the table to be queried. -- a default value is attached ,: = v_email varchar2 (20); -- another method, 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 In the employees table -- % type. If the precision of the custom type is insufficient, % type is used to dynamically obtain the type and precision of the table. begin select salary, email, hire_date into v_sal, v_email, v_hire_date from employees where employee_id = 100; -- Implement the select Operation, note that select into dbms_output.put_line (v_sal | ',' | v_email | ',' | v_hire_date); -- print end;

Modify data

declare     v_emp_id employees.employee_id%type;begin     v_emp_id :=100;     update employees     set salary = salary + 100     where employee_id = v_emp_id;     dbms_output.put_line('OK');end; 

Use % TYPE
Defines a variable. Its data type is the same as that of a defined data variable or the data type of a column in a database table.
In this case, % TYPE can be used.
The advantages of using the % TYPE feature are:
You do not need to know the Data Type of the referenced database column;
The data type of the referenced database column can be changed in real time.
Use % ROWTYPE
PL/SQL provides the % ROWTYPE operator to return a record type. The data type is consistent with the data structure of the database table.
The advantages of using the % ROWTYPE feature are:
You do not need to know the number and Data Type of columns in the referenced database;
The number and Data Type of columns in the referenced database can be changed in real time.
Ii. Record type
Type... is record (,,);

DECLARE -- DECLARE a record type, similar to the TYPE emp_record is record (v_sal employees. salary % type, v_email employees. email % type, -- use a comma to link 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; substring (v_emp_record.v_sal | ', '| v_emp_record.v_email |', '| v_emp_record.v_hire_date); END;

Obtains the record types of all columns in a table.

Declare v_emp_record employees % rowtype; v_emp_id number (10); begin v_emp_id: = 100; select * into v_emp_record from employees where employee_id = v_emp_id; values (records | ', '| v_emp_record.last_name); -- use the column name in the table to end;

3. Process Control:
Conditional judgment: (two types)
Method 1: if... then elseif then... else... end if;
Method 2: case... when... then... end;
Loop Structure: (3)
Method 1: loop... exit when... end loop;
Method 2: while... loop... end;
Method 3: for I in... loop... end loop;
Keywords: goto (similar to the break in java followed by a tag to jump out of that loop), exit
Condition determination:
-- Query the salary of employee No. 150. If the salary is greater than or equal to 10000, print 'salary> = 100 ';
-- If the value ranges from 5000 to 10000, '2018010' <= salary <20180101' is printed; otherwise, 'salary <20180101' is printed'
Method 1

declare     v_sal employees.salary%type;     v_result varchar2(20);begin     select salary into  v_sal from employees where employee_id = 150;     if v_sal >= 10000 then v_result := 'salary >= 10000';     elsif v_sal >=5000 then v_result := 'salary >= 5000';     else v_result := 'salary < 5000';     end if;     dbms_output.put_line(v_sal||' , '||v_result);end;

Method 2

declare     v_sal employees.salary%type;     v_result varchar2(20);begin     select salary into  v_sal from employees where employee_id = 150;     v_result :=     case trunc(v_sal/5000) when 2  then 'salary >= 10000'                            when 1  then '5000 <= salary < 10000'                            when 0  then 'salary < 5000'     end;     dbms_output.put_line(v_sal||' , '||v_result);end;

Case... when... then... is equivalent to a java switch, which has limitations. It is better if... then... elsif... then.
Note that if... then must be followed by a semicolon (;), while case... when... then cannot be added. The semicolon (;) problem is more difficult in PL/DQL.
Loop Structure:
-- Print 1-100 using a loop Statement (three methods)
-- (1) initialization (2) Cyclic body (3) Cyclic condition (4) iteration Condition

Declare -- (1) v_ I number (3): = 1;/* method 1 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 row must have a semicolon (;) end; */Method 2: (recommended) begin while v_ I <= 100 loop dbms_output.put_line (v_ I); v_ I: = v_ I + 1; end loop; end;/* method 3 begin for I in 1 .. 100 loop -- for I in reverse 1 .. 100 loop, this is the opposite. From 100-1, note that there are two ". "dbms_output.put_line (I); end loop; end ;*/

Output all prime numbers from 1

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_flag := 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;

Nesting of the while loop. Pay special attention to the initialization conditions.

Iv. Use of cursors (similar to java iterator)
It is mainly used to process 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); -- declare 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 the cursor emp_sal_cursor; -- extract the cursor fetch emp_sal_cursor into v_emp_record; while emp_sal_cursor % found loop values (values | ':' | values); fetch values into v_emp_record; end loop; close emp_sal_cursor; end;

The use of cursors is easier to use with.

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;

Parameter-based cursor

Declare -- defines the cursor emp_sal_cursor (dept_id number, sal number) is select salary + 1000 sal, employee_id id from employees where department_id = dept_id and salary> sal; -- Define the base variable temp number (4, 2); begin -- process the cyclic Operation of the cursor for c in emp_sal_cursor (sal => 4000, dept_id => 80) loop -- judge the employee's salary and perform the update operation -- dbms_output.put_line (c. id | ':' | c. sal); if c. sal <= 5000 then 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;

Implicit cursor
Implicit cursor property
SQL % FOUND: Boolean attribute. The value is TRUE if the last read record is returned successfully;
SQL % NOTFOUND Boolean attribute, opposite to % FOUND;
SQL % ROWCOUNT numeric attribute, returns the number of records read from the cursor;
SQL % ISOPEN Boolean attribute. The value is always FALSE. Close the implicit cursor immediately after the SQL command is executed.
-- Update the information of a specified employee. If the employee is not found, print "No such employee.

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 (' '); end if; end;

5. Exception Handling (three types)
Exception Handling Concept
EXCEPTION Handling (EXCEPTION) is used to handle unexpected events during normal execution. The EXCEPTION handling of the program block is a predefined error.
And custom errors. Once a PL/SQL block has an exception and does not point out how to handle it, the program will automatically terminate the entire program.
There are three types:
1. Predefined Error
There are about 24 predefined ORACLE exceptions. To handle this exception, you do not need to define it in the program.
.
2. Predefined Error
That is, other standard ORACLE errors. To handle this exception, you need to define it in the program, and then ORACLE automatically
.
3. user defined (User_define) Error
During program execution, the programmer may think that the program is abnormal. To handle such exceptions, you must define them in the program,
Then explicitly raise it in the program.
Basic Structure of exception handling:

EXCEPTIONWHEN first_exception THEN <code to handle first exception >WHEN second_exception THEN <code to handle second 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 exception

Declare v_sal employees. salary % type; begin select salary into v_sal from employees where employee_id> 100; variance (v_sal); exception when too_many_rows then limit ('too many lines to be output! '); When others then dbms_output.put_line ('other errors! '); End;

Non-predefined exception
The procedure is as follows:
1. Define exceptions in the definition section of PL/SQL blocks:
<EXCEPTION> EXCEPTION;
2. Associate the defined exceptions with standard ORACLE errors and use the PRAGMA EXCEPTION_INIT statement:
PRAGMA EXCEPTION_INIT (<exception status>, <error code> );
3. handle exceptions in PL/SQL blocks.

SQL> delete employeeswhere employee_id = 100ORA-02292: violation of the complete constraints (SCOTT. deleted)-the Child record has been found because the manager_id In the table also points to the employee_id In the table, and the emp_id is deleted. Because the manager_id points to, the declare e_deleteid_exception exception cannot be deleted, -2292); -- associate the 2292 error with the custom exception name begin delete employees where employee_id = 100; exception when e_deleteid_exception then dbms_output.put_line ('Integrity violation constraint exception! '); End;

Custom Exception Handling

When an error related to an exception occurs, it is implicitly triggered. User-defined exception errors are explicitly
It is triggered by RAISE statements. When an EXCEPTION error is thrown, the control is redirected to the EXCEPTION part of the EXCEPTION block, where the execution error occurs.
Management Code.
The procedure is as follows:
1. Define exceptions in the definition section of PL/SQL blocks:
<EXCEPTION> EXCEPTION;
2. RAISE <exception>;
3. handle exceptions in PL/SQL Blocks
On the basis of Non-predictive definition exceptions

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 = 100; select salary into v_sal from employees where employee_id = 100; if v_sal> 10000 then raise e_too_high_sal_exception; -- throw an exception end if; exception when e_too_high_sal_exception then hour ('salary too high '); when e_deleteid_exception then hour ('Integrity violation exception exception'); when others then dbms_output.put_line ('other exception'); end;

1-5 as the foundation
Vi. storage functions and stored procedures

The only difference between a process and a function is that a function always returns data to the caller, while a process does not return data.
That is, stored functions (with return values) and stored procedures (without return values)
----- Storage function:

Create Function 1. syntax for creating nested functions: 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 variables> BEGINFUNCTION_bodyEXCEPTION other statements END;

Note:
1) or replace is optional. With it, you can create a new function or replace a function with the same name without conflict.
2) The function name is followed by an optional parameter list, which contains the IN, OUT, or in out Mark. parameters are separated by commas. IN Parameters
Mark indicates that the value passed to the function does not change during the function execution. The OUT mark indicates that a value is calculated in the function and passed through this parameter.
The number is passed to the call statement. The in out mark indicates that the value passed to the function can be changed and passed to the call statement. If the mark is omitted, the parameter
The hidden value is IN.
3) because the function needs to RETURN a value, RETURN contains the data type of the returned result.
-- Storage function structure

Create or replase function func_name (depart_id number, salary number) return number -- return all salaries of a department is -- function in use, variable to be declared, record type record, cursor cursorbegin -- function execution body exception -- handle Abnormal end During function execution;

Hello, World demo

create or replace function HelloDemoreturn varchar2isbegin       return 'Hello,World';end;

The Function created is displayed, indicating that the Function has been created.
Call a function
Method 1:

begin  dbms_output.put_line(HelloDemo);end;

Method 2:

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*/

Obtain System Time Functions

create or replace function get_sysdatereturn dateis   v_date date;begin       v_date := sysdate;       return v_date;end;select get_date from  dual;

Define the function for adding two numbers

Create or replace function add_func (v_num1 number, v_num2 number) return numberis v_sum number (10); -- specify the length of begin v_sum: = v_num1 + v_num2; return v_sum; end; select add_func (1, 2) from dual;

Obtains the total salary of a 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 OUT parameters
Because a function can only have one return value, PL/SQL programs can use the OUT parameter to implement multiple return values.

Obtains the sum of salaries and total employees of a specified department.

Create or replace function get_all_sal (dept_id number, emp_num out number) -- Note out here return numberis v_sumsal number (): = 0; cursor salary_cursor is 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 indicates the end of the Department;

----- Stored procedure:
-- Defines the stored procedure to realize the salary of a specified department (through the out parameter). The Department id and total salary are required as parameters.

Create or replace procedure get_sal (dept_id number, v_sal out number) -- The returned value is cursor salary_cursor is 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); -- directly call end;

Because the stored procedure does not require a return value, you can define the Stored Procedure for the addition, deletion, and modification operations.
VII. triggers
Triggers are a technology provided by many relational database systems. In the ORACLE system, triggers are similar to processes and functions.
PL/SQL statement during declaration, execution, and Exception Handling
A helloworld-Level Trigger

create or replace trigger hello_triggerafter update on employees--for each rowbegin     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 key points