Snippet Name: 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.
Also see: » FUNCTIONS: Deterministic
» FUNCTIONS: Nested Functions
» FUNCTIONS: IF statement
» FUNCTIONS: date/time
» FUNCTIONS: Sample functions
» FUNCTIONS: drop
» FUNCTIONS: Recompile
» FUNCTIONS: DEBUG mode
» FUNCTIONS: IN OUT parameter
» FUNCTIONS: with output parameters
» FUNCTIONS: without parameters
» FUNCTIONS: Create function
» FUNCTIONS: special restrictions
» FUNCTIONS: System Privileges
» IN Function
» Built-In Functions: CASE
» Built-In Functions: DECODE
» SUBST and INSTR together
» INSTR (InString)
» SUBSTR (SubString)
» String Functions: REVERSE
» String Functions: LENGTH
» String Functions: INSTR
» String Functions: CONCAT
» String Functions: CHAR
» String Functions: INITCAP
» String Functions: LOWER
» String Functions: UPPER
» Date Functions: NUMTOYMINTERVAL
» Date Functions: NUMTODSINTERVAL
Comment: (none)
Language:
Highlight Mode: PLSQL
Last Modified: March 13th, 2009
|
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;
/ |