Oracle Summary Article 3 [PLSQL]

Introduction to PLSQL

PLSQL is an extension of Oracle to SQL99. Basically every database will extend SQL. Oracle's extension to SQL is called PLSQL.

What is SQL99

(1) Rules for operating all relational databases
(2) It is the fourth generation language.
(3) It is a structured query language
(4) Only by issuing legitimate and reasonable orders can the corresponding results be obtained.

Characteristics of SQL

(1) Interactive and non-procedural
(2) Strong database manipulation ability, just send commands, no need to pay attention to how to achieve.
(3) In multi-table operation, automatic navigation is simple, such as:
- select emp.empno,emp.sal,dept.dname
- from emp,dept
- where emp.deptno = dept.deptno
(4) Easy debugging, error prompting, straightforward
(5) SQL emphasizes results

What is PLSQL

It's dedicated to Oracle server. On the basis of SQL, it adds some procedural control statements, called PLSQL.
Proceduralization includes type definitions, judgments, loops, cursors, exception or exception handling...
PLSQL emphasizes process

Why PLSQL

Because SQL is the fourth generation imperative language, it can not display the procedural business, so we have to use a procedural programming language to make up for the deficiencies of SQL.
SQL and PLSQL are not substitutes, but complements.

PLSQL grammar

declare and exception can be omitted, begin and end; / can not be omitted.

     [declare]
          Variable declaration;
      Variable declaration;
     begin
          DML/TCL operation;
      DML/TCL operation;
     [exception]
          Exceptions;
      Exceptions;
     end;
     /

In PLSQL programs:; Numbers denote the end of each statement, / denotes the end of the entire PLSQL program.

What is the difference between PLSQL and SQL execution?

(1) SQL is executed singly
(2) PLSQL is executed as a whole and cannot be executed alone. The whole PLSQL is terminated with /, in which each statement is terminated with a number.

PLSQL variable

Since PLSQL is process-oriented, there must be a basic grammar for writing process programs. First, let's introduce the variables of PLSQL.

PLSQL has four variables

number
varchar2
Same as column name type
The same column type as the entire table

Example

Write a PLSQL program, output "hello world" string, grammar: dbms_output.put_line('the string to be output');
begin
    Output string to the SQLPLUS client tool
    dbms_output.put_line('hello');
end;
/

Be careful:
dbms_output is an output object in oracle
 put_line is a method of the above object to output a string to wrap automatically 

Set to display the results of PLSQL program execution, by default, do not display the results of PLSQL program execution, grammar: set server routput on / off;
set serveroutput on;

Using basic type variables, constants, and annotations, find the sum of 10 + 100
declare
    Define variables
    mysum number(3) := 0;
    tip varchar2(10): ='result';
begin
    /* Business Algorithms*/   
    mysum := 10 + 100;
    /* Output to Controller*/
    dbms_output.put_line(tip || mysum);
end;
/

Output 7369 employee name and salary, the format is as follows: 7369 employee name is SMITH, salary is 800, grammar: use table name. Field% type
declare
    Define two variables, name and salary, respectively
    pename emp.ename%type;
    psal   emp.sal%type;
begin  
    --SQL statement
    --select ename,sal from emp where empno = 7369;
    PLSQL statement, which puts the value of ename in the pename variable and the value of sal in the psal variable    
    select ename,sal into pename,psal from emp where empno = 7369;
    - Output
    dbms_output.put_line('7369 employee's name is'| pename | salary is' | | psal);    
end;
/

Output 7788 employee name and salary, the format is as follows: 7788 employee name is SMITH, salary is 3000, grammar: use table name% rowtype
declare
    emp_record emp%rowtype;
