Snippet Name: FUNCTIONS: IF statement Description: A sample 'IF Statement' Function in PL/SQL. Also see:» FUNCTIONS: Deterministic » FUNCTIONS: Nested Functions » FUNCTIONS: date/time » FUNCTIONS: Sample functions » FUNCTIONS: drop » FUNCTIONS: Recompile » FUNCTIONS: DEBUG mode » FUNCTIONS: IN OUT parameter » FUNCTIONS: with output parameters » FUNCTIONS: with 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
Description: A sample 'IF Statement' Function in PL/SQL.
Also see:» FUNCTIONS: Deterministic » FUNCTIONS: Nested Functions » FUNCTIONS: date/time » FUNCTIONS: Sample functions » FUNCTIONS: drop » FUNCTIONS: Recompile » FUNCTIONS: DEBUG mode » FUNCTIONS: IN OUT parameter » FUNCTIONS: with output parameters » FUNCTIONS: with 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
-- There are three different syntax formats for these types of statements: -- Syntax #1: IF-THEN IF condition THEN {...statements...} END IF; -- Syntax #2: IF-THEN-ELSE IF condition THEN {...statements...} ELSE {...statements...} END IF; -- Syntax #3: IF-THEN-ELSIF IF condition THEN {...statements...} ELSIF condition THEN {...statements...} ELSE {...statements...} END IF; -- 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; /