Scribd
Upload
15 views44 pages

PL SQL 1

This document provides an overview of PL/SQL syntax and programming. It discusses PL/SQL syntax structure including declarations, executable commands, and exception handling sections. It also covers variable declaration and scope, conditional statements like IF-THEN, IF-THEN-ELSE, CASE statements, and nested conditional logic. Examples are provided throughout to demonstrate PL/SQL programming concepts. The document appears to be from a lecture on advanced database management systems focusing on the PL/SQL programming language.

Uploaded by

maliknad971
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd
15 views44 pages

PL SQL 1

This document provides an overview of PL/SQL syntax and programming. It discusses PL/SQL syntax structure including declarations, executable commands, and exception handling sections. It also covers variable declaration and scope, conditional statements like IF-THEN, IF-THEN-ELSE, CASE statements, and nested conditional logic. Examples are provided throughout to demonstrate PL/SQL programming concepts. The document appears to be from a lecture on advanced database management systems focusing on the PL/SQL programming language.

Uploaded by

maliknad971
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd
You are on page 1/ 44

Advance Database Management System – Fall 2023

PL/SQL
Lecture 20
Fahad Maqbool
Department of Computer Science

University of Sargodha
Advance Database Management System – Fall 2023

Syntax
• Declarations (Optional)
• Declares all variables, cursors, subprograms etc.

• Executable Commands (Mandatory)


• Executable PL/SQL statements of the program
• At least one executable line of code

• Exception Handling (Optional)


• Handle errors in the program

University of Sargodha
Advance Database Management System – Fall 2023

Syntax
• Every PL/SQL statement ends with a semicolon
• PL/SQL blocks can be nested within other PL/SQL blocks
using BEGIN and END

DECLARE
<declarations section>
BEGIN
<executable command(s)>
EXCEPTION
<exception handling>
END;

University of Sargodha
Advance Database Management System – Fall 2023

Example
DECLARE message varchar2(20):= 'Hello, World!';
BEGIN
dbms_output.put_line(message);
END;
/

Hello World

PL/SQL procedure successfully completed.

University of Sargodha
Advance Database Management System – Fall 2023

Variable Declaration in PL/SQL


variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value]

sales number(10, 2);


pi CONSTANT double precision := 3.1415;
name varchar2(25);
address varchar2(100);

sales number(10, 2);


name varchar2(25);
address varchar2(100);

University of Sargodha
Advance Database Management System – Fall 2023

Initializing Variables in PL/SQL


• PL/SQL assigns it a default value of NULL.
• To initialize use
• DEFAULT keyword
• Assignment operator

counter binary_integer := 0;
greetings varchar2(20) DEFAULT 'Have a Good Day';

University of Sargodha
Advance Database Management System – Fall 2023

Initializing Variables in PL/SQL


DECLARE
a integer := 10;
b integer := 20;
c integer;
f real;
BEGIN
c := a + b;
dbms_output.put_line('Value of c: ' || c);
f := 70.0/3.0;
dbms_output.put_line('Value of f: ' || f);
END;
/

Value of c: 30
Value of f: 23.333333333333333333
PL/SQL procedure successfully completed.

University of Sargodha
Advance Database Management System – Fall 2023

Variable Scope in PL/SQL


• Local variables − Variables declared in an inner block and
inaccessible to outer blocks.

• Global variables − Variables declared in the outermost block or a


package.

University of Sargodha
Advance Database Management System – Fall 2023

DECLARE

Variable Scope -- Global variables


