Mega Code Archive
Use a nested block to catch exceptions from singleton SELECT
<![CDATA[
SQL> CREATE TABLE good_for (
2 product_id NUMBER,
3 good_for INTERVAL YEAR(2) TO MONTH NOT NULL
4 );
Table created.
SQL>
SQL> CREATE OR REPLACE FUNCTION get_expiration (p_product_id NUMBER)
2 RETURN DATE
3 AS
4 v_good_for good_for.good_for%type;
5 expiration_date DATE;
6 found_flag BOOLEAN;
7 BEGIN
8 BEGIN
9 SELECT gf.good_for INTO v_good_for
10 FROM good_for gf
11 WHERE gf.product_id = p_product_id;
12 found_flag := TRUE;
13 EXCEPTION
14 WHEN OTHERS THEN
15 found_flag := FALSE;
16 END;
17 IF found_flag THEN
18 expiration_date := TRUNC(SYSDATE) + v_good_for;
19 ELSE
20 expiration_date := null;
21 END IF;
22
23 RETURN expiration_date;
24 END;
25 /
Function created.
SQL>
SQL> INSERT INTO good_for (product_id, good_for)
2 VALUES (1, INTERVAL '1-4' YEAR TO MONTH);
1 row created.
SQL>
SQL> SELECT get_expiration(1) FROM DUAL;
GET_EXPIR
---------
24-NOV-09
SQL>
SQL> drop table good_for;
Table dropped.
SQL>
]]>