Overview of PL/SQL

PL/SQL fully supports SQL data operations, fully supports SQL data types, and reduces the operation of converting data between applications and databases.

The basic unit that makes up a PL/SQL program is the program block. PL/SQL blocks are not executed by sending SQL statements one by one across the network, but as a set of SQL statements sent to Oracle as a whole. When the first sentence of the program begins with DECLARE or BEGIN, the system automatically recognizes that it is a PL/SQL statement. PL/SQL, when run in SQL PLUS, commits to the database for execution only on slashes, unlike SQL commands, which execute on semicolons.

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

Define variables, prefixed with v_, e.g. v_sal,v_job, etc.

Define constants prefixed with c_, e.g. c_rate

Define cursors with_cursor suffix, e.g. emp_cursor

Define exceptions, prefixed with e_, e.g. e_integrity_error

When defining PL/SQL table types, it is recommended to use_table_type as a suffix, such as sal_table_type

When defining PL/SQL table variables, it is recommended to use_table as a suffix, such as sal_table

When defining PL/SQL record types, it is recommended to use_record_type as a suffix, for example emp_record_type

When defining PL/SQL record variables, it is recommended to use_record as a suffix, such as emp_record

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

SQL keywords are capitalized, such as SELECT, UPDATE, SET, WHERE, etc.

PL/SQL keywords are capitalized, such as DECLARE, BEGIN, END, etc.

Data types are in upper case format, e.g. INT, VARCHAR2, DATE, etc.

Identifiers and parameters are in lowercase format, such as v_sal, c_rate, etc.

Database objects and columns are in lowercase format, e.g. emp, sal, ename, etc.