Explicit Cursors

Specify using CURSOR <cursor_name> IS <select_statement>. Can also specify cursor parameters during declaration.

Simple example:

DECLARE
    l_dept_id departments.dept_id%TYPE;
    l_dept_name departments.dept_name%TYPE;

    CURSOR cur_get_departments IS
        SELECT dept_id, dept_name
            FROM departments
            WHERE dept_id = 1;
BEGIN
    OPEN cur_get_departments;
    FETCH cur_get_departments
        INTO l_dept_id, l_dept_name;
    dbms_output.PUT_LINE(l_dept_name);
    CLOSE cur_get_departments;
END;

Multi row select example:

DECLARE
    CURSOR cur_get_employees IS
        SELECT emp_id,
               emp_sal * 0.10 bonus
            FROM employee;
    cur_get_employees_var cur_get_employees%ROWTYPE;
BEGIN
    OPEN cur_get_employees;
    LOOP
        FETCH cur_get_employees
            INTO cur_get_employees_var;
        EXIT WHEN cur_get_employees%NOTFOUND;
        dbms_output.PUT_LINE(cur_get_employees%ROWCOUNT);  -- cumulative count
    END LOOP;
    IF cur_get_employees%ISOPEN THEN
        CLOSE cur_get_employees;
    END IF;
END;

Cursor parameter example:

DECLARE
    CURSOR cur_get_departments(p_rows NUMBER DEFAULT 5) IS
        SELECT dept_id,
               dept_name,
            FROM departments
            WHERE ROWNUM <= p_rows;
    cur_get_departments_var cur_get_departments%ROWTYPE;
BEGIN
    OPEN cur_get_departments(2);  -- p_rows := 2
    LOOP
        FETCH cur_get_departments
            INTO cur_get_departments_var;
        EXIT WHEN cur_get_departments%NOTFOUND;
        dbms_output.PUT_LINE('Dept Id: ' || cur_get_departments_var.dept_id);
        dbms_output.PUT_LINE('RowCount: ' || cur_get_departments%ROWCOUNT);
    END LOOP;
    IF cur_get_departments%ISOPEN THEN
        CLOSE cur_get_departments;
    END IF;
END;