Database Management Systems

PL/SQL
What is PL/SQL
PL/SQL is a block structured language that enables developers to combine the power of SQL with
procedural statements.All the statements of a block are passed to oracle engine all at once which
increases processing speed and decreases the traffic.

Basics of PL/SQL

• PL/SQL stands for Procedural Language extensions to the Structured Query Language (SQL).
• PL/SQL is a combination of SQL along with the procedural features of programming languages.
• Oracle uses a PL/SQL engine to processes the PL/SQL statements.
• PL/SQL includes procedural language elements like conditions and loops. It allows declaration of
constants and variables, procedures and functions, types and variable of those types and triggers.
Features of PL/SQL:
• PL/SQL is basically a procedural language, which provides the functionality of decision making,
iteration and many more features of procedural programming languages.
• PL/SQL can execute a number of queries in one block using single command.
• One can create a PL/SQL unit such as procedures, functions, packages, triggers, and types, which are
stored in the database for reuse by applications.
• PL/SQL provides a feature to handle the exception which occurs in PL/SQL block known as
exception handling block.
• Applications written in PL/SQL are portable to computer hardware or operating system where Oracle
is operational.
• PL/SQL Offers extensive error checking.
• It supports object-oriented programming.
• It supports the development of web applications and server pages.
Advantages of PL/SQL
• SQL is the standard database language and PL/SQL is strongly integrated with SQL.
PL/SQLsupports both static and dynamic SQL. Static SQL supports DML operations and transaction
control from PL/SQL block. In Dynamic SQL, SQL allows embedding DDL statements in PL/SQL
blocks.
• PL/SQL allows sending an entire block of statements to the database at one time. This reduces
network traffic and provides high performance for the applications.
• PL/SQL gives high productivity to programmers as it can query, transform, and update data in
adatabase.
• PL/SQL saves time on design and debugging by strong features, such as exception handling
encapsulation, data hiding, and object-oriented data types.
• Applications written in PL/SQL are fully portable.
• PL/SQL provides high security level.
• PL/SQL provides access to predefined SQL packages.
• PL/SQL provides support for Object-Oriented Programming.
Structure of PL/SQL Block

PL/SQL program units organize the code into blocks. A block without a name is known as an
anonymous block. The anonymous block is the simplest unit in PL/SQL. It is called
anonymous block because it is not saved in the Oracle database.
Structure of PL/SQL Block
• Declare section starts with DECLARE
keyword in which variables, constants, records
as cursors can be declared which stores data
temporarily. It basically consists definition of
PL/SQL identifiers. This part of the code is
optional.
• Execution section starts with BEGIN and ends
with END keyword.This is a mandatory
section and here the program logic is written
to perform any task like loops and conditional
statements. It supports all DML commands,
DDL commands and SQL*PLUS built-in
functions as well.
• Exception section starts with EXCEPTION
keyword.This section is optional which
contains statements that are executed when a
run-time error occurs. Any exceptions can be
handled in this section.
 Structure of PL/SQL Block
Explanation:
SQL> SET SERVEROUTPUT ON;
SET SERVEROUTPUT ON: It is used to
SQL> DECLARE display the buffer used by the dbms_output.
var varchar2(40) := 'Hello PL/SQL' ;
var varchar2 : It is the declaration of variable,
named var1 which is of integer type. There are
BEGIN many other data types that can be used like float,
dbms_output.put_line(var); int, real, smallint, long etc. It also supports
variables used in SQL as well like
NUMBER(prec, scale), varchar etc.
END; PL/SQL procedure successfully completed: It
/ is displayed when the code is compiled and
Output: executed successfully.
Hello PL/SQL Slash (/) after END;: The slash (/) tells the
PL/SQL procedure successfully SQL*Plus to execute the block.
completed. Assignment operator (:=) : It is used to assign a
value to a variable.
 Structure of PL/SQL Block
