Oracle-pl/sql2

A stored procedure

A stored procedure is a precompiled collection of SQL statements and optional control-flow statements, stored as a single name and processed as a unit. Establishing stored procedures on ORACLE server can be called by multiple applications, passing parameters to stored procedures, or passing parameters back to stored procedures.

Stored procedures are stored in the database and can be executed by the application through a call, and allow the user to declare variables, conditional execution, and other powerful programming features.

Stored procedures are compiled on the server at the time they are created, so they execute faster than a single SQL statement.

1.1 Advantages of stored procedures

(1) The stored procedure is compiled only at creation time, and each subsequent execution of the stored procedure does not need to be recompiled, while the general SQL statements are compiled once per execution, so using stored procedures can improve database execution speed.

(2) When complex operations are performed on a database, such as when multiple tables are update,insert,query,delete, this complex operation can be encapsulated with stored procedures and used in conjunction with the transactional processing provided by the database.

(3) The stored procedure can be reused to reduce the workload of the database developer.

(4) High security, can be set only a certain user has the right to use the specified stored procedure.

Grammar

      CREATE [OR REPLACE] PROCEDURE procedure_name[(argment [{in | In Out}] type,argment [{in | Out | In Out}] Type]{is | as}< type. Description of variable >BEGIN< execution part >EXCEPTION          

First case (create a simple stored procedure)

Example: Inserting data into the EMP table (111, ' Zhang San ')

CREATE PROCEDURE Pro1 Isbegininsert into EMP (empno, ename) VALUES (111, ' Zhang San '); end; call stored procedure: EXEC  Pro1 stored procedure with parameters: Removes employee information for the specified employee number in the EMP table. CREATE PROCEDURE Pro2 (in_empno number) Isbegindelete from emp where empno = in_empno;end; invoke: Exec Pro2 (111); practice : Establish a stored procedure that shows the employee's salary and the average salary of the department created or replace procedure Pro1 (v_in_empno in numbers, v_out_mysal out number,v_ Out_avgsal out number) Isbegin Select Sal to v_out_mysal from EMP where empno=v_in_empno; select AVG (SAL) into v_ Out_avgsal from empwhere deptno= (select Deptno from emp where empno=v_in_empno); end; Call execution: Declare v_ Mysal number;v_avgsal number; begin Pro1 (7902, v_mysal,v_avgsal); Dbms_output.put_line (v_mysal| | ' ' | | v_avgsal); end;                 

Two functions

Functions are generally used to calculate and return a value that can often be written as a function of a calculation. The call to a function is part of the expression, and the calling procedure is a PL/SQL statement.

2.1 Structure of the function

Declarations section: includes types, variables, cursors

execution part: SQL statement written to complete function or PL/SQL code block

Exception Handling section:

Functions and procedures are created in a somewhat similar form, and are compiled and placed in memory for use by the user, except when the function is called with an expression, rather than a procedure that requires a procedure name. In addition, the function must have a return value, and the procedure does not.

2.2 Creating a function

The function is used to return specific data, and when a function is established, the function header must contain a return clause , and the body of the function must contain the data returned by the return statement.

Grammar:

Create  or  replace  function name (parameter 1,...) Return data type is        define variable; begin        executes statement; end;   

Example: Define a function, enter a job name, and return the number of employees responsible for the job

--Creating a function Create OR REPLACE function GetCount (Major in Varchar2 )--Declares the return type returns number isf_count number; begin--uses the INTO statement to assign the result to the variable select COUNT (*) into F_count from students where major=' major ';--returns return using the return statement F_count ;  --Returns the result end;   

Function call

Method One:--Call the function--declare--declare the return value of the variable receive function           v_count number; BEGIN           v_count:=getcount (' MUSIC ');            Dbms_output.put_line (V_count); END; mode two: var variable name variable type call Function name (parameter value,...) into : variable name, print variable name method Three: Select Function name (parameter,...) from dual; 

2.3 Deleting a function

Grammar:

DROP function Function name

Case

Write a function that receives the user name and returns the user's annual salary. Create function Inname_outsal (v_in_name varchar2) return number is        v_annual_sal number; begin       Select (SAL+NVL (comm,0)) *13 into V_annual_sal from emp where ename=v_in_name;       return v_annual_sal;end;     

2.4 Differences between functions and procedures

1. The function must have a return value, and the procedure may not; 2. Functions and procedures are not called in Java in the same way; 3. Calling an Oracle function in Java can be called directly in a SELECT statement, such as a select Custom function name (parameter) from table, or a procedure that uses CallableStatement to complete a call. 

Three-cursor

cursors: Tools for handling multi-line records retrieved from a database using the SELECT statement

Classification:

1) Display Cursors

