Preface
Body
PL/SQL
(Procedure Language, Procedural Language) SQL 1999 a structured Language commonly used by major database vendors PL/SQL only supports Oracle databases
Basic syntax
Multi-line comment/**/single-line comment --
PLSQL Block
Structure
[Declare]
Define Variables
Begin
Process statement
[Exception]
Exception Handling
End;
Example output helloworld
Begin
Dbms_output.put_line ('hello ');
End;
-- If no output is displayed after the process is executed, the server output must be opened.
Run
SQL> set serveroutput on/off;
SQL>
SQL> begin
2 dbms_output.put_line ('hello ');
3 end;
4/
Hello
PL/SQL procedure successfully completed
Variable
Declare
Variable name: variable type;
Begin
End;
Identifier: cannot start with a number and cannot exceed 30 characters
Variable Initialization
Use: = to assign values
V_age number (10): = 10;
Variable type
Number (m, n) numeric type
Char () fixed-length string
Varchar2 () variable-length string
Date type
Boolean (true/false/null) boolean Type
Binary_integer signed integer
Note: The boolean type cannot be output directly.
Sample Code:
Note:: The variable name cannot be an SQL keyword. It is best not to have the same name as the field in the table.
Variable name:
V _ actual words
Constant Name:
C _ actual meaning
Parameter Name
P _ actual meaning
Cursor type
Name_cursor
Table type
Name_table
Record type
Name_record
Variable Scope
The variable ranges from the declaration to the end of the corresponding module to its scope.
-- Sample code for variable scope
Other types of Variables
1. Table type
Define table types
Type table type name is table of table type storage basic type index by binary_integer
Declare variables as table types
Variable name table type name
Table types are similar to arrays in java and used to store data of the same type.
Attribute:
First indicates getting the first subscript of the table Type
Last indicates obtaining the last subscript of the table type.
Count indicates the number of data types in the table.
Next (subscript) indicates the next subscript
Prior (subscript) indicates the first subscript
Sample Code:
Table type through subscript operation
2. Record type
Used to store a record
Type record type name is record (field1 type, field2 type)
Define variables and set the variable type to the record type
V_record record type name
Sample Code
A simple way to define record types
% Rowtype defines the record type
Table name % rowtype
Sample Code
<div></div>
A simple way to define table types
% Type defines the table type
Table name. Field name % type
Sample Code
Code example: store all records in a table in a variable
Operator
Arithmetic Operators
Consistent with java
V_age number (3): = 0;
V_age: = v_age + 1;
Comparison Operators
><>== Not equal! = <> ~ = ^ =
Logical operators
And or not
Between and
Value assignment operator
: =
Process Control
Branch:
1. if Branch
If Boolean expression then
............
Else
............
End if;
Multiple branches:
If Boolean expression then
............
Elsif Boolean expression then
............
Elsif Boolean expression then
............
Else
............
End if;
Case: -- search for employees and increase their salaries based on their positions
2. case branch, similar to the switch ''' case in java
Case variable
When condition then
Statement ·····
When condition then
Statement ·····
When condition then
Statement ·····
Else
Statement ·····
End case;
Case
Loop:
1. loop
Define a cyclic Index
V_index binary_integer: = 0;
Loop
If v_index = number of cycles then
Exit;
End if;
Loop body
Auto-increment cycle index
V_index: = v_index + 1;
End loop;
Case:
-- Loop
Declare
-- Define the cyclic Index
V_index binary_integer: = 1;
Begin
-- Define loop
Loop
-- Define conditions for release cycles
If v_index = 5 then
Exit;
End if;
-- Execution cycle body
Dbms_output.put_line (v_index );
-- Auto-increment of cyclic Index
V_index: = v_index + 1;
End loop;
End;
2. for Loop
Define a cyclic Index
V_index binary_integer;
For loop index in lower limit index... upper limit index loop
Loop body
End loop;
Case:
-- For Loop
Note: The starting value of the cyclic index must be smaller than the ending value.
Implement cyclic inversion using the reverse keyword
-- For Loop Inversion
Declare
V_index binary_integer;
Begin
For v_index in reverse 10 .. 20 loop
Dbms_output.put_line (v_index );
End loop;
End;
3. while Loop
Define cycle index
V_index binary_integer: = 1;
While Boolean expression loop
Loop body
Auto-increment cycle index
End loop;
Case:
-- While loop
Declare
V_index binary_integer: = 1;
Begin
While v_index <5 loop
Dbms_output.put_line (v_index );
V_index: = v_index + 1;
End loop;
End;
Loop nesting
Use the <label> label method to control loops
Case:
4. goto jump
Define SQL in PL/SQL
1. DQL Language
Select statement requirements: 1. The into keyword must be written.
2. A query statement can return only one value.
Exception example: no value of no_data_found; too many values of too_many_rows
2. DML (insert/update/delete)
Note: Execute DML statements to process transactions.
Dynamic parameter passing
& + Variable for dynamic parameter passing
-- Execute DML statements
Declare
Begin
-- Common DML statements
Delete from emp where empno = & xx;
-- Transaction Processing
Commit;
End;
3. DDL (create/drop/alter/truncate)
The DDL statement must be in execute immediate ('ddler ')
Cursor)
During SQL statement execution, an area is opened in the memory to store the executed SQL statements and returned data. We call this memory area context ); A cursor is a pointer to the context.
Cursor type
Implicit cursor: created and executed by the database management system
Display cursor: A programmer is responsible for creating and executing the cursor.
Cursor attributes
% Rowcount stores the number of records affected by cursor execution.
Operation attribute
Display cursor: Custom cursor name % rowcount
Implicit cursor: SQL % rowcount
% Found: determines whether the current data contains a true/fase
% Notfound: determines whether the current data contains a true/fase
% Isopen determine whether the cursor is enabled
Display cursor
1. Create a cursor
Cursor name is query statement
2. Enable cursor
Open cursor name
3. Get Data
Fetch cursor name into variable
4. Close the cursor
Close cursor name
Case: -- display cursor
For loop to loop cursor
Passing parameters when defining a cursor
Note: Do not set the parameter length when setting the parameter. The real parameter is passed when the cursor is enabled.
The cursor cannot be enabled or disabled repeatedly.
Variable defining the cursor type
1. Define the cursor type
Type cursor type name is ref cursor return result type
2. variables defining the cursor type
Variable name cursor type name
Procedures and functions
A stored procedure is a named PL/SQL block.
The process is equivalent to the method in java. It focuses on implementing certain business functions.
A function is also equivalent to a method in java. It focuses on Computing and always returns results.
Both procedures and functions are program code blocks that can be permanently stored in the database, and are executed by calling the application.
Basic Process Structure
Create [or replace] procedure process name (the type of the form parameter name and the type of the form parameter name ······)
Is |
Define Variables
Begin
Process body
Exception
Exception
End;
1. Process containing input parameters
Input parameters can be omitted if they are identified in.
2. process without Parameters
3. Process with output parameters
Output parameters are identified by out
Process call
1. Call through anonymous Blocks
Input parameters
Output parameter Process
Process without Parameters
Begin
Mypro_noparam;
End;
2. Command Line call
Call input parameters
SQL> exec mypro (7788,3000 );
Output Parameters
SQL> var v_sal number; registers the variable
SQL> exec mypro (7788,: v_sal);: variable names use variables to receive output
No parameters for the call
SQL> exec mypro;
Sample Code:
1. Write a process to encapsulate data in the emp table
2. Write a process and enter the employee ID. The cursor is used to obtain the information of the employee's subordinates.
3./* calculate the prime number of 100-200 */
3. java call process code example
Custom Functions
Function. SQL code example
Exception
System exceptions include pre-defined exceptions and pre-defined exceptions. They are defined by the database and contain exception codes, exception names, and exception information. There are about 20 types of exceptions, for example, too_many_rouws; no_data_found; zero_divide. non-predefined exception: Exception Code, exception information, but no exception name
Non-predefined Exception Code example
Custom exception
Sample custom Exception Code
The package is used to manage processes and function packages. It can be divided into headers and packages.
Package code example
Example code of the Process body containing a jdbc call
Note: A packet must have a packet header. the packet header declares functions, processes, variables, and constant packages to implement the defined functions and process package encapsulation. the packet header can be separately defined, the separately defined header can only contain constants.
Trigger
Similar to Listener in java
The database management system is responsible for calling and executing trigger calls by triggering the things that the trigger listens.
Table-level triggers (Listen to the entire database table) Row-level triggers (listen to each row in the table)
Trigger code example
Note: trigger execution sequence
A table-Level Trigger. A Row-Level Trigger acts on the same table.
1. before table-level triggers 2. before row-level triggers 3. after row-level triggers 4. after table-level triggers
System triggers
DBA used to debug the system on database
Note:The trigger does not contain parameters, and the value is not put back. No transaction processing is performed.
Summary
