Database review 10--PL/SQL, database review plsql

Database review 10--PL/SQL, database review plsql
Database review CH10 PL/SQL10.1 PL/SQL Introduction

PL/SQLIt is an Oracle procedural extension of SQL. PL/SQL can implement SQL-related procedural programs andStored ProcedureAndFunctionAllows SQL business logic to reside on the SQL Server to reduce client computing tasks and reduce network I/O.

10.2 PL/SQL programming BASICS (1) Overview

PL/SQL programming framework:

DECLARE     <Variable List>BEGIN    <Extented SQL Execution>EXCEPTION    <Exception Handler>END

If it is a stored procedure or function, change the headerCreate Procedure/Function ... IS <Variable List>(See the following example)

First, let's look at a complete PL/SQL program example:

Includeclare name varchar2 (20); BEGIN Select sname Into name From Student Where s # = '001'; DBMS_OUTPUT.PUT_LINE ('student name of Student 001 is: '| sname) EXCEPTION When NO_DATA_FOUND Then DBMS_OUTPUT.PUT_LINE ('student with student ID 001 does not exist '); When others Then DBMS_OUTPUT.PUT_LINE ('other error occurred'); END;

Where:

• name varchar2(20)Declare a typevarchar2Variable name with a length of 20
• select S into VIs an extended select statement that assigns the table field S to the variable V
• DBMS_OUTPUT.PUT_LINE()Is a PL/SQL standard output statement
• when <Exception> then <Extented SQL Execution>Is the Branch judgment for PL/SQL Exception Handling

(2) variable Declaration

Variable declaration is divided into common declaration, table field type declaration, and record type declaration

General statementname varchar2(20);Equivalent to table field type declarationname Student.sname%TYPEThe latter uses the type of a field in a table to declare the type of the variable.

The record type is equivalent to a struct to improve program readability, for example, defining the stu record type:

TYPE stu IS RECORD (    S# varchar2(10),    name varchar2(20),    age number);val stu;

Point operations for reference:val.name

(3) branches and loops

1. if Branch

If <Condition> Then    <Statement>Elseif <Condition> Then    <Statement>Else    <Statement>End If;

Note:=Is equal,:=Yes value assignment

2. while Loop

While <Condition> Loop    <Statement>End LOOP;

3. for Loop

For <Loop Variable> In [Reverse] <begin>..<end> Loop    <Statement>End LOOP;

4. loop

An exit statement must be added to the Loop body for an unconditional Loop.Exit;OrExit When <Condition>Can exit this loop

(4) Exception Handling

Standard Format:

Exception    When <exception_name_1> Or <exception_name_2> Then        <Statement>    ...    When Others Then        <Statement>

The System-defined prediction_name includes:

• NO_DATA_FOUND: no matching tuples found in the select into statement
• TOO_MANY_ROWS: select into returns multiple rows of data.
• VALUE_ERROR: Value assignment error (Type mismatch and long length)
• ZERO_DIVIDE: Division by zero
• TIMEOUT_ON_RESOURCE: Wait for resource timeout

You can useRaise <exception>Generate a custom Exception and capture it in the Exception section. You can alsoraise_application_errorGenerate a program error and capture it from the outside

10.3 cursor

PL/SQL can only process a single tuple at a time. To make it and SQL ** multi-group processingAnd introduceCursor **

CursorIt is a memory used on the client to store the intermediate results returned by SQL statements. The objective is to coordinate the conflicts between PL/SQL and SQL data processing count.

(1) declare and open and close

In PL/SQL, the cursor can only be storedselectStatement. Its declaration is as follows:

Declare    Cursor <cursor_name> IS <Select Statement>;

The cursor declaration is not executed immediately and must be explicitly called.openStatement (corresponding tocloseStatement ):

Begin    Open <cursor_name>;

(2) loop reading

There are several parameters in the cursor to help control the cursor to read data:

• % FOUND: Boolean, currentFetchReturns true for a row.
• % NOTFOUND: Boolean, currentFetchTrue when no row is returned
• % ISOPEN: Boolean. The cursor has been opened to true.
• % ROWCOUNT: Numeric value, returns the number of tuples that have been retrieved from the cursor

UseFetch intoYou can use the following two methods to access cursor data:

1. while Loop version Begin Open <cursor_name>; Fetch <cursor_name> into <record_type_variable>; While <cursor_name> % FOUND Loop DBMS_OUTPUT.PUT_LINE (...); fetch <cursor_name> into <record_type_variable>; End Loop; Close <cursor_name> ;... 2. for Loop version Begin For <Loop_variable> in <cursor_name> Loop DBMS_OUTPUT.PUT_LINE (...); end Loop ;...

Including:

• Fetch intoThe statement returns a tuple, And the cursor automatically points to the next tuple.
• For inYou can easily traverse the data in the cursor. The cursor is automatically opened before the cycle starts to read data, and the cursor is automatically closed after the cycle ends.<Loop_variable>Storage of declared record types

(3) parameter-based cursor

You can add parameters to a cursor. For example, you can specify parameters in the cursor.whereClause conditions:

Cursor cs_s(val Number(3)) IS Select * from stu where age = val;

10.4 stored procedures and functions

Generally, anonymous PL/SQL programs need to be compiled every time they run, while PL/SQL programs are namedStored Procedure,FunctionAndTriggerIs compiled and resident in the database, and can be called by SQL or other PL/SQL programs at any time.

The procedure is slightly different from the anonymous PL/SQL program in the Declaration format:

Create [or Replace] Procedure <procedure_name> (    <param_name> In|Out|In Out <type> [:= <initial_value>],    ...)AS|IS    <Variable Declaration>Begin    <PL/SQL statement>Exception    <Exception Handler>End;)

Including:

• The In parameter cannot be modified, and the Out parameter can only be assigned a value.
• The length cannot be specified, but % TYPE can be used.
• By default, parameter passing is performed in order of location. You can also explicitly use<param_name> => <param_value>Pass by name in disorder
• A function is a stored procedure with a return type.ASAdd the return type declarationReturn <return_type>And then use it in the function body.return <return_value>Return the value of the corresponding type. In other PL/SQL statements, pass the parameter call by function name and use:=Assign values to other variables

10.5 trigger

A trigger is a type of PL/SQL ** Stored Procedure ** that is automatically executed by a specific DML statement (Update, Insert, Delete) bound to a table **, one table can have multiple triggers, but one trigger can only be bound to one table.

The trigger concept has been explained in detail in the previous SQL review. The trigger declaration rules in PL/SQL are listed here:

Create [Or Replace] Trigger <trigger_name> // declare the Trigger action and Trigger time Before | After Delect | Insert | Update [Of <attribute_name>] // Or connects multiple Trigger time statement {Or Before | After Delect | Insert | Update [Of <attribute_name>]} * On <table_name> // if no value is specified, the statement is triggered, writing For Each Row indicates that the Row triggers [For Each Row] Declare...

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.