When multiple records are returned, use the display cursor to read line by row

2) Implicit cursors

PL/SQL automatically creates an implicit cursor for a DML statement that contains a return record

3.1 Properties of Cursors

Property name	Description
%found	Used to verify the success of a cursor, typically used before a FETCH statement, and returns True when a cursor queries a record by condition
%isopen	If the cursor is open and an attempt is made to open a cursor that is already open or closed, an error will occur
%notfound	As opposed to%found, returns True when a record cannot be queried according to the criteria
%rowcount	Returns the number of rows retrieved for the recorded data when the cursor is being read by a loop execution

3.2 Use of cursors

1) Declaration of Cursors

Grammar:

CURSOR cursor_name [(parameter [, parameter] ...)]

[RETURN Return_type] Is Selectsql

Description

Cursor: Used to declare a cursor

Parameter: Optional parameters for specifying parameter types, patterns, etc.

Return: optional, specifying the return type of the cursor

Selectsql: SELECT statement to be processed, cannot contain into clause

2 ) to open the cursor

Syntax: Open test_cursor using open statement to open a cursor

3 ) Extract Cursors

Grammar:

FETCH cursor_name into Variable_list

Description

Using a FETCH statement to read the contents of a cursor

Variable_list must be the same as the result set type extracted from the cursor

4 ) Close the cursor

Grammar:

Close cursor_name

Description

To close a cursor by using the close statement

When the cursor is closed, all resources are freed and cannot be opened again

Example: Use of cursors

1 ) reads a single piece of data

For example: Query employee's label: 7369 employee name and salary;

DECLARE     v_ename varchar2 ();     V_sal emp.sal%type;     --Declares a cursor     c_emp is a select ename, sal from EMP where empno=7369 ; BEGIN     -Opens the cursor    open c_emp;    -- Determine if the cursor returns a record if C_emp%notfound then dbms_output.put_line (' No corresponding record found '); else-- reads the record fetch from the cursor c_emp Into v_ename, v_sal; Dbms_output.put_line (V_ename | | Salary for ' | | v_sal); End If; CLOSE c_emp; END;            

2 ) read multiple records:

If you can read only one row of records using a FETCH statement, how do you read multiple rows of records?

Loops can be used when reading multiple rows of records: Loop loop, while loop, and for loop

For example: Query the name and salary of everyone whose salary is less than 3000?

For loop, automatically open cursors without using the Open statement

DECLARE        v_ename varchar2 ();        V_sal emp.sal%type;        CURSOR C_emps is a select ename, sal from EMP where sal<=3000 ; BEGIN for       c_emp in c_emps LOOP         v_ename:=c_emp.ename; v_sal:=c_emp.sal; dbms_output.put_line (' Employee Name: ' | | v_ename| | ' Salary for ' | | v_sal); END LOOP; END;        

Description

1. When using a For loop, automatically open the cursor without using the Open statement

2.pl/sql automatically declares variables implicitly

3. When the loop is finished, the cursor will always close

3.3 Practice

L Requirements Description:

L Write PL/SQL statements to implement the information (name, number, department name) of reading out ' SMITH ' from the table using a cursor

L Hint:

L Open cursors with

L to judge the content of the cursor

L use Fetch to read

L Close Cursors

DECLARE    v_ename emp.ename%type;    V_deptno   emp.deptno%type;    V_dname  dept.dname%type;--declares a cursor    , cursor c_emp is select Ename,emp.deptno,dnamefrom emp, Deptwhere ename= ' SMITH ' and emp.deptno=Dept.deptno; begin--Open cursor Open c_emp;--determine if cursor returns record if C_emp%notfound Then dbms_output.put_line (' did not find the corresponding record! ') ); Else-Reads a record fetch from the cursor c_emp into v_ename,v_deptno,v_dname; Dbms_output.put_line (v_ename| | ' Department Number: ' | | v_deptno| | ' Department Name: ' | | v_dname); End If;--Closes the cursor close c_emp; end;             

L Requirements Description:

