PL SQL.pptx in computer language in database

PL/SQL
• PL/SQL is procedural language extension to SQL.
• PL/SQL is the superset of SQL.
• PL/SQL allows the user to mix SQL statements with procedural statements like if statement, looping
structures etc.
• With PL/SQL, you can use SQL statements to manipulate Oracle data and flow of control statements to
process the data.
Features of PL/SQL
Block Structures:
PL SQL consists of blocks of code, which can be nested within each other. Each block forms a unit of a
task or a logical module. PL/SQL Blocks can be stored in the database and reused.
Variables and Constants:
pl/sql allows the user to declare variables and constants. Variables are used to store values
temporarily. Variables and constants can be used in sql and pl/sql procedural statements just like
expression.

Control Structures:
PL/SQL allows control structures like if statement, for loop, while loop to be used in the block.
Modularity:
pl/sql allows the process can be divided into different modules. These modules can be named as
subprograms (procedures, functions).
Cursors:
A cursor is private sql area used to execute sql statements and store processing information.
Better Performance:
PL SQL engine processes multiple SQL statements simultaneously as a single block, thereby reducing
network traffic.
Error Handling:
PL/SQL handles errors or exceptions effectively during the execution of a PL/SQL program.

SQL PL/SQL
It is a Structured Query Language used to issue a
single query or execute a single insert/update/delete.
It is a programming language of SQL, used to write full
programs using variables, loops, and operators etc. to
carry out multiple selects/inserts/updates/deletes.
It is considered as the source of data for reports, web
pages and screens.
It can be considered as the application language. It
might be the language used to build, format and
display those reports, web pages and screens.
It is a data oriented language used to select and
manipulate sets of data.
It is a procedural language used to create applications.
It is used to write queries by using DDL and DML
statements.
It is used to write program blocks, functions,
procedures triggers, and packages.
It is executed one statement at a time. It is executed as a block of code.
It is declarative, i.e., it tells the database what to do
but not how to do it.
It is procedural, i.e., it tells the database how to do
things.
It can be embedded within a PL/SQL program. It can’t be embedded within a SQL statement.
Differences between SQL and PL/SQL

The PL/SQL Identifiers
• Identifiers are nothing but a name that is
given to a PL/SQL object.
• Examples of identifiers are constants,
variables, exceptions names, procedures
names, cursors etc.
The PL/SQL Literals
A literal is an explicit numeric, character, string, or Boolean value not represented by an identifier. For
example, TRUE, 786, NULL, 'tutorialspoint' are all literals of type Boolean, number, or string. PL/SQL,
literals are case-sensitive.
PL/SQL supports the following kinds of literals −
 Numeric Literals
 Character Literals
 String Literals
 BOOLEAN Literals
 Date and Time Literals
properties of PL/SQL identifiers are
• Up to 30 characters in length
• Must start with a letter
• Can include $ (dollar sign), _ (underscore),
and # (hash sign)
• Cannot contain any “whitespace” characters If the
only difference between
• You cannot use a reserved keyword as an identifier.

The PL/SQL Comments
Program comments are explanatory statements that can be included in the PL/SQL code that you
write and helps anyone reading its source code. All programming languages allow some form of
comments.
The PL/SQL supports single-line and multi-line comments. All characters available inside any
comment are ignored by the PL/SQL compiler. The PL/SQL single-line comments start with the delimiter
-- (double hyphen) and multi-line comments are enclosed by /* and */.
DECLARE
-- variable declaration message
varchar2(20):='Hello, World!’;
BEGIN
/*
PL/SQL executable
statement(s)
*/
dbms_output.put_line(message);
END;

Structure of PL/SQL Block:
PL/SQL Block consists of three sections. They are
• Declaration section,
• Execution section,
• Exception Handling section.
DECLARE
[Variable declaration]
BEGIN
Program Execution
[EXCEPTION]
Exception handling
END;

Variable:
To store data temporarily during the execution of code. It helps the user to manipulate data in
PL/SQL programs.
Rules for define variable name:
1. The variable name must start with an alphabet.
2. The size of variable name must be less than 31 characters.
3. It does not allow spaces, hyphens but it allows under score as special character
Syntax:
variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value]
Assignment Operator: An assignment operator is used to assign a value to be stored in a
variable
or an expression. It can be represented by “:=’. The syntax is,
variable_name := value/expression;

Displaying output from pl/sql:
In order to display output from pl/sql block, we have to use dbms_output.put_line package.
A package is a collection of procedures and functions.
Select … into statement:
It retrieves data from one or more database tables, and assigns the selected values to variables or
collections.
Write a program to display your name
declare
begin
dbms_output.put_line(‘srdc’);
end;

