Creating, Executing, and Dropping Functions

Creating, Executing ,and Dropping Functions

Creating Functions

A function is provided to create functions. When creating a function, you must end with the following syntax, and the return type must be defined.

END;
/

Successful creation of the function can be confirmed by checking the sys_procedures_ meta table.

In the following example, the function emp_func, which executes an UPDATE statement and a SELECT statement, is created:

iSQL> CREATE OR REPLACE FUNCTION emp_func(f1 IN INTEGER)
RETURN NUMBER
AS
 f2 NUMBER;
BEGIN
 UPDATE employees SET salary = 1000000 WHERE eno = f1;
 SELECT salary INTO f2 FROM employees WHERE eno = f1;
 RETURN f2;
END;
/
Create success.

iSQL> SELECT * FROM system_.sys_procedures_;
USER_ID     PROC_OID             PROC_NAME                                 
-------------------------------------------------------------------------------
OBJECT_TYPE STATUS      PARA_NUM    RETURN_DATA_TYPE RETURN_LANG_ID
--------------------------------------------------------------------------
RETURN_SIZE RETURN_PRECISION RETURN_SCALE PARSE_NO    PARSE_LEN   
------------------------------------------------------------------------
CREATED      LAST_DDL_TIME
------------------------------
.
.
.
2           3300024              INOUTPROC1                                
0           0           3                                   
                                    2           132         
15-SEP-2010  15-SEP-2010  
2           3302344              EMP_FUNC                                  
1           0           1           6           30000       
23          38          0           3           209         
15-SEP-2010  15-SEP-2010  
36 rows selected.

Executing Functions

Functions can be executed to simultaneously execute multiple queries. If the function to be executed has parameters, as many variables as there are functions must be declared before the function is executed. Additionally, a variable for saving the result of the function must also be defined.

The following is an example of executing the function emp_func:

iSQL> VAR eno INTEGER
iSQL> VAR ret NUMBER
iSQL> EXEC :eno := 11;
Execute success.
iSQL> EXEC :ret := emp_func(:eno);
Execute success.
iSQL> SELECT eno, salary FROM employees WHERE eno = 11;
ENO         SALARY      
---------------------------
11          1000000     
1 row selected.

Dropping Functions

The DROP FUNCTION statement is used to drop functions.

In the following example, the function emp_func is deleted:

iSQL> DROP FUNCTION emp_func;
Drop success