Advanced PL/SQL: Cursors, Packages and Exceptions

Explicit Cursors

A cursor fetches query rows one at a time inside PL/SQL. Cursor FOR loops open, fetch and close automatically.

Cursor For Loop

DECLARE
  CURSOR c_emp IS SELECT empno, ename, sal FROM emp;
BEGIN
  FOR r IN c_emp LOOP
    DBMS_OUTPUT.PUT_LINE(r.ename || ' earns ' || r.sal);
  END LOOP;
END;

Packages

Packages group procedures, functions and shared variables into one named unit with a specification and a body. Private helpers stay in the body, and public members can be called from SQL or other blocks.

Exception Handling

DECLARE
  v_name emp.ename%TYPE;
  v_id NUMBER := 99;
BEGIN
  SELECT ename INTO v_name FROM emp WHERE empno = v_id;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No employee ' || v_id);
END;

Key Points

• Explicit cursors give row-by-row control.
• Packages organize code into reusable units.
• Named exceptions such as NO_DATA_FOUND trap known errors.
• Add a WHEN OTHERS handler last for unexpected cases.