PL/SQL FUNCTIONS WITH VARIOS OPTIONS

------------------------------------------------------------
-Functions - debug
-------------------------------------------------------------

To examine a function in debug mode

DESC user_plsql_object_settings

SELECT name, TYPE, plsql_debug
FROM user_plsql_object_settings;

ALTER FUNCTION test1 COMPILE DEBUG;

------------------------------------------------------------
-Function without Parameter
-------------------------------------------------------------
CREATE OR REPLACE FUNCTION fn_easy RETURN VARCHAR2 IS
BEGIN
RETURN 'This is a simple function';
END fn_easy;
/

------------------------------------------------------------
-Function - IN Parameter
-------------------------------------------------------------
CREATE OR REPLACE FUNCTION is_ssn (string_in IN VARCHAR2)
RETURN BOOLEAN IS
-- validating ###-##-#### format
incorrect EXCEPTION;
delim CHAR(1);
part1 NUMBER(3,0);
part2 NUMBER(2,0);
part3 NUMBER(4,0);
BEGIN
IF LENGTH(string_in) <> 11 THEN
RAISE incorrect;
END IF;

part1 := TO_NUMBER(SUBSTR(string_in,1,3),'999');

delim := SUBSTR(string_in,4,1);
IF delim <> '-' THEN
RAISE incorrect;
END IF;

part2 := TO_NUMBER(SUBSTR(string_in,5,2),'99');

delim := SUBSTR(string_in,7,1);
IF delim <> '-' THEN
RAISE incorrect;
END IF;

part3 := TO_NUMBER(SUBSTR(string_in,8,4),'9999');

RETURN TRUE;
EXCEPTION
WHEN incorrect THEN
RETURN FALSE;
WHEN OTHERS THEN
RETURN FALSE;
END is_socsecno;
/

SET serveroutput ON

BEGIN
IF is_ssn('123-45-6789') THEN
DBMS_OUTPUT.put_line('True');
ELSE
DBMS_OUTPUT.put_line('False');
END IF;
END;
/

BEGIN
IF is_ssn('123-A5-6789') THEN
DBMS_OUTPUT.put_line('True');
ELSE
DBMS_OUTPUT.put_line('False');
END IF;
END;
/

BEGIN
IF is_ssn('123=45-6789') THEN
DBMS_OUTPUT.put_line('True');
ELSE
DBMS_OUTPUT.put_line('False');
END IF;
END;
/

BEGIN
IF is_ssn('123-A5-67890') THEN
DBMS_OUTPUT.put_line('True');
ELSE
DBMS_OUTPUT.put_line('False');
END IF;
END;
/



-----------------------------------------------------------------
--FUNCTIONS: OUT parameters
-----------------------------------------------------------------

CREATE OR REPLACE FUNCTION out_fn (outparm OUT VARCHAR2)
RETURN VARCHAR2 IS

BEGIN
outparm := 'out param';
RETURN 'return param';
END out_fn;
/

SET serveroutput ON

DECLARE
retval VARCHAR2(20);
outval VARCHAR2(20);
BEGIN
retval := out_fn(outval);
DBMS_OUTPUT.put_line(outval);
DBMS_OUTPUT.put_line(retval);
END;
/



-----------------------------------------------------------
-- FUNCTIONS: IN OUT parameter
----------------------------------------------------------
CREATE OR REPLACE FUNCTION inout_fn (outparm IN OUT VARCHAR2)
RETURN VARCHAR2 IS

BEGIN
outparm := 'Coming out';
RETURN 'return param';
END inout_fn;
/

SET serveroutput ON

DECLARE
retval VARCHAR2(20);
ioval VARCHAR2(20) := 'Going in';
BEGIN
DBMS_OUTPUT.put_line('In: ' || ioval);
retval := inout_fn(ioval);
DBMS_OUTPUT.put_line('Out: ' || ioval);
DBMS_OUTPUT.put_line('Return: ' || retval);
END;
/

--------------------------------------------------------------------------------
--FUNCTION OUT PARAMTER
-------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION out_fn (outparm OUT VARCHAR2)
RETURN VARCHAR2 IS

BEGIN
outparm := 'out param';
RETURN 'return param';
END out_fn;
/

SET serveroutput ON

DECLARE
retval VARCHAR2(20);
outval VARCHAR2(20);
BEGIN
retval := out_fn(outval);
DBMS_OUTPUT.put_line(outval);
DBMS_OUTPUT.put_line(retval);
END;
/
----------------------------------------------------------------
--Function to determine if a number is even:
--------------------------------------------------------------------
CREATE OR REPLACE FUNCTION is_even(num_in NUMBER) RETURN BOOLEAN IS
BEGIN
IF MOD(num_in, 2) = 0 THEN
RETURN TRUE;
END IF;
EXCEPTION
WHEN OTHERS THEN
RETURN FALSE;
END is_even;
/

-----------------------------------------------------------------------
-- Function to determine if a number is odd:
-----------------------------------------------------------------------
CREATE OR REPLACE FUNCTION is_odd(num_in NUMBER) RETURN BOOLEAN IS
BEGIN
RETURN MOD(num_in, 2) = 1;
EXCEPTION
WHEN OTHERS THEN
RETURN FALSE;
END is_odd;
/


---------------------------------------------------------------------
-- Sample IF Statement Function:
----------------------------------------------------------------------
CREATE OR REPLACE FUNCTION fn_price(pProd VARCHAR2)
RETURN PLS_INTEGER IS
tabPrice discounts.prodprice%TYPE;
tabDisc discounts.proddisc%TYPE;
i PLS_INTEGER;
BEGIN
SELECT COUNT(*)
INTO i
FROM discounts
WHERE prodname = pProd;

IF i <> 0 THEN
SELECT prodprice, proddisc
INTO tabPrice, tabDisc
FROM discounts
WHERE prodname = pProd;

RETURN tabPrice - (tabPrice * tabDisc/100);
ELSE
RETURN 0;
END IF;
EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END fn_price;



------------------------------------------------------------------
-- Function - time difference
------------------------------------------------------------------
CREATE OR REPLACE FUNCTION
time_diff(DATE_1 IN DATE, DATE_2 IN DATE)
RETURN NUMBER IS
NDATE_1 NUMBER;
NDATE_2 NUMBER;
NSECOND_1 NUMBER(5, 0);
NSECOND_2 NUMBER(5, 0);
BEGIN
-- Get Julian date number from
-- first date (DATE_1)
NDATE_1 := TO_NUMBER(TO_CHAR(DATE_1, 'J'));

-- Get Julian date number from
-- second date (DATE_2)
NDATE_2 := TO_NUMBER(TO_CHAR(DATE_2, 'J'));

-- Get seconds since midnight
-- from first date (DATE_1)
NSECOND_1 := TO_NUMBER(TO_CHAR(DATE_1, 'SSSSS'));

-- Get seconds since midnight
-- from second date (DATE_2)
NSECOND_2 := TO_NUMBER(TO_CHAR(DATE_2, 'SSSSS'));

RETURN (((NDATE_2 - NDATE_1)*86400)+(NSECOND_2 - NSECOND_1));
END time_diff;
/