num1 number := 95;
num2 number := 85;
BEGIN
dbms_output.put_line('Outer Variable
num1: ' || num1);
Outer Variable num1: 95 dbms_output.put_line('Outer Variable
Outer Variable num2: 85 num2: ' || num2);
Inner Variable num1: 195 DECLARE
Inner Variable num2: 185 -- Local variables
num1 number := 195;
PL/SQL procedure successfully num2 number := 185;
completed. BEGIN
dbms_output.put_line('Inner Variable
num1: ' || num1);
dbms_output.put_line('Inner Variable
num2: ' || num2);
END;
END;
/

University of Sargodha
Advance Database Management System – Fall 2023

SQL Query Results to PL/SQL Variables


CREATE TABLE CUSTOMERS(
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25),
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)


VALUES (1, ‘Ali', 32, ‘Lahore', 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)


VALUES (2, ‘Zohaib', 25, ‘Islamabad', 1500.00 );

University of Sargodha
Advance Database Management System – Fall 2023

SQL Query Results to PL/SQL Variables


DECLARE
c_id customers.id%type := 1;
c_name customers.name%type;
c_addr customers.address%type;
c_sal customers.salary%type;
BEGIN
SELECT name, address, salary INTO c_name, c_addr, c_sal
FROM customers
WHERE id = c_id;
dbms_output.put_line
('Customer ' ||c_name || ' from ' || c_addr || ' earns ' || c_sal);
END;
/

Customer Ali from Lahore earns 2000


PL/SQL procedure completed successfully
University of Sargodha
Advance Database Management System – Fall 2023

DECLARE
-- constant declaration
pi constant number := 3.141592654;
-- other declarations
radius number(5,2);
dia number(5,2);
circumference number(7, 2);
area number (10, 2);
BEGIN
-- processing
radius := 9.5;
dia := radius * 2;
circumference := 2.0 * pi * radius;
area := pi * radius * radius;
-- output
dbms_output.put_line('Radius: ' || radius);
dbms_output.put_line('Diameter: ' || dia);
dbms_output.put_line('Circumference: ' || circumference);
dbms_output.put_line('Area: ' || area);
END;
/

University of Sargodha
Advance Database Management System – Fall 2023

Conditional Statements
• IF-THEN
• IF-THEN-ELSE
• IF-THEN-ELSIF
• CASE
• SEARCHED CASE
• NESTED IF-THEN-ELSE

University of Sargodha
Advance Database Management System – Fall 2023

IF condition THEN
IF-THEN S;
END IF;

DECLARE
a number(2) := 10;
BEGIN
a:= 10;
-- 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;
/
a is less than 20
value of a is : 10

University of Sargodha
Advance Database Management System – Fall 2023

IF-THEN-ELSE
DECLARE
a number(3) := 100;
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;
/

a is not less than 20


value of a is : 100
University of Sargodha
Advance Database Management System – Fall 2023

IF-THEN-ELSIF
DECLARE
a number(3) := 100;
BEGIN
IF ( a = 10 ) THEN
dbms_output.put_line('Value of a is 10' );
ELSIF ( a = 20 ) THEN
dbms_output.put_line('Value of a is 20' );
ELSIF ( a = 30 ) THEN
dbms_output.put_line('Value of a is 30' );
ELSE
dbms_output.put_line('None of the values is matching');
END IF;
dbms_output.put_line('Exact value of a is: '|| a );
END;
/
University of Sargodha
Advance Database Management System – Fall 2023

CASE
DECLARE
grade char(1) := 'A';
BEGIN
CASE grade
when 'A' then dbms_output.put_line('Excellent');
when 'B' then dbms_output.put_line('Very good');
when 'C' then dbms_output.put_line('Well done');
when 'D' then dbms_output.put_line('You passed');
when 'F' then dbms_output.put_line('Better try again');
else dbms_output.put_line('No such grade');
END CASE;
END;
/

University of Sargodha
Advance Database Management System – Fall 2023

Searched CASE
DECLARE
grade char(1) := 'B';
BEGIN
case
when grade = 'A' then dbms_output.put_line('Excellent');
when grade = 'B' then dbms_output.put_line('Very good');
when grade = 'C' then dbms_output.put_line('Well done');
when grade = 'D' then dbms_output.put_line('You passed');
when grade = 'F' then dbms_output.put_line('Better try
again');
else dbms_output.put_line('No such grade');
end case;
END;
/

University of Sargodha
Advance Database Management System – Fall 2023

Nested IF-THEN-ELSE
DECLARE
a number(3) := 100;
b number(3) := 200;
BEGIN
-- check the boolean condition
IF( a = 100 ) THEN
-- if condition is true then check the following
IF( b = 200 ) THEN
-- if condition is true then print the following
dbms_output.put_line('Value of a is 100 and b is 200' );
END IF;
END IF;
dbms_output.put_line('Exact value of a is : ' || a );
dbms_output.put_line('Exact value of b is : ' || b );
END;
/
University of Sargodha
Advance Database Management System – Fall 2023

Loops
• Simple Loop

• For While

• While Loop

• Nested Loop

University of Sargodha
Advance Database Management System – Fall 2023

Simple Loop
DECLARE x number := 10;
BEGIN
LOOP
dbms_output.put_line(x);
x := x + 10;
IF x > 50 THEN
exit;
END IF;
END LOOP;
-- after exit, control resumes here
dbms_output.put_line('After Exit x is: ' || x);
END;
/

University of Sargodha
Advance Database Management System – Fall 2023

Simple Loop
DECLARE
x number := 10;
BEGIN
LOOP
dbms_output.put_line(x);
x := x + 10;
exit WHEN x > 50;
END LOOP;
-- after exit, control resumes here
dbms_output.put_line('After Exit x is: ' || x);
END;
/

University of Sargodha
Advance Database Management System – Fall 2023

While Loop

DECLARE
a number(2) := 10;
BEGIN
WHILE a < 20 LOOP
dbms_output.put_line('value of a: ' || a);
a := a + 1;
END LOOP;
END;
/

University of Sargodha
Advance Database Management System – Fall 2023

For Loop

DECLARE
a number(2);
BEGIN
FOR a in 10 .. 20 LOOP
dbms_output.put_line('value of a: ' || a);
END LOOP;
END;
/

University of Sargodha
Advance Database Management System – Fall 2023

Reverse For Loop

DECLARE
a number(2) ;
BEGIN
FOR a IN REVERSE 10 .. 20 LOOP
dbms_output.put_line('value of a: ' || a);
END LOOP;
END;
/

University of Sargodha
Advance Database Management System – Fall 2023

Nested Loops DECLARE


i number(3);
j number(3);
BEGIN
i := 2;
LOOP
j:= 2;
LOOP
exit WHEN ((mod(i, j) = 0) or (j = i));
j := j +1;
END LOOP;
IF (j = i ) THEN
dbms_output.put_line(i || ' is prime');
END IF;
i := i + 1;
exit WHEN i = 50;
END LOOP;
END;
/

University of Sargodha
Advance Database Management System – Fall 2023

Arrays

CREATE Or REPLACE TYPE namearray IS VARRAY(3) OF


VARCHAR2(10);
/

TYPE namearray IS VARRAY(5) OF VARCHAR2(10);


Type grades IS VARRAY(5) OF INTEGER;

University of Sargodha
Advance Database Management System – Fall 2023

Arrays
DECLARE
type namesarray IS VARRAY(5) OF VARCHAR2(10);
type grades IS VARRAY(5) OF INTEGER;
names namesarray;
marks grades;
total integer;
BEGIN
names := namesarray('Kavita', 'Pritam', 'Ayan', 'Rishav', 'Aziz');
marks:= grades(98, 97, 78, 87, 92);
total := names.count;
dbms_output.put_line('Total '|| total || ' Students');
FOR i in 1 .. total LOOP
dbms_output.put_line('Student: ' || names(i) || '
Marks: ' || marks(i));
END LOOP;
END;
/

University of Sargodha
Advance Database Management System – Fall 2023

Sub Program
• Schema Level
• standalone subprogram
• CREATE PROCEDURE / DROP PROCEDURE
• Inside Package
• Packaged SubProgram
• Can be deleted with DROP PACKAGE
• Inside PL/SQL Block

University of Sargodha
Advance Database Management System – Fall 2023

Sub Program Types


• Functions − These subprograms return a single value; mainly used to
compute and return a value.

• Procedures − These subprograms do not return a value directly;


mainly used to perform an action.

University of Sargodha
Advance Database Management System – Fall 2023

Procedures
CREATE OR REPLACE PROCEDURE greetings
AS
BEGIN
dbms_output.put_line('Hello World!');
END;
/

EXECUTE greetings;

BEGIN
greetings;
END;
/

DROP PROCEDURE greetings;


University of Sargodha
Advance Database Management System – Fall 2023

Procedures DECLARE
a number;
b number;
c number;
PROCEDURE findMin(x IN number, y IN number, z OUT number)
IS
BEGIN
IF x < y THEN
z:= x;
ELSE
z:= y;
END IF;
END;
BEGIN
a:= 23;
b:= 45;
findMin(a, b, c);
dbms_output.put_line(' Minimum of (23, 45) : ' || c);
END;
/

University of Sargodha
Advance Database Management System – Fall 2023

Procedures

DECLARE
a number;
PROCEDURE squareNum(x IN OUT number) IS
BEGIN
x := x * x;
END;
BEGIN
a:= 23;
squareNum(a);
dbms_output.put_line(' Square of (23): ' || a);
END;
/

University of Sargodha
Advance Database Management System – Fall 2023

Functions CREATE OR REPLACE FUNCTION totalCustomers


RETURN number IS
total number(2) := 0;
BEGIN
SELECT count(*) into total
FROM customers;

RETURN total;
END;
/

DECLARE
c number(2);
BEGIN
c := totalCustomers();
dbms_output.put_line('Total no. of Customers: ' || c);
END;
/

University of Sargodha
Advance Database Management System – Fall 2023
DECLARE
a number;
Functions b number;
c number;
FUNCTION findMax(x IN number, y IN number)
RETURN number
IS
z number;
BEGIN
IF x > y THEN
z:= x;
ELSE
Z:= y;
END IF;
RETURN z;
END;
BEGIN
a:= 23;
b:= 45;
c := findMax(a, b);
dbms_output.put_line(' Maximum of (23,45): ' || c);
END;
University of Sargodha
Advance Database Management System – Fall 2023
DECLARE
num number;
Recursive Functions factorial number;

FUNCTION fact(x number)


RETURN number
IS
f number;
BEGIN
IF x=0 THEN
f := 1;
ELSE
f := x * fact(x-1);
END IF;
RETURN f;
END;

BEGIN
num:= 6;
factorial := fact(num);
dbms_output.put_line(' Factorial '|| num || ' is ' || factorial);
END;
University of Sargodha
Advance Database Management System – Fall 2023

Cursors
• Implicit Cursors
• Automatically created when SQL executed

• Explicit Cursors
• Programmer-defined
• Declaring the cursor for initializing the memory
• Opening the cursor for allocating the memory
• Fetching the cursor for retrieving the data
• Closing the cursor to release the allocated memory

University of Sargodha
Advance Database Management System – Fall 2023

Implicit Cursors
DECLARE
total_rows number(2);
BEGIN
UPDATE customers
SET salary = salary + 500;
IF sql%notfound THEN
dbms_output.put_line('no customers selected');
ELSIF sql%found THEN
total_rows := sql%rowcount;
dbms_output.put_line( total_rows || ' customers selected
');
END IF;
END;
/

University of Sargodha
Advance Database Management System – Fall 2023

Explicit Cursors
CURSOR c_customers IS
SELECT id, name, address FROM customers;

OPEN c_customers;

FETCH c_customers INTO c_id, c_name, c_addr;

CLOSE c_customers;

University of Sargodha
Advance Database Management System – Fall 2023

Explicit 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;
/

University of Sargodha
Advance Database Management System – Fall 2023

Records
• Table Based
• Cursor Based
• User Defined

University of Sargodha
Advance Database Management System – Fall 2023

Table Based Records


DECLARE
customer_rec customers%rowtype;
BEGIN
SELECT * into customer_rec
FROM customers
WHERE id = 5;
dbms_output.put_line('Customer ID: ' || customer_rec.id);
dbms_output.put_line('Customer Name: ' || customer_rec.name);
dbms_output.put_line('Customer Address: ' || customer_rec.address);
dbms_output.put_line('Customer Salary: ' || customer_rec.salary);
END;
/

University of Sargodha
Advance Database Management System – Fall 2023

Cursor Based Records


DECLARE
CURSOR customer_cur is
SELECT id, name, address
FROM customers;
customer_rec customer_cur%rowtype;
BEGIN
OPEN customer_cur;
LOOP
FETCH customer_cur into customer_rec;
EXIT WHEN customer_cur%notfound;
DBMS_OUTPUT.put_line(customer_rec.id || ' ' || customer_rec.name);
END LOOP;
END;
/

University of Sargodha
Advance Database Management System – Fall 2023

DECLARE
User Def Records type books is record
(title varchar(50),
author varchar(50),
subject varchar(100),
book_id number);
book1 books;
book2 books;
BEGIN
-- Book 1 specification
book1.title := 'C Programming';
book1.author := 'Nuha Ali ';
book1.subject := 'C Programming Tutorial';
book1.book_id := 6495407;

-- Print book 1 record


dbms_output.put_line('Book 1 title : '|| book1.title);
dbms_output.put_line('Book 1 book_id : ' || book1.book_id);

END;

University of Sargodha

You might also like