Oracle Database Tutorial: PL/SQL notes

PLSQL (procedurallanguagesql): it is an extension of oracle based on the standard SQL language. In PLSQL, You can nest SQL statements, define variables, define constants, and use explain

PL/SQL (procedural language/SQL): oracle is an extension based on the standard SQL language. In PL/SQL, You can nest SQL statements, define variables, define constants, and use explain

Based on the above table myinfo

),Since it is an extension, it will certainly make its functions more powerful!

PL/SQL is a powerful database process language!

Important: PL/SQL is closely integrated in Oracle databases.

PL/SQL is one of the three languages used by the Oracle database, and the other two are SQL and Java.

Program languages with similar functions as PL/SQL and other relational databases: Sybase ASE, Microsoft SQL Server's Transact-SQL, and PostgreSQL database's PL/pgSQL (imitating pl/SQL) and IBM DB2 SQL pl [1], both comply with iso SQL/PSM standards.

Bytes ---------------------------------------------------------------------------------------------------

Advantages of pl/SQL:

Improve application performance, modular design, reduce network transmission volume, and improve security

(For traditional database operations, let's think about it: SQL statements are written in java programs to access the database. First, you get the connection and compile SQL statements so that the database can understand your SQL statements, every compilation is a waste of time. When the data volume is large, your program performance is poor! Here, if we write some operations into a process, such as paging and transfer (the function is divided into a module, which is maintained by a dedicated person ......). This greatly improves the performance of our program, and database operations are used as a module. When a java program is called, it calls compiled SQL statements, therefore, you can use the Stored Procedure for optimization during program optimization .)

[Of course, there are other advantages. Please add]

Disadvantages:

Portability is flawed when you need to change the database.

| | |

PL/SQL writing specifications:

A:

Single line comment :--

Multi-line comment :/*...... */

B:

Naming rules for identifiers:

A. Define the variable. We recommend that you use v _ as the prefix, for example, v_sale.

B. Define constants. c _ is recommended as the prefix,Example:C_rate

C. Define the cursor. _ cursor is recommended as the suffix.Example:Emp_cursor

D. Define exceptions. We recommend that you use e _ as the prefix.Example:E_error

Bytes -----------------------------------------------------------------------------------

PL/SQL is based on blocks. Block Classification {mainly contains stored procedures, functions, triggers, and packages}

Block introduction:

As mentioned above,PL/SQL is based on blocks,We compile PL/SQL in writing blocks and simple application functions. We may only need one PL/SQL block to implement these functions. complex functions require multiple blocks, blocks can be nested.

A block consists of three parts: {definition part, execution part, and Exception Handling part}, namely:

Declare (optional)

/* Declaration part-Defines constants and variables. Cursor, exception, and complex data type */

Begin (must be dropped)

/* Execution part --- pl/SQL statements to be executed and SQL statements */

Exception (optional)

/* Handle various running errors */

End;(Must drop)

Compile a simple block first:

Example,

Example, example, and display password:

A box will pop up here, so that the input is the psw value, where passwd = & psw

What if the data cannot be queried? Oracle throws an exception,

Here we can use the Exception, Exception