begin
    select * into emp_record from emp where empno = 7788;
    dbms_output.put_line('7788 employee's name is'| emp_record.ename |', salary is' | | emp_record.sal);
end;
/

When do you use% type and% rowtype?

When defining a variable, the type of the variable is the same as that of a field in the table, you can use% type
When defining a variable, which is exactly the same as the whole table structure, you can use% rowtype, and then you can get the corresponding value in the variable by the variable name. field name.
In projects, commonly used% type

Judgment body

Grammar:

It's worth noting that eslif is not wrong. It's missing an e.

Example

//Useif-else-end ifWhat day is it today? Yes."Working day"still"Rest Day"
declare
    pday varchar2(10);
begin
    select to_char(sysdate,'day') into pday from dual;
    dbms_output.put_line('Today is the day.'||pday);
    if pday in ('Saturday','Sunday') then
    dbms_output.put_line('Rest Day');
    else
    dbms_output.put_line('Working day');
    end if;
end;
/

//Receive values from the keyboard, usingif-elsif-else-end ifdisplay"age<16"，"age<30"，"age<60"，"age<80"
declare
    age number(3) := &age;
begin
    if age < 16 then
       dbms_output.put_line('You're under age');
    elsif age < 30 then
       dbms_output.put_line('Your youth');
    elsif age < 60 then
       dbms_output.put_line('You struggle');
    elsif age < 80 then 
       dbms_output.put_line('You enjoy people');
    else
       dbms_output.put_line('Unfinished');
    end if;
end;
/

loop

In PLSQL, there are three grammars of loops:

WHILE cycle:

while is followed by a loop condition, similar to java, LOOP and END LOOP are the keywords **

WHILE  total  <= 25000  

LOOP
    total : = total + salary;
END  LOOP;

LOOP cycle:

The condition behind exit is set before it exits the loop.

Loop
   exit [when Conditions hold];
   total:=total+salary;
end loop;

FOR cycle:

The increment of the loop can only be 1. You can't customize the step size.

FOR   I   IN   1 . . 3  

LOOP

Statement sequence;

END    LOOP ;

Example

//Using loop loops to display1-10
declare
    i number(2) := 1;
begin
    loop
        --Wheni>10Exit the loop
        exit when i>10;
        --outputiValue
        dbms_output.put_line(i);
        --Independent addition of variables
        i := i + 1;  
    end loop;
end;
/

//UsewhileCyclic display1-10
declare
    i number(2) := 1;
begin
    while i<11 
    loop
        dbms_output.put_line(i);
        i := i + 1;
    end loop;
end;
/

//UsewhileCycle, to emp Insert in table999A record
declare
    i number(4) := 1;
begin 
    while( i < 1000 )
    loop
        insert into emp(empno,ename) values(i,'Ha-ha');
        i := i + 1;
    end loop;   
end;
/

//UsewhileCycle, from emp Delete from the table999A record
declare
    i number(4) := 1;
begin 
    while i<1000
    loop
        delete from emp where empno = i;
        i := i + 1;
    end loop;
end;
/

//UseforCyclic display20-30
declare
    i number(2) := 20;
begin
    for i in 20 .. 30
    loop
        dbms_output.put_line(i);
    end loop;
end;
/

cursor

The cursor in Oracle is actually a resultSet similar to that in JDBC, which is the concept of a pointer.

Since it is similar to resultSet, cursors are only valid for queries.

grammar


CURSOR cursor name [(parameter name data type [, parameter name data type]...]
      IS SELECT statement;

Example

//Use cursor without reference to query the names and salaries of all employees. [If you need to traverse more than one record, use cursor with no record to find the use of cemp%notfound]
declare
    --Define cursors
    cursor cemp is select ename,sal from emp;
    --Define variables
    vename emp.ename%type;
    vsal   emp.sal%type;
begin
    --Open the cursor, which is before the first record
    open cemp;
    --loop
    loop
       --Move the cursor down once
       fetch cemp into vename,vsal; 
       --Exit the cycle,When the cursor is moved down once and no record is found, the loop exits.
       exit when cemp%notfound;
       --Output results
       dbms_output.put_line(vename||'--------'||vsal);
    end loop;
    --Close the cursor
    close cemp;
end;
/

//Using cursor with reference cursor, query10Name and salary of employees in No. 1 Department
declare
    cursor cemp(pdeptno emp.deptno%type) is select ename,sal from emp where deptno=pdeptno;
    pename emp.ename%type;
    psal emp.sal%type; 
begin 
    open cemp(&deptno);
    loop
        fetch cemp into pename,psal;     
        exit when cemp%notfound;
        dbms_output.put_line(pename||'The salary is'||psal);
    end loop;
    close cemp;
end;
/

//Use cursor without reference cursor to really raise employees'salary and analyYST to increase1000，MANAGER rise800，Other gains400，Request display number, name, position, salary
declare
    cursor cemp is select empno,ename,job,sal from emp;
    pempno emp.empno%type;
    pename emp.ename%type;
    pjob   emp.job%type;
    psal   emp.sal%type;
begin
    open cemp;
    loop
        fetch cemp into pempno,pename,pjob,psal;
        --Cyclic exit conditions must be written
        exit when cemp%notfound;
        if pjob='ANALYST' then
            update emp set sal = sal + 1000 where empno = pempno;
        elsif pjob='MANAGER' then
            update emp set sal = sal + 800 where empno = pempno;
        else 
        update emp set sal = sal + 400 where empno = pempno;
        end if;
    end loop;
    commit;
    close cemp;
end;
/

Exceptions, anomalies

As we have seen from the PLSQL grammar above, there is an exception, which is called an exception in Oracle, and we can also simply see that achievement is an exception in Java...

Grammar:

Define exceptions in the declare section   
out_of   exception ;

 Throw an exception in a feasible statement in the begin section  
raise out_of ；

 Handling exceptions in the exception section
when out_of then ...

Example

//Using the built-in exceptions of oracle system, the demonstration except 0 cases [zero_divide]
declare
    myresult number;
begin
    myresult := 1/0;
    dbms_output.put_line(myresult);
exception
    when zero_divide then 
     dbms_output.put_line('The divisor cannot be zero');
     delete from emp;  
end;
/

//Use the built-in exceptions of oracle system to query the employee name of department 100 and demonstrate that no data was found.
declare
    pename varchar2(20);
begin
    select ename into pename from emp where deptno = 100;
    dbms_output.put_line(pename);
exception
    when NO_DATA_FOUND then 
     dbms_output.put_line('No staff in this department');
     insert into emp(empno,ename) values(1111,'ERROR');
end;
/

Stored Procedures and Stored Functions

In Oracle, the concepts of stored procedures and stored functions are similar. Generally, we can use them together. But sometimes the process is better, sometimes the function is better. Next, we will explain when to use a process or a function.

First, before we learn stored procedures and stored functions, we need to understand why we need to learn them. .

In fact, stored procedures and functions are similar to the concept of functions in Java. .

So far, our PLSQL has several shortcomings:

PLSQL can't encapsulate it. Every time it calls, it copies the whole code to call it.
Sometimes, we want to save PLSQL code, we can only save it manually in the hard disk, very troublesome.
We learn about databases so that programs can be called, but PLSQL can't make programs (java) call.

Therefore, stored procedures and stored functions can solve the above problems, can encapsulate the code, save it in the database, let the programming language to invoke. .

Grammar of stored procedures and functions

The grammar of process:

create [or replace] procedure Process name[(parameter list)]  
as
        PLSQLProcedure body;[begin...end;/]

Grammar of functions:

CREATE [OR REPLACE] FUNCTION Function name[(parameter list) ]
 RETURN  return type
AS
PLSQL Subprogram body;

[begin...end;/]

The declare keyword is replaced by the as keyword in both process and function.

Process examples

Create the first process:

CREATE OR REPLACE PROCEDURE hello
AS
  BEGIN
    dbms_output.put_line('hello world');
  END;

There are three ways to invoke a procedure:

exec procedure name [used in SQLPLUS]
PLSQL program call
Java calls

PLSQL Call

BEGIN
  hello();

END;

Create a reference stored procedure raiseSalary (No.) to increase the salary of Employee 7369 by 10%. Demonstrate the use of in, default in, case insensitive

CREATE or REPLACE PROCEDURE bb(pempno in NUMBER)
  AS
  BEGIN
    UPDATE EMP
    SET sal = sal * 1.2
    WHERE empno = pempno;

  END;

Call:

  BEGIN
    bb(7369);
  END;

Create FindEmpName AndSalAndJob (Number) with reference stored procedure, query 7788 employee's name, position, monthly salary, return multiple values, demonstrate the use of out

Creation process: In the process parameters, the default value is IN, if it is output, then we need to specify OUT.

CREATE OR REPLACE PROCEDURE find(pempno IN NUMBER, psal OUT VARCHAR2, pename OUT VARCHAR2, pjob OUT VARCHAR2)
AS
  BEGIN
    SELECT
      ename,
      sal,
      job
    INTO pename, psal, pjob
    FROM emp
    WHERE empno = pempno;
  END;

Invocation: When invoking, the psal, pname and pjob used are not defined at the time of invocation, so we need to define variables before using them!

DECLARE

  psal   emp.sal%TYPE;
  pename emp.ename%TYPE;
  pjob   emp.job%TYPE;

BEGIN
  find(7369, psal, pename, pjob);

  dbms_output.put_line(psal || pename || pjob);

END;/

Examples of functions

Create a parametric storage function findEmpIncome (number), query the annual income of employees 7369, demonstrate the use of in, default in

CREATE OR REPLACE FUNCTION findEmpIncome(pempno IN NUMBER)
  --What is specified here is the return value type.
  RETURN NUMBER
AS
  income NUMBER;
  BEGIN
    SELECT sal * 12
    INTO income
    FROM emp
    WHERE empno = pempno;

    /*There must be a return statement in PLSQL*/
    RETURN income;
  END;

Call: In PLSQL, the assignment statement is not a direct "=", but::=

DECLARE
  income number;
BEGIN
  income := findEmpIncome(7369);
  dbms_output.put_line(income);

END;/

If you write the = sign, the following error will occur:

[2017-07-11 13:58:14] [65000][6550] ORA-06550: Line 4, Column 10: 
PLS-00103: Appearance Symbol "="When one of the following is needed:
 := . ( @ % ;
ORA-06550: Line 4, Column 31: 
PLS-00103: Appearance Symbol ";"When one of the following is needed:
 . ( ) , * % & -
   + / at mod remainder rem <an exponent (**)> and or ||
   multiset
ORA-06550: Line 7, Column 4: 
PLS-00103: Appearance Symbol "end-of-file"When one of the following is needed:
 end
   not pragma final instantiable order overriding static member
   constructor map

Create a parameter storage function findEmpName AndJob AndSal (number), query the name (return), position (out), monthly salary (out) of 7788 employees, and return multiple values.

CREATE OR REPLACE FUNCTION findEmpNameAndJobAndSal(pempno IN NUMBER, pjob OUT VARCHAR2, income OUT NUMBER)
  --What is specified here is the return value type.
  RETURN VARCHAR
AS
  /*Queried fields are the same as column names, so use the same type of column names.*/
  pename emp.ename%TYPE;
  BEGIN
    SELECT
      sal,
      ename,
      job
    INTO income, pename, pjob
    FROM emp
    WHERE empno = pempno;

    /*There must be a return statement in PLSQL*/
    RETURN pename;
  END;

Call function:

DECLARE

  /*The output field is of the same type as the column name.*/
  income emp.sal%TYPE;
  pjob   emp.job%TYPE;
  pename emp.ename%TYPE;
BEGIN
  pename := findEmpNameAndJobAndSal(7369, pjob, income);
  dbms_output.put_line(pename || pjob || income);

END;/

Scenarios for the Use of Processes and Functions

We find that the difference between process and function is not very big. Generally, when we can use function to achieve, we can also use process to achieve. .

However, there are always some situations, using function is better than using process, using process is better than using function, when to use process, when to use function???

It is not difficult to find that a function must have a return value. When we call it, we accept the return value and get it directly.

In other words

When the return value has only one parameter, the storage function is used!
When the return value has no parameter or more than one parameter, use the procedure!

Scenarios for the Use of SQL and Procedural Functions

[Suitable for use] Process functions:

> Need to be stored in the database for a long time
> Need to be called repeatedly by multiple users
> Business logic is the same, but parameters are different
> Batch manipulation of large amounts of data, e.g. batch insertion of large amounts of data

[Suitable for use] SQL:

> Any of the above negative, you can use SQL
> Use SQL for tables, views, sequences, indexes, etc.

trigger

In PLSQL, there is a concept similar to filter in our Java Web, which is trigger. The idea of trigger is almost the same as that of Filter. .

It is worth noting that for triggers, they are not for query operations. That is to say: triggers only for deletion, modification, insertion operations!

Trigger grammar

   CREATE  [or REPLACE] TRIGGER  Trigger Name
   {BEFORE | AFTER}
   { INSERT | DELETE|-----Statement level
      UPDATE OF Column names}----Row level
   ON  Table name

    -- Traverse through each row of records
   [FOR EACH ROW]
   PLSQL Block [declare...begin...end;/]

Example

Create a statement-level trigger insertEmpTrigger to display "hello world" before adding an insert operation to the table [emp]

CREATE OR REPLACE TRIGGER insertempTiriger
BEFORE
INSERT
  ON EMP
  BEGIN
    dbms_output.put_line('helloword');

  END;

Call:

INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (1, '2', '3', 4, NULL, NULL, NULL, 10);

Result:

Monday to Friday, and 9-20 points can insert data into the database emp table, otherwise use functions to throw exceptions.
Syntax: raise_application_error('-20000','exceptional cause')

CREATE OR REPLACE TRIGGER securityTrigger
BEFORE
INSERT
  ON EMP
  DECLARE
    pday  VARCHAR2(10);
    ptime NUMBER;
  BEGIN
    /*Get Wednesday*/
    SELECT to_char(sysdate, 'day')
    INTO pday
    FROM dual;

    /*Get Time*/
    SELECT to_char(sysdate, 'hh24')
    INTO ptime
    FROM dual;

    IF pday IN ('Saturday', 'Sunday') OR ptime NOT BETWEEN 7 AND 23
    THEN
      RAISE_APPLICATION_ERROR('-20000', 'Non-work events, please come back at work time!');

    END IF;

  END;

Insert data, response trigger:

INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (3, '2', '3', 4, NULL, NULL, NULL, 10);

Create a row-level trigger checkSalary Trigger to ensure that the salary column after the increase is larger than the salary before the increase. Grammar: for each row/:new.sal/:old.sal

You can use: new.sal/:old.sal to compare values before and after insertion

CREATE OR REPLACE TRIGGER checkSalTrigger
BEFORE
UPDATE OF sal
  ON EMP
FOR EACH ROW
  BEGIN
    IF :new.sal <= :old.sal
    THEN
      RAISE_APPLICATION_ERROR('-20001', 'Your salary increase is too small!!!');

    END IF;

  END;

Call:

UPDATE emp
SET sal = sal - 1
WHERE empno = 7369;

Posted by dg on Fri, 14 Jun 2019 17:35:03 -0700

Programmer Group

Oracle Summary Article 3 [PLSQL]

Introduction to PLSQL

PLSQL grammar

PLSQL variable

Example

Judgment body

Example

loop

Example

cursor

grammar

Example

Exceptions, anomalies

Grammar:

Example

Stored Procedures and Stored Functions

Grammar of stored procedures and functions

Process examples

Examples of functions

Scenarios for the Use of Processes and Functions

Scenarios for the Use of SQL and Procedural Functions

trigger

Trigger grammar

Example

Hot Keywords