VIDYA VIKAS DEGREE COLLEGE

DEVIDAS LANE, NR MURLIDHAR SWEETS,BORIVALI WEST

PL/SQL

Name of the Teacher:ANOOP MISHRA Class: FYBSC Subject: DBMS IN PL/SQL

 Role of SQL in PL/SQL
• SQL is a single query that is used to
perform DML and DDL operations.
• PL/SQL is a block of codes that used to
write the entire program blocks/
procedure/ function and Stored
Procedures etc.
 Role of SQL in PL/SQL
• PL/SQL stands for “Procedural Language
extensions to the Structured Query
Language”.
• SQL is a popular language for both
querying and updating data in the relational
database management systems (RDBMS).
• PL/SQL adds many procedural constructs
to SQL language to overcome some
limitations of SQL.
 Difference between
SQL and PL/SQL
SQL PL/SQL
SQL is a single or join(ed) or subquery PL/SQL is a block of code that used to
that is used to perform DML and DDL write the entire program blocks/
operations. procedure/ function, etc.
It is declarative, that defines what needs It tells how the things are to be done
to be done.

Execution of a single statement Executes as a whole block

Used to manipulate data Used to create an application as a

backend

No PL/SQL code here Use of SQL in it, without that not

possible
 PL/SQL
• The purpose of PL/SQL is to combine
database language and procedural
programming language.
• The basic unit in PL/SQL is called a block
and is made up of three parts: a
declarative part, an executable part and an
exception-building part.
 PL/SQL
• The basic difference between two
languages is that SQL executes the single
query at a time whereas, Pl/SQL executes
the block of code at once.
• SQL is a Structured Query Language
whereas, PL/SQL is a Procedural
Language/ Structured Query Language.
PL/SQL Block

• Declare part is where variable

declaration is done
• The Begin part is where the bulk
of your programs shall be placed.
Here, you can have IF statements,
loops, etc
• Exceptions - The exception section
is where we place error handling
code.
• End-The end signifies the end of
this program block.

Search on OCP and OCA Oracle

Certifications
 st
1 Block
• Type following code in the Notepad and execute it from Oracle SQL Prompt
-
BEGIN
DBMS_OUTPUT.PUT_LINE(’Hello World’);
END;
/
Hello World
PL/SQL procedure successfully completed
 Variables in PL/SQL
• Just as most procedural languages, PL/SQL has some sort of variables.
• The types of variables are SQL column types that we know already
– Number
– Char
– Varchar2
– Date
– Boolean
• You can also refer to a type of a particular column explicitly by specifying the fully
qualified column name (tablename.columname) followed by %TYPE.
• For example:
PRODUCT.PRICE%TYPE
• Similarly, we can refer to a particular row as a single type. Again, you declare it by
• referring to a database table directly: PRODUCT%ROWTYPE. This would refer to a
single record stored in the PRODUCT table.
• Along with the above mentioned, some common types are: BOOLEAN, DATE, NUMBER,
CHAR, and VARCHAR2.
• We declare variables of these types similarly to specifying columns in tables. First, we
• list the name of the variable, then the type we want it to have.
• For example, to declare a price variable of a fixed point NUMBER, we might do
something like this:
PRICE NUMBER(6,2);
 PL/SQL Block example
Declare
a number(3);
begin
dbms_output.put_line('hello');
end;
.
/
 Procedures
• A stored procedure is a prepared SQL code
that you can save, so the code can be reused
over and over again.
• So if you have an SQL query that you write
over and over again, save it as a stored
procedure, and then just call it to execute it.
• You can also pass parameters to a stored
procedure, so that the stored procedure can
act based on the parameter value(s) that is
passed.
 SYNTAX
PROCEDURE procedure_name IS
BEGIN
procedure_body
END;
 EXAMPLES
CREATE OR REPLACE PROCEDURE Sum(a IN
number, b IN number) IS
c number;
BEGIN
c := a+b;
dbms_output.put_line('Sum of two nos= '|| c);
END Sum;
 BENEFITS OF PROCEDURES
