PL/SQL PRACTICAL 1-5

Name: Varun Anandam Sirra

Roll no.: SCS2425099

Practical 1:
Aim:
Writing PL/SQL Blocks with basic programming constructs by including
following:
a. Sequential Statements
b. Unconstrained loop

Description:
Sequential statements in PL/SQL are executed one after the other, in the order
they appear. These statements include assignments, procedure calls, control
statements, and so on. They form the backbone of PL/SQL programs, enabling
structured and logical execution flow.
An unconstrained loop in PL/SQL repeatedly executes a block of code without a
predefined stopping condition. It relies on explicit exit conditions, such as the
EXIT statement, within the loop body to terminate the loop execution. This
allows for flexible loop control based on dynamic conditions.
Code:
SET SERVEROUTPUT ON;
DECLARE
v_counter NUMBER := 1;
v_sum NUMBER := 0;
BEGIN
DBMS_Output.PUT_LINE('Starting the PL/SQL block...');
DBMS_Output.PUT_LINE('Initializing variables...');
LOOP
v_sum := v_sum + v_counter;
v_counter := v_counter + 1;
IF v_counter > 10 THEN
EXIT;
END IF;
END LOOP;
DBMS_Output.PUT_LINE('The sum of numbers from 1 to 10 is: ' || v_sum);
DBMS_Output.PUT_LINE('Ending the PL/SQL block...');
END;
/

Output:
PL/SQL PRACTICAL 2

Aim: Sequences:
a. Creating simple Sequences with clauses like START WITH,
INCREMENT BY,MAXVALUE, MINVALUE, CYCLE | NOCYCLE,
CACHE | NOCACHE, ORDER |NOORECER.

Description:
 START WITH: Specifies the starting value of the sequence.
 INCREMENT BY: Specifies the interval between sequence
numbers.
 MAXVALUE: Sets the maximum value the sequence can generate.
 MINVALUE: Sets the minimum value the sequence can generate.
 CYCLE | NOCYCLE: Indicates whether the sequence should
restart from the minimum value when it reaches the maximum
value (CYCLE), or stop generating values (NOCYCLE).
 CACHE | NOCACHE: Specifies whether to preallocate sequence
numbers in memory to improve performance (CACHE) or not
(NOCACHE).
 ORDER | NOORDER: Specifies whether sequence numbers are
generated in order of request (ORDER) or not (NOORDER).

Code:
SET SERVEROUTPUT ON;
CREATE SEQUENCE my_sequence
START WITH 1
INCREMENT BY 1
MAXVALUE 1000
MINVALUE 1
CYCLE
NOCACHE
NOORDER;
 Output:

b. Creating and using Sequences for tables.

Code:
CREATE SEQUENCE customer_order_seq
START WITH 1
INCREMENT BY 1
NOCACHE
NOORDER;

CREATE TABLE order (

order_id NUMBER PRIMARY KEY,
customer_id NUMBER,
order_date DATE,
created_at DATE
);
Output:
PL/SQL PRACTICAL 3

Aim: Writing PL/SQL Blocks with basic programming constructs by including

following:
a. If...then...Else, IF...ELSIF...ELSE... END IF
Description:
 If...then...Else:
The IF...THEN...ELSE statement in PL/SQL allows you to execute
a block of code if a condition is true, and an alternative block of
code if the condition is false. It's used for simple decision-making
in your PL/SQL programs.

 IF...ELSIF...ELSE... END IF:

The IF...ELSIF...ELSE...END IF statement in PL/SQL is used for
multi-way branching. It lets you test multiple conditions
sequentially and execute different blocks of code based on which
condition is true. If none of the conditions are met, the ELSE block
is executed.

