Oracle function Usage (this article is from Baidu Library)

　　

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

The syntax for the 1.function function is as follows:

Create or Replace function function_name (

ARGU1 [Mode1] datatype1,--Defining parameter variables

ARGU2 [Mode2] datatype2--Defining parameter variables

) return datatype--defines the type of data returned

Is

Begin

End

Execute VAR v1 varchar2 (Exec:v1:=function_name)

2. Definition without any parameters

Create or Replace function Get_user

return VARCHAR2

Is

Result VARCHAR2 (50); --Defining variables

Begin

Select username into Result from user_users;

return (Result); --Return value

End Get_user;

3. 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

Execution: sql> var sal number sql> exec:sal:=get_sal (' Scott ');

4. 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

Execution: sql> var job varchar2 (sql> var dname varchar2) sql> exec:d name:=get_info (' SCOTT ',: Job)

5. Examples of function calls

Create or Replace function f_sys_getseqid (

V_seqname in VARCHAR2,

V_provincecode in VARCHAR2--province code)

Return VARCHAR2 is

Iv_date VARCHAR2 (8);

Iv_seqname VARCHAR2 (50);

Iv_sqlstr VARCHAR2 (200);

Iv_seq VARCHAR2 (8);

Iv_seqid VARCHAR2 (16);

BEGIN

Iv_seqname: = LOWER (TRIM (v_seqname));

Iv_sqlstr: = ' SELECT ' | | iv_seqname| | '. Nextval from DUAL ';

Execute IMMEDIATE iv_sqlstr into iv_seq;--execute a dynamic SQL statement that executes a similar set of statements

IF v_seqname = ' seq_funcrole_id ' then iv_seqid:= ' ESS ' | | Lpad (iv_seq,5, ' 0 ');

ELSE

SELECT substrb (v_provincecode,1,2) | | To_char (sysdate, ' YYMMDD ') into the iv_date from DUAL;

iv_seqid:= Iv_date | | Lpad (iv_seq,8, ' 0 ');

END IF;

RETURN iv_seqid;

EXCEPTION when OTHERS then RETURN NULL;

END;

The method is called as follows:

SELECT To_number (f_sys_getseqid (' seq_termtrade_id ', V_province_code)) into v_batch_id from DUAL;

Description of the EXECUTE immediate: Executes the dynamic SQL statement.

Oracle function Usage (this article is from Baidu Library)