Plsql8

Database Systems Lab
DIGITAL ASSIGNMENT-8
Submitted in partial fulfillment of the requirements for the course
BCSE302P
FALL SEM 2024-25
Submitted
by
22BCE2394
Yuvraj Kr
Under Faculty-
SRIDEVI S
VIT, Vellore
Vellore – 632014, Tamil Nadu, India

September 2024
PL/SQL
EXAMPLE CODE:
DECLARE
v_message VARCHAR2(100); -- Declare a variable to hold the message
BEGIN
v_message := 'Hello, PL/SQL!'; -- Assign a value to the variable
DBMS_OUTPUT.PUT_LINE(v_message); -- Output the message
END;
/
OUTPUT
1.Program to calculate factorial:

DECLARE
v_number NUMBER := 5; -- Input number
v_factorial NUMBER := 1; -- Variable to hold factorial
BEGIN
FOR i IN 1..v_number LOOP
v_factorial := v_factorial * i; -- Calculate factorial
END LOOP;
DBMS_OUTPUT.PUT_LINE('Factorial of ' || v_number || ' is ' || v_factorial);
END;
/
OUTPUT
2.Program to check even or odd

DECLARE
v_number NUMBER := 10; -- Input number
BEGIN
IF MOD(v_number, 2) = 0 THEN
DBMS_OUTPUT.PUT_LINE(v_number || ' is Even.');
ELSE
DBMS_OUTPUT.PUT_LINE(v_number || ' is Odd.');
END IF;
END;
/
OUTPUT
3.Program to find maximum of 2 numbers

DECLARE
v_num1 NUMBER := 15; -- First number
v_num2 NUMBER := 20; -- Second number
v_max NUMBER; -- Variable to hold maximum
BEGIN
IF v_num1 > v_num2 THEN
v_max := v_num1; -- Assign v_num1 to v_max if it's greater
ELSE
v_max := v_num2; -- Otherwise assign v_num2 to v_max
END IF;
DBMS_OUTPUT.PUT_LINE('Maximum is ' || v_max); -- Output the
maximum
END;
/
OUTPUT
4. Program to Concatenate Strings

DECLARE
v_first_name VARCHAR2(50) := 'John';
v_last_name VARCHAR2(50) := 'Doe';
v_full_name VARCHAR2(100);
BEGIN
v_full_name := v_first_name || ' ' || v_last_name;
DBMS_OUTPUT.PUT_LINE('Full Name: ' || v_full_name);
END;
/
OUTPUT
5.Program to Count from 1 to 10

BEGIN
FOR i IN 1..10 LOOP
DBMS_OUTPUT.PUT_LINE(i);
END LOOP;
END;
/
OUTPUT
6. Arithmetic Operators
DECLARE
num1 NUMBER := 10;
num2 NUMBER := 5;
sum NUMBER;
difference NUMBER;
product NUMBER;
quotient NUMBER;
BEGIN
sum := num1 + num2;
difference := num1 - num2;
product := num1 * num2;
quotient := num1 / num2;
DBMS_OUTPUT.PUT_LINE('Sum: ' || sum);

DBMS_OUTPUT.PUT_LINE('Difference: ' || difference);
DBMS_OUTPUT.PUT_LINE('Product: ' || product);
DBMS_OUTPUT.PUT_LINE('Quotient: ' || quotient);
END;
/
OUTPUT
7.Relational Operators
DECLARE
num1 NUMBER := 10;
num2 NUMBER := 5;
BEGIN
IF num1 > num2 THEN
DBMS_OUTPUT.PUT_LINE(num1 || ' is greater than ' || num2);
ELSIF num1 < num2 THEN
DBMS_OUTPUT.PUT_LINE(num1 || ' is less than ' || num2);
ELSE
DBMS_OUTPUT.PUT_LINE(num1 || ' is equal to ' || num2);
END IF;
END;
/
OUTPUT
8. Logical Operators
DECLARE
age NUMBER := 20;
has_permission BOOLEAN := TRUE;
BEGIN
IF (age >= 18 AND has_permission) THEN
DBMS_OUTPUT.PUT_LINE('Access granted.');
ELSE
DBMS_OUTPUT.PUT_LINE('Access denied.');
END IF;
END;
/
OUTPUT
9.Assignment Operators
DECLARE
total NUMBER := 0;
value NUMBER := 10;
BEGIN
total := total + value; -- Using the += operator
DBMS_OUTPUT.PUT_LINE('Total: ' || total);
END;
/
OUTPUT
10.Using Operators in PL/SQL Collections

DECLARE
TYPE num_table IS TABLE OF NUMBER;
numbers num_table := num_table(1, 2, 3, 4, 5);
total NUMBER := 0;
BEGIN
FOR i IN 1 .. numbers.COUNT LOOP
total := total + numbers(i);
END LOOP;
DBMS_OUTPUT.PUT_LINE('Total of collection: ' || total);
END;
/
OUTPUT
11. Creating a Table and Inserting Data
CREATE TABLE EMPLOYEE (
empId NUMBER PRIMARY KEY,
name VARCHAR2(15) NOT NULL,
dept VARCHAR2(10) NOT NULL
);
INSERT INTO EMPLOYEE VALUES (1, 'Clark', 'Sales');

INSERT INTO EMPLOYEE VALUES (2, 'Dave', 'Accounting');
INSERT INTO EMPLOYEE VALUES (3, 'Ava', 'Sales');
SELECT * FROM EMPLOYEE WHERE dept = 'Sales';

