Oracle Custom Functions

Core tips:function is used to return specific data. When executing, we need to find the return value of a variable receiving function; The syntax is as follows: Create or Replace function function_name (argu1 [Mode1] datatype1, argu2 [Mode2] datatype2, ...) return Datat Ype is begin end; Execute VAR v1 varchar2 (Exec:v1:=function_na)

function is used to return specific data. When executing, we need to find the return value of a variable receiving function;

• Home balance Bao Wealth Management pass Baidu Hundred make hundred earn arbitrage change treasure current treasure cash treasure current financial information

The syntax is as follows: Create or Replace function function_name

(

ARGU1 [Mode1] Datatype1,

ARGU2 [Mode2] datatype2, ...

)

return datatype

Is

Begin

End

Execute VAR v1 varchar2 (100)

Exec:v1:=function_name

Without any parameters

Create or Replace function Get_user return VARCHAR2 is

Result VARCHAR2 (50);

Begin

Select username into Result from user_users;

return (Result);

End Get_user;

Perform:

With the in parameter

Create or Replace function get_sal (empname in VARCHAR2) return number is

Result number;

Begin

Select Sal into Result from EMP where ename=empname;

return (Result);

End Get_sal;

Execution: sql> var sal number

Sql> exec:sal:=get_sal (' Scott ');

Functions with Out parameters

Create or Replace function Get_info (e_name varchar2,job out VARCHAR2) return number is

Result number;

Begin

Select Sal,job to Result,job from EMP where ename=e_name;

return (Result);

End Get_info;

Execution: sql> var job varchar2 (20)

sql> var dname varchar2 (20)

sql> exec:d name:=get_info (' SCOTT ',: Job)

function with in out parameter

Create or Replace function result (NUM1 number,num2 in Out number) return number is

V_result number (6);

V_remainder number;

Begin

V_result: =num1/num2;

V_remainder: =mod (NUM1,NUM2);

NUM2: =v_remainder;

return (V_result);

Exception

When Zero_divide Then

Raise_application_error (-20000, ' cannot divide 0 ');

end result;

Execution: var result1 number;

var result2 number;

Exec:result2:=30

Exec:result1:=result (100,:RESULT2)

eg

1, one of the simplest custom function fun_test1 definition.

Create or Replace function Fun_test1 (p_1 number)--fun_test1 is the function name and has an input parameter p_1, which is number type. The return value is also number type

return number

Is

Begin

If P_1>0 Then

return 1;

Elsif P_1=0 Then

return 0;

Else

return-1;

End If;

End

--This function just knows the definition and format of the custom function. There is no use in fact.

2. Fun_test1 A stored procedure pro_fun_test1_1 example of a call to a custom function:

Create or replace procedure pro_fun_test1_1 (

P1_in in number,

P2_out out number

)

As

Begin

P2_out:=fun_test1 (p1_in);

End Pro_fun_test1_1;

--One input parameter, one output parameter

3. Fun_test1 A stored procedure pro_fun_test1_2 example of a call to a custom function:

Create or replace procedure pro_fun_test1_2 (

P1_in in number,

P2_out out number

)

As

T_1 number;

Begin

Select Fun_test1 (p1_in) +100 into P2_out

from bill_org where org_id=1;

End Pro_fun_test1_2;

--The calling method of the custom function is the same as the rest of Oracle's intrinsic functions.

Ii. Introduction to the definition and use of packages

Packages are generally a collection of procedures and functions, and better encapsulation of procedures and functions, generally not for fields.

The package consists of Baotou and the package body.

1, the definition of Baotou:

Baotou simply describes the methods in the package and does not implement

Grammar:

Create or Replace package Mypackage_1

Is

Procedure Syahello (VName varchar2);--affirmed a process in the package

End

2, the package body definition:

Inclusion is the concrete implementation of the process and function defined in Baotou.

Create or replace package body Mypackage_1

Is

