function Development in Oracle

ORACLE function Development

1. Establish the syntax of the function:

CREATE [OR REPLACE] Function function name

[ (arg1 [mode] datatype[,........]) ]--when specifying the parameter data type, you cannot specify its length. Mode value: In, out, in out, represented as in when mode is not specified

return datatype --the data type used to specify the function return value, cannot specify its length

Is|as

PL/SQL block; --When a function is established, it must have a return clause in the function's head and at least one return statement in the function body.

Example 1, no parameter function

sql> Create OR REPLACE function hx_01--Creating hx_01 functions
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 created

Sql> select hx_01 from dual; --Execution function

Hx_01
--------------------------------------------------------------------------------
SMITH

Example 2, with an in parameter

sql> Create OR REPLACE function hx_02--Creating hx_02 functions
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
Ten select A.ename,a.sal,b.dname to V_name,v_sal,v_dname from emp a,dept b where A.deptno=b.deptno and empno=v_no;
v_return:= ' name: ' | | Rpad (v_name,8) | | Lpad (' Wages: ', 6) | | v_sal| | Lpad (' Department ', 6) | | V_dname;
return v_return;
The end;
14/

Function created

sql> begin
2 Dbms_output.put_line (hx_02 (7566)); --Execution function
3 END;
4
5/

PL/SQL procedure successfully completed
Output: Name: JONES Salary: 2,975 Department of Science

or:

sql> Select hx_02 (7566) from dual;

Hx_02 (7566)
--------------------------------------------------------------------------------
Name: JONES Salary: 2,975 Department of Science

Example 3, with in, out parameters

sql> Create function hx_03--Creating hx_03 functions
2 (v_n1 emp.empno%type,v_n2 out emp.ename%type,v_n3 off 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 A.job,a.ename,a.sal,b.dname into Job,v_n2,v_n3,v_n4
8 from EMP a,dept b
9 where A.deptno=b.deptno
Ten and A.empno=v_n1;
one return job;
The end;
13/

Function created

Declare
V1 emp.job%type;
V2 Emp.ename%type;
V3 Emp.sal%type;
V4 Dept.dname%type;
Begin
V1:=HX_03 (7566,V2,V3,V4); --Using the variable you just defined to receive the output value of the function
Dbms_output.put_line (' Name: ' | | v2| | ' ' | | ' Occupation: ' | | v1| | ' ' | | ' Salary: ' | | v3| | ' ' | | ' Department: ' | | V4);
End

Output: Name: JONES Occupation: MANAGER Salary: 2,975 Department: Science

Example 4, with an in-out parameter

Create OR REPLACE function hx_04 (v_name in Out varchar2)--Creating a hx_04 function
RETURN VARCHAR2
As
BEGIN
SELECT DEPT. Dname into V_name from Dept,emp WHERE DEPT. Deptno=emp. DEPTNO and EMP. Ename=v_name;
RETURN NULL;
END;

DECLARE--Execution 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 function syntax

The name of the drop function function;

function Development in Oracle