PL/SQL Overview

PL/SQL provides full support for data operations in the database, fully supports SQL data types, and reduces the operation of converting data between applications and databases.

The basic unit that forms a PL/SQL program is a block. PL/SQL blocks do not have to be executed on the network each time they are executed, but are sent to Oracle as a whole set of SQL statements. When the first sentence of a program begins with declare or begin, the system automatically recognizes that it is a PL/SQL statement. When PL/SQL is running in Sqlplus, the execution of the database is committed when a slash is encountered, rather than a semicolon when it is encountered as a SQL command.

To improve the readability of the program, Oracle recommends that users define various identifiers according to the following rules:

Defining variables, using V_ as prefixes, such as v_sal,v_job, etc.

Define constants, prefixed with c_, such as C_rate

Defines a cursor, using _cursor as a suffix, such as emp_cursor

Define exceptions, using E_ as a prefix, such as E_integrity_error

When defining a PL/SQL table type, it is recommended to use _table_type as a suffix, such as Sal_table_type

When defining a PL/SQL table variable, it is recommended to use _table as a suffix, such as sal_table

When defining a PL/SQL record type, it is recommended to use _record_type as a suffix, such as Emp_record_type

When defining a PL/SQL record variable, it is recommended to use _record as a suffix, such as Emp_record

To improve the readability and performance of the program, Oracle recommends that users write code according to the following casing rules:

The SQL keyword is in uppercase format, such as SELECT, UPDATE, SET, where, and so on

PL/SQL keywords in uppercase format, such as Declare,begin,end

Data type in uppercase format, such as Int,varchar2,date

Identifiers and parameters in lowercase format, such as V_sal,c_rate

database objects and columns in lowercase format, such as Emp,sal,ename

PL/SQL Overview