Output
SQL> SET SERVEROUTPUT ON;
Enter value for a: 2
SQL> DECLARE Enter value for b: 3
-- taking input for variable a
a integer := &a ; Sum of 2 and 3 is = 5
-- taking input for variable b
b integer := &b ; PL/SQL procedure successfully completed.
c integer ;
BEGIN
c := a + b ;
dbms_output.put_line('Sum of
'||a||' and '||b||' is = '||c);
END;
/
PL/SQL Variables
In PL/SQL, a variable is a meaningful name of a temporary storage location that supports a particular data type in a
program.
• It needs to declare the variable first in the declaration section of a PL/SQL block before using it.
• By default, variable names are not case sensitive. A reserved PL/SQL keyword cannot be used as a
variable name.
PL/SQL variables naming rules
• The variable name must be less than 31 characters. Try to make it as meaningful as possible within
31 characters.
• The variable name must begin with an ASCII letter. It can be either lowercase or uppercase.
• Followed by the first character are any number, underscore ( _), and dollar sign ( $) characters.

Example:
Radius Number := 5;
Date_of_birth date;
PL/SQL Variables
PL/SQL variables naming convention
PL/SQL Variables
Declaration Restrictions:
• Forward references are not allowed i.e. you must declare a constant or variable before referencing it in another
statement even if it is a declarative statement.
val number := Total - 200;
Total number := 1000;
The first declaration is illegal because the TOTAL variable must be declared before using it in an assignment
expression.
• Variables belonging to the same datatype cannot be declared in the same statement.
N1, N2, N3 Number; It is an illegal declaration.
Initializing Variables in PL/SQL
• The DEFAULT keyword
• The assignment operator
counter binary_integer := 0;
greetings varchar2(20) DEFAULT 'Hello JavaTpoint';
PL/SQL Variables
DECLARE Output
a integer := 30;
b integer := 40; Value of c: 70
c integer; Value of f: 33.333333333333333333
f real;
BEGIN PL/SQL procedure successfully completed.
c := a + b;
dbms_output.put_line('Value of c: ' || c);
f := 100.0/3.0;
dbms_output.put_line('Value of f: ' || f);
END;
 PL/SQL Variables DECLARE
-- Global variables
Variable Scope in PL/SQL: num1 number := 95;
• Local Variable:Local variables are the inner block num2 number := 85;
variables which are not accessible to outer blocks.
BEGIN
dbms_output.put_line('Outer Variable num1: ' || num1);
• Global Variable:Global variables are declared in
outermost block. dbms_output.put_line('Outer Variable num2: ' || num2);
DECLARE
Output -- Local variables
Outer Variable num1: 95 num1 number := 195;
Outer Variable num2: 85
num2 number := 185;
Inner Variable num1: 195
BEGIN
Inner Variable num2: 185
dbms_output.put_line('Inner Variable num1: ' || num1);

PL/SQL procedure successfully completed. dbms_output.put_line('Inner Variable num2: ' || num2);

END;
END;
 PL/SQL Variables anchors
PL/SQL provides you with a very useful feature called
variable anchors. It refers to the use of the %TYPE
keyword to declare a variable with the data type is associated
with a column’s data type of a particular column in a table.

DECLARE
v_first_name EMPLOYEES.FIRST_NAME%TYPE;
v_last_name EMPLOYEES.LAST_NAME%TYPE;
n_employee_id EMPLOYEES.EMPLOYEE_ID%TYPE;
d_hire_date EMPLOYEES.HIRE_DATE%TYPE;
BEGIN
NULL; Employees Table
END;
/
 PL/SQL Nested Block
DBMS_OUTPUT.PUT_LINE('First name of employee ' ||
SET SERVEROUTPUT ON SIZE 1000000; n_emp_id || ' is ' || v_name);
DECLARE EXCEPTION
n_emp_id EMPLOYEES.EMPLOYEE_ID%TYPE := WHEN no_data_found THEN
&emp_id1;
DBMS_OUTPUT.PUT_LINE('Employee ' || n_emp_id || '
BEGIN not found');
DECLARE END;
n_emp_id employees.employee_id%TYPE := &emp_id2; END;
v_name employees.first_name%TYPE; /

BEGIN
SELECT first_name
INTO v_name
FROM employees
WHERE employee_id = n_emp_id;
 PL/SQL Nested Block
SET SERVEROUTPUT ON SIZE 1000000;
DBMS_OUTPUT.PUT_LINE('First name of employee '
<<parent>> || parent.n_emp_id || ' is ' || child.v_name);
DECLARE
n_emp_id EMPLOYEES.EMPLOYEE_ID%TYPE := EXCEPTION
&emp_id1;
WHEN no_data_found THEN
BEGIN
DBMS_OUTPUT.PUT_LINE('Employee ' ||
<<child>> parent.n_emp_id || ' not found');
DECLARE END;
n_emp_id employees.employee_id%TYPE := &emp_id2; END;
v_name employees.first_name%TYPE; /
BEGIN
SELECT first_name
INTO v_name
FROM employees
WHERE employee_id = parent.n_emp_id;
PL/SQL If
he PL/SQL IF statement has three forms: IF-THEN, IF-THEN-ELSE and IF-THEN-ELSIF.
DECLARE
a number(3) := 500;
BEGIN
-- check the boolean condition using if statement
IF( a < 20 ) THEN
-- if condition is true then print the following
dbms_output.put_line('a is less than 20 ' );
END IF;
dbms_output.put_line('value of a is : ' || a);
END;
/
PL/SQL IF-THEN-ELSE
he PL/SQL IF statement has three forms: IF-THEN, IF-THEN-ELSE and IF-THEN-ELSIF.
DECLARE
a number(3) := 500;
BEGIN
-- check the boolean condition using if statement
IF( a < 20 ) THEN
-- if condition is true then print the following
dbms_output.put_line('a is less than 20 ' );
ELSE
dbms_output.put_line('a is not less than 20 ' );
END IF;
dbms_output.put_line('value of a is : ' || a);
END;
/
 PL/SQL IF-THEN-ELSE
DECLARE Output
a number(3) := 500;
a is not less than 20
BEGIN
value of a is : 500
-- check the boolean condition using if statement
PL/SQL procedure successfully completed.
IF( a < 20 ) THEN
-- if condition is true then print the following
dbms_output.put_line('a is less than 20 ' );
ELSE
dbms_output.put_line('a is not less than 20 ' );
END IF;
dbms_output.put_line('value of a is : ' || a);
END;
/
 PL/SQL IF-THEN-ELSIF
DECLARE ELSIF hiredate >
bonus NUMBER(6,2); TO_DATE('01-JAN-96') THEN
empid NUMBER(6) := 120; bonus := 1000;
hiredate DATE; ELSE
BEGIN
bonus := 1500;
-- retrieve the date that employee was hired, the date is
checked END IF;
-- to determine the amount of the bonus for the employee
SELECT hire_date INTO hiredate FROM employees DBMS_OUTPUT.PUT_LINE('Bonus
WHERE employee_id = empid; for employee: ' || empid || ' is: ' || bonus
IF hiredate > TO_DATE('01-JAN-98') THEN );
bonus := 500; END;
/
PL/SQL case statement
DECLARE when 'F' then dbms_output.put_line('Passed with
grade char(1) := 'A'; Grace');

BEGIN else dbms_output.put_line('Failed');

CASE grade END CASE;

when 'A' then dbms_output.put_line('Excellent'); END;

when 'B' then dbms_output.put_line('Very good'); /

when 'C' then dbms_output.put_line('Good');

when 'D' then dbms_output.put_line('Average');
 PL/SQL case statement
CASE n_pct

SET SERVEROUTPUT ON SIZE 1000000; WHEN 0 THEN

DECLARE v_eval := 'N/A';

n_pct employees.commission_pct%TYPE; WHEN 0.1 THEN

v_eval varchar2(10); v_eval := 'Low';

WHEN 0.4 THEN
n_emp_id employees.employee_id%TYPE := 145;
v_eval := 'High';
BEGIN
ELSE
-- get commission percentage
v_eval := 'Fair';
SELECT commission_pct
END CASE;
INTO n_pct
-- print commission evaluation
FROM employees
DBMS_OUTPUT.PUT_LINE('Employee ' || n_emp_id ||
WHERE employee_id = n_emp_id;
' commission ' || TO_CHAR(n_pct) ||
' which is ' || v_eval);
END;
/
 PL/SQL LOOP Statement
Using Exit Statement
PL/SQL LOOP statement with EXIT and EXIT-WHEN SET SERVEROUTPUT ON SIZE 1000000;
statements:
DECLARE n_counter NUMBER := 0;
BEGIN
LOOP
n_counter := n_counter + 1;
DBMS_OUTPUT.PUT_LINE(n_counter);
IF n_counter = 5 THEN
EXIT;
END IF;
END LOOP;
END;
/
 PL/SQL LOOP Statement
Using Exit When Statement
PL/SQL LOOP statement with EXIT and EXIT-WHEN SET SERVEROUTPUT ON SIZE 1000000;
statements:
DECLARE n_counter NUMBER := 0;
BEGIN
LOOP
n_counter := n_counter + 1;
DBMS_OUTPUT.PUT_LINE(n_counter);
EXIT WHEN n_counter = 5;
END LOOP;
END;
/
PL/SQL WHILE Loop SET SERVEROUTPUT ON SIZE 1000000;
DECLARE
n_counter NUMBER := 10;
n_factorial NUMBER := 1;
n_temp NUMBER;
BEGIN
n_temp := n_counter;
WHILE n_counter > 0
LOOP
n_factorial := n_factorial * n_counter;
n_counter := n_counter - 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE('factorial of ' || n_temp ||
' is ' || n_factorial);
END;
/
PL/SQL FOR Loop
SET SERVEROUTPUT ON SIZE 1000000;
DECLARE
n_times NUMBER := 10;
BEGIN
FOR n_i IN 1..n_times LOOP
DBMS_OUTPUT.PUT_LINE(n_i);
END LOOP;
END;
/
PL/SQL FOR Loop
SET SERVEROUTPUT ON SIZE 1000000;
DECLARE
n_times NUMBER := 10;
BEGIN
FOR n_i IN REVERSE 1..n_times LOOP
DBMS_OUTPUT.PUT_LINE(n_i);
END LOOP;
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 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:

1. Implicit Cursors
2. Explicit Cursors
PL/SQL Cursor
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.

These are created by default to process the statements when DML statements like INSERT,
UPDATE, DELETE etc. are executed.

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.
PL/SQL Cursors Attributes
PL/SQL Implicit Cursors
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 Explict Cursors
PL/SQL Explict Cursors
The Explicit cursors are defined by the 1) Declare the cursor:
programmers to gain more control over the
context area. These cursors should be defined in CURSOR name IS
the declaration section of the PL/SQL block. It is
created on a SELECT statement which returns SELECT statement;
more than one row. 2) Open the cursor:
OPEN cursor_name;
Steps:
3) Fetch the cursor:
FETCH cursor_name INTO variable_list;
• Declare the cursor to initialize in the memory.
4) Close the cursor:
• Open the cursor to allocate memory.
Close cursor_name;
• Fetch the cursor to retrieve data.
• Close the cursor to release allocated memory.
 PL/SQL Explict Cursors
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;
/
PL/SQL Cursors PL/SQL Cursors with
Parameters BEGIN

