PLSQL

PRACTICAL 1
Use of Variables, Write Executable Statements, Interacting with Oracle Server, create
anonymous PLSQL block sequences.

1. Variables in PLSQL:
SET SERVEROUTPUT ON;
DECLARE
num NUMBER := 100;
name VARCHAR2(30) := 'SS';
code CHAR(4) := 'AB12';
today DATE := SYSDATE;
--student_record students%ROWTYPE;
TYPE num_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
numbers num_table;
large_text CLOB := 'India is my country.';
BEGIN
numbers(1) := num;
numbers(2) := 200;
DBMS_OUTPUT.PUT_LINE('Number: ' || num);
DBMS_OUTPUT.PUT_LINE('Name: ' || name);
DBMS_OUTPUT.PUT_LINE('Code: ' || code);
DBMS_OUTPUT.PUT_LINE('Today: ' || today);
DBMS_OUTPUT.PUT_LINE('First number in collection: ' || numbers(1));
DBMS_OUTPUT.PUT_LINE('Second number in collection: ' || numbers(2));
DBMS_OUTPUT.PUT_LINE('Large text: ' || large_text);
END;
/
 2. Executable Statements:
SET SERVEROUTPUT ON;
DECLARE
stud_name VARCHAR2(50);
course_name Varchar2(30);
BEGIN
stud_name:='Sambare';
course_name:='[Link]';

DBMS_OUTPUT.PUT_LINE('Student Name:'||stud_name);
DBMS_OUTPUT.PUT_LINE('Course Name:'||course_name);
END;
/

3. Interacting With Oracle Server:

SET SERVEROUTPUT ON;

DECLARE
vstudentid NUMBER;
vfirstname VARCHAR2(50);
vcourse VARCHAR2(30);
BEGIN
SELECT studentid, firstname, course
INTO vstudentid, vfirstname, vcourse
FROM students
WHERE studentid=1;

DBMS_OUTPUT.PUT_LINE('Student ID: '|| vstudentid);

DBMS_OUTPUT.PUT_LINE('First Name: '|| vfirstname);
DBMS_OUTPUT.PUT_LINE('Course: '|| vcourse);
END;
/
 4. Sequence:
SET SERVEROUTPUT ON;
CREATE SEQUENCE s1
START WITH 1
INCREMENT BY 1
NOCYCLE
CACHE 10;

DECLARE
a NUMBER; BEGIN
a:= [Link];
DBMS_OUTPUT.PUT_LINE('Next ID:'|| a);
END;
/
 PRACTICAL 2
Using WHILE LOOP, DO LOOP, FOR LOOP, Use of GOTO Statement.

1. While Loop:
SET SERVEROUTPUT ON;

DECLARE
counter NUMBER := 1;
BEGIN
WHILE counter <= 5 LOOP
DBMS_OUTPUT.PUT_LINE('Counter: ' || counter);
counter := counter + 1;
END LOOP;
END;

2. For Loop:
SET SERVEROUTPUT ON;

BEGIN
FOR i IN 1..5 LOOP
DBMS_OUTPUT.PUT_LINE('Number: ' || i);
END LOOP;
END;
/
 3. Loop With Exit:
SET SERVEROUTPUT ON;
DECLARE
num NUMBER:=1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE('Number:'|| num);
num:=num+1;
EXIT WHEN num>5;
END LOOP;
END;
/

4. GOTO Statement:
SET SERVEROUTPUT ON;
DECLARE
counter NUMBER := 1;
BEGIN
IF counter= 1 THEN
GOTO my_label;
END IF;

DBMS_OUTPUT.PUT_LINE('This line will not be executed');

<<my_label>>
DBMS_OUTPUT.PUT_LINE('GOTO statement example');
END;
/
 PRACTICAL 3
Create Conditional Statement using PL/SQL- Using IF Statement, Using IF ElSE
Statement, Using ELSIF Ladder, Using Case Expression.

1. Using IF Statement with Variables:

SET SERVEROUTPUT ON;
DECLARE
s NUMBER:=10;
BEGIN
IF S>5 THEN
DBMS_OUTPUT.PUT_LINE('s is greater than 5');
END IF;
END;
/

2. Using IF-ELSE Statement with Variables:

SET SERVEROUTPUT ON;
DECLARE
s NUMBER:=10;
t NUMBER:=5;
BEGIN
IF s>t THEN
DBMS_OUTPUT.PUT_LINE('s is greater than t');
ELSE

DBMS_OUTPUT.PUT_LINE('s is not greater than t');

END IF;
END;
/
 3. Using ELSIF Ladder with Variables:
SET SERVEROUTPUT ON;
DECLARE
s NUMBER:=10;
t NUMBER:=20;
BEGIN
IF s>t THEN
DBMS_OUTPUT.PUT_LINE('s is greater than t');
ELSIF s=t THEN
DBMS_OUTPUT.PUT_LINE('s is equal to t');
ELSE
DBMS_OUTPUT.PUT_LINE('s is less than t');
END IF;
END;
/

4. Using Case Expression with Variables:

SET SERVEROUTPUT ON;
DECLARE
day_num NUMBER:=3;
BEGIN
CASE day_num

WHEN 1 THEN DBMS_OUTPUT.PUT_LINE('Monday');

