Introduction to PL/SQL
Introduction to PL/SQL
• Procedural Language extension for SQL
• Oracle Proprietary
• 3GL Capabilities
• Integration of SQL
• Portable within Oracle data bases
• Callable from any client
Characteristics of PL/SQL:
• PL/SQL is seamlessly integrated with SQL.
• It offers an extensive feature of error checking.
• It also offers numerous varieties of data types.
• It offers programmers to select programming structures.
• It is block-structured programming that provides support
to functions and procedures.
• It supports object-oriented programming concepts and
features.
• It helps the event of web applications and server page
development.
Advantages of PL/SQL:
• SQL is that the quality database language, and PL/SQL is strongly integrated
with SQL. PL/SQL supports both static and dynamic SQL.
• In Static SQL DML operations and transaction, control is supported from
PL/SQL block. In Dynamic SQL, embedded DDL statements are allowed in
PL/SQL blocks.
• It sends an entire block of statements to the database on just one occasion.
This helps to reduce network traffic and also provides high performance for the
applications.
• PL/SQL provides high productivity performance to programmers because it can
query, transform, and update data during a database.
• PL/SQL reduces time consumption on design and debugging by strong
features, like exception handling, encapsulation, data hiding, and object-
oriented data types.
• The applications written in PL/SQL are highly portable.
• PL/SQL gives a high-security level.
• PL/SQL gives access to predefined SQL packages.
Disadvantages of PL/SQL:
• Procedures that are stored in PL/SQL use high memory
spaces.
• These stored procedures lack functionality debugging.
• Any change taking place in the primary database needs
a change in the presentation layer also.
• It does not separate the roles of back-end developer and
front-end developer.
• It is very difficult to separate HTML development from
PL/SQL development.
Difference between SQL and PL/SQL:
SQL PL/SQL
PL/SQL is a block-structure of codes
SQL is Structured Query Language,
that are used to write the entire
a single line statement to perform
program blocks, procedure, function,
DML and DDL operations.
etc.
PL/SQL is a block of code that
It is a declarative statement that
contains a procedure that defines
defines what should be done.
how things should be done.
It executes as a single statement at It executes as a whole code block at
a time. a time.
It is used mainly for data PL/SQL is mainly used to develop
manipulation. an application.
It does not interact with the database
It interacts with a database server
server.
SQL cannot contain PL/SQL code PL/SQL is an extension of SQL so
inside it. that it contains SQL inside it.
Structure of PL/SQL
• PL/SQL is Block Structured
A block is the basic unit from which all PL/SQL
programs are built. A block can be named
(functions and procedures) or anonymous
• Sections of block
1- Header Section
2- Declaration Section
3- Executable Section
4- Exception Section
Structure of PL/SQL
HEADER
Type and Name of block
DECLARE
Variables; Constants; Cursors;
BEGIN
PL/SQL and SQL Statements
EXCEPTION
Exception handlers
END;
PL/SQL Program Blocks
• Declaration section
– Optional
• Execution section
– Required
• Exception section
– Optional
• Comment statements
Enclosed within /* and */ for
several lines’ comments /* Script: Student register
Purpose: to enroll students in
-- for single line comments class */
-- Script: Student register
-- Purpose: to enroll students
9
Structure of PL/SQL
DECLARE
a number;
text1 varchar2(20);
text2 varchar2(20) := “HI”;
BEGIN
---------- ---------- ----------
END;
Important Data Types in PL/SQL include
NUMBER, INTEGER, CHAR, VARCHAR2, DATE
etc
to_date(‘02-05-2007','dd-mm-yyyy') { Converts
String to Date}
PL/SQL Arithmetic Operators in
Describing Order of Precedence
• Parentheses are used to force PL/SQL
interpreter to evaluate operations in a certain
order total_hours_worked - 40 * over_time_rate
(total_hours_worked – 40) * over_time_rate
11
Questions: 2 * 2 ** 2 = ? 100 / 2 * 5 = ?
Assignment Statements
DECLARE
variable1 NUMBER := 0;
variable2 NUMBER := 0;
• Assigns value to variable BEGIN
variable2 := variable1 +1;
• Operator: := END;
• Syntax: variable_name := value;
• String literal within single quotation mark
• Examples:
current_s_first_name := ‘Tammy’;
current_student_ID NUMBER := 100;
• Result of adding a value to a NULL value is another
Q: What is the
final value of
NULL value variable2?
• DEFAULT keyword can be used instead of
assignment operator
12
Structure of PL/SQL
• Data Types for specific columns
Variable_name Table_name.Column_name%type;
This syntax defines a variable of the type of the
referenced column on the referenced table
PL/SQL Control Structure
• PL/SQL has a number of control structures
which includes:
• Conditional controls
• Iterative or loop controls.
• Exception or error controls
• It is these controls, used singly or together, that
allow the PL/SQL developer to direct the flow of
execution through the program.
PL/SQL Control Structure
• Conditional Controls
IF....THEN....END IF;
IF....THEN...ELSE....END IF;
IF....THEN...ELSIF....THEN....ELSE....END IF;
PL/SQL Control Structure
• LOOP
...SQL Statements...
EXIT;
END LOOP;
• WHILE loops
• WHILE condition LOOP
...SQL Statements...
END LOOP;
• FOR loops
• FOR <variable(numeric)> IN [REVERSE]
<lowerbound>..<upperbound> LOOP .... ..... END LOOP;
Cursor in PL/SQL
A cursor can be basically referred to as a pointer to the
context area.Context area is a memory area that is created by
Oracle when SQL statement is processed.The cursor is thus
responsible for holding the rows that have been returned by a
SQL statement.Thus the PL/SQL controls the context area by
the help of cursor.An Active set is basically the set of rows
that the cursor holds. The cursor can be of two types: Implicit
Cursor, and Explicit Cursor.
Advantages of Cursor:
•They are helpful in performing the row by row processing and
also row wise validation on each row.
•Better concurrency control can be achieved by using cursors.
•Cursors are faster than while loops.
Disadvantages of Cursor:
•They use more resources each time and thus may result in
network round trip.
•More number of network round trips can degrade the
performance and reduce the speed.
Trigger in PL/SQL
A Trigger is basically a program which gets automatically
executed in response to some events such as modification
in the database.Some of the events for their execution are
DDL statement, DML statement or any Database
operation.Triggers are thus stored within the database and
come into action when specific conditions match. Hence,
they can be defined on any schema, table, view etc. here are
six types of triggers: BEFORE INSERT, AFTER INSERT, BEFORE
UPDATE, AFTER UPDATE, BEFORE DELETE, and AFTER
Advantages of Trigger:
•They are helpful in keeping the track of all the changes within
the database.
•They also help in maintaining the integrity constraints.
Disadvantages of Trigger:
•They are very difficult to view which makes the debugging
also difficult.
•Too much use of the triggers or writing complex codes within
a trigger can slow down the performance.
S.N
Cursor Trigger
O
It is a pointer which is used to control the It is a program which gets executed in
1. context area and also to go through the response to occurrence of some
records in the database. events.
A cursor can be created within a trigger by
A trigger cannot be created within a
2. writing the declare statement inside the
cursor.
trigger.
It gets created in response to execution of
3. SQL statement thus it is not previously It is a previously stored program.
stored.
The main function of the cursor is retrieval
The main function of trigger is to
4. of rows from the result set one at a time
maintain the integrity of the database.
(row by row).
A trigger is executed in response to a
A cursor is activated and thus created in
5. DDL statement, DML statement or any
response to any SQL statement.
database operation.
The main disadvantage of cursor is that it The main disadvantage of trigger is that
6. uses more resources each time and thus they are hard to view which makes the
results in network round trip. debugging really difficult.
PL/SQL Control Structure
• Cursor
DECLARE
name varchar2(20);
Cursor c1 is
select t.name
from table t
where date is not null;
BEGIN
OPEN c1;
LOOP
FETCH c1 into name;
exit when c1%NOTFOUND;
END LOOP;
CLOSE c1;
END;
Debuging
• Syntax error
– Occurs when command does not follow
guidelines of programming language
– Generate compiler or interpreter error
messages
• Logic error
– Does not stop program from running
– Results in incorrect result
Finding Syntax Errors
• Often involve:
– Misspelling reserved word
– Omitting required character in command
– Using built-in function improperly
• Interpreter
– Flags line number
– Displays error code and message
• Example: PLS-00103: Encountered the symbol “Blank space” when
expecting one of the following …
– Error may actually be on preceding line
• To find error: (a) comment out suspect program lines using
--, REM, (b) modify suspect lines.
• Cascading errors
– One syntax error can generate many more errors
24
Finding Logic Errors
• Caused by:
– Not using proper order of operations in
arithmetic functions
– Passing incorrect parameter values to built-
in functions
– Creating loops that do not terminate properly
– Using data values that are out of range or
not of right data type
25
Finding Logic Errors
(continued)
• Debugger
– Program that enables software developers
to pause program execution and examine
current variable values
– Best way to find logic errors
– SQL*Plus environment does not provide
PL/SQL debugger
– Use DBMS_OUTPUT to print variable
values
26
Execution
• How to execute a function in PL/SQL?
Var issue_flag number;
exec :issue_flag:=fun_name(arg1,arg2,. . . .);
PRINT :issue_flag;
• How to execute a procedure in PL/SQL?
Exec procedure_name(arg1,arg2,. . . .);
THANK YOU