— Open and fetch data using the cursor

SET SERVEROUTPUT ON;
OPEN GFG(951);
DECLARE
LOOP
CURSOR GFG (Min_rank NUMBER) IS
FETCH GFG INTO cur_id, cur_name, cur_rank;
SELECT Id, name, rank EXIT WHEN GFG%NOTFOUND;

FROM Geeks — Process fetched data

WHERE rank > Min_rank; DBMS_OUTPUT.PUT_LINE(‘ID: ‘ || cur_id || ‘, Name: ‘ ||

cur_name || ‘, Rank: ‘ || cur_rank);
— Declare variables
— Close the loop
cur_id Geeks.Id%TYPE; END LOOP;

cur_name Geeks.name%TYPE; — Close the cursor

cur_rank Geeks.rank%TYPE; CLOSE GFG;

 PL/SQL - Triggers
Triggers in PL/SQL. Triggers are stored programs, which are automatically executed or fired when
some events occur. Triggers are, in fact, written to be executed in response to any of the following
events −

A database manipulation (DML) statement (DELETE, INSERT, or UPDATE)

A database definition (DDL) statement (CREATE, ALTER, or DROP).

A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).

Triggers can be defined on the table, view, schema, or database with which the event is associated.
 Creating Triggers in PL SQL
CREATE [OR REPLACE ] TRIGGER DECLARE
trigger_name Declaration-statements
{BEFORE | AFTER | INSTEAD OF} BEGIN
{INSERT [OR] | UPDATE [OR] | DELETE} Executable-statements
[OF col_name] EXCEPTION
ON table_name Exception-handling-statements
[REFERENCING OLD AS o NEW AS n] END;