1. Improves Database Performance
• Compilation is automatically done by oracle engine.
• Whenever the calling of procedure or function is done ,the oracle
engine
loads the compiled code into a memory area called System Global
Area(SGA) due to which execution becomes faster.
2. Provides Reusability and avoids redundancy
• The same block of code for procedure or function can be called any
number of times for working on multiple data.
• Due to which number of lines of code cannot be written repeatedly.
3. Maintains Integrity
• Integrity means accuracy. Use of procedure or function ensures
integrity
because they are stored as database objects by the oracle engine.
4. Maintains Security
• Use of stored procedure or function helps in maintaining the
security of
the database as access to them and their usage can be controlled by
granting access/permission to users while the permission to change
or to
edit or to manipulate the database may not be granted to users.
5. Saves Memory
• Stored procedure or function have shared memory. Due to which it
saves
memory as a single copy of either a procedure or a function can be
loaded for execution by any number of users who have access
permission.
• Functions
• Functions is a standalone PL/SQL
subprogram.
• Like PL/SQL procedure, functions have a
unique name by which it can be referred.
• These are stored as PL/SQL database objects.
Below are some of the characteristics of
functions.
 SYNTAX
CREATE [OR REPLACE] FUNCTION function_name
(parameter_name type [, …])

— This statement is must for functions

RETURN return_datatype

{IS | AS}

BEGIN
— program code

[EXCEPTION
exception_section;

END [function_name];
 FUNCTION EXAMPLES
c reate or replace function adder(n1 in number, n2 in number)

return number

is

n3 number(8);

begin

n3 :=n1+n2;

return n3;

end;

/
PL/SQL Cursor

• When an SQL statement is processed,

Oracle creates a memory area known
as context area.
• A cursor is a pointer to this context
area.
• It contains all information needed for
processing the statement.
• In PL/SQL, the context area is
controlled by Cursor. A cursor
contains information on a select
statement and the rows of data
accessed by it.
• A cursor is used to referred to a program to
fetch and process the rows returned by the
SQL statement, one at a time. There are two
types of cursors:
• Implicit Cursors
• Explicit Cursors
PL/SQL Implicit Cursors

• The implicit cursors are

automatically generated by
Oracle while an SQL statement
is executed, if you don't use an
explicit cursor for the statement.
• Orcale provides some attributes
known as Implicit cursor's
attributes to check the status of
DML operations. Some of them
are: %FOUND, %NOTFOUND,
%ROWCOUNT and %ISOPEN.
• For example: When you execute the
SQL statements like INSERT, UPDATE,
DELETE then the cursor attributes tell
whether any rows are affected and how
many have been affected. If you run a
SELECT INTO statement in PL/SQL
block, the implicit cursor attribute can
be used to find out whether any row has
been returned by the SELECT statement.
It will return an error if there no data is
selected.
DECLARE
total_rows number(2);
BEGIN
UPDATE customers
SET salary = salary + 5000;
IF sql%notfound THEN
dbms_output.put_line('no customers updated');
ELSIF sql%found THEN
total_rows := sql%rowcount;
dbms_output.put_line( total_rows || ' customers updated
');
END IF;
END;
/
PL/SQL Explicit Cursors

• The Explicit cursors are defined by the

programmers to gain more control over the
context area.
• These cursors should be defined in the
declaration section of the PL/SQL block. It is
created on a SELECT statement which
returns more than one row.
• Following is the syntax to create an explicit
cursor:
1. CURSOR cursor_name IS select_statement;;
PL/SQL Explicit Cursor
Example
• Explicit cursors are defined by
programmers to gain more
control over the context area. It
is defined in the declaration
section of the PL/SQL block. It is
created on a SELECT statement
which returns more than one
row.
DECLARE
c_id customers.id%type;
c_name customers.name%type;
c_addr customers.address%type;
CURSOR c_customers is
SELECT id, name, address FROM customers;
BEGIN
OPEN c_customers;
LOOP
FETCH c_customers into c_id, c_name, c_addr;
EXIT WHEN c_customers%notfound;
dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr);
END LOOP;
CLOSE c_customers;
END;
/
Thank You!!