Stored procedures, functions, triggers, optimization scenarios

/* Display cursor: Action query result set (operation of query result returned by SELECT statement)
Use: Declare---Open a cursor---Read the cursor---data will continue to read, and no data will be closed.
*/

DECLARE 
 /* Declares a query cursor with parameters
 /cursor cur_emp (var_job in varchar2) was
 select Empno,ename,sal from 
 EMP
 where Job=var_job;

/* Define a record table * *

Type record_emp is record
 (
      var_empno emp.empno%type,
      var_ename emp.ename%type,
      var_sal emp.sal%type
 );

/* Declaration definition of the record variable, in the following use * *

Emp_row record_emp;
 Begin
   /* open cursor, and incoming query parameters
   /Open cur_emp (' MANAGER ');
   /* Crawl cursor data, and put the data into the record type variable * *
   fetch cur_emp into Emp_row;
  /* To determine whether there are records in the result set of the crawl. *
   /while Cur_emp%found loop 
     dbms_output.put_line (emp_row.var_sal);
     /* Crawl result set let while judge whether there is a record * *
     fetch cur_emp into Emp_row;
     End Loop;
     Close cur_emp;
   End

/* Stored Procedures * *
Temporary PL, SQL blocks are stored in the temporary cache, when the exit of PL, SQL block disappeared.
Named SQL blocks can be saved to the database for reuse: stored procedures, functions, triggers, packages
/*1. Create a stored procedure
Named PL, SQL block, can have no parameters, can have multiple inputs, multiple output parameters. However, there is usually no return value. High efficiency.

create [or Replace] procedure Pro_name [(Para1[,para2] ...)] is|as 
begin 
       Plsql_sentences;
Exception
  dowith sentences;
End

2. Parameters of stored Procedures
In mode parameters
The input type parameter, the parameter value is passed in by the caller, and can only be read by the stored procedure.
(1) Specify name passing: Parameter name => argument value
(2) by position Pass: Direct value by parameter position
(3) Mixed mode transfer: Using the above two ways of mixing
Out mode parameters
The parameter of the output type, which is already assigned in the stored procedure, and this parameter can be passed to an environment other than the current stored procedure.


Default value for the 3.in parameter * *
/*
When the in parameter is declared, a specific default value is given, so that if the in parameter has no arguments passed in when the stored procedure is invoked, the stored procedure can operate using the default value.
*/
--1 the simplest stored procedure

CREATE PROCEDURE pro_insertdept
is begin
  inserts into Dept values (77, ' marketing department ', ' Jilin ');--insert data record
  commit; Submit Data
  Dbms_output.put_line (' Insert new record succeeded. '); --Prompt Insert record successful end
pro_insertdept;

--2 If there is, replace the new

Create or Replace procedure pro_insertdept
is begin
  inserts into Dept values (99, ' marketing department ', ' BEIJING ');--Inserting data records
  commit;--submit data
  Dbms_output.put_line (' Insert new record succeeded. '); --Prompt Insert record successful end
pro_insertdept;

--3 stored procedure with in parameter

Create or Replace procedure insert_dept (
  num_deptno in number,--defines the variable in the in mode, which stores the department number
  Var_ename in varchar2,-- A variable that defines in mode, which stores the department name
  Var_loc in varchar2) is
begin
  inserts into Dept
  VALUES (Num_deptno,var_ename, VAR_LOC); --Insert a record commit into the Dept table
  ;--Submit the database end
insert_dept;

--4 stored procedures with out parameters

Create or Replace procedure select_dept (
  num_deptno in number,--define in-mode variables, requiring the department numbers to be entered
  var_dname out dept.dname% Type,--defines an out mode variable that can store the department name and output
  var_loc out Dept.loc%type
)
  is begin select Dname,loc into
  var_dname, Var_loc
  from dept
  where Deptno = num_deptno;--retrieves departmental information for a department number
exception when
  No_data_found then- If the SELECT statement has no return record
    dbms_output.put_line (' No presence of this department number ');--Output information end
select_dept;

