PL/SQL programming, plsql Programming

PL/SQL programming, plsql Programming
PL/SQL programming 1: What is PL/SQL

(1) PL/SQL architecture:
PL/SQL engine is used for compilation and execution. PL/SQL blocks or subprograms reside on the Oracle server.

(2) PL/SQL block Introduction

PL/SQL is a block-structured speech that places a group of statement blocks in a fast state.

(3) operators and expressions:

PL/SQL speech support operators include Relational operators, general operators and logical operators, similar to SQL speech.

(4) constants and variable declarations

Before you reference variables and constants in the executable part of PL/SQL blocks, you must declare them. Partial declaration of variables and PL/SQL blocks is used in the executable part of PL/SQ blocks. Syntax:

Variable_name the initial value of the range variable;

(5) Notes:

You can use the following two annotation symbols in PL/SQL:

'--' Double minus sign

'/**/'

 

You must declare the variables and constants that can be referenced in the executable section of the PL/SQL quick.

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

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

 

Ii. PL/SQL data types

(1) LOB Data Type

(2) attribute type:

% Type: defines a variable. Its data Type is consistent with the data Type of a defined data variable (especially a column in a table). In this case, you can use % Type (advantage: you do not need to know the Data Type of the referenced database column. The referenced data type can be changed when implemented, so it is easy to maintain consistency without modifying the PL/SQL program)

% ROWTYPE: return a record type. The data type is consistent with the data structure of the database table. In this case, % ROWTYPE can be used. (advantage: you do not need to know the number and Data Type of referenced database columns. The number of columns in the referenced database and the database type can be changed, so it is easy to maintain consistency without modifying the PL/SQL program)

Iii. PL/SQL control statements

(1) condition control: the if statement is as follows:

If Boolean expression then

PL/SQL and SQL statements

End if;

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

If Boolean expression then

PL/SQL and SQL statements

Elst

Other statements

End if;

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

If Boolean expression then

PL/SQL and SQL statements

Other boolean expressions of Elsif, then

Other statements

Other boolean expressions of Elsif, then

Else

Other statements

End if;

 

(2) The case syntax is as follows:

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

Case conditional expression

When condition expression 1 then

Statement segment 1

When condition expression 2 then

Statement Segment 2

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

When condition expression n then

Statement segment n

End case;

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

Case conditional expression

When condition expression 1 then

Statement segment 1

When condition expression 2 then

Statement Segment 2

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

When condition expression n then

Statement segment n

Else statement segment

End case;

(3) Loop control: the Loop syntax is as follows:

Loop

The statement to be executed;

Exit when Condition Statement ------- jump out of the loop when the condition is met

End loop;

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

The While LOOP syntax is as follows:

While Boolean expression loop

The statement to be executed;

End loop;

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

The For loop syntax is as follows:

For loop counter In reverse lower limit ....... Upper Limit loop

Statement to be executed

End loop;

Iv. Exception Handling:

(1) pre-defined exception:

 

(2) handle user-defined exceptions:

Exceptions defined in the definition section of PL/SQL Blocks

Abnormal Situation Tlon

Throw exceptions:

Raise exception

Handle exceptions in PL/SQL Blocks

V. cursor:

(1) cursor Classification

Implicit cursor: returns a single row record.

Display cursor: multiple rows of records are returned.

Procedure for displaying a cursor:

(1) declared cursor: cursor name is

(2) open cursor: open name;

(3) extract cursor: fetch name into variables;

(4) close cursor: close name;

(5) use cyclic cursors to simplify the cursor reading Syntax:

For declaration record variable in name

Loop

Executable_statements

End loop;

(4.) differences between No_data_found and notfound

Select ......... The into statement returns 0 records and multiple records, which triggers no_data_found.

% Notfound is triggered when the where clause of the update or delete statement is not found.

Use % notfound or % found in the extract loop to determine the exit condition of the loop, instead of no_data_fuond.

Vi. Stored Procedure:

Subprogram composition:

(1) declaration part: type, cursor, constant, variable, exception, nested subroutine Declaration

(2) executable part: the executable part includes assigning values to control exceptions in the stored procedure.

(3) exception handling part: the exception handling program is responsible for handling exceptions during the execution of stored procedures.

The advantages of subprograms are as follows:

Modular, reusable, maintainability, and security.

(4) Stored Procedure usage:

1. Create a stored procedure: Syntax:

Create statement procedure storage name

Parameter List

Is |

Partial Declaration

Begin

Executable statement
Exception

Exception Handling Program

End storage name;

2. Call the Stored Procedure

(1) Use commands to call

Syntax:

Exce process name parameter list;

(2) There are three transmission methods for parameters:

Deliver by location

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

Pass by name

 

Mixed Transfer

3. the syntax of the stored procedure parameter mode is as follows:

Storage name in | out | in out datatype: = value assignment;

4. Stored Procedure access permission

----- Grant a permission to execute emp

Grant execute no add_emp to

------- Revoke permissions

Revoke execute on add_emp from;

5. delete a stored procedure

Drop procedure storage name;

7. debugging and tracking of stored procedures:

(1) debug with SQL * plus

(2) User PL/SQL Developer tool debugging

On_flag has three situations:

* 0 indicates that the process is successfully executed but no prompt is displayed.

* If the value is greater than 0, the process is successfully executed, but a prompt is displayed.

* If the value is less than 1, the process fails and a prompt is displayed.