Database Review 10--pl/sql

Database review CH10 pl/sql10.1 PL/SQL Introduction

PL /SQL is the process of Oracle's expansion of the procedure for the implementation of a program of SQL-related procedures, and can be stored procedures and functions to allow a section of SQL business logic to reside in the SQL Server, To reduce client computing tasks and reduce network I/O

10.2 PL/SQL Programming Fundamentals (1) Introduction

The PL/SQL Programming framework is:

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

If a stored procedure or function, the header is replaced Create Procedure/Function ... IS <Variable List> (see the following example)

Let's look at a full PL/SQL Program example:

?DECLARE    name varchar2(20);BEGIN    SelectIntoFromWhere s#=‘001‘;     DBMS_OUTPUT.PUT_LINE(‘学号001的学生姓名是:‘ || sname)EXCEPTION    WhenThen        DBMS_OUTPUT.PUT_LINE(‘学号为001的学生不存在‘);    WhenThen        DBMS_OUTPUT.PUT_LINE(‘发生了其它错误‘);END;

which

• name varchar2(20)Declares a varchar2 variable of type length 20 name
• select S into Vis an extended SELECT statement that assigns the table's 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 of PL/SQL exception handling

(2) Variable declaration

Variable declarations are divided into general declarations, table field type declarations, and record type declarations

An ordinary declaration name varchar2(20); is equivalent to a table name Student.sname%TYPE field type declaration, which uses the type of a table's fields to declare the type of the variable

Record types are equivalent to structs, improving program readability, such as defining Stu record types:

TYPEISRECORD (    S# varchar2(10),    name varchar2(20),    age number);val stu;

Use point arithmetic when referencing:val.name

(3) Branching and looping

1.if Branch

IfThen    <Statement>ElseifThen    <Statement>Else    <Statement>EndIf;

Note that = is equal, := is an assignment

2.while Cycle

WhileLoop    <Statement>EndLOOP;

3.for Cycle

For <LoopVariableIn [Reverse] <begin>..<endLoop    <Statement>EndLOOP;

4.loop Cycle

Unconditional loop, you must include an exit statement inside the loop body Exit; or Exit When <Condition> to exit the loop

(4) Exception handling

Standard format:

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

System-defined exception_name include:

• No_data_found:select into statement did not find a matching tuple
• Too_many_rows:select into returns multiple rows of data
• Value_error: Assignment error (type mismatch, length too long)
• Zero_divide: except 0
• Timeout_on_resource: Waiting for resource timeout

You can either Raise <exception> generate a custom exception and then catch it in the exception segment, or you can raise_application_error generate a program error that is captured externally

10.3 Cursors

PL/SQL can only process a single tuple at a time, in order to keep it consistent with the characteristics of the sql** multi-group processing, introduce cursors * *

A cursor is a piece of memory on the client that is used to hold an SQL statement to return intermediate results, in order to coordinate the contradiction between PL/SQL and the number of data processed

(1) Declaration and open Close

PL/SQL Midstream can store only select the intermediate result of the statement, which is declared as follows:

Declare    IS <Select Statement>;

The cursor declaration is not executed immediately and requires an explicit invocation open of the statement (corresponding to the statement that closes the cursor close ):

Begin    Open <cursor_name>;

(2) Cyclic reading

There are several parameters in the cursor that are used to assist in controlling cursor read data:

• %found: Boolean, Fetch True when a row is currently returned
• %notfound: Boolean, Fetch True if no row is currently returned
• %isopen: Boolean, cursor opened as true
• %rowcount: Numeric value that returns the number of tuples that have been fetched from the cursor

Then Fetch into there are two ways to complete cursor data access using a statement (returning a tuple):

1.While循环版本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循环版本Begin    in <cursor_name> Loop        DBMS_OUTPUT.PUT_LINE(...);    End Loop;    ...

Among them are:

• Fetch intoStatement returns a tuple, and the cursor automatically points to the next tuple
• For inYou can simply traverse the data within the cursor, automatically open the cursor to read the data before the loop starts, loop the end to automatically close the cursor, and automatically <Loop_variable> store the claim record type

(3) Cursors with parameters

Cursors can add parameters, such as the conditions in which clauses can be qualified in Parameters where :

Select * from stu where age = val;

10.4 Stored procedures and functions

generally anonymous PL/SQL programs need to be compiled each time, while named PL/SQL programs such as stored procedures , functions , and triggers are compiled to reside in the database and can be called by SQL or other PL

Procedures and anonymous PL/SQL programs are only slightly different in the declaration format:

Create[orReplace] Procedure <procedure_name> (<param_name> in |  Out | inch Out <type> [: = <initial_value>], ...)   as |  is <Variable Declaration>Begin <PL/SQL statement>Exception <Exception Handler>End;)

Among them are:

• In parameter cannot be modified, out parameter can only be assigned value
• Parameters cannot be specified in length, but can be used with%type
• Parameter passing is passed by default in positional order, or it can be <param_name> => <param_value> passed explicitly by name
• A function is a stored procedure with a return type, AS preceded by a return type declaration Return <return_type> and then returned by the function body with a return <return_value> value of the corresponding type, called by the function name in other PL/SQL, and assigned := to other variables

10.5 Triggers

A trigger is a class of pl/sql** stored procedures that are bound on a table and triggered automatically by a specific DML statement (Update, Insert, Delete) * *, a table can have multiple triggers but a trigger can only be bound to one table

The concept of triggers is explained in detail in previous SQL review, which lists the trigger declaration rules in PL/sql:

Create[Or Replace]Trigger<trigger_name>//Declaration of trigger action and trigger timebefore| AfterDelect|Insert|Update[ of<attribute_name>]//OrConnecting multiple triggering action time declarations {Or before| AfterDelect|Insert|Update[ of<attribute_name>]}* on<table_name>//Do not write indicating statement trigger, write for  each RowRepresents a row trigger [ for  each Row]Declare...

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Database Review 10--pl/sql