Procedure Syahello (VName VARCHAR2)--implementation of the process defined in the package

Is

Begin

Dbms_output.put_line (' Hello ' | | VName);

End

End

It is important to note that:

The name after the Create or replace package must match the name behind the Create or replace package body,

If the name after the Create or replace package body is changed to, ' MyPackage '

Otherwise, there will be errors such as the following:

must indicate identifier ' MyPackage '

3. Custom methods for calling packets:

Create or replace procedure Pro_test_package (

P1_in string

)

As

Begin

Mypackage_1.syahello (p1_in);

End Pro_test_package;

EG2:

--Functions with no parameters

Create or Replace function Get_user return varchar2 is V_user varchar2 (50);

Begin

Select username to V_user from User_users;

return v_user;

return v_user;

--Test

Method One

Select Get_user from dual;

Method Two

sql> var v_name varchar2 (50)

Sql> Exec:v_name:=get_user;

--Functions with in parameters

Create or Replace function Get_empname (v_id in number) return VARCHAR2 as V_name varchar2 (50);

Begin

Select name into V_name from employee where id = v_id;

return v_name;

exception

When No_data_found then Raise_application_error (-20001, ' The ID you entered is invalid! ');

End Get_empname;

Report:

Function call Restrictions

1, the SQL statement can only call the storage function (server side), and cannot call the client's function

2, SQL can only call with input parameters, not with output, input and output functions

3. SQL cannot use the unique data type (Boolean,table,record, etc.)

4. A function called in an SQL statement cannot contain insert,update and DELETE statements

View function Yard Source code

Oracle stores the function name and its source code information in a data dictionary User_source

Select text from User_source where name= ' get_empname ';

Delete a function

Drop function Get_empname;

To determine when a task expires:

Create or Replace function geturgentstate (M_taskid varchar2,

M_sendtime date,

M_flag varchar2)

Return VARCHAR2 is

MyDate date;

Expiretime date;

strSQL VARCHAR2 (200);

Begin

MyDate: = M_sendtime;

strSQL: = ' Select Max (expiretime) from t_wf_supervise where TASKID = ' ' | |

M_taskid | | ‘‘‘‘;

Execute Immediate strSQL

into Expiretime;

-No expiry time is the normal state

If expiretime is null then

If M_flag = ' String ' Then

return ' normal ';

End If;

If M_flag = ' Img ' Then

Return ' cb_execute.gif ';

End If;

End If;

--Does not send the task, is to determine the current time

If m_sendtime is null then

MyDate: = sysdate;

End If;

If Expiretime < mydate Then

If M_flag = ' String ' Then

return ' overdue ';

End If;

If M_flag = ' Img ' Then

Return ' cb_limit.gif ';

End If;

End If;

--Task alerts of less than 3 days

If expiretime-mydate < 3 Then

If M_flag = ' String ' Then

return ' warning ';

End If;

If M_flag = ' Img ' Then

Return ' cb_warning.gif ';

End If;

Else

If M_flag = ' String ' Then

return ' normal ';

End If;

If M_flag = ' Img ' Then

Return ' cb_execute.gif ';

End If;

End If;

End

Querying other table data:

Create or Replace function Getprenode (m_pretaskid varchar2) return varchar2 is

NodeName VARCHAR2 (50);

strSQL VARCHAR2 (200);

Begin

If M_pretaskid is null then

Return ';

End If;

strSQL: = ' Select Max (nodename) from t_wf_tasklist where TaskID = ' ' | |

m_pretaskid| | ‘‘‘‘;

Execute Immediate strSQL

into NodeName;

return nodename;

End

Formatted header output:

Create or Replace function Formattitle (M_title varchar2,

M_length number,

M_fillchar varchar2) return varchar2 is

Begin

If LENGTHB (m_title) > M_length*2 Then

Return substr (M_title, 0,m_length) | | M_fillchar;

Else

return m_title;

End If;

End