PL/SQL Introductions
PL/SQL stands for Procedural Language / Structured Query
Language, is a combination of
procedural programming languages with the
programming language of SQL.
Oracle database using
PL/SQL to perform the
manufacture of objects such as
Stored Procedure, Stored Function,
Package and Trigger.
Such objects are known to modular blocks PL/SQL. Beside
from making modular blocks, PL/SQL can also make non-modular
block of PL/SQL known as anonymous block. This post will give examples
of PL/SQL block both modular and non-modular.
Before going any further, let us find out the parts of a block of PL/SQL
A. Non-Modular blocks (Anonymous)
DECLARE
Variable declaration;
Declaration of constants;
Explicit Cursors;
BEGIN
Program content;
conditional;
Repetition;
Implicit Cursor;
Exceptions;
Variable declaration;
Declaration of constants;
Explicit Cursors;
BEGIN
Program content;
conditional;
Repetition;
Implicit Cursor;
Exceptions;
[return variable];
END;
END;
/
B. Modular blocks (Object)
B. Modular blocks (Object)
CREATE
OR REPLACE <object_type> <object_name> [<parameters>][return
<data type>] AS
Variable declaration;
Declaration of constants;
Explicit Cursor;
BEGIN
Program content;
conditional;
Repetition;
Implicit Cursor;
Exceptions;
Variable declaration;
Declaration of constants;
Explicit Cursor;
BEGIN
Program content;
conditional;
Repetition;
Implicit Cursor;
Exceptions;
[return variable];
END [<object_name>];
END [<object_name>];
/
PL / SQL can be made on multiple interfaces. Interface that can be used are SQL*Plus, Oracle SQL Developer, PL/SQL Developer and TOAD. The examples that will be given are using SQL*Plus as an interface. To enable the SQL*Plus please run cmd from the run menu of Windows. After performing the command prompt please log in to your Oracle database by typing in a username and password. Login examples are:
sqlplus system/mypassword
system is the default user and mypassword is the password that you type in when installing Oracle database. If the login is successful then SQL> would appear to indicate SQL*Plus is ready to receive commands to manage Oracle databases.
When the connection successfully established, first thing you need to do is type this command:
SET SERVEROUTPUT ON
that command will allow you to view the PL/SQL output result.
Next we're going to create Non-Modular Block PL/SQL (Anonymous Block) by following A section template of this post.
Here is the code of Anonymous Block PL/SQL
Here is example for Modular Block PL/SQL (Object) by following B section template of this post.
Here is the code of Modular BLock PL/SQL
Here is the tutorial video:
system is the default user and mypassword is the password that you type in when installing Oracle database. If the login is successful then SQL> would appear to indicate SQL*Plus is ready to receive commands to manage Oracle databases.
When the connection successfully established, first thing you need to do is type this command:
SET SERVEROUTPUT ON
that command will allow you to view the PL/SQL output result.
Next we're going to create Non-Modular Block PL/SQL (Anonymous Block) by following A section template of this post.
Here is the code of Anonymous Block PL/SQL
Here is example for Modular Block PL/SQL (Object) by following B section template of this post.
Here is the tutorial video:
Leave a Comment