Based on the previous exercise, read and display the information from the staff of the department where ' Smith ' is located

L Tip: use a For loop to iterate through cursors

Create or Replace procedure P3 is   v_ename varchar2 ();   V_deptno Number (4);   V_dname varchar2 ();     Cursor C_emps is a select Ename,dept.deptno,dname from                     EMP, dept where emp.deptno=Dept.deptno and Emp.deptno = (select deptno from emp where ename= ' SMITH '), begin for c_emp in C_emps loop v_ename:=
              
                C_emp.ename; v_deptno:=
               c_emp.deptno; v_dname:= c_emp.dname; dbms_output.put_line (v_ename| | ' Department Number ' | | v_deptno| | ' Department Name: ' | | v_dname); End loop; end;    
                        

Four triggers

Consider a few questions first:

(1) What if a user logs on to Oracle and logs the user name and logon time to Oracle in a single table?

(2) Prohibit the user in Sunday to delete a table, how to do?

(3) When the user deletes a table, the deleted records are automatically backed up to another sheet ...

Many relational databases provide a technique to automate another operation when a user is doing something, and we turn this into a trigger technology.

triggers are stored procedures that are stored in the database, are implicitly executed , can support DML triggers , and are based on system events (starting the database, shutting down the database, logging on to the database, and DDL action to establish a trigger ).

4.1 Classification of triggers

L DML triggers (INSERT, delete, UPDATE trigger)

L DDL triggers (CREATE TABLE, create view, drop table ...) ）

L system triggers (System-related triggers, such as user login, exit, start database, close database)

4.2 Creating a Trigger

Grammar:

Create [or replace] trigger trigger_name   – Trigger name {before| after}                        -before or after {Insert | delete | Update}– What kind of operation is performed on [schema.] table_name               --in which user's table [for each row]                        --Every line   Begintrigger_body; End;   

After-is triggered after recording manipulation, is the first to complete the data additions and deletions, and then trigger, trigger the statement later than the monitoring of the deletion and modification operation;

Before-is triggered before recording manipulation, is the first to complete the trigger, then increase the deletion, trigger the statement before the monitoring of the increase and deletion.

4.3 DML triggers

Case 1 : When you add a piece of data to a table (U1), you are prompted to "add a piece of data".

CREATE TABLE u1 (ID number,name varchar2), create or replace trigger t1after Insert on u1begin
       
        dbms_output.put_line (' You've inserted a piece of data in the U1 table! '
        ); End; insert into U1 values (1, ' Zhang San ');  
          

Case 2 : When you modify multiple data in a table (EMP), you are prompted to "modify the data" multiple times.

If this is the case, only one sentence will be output. (sentence-level notation)

Create or Replace trigger t1after update on Scott.empbegin    dbms_output.put_line (' Modify a piece of data '); end ;--Performing an update operation updates emp Set sal = Sal +10; it should be written like this to output multiple statements: (change to line-level notation) Create or replace trigger t1after update on s Cott.empfor each row--represents a row-level trigger begin Dbms_output.put_line (' Modify a piece of data ');--Perform an update operation updates emp Set sal = Sal + Ten;          

Case 3 :

To prevent workers from deleting employee information during the rest day, developers can establish before statement triggers to achieve data security.

Create or replace trigger t1before Delete on scott.empbeginif to_char (sysdate, "Day") in (' Saturday ', ' Sunday ') Then     dbms_output.put_line (' Rest day cannot delete employee information! ');     Raise_application_error ( -20001,   ' sorry, day off cannot delete employees! '), End if; end;--Performs a delete operation, throwing an error message. Delete from emp where empno =7499;      

Description

1) raise_application_error (error_number number, error_msg varchar2) system has defined a stored procedure, let the system error.

2) Error_number [custom] from 20000 to 20999, this will not conflict with any of Oracle's error codes. error_msg_in[the length of the custom] can not exceed 2K, or intercept 2K, is the wrong message;

Use conditional verbs:

When a trigger contains multiple triggering events (INSERT, UPDATE, delete), three conditions can be used in order to differentiate specific triggering events in the trigger code: inserting, updating, deleting.

Case 3 : When user action table U1, give a prompt to explicitly indicate whether the user is an INSERT, update, or delete operation.