%type
It is used to assign data type of tables column to the variable.
Declare
v_name emp.ename%type;
v_sal emp.sal%type;
Begin
select ename,sal into v_name, v_sal from emp
where empno=2;
dbms_output.put_line(‘Name : ’ ||v_name);
dbms_output.put_line(‘Salary : ’ ||v_sal);
end;
Write a program read empno and display details of the employ by
using %type variable

declare
emprec emp%rowtype;
Begin
Select * into emprec from emp where empno=10;
dbms_output.put_line(‘Name : ’ || emprec.ename);
dbms_output.put_line(‘Salary : ’ || emprec.salary);
End;
It is used to declare variable to keep a single record, since a record is nothing
but collection of column. This is also known as composite data type.
Write a program read empno and display details of the employ by using %rowtype
variable
%rowtype

Conditional statements/ Control statements
Conditional statements
1. if then statement
2. if then else statement
3. if then elsif statement
if then statement:
The if statement associated a condition with a sequence of statements enclosed by the keywords
“then” and “endif”. If the condition is true, the statements get executed and if the condition is false
or NULL then the if statement terminate automatically. The sytax is,
if<condition> then
set of statements;
end if;

if then else statement:
The if statement adds the keyword else followed by
an alternative sequence of statements. If the
condition is true, it will execute set of statements. If
the condition is false or NULL, then only the
alternative sequence of statements gets executed. It
ensures that either of the sequence of statements is
executed.
The syntax is,
if<condition> then
set of statements;
else
set of statements;
end if;
Write a program to print
big number between two
numbers
declare
a number:=&a;
b number:=&b;
begin
if a>b then
dbms_output.put_line(a);
else
dbms_output.put_line(b);
end if;

