AntDB-Oracle Compatibility Developer’s Manual P4–8

Delete Function

The DROP FUNCTION command removes a function from the database.

DROP FUNCTION name;

name is the name of the function to be deleted

In the following example, we delete a function that has already been created:

DROP FUNCTION simple_function;

See the DROP FUNCTION command for details.

Parameters of Stored Procedures and Functions

One of the important functions in using stored procedures and functions is for the application to pass values to the procedure or function and to receive the returned data from the procedure and function. This function can be accomplished through the use of parameters.

Parameters are declared in parentheses after the procedure or function. Parameters defined in a procedure or function are called formal parameters. When a procedure or function is called, the application provides the actual value to the called function/storage procedure and gets the return result of the called function/storage procedure through a variable. When a procedure or function is called, the values and variables provided by the application calling them are called actual parameters.

The following is the format of a formal parameter declaration:

(name [ IN | OUT | IN OUT ] data_type [ DEFAULT value ])

name is the identifier assigned to the formal parameter. If the IN clause is specified, the parameter will receive input data for use by the procedure or function. We can use the default value to initialize the input parameter. If the OUT clause is specified, the parameter returns the results of execution in the procedure or function to the application that called them. If the IN OUT clause is specified, then the parameter can be used as both an input and an output parameter. If the IN, OUT, and IN OUT clauses are omitted, then the parameter is specified as an input parameter by default. The use of the parameter is determined by IN, OUT, IN OUT. data_type defines the data type of the parameter. If no value is specified for the actual parameter during the call, then the value of the parameter VALUE is assigned to the input parameter as the default value.

The following is an example of a procedure with parameters:

\set PLSQL_MODE ON
CREATE OR REPLACE PROCEDURE emp_query (
p_deptno    IN  NUMBER,
p_empno IN OUT NUMBER,
p_ename IN OUT VARCHAR2,
p_job   OUT VARCHAR2,
p_hiredate  OUT DATE,
p_sal   OUT NUMBER
)
IS
BEGIN
SELECT empno, ename, job, hiredate, sal
INTO p_empno, p_ename, p_job, p_hiredate, p_sal
FROM emp
WHERE deptno = p_deptno
AND (empno = p_empno
OR  ename = UPPER(p_ename));
END;
/

In this example, p_deptno is the input formal parameter, p_empno and p_ename are the IN OUT formal parameters. p_job, p_hiredate and p_sal are output formal parameters.

Note: In this previous example, the maximum length of the VARCHAR2 type parameter and the range of values and decimal places of the NUMBER type parameter are not specified. In the actual parameter declaration, it is not allowed to specify the length, precision, and numeric range of the parameter or other constraints. These constraints are actually inherited automatically from the actual parameters used when the procedure or function is called.

Other applications can call the emp_query procedure and pass it the actual parameters. The following is an example that demonstrates how to do this by calling the emp_query procedure.

As shown below, the fourth, fifth, and sixth arguments are of type OUT, which need not be passed in when called, and only the first three arguments are written. This is not compatible with Oracle.

select emp_query(30, 7900, '');

Note that in AntDB, when calling functions or stored procedures, parameters of type out cannot be directly used as function inputs. In the above example, v_deptno, v_empno, v_ename are IN type or IN OUT type parameters, while v_job, v_hiredate and v_sal are OUT type parameters. Therefore, when calling the function, only the first three parameters are passed. This is not compatible with Oracle.

The output shows the following.

                  EMP_QUERY                   
-----------------------------------------------
 (7900,JAMES,CLERK,"1981-12-03 00:00:00",950)
(1 row)