Create or replace trigger t1before Delete or update or insert on scott.u1begincasewhen inserting then         DBMS _output.put_line (' You are in the insert operation! updating         then Dbms_output.put_line (' You are making a modification! deleting then Dbms_output.put_line (' You are deleting! '). end case;--insert into U1 (empno, ename) VALUES (1, ' Zhang San ');--delete operation delete from EMP where Empno =7499;--updating Operation update emp Set ename = ' Zhang San ' where Empno =7499;          

4.4:old and: New keyword

Thinking:

When a trigger is triggered, the value of the column in the record being inserted, deleted, or modified is used, and sometimes the pre-and post-column values are used.

: old modifier to access the value of the top of the operation

: New modifier access operation

Case 4 : Displays the employee's salary before and after the revised value when modifying the payroll for an EMP table employee. How to ensure that the employee's salary should not be lower than the original salary.

Create or Replace trigger t1before Update on scott.empfor all rowbeginif:new.sal <: Old.sal then DBMS _output.put_line (' updated wage value is less than the value before update! Can't do this! '); Raise_application_error (-20005, ' The updated salary value is less than the value before the update! Can't do this! ') Else dbms_output.put_line (' Pre-update salary: ' | |:o Ld.sal | | ' Post-Update salary: ' | | : new.sal); endif;end;--Perform Update operation update EMP set SAL =2 where Empno =7499; update emp set sal =7000 where EMPN o =7499;         

Case 5 : Write a trigger that automatically backs up deleted records to another table (U1) when the user deletes a table (EMP) record.

Create or replace trigger t2before Delete on scott.empfor all rowbegininsert into U1 (ID, ename) values(: OL D.empno,:old.ename); end; Delete from emp where empno =7499;    

4.5 system triggers

System triggers are those that are based on Oracle events (such as user logon logon,logoff , and database startup and shutdown startup, SHUTDOWN) A trigger that provides a mechanism for tracking a system or a database change by using a system event trigger;

Here are some commonly used system event property functions, and the establishment of various event triggers, when establishing a system event trigger, we need to use the event property function, the usual time attribute functions are as follows:

Ora_client_ip_address//Return IP to Client

Ora_database_name//Returns the database name

Ora_login_user//Return login user name

Ora_sysevent//Returns the name of the system event that triggered the trigger

Ora_des_encrypted_password//Return User des (MD5) password after encryption

Case: Create a login or exit trigger

Grammar:

Create or replace trigger trigger name after [before] logon [logoff] on databasebegin--EXECUTE statement ...; End;

Case:

Complete the logon trigger and exit trigger creation.

In order to log the user's login and exit events, we can establish a login or exit trigger. In order to record the user name, time, IP address, we first create a table of information:

Open a command-line window: Sqlplus system/[email protected] as dba;--create a table that stores logins or exits system information. Create table log_table (username varchar2, logon_time date,logoff_time date, address varchar2);-- Create login triggers: Create or replace trigger Mytrigger1after logon on Databasebegininsert into log_table (username, logon_ Time, address) values(Ora_login_user, Sysdate, ora_client_ip_address); End;--Creating an exit Trigger: Create or replace Triggerbefore logoff on Databasebegininsert into log_table (username, logoff _time, address) values(Ora_login_user, Sysdate, ora_client_ip_address); End;         

4.6 DDL triggers

The DDL is simply the data definition statements that we use to create, alter, and drop as usual.

Grammar:

Create or replace trigger trigger name before| After ddl|create|alter|drop on  user name. schema-such as scott.schemabegin--execute statement ...; End;

Case:

Write a trigger that can record DDL operations performed by a user.

In order to record the DDL events that occur in the system, a table (My_ddl_record) should be established to store the relevant information

Attention:

You need to log on using System User:

Conn System/manager as sysdba; CREATE TABLE My_ddl_record (varchar2), username varchar2 (+), ddl_time date);--Creating a trigger or Replace Trigger mytrigger2after DDL on Scott.schemabegin       Insert into My_ddl_record values(ora_sysevent, Ora _login_user, sysdate); End;     

Note: in Oracle, DML transactions need to be committed manually, and DDL statements do not need to be manually committed. When we write DML statements, they are automatically committed when exiting the console if they are not manually committed.

4.7 Trigger Management

Disable triggers

means to temporarily invalidate a trigger.

Alter trigger trigger name disable;

activating triggers

Alter trigger trigger name enable;

Delete Trigger

Drop trigger trigger name;

Oracle-pl/sql2