Basic Oracle syntax

The basic syntax of Oracle basic syntax IS as follows: create or replace function <function_name> [(<parameters>)] RETURN <datatype> IS [declare section] BEGIN www.2cto.com [<statement (s)>] RETURN <expression>; [EXCEPTION <exception handler (s);] END [<function_name>]; from the preceding format, we can see that: 1) a Function must have a name <function_name>; 2) one or more parameters may exist. 3) the type of the returned value must be specified. <datatype> 4) The function body must contain the keyword BEGIN and END, and the RETURN value must exist; some simple examples 1. A function that obtains a name directly returns the string "create or replace func ". TION fun_getMyNameRETURN varchar2 isBEGIN RETURN 'luxh'; END; call: select fun_getMyName from dual; -- or select fun_getMyName () from dual; 2. Given employee ID, calculate the tax and insurance deduction functions from the employee table. This function contains a parameter, a description of a local variable, and exception handling. Create or replace function fun_deductions (emp_id NUMBER) -- emp_id receives the input parameter return number is total_deductions NUMBER; -- Define the returned variable BEGIN www.2cto.com SELECT tax + insurance -- find the required data INTO total_deductions -- assign a value to the variable total_deductions FROM emp where id = emp_id; RETURN total_deductions ctions; -- RETURN result exception when no_data_found THEN -- RETURN 0 cannot be found; when others then -- other exception return-1; END fun_deductions; call: select fun_deductions (1) from dual; 3. a function for calculating the cycle length, two input parameters, one constant defines create or replace function fun_circum_perimeter (angle NUMBER, radius NUMBER) -- defines two parameters, angle: angle, radius: radius return number is pi constant number: = 3.1415926; -- defines the CONSTANT circum_perimeter NUMBER; -- defines the RETURN variable BEGIN circum_perimeter: = ROUND (angle/360) * 2 * PI * radius, 2); -- calculate RETURN circum_perimeter; END fun_circum_perimeter; www.2cto.com call: SELECT fun_circum_perimeter (36) from dual; 4. SELECT the result to return create or replace function fun_if (n INTEGER) return integer isbegin if n = 0 then return 1; ELSIF n = 1 then return n; else return-1; end if; END; 5. factorial create or replace function fun_factorial (n POSITIVE) return positive isbegin if n = 1 then return n; else return n * fun_factorial (n-1); end if; END; 6. Fibonacci www.2cto.com create or replace function fun_fibonacci (n PLS_INTEGER) RETURN PLS_INTEGER IS fib_1 PLS_INTEGER: = 0; fib_2 PLS_INTEGER: = 1; begin if n = 1 then return fiber _ 1; ELSIF n = 2 then return fib_2; else return fun_fibonacci (n-2) + fun_fibonacci (n-1); end if; END; author CN. programmer. luxh