Code:
DECLARE
v_num1 NUMBER := 10;
v_num2 NUMBER := 20;
v_result VARCHAR2(50);
BEGIN
IF v_num1 > v_num2 THEN
v_result := 'v_num1 is greater than v_num2';
ELSE
v_result := 'v_num1 is not greater than v_num2';
END IF;
DBMS_Output.PUT_LINE(v_result);
IF v_num1 > v_num2 THEN
v_result := 'v_num1 is greater than v_num2';
ELSIF v_num1 < v_num2 THEN
v_result := 'v_num1 is less than v_num2';
ELSE
v_result := 'v_num1 is equal to v_num2';
END IF;
DBMS_Output.PUT_LINE(v_result);
END;
/
Output:
b. Case statement
Description:
The CASE statement in PL/SQL is used for conditional logic that allows
you to execute different blocks of code based on the value of an
expression.
Code:
DECLARE
v_grade CHAR(1) := 'B';
v_result VARCHAR2(50);
BEGIN
CASE v_grade
WHEN 'A' THEN
v_result := 'Excellent';
WHEN 'B' THEN
v_result := 'Good';
WHEN 'C' THEN
v_result := 'Average';
WHEN 'D' THEN
v_result := 'Below Average';
ELSE
v_result := 'Fail';
END CASE;
DBMS_Output.PUT_LINE('Grade: ' || v_grade || ' - ' || v_result);
END;
/
Output:
PL/SQL PRACTICAL 4

Aim: Writing PL/SQL Blocks with basic programming constructs for following
Iterative Structure:
a. While-loop Statements
Description:
A WHILE loop in PL/SQL repeatedly executes a block of code as long as a
specified condition is true. It's used for scenarios where the number of
iterations is not known in advance, and the loop continues until the condition
evaluates to false.
Code:
DECLARE
v_counter NUMBER := 1;
v_sum NUMBER := 0;
BEGIN
WHILE v_counter <= 10 LOOP
v_sum := v_sum + v_counter;
v_counter := v_counter + 1;
END LOOP;
DBMS_Output.PUT_LINE('The sum of numbers from 1 to 10 is: ' || v_sum);
END;
/
Output:

b. For-loop Statements.
Description:
A FOR loop in PL/SQL is used to iterate over a range of values or a cursor
result set, executing a block of code for each iteration. It provides a concise
and controlled way to loop through a sequence of values, making it ideal for
scenarios where the number of iterations is known in advance.
Code:
DECLARE
v_sum NUMBER := 0;
BEGIN
FOR v_counter IN 1..10 LOOP
v_sum := v_sum + v_counter;
END LOOP;
DBMS_Output.PUT_LINE('The sum of numbers from 1 to 10 is: ' || v_sum);
END;
/
Output:
PL/SQL PRACTICAL 5

Aim: Writing PL/SQL Blocks with basic programming constructs by including

a GoTO tojump out of a loop and NULL as a statement inside IF.
Description:
GOTO to Jump Out of a Loop
The GOTO statement in PL/SQL is used to transfer control to a labeled
statement within the same block. It can be used to jump out of a loop, providing
a way to exit the loop unconditionally when a certain condition is met.
NULL as a Statement inside IF
The NULL statement in PL/SQL is a no-operation statement used to signify that
no action should be taken. When used inside an IF statement, it effectively acts
as a placeholder to explicitly indicate that no action is required when the
condition is met.

Code:
DECLARE
v_counter NUMBER := 1;
v_sum NUMBER := 0;
BEGIN
LOOP
v_sum := v_sum + v_counter;
v_counter := v_counter + 1;
IF v_counter > 10 THEN
GOTO exit_loop;
END IF;
END LOOP;
<<exit_loop>>
DBMS_Output.PUT_LINE('Exited the loop. The sum is: ' || v_sum);
IF v_sum > 50 THEN
DBMS_Output.PUT_LINE('The sum is greater than 50.');
ELSE
NULL; -- Do nothing
END IF;
END;
/

Output:
PROCEDURE
Varun Sirra
SCS2425099

1. Write a procedure to display greeting message when called

2. Write a procedure that accepts an input parameter and prints a message including that
parameter.
 3. Write a procedure that computes the square of the given number and returns it using an output
parameter

4. Create a table employees having field id, name, salary. Create a procedure to
A) insert a record
B) Update a record
C) Delete a record
Creating table
Insert a record

Update record
Delete a record
5. write a plsql procedure that demonstrates exception handling by catching an exception when
trying to divide by zero.

6. write a procedure that inserts multiple records into a table using loop.
7. Write a procedure that retrieves data from a table and displays it
8. Write a recursive procedure to calculate the factorial of a number.

9. Write a procedure to print the sum of two numbers.

10. Write a procedure to display reverse of a number.
ww
FUNCTIONS:
Varun SIrra
SCS2425099

