Introduction to stored procedures, stored functions, and triggers

1, first introduce what is Pl/sql (Procedure language/sql).

Plsql is Oracle's process extension of SQL language, which refers to the addition of process processing statements (such as branching, loops, etc.) in the SQL command language so that the SQL language has process processing capabilities. Combining the data manipulation ability of SQL language with the data processing ability of process language makes the PLSQL process-oriented but simpler, more efficient, more flexible and more practical than the process language.

2. Stored procedures and stored functions:

A subroutine stored in a database that is invoked by all user programs (Pl/sql language programs) is called a stored procedure, a stored function. The biggest difference: the stored function can return the value of the function through the returns statement, and the stored procedure is not available. However, both procedures and functions can specify one or more output parameters through out. We can use out parameters to return multiple values in procedures and functions.

If there is only one return value, use the stored function, otherwise use the stored procedure.

Stored Procedure instance: (with parameters)

/*
  give the specified employee a 100 increase and print the salary before and after the rise/
Create or replace procedure rasisalary (p_id in number)--in (pass in an ID value)
as
  --Define variables to save before rising wages
  p_salary s_emp.salary%type;
Begin 
  -Get a raise before a salary
  select salary into P_salary from s_emp where id=p_id;
  --It is  not necessary to commit the call to the stored procedure;
  Update s_emp set salary=salary+100 where id=p_id;
  
  Dbms_output.put_line (' Pre-rise Salary: ' | | p_salary| | ' Post-rise Salary: ' | | (p_salary+100));
  End;

The stored procedure can be compiled in the following two ways:

--Call stored procedure
1.exec rasisalary (23);--Incoming employee number 2 with ID 23

. Begin
    Rasisalary;
    commit;
 End;
    /

Execute the stored procedure to print the salary of employee number 23rd before and after the rise.

Stored function instances:

--Query an employee's yearly income
Create or replace Function queryincom (p_id in number) return number
  AS--Define variable save monthly salary and bonus
  p_salary S_emp.salary%type;
  p_pct S_emp.commission_pct%type;
Begin
  --query
  select salary,commission_pct into p_salary,p_pct from s_emp where id=p_id;
  
  --Returns the yearly income return
  P_SALARY*12+NVL (p_pct,0);
End

Execute the stored function:

Begin
: Result: =queryincom (id =>23);
Dbms_out.put_line (: result);
End;
/

3, Trigger:

A database trigger is a stored Pl/sql program that is associated with a table. Whenever a specific data manipulation statement such as

Additions and deletions When a statement is issued on a specified table, Oracle automatically executes the sequence of statements defined in the trigger.

Statement-level trigger: Executes before or after the specified action statement, regardless of how many rows are affected (for table operations)

/* Trigger application One: Prohibit the insertion of new employees during non-working hours

1. Weekend: To_char (sysdate, ' Day ') in (' Saturday ', ' Sunday ')
2. Before work: To_number (To_char ( Sysdate, ' hh24 ') not between 9 and */
Create or replace trigger Securityemp
before insert on
s_emp
  
   begin
  if To_char (sysdate, ' Day ') in (' Saturday ', ' Sunday ') or
     to_number (To_char (sysdate, ' hh24 ')) is not between 9 and 17< C10/>then
     --Prohibit insert Operation
     raise_application_error (-20001, ' prohibit inserting new employee during non-working hours ');
  End If;

End;
/
  

The insert operation will have an exception after the trigger is compiled at its specified time.

Row-level triggers (for each row): Every record that triggers the action of a statement is triggered. Used in row-level triggers: Old and: New pseudo-record variables, identifying the state of a value

/* Trigger application Two: Data confirmation the
salary can not be less than the salary before the rise/
*
Create or replace trigger Checksalary
before update on
S_ EMP for each
row--row-level triggers
begin
  --if Salary < salary before the rise then
  if:new.salary <: Old.salary then
    raise _application_error (-20002, ' the salary after the rise cannot be less than the salary before the rise. Before the rise: ' | |:o ld.sal| | '   After the rise: ' | |:new.sal;
  End If;
End;
/

When the row-level trigger is compiled, there will be an exception if the employee's payroll is reduced.