[FOR EACH ROW]

WHEN (condition)
Creating Triggers in PL SQL

Output
Types of Triggers in PL/SQL
1. Row-Level Triggers
A row-level trigger occurs once for each row that a triggering event affects.
A. Before Row Triggers
This trigger occurs before the insertion, update, or deletion of a row. It may
be used to change the values of the currently processed row.
B. After-Row Triggers
Following an INSERT, UPDATE, or DELETE operation on a row, this type of
trigger occurs. It may be used to conduct actions based on the row’s
modifications.
C. Instead of Row Triggers
This trigger is used with views and fires instead of the view’s default DML
actions. It enables you to create custom actions for DML operations.
Types of Triggers in PL/SQL
2. Statement-Level Triggers
No matter how many rows are impacted, a trigger event on a table always fires
a statement-level trigger.
A. Before Statement Triggers
This trigger occurs before the execution of a SQL query. It can be used to take
actions or validations before processing the statement.
B. After Statement Triggers
Upon execution of a SQL statement, this trigger occurs. It can be used to
conduct actions based on the statement’s overall outcome.
 Types of Triggers in PL/SQL
3. Database-Level Triggers
No matter which user or application provides the statement, database triggers in PL SQL are
specified on a table, saved in the corresponding database, and performed as a result of an
INSERT, UPDATE, or DELETE statement being made against a table.

