ORACLE function development

Oracle function development 1. syntax for creating a FUNCTION: CREATE [or replace] FUNCTION name [(arg1 [mode] datatype [, ......])] -- When the parameter data type is specified, its length cannot be specified. Mode Value: IN, OUT, and in out. If the mode is not specified, it is represented as in return datatype -- the data type used to specify the function RETURN value, cannot specify its length IS | AS pl/SQL block; -- when creating a function, a RETURN clause must be included in the function header and at least one RETURN statement must be included in the function body. Example 1: No Parameter Function

SQL> CREATE OR REPLACE function HX_01 -- CREATE HX_01 function 2 RETURN VARCHAR2 3 IS 4 v_name varchar2 (20); 5 begin 6 select ename into v_name from emp where empno = 7369; 7 return v_name; 8 end; 9/Function createdSQL> select hx_01 from dual; -- execution Function HX_01--------------------------------------------------------------------------------SMITH

 

Example 2: An IN Parameter

SQL> CREATE OR REPLACE function HX_02 -- CREATE HX_02 function 2 (v_no in emp. empno % type) 3 RETURN VARCHAR2 4 IS 5 v_name varchar2 (20); 6 v_sal emp. sal % type; 7 v_dname dept. dname % type; 8 v_return varchar2 (100); 9 begin 10 select. ename,. sal, B. dname into v_name, v_sal, v_dname from emp a, dept B where. deptno = B. deptno and empno = v_no; 11 v_return: = 'name: '| rpad (v_name, 8) | LPAD ('salary:', 6) | v_sal | lpad ('Team ', 6) | v_dname; 12 return v_return; 13 end; 14/Function createdSQL> begin 2 dbms_output.put_line (hx_02 (7566 )); -- execute the function 3 end; 4 5/PL/SQL procedure successfully completed output: Name: JONES salary: 2975 Department RESEARCH or: SQL> select hx_02 (7566) from dual; HX_02 (7566) ---------------------------------------------------------------------------------- name: JONES salary: 2975 Department RESEARCH

 

Example 3: parameters with IN and OUT

SQL> CREATE FUNCTION hx_03 -- CREATE HX_03 FUNCTION 2 (v_n1 emp. empno % type, v_n2 out emp. ename % type, v_n3 out emp. sal % type, v_n4 out dept. dname % type) 3 return emp. job % type 4 as 5 job emp. job % type; 6 begin 7 select. job,. ename,. sal, B. dname into job, v_n2, v_n3, v_n4 8 from emp a, dept B 9 where. deptno = B. deptno 10 and. empno = v_n1; 11 return job; 12 end; 13/Function createddeclare v1 emp. job % type; v2 emp. ename % type; v3 emp. sal % type; v4 dept. dname % type; begin v1: = hx_03 (7566, v2, v3, v4); -- use the output value dbms_output.put_line ('Name: '| v2 | ''| 'occupation:' | v1 |'' | 'salary: '| v3 | ''|' department: '| v4); end;

 

Output: Name: JONES occupation: MANAGER salary: 2975 Department: RESEARCH example 4, with the in out Parameter

Create or replace function HX_04 (v_name in out varchar2) -- CREATE HX_04 function return VARCHAR2ASBEGIN select dept. dname into V_NAME from dept, emp where dept. DEPTNO = EMP. deptno and emp. ENAME = V_NAME; return null; END; DECLARE -- execute the function A VARCHAR2 (20); B VARCHAR2 (20); begin a: = 'ward '; B: = HX_04 (A); DBMS_OUTPUT.put_line ('a: = '| A |' B: = '| B); END; output: A: = sales B: =

 

2. Delete the function syntax drop function name;