This section describes the basic contents of PL SQL

The sample data listed in this section comes from the emp and dept tables under oracle user scott, as follows:

1. Introduction to plsql:

1. Concept: procedural language, procedural SQL language, is a process-oriented language. On the basis of ordinary sql, the features of programming language are added.The basic unit of PL/SQL is the block.

2. Introduction of blocks:

(1) Basic structure:

DECLARE

(declaration part)

BEGIN

(Execution)

EXCEPTION

(exception handling section)

END (End Marker)

The red part is necessary and indispensable

3. Classification of blocks:

(1) Anonymous blocks: only once, constructed dynamically.

(2) Subprograms: stored procedures, functions, packages, etc. stored in the database, which complete certain functions and can be called on other programs.

(3) When a database operation occurs, it triggers an event to execute the corresponding program automatically.

(Note: Stored procedures, triggers will be detailed in a later blog.)

2. Identifiers:

1. Concept: No more than 30 characters, the first character must be a letter, no minus sign'-'and case-insensitive.

2. Naming variables: To improve code readability, the following rules are recommended:

(1) Program variables: v_variablename

(2) Program constants: v_constantname

(3) Cursor variable: cursorname_cursor

(4) Exception identification: e_exceptionname

(5) Record type: recordname_record

Example: Query the salary employee date of an employee with employee number 7788 and the result of a 500-year increase in salary.

DECLARE
  v_name VARCHAR2(20);
  v_sal NUMBER(7,2);
  v_hiredate DATE;
  c_addsal NUMBER(7,2):=500;
  v_newsal NUMBER(7,2);
BEGIN
  SELECT ename,sal,hiredate
  INTO v_name,v_sal,v_hiredate FROM emp WHERE empno=7788;
  v_newsal := v_sal + c_addsal;
  DBMS_OUTPUT.PUT_LINE(v_name||'Wages are'||v_sal||'Employee Date is'||v_hiredate||'After wage increase'||v_newsal);
EXCEPTION
  WHEN  NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No employee data');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

Above is a simple anonymous block used only once, DBMS_OUTPUT.PUTLINE() used for printout, NO_DATA_FOUND was not found, SQLERRM error message.

3. Record and reference variables:

1. Referential variables:

The index data type is the same as that of a column already defined or in a database:

Example:v_name emp.ename%TYPE; the variable v_name defined here has the same ename data type as in the EMP table.

2. Recorded variables:

Returns a record type consistent with the data type of the database table.

Example:emp_record emp%rowtype;emp_record and database table emp have the same data type, so emp_record.ename can be used

Here is an example of each:

Example1: Print out 7788 employees'salaries

DECLARE
  v_name emp.ename%TYPE;
  v_sal emp.sal%TYPE;
BEGIN
  SELECT ename,sal
  INTO v_name,v_sal FROM emp WHERE empno=7788;
  DBMS_OUTPUT.PUT_LINE(v_name||'Wages are'||v_sal);
END;

Example2: Print out 7788 employees'salaries, using record variables

DECLARE
  emp_record emp%ROWTYPE;
BEGIN
  SELECT * INTO emp_record FROM emp WHERE empno=7788;
  DBMS_OUTPUT.PUT_LINE(emp_record.ename||'Wages are'||emp_record.sal);
END;

4. Process control statements:

1. Concepts: divided into 3 categories

(1) Conditional control statements: IF statements, CASE statements

(2) Loop statement: LOOP statement

(3) Sequential statement: GOTO statement, NULL statement

Example1: Judges the level based on the value entered and outputs

DECLARE
  v_level CHAR(1):='&LEVEL';
BEGIN
  IF v_level='A' THEN
    DBMS_OUTPUT.PUT_LINE('excellent');
  ELSIF v_level='B' THEN
    DBMS_OUTPUT.PUT_LINE('good');
  ELSIF v_level='C' THEN
    DBMS_OUTPUT.PUT_LINE('commonly');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Input Error');
  END IF;
END;

Here's the symbol &used to assign values to variables, which pops up the window shown in the second figure to assign values to user s and gives the result.

The ELSIF and ELSE used may not be used as appropriate, note that ELSIF is not ELSEIF here.

Example2: Judges the level based on the value entered and outputs

