Write a function and pass a department number to it. If the DEPT table does not contain that department number, return a FALSE value, otherwise return a TRUE value. Print the appropriate message in the calling program based on the result.

Question

What will be an ideal response?

Answer 1

```
SQL> CREATE OR REPLACE FUNCTION find_dept
2 (i_id IN NUMBER)
3 RETURN BOOLEAN
4 IS
5 v_deptname dept.DeptName%TYPE;
6 BEGIN
7 SELECT DeptName
8 INTO v_deptname
9 FROM dept
10 WHERE DeptId = i_id;
11 IF v_deptname IS NOT NULL THEN
12 RETURN TRUE;
13 END IF;
14 EXCEPTION
15 WHEN NO_DATA_FOUND THEN
16 RETURN FALSE;
17 END;
18 /

Function created.


SQL> DECLARE
2 v_id dept.DeptId%TYPE := '&Department_Id';
3 found BOOLEAN;
4 BEGIN
5 found := find_dept(v_id);
6 IF found = TRUE THEN
7 DBMS_OUTPUT.PUT_LINE('Department '||v_id||' exists');
8 ELSE
9 DBMS_OUTPUT.PUT('Department '||v_id||' does not exist');
10 END IF;
11 END;
12 /
Enter value for department_id: 10
Department 10 exists

PL/SQL procedure successfully completed.

SQL> /
Enter value for department_id: 77
Department 77 does not exist

PL/SQL procedure successfully completed.
```