if then elsif statement: It allows the user to
choose between several alternatives.
The syntax is,
if<condition1> then
set of statements;
elsif<condition2> then
set of statements;
else
set of statements;
end if;
here first it will evaluate the condition1, if it is true it
will execute the set of statements and comes out side
of the program. If the condition1 is false, it will
evaluate the condition2 if it is true, it will execute
the set of statements and comes outside the
program. If the given above all conditions are false,
it will execute the set of statements which are
enclose in else part.
Write a program to print greatest
number among three numbers
declare
a number:=&a;
b number:=&b;
c number:=&c;
begin
if a>b and a>c then
dbms_output.put_line('a is the greatest
number');
elsif b>a and b>c then
dbms_output.put_line('b is the greatest
number');
else dbms_output.put_line('c is the
greatest number');
end if;
end;

Loops: A loop can repeat a statement or statements multiple times. The following are
different types of loops available in oracle. They are
1.Basic loop
2.While loop
3.For loop
1.Basic loop:
The simplest form of loop is basic loop, which encloses a
sequences of statements between the keywords loop and
end loop. The basic loop allows the sequence of
statements at least once, even if the condition is false.
The exit statement can terminate the loop. Without exit
statement the loop can be treated as infinite loop.
The syntax is
loop
statement-1;
statement-2;
.
.
statement-n;
exit when <condition>;
end loop;

2.While loop:
In while loop, before each iteration of the loop, the
condition is evaluated. It the condition is true, the
sequence of statements is executed. If the condition is false
or null, the loop is terminated and control resumes after
the end loop statement.
The syntax is
while<condition>
loop
sequence of statements;
end loop;
write a program to print even
number from 1 to 10 using while
loop.
declare
n number(3):=0;
begin
while n <=10
loop
n:=n+2;
dbms_output.put_line(n);
end loop;
end;

3. for loop
It is a repetition control structure. It allows the user to execute code repeatedly for a fixed
number of times.
The syntax is,
for counter in [reverse] lower_bound .. upper_bound
loop
sequence of statement;
end loop;
Write a program to print first 10 natural numbers using for loop
declare
begin
for i in 1..10
loop
dbms_output.put_line(i);
end loop;
end

Functions:
A function must return a value to the calling environment. A function must have return clause in
the header.
Syntax:
create or replace function <function_name>
[(parameter1 [mode1] datatype,Parameter2 [mode2] datatype, . . . )] return datatype is/as
Pl/sql block;
Here function_name --- name of the function
Parameter -- name of pl/sql variable
Mode – the type of the parameter. Only IN parameter
Datatype --- datatype of the parameter
Return datatype -- datatype of the return value

Removing function: using DROP command we can remove function from data base.
Syntax: drop function <function_name>;
Ex: drop function emp_fun;
-- return area of rectangle
create or replace function area_fun(l in number, b in number) return number
is
a number;
begin
a:=l*b;
return a;
end;
output:
select area_fun(4,3) from dual;

Procedures:
A procedure is a subprogram that can performs an action. A
procedure can be stored in database.
Syntax: create [or replace] procedure <procedure_name>
[parameter1 [mode] datatype1,Parameter2 [mode] datatype2,. . .
]Is/as
Pl/sql block;
End [procedure_name];
Here procedure_name --- name of the procedure
Parameter --- name of pl/sql variable
Mode ---- in/out/in out (in --- default)
Datatype --- data type of the parameter
Pl/sql block --- procedural body of the program.
IN OUT INOUT
Default mode Must be
specified
Must be
specified
Value is
passed into
subprogram
Returned to
calling
environment
Passed into
subprogram,
returned to
calling
environment
Formal
parameter
acts as a
constant
Unintialized
variable
Initialized
variable
Can be
assigned a
default value
Cannot be
assigned a
default value
Cannot be
assigned a
default value
Actual
parameter
can be a
literal,
expression,
constant, or
initialized
Must be a
variable
Must be a
variable

Create a procedure, which receives a number and
display whether it is odd or even.
Create or replace procedure oddeven (num in number) is
a number(3);
Begin
a := mod(num,2);
If a = 0 then
dbms_output.put_line( num ||' is even number');
Else
dbms_output.put_line( num ||' is odd number');
End if;
End;
-- output
SQL> execute oddeven(100);
Execution of procedure:
Procedure is executed from a SQL prompt as
per follows and One can execute procedure
from caller program also.
Syntax: exec[ute]procedure_name(parameter
list)
Removing Procedure:
using DROP command we can remove
procedure from data base.
Syntax: drop procedure <procedure_name>;
Ex: drop procedure emp_proc;

Exceptions:
• In PL/SQL, errors are called
exceptions.
• All error handling statements are
placed in the EXCEPTION program
block.
• Predefined and user defined
exceptions are available in oracle.
Predefined Exceptions:
• Predefined exceptions are errors
which occur during the execution of
the program. The predefined
exceptions are internally defined
exceptions.
Exception Description
INVALID_CURSOR
It is raised when attempts are
made to make a cursor
operation that is not allowed,
such as closing an unopened
cursor.
INVALID_NUMBER
It is raised when the
conversion of a character
string into a number fails
because the string does not
represent a valid number.
NO_DATA_FOUND
It is raised when a SELECT
INTO statement returns no
rows.
PROGRAM_ERROR
It is raised when PL/SQL has
an internal problem.
ZERO_DIVIDE
It is raised when an attempt is
made to divide a number by
zero.

write a program for no_data_found exception
Declare
ere cemp%rowtype;
v_empno emp.empno%type:=&v_empno;
Begin
SELECT * into erec from emp where
empno=v_empno;
dbms_output.put_line('Employee Salary : '||
erec.sal);
Exception
When No_Data_Found then
dbms_output.put_line('Entredempno is not found');
When Others then
Null;
End;
Syntax:
Declare
...
Begin
...
Exception
When Exception_1 then
<Statement(s)>;
When Exception_2 then
<Statement(s)>;
End;

User-Defined Exceptions:
• user-defined exceptions should be
explicitly specified.
• The user-defined exception must be
declared in the declaration part of
the PL/SQL Block and it can
explicitly raised with the RAISE
Statement.
write a program for user defined exception
declare
a number:=&a;
b number:=&b;
c number;
zerodivide exception;
begin
if b>0 then
c:=a/b;
dbms_output.put_line(c);
else
raise zerodivide;
end if;
exception
when zerodivide then
dbms_output.put_line('b must be greater than zero');
end;

Implicit cursors: implicit cursors are declared by pl/sql implicitly for all DML and pl/sql
statement. These cursors can return only one row.
-- implicit cursor example
declare
v_empnoemp.empno%type:=&v_empno;
begin
update remp set sal=sal+1000 where empno=v_empno;
if sql%notfound then
dbms_output.put_line('record not found');
end if;
end;
Explicit cursor attributes:
%isopen – evaluates to true if the cursor
is open
%notfound – evaluates to true if the
most recent fetch does not return a row
%found --- evaluates to true if the most
recent fetch returns a row
%rowcount – evaluates the total
number of rows returned by the cursor
Cursors:
Cursor is private sql work area. There are two types of cursors available in oracle. They are Implicit and
Explicit cursors

Syntax:
declare
Cursor <cursor_name> is select statement.
begin
Open <cursor>
loop
Fetch <cursor> into <userdefinedvar>
Exit when condition
End loop;
Close cursor;
Here cursor_name --- name of the cursor
Open cursor --- the open statement executes the
query
Fetch – fetch the data from the cursor
Close cursor – the close statement releases the active
set of rows
Explicit cursors
These cursors are declared by the user explicitly in declaration section of the pl/sql
block for their requirements.

Example Program of Explicit cursors
write a program to display top5 salries by using explicit cursor
declare
cursor v_cur is select *from emp order by sal desc;
v_rec emp%rowtype;
begin
open v_cur; ---- open cursor
loop
fetch v_cur into v_rec; --- fetch cursor into variable
exit when v_cur%rowcount>=5 ; --- exit when rowcount>5
dbms_output.put_line(v_rec.empno||'-'||v_rec.ename||'-'||v_rec.sal||'-'||v_rec.job);
end loop;
end;

Triggers:
A trigger is a PL/SQL block or a PL/SQL procedure associated with a table, view, schema, or the database.
Executes implicitly whenever a particular event takes place. Triggers can be executed, or fired, in response to
the following events:
 A row is inserted into a table
 A row in a table is updated
 A row in a table is deleted
Type of triggers:
Application trigger: Fires whenever an event occurs with a particular application
Database trigger: Fires whenever a data event (such as DML) or system event (such as logon or shutdown)
occurs on a schema or database

DML Trigger components:
Row-Level Triggers:
• Row-level triggers execute once for each row in a transaction.
• Row-level triggers are the most common type of trigger; they are often used in data auditing applications.
• Row-level triggers are also useful for keeping distributed data in sync.
• Row-level triggers are created using the for each row clause in the create trigger command.
Statement-Level Triggers:
• Statement-level triggers execute once for each transaction. For example, if a single transaction inserted 500 rows
into a table, then a statement-level trigger on that table would only be executed once.
• Statement-level triggers therefore are not often used for data-related activities; they are normally used to enforce
additional security measures on the types of transactions that may be performed on a table.
• Statement-level triggers are the default type of trigger created via the create trigger command.

Before: Execute the trigger body before the triggering DML event on table.
After: execute the trigger body after the triggering DML event on table.
Instead of Trigger: Executes the trigger body instead of the triggering statement. This is used for views that
are not otherwise modifiable.
Syntax:
Create [ or replace ] trigger [user.] <trigger_name>
{ before | after | instead of }
{ delete | insert | update [ of column [, column] …] } on [user.]{ Table | View
}
for each { row | statement }
[ when (condition) ]
PL/SQL Block

--create a trigger to restrict user does not insert data into emp
create or replace trigger ins_emp
before insert on emp
begin
raise_application_error(-20300,'can not insert data into emp table');
end;
output:
insert into emp (empno) values(300);

Packages:
• Package is group logically related PL/SQL types, items, and subprograms (procedures, functions, cursors,
exceptions). It consist of Package Specification andPackage Body parts.
• A package specification can exist without a package body, but a package body cannot exist without a package
specification.
Advantages of Packages
• Modularity
• Easier application design
• Hiding information
• Added functionality
• Better performance
• Overloading

Syntax for package specification:
CREATE [OR REPLACE] PACKAGE
<package_name>
IS|AS
public type and item declarations
subprogram specifications
END [package_name];
Syntax for package body:
CREATE [OR REPLACE] PACKAGE BODY
<package_name>
IS|AS
private type and item declarations
subprogram bodies
END [package_name];
Here
The REPLACE option drops and recreates the
package body.
Identifiers defined only in the package body are
private constructs. These are not visible outside
the package body.
All private constructs must be declared before
they are used in the public constructs.
Here
REPLACE option drops and recreates the
package specification.
Variables declared in the package specification
are initialized to NULL by default.
All the constructs declared in a package
specification are visible to users who are
granted privileges on the package.

Execute package:
-- create a package to display sal and
dname for given empno and deptno
create or replace package emp_pak as
procedure emp_pro(v_empno number);
procedure dept_pro(v_deptno number);
end;
/
create or replace package body emp_pak as
procedure emp_pro(v_empno number) is
v_salemp.sal%type;
begin
select sal into v_sal from emp where
empno=v_empno;
dbms_output.put_line('sal is:'||v_sal);
end;
procedure dept_pro(v_deptno number) is
v_dnamedept.dname%type;
begin
select dname into v_dname from dept where
deptno=v_deptno;
dbms_output.put_line('department name
is:'||v_dname);
end;
end;
---output
execute emp_pak.emp_pro(7369); -- it will display
sal for given empno
execute emp_pak.dept_pro(10); --- it will display
departname for given deptno

PL SQL.pptx in computer language in database

More Related Content

Similar to PL SQL.pptx in computer language in database

More from ironman82715

Recently uploaded

PL SQL.pptx in computer language in database