implicit vs explicit

-- This is an implicit cursor.
-- If you want to process every row returned by a query use this.

Code:

---

DECLARE
  CURSOR cur_implicit IS
      SELECT department
        FROM mytable
      WHERE department = 'Sales';
BEGIN
  FOR v_row IN cur_implicit
  LOOP
--    do stuff here
  END LOOP;

---

-- This is function that uses an explicit cursor to see if there is data.
-- It doesn't tell you how many rows, or anything about any other column
-- in the table. But if you want to know whether or not a column has a
-- given value this is a good way to find out.

Code:

---

CREATE OR REPLACE FUNCTION departmentExists
        ( p_department mytable.department%TYPE)
RETURN BOOLEAN
  CURSOR cur_explicit IS
      SELECT department
        FROM mytable
      WHERE department = 'Sales';

  v_department mytable.department%TYPE;
BEGIN
  OPEN cur_explicit;
  FETCH cur_explicit INTO v_department;
  CLOSE cur_explicit;
  RETURN TRUE;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
      CLOSE cur_explicit;
      RETURN FALSE;
END departmentExists;
/

---

-- You can also do this, but the potential errors are NO_DATA_FOUND
-- and TOO_MANY_ROWS_RETURNED.

Code:

---

DECLARE
  v_mytable_row mytable%ROWTYPE;
BEGIN
  SELECT col1, col2, col3
      INTO  v_mytable_row
      WHERE department = 'Sales';
END;
/

---

gandolf989 wrote

Quote:

---

CREATE OR REPLACE FUNCTION departmentExists
( p_department mytable.department%TYPE)
RETURN BOOLEAN
CURSOR cur_explicit IS
SELECT department
FROM mytable
WHERE department = 'Sales';

v_department mytable.department%TYPE;
BEGIN
OPEN cur_explicit;
FETCH cur_explicit INTO v_department;
CLOSE cur_explicit;
RETURN TRUE;
EXCEPTION
WHEN NO_DATA_FOUND THEN
CLOSE cur_explicit;
RETURN FALSE;
END departmentExists;
/

---

This has potential retrieving incorrect informattion IF MORE THAN 1 RECORD satisfy specified condition.

gandolf989 wrote

Quote:

---

CREATE OR REPLACE FUNCTION departmentExists
( p_department mytable.department%TYPE)
RETURN BOOLEAN
CURSOR cur_explicit IS
SELECT department
FROM mytable
WHERE department = 'Sales';

v_department mytable.department%TYPE;
BEGIN
OPEN cur_explicit;
FETCH cur_explicit INTO v_department;
CLOSE cur_explicit;
RETURN TRUE;
EXCEPTION
WHEN NO_DATA_FOUND THEN
CLOSE cur_explicit;
RETURN FALSE;
END departmentExists;
/

---

this function is correct to check if department exists in table.

but if you want to retrieve some column values, you should use

Quote:

---

DECLARE
v_mytable_row mytable%ROWTYPE;
BEGIN
SELECT col1, col2, col3
INTO v_mytable_row
WHERE department = 'Sales';
END;

---

and check for NO_DATA_FOUND and TOO_MANY_ROWS