DECLARE
  v_level CHAR(1):='&LEVEL';
BEGIN
  CASE v_level
    WHEN 'A' THEN
      DBMS_OUTPUT.PUT_LINE('excellent');
    WHEN 'B' THEN
      DBMS_OUTPUT.PUT_LINE('good');
    WHEN 'C' THEN
      DBMS_OUTPUT.PUT_LINE('commonly');
    ELSE
      DBMS_OUTPUT.PUT_LINE('Input Error');
  END CASE;
END;

This uses CASE statements, which he can convert with IF statements, and which one he likes to use to see his personal preferences.

Example3: Print Output 1,2,3,4,5

DECLARE
  v_num INT:=1;
BEGIN
  LOOP
    DBMS_OUTPUT.PUT_LINE(v_num);
    EXIT WHEN v_num=5;
    v_num :=v_num+1;
  END LOOP;
END;

The basic loop of loop is used here.Note that there must be a condition to exit the loop, or the infinite loop will become an infinite loop.The EXIT WHEN statement is used here, which is a conditional exit loop.

There is also an EXIT to exit the loop directly without condition, and there is a new feature in oracle 11g, CONTINUE and CONTINUE WHEN, where CONTINUE is used to skip

Front loop, CONTINUE WEHN is used to conditionally skip the current loop, as shown below:

Example3.2: Print out 1,2,3,5

DECLARE
  v_num INT:=0;
BEGIN
  WHILE v_num<5 LOOP
    v_num :=v_num+1;
    CONTINUE WHEN v_num=4;
    DBMS_OUTPUT.PUT_LINE(v_num);
  END LOOP;
END;

Here you can see that when the value 4 is out of this cycle and no 4 is output.

Example4: Print Output 1,2,3,4,5

DECLARE
  v_num INT:=1;
BEGIN
  WHILE v_num<=5 LOOP
    DBMS_OUTPUT.PUT_LINE(v_num);
    v_num :=v_num+1;
  END LOOP;
END;

The while loop is used here, but the condition to exit the loop is changed from EXIT WHEN to WHILE, which is not very different from the previous example. The usage depends on your personal preferences.

Example5: Reverse Print 5,4,3,2,1

BEGIN
    FOR i IN REVERSE 1..5 LOOP
        DBMS_OUTPUT.PUT_LINE(i);
    END LOOP;
END;

The For loop is used here to control the number of loops, which also serves as a condition to exit the loop, but the more precise number of times, where REVERSE plays the role of inversion or can be removed to become positive.

Example6: Judges the level based on the value entered and outputs it. If D is entered, does nothing

DECLARE
  v_level CHAR(1):='&LEVEL';
BEGIN
  CASE v_level
    WHEN 'D' THEN
      GOTO the_next;
    WHEN 'A' THEN
      DBMS_OUTPUT.PUT_LINE('excellent');
    WHEN 'B' THEN
      DBMS_OUTPUT.PUT_LINE('good');
    WHEN 'C' THEN
      DBMS_OUTPUT.PUT_LINE('commonly');
   END CASE;
   <<the_next>>
   null;
END;

GOTO is used here, jumping unconditionally to the label of the same program, but it is not recommended for use in any programming language. It makes the program difficult to track processes and maintain.

At the same time, a null statement is used, as its name implies, to do nothing. What it means is that some statements become meaningful and improve the readability and integrity of the statement.

5. Nested loops

1. Concepts: Statements that nest another loop in one loop to mark the label of the nested loop, expressed with < label_name >.

Example: Print out multiplication tricks from 1x1 to 5x5

BEGIN
  <<outter>>
  FOR i IN 1..5 LOOP
    <<inner>>
    FOR j IN 1..5 LOOP
      CONTINUE WHEN j>i;
      DBMS_OUTPUT.PUT_LINE(i||'*'||j||'='||i*j);
    END LOOP inner;
  END LOOP outter;
END;

With two levels of nested loops, CONTINUE WHEN makes conditions to jump out of the current loop, << inner ><< outter >> is used to end the inner loop and the outer loop, respectively.

 

At this point, the basic content of plsql is complete, and some stored procedures and triggers are described in more detail later.

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　2018-08-15    15:26:17