PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation’s procedural extension for SQL and the Oracle relational database. PL/SQL is available in Oracle Database (since version 7), Times Ten in-memory database (since version 11.2.1), and IBM DB2 (since version 9.7). Oracle Corporation usually extends PL/SQL functionality with each successive release of the Oracle Database.
PL/SQL works analogously to the embedded procedural languages associated with other relational databases.
The basic unit of a PL/SQL source program is the block, which groups together related declarations and statements. A PL/SQL block is defined by the keywords DECLARE, BEGIN, EXCEPTION, and END.
PL/SQL Language Fundamentals
The PL/SQL Character Set
The PL/SQL language is constructed from letters, digits, symbols, and whitespace.
Every database character set includes these basic characters:
Latin letters: A through Z and a through z
Decimal digits: 0 through 9
Whitespace characters: space, tab, new line, and carriage return
PL/SQL source text that uses only the basic characters can be stored and compiled in any database.
Every PL/SQL constant, variable, parameter, and function return value has a data type that determines its storage format and its valid values and operations.
The PL/SQL scalar data types are:
The SQL data types
REF CURSOR, explained in “Cursor Variables”
Unlike SQL, PL/SQL lets you declare variables, to which the following topics apply:
CHAR and VARCHAR2 Variables
LONG and LONG RAW Variables
ROWID and UROWID Variables
Different Maximum Sizes
The SQL data types listed below have different maximum sizes in PL/SQL and SQL.
|Data Type||Maximum Size in PL/SQL||Maximum Size in SQL|
|CHARFoot 1||32,767 bytes||2,000 bytes|
|NCHARFootref 1||32,767 bytes||2,000 bytes|
|RAWFootref 1||32,767 bytes||2,000 bytes|
|VARCHAR2Footref 1||32,767 bytes||4,000 bytes|
|NVARCHAR2Footref 1||32,767 bytes||4,000 bytes|
|LONGFoot 2||32,760 bytes||2 gigabytes (GB) – 1|
|LONG RAWFootref 2||32,760 bytes||2 GB|
|BLOB||128 terabytes (TB)||(4 GB – 1) * database_block_size|
|CLOB||128 TB||(4 GB – 1) * database_block_size|
|NCLOB||128 TB||(4 GB – 1) * database_block_size|
PL/SQL Static SQL
Static SQL is a PL/SQL feature that allows SQL syntax directly in a PL/SQL statement. Static SQL has the same syntax as SQL, except as noted.
SELECT INTO Statement
The SELECT INTO statement retrieves values from one or more database tables (as the SQL SELECT statement does) and stores them in variables (which the SQL SELECT statement does not do).
Another (usually short) name for the referenced column, table, or view.
SQL SELECT statement (not a PL/SQL SELECT INTO statement).
Literal of a numeric data type.
Name of the schema that contains the table or view. Default: your schema.
If you like this post then share and like this post.
Visit us @tccicomputercoaching.com
Call us @ 98256 18292