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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s