Oracle Study Notes 15

PL/SQL Introduction

PL/SQL is also a program Language called Procedural Language/SQL ). PL/SQL is an Oracle database pair

SQL statement extension. The features of the programming language are added to the use of common SQL statements. Therefore, PL/SQL organizes data operations and query statements in

In procedural units of PL/SQL code, complex functions or computing languages are implemented through logical judgment, loops, and other operations-Baidu encyclopedia.

PL/SQL is the core language of the Oracle system. Currently, many components of Oracle are written by PL/SQL. The following SQL statements can be used:

INSERT, UPDATE, DELETE, SELECT... INTO..., COMMIT, ROLLBACK, SAVEPOINT.

Block Structure

The basic unit of a PL/SQL program is a statement block. All PL/SQL programs are composed of statement blocks. It consists of three parts, namely the Declaration part,

Execution and exception handling.

The specific structure is as follows:

DECLARE

-- Declaration part: Used to declare functions, such as constants, variables, types, cursors, and names for exception handling.

BEGIN

-- Execution part: Execution program of the program

EXCEPTION

-- Exception Section to handle exceptions

END;

PL/SQL blocks can be divided into three types:

1. Unknown block: it is a dynamic structure and can only be executed once.

2. Subroutine: After the database is established, you can call them in other programs.

3. Trigger: When a database operation occurs, it will trigger some time and automatically execute some programs.

Identifier

1. The character length cannot exceed 30.

2. It must start with a letter.

3. It is composed of letters A-Z, a-z, 0-9, underscores (_), dollar signs ($), and pound signs.

4. SQL Reserved Words and keywords cannot be used.

Note

Single-line comment: the comment text placed on a single line. It starts with the symbol "--" and is commented out until the end of the line.

Multi-line comments: Begin with the symbol "/*" and end with the symbol "*/". All statements between the two symbols are commented out.

For example, the learning of programming language starts from helloworld. The first pl/SQL program is written below.

SQL> set serveroutput on;

BEGIN
Dbms_output.put_line ('Hello World ');
END;

Hello world
PL/SQL procedure successfully completed

The declare and exception parts are omitted. This is acceptable. declare can be omitted when no variable Declaration is available.

Part. You can also omit the exception part if no exception is caught. Function of dbms_output.put_line () and

System. out. println () is similar. It must be noted that the set serveroutput on statement is run before the pl/SQL statement is executed;

The execution result is invisible.

For example, use select... into... to query the salary of employee number 7934 In the emp table.

Declare
V_sal number (20 );
V_ename varchar2 (20 );
BEGIN
Select sal, ename into v_sal, v_ename from emp where empno = 7934;
Dbms_output.put_line (v_ename | ',' | v_sal );
END;

MILLER, 1300

Two variables, v_sal and v_ename, are defined above, respectively. Another way to define the variable type is to make

The statement uses "% type", that is, v_sal emp. sal % type; v_ename emp. ename % type;, which indicates the data class of the sal column in the table emp.

Type is the same. You can use a defined data variable or the data type of columns in a database table to define the variable type.

Variable type

Char: a fixed-length string.

Varchar2: a variable string.

Number: it can store floating point values or integer values.

Date: date type.

Boolean: boolean.

Record type

The record type stores logical data as a unit. The function is to store information that is identical but logically related to each other. Similar to JAVA

Class concept.

Declare
Type emp_record is record (
V_sal emp. sal % type,
V_ename emp. ename % type
);
E_r emp_record; -- declare a record type variable
BEGIN
Select sal, ename into e_r from e-mapreduce where empno = 7934;
Dbms_output.put_line (e_r.v_sal | ',' | e_r.v_ename );
END;

Use "." To Call The variable in the record type. For example, the v_sal variable e_r.v_sal must be used in the preceding example.

For example, define a record type that is the same as all columns in the emp table.

Declare
Emp_record emp % rowtype;
BEGIN
Select * into emp_record from E where empno = 7934;
Dbms_output.put_line ('wage '| emp_record.sal |', '| 'name' | emp_record.ename );
END;

Salary: 1300, name: MILLER
PL/SQL procedure successfully completed

The variables in the record type must correspond to all columns in the table. Although the definition of a row is acceptable, it is still troublesome to make

% Rowtype is used to implement the above requirements. However, if the corresponding column is worth calling, the actual column name in the table must be used.

Operator

Note: = is used to assign values. V_sal int: = 2000; defines the v_sal variable and assigns a value.

Process Control

Condition Statement

① If Boolean expression then

Pl/SQL and SQL statements;

End if;

② If Boolean expression then

Pl/SQL and SQL statements;

Else

Other statements;

End if;

③ If Boolean expression then

Pl/SQL and SQL statements;

Elsif Boolean expression then

Other statements;

Elsif Boolean expression then

Other statements;

Else

Other statements;

End if;

Case expression

Case expression

When value 1 then Statement 1;

When value 2 then Statement 2;

...

Else

Statment_else;

End case;

Loop Structure

The LOOP structure has three forms: LOOP, while loop, and for loop.

LOOP cycle failure

LOOP

Execute the statement;

Exit when condition statement;

End loop;

LOOP statement execution is required at least once.

For example, use LOOP to find the value of 1-10.

Declare
V_ I int: = 1;
V_sum int: = 0;
BEGIN
Loop
V_sum: = v_sum + v_ I;
Exit when v_ I = 10;
V_ I: = v_ I + 1;
End loop;
Dbms_output.put_line (v_sum );
END;

WHILE repeating

First, judge the condition of the cycle. Only those that meet the cycle condition can enter the cycle of the bad body for the cycle operation.

While Boolean expression loop

Statement executed (based on the bad body );

End loop;

When a while Loop is used, the statement to be executed may not be executed at one time, which is different from the Loop.

For example, if the while clause is used, calculate the sum of 1-10.

Declare
V_ I int: = 1;
V_sum int: = 0;
BEGIN
While v_ I <= 10 loop
V_sum: = v_sum + v_ I;
V_ I: = v_ I + 1;
End loop;
Dbms_output.put_line (v_sum );
END;

FOR Loop

For loop Variable in [reverse] lower limit... upper limit loop

Statement to be executed (based on the bad body );

End loop;

Once every cycle, the cyclic variable is automatically added with 1, and the reverse keyword is used. The cyclic variable is automatically reduced by 1,

For example, use a for-in-loop to calculate the factorial from 1 to 10.

Declare
V_ I int: = 1;
V_sum int: = 1;
BEGIN
For v_ I in 1 .. 10 loop
V_sum: = v_sum * v_ I;
End loop;
Dbms_output.put_line (v_sum );
END;

The execution process is: first, v_ I determines whether it is within the range of 1 .. 10. If the loop body, end loop, and then v_ I auto-increment 1 are executed, the range is determined.

Break, execute the loop body, know that v_ I is not in the range, and the loop ends.

GOTO statement

The goto statement indicates that the system jumps to the specified label unconditionally. Goto lebel_name; <label>.../* the label must be <> included */

Declare
V_ I int: = 1;
V_sum int: = 1;
BEGIN
For v_ I in 1 .. 10 loop
V_sum: = v_sum * v_ I;
If v_ I = 8 then
Goto at1;
End if;
End loop;
<At1>
Dbms_output.put_line ('at ');
Dbms_output.put_line (v_sum );
END;

When v_ I = 8, it jumps to the <at1> position and ends the loop.

NULL statement

A null statement is called a null statement, indicating that no operation is performed.