Exception in PL/SQL
It looks like you need information on Exception Handling, Cursors, and
Procedures in SQL and PL/SQL. I'll explain each topic briefly:
1. Exception Handling
a) Predefined Exceptions
These are system-defined exceptions that occur during runtime. Some common
predefined exceptions:
NO_DATA_FOUND – No rows are returned by a SELECT INTO statement.
TOO_MANY_ROWS – A SELECT INTO statement returns more than one row.
ZERO_DIVIDE – An attempt to divide by zero.
INVALID_NUMBER – Conversion of a character string to a number fails.
Example:
BEGIN
SELECT salary INTO v_salary FROM employees WHERE id = 101;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No record found.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Some other error occurred.');
END;
/
b) User-Defined Exceptions
You can define your own exceptions using the EXCEPTION keyword.
Example:
Exception in PL/SQL 1
DECLARE
insufficient_balance EXCEPTION;
balance NUMBER := 500;
BEGIN
IF balance < 1000 THEN
RAISE insufficient_balance;
END IF;
EXCEPTION
WHEN insufficient_balance THEN
DBMS_OUTPUT.PUT_LINE('Balance is too low.');
END;
/
2. Cursors
Cursors are used to retrieve multiple rows from a database.
a) Declaring a Cursor
DECLARE
CURSOR emp_cursor IS SELECT id, name FROM employees;
b) Opening and Retrieving Records
BEGIN
OPEN emp_cursor;
FETCH emp_cursor INTO v_id, v_name;
END;
c) Closing a Cursor
CLOSE emp_cursor;
Exception in PL/SQL 2
d) Explicit vs Implicit Cursors
Implicit Cursor: Automatically created by PL/SQL for SELECT statements.
Explicit Cursor: Created explicitly using DECLARE CURSOR .
e) Parameter Passing in Cursors
DECLARE
CURSOR emp_cursor(dept_id NUMBER) IS
SELECT id, name FROM employees WHERE department = dept_id;
3. Procedures
Procedures are stored subprograms that execute a set of SQL statements.
a) Create and Drop Procedure
CREATE PROCEDURE get_salary (emp_id IN NUMBER, emp_salary OUT NUMB
ER) AS
BEGIN
SELECT salary INTO emp_salary FROM employees WHERE id = emp_id;
END;
/
To drop a procedure:
DROP PROCEDURE get_salary;
b) Calling Procedures
DECLARE
v_salary NUMBER;
BEGIN
get_salary(101, v_salary);
DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);
Exception in PL/SQL 3
END;
/
c) Granting EXECUTE Permission
GRANT EXECUTE ON get_salary TO user_name;
4. Problems on Exception Handling, Cursors, and Procedures
1. Handle a division by zero error using exception handling.
2. Fetch all employees from a department using a cursor.
3. Create a procedure that returns the highest salary in a department.
Let me know if you need detailed explanations with examples! 🚀
Exception in PL/SQL 4
