To enable the output, run the following command before executing any PL/SQL block.
SET SERVEROUTPUT ON;
The forward slash symbol / at the end tells SQL*Plus to execute the PL/SQL block.
1. Implicit Cursor and %TYPE
Implicit Cursor: PL/SQL automatically creates a cursor for every SQL SELECT statement that returns a single row. These are called implicit cursors because the programmer does not have to explicitly declare or control them. PL/SQL automatically opens, fetches, and closes these cursors.
%TYPE: The %TYPE attribute is used to declare a variable that has the same data type as a column in the table. This is useful for consistency and reduces the chance of errors.
DECLARE
v_faculty_id FACULTY.faculty_id%TYPE;
BEGIN
SELECT faculty_name INTO v_faculty_id FROM FACULTY WHERE faculty_id = '1';
DBMS_OUTPUT.PUT_LINE('Faculty Name: ' || v_faculty_id);
END;
/
2. Explicit Cursor and %ROWTYPE
Explicit Cursor: An explicit cursor is a cursor that a programmer defines and controls. It is used for queries that return more than one row, and the programmer can open, fetch, and close it manually.
%ROWTYPE: The %ROWTYPE attribute is used to define a variable that can hold an entire row of data from a table. It simplifies handling rows of data when using cursors.
DECLARE
CURSOR faculty_cursor IS
SELECT * FROM FACULTY WHERE dept_id = 10;
v_faculty FACULTY%ROWTYPE;
BEGIN
OPEN faculty_cursor;
LOOP
FETCH faculty_cursor INTO v_faculty;
EXIT WHEN faculty_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Faculty ID: ' || v_faculty.faculty_id || ', Faculty Name: ' ||
v_faculty.faculty_name);
END LOOP;
CLOSE faculty_cursor;
END;
/
3. Explicit Cursor and %TYPE
Explicit Cursor with %TYPE: Using explicit cursors, we can define variables that match the datatype of specific columns using %TYPE. This ensures that the variable data types stay consistent with the column types in the table.
DECLARE
CURSOR faculty_cursor IS
SELECT faculty_name, faculty_address FROM FACULTY;
v_name FACULTY.faculty_name%TYPE;
v_address FACULTY.faculty_address%TYPE;
BEGIN
OPEN faculty_cursor;
FETCH faculty_cursor INTO v_name, v_address;
DBMS_OUTPUT.PUT_LINE('Faculty Name: ' || v_name);
CLOSE faculty_cursor;
END;
/
4. Creating and Using a Function
Function: A function in PL/SQL is a named block that performs a specific task and returns a single value. Functions can be called in SQL queries or other PL/SQL blocks.
CREATE OR REPLACE FUNCTION get_dept_name(p_dept_id INT)
RETURN VARCHAR2 IS
v_dept_name DEPARTMENT.dept_name%TYPE;
BEGIN
SELECT dept_name INTO v_dept_name FROM DEPARTMENT WHERE dept_id = p_dept_id;
RETURN v_dept_name;
END;
/
BEGIN
DBMS_OUTPUT.PUT_LINE(get_dept_name(10));
END;
/
5. Usage of Procedure
Procedure: A procedure in PL/SQL is a named block similar to a function, but it does not return a value. It can perform actions like inserting, updating, or deleting records in a table.
While adding new faculty details using the procedure, ensure that the faculty id you are adding does not exist. Otherwise it will generate error as the faculty id is declared as primary key.
CREATE OR REPLACE PROCEDURE add_faculty(
new_faculty_id IN FACULTY.faculty_id%TYPE,
new_faculty_name IN FACULTY.faculty_name%TYPE
) IS
BEGIN
INSERT INTO FACULTY (faculty_id, faculty_name) VALUES (new_faculty_id, new_faculty_name);
END;
/
BEGIN
add_faculty('14', 'David King');
END;
/
select * from FACULTY;
6. Implementation of Trigger
Trigger: A trigger is a stored program that automatically executes (or “fires”) in response to certain events on a particular table or view. For example, you can have a trigger fire before or after an INSERT, UPDATE, or DELETE operation.
CREATE OR REPLACE TRIGGER trg_before_faculty_insert
BEFORE INSERT ON FACULTY
FOR EACH ROW
BEGIN
IF :NEW.faculty_date_of_joining < TO_DATE('2000-01-01', 'YYYY-MM-DD') THEN
RAISE_APPLICATION_ERROR(-20001, 'Date of joining must be after the year 2000');
END IF;
END;
/
-- Test the trigger by inserting an invalid record
BEGIN
INSERT INTO FACULTY (faculty_id, faculty_name, faculty_address, faculty_date_of_joining, dept_id)
VALUES ('7', 'Sam Wilson', '505 Maple St', TO_DATE('1999-12-31', 'YYYY-MM-DD'), 20);
END;
/
BEGIN
INSERT INTO FACULTY (faculty_id, faculty_name, faculty_address, faculty_date_of_joining, dept_id)
VALUES ('15', 'Sam Wilson', '505 Maple St', TO_DATE('2001-12-31', 'YYYY-MM-DD'), 20);
END;
/
7. User-Defined Exception with Cursor
User-Defined Exception: PL/SQL allows programmers to define their own exceptions in addition to the built-in ones. A user-defined exception can be used to handle specific error conditions that are not covered by standard exceptions.
DECLARE
CURSOR emp_cursor IS
SELECT faculty_id FROM FACULTY WHERE faculty_id = '100'; -- Assume no faculty with id 100
v_faculty_id FACULTY.faculty_id%TYPE;
no_data_found EXCEPTION; -- User-defined exception
BEGIN
OPEN emp_cursor;
FETCH emp_cursor INTO v_faculty_id;
IF emp_cursor%NOTFOUND THEN
RAISE no_data_found; -- Raise user-defined exception if no data found
END IF;
DBMS_OUTPUT.PUT_LINE('Faculty ID: ' || v_faculty_id);
EXCEPTION
WHEN no_data_found THEN
DBMS_OUTPUT.PUT_LINE('No data found for the specified faculty ID.');
END;
/
DELETE ALL TABLES
BEGIN
FOR t IN (SELECT table_name FROM user_tables) LOOP
EXECUTE IMMEDIATE 'DROP TABLE ' || t.table_name || ' CASCADE CONSTRAINTS PURGE';
END LOOP;
END;
/
SELECT table_name FROM user_tables;