A. Startup Triggers
This trigger activates after the initialization of the database. It can be used to undertake setup
activities or to carry out particular operations during startup.
B. Shutdown Triggers
This trigger starts when the database is shutting down. It can be used to undertake cleaning tasks
or to carry out particular operations upon shutdown.
Types of Triggers in PL/SQL
4. DDL Triggers
DDL (Data Definition Language) triggers are actions that occur in reaction to
DDL statements, such as CREATE, ALTER, or DROP. It enables you to capture
and control DDL activities in the database.
5. Instead of Triggers
This trigger is used with views and fires instead of the view’s usual DML
actions. It enables you to create custom actions for DML activities.
6. Compound Triggers
To increase flexibility and effectiveness, this trigger combines row-level and
statement-level triggers. It enables you to specify actions at various levels
and phases of the triggering event.
Types of Triggers in PL/SQL
7. System Triggers

The Oracle database defines and invokes these triggers in response to specified
system events.Server problems, log-in or log-off events, and particular user
activities are examples of these events. The behavior and examples of system
triggers are dependent on the precise event to which they are related.
Types of Triggers in PL/SQL
Row-Level Triggers

Output
Types of Triggers in PL/SQL
Row-Level Triggers
Types of Triggers in PL/SQL
Row-Level Triggers
Types of Triggers in PL/SQL
Row-Level Triggers
Output
Types of Triggers in PL/SQL
Instead of Triggers

Output
Error
Types of Triggers in PL/SQL
Instead of Triggers

Output
1 row inserted
 Types of Triggers in PL/SQL
Instead of Triggers(Update)

Output
Error
 Types of Triggers in PL/SQL
Statement Level Trigger
 Types of Triggers in PL/SQL
DDL Trigger
 Types of Triggers in PL/SQL
Database Trigger
Types of Triggers in PL/SQL
Database Trigger Logon
 Types of Triggers in PL/SQL
Database Trigger Logon
 Types of Triggers in PL/SQL
Database Trigger Logoff
 Types of Triggers in PL/SQL
Database Trigger Logoff
 Types of Triggers in PL/SQL
Startup Trigger

Step1: Logon to the database

Startup triggers execute during the
startup process of the database. In order Step 2: Create a Table
to create a database event trigger for Step 3: Create the database Event
shutdown and startup events we either
need to logon to the database as a user Startup Trigger
with DBA privileges such as sys or we
must possess the ADMINISTER
DATABASE TRIGGER system privilege.
Types of Triggers in PL/SQL
Startup Trigger
Types of Triggers in PL/SQL
Startup Trigger
 PL/SQL Procedure
