Functions: With Parameters

Description: By passing parameters into a function, the parameters can be evaluated, manipulated, and/or used to control the program flow within the function.

Syntax -
 
CREATE OR REPLACE FUNCTION <function_name> (
<parameters> [IN | OUT | IN OUT] [NOCOPY] <data_type>)
RETURN <data_type> [AUTHID <current_USER | DEFINER>] IS
 
<constant, EXCEPTION, AND variable declarations>
 
BEGIN
  <code_here>;
END <function_name>;
/
 
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;
/

Enjoyed this post? Share it!

 

Leave a comment

Your email address will not be published.