OUTPUT
12.Conditional Statements (IF-THEN-ELSE)

DECLARE
num NUMBER := 10;
BEGIN
IF num > 0 THEN
DBMS_OUTPUT.PUT_LINE('The number is positive.');
ELSIF num < 0 THEN
DBMS_OUTPUT.PUT_LINE('The number is negative.');
ELSE
DBMS_OUTPUT.PUT_LINE('The number is zero.');
END IF;
END;
/
OUTPUT
13.IF statement with Multiple Conditions

DECLARE
score NUMBER := 85;
grade CHAR(1);
BEGIN
IF score >= 90 THEN
grade := 'A';
ELSIF score >= 80 THEN
grade := 'B';
grade := 'C';
grade := 'D';
ELSE
grade := 'F';
END IF;
DBMS_OUTPUT.PUT_LINE('Score: ' || score || ', Grade: ' || grade);

END;
/
OUTPUT
14. CASE Statement

DECLARE
day NUMBER := 3; -- Assume 1-Sunday, 2-Monday, etc.
day_name VARCHAR2(10);
BEGIN
CASE day
WHEN 1 THEN day_name := 'Sunday';
WHEN 2 THEN day_name := 'Monday';
WHEN 3 THEN day_name := 'Tuesday';
WHEN 4 THEN day_name := 'Wednesday';
WHEN 5 THEN day_name := 'Thursday';
WHEN 6 THEN day_name := 'Friday';
WHEN 7 THEN day_name := 'Saturday';
ELSE day_name := 'Invalid day';
END CASE;
DBMS_OUTPUT.PUT_LINE('The day is: ' || day_name);
END;
/
OUTPUT
15.Nested IF Statement
DECLARE
salary NUMBER := 50000;
BEGIN
IF salary < 30000 THEN
DBMS_OUTPUT.PUT_LINE('Salary is low.');
ELSIF salary >= 30000 AND salary < 60000 THEN
DBMS_OUTPUT.PUT_LINE('Salary is moderate.');
IF salary < 45000 THEN
DBMS_OUTPUT.PUT_LINE('Consider asking for a raise.');
ELSE
DBMS_OUTPUT.PUT_LINE('You have a decent salary.');
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('Salary is high.');
END IF;
END;
/
OUTPUT
16. Using Conditions in Loops

DECLARE
sum_even NUMBER := 0;
BEGIN
FOR i IN 1..10 LOOP
IF MOD(i, 2) = 0 THEN
sum_even := sum_even + i;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Sum of even numbers from 1 to 10: ' ||
sum_even);
END;
/
OUTPUT
17. FOR Loop

BEGIN
FOR i IN 1..10 LOOP
DBMS_OUTPUT.PUT_LINE('Number: ' || i);
END LOOP;
END;
/
OUTPUT
18. WHILE Loop

DECLARE
i NUMBER := 1;
BEGIN
WHILE i <= 20 LOOP
i := i + 1; -- Increment i
END LOOP;
END;
/
OUTPUT
19.LOOP with EXIT Condition

DECLARE
i NUMBER := 1;
BEGIN
LOOP
i := i + 1;
EXIT WHEN i > 5; -- Exit condition
END LOOP;
END;
/
OUTPUT
20. Nested Loops

DECLARE
i NUMBER;
j NUMBER;
BEGIN
FOR i IN 1..3 LOOP
FOR j IN 1..3 LOOP
DBMS_OUTPUT.PUT_LINE(i || ' * ' || j || ' = ' || (i*j));
END LOOP;
END LOOP;
END;
/
OUTPUT
21.Using CONTINUE Statement

BEGIN
FOR i IN 1..10 LOOP
IF MOD(i, 2) = 0 THEN
CONTINUE; -- Skip even numbers
END IF;
DBMS_OUTPUT.PUT_LINE('Odd Number: ' || i);
END LOOP;
END;
/
OUTPUT
22.Strings
DECLARE
name varchar2(20);
company varchar2(30);
introduction clob;
choice char(1);
BEGIN
name := 'John Smith';
company := 'Infotech';
introduction := 'Hello! I''m John Smith from Infotech.';
choice := 'Y';
dbms_output.put_line(name);
dbms_output.put_line(company);
dbms_output.put_line(introduction);
END;
/
OUTPUT
23.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;
/
OUTPUT

Plsql8

Uploaded by

Document Informationclick to expand document information

Copyright:

Available Formats

Plsql8

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Plsql8

Uploaded by

Copyright:

Available Formats

Database Systems Lab

Submitted in partial fulfillment of the requirements for the course

Vellore – 632014, Tamil Nadu, India

1.Program to calculate factorial:

2.Program to check even or odd

3.Program to find maximum of 2 numbers

4. Program to Concatenate Strings

5.Program to Count from 1 to 10

DBMS_OUTPUT.PUT_LINE('Sum: ' || sum);

10.Using Operators in PL/SQL Collections

INSERT INTO EMPLOYEE VALUES (1, 'Clark', 'Sales');

SELECT * FROM EMPLOYEE WHERE dept = 'Sales';

12.Conditional Statements (IF-THEN-ELSE)

13.IF statement with Multiple Conditions

DBMS_OUTPUT.PUT_LINE('Score: ' || score || ', Grade: ' || grade);

14. CASE Statement

16. Using Conditions in Loops

17. FOR Loop

18. WHILE Loop

19.LOOP with EXIT Condition

20. Nested Loops

21.Using CONTINUE Statement

You might also like