PL/SQL Language TCCICOMPUTERCOACHING.COM

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

Punctuation characters 

 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

BOOLEAN

PLS_INTEGER

BINARY_INTEGER

REF CURSOR, explained in "Cursor Variables"

User-defined subtypes

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).

Alias:

Another (usually short) name for the referenced column, table, or view.

sub query

SQL SELECT statement (not a PL/SQL SELECT INTO statement).

numeric literal

Literal of a numeric data type.

Schema

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

To Learn More sql course in Ahmedabad, TCCI