[Link] a simple function that takes two numbers as input parameters and returns
their sum.
INPUT & OUTPUT

[Link] a function that takes multiple parameters (e.g., two numbers) and returns the
result of an operation (e.g., the average).
INPUT & OUTPUT

[Link] a function that concatenates two strings and returns the result.

INPUT & OUTPUT

[Link] a function that handles NULL input values and returns a default value.
INPUT & OUTPUT

[Link] a function that calculates the factorial of a number.

INPUT & OUTPUT

[Link] a recursive function to calculate the Fibonacci series.

INPUT & OUTPUT
[Link] a function with exception handling to catch errors such as division by zero.

INPUT & OUTPUT

[Link] a function that calculates a discount based on a customer's purchase

amount.
INPUT & OUTPUT

[Link] a function that takes a start and end date as input and returns the number of
days between them.
INPUT & OUTPUT

[Link] a function that uses OUT parameters to return multiple values.

INPUT & OUTPUT

[Link] a function that checks whether a string is a palindrome.

INPUT & OUTPUT

[Link] table employee and write a function to display the count of number of
employee.
INPUT & OUTPUT
EXCEPTION
Varun Sirra
SCS2425099

1. Write a simple anonymous PL/SQL block that handles the

NO_DATA_FOUND and TOO_MANY_ROWS [Link]: Fetch
data from a table and handle when no rows are found or multiple rows are
returned.

INPUT&OUTPUT

[Link] a custom exception and use it in an anonymous PL/SQL block.

INPUT&OUTPUT
[Link] an anonymous block with nested blocks where an exception in
the inner block is propagated to the outer block.

INPUT&OUTPUT

4. Handle multiple predefined exceptions such as NO_DATA_FOUND,

TOO_MANY_ROWS, and ZERO_DIVIDE.

INPUT&OUTPUT
5. Write a PL/SQL block that includes the OTHERS exception handler to
catch all types of errors and log the error message using SQLERRM.

INPUT&OUTPUT

6. Write a PL SQL block to demonstrate SQLcode and SQLERRM built-in

exceptions.

INPUT&OUTPUT
TRIGGERS
VARUN SIRRA
SCS2425099

-------------------------------------------------------------------------------------------------------------
1. Basic DML triggers
Create a trigger that automatically inserts the current stamp into a ‘created_at’
column when a new row is inserted into the ‘orders’ table.

CODE:

OUTPUT:
-Write a trigger that prevents deletion of records in the ‘employees’ table if the
employees is a manager (i.e, has subordinates ).

CODE:
-Create a trigger to log all updates to a ‘product’ take into an audit table,
capturing old and new values of the ‘price’ column

CODE:
2. Compound Triggers
-Create a compound trigger to handle ‘INSERT’ , ‘UPDATE’, and ‘DELETE’
operation to the ‘INVENTRYORY’ table. Ensure the compound trigger manges
operations as both the row and statement levels.

CODE:
-Write a compound trigger that ensures for each ‘UPDATE’ to the ‘employees’
table, if an employees salary is changed, the change is logged in a seperate
‘salary_changes’ table.

CODE:
3. Before and After triggers .
-Write a ‘BEFORE INSERT’ trigger for the ‘user’ table to automatically assign a
unique ID to eachnew user (if No ID is provided).

CODE:

-Create an ‘AFTER DELETE’ trigger that sends an email notification whenever a

record is deleted from the ‘customers’ table.

CODE:
4. INSTEAD of Triggers(Views)
-Create an ‘INSTEAD OF’ trigger on a view that updates the ‘employee_details’
views and directs changes to the underlying ‘employee’ table.

CODE:
5. DDL Triggers.
- Write a `BEFORE DROP` DDL trigger that prevents dropping of the `products`
table.

CODE:

OUTPUT:
 - Create an `AFTER ALTER` DDL trigger that logs schema changes, such as
adding or modifying columns in the `employees` table, to an `audit_log` table.

CODE:

6. Exception Handling in Triggers

- Write a trigger that raises an exception if an `INSERT` into the `orders` table
has a `quantity` less than 1.

CODE:
- Create a trigger that handles the `mutating table` error when trying to query
the same table being modified in a `BEFORE UPDATE` trigger on the
`employees` table.