The PL/SQL stored procedure or simply a procedure is a PL/SQL block which performs one or more specific
tasks. It is just like procedures in other programming languages.

The procedure contains a header and a body.

Header: The header contains the name of the procedure and the parameters or variables passed to the
procedure.
Body: The body contains a declaration section, execution section and exception section similar to a general
PL/SQL block.
Pass parameters in procedure
IN parameters: The IN parameter can be referenced by the procedure or
function. The value of the parameter cannot be overwritten by the procedure or
the function.
OUT parameters: The OUT parameter cannot be referenced by the procedure or
function, but the value of the parameter can be overwritten by the procedure or
function.
INOUT parameters: The INOUT parameter can be referenced by the procedure
or function and the value of the parameter can be overwritten by the procedure
or function
PL/SQL Create Procedure
Syntax for creating procedure:

CREATE [OR REPLACE] PROCEDURE procedure_name

[ (parameter [,parameter]) ]
IS
[declaration_section]
BEGIN
executable_section
[EXCEPTION
exception_section]
END [procedure_name];
PL/SQL Create Procedure
Table creation: Procedure Code:
create or replace procedure
INSERTUSER
create table user (id IN NUMBER,
(id number(10) primary key, name IN VARCHAR2)
name varchar2(100)); is
begin
insert into user values(id,name);
end;
/
PL/SQL program to call procedure
BEGIN
insertuser(101,'Rahul');

dbms_output.put_line('recor
d inserted successfully');
END;
/
IN & OUT Mode
DECLARE BEGIN
a number; a:= 23;
b:= 45;
b number;
findMin(a, b, c);
c number; dbms_output.put_line(' Minimum of (23, 45) : ' || c);
PROCEDURE findMin(x IN number, y IN END;
number, z OUT number) IS
/
BEGIN
IF x < y THEN
z:= x;
ELSE
z:= y;
END IF;
END;
IN & OUT Mode
DECLARE BEGIN
a number; a:= 23;
squareNum(a);
PROCEDURE squareNum(x IN OUT
number) IS dbms_output.put_line(' Square of (23): ' || a);
END;
BEGIN
/
x := x * x;
END;
Executing a Standalone Procedure
A standalone procedure can be I Using the EXECUTE keyword
called in two ways − CREATE OR REPLACE PROCEDURE greetings
AS
BEGIN
I Using the EXECUTE keyword dbms_output.put_line('Hello World!');
END;
/

II Calling the name of the procedure

from a PL/SQL block
Executing a Standalone Procedure
II Calling the name of the The procedure can also be called from another PL/SQL
block −
procedure from a PL/SQL block
BEGIN
CREATE OR REPLACE PROCEDURE greetings;
greetings
END;
AS /
BEGIN
dbms_output.put_line('Hello /
World!');
END;
/
Deleting a Standalone Procedure
Syntax DROP PROCEDURE greetings;

DROP PROCEDURE
procedure-name;
 PL/SQL blocks
1. Anonymous blocks: In PL/SQL, That’s blocks which --find largest number
is not have header are known as anonymous blocks.
These blocks do not form the body of a function or --take it in c variable
triggers or procedure. Example: Here a code example
of find greatest number with Anonymous blocks. IF a > b THEN
Example c:= a;
DECLARE
ELSE
-- declare variable a, b and c
c:= b;
-- and these three variables datatype are integer
a number; END IF;
b number; dbms_output.put_line(' Maximum number in 10
c number; and 100: ' || c);
BEGIN END;
a:= 10; /
b:= 100;
 PL/SQL blocks
2. Named blocks: That’s PL/SQL blocks which having header z:= x;
or labels are known as Named blocks. These blocks can
either be subprograms like functions, procedures, packages ELSE
or Triggers. Example: Here a code example of find greatest
number with Named blocks means using function. a:= y;
END IF;
Example RETURN z;
DECLARE END;
a number; BEGIN
b number; a:= 10;
c number; b:= 100;
FUNCTION findMax(x IN number, y IN number) c := findMax(a, b);
RETURN number
dbms_output.put_line(' Maximum number in 10
S and 100 is: ' || c);
z number; END;
BEGIN /
IF x > y THEN