Oracle PL/SQL Block

Oracle PL/SQL Block

PL/SQL block Description: PL/SQL is the abbreviation of Procedure Language & Structured Query Language. It is an extension of standard database languages by Oracle.

PL/SQL block Syntax:

Declare
<Declaration part>
Begin
<Logical part>
Exception
When other then
<Exception Handling part>
End;

PL/SQL block syntax

Note: exception handling is similar to Exception Handling in other languages from low to high.

Common Oracle Exception Handling
Name	Description
Other	Including all exceptions (maximum exceptions)
Value_error	Value exception
No_data_found	No data found
Too_many_rows	Too much data

Declare PL/SQL packages

Syntax:

-- Package declaration similar interface
Create or replace package name
As
<The declaration part can be the stored procedure of variable functions>
End

-- Package implementation Interface
Create or replace package body package name
As
<Implement declaration of some function stored procedures>
End

PL/SQL package

Declare PL/SQL Functions

Syntax:

Create or replace function method name return value
As
<Declaration variable>
Begin
<Logical body>
End;

-------------------------------------- Split line --------------------------------------

Rlwrap

SQLPLUS spool to dynamic Log File Name

Oracle SQLPLUS prompt settings

Accelerate SQL return by setting SQLPLUS ARRAYSIZE (row prefetch)

PL/SQL Developer Practical Skills

-------------------------------------- Split line --------------------------------------