CODE:
7. Using `:NEW` and `:OLD` in Triggers
- Create a trigger that calculates the percentage increase in the `salary` column
of the `employees` table whenever an update occurs, and logs the change into
a `salary_change` table.

CODE:

- Write a trigger that prevents any updates to the `salary` column if the new
value is less than the current value using `:NEW` and `:OLD` values.

CODE:
8. **Trigger to Prevent Invalid Data**
- Write a trigger that raises an error if an invalid `phone_number` format is
inserted into the `customers` table (e.g., the number must match a specific
regex format).

CODE:

- Create a trigger that checks if a `product`’s `price` is greater than zero before
inserting it into the `products` table

CODE:
Varun Sirra

PACKAGE SCS2425099

• Basic Package creation

• Create a package that defines a procedure to insert a new employee into the employees table.
The package should include the procedure and any necessary variables.
• Write a package that defies a function to calculate the total salary of an employee given their
employee_id.
• Package Specification and Body

• Define a package specification and body that includes a function to retrieve a product’s price
based on the product_id from the products table. Ensure the package is structured correctly
with the specification defining the function, and the body implementing the logic.
• Create a package with a procedure that deletes an employee from the employees table based
on the employee_id and logs the operation in an audit_log table.
• Package Variables and Constants

• Create a package that includes a constant variable for the maximum allowed salary
(MAX_SALARY) and a procedure that checks if an employee’s salary exceeds this value. If
it does, raise an exception.
• Write a package that includes a private (local) variable to store the total number of active
employees, and a procedure to update this count whenever an employee is added or removed.
• Package Functions

• Create a function that returns the full name of an employee (concatenating the first and last
names) from the employees table based on the employee_id.
• Write a function that returns the count of product in the inventory that are out of stock
• Package Procedures

• Create a package with a procedure to update the salary of an employee in the employees
table. The procedure should take the employee’s id and new salary as input parameters.
• Design a package with a procedure to transfer an employee from one department to another.
The procedure should update the department_id in the employees table
• Exception Handling in Packages

• Write a package that includes a procedure to add a new department to the departments table.
The procedure should raise an exception if the department already exist
• Create a package with a function that returns the employee_id based on the employee’s name.
If no matching employee is found, raise a custom exception and handle it within the package
body.
• Cursors in Packages

• Define a package that uses a cursor to iterate through all employees in a specific department
and returns the employee details.
• Create a package with a function that uses an implicit cursor to fetch and return the highest-
paid employee's details from the employees table.
 Name: Varun Anandam Sirra
Roll No: SCS2425099
PRACTICAL 11: MONGODB

➢ What is Mongo Db?

• MongoDB is a NoSQL database that stores data in JSON-like documents

instead of tables like SQL databases.
• It is schema-less, meaning no fixed structure is needed. Uses BSON
(Binary JSON) for storage.

➢ What is Document?
• A document is a single record in MongoDB, similar to a row in SQL.
• It is stored in key-value pairs (like JSON format).
• E.g.: - {
"_id": 1, "name": "Endrik", "age": 19, "city": "Sao Paulo
"}

➢ What is Collection?
A collection is a group of documents, similar to a table in SQL.
Documents inside a collection can have different structures.
E.g.: -
{“_id": 1, "name": "Gareth", "age": 38}
{“_id": 2, "name": "Bale", "city": "Tottenham”}

➢ What is Database?
A database is a container for multiple collections.
Like SQL, but in MongoDB, it does not enforce relationships.
1. Installation of pymongo

2. Checking if installation pymongo is properly done

3. Naming the database as customers (collection)

4. Checking if the collection exists and inserting only 1 record

5. Inserting many records

6. Insert with id

7. Find only one record

8. Finding many records

9. Address is 0 hence not displayed

10. Name=0 and address=1, this will give an error

11. To find any record of your own choice

12. Doc where the address stats with the letter s or higher

13. $regex to perform pattern matching on string fields (if the character is followed by ^ then
it begins with s and if it is followed by

14. Sorting all the names alphabetically (-1 for ascending and 1 for decending)

15. Updating address and name

16. Setting the limit for the number of records to be displayed

17. delete

18. deletion of data and collection