WHEN 2 THEN DBMS_OUTPUT.PUT_LINE('Tuesday');
WHEN 3 THEN DBMS_OUTPUT.PUT_LINE('Wednesday');
WHEN 4 THEN DBMS_OUTPUT.PUT_LINE('Thursday');
WHEN 5 THEN DBMS_OUTPUT.PUT_LINE('Friday');
WHEN 6 THEN DBMS_OUTPUT.PUT_LINE('Saturday');
WHEN 7 THEN DBMS_OUTPUT.PUT_LINE('Sunday');
ELSE DBMS_OUTPUT.PUT_LINE('Invalid day number');
END CASE;
END;
/
 PRACTICAL 4
Create Cursor in PL/SQL- Implicit Cursor, Explicit Cursor, Parameterized Cursor.

1. Implicit Cursor:
SET SERVEROUTPUT ON;
DECLARE
v_firstname [Link]%TYPE;
BEGIN
SELECT firstname
INTO v_firstname
FROM students
WHERE studentid=1;

DBMS_OUTPUT.PUT_LINE('First Name: ' || v_firstname);

END;
/

2. Explicit Cursor:
DECLARE
CURSOR student_cursor IS
SELECT firstname, lastname FROM students;
v_firstname [Link]%TYPE;
v_lastname [Link]%TYPE;
BEGIN
OPEN student_cursor;

LOOP
FETCH student_cursor INTO v_firstname, v_lastname;
EXIT WHEN student_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Name: ' || v_firstname || ' ' || v_lastname);
END LOOP;
CLOSE student_cursor;
END;
/
 3. Parameterized Cursor:
SET SERVEROUTPUT ON;
DECLARE
CURSOR student_cursor(p_course VARCHAR2) IS
SELECT first_name
FROM students
WHERE course = p_course;
v_firstname students.first_name%TYPE;
BEGIN
OPEN student_cursor('[Link] IT');
LOOP
FETCH student_cursor INTO v_firstname;
EXIT WHEN student_cursor%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('First Name: ' || v_firstname);

END LOOP;
CLOSE student_cursor;
END;
/
 PRACTICAL 5
Collection and Composite Data Types- Working with Collections, Working with
Composite Data Types.

5.1 Types of Collections:

1. Index-by Tables:
SET SERVEROUTPUT ON;
DECLARE
TYPE student_table IS TABLE OF VARCHAR(50)INDEX BY
BINARY_INTEGER;

students student_table;
BEGIN
students(1):='Yashashree';
students(2):='Vedshree';
students(10):='Sambare'; --Skips indices 3 to 9

DBMS_OUTPUT.PUT_LINE(students(1));
DBMS_OUTPUT.PUT_LINE(students(2));
DBMS_OUTPUT.PUT_LINE(students(10));
END;
/

2. Nested Tables:
SET SERVEROUTPUT ON;
DECLARE
TYPE name_table IS TABLE OF VARCHAR2(50) NOT NULL;
names name_table;
BEGIN
names:= name_table();
[Link](3);
names(1):= 'Tushar';
names(2):= 'Sandip';
names(3):= 'Dhiraj';
DBMS_OUTPUT.PUT_LINE(names(1));
DBMS_OUTPUT.PUT_LINE(names(2));
DBMS_OUTPUT.PUT_LINE(names(3));
END;
/
3. VARRAYs:
SET SERVEROUTPUT ON;
DECLARE
TYPE name_array IS VARRAY(5) OF VARCHAR2(50) NOT NULL;
names name_array;
BEGIN
names:= name_array('Red', 'Green', 'Blue');
DBMS_OUTPUT.PUT_LINE(names(1));
DBMS_OUTPUT.PUT_LINE(names(2));
DBMS_OUTPUT.PUT_LINE(names(3));
-- Adding more elements than the maximum size is not allowed
-- names. EXTEND(3); - This would cause an error if uncommented
END;
/

5.2 Collection Methods:

SET SERVEROUTPUT ON;
DECLARE
TYPE color_array IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
colors color_array;
cnt PLS_INTEGER;
BEGIN
colors(1):='Red';
colors(2):='Green';
colors(3):='Blue';
[Link](2);
cnt:=[Link];
DBMS_OUTPUT.PUT_LINE('Number of elements: '|| cnt);
DBMS_OUTPUT.PUT_LINE('First index: '|| [Link]);
DBMS_OUTPUT.PUT_LINE('Last index: '||[Link]);
DBMS_OUTPUT.PUT_LINE('Index before the last: '||
[Link]([Link]));
DBMS_OUTPUT.PUT_LINE('Index after the first:'||
[Link]([Link]));
FOR i IN [Link]..[Link] LOOP
DBMS_OUTPUT.PUT_LINE('Element at index:'||i||colors(i));
END LOOP;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/

Composite Data Types:

Records:
SET SERVEROUTPUT ON;
DECLARE
TYPE srecord IS RECORD(
sid NUMBER,
firstname VARCHAR2(50),
lastname VARCHAR2(50)
);

sinfo srecord;
BEGIN
[Link] := 1;
[Link] := 'Sonali';
[Link] := 'Sambare';

DBMS_OUTPUT.PUT_LINE('ID: ' || [Link]);

DBMS_OUTPUT.PUT_LINE('Name: ' || [Link] || ' ' ||
[Link]);
END;
/