Oracle Custom Functions 1

A user-defined function is a block of code stored in a database that can be returned to the calling program. Called as a system function, such as the max (value) function, where value is called a parameter. There are 3 types of function arguments.

In Parameter type: represents the input parameter to the function.

Out parameter type: Indicates that the parameter is assigned in the function and can be passed to the function calling program.

In out parameter type: Indicates that the parameter can be either passed or assigned.

1. Syntax format:

The syntax format created by SQL syntax is:

CREATE OR REPLACE function function_name/* Functions name */
(
Parameter_name1,mode1 datatype1,/* Parameter definition section */
Parameter_name2,mode2 Datatype2,
Parameter_name3,mode3 Datatype3
...
)
Return return_datatype/* Define return value type */
Is/as
BEGIN
Function_body/* Function body part */
Return scalar_expression/* Returns statement */
END function_name;

Description

Function_name:: User-defined function name. The function name must conform to the identifier's definition rule, which is unique to its owner in the database.

Parameter: User-defined parameters. A user can define one or more parameters.

Mode: Parameter type.

DataType: The data type of the user-defined parameter.

Return_type:: The data type of the user return value.

The function returns the value of the scalar_expression expression, and the Function_body function body is composed of a PL/SQL statement.

2. Example

Function code:

Create or Replace function T01001_count
return number
Is
COUNT_T01001 number;
Begin
Select COUNT (*) into count_t01001 from T01001;
return (COUNT_T01001);
End T01001_count; --Remember to make sure to score number

Call:
Declare
I number;
Begin
I:=t01001_count ();
Dbms_output.put_line (To_char (i));
End --Remember to make sure to score number

Attention:

(1) If the function has no arguments, then the function name should not be followed by parentheses;

(2) When you create a function, you must remember to write the name of the letter after the end.

--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;
End Get_user;

--Test
Method One
Select Get_user from dual;

Method Two
sql> var v_name varchar2 (50)
Sql> Exec:v_name:=get_user;

The PL/SQL process has completed successfully.

sql> Print V_name

V_name
------------------------------
TEST

Method Three
sql> exec dbms_output.put_line (' Current database user is: ' | | Get_user);
The current database user is: TEST

The PL/SQL process has completed successfully.
--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;
End Get_user;
--Test
Method One
Select Get_user from dual;
Method Two
sql> var v_name varchar2 (50)
Sql> Exec:v_name:=get_user;
The PL/SQL process has completed successfully.
sql> Print V_name
V_name
------------------------------
TEST
Method Three
sql> exec dbms_output.put_line (' Current database user is: ' | | Get_user);
The current database user is: TEST
The PL/SQL process has completed successfully.
SQL code
--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;
--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

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

Oracle Custom Functions 1