PL/SQL Programming

Have a chat, don't abandon (cover your face)

PL/SQL is an extension of Oracle 's process language and structured Query language, and is an object-oriented query language

has the following advantages : The characteristics of programming language, the use of procedural language control program structure, exception handling mechanism, better portability, reduce network interaction, improve the performance of the program.

Introduction :

The PL/SQL engine executes only the procedural language, which is executed by the SQL executor.

PL/SQL consists of three parts: declaration, execution, exception handling

Declare

-- declarations of variables and constants

Begin

-- procedures and SQL statements

Exception

-- Exception handling

End;

PL/SQL data type :

%TYPE: the variables defined are consistent with the types of data variables that are already defined .

%rowtype: data type is the type of row of data in a table

PL/SQL Control Language :

If statement case statement Loop statement

PL/SQL exception handling :

handling user-defined exceptions : Define exception conditions in the definition section: Exception exception;

Throw exception : RAISE abnormal condition ;

handling in the Exception handling section

Basic principles of cursors :

When the PL/SQL block of a crud is executed , Oracle assigns it a buffer in memory and puts the execution result in the buffer. The cursor is a pointer to the area.

cursors provide an application with a method for processing each row of data in a query result set of multiple rows of data separately .

cursors are static and dynamic cursors, statically, when compiled with a cursor that explicitly selects a SELECT statement, as well as a sub-display and an implicit cursor. Displays the cursor, using the display cursor to read rows at a time when multiple records are returned.

Use steps : 1. declaring Cursors

Create cursor_name is select_statement

1. opening : Open cursor_name
2. extract cursor : Fetch cursor_name into variables;
3. Close cursor : Close cursor_name

Using a display cursor to delete or update

Declaring an update cursor

Create cursor_name is select_statement for update

Update

Update ... where cursor of cursor_name

To delete a cursor :

Delect from table_name where cursor of cursor_name

Using circular cursors to simplify cursor reading

Loop Leng Opens the cursor, automatically closes the cursor after the row is fetched from the result set and automatically when all rows are played . cursors are automatically created %rowtype A variable of type is used as the record index

Grammar

For Record_index in cursor_name

Loop

Excuteable_statements

End Loop;

Sub-Program is named when the PL /SQL blocks, including stored procedures and functions. Use a stored procedure to perform an operation and return a value using a function.

PL/SQL Programming