PL/SQL Programming

PL/SQL Programming One: what is PL/SQL

(1.) PL/SQL Architecture:
The PL/SQL engine is used to compile and execute a PL/SQL block or subroutine that resides on an Oracle server.

(2.) Introduction to PL/SQL blocks

PL/SQL is a block-structured voice that places a set of statement blocks in one fast.

(3.) Operators and Expressions:

The PL/SQL Voice support operator contains relational operators, general operators, and logical operators that are similar to those for voice.

(4.) Constants and variable declarations

Before you can reference variables and constants in the executable part of a PL/SQL block, you must first declare it. A partial declaration of a variable and a PL/SQL block, which is used in the executable part of the PL/SQ block. The syntax is as follows:

Variable_name the initial value of data_type variable range variable;

(5.) Note:

The following two types of annotation symbols can be used in PL/sql:

'--' double minus '

‘/* */’

Before the variables and constants referenced in the executable section of PL/SQL fast, they must be declared

Syntax: variable_name data_type[(size)][:=init_value];

Variable name data type size specifies the initial value of the variable

Two: PL/SQL data type

(1.) LOB data type

(2.) attribute type:

%Type: Defines a variable whose data type is consistent with the data type of a data variable that has already been defined (especially one of the table's columns) then you can use%Type (advantage: You don't have to know the data type of the referenced database column.) Referenced data types can be changed when implemented, easy to keep consistent, without having to modify PL/SQL programs)

%rowtype: Returns a record type whose data type is consistent with the data structure of the database table, which can be used%rowtype. (Pros: You don't have to know the number and data types of the referenced database columns.) The number of columns in the referenced database and the database type can be changed, easy to keep consistent, without modifying the PL/SQL program)

Three: PL/SQL control statements

(1.) Condition control: The IF statement is as follows:

If Boolean expression Then

PL/SQL and statements

End if;

--------------------------------------------

If Boolean expression Then

PL/SQL and statements

Elst

Other statements

End if;

---------------------------------------------

If Boolean expression Then

PL/SQL and statements

elsif other Boolean expressions Then

Other statements

elsif other Boolean expressions Then

Else

Other statements

End if;

(2.) Case syntax is as follows

----------------Format 1----------------

Case conditional expression

When conditional expression 1 then

Statement Segment 1

When conditional expression 2 Then

Statement Segment 2

................

When conditional expression n Then

Statement Segment N

End case;

----------------Format 2----------------

Case conditional expression

When conditional expression 1 then

Statement Segment 1

When conditional expression 2 Then

Statement Segment 2

................

When conditional expression n Then

Statement Segment N

Else Statement Segment

End case;

(3.) loop control: The loop loop syntax is as follows:

Loop

The statement to execute;

Exit when conditional statement jumps out of circulation-------condition is met

End Loop;

-------------------------------------------------------

The while loop syntax is as follows:

While boolean-expression loop

The statement to execute;

End Loop;

--------------------------------------------

The For loop syntax is as follows

For loop counter in reverse lower limit .... Upper Loop

The statement to execute

End Loop;

Four: Exception handling:

(1.) Pre-defined exceptions:

(2.) Handling user-defined exceptions:

Exception conditions defined in the definition section of a PL/SQL block

Abnormal condition Exceptlon

To throw an abnormal condition:

Raise Abnormal condition

corresponding handling of exception cases in the exception handling section of PL/SQL blocks

Five: Cursors:

(1.) Classification of cursors

Implicit cursor: Returns a single row of records

Display cursors: Returning multiple rows of records

To display cursor use steps:

(1.) Declaring a cursor: cursor name is

(2.) Open the cursor: the open name;

(3.) Extract cursor: fetch name into variables;

(4.) Close the cursor: close name;

(5.) Use circular cursors to simplify cursor read syntax:

For declaration record variable in name

Loop

Executable_statements

End Loop;

(4) The difference between No_data_found and NotFound

The Select.........into statement returns 0 records and multiple records are triggered No_data_found

Trigger%notfound when the WHERE clause of the UPDATE or DELETE statement is not found

Use%notfound or%found in the extraction loop to determine the exit condition of the loop without No_data_fuond

Six: Stored procedures:

Sub-Program Composition:

(1.) Declaration part: Type, cursor, constant, variable, exception, nested-procedure declaration

(2.) executable part: The executable part includes assignment to control the exception that occurs during the execution of the stored procedure

(3.) Exception Handling section: Exception handlers that handle exceptions that occur during the execution of a stored procedure.

The advantages of the subroutine are as follows:

Modularity, reusability, maintainability, security.

(4.) Stored Procedure usage:

1. Create a stored procedure: The syntax is as follows:

Create Statement Procedure Store Name

Parameter list

Is|as

Local declarations

Begin

Executable statements
exception

Exception handlers

END Store name;

2. Calling a stored procedure

(1.) Call with command

The syntax is as follows:

EXCE process name parameter list;

(2.) parameters are passed in three ways:

Pass by location

Exec add_emp (1111, ' Mary ', ' a ', ' manager ', 10);

Pass by name

Mixed Mode delivery

3. The stored procedure parameter pattern syntax is as follows:

Storage Name In|out|in out datatype: = assignment;

4. Stored Procedure access rights

-----grant a The ability to perform an EMP

Grant Execute no add_emp to a

-------Revoke Permissions

Revoke execute on add_emp from A;

5. Delete a stored procedure

Drop procedure Storage Name;

Seven: Debugging and tracking of stored procedures:

(1.) Commissioning under Sql*plus

(2.) User PL/SQL developer tool debugging

There are three types of On_flag:

*0 indicates that the procedure executed successfully without prompting information.

* Greater than 0 indicates successful process execution with prompt information.

* Less than 1 indicates that the procedure execution failed with a prompt message.

PL/SQL Programming