PL/SQL (Procedure Language & Structured Query Language)

The current PL/SQL includes two parts, part of the database engine, and a separate engine that can be embedded in many products, such as the C language, the Java language, and so on. You can call these two parts: database PL/SQL and tool PL/SQL.

The basic unit in PL/SQL is block, and all PL/SQL programs are made up of blocks. The blocks in PL/SQL consist of 3 parts: Declaration part, program code, exception handling code. As follows:

DECLARE

/* Declaration section: The variables, types and cursors used in this declaration, and the local stored procedures and functions */

BEGIN

/* Execute part: Process and SQL statement, which is the main part of the program */

EXCEPTION

/* Execute exception section: Error handling */

END;

Interview question: How to solve the problem of slow system? If you are using traditional methods, you need to optimize your SQL statements, and another way is to write SQL statements in PL/

Benefits: Modular program design, faster, less network traffic, improved reusability and sharing of code, and increased security (stored procedures encapsulate the table's specific fields).

Cons: Poor portability (if all stored procedures are rewritten when you turn to DB2 by Oracle)

First, the process

Creating process Syntax :

CREATE [OR REPLACE] PROCEDURE procedure_name

[(argment [{in | In Out}] Type,

argment [{in | Out | In Out}] Type]

{is | As}

< type. Description of the variable >

BEGIN

< Executive section >

EXCEPTION

< optional exception error handlers >

End[procedure_name];

Local functions and procedures can also be established within a block in a PL/SQL program, and these functions and procedures are not stored in the database, but can be called repeatedly in the PL/SQL program in which they are created. Local functions and procedures are defined in the Declarations section of the PL/SQL block, which have the same syntax format as stored functions and procedures, but cannot use the Create OR REPLACE keyword.

Second, function

To create the function syntax:

CREATE [OR REPLACE] FUNCTION function_name

[(argment [{in| In Out}] type,

argment [{in | Out | In Out}] type]

RETURN Return_type

{is | As}

< type. Description of the variable >

BEGIN

Function_body (final with return statement)

EXCEPTION

Other statements

End[function_name];

Three, package and package body

Creating a package definition :

CREATE [OR REPLACE] Package Package_name

[AUTHID {current_user | Definer}]

{is | As}

[Public data type definition [public data type definition] ...]

[Public Cursor Declaration [public Cursor declaration] ...]

[public variables, constant declarations [public variables, constant declarations] ...]

[Public Sub-Program declaration [public subroutine declaration] ...]

END [Package_name];

Creating a package body :

CREATE [OR REPLACE] Package BODY package_name

{is | As}

[Private data type definition [private data type definition] ...]

[private variable, constant declaration [private variable, constant declaration] ...]

[Private subroutine declaration and definition [private subroutine declaration and definition] ...]

[Public cursor definition [public cursor definition] ...]

[Public Sub-program definition [public subroutine definition] ...]

BEGIN (?? Is there?)

PL/SQL statements

END [Package_name];

Iv. triggers

A total of 5 kinds.

the general syntax for creating triggers is :

CREATE [OR REPLACE] TRIGGER trigger_name

{before | After | INSTEAD of}

{INSERT | DELETE | UPDATE [ of column [, Column ...]}

on {[Schema.] table_name | [Schema.] View_name}

[Referencing {old [as] old | NEW [as] new| Parent as parent}]

[For each ROW]

[When condition]

Trigger_body;

The referencing clause describes the correlation name, which can be used to refer to the current new and old column values in the PL/SQL block and when clauses of a row trigger, with the default correlation name of both OID and new. When you apply a related name in a PL/SQL block of a trigger, you must precede them with a colon (:), but you cannot add a colon in the When clause.

The When clause describes the triggering constraint condition. When Condition is a logical expression, it must contain a correlation name, not a query statement, or a PL/SQL function. The trigger constraint specified by the When clause can only be used in before and after row triggers, not in instead of row triggers and other types of triggers.

Trigger Trigger Order:

Step1: Executing before statement-level triggers

Step2: For each row affected by the statement

Executing before row-level triggers

Executing DML statements

Executing after-row-level triggers

Step3: Executing after-statement-level triggers

Limitations OF Triggers

L CREATE Trigger statement text can not exceed 32KB character length;

L The SELECT statement in the trigger body can only be select ... Into ... Structure, or the SELECT statement used to define the cursor.

L cannot use database transaction control statement COMMIT in trigger; ROLLBACK, Svaepoint statement;

L A procedure or function called by a trigger cannot use a database transaction control statement;

L cannot use long, long RAW type in trigger;

The column values of the LOB type column can be referenced within the trigger, but cannot be modified by: NEW to modify the data in the LOB column;

The table accessed by the trigger is constrained by the table, which is the "Table of Changes" later.

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

: The new modifier accesses the value of the column after the operation is completed

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

Characteristics	INSERT	UPDATE	DELETE
Old	Null	Effective	Effective
NEW	Effective	Effective	Null

1, statement trigger (refers to when a trigger event occurs, the trigger executes only once)

CREATE [OR REPLACE] TRIGGER < trigger name >

[before| After] < triggering events > on < table names >

<pl/sql Program Body >

2. Line trigger (refers to the trigger is executed individually once for each row of data affected by the action when a triggering event occurs)

CREATE [OR REPLACE] TRIGGER < trigger name >

[before| After] < triggering events > on < table names >

For each ROW

<pl/sql Program Body >

When the For each ROW option is omitted, the before and after triggers are statement triggers, while the instead OF triggers are row triggers.

3. INSTEAD of trigger (only for view, Oracle activation trigger without triggering event)

CREATE [OR REPLACE] TRIGGER < trigger name >

INSTEAD of < triggering events > on < table names >

<pl/sql Program Body >

4. User Event Trigger

5. System Event Trigger