CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
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 ;
/
SQL University.net courses meet the most demanding needs of the business world for advanced education
in a cost-effective manner. SQL University.net courses are available immediately for IT professionals
and can be taken without disruption of your workplace schedule or processes.
Compared to traditional travel-based training, SQL University.net saves time and valuable corporate
resources, allowing companies to do more with less. That's our mission, and that's what we deliver.
Click here to find out more
26 users online
© 2009 psoug.org