/*4.2 execute stored procedures with output parameters.

Set serverout on
declare
  var_dname dept.dname%type;
  Var_loc Dept.loc%type;
Begin
  Select_dept (99,var_dname,var_loc);
  Dbms_output.put_line (var_dname| | ' Located in: ' | | VAR_LOC);
End

--5 in default value

Create or Replace procedure insert_dept (
  num_deptno in number,--define the in parameter of the storage department numbers
  var_dname in varchar2 default ' Integrated section ',--define the in parameter of the storage department name, and the initial default value
  Var_loc in varchar2 Default ' Beijing ') is
begin inserts into
  dept VALUES (Num_deptno, VAR_DNAME,VAR_LOC);--Insert a record end
;

/* Triggers: can be considered as a special stored procedure. Defines some related database-related events (INSERT, update, create, and so on) to execute the corresponding "functional code" when the event occurs. Used to manage the full constraints responsible, or
Monitor the modification of the table, or notice the peculiar program, even can realize the audit function to the database. */
/* Trigger concept: Triggers an event. */
/* statement triggers: Triggers executed against a DML statement, and triggers are executed only once.
--1. Create a log table

CREATE TABLE Dept_log
(
  operate_tag varchar2 (10),--Define the field, store the type of information
  operate_time Date--Define the field, store the operation date
);

--2 creates a trigger for the Dept table for logging.

Create or replace trigger tri_dept
  before insert or update or delete
  on dept-Creates a trigger that causes the trigger to execute when the Dept table is inserted, modified, and deleted C2/>declare
  Var_tag varchar2 (10);--declares a variable that stores the type of action performed on the Dept table
Begin
  If inserting then--when the trigger event is insert
    Var_tag: = ' Insert ';--Identity insert operation
  elsif updating then--When trigger event is update
    var_tag: = ' modify ';--identity modification operation
  elsif deleting then--when the trigger event is delete
    var_tag: = ' delete ';--Identity Delete operation end
  if;
  INSERT INTO Dept_log
  values (var_tag,sysdate)--Inserts the action information for the Dept table into the Log table end
tri_dept;

/* Row-level triggers: row-level triggers return a trigger to each row of data that is affected by the DML action statement. To create the trigger, you must use the For each row this option in the syntax * *

CREATE TABLE Goods
(
  ID int primary KEY,
  good_name varchar2
);

Create sequence seq_id;
Use:

Create or replace trigger Tri_insert_good
  before insert
  on goods--about the ID of the goods datasheet and before inserting the ID column, causing the trigger to run for
  Each row--create row-level triggers
begin
  Select Seq_id.nextval
  into:new.id
  from dual;--generate a new value from the sequence, assigning to the ID column of the current inserted row End
;

/* Replace trigger: * *
/* User event Triggers/*

/* Function * *
/* Function definition: For calculation, return a value
*/

Create or Replace function Get_avg_pay (Num_deptno number) Return No. is--creates a function that calculates the average wage in a department, and then passes in the departmental numbering parameter
  num_ Avg_pay number;--defines a temporary variable, saves the average salary of a department
begin
  Select AVG (SAL) into Num_avg_pay from EMP where deptno=num_deptno;- -Get the average wage return of a department
  (round (num_avg_pay,2));--Returns the average wage
exception
  when No_data_found then--if this department number does not exist
    Dbms_output.put_line (' The department number does not exist ');
    return (0); --Returns the average wage of 0 end
;

General SQL statement Optimization
1. It is not recommended to replace all columns with "*"
2. Replace Delete with truncate
3. Use of a COMMIT statement in the context of ensuring completeness.
4. Minimizing the number of queries to the table
5. Replace in with exists
Table Join optimization
1. Selection of driver table
The concatenation order of the 2.where clauses: filtering out unnecessary data through the where condition before the connection is better. General SQL statement Optimization
1. It is not recommended to replace all columns with "*"
2. Replace Delete with truncate
3. Use of a COMMIT statement in the context of ensuring completeness.
4. Minimizing the number of queries to the table
5. Replace in with exists
Table Join optimization
1. Selection of driver table
The concatenation order of the 2.where clauses: filtering out unnecessary data through the where condition before the connection is better.