2. SQL Statements for Managing Stored Procedures#
Overview#
The SQL Statements that are used with Stored Procedures#
This table lists the DDL statements that are used to create and manage stored procedures, stored functions, and typesets. The descriptions of the CREATE TYPESET and DROP TYPESET statements can be found in Chapter7: Typesets of this manual.
| Statement Type | Statement | Description |
|---|---|---|
| Creationg | CREATE [OR REPLACE] PROCEDURE statement | This SQL statement is used to create a new stored procedure or change the definition of an existing stored procedure. |
| CREATE [OR REPLACE] FUNCTION statement | This SQL statement is used to create a new stored function or change the definition of an existing stored function. | |
| CREATE [OR REPLACE] TYPESET statement | This SQL statement is used to create or alter a typeset. | |
| Modification | ALTER PROCEDURE statement | This SQL statement is used to alter the stored procedure state by recompiling the stored procedure. |
| ALTER FUNCTION statement | This SQL statement is used to alter the stored function state by recompiling the stored function. | |
| Removal | DROP PROCEDURE statement | This SQL statement is used to remove a stored procedure. |
| DROP FUNCTION statement | This SQL statement is used to remove a stored function. | |
| DROP TYPESET statement | This SQL statement is used to remove a TYPESET. | |
| Execution | EXECUTE statement | This SQL statement is used to EXECUTE a stored procedure or stored function. |
| function_name | Stored functions can be referenced by name within SQL statements. |
Data Types#
The following data types are supported for use with stored procedures:
-
SQL Data Types
-
BOOLEAN Types
-
FILE_TYPE
FILE_TYPE can be used only in stored procedures, and is used to control files in stored procedures. For more information, please refer to Chapter 11: File Control in this manual. -
User-defined Types
User-defined types can be used only in stored procedures: records and associative arrays are supported for use as user-defined types. For more information, please refer to Chapter 6: User-Defined Types in this manual.
SQL Data Types#
Data types available for use in SQL statements can be used in both stored procedures and stored functions. For more detailed information on each data type, please refer to "Data Types" in General Reference.
The SQL data types listed in the following table have different maximum sizes in SQL statements and PSMs (stored procedures, stored functions).
| Data Type | Maximum Size in SQL Statements | Maximum Size in PSMs |
|---|---|---|
| CHAR(M) | 32000 | 65534 |
| VARCHAR(M) | 32000 | 65534 |
| NCHAR(M) | 16000 (UTF-16) 10666 (UTF-8) | 32766 (UTF-16) 21843 (UTF-8) |
| NVARCHAR(M) | 16000 (UTF-16) 10666 (UTF-8) | 32766 (UTF-16) 21843 (UTF-8) |
| BLOB | 2GB - 1 | 100MB Determined by the LOB_OBJECT_BUFFER_SIZE property (default value: 32KB) |
| CLOB | 2GB - 1 | 100MB Determined by the LOB_OBJECT_BUFFER_SIZE property (default value: 32KB) |
On omission, the size of the CHAR, VARCHAR, NCHAR, and NVARCHAR types is 1 by default.
In case the data type of parameters or return values is set to CHAR, NCHAR, NVARCHAR or VARCHAR in the stored procedures or functions, the size of data type is set to the specified size of properties as follows:
-
PSM_CHAR_DEFAULT_PRECISION
-
PSM_NCHAR_UTF8_DEFAULT_PRECISION
-
PSM_NCHAR_UTF16_DEFAULT_PRECISION
-
PSM_NVARCHAR_UTF8_DEFAULT_PRECISION
-
PSM_NVARCHAR_UTF16_DEFAULT_PRECISION
-
PSM_VARCHAR_DEFAULT_PRECISION
Refer to the General Reference for in-depth information on each property.
BOOLEAN Types#
A BOOLEAN type is only available for use in stored procedures or stored functions, and can only have the value, TRUE, FALSE or NULL.
A BOOLEAN variable can be declared as below.
variable_name BOOLEAN;
As the BOOLEAN type is not compatible with any other SQL data type, it has the following restrictions.
- A BOOLEAN value cannot be input to a table column.
- A table column value cannot be fetched into a BOOLEAN variable.
- A stored function or built-in function that returns a BOOLEAN type is not available for use in a SQL statement.
- A BOOLEAN value cannot be passed as the argument of an output function (e.g., PRINT, PUT, etc).
The BOOLEAN type can be used as below.
done BOOLEAN;
...
done := TRUE;
done := FALSE;
done := NULL;
...
IF done = TRUE THEN
...
IF done = FALSE THEN
...
IF done THEN
...
IF done is NULL THEN
...
CREATE PROCEDURE#
Syntax#
create_procedure::=
parameter_declaration::=
invoker_rights_clause::=
Purpose#
This statement creates a new stored procedure, or replaces an existing stored procedure with a new stored procedure.
parameter_declaration#
Arguments may be omitted. If an argument is specified, the name, data type, and input / output distinction must be specified. Available I / O classification value is one of the following three, and defaults to IN when omitted. If the argument is OUT or INOUT, the DEFAULT expression cannot be defined.
-
IN: an input parameter for which the value is specified when the procedure is called
-
OUT: an output parameter, which returns an output value after the procedure has executed
-
INOUT: an input/output parameter, for which the value is specified when calling the procedure, and which also returns an output value, typically after some operations are performed thereon
If the parameter type is omitted, IN is the default type. If the parameter type is OUT or INOUT, DEFAULT expression cannot be used.
When a stored procedure is executed, values are passed to the stored procedure using IN parameters, and the procedure returns values to the calling routine using OUT parameters.
An IN parameter is handled as a constant within a stored procedure. This means that a value cannot be assigned to an IN parameter within a stored procedure. Additionally, an IN parameter cannot be used in an INTO clause of a SELECT statement.
There are two methods of specifying parameters. The first method would be substituting values, and another one is substituting reference values by using NOCOPY option, which only supports the ASSOCIATIVE ARAY type.
A parameter can have a default value. If no value is passed to a procedure for a parameter that has a default value, this default value will be used.
invoker_rights_clause#
When executing a procedure, users can specify whether to refer to an object with the CREATE (DEFINER) permission or to execute the (CURRENT_USER) permission.
-
AUTHID CURRENT_USER
This executes a procedure by referencing an object owned by the user. -
AUTHID DEFINER
This executes by the user who created the procedure (DEFINER) by referring to the object of the procedure constructor.
declaration_section#
Please refer to Declaring Local Variables in Chapter 3 of this manual.
data_type#
Please refer to Declaring Local Variables in Chapter 3 of this manual.
Exception Handler#
Please refer to Exception Handlers in this in Chapter 9 of this manual.
Executing the CREATE PROCEDURE Statement#
A stored procedure creation statement can be written in advance in a text editor and pasted into iSQL, or can be entered line-by-line directly using iSQL.
Use a semicolon (";") at the end of SQL statements, stored procedure control flow statements, and blocks ("END").
On the line following the last END statement, be sure to use a slash ("/") to indicate the end of the procedure creation statement when using iSQL. The procedure creation statement is now ready for execution. When the CREATE PROCEDURE statement is executed, if there are no compile errors and the block is successfully compiled, the message "Create success" is output. The elements that are used in the body of a stored procedure, namely blocks, control flow statements, cursors, and exception handlers, will be described individually in subsequent chapters.
Example#
Example 1 (Using IN parameters)#
CREATE TABLE t1 (i1 INTEGER UNIQUE, i2 INTEGER, i3 INTEGER);
INSERT INTO t1 VALUES (1,1,1);
INSERT INTO t1 VALUES (2,2,2);
INSERT INTO t1 VALUES (3,3,3);
INSERT INTO t1 VALUES (4,4,4);
INSERT INTO t1 VALUES (5,5,5);
SELECT * FROM t1;
CREATE OR REPLACE PROCEDURE proc1
(p1 IN INTEGER, p2 IN INTEGER, p3 IN INTEGER)
AS
v1 INTEGER;
v2 t1.i2%type;
v3 INTEGER;
BEGIN
SELECT *
INTO v1, v2, v3
FROM t1
WHERE i1 = p1 AND i2 = p2 AND i3 = p3;
IF v1 = 1 AND v2 = 1 AND v3 = 1 THEN
UPDATE t1 SET i2 = 7 WHERE i1 = v1;
ELSIF v1 = 2 AND v2 = 2 AND v3 = 2 THEN
UPDATE t1 SET i2 = 7 WHERE i1 = v1;
ELSIF v1 = 3 AND v2 = 3 AND v3 = 3 THEN
UPDATE t1 SET i2 = 7 WHERE i1 = v1;
ELSIF v1 = 4 AND v2 = 4 AND v3 = 4 THEN
UPDATE t1 SET i2 = 7 WHERE i1 = v1;
ELSE
DELETE FROM t1;
END IF;
INSERT INTO t1 VALUES (p1+10, p2+10, p3+10);
END;
/
iSQL> EXEC proc1 (2,2,2);
Execute success.
iSQL> SELECT * FROM t1;
T1.I1 T1.I2 T1.I3
----------------------------------------
1 1 1
3 3 3
4 4 4
5 5 5
2 7 2
12 12 12
6 rows selected.
Example 2 (using parameters with default values)#
CREATE TABLE t1 (i1 INTEGER, i2 INTEGER, i3 INTEGER);
CREATE OR REPLACE PROCEDURE proc1
(p1 IN INTEGER DEFAULT 1, p2 IN INTEGER DEFAULT 1, p3 IN INTEGER DEFAULT 1)
AS
BEGIN
INSERT INTO t1 VALUES (p1, p2, p3);
END;
/
EXEC proc1;
SELECT * FROM t1;
EXEC proc1(2);
SELECT * FROM t1;
EXEC proc1(3,3);
SELECT * FROM t1;
EXEC proc1(4,4,4);
iSQL> SELECT * FROM t1;
T1.I1 T1.I2 T1.I3
----------------------------------------
1 1 1
2 1 1
3 3 1
4 4 4
4 rows selected.
Example 3#
CREATE OR REPLACE PROCEDURE proc1
(emp_id INTEGER, amount NUMBER(10,2))
AS
BEGIN
UPDATE employees SET salary = salary + amount
WHERE eno = emp_id;
END;
/
iSQL> EXEC proc1(15, '250');
Execute success.
iSQL> SELECT * FROM employees WHERE eno=15;
ENO E_LASTNAME E_FIRSTNAME EMP_JOB
------------------------------------------------------------------------------
EMP_TEL DNO SALARY SEX BIRTH JOIN_DATE STATUS
-----------------------------------------------------------------------------------
15 Davenport Jason webmaster
0119556884 1003 1250 M 901212 H
1 row selected.
Example 4 (Using OUT and IN/OUT parameters)#
CREATE TABLE t4(i1 INTEGER, i2 INTEGER);
INSERT INTO t4 VALUES(1,1);
INSERT INTO t4 VALUES(1,1);
INSERT INTO t4 VALUES(1,1);
INSERT INTO t4 VALUES(1,1);
INSERT INTO t4 VALUES(1,1);
CREATE OR REPLACE PROCEDURE proc1(a1 OUT INTEGER, a2 IN OUT INTEGER)
AS
BEGIN
SELECT COUNT(*) INTO a1 FROM t4 WHERE i2 = a2;
END;
/
iSQL> VAR t3 INTEGER;
iSQL> VAR t4 INTEGER;
iSQL> EXEC :t4 := 1;
Execute success.
iSQL> EXEC proc1(:t3, :t4);
Execute success.
iSQL> PRINT t3;
NAME TYPE VALUE
-----------------------------------------------
T3 INTEGER 5
Example 5#
CREATE OR REPLACE PROCEDURE proc1(p1 INTEGER, p2 IN OUT INTEGER, p3 OUT INTEGER)
AS
BEGIN
p2 := p1;
p3 := p1 + 100;
END;
/
iSQL> VAR v1 INTEGER;
iSQL> VAR v2 INTEGER;
iSQL> VAR v3 INTEGER;
iSQL> EXEC :v1 := 3;
Execute success.
iSQL> EXEC proc1(:v1, :v2, :v3);
Execute success.
iSQL> PRINT VAR;
[ HOST VARIABLE ]
-----------------------------------------------
NAME TYPE VALUE
-----------------------------------------------
V1 INTEGER 3
V2 INTEGER 3
V3 INTEGER 103
Example 6 (Using an IN/OUT parameter)#
CREATE TABLE t3(i1 INTEGER);
INSERT INTO t3 VALUES(1);
INSERT INTO t3 VALUES(1);
INSERT INTO t3 VALUES(1);
CREATE OR REPLACE PROCEDURE proc1(a1 IN OUT INTEGER)
AS
BEGIN
SELECT COUNT(*) INTO a1 FROM t3 WHERE i1 = a1;
END;
/
iSQL> VAR p1 INTEGER;
iSQL> EXEC :p1 := 1;
Execute success.
iSQL> EXEC proc1(:p1);
Execute success.
iSQL> PRINT p1;
NAME TYPE VALUE
-----------------------------------------------
P1 INTEGER 3
Example 7#
CREATE OR REPLACE PROCEDURE proc1(p1 INTEGER, p2 IN OUT INTEGER, p3 OUT INTEGER)
AS
BEGIN
p2 := p1 + p2;
p3 := p1 + 100;
END;
/
iSQL> VAR v1 INTEGER;
iSQL> VAR v3 INTEGER;
iSQL> EXEC :v1 := 3;
Execute success.
iSQL> EXEC :v2 := 5;
Execute success.
iSQL> EXEC proc1(:v1, :v2, :v3);
Execute success.
iSQL> PRINT VAR;
[ HOST VARIABLE ]
-----------------------------------------------
NAME TYPE VALUE
-----------------------------------------------
V1 INTEGER 3
V2 INTEGER 8
V3 INTEGER 103
Example 8(Using NOCOPU option in IN/OUT parameters)#
iSQL> CREATE TYPESET TYPE1 AS
TYPE ARR_TYPE IS TABLE OF INTEGER INDEX BY INTEGER;
END;
/
Create success.
iSQL> CREATE OR REPLACE PROCEDURE PRINT_PROC( P1 IN NOCOPY TYPE1.ARR_TYPE )
AS
BEGIN
FOR I IN P1.FIRST() .. P1.LAST() LOOP
PRINTLN(P1[I]);
END LOOP;
END;
/
Create success.
iSQL> CREATE OR REPLACE PROCEDURE PROC1
AS
VAR1 TYPE1.ARR_TYPE;
BEGIN
FOR I IN 1 .. 10 LOOP
VAR1[I] := I;
END LOOP;
PRINT_PROC(VAR1);
END;
/
Create success.
iSQL> EXEC PROC1;
1
2
3
4
5
6
7
8
9
10
Execute success.
Example 9 (AUTHID CURRENT_USER)#
create object: user1
iSQL> connect user1/user1;
Connect success.
iSQL> create table t1( c1 integer );
Create success.
iSQL> insert into t1 values ( 1 );
1 row inserted.
iSQL> create or replace procedure proc1 authid current_user as
var1 integer;
begin
select c1 into var1 from t1;
println( var1 );
end;
/
Create success.
iSQL> select proc_name , object_type , authid
from system_.sys_procedures_
where proc_name ='PROC1';
PROC_NAME
----------------------------------------------------
OBJECT_TYPE AUTHID
---------------------------------------------------
PROC1
0 1
1 row selected.
iSQL> connect user2/user2;
Connect success.
iSQL> create table t1( c1 integer );
Create success.
iSQL> insert into t1 values ( 100 );
1 row inserted.
create object: user2
iSQL> connect user2/user2;
Connect success.
iSQL> create table t1( c1 integer );
Create success.
iSQL> insert into t1 values ( 100 );
1 row inserted.
execute procedure: user1
iSQL> exec proc1;
1
Execute success.
execute procedure: user2
iSQL> exec user1.proc1;
100
Execute success.
Example 10 (AUTHID DEFINER)#
create object: user1iSQL> connect user1/user1;
Connect success.
iSQL> create table t1( c1 integer );
Create success.
iSQL> insert into t1 values ( 1 );
1 row inserted.
iSQL> create or replace procedure proc1 authid definer as
var1 integer;
begin
select c1 into var1 from t1;
println( var1 );
end;
/
Create success.
iSQL> select proc_name , object_type , authid
from system_.sys_procedures_
where proc_name ='PROC1';
PROC_NAME
----------------------------------------------------------------
OBJECT_TYPE AUTHID
---------------------------
PROC1
0 0
1 row selected.
iSQL> connect user2/user2;
Connect success.
iSQL> create table t1( c1 integer );
Create success.
iSQL> insert into t1 values ( 100 );
1 row inserted.
create object: user2
iSQL> connect user2/user2;
Connect success.
iSQL> create table t1( c1 integer );
Create success.
iSQL> insert into t1 values ( 100 );
1 row inserted.
execute procedure: user1
iSQL> exec proc1;
1
Execute success.
execute procedure: user2
iSQL> exec user1.proc1;
1
Execute success.
ALTER PROCEDURE#
Syntax#
Purpose#
A stored procedure can access various database objects, such as tables, views, and sequences, and can also call other stored procedures and stored functions. After a procedure is created, if any of these objects are altered or changed, the stored procedure can enter what is known as an invalid state.
For example, suppose that an index that existed when a stored procedure was created is later deleted. In this case, because the execution plan for a SQL statement in the stored procedure used the index to access a table, it will become impossible to access the table using the stored procedure from the moment the index is deleted.
When an invalid procedure is called, it is automatically and immediately recompiled by the database. However, compiling at run time in this way can cause significant performance issues in some systems. Therefore, it is recommended that procedures be recompiled when they enter an invalid state.
The ALTER PROCEDURE statement is used to explicitly recompile a stored procedure under these circumstances.
Example#
Example 1#
CREATE TABLE t1 (i1 NUMBER, i2 VARCHAR(10), i3 DATE);
CREATE OR REPLACE PROCEDURE proc1
(p1 IN NUMBER, p2 IN VARCHAR(10), p3 IN DATE)
AS
BEGIN
IF p1 > 0 then
INSERT INTO t1 VALUES (p1, p2, p3);
END IF;
END;
/
iSQL> EXECUTE proc1 (1, 'seoul', '20-JUN-2002');
Execute success.
iSQL> EXECUTE proc1 (-3, 'daegu', '21-APR-2002');
Execute success.
iSQL> SELECT * FROM t1;
T1.I1 T1.I2 T1.I3
-----------------------------------------------
1 seoul 20-JUN-2002
1 row selected.
Example 2#
CREATE TABLE t1 (i1 NUMBER, i2 VARCHAR(10), i3 DATE DEFAULT SYSDATE);
ALTER PROCEDURE proc1 COMPILE;
iSQL> EXECUTE proc1 (2, 'incheon', SYSDATE);
Execute success.
iSQL> SELECT * FROM t1;
T1.I1 T1.I2 T1.I3
-----------------------------------------------
2 incheon 28-DEC-2010
1 row selected.
DROP PROCEDURE#
Syntax#
Purpose#
This statement removes a stored procedure from the database.
Note that this statement will execute successfully even if there are other stored procedures or stored functions that reference the procedure to be dropped.
When a stored procedure or stored function attempts to call a stored procedure or stored function that has already been dropped, an error is returned.
Example#
DROP PROCEDURE proc1;
EXECUTE#
Syntax#
execute_procedure_statement::=#
execute_function_statement::=#
parameter_notation::=#
Purpose#
This statement is used to execute a stored procedure or stored function.
parameter_notation#
The way to deliver a value to parameter is as follows:
-
Position-based: By default, the values are entered according to the position of the defined parameter
-
Name-based : The values are entered the name of the defined parameter and the value after the arrow (=>). Values can be delivered in any order of parameters.
-
Mixed: Position-based and name-based approaches can be used together. However, the position-based delivery method must be entered first.
Example#
<Query>
CREATE OR REPLACE PROCEDURE proc1(eid INTEGER, amount NUMBER(10,2))
AS
current_salary NUMBER(10,2);
BEGIN
SELECT salary
INTO current_salary
FROM employees
WHERE eno = eid;
UPDATE employees
SET salary = salary + amount
WHERE eno = eid;
END;
/
iSQL> SELECT * FROM employees WHERE eno = 15;
ENO E_LASTNAME E_FIRSTNAME EMP_JOB
------------------------------------------------------------------------------
EMP_TEL DNO SALARY SEX BIRTH JOIN_DATE STATUS
-----------------------------------------------------------------------------------
15 Davenport Jason webmaster
0119556884 1003 501000 M 901212 H
1 row selected.
iSQL> EXEC proc1(15, 333333);
Execute success.
iSQL> SELECT * FROM employees WHERE eno = 15;
ENO E_LASTNAME E_FIRSTNAME EMP_JOB
------------------------------------------------------------------------------
EMP_TEL DNO SALARY SEX BIRTH JOIN_DATE STATUS
-----------------------------------------------------------------------------------
15 Davenport Jason webmaster
0119556884 1003 834333 M 901212 H
1 row selected.
<질의>
iSQL> EXEC proc1(amount => 333333, eid => 15);
Execute success.
iSQL> SELECT * FROM employees WHERE eno = 15;
ENO E_LASTNAME E_FIRSTNAME EMP_JOB
------------------------------------------------------------------------------
EMP_TEL DNO SALARY SEX BIRTH JOIN_DATE STATUS
-----------------------------------------------------------------------------------
15 Davenport Jason webmaster
0119556884 1003 834333 M 901212 H
1 row selected.
CREATE FUNCTION#
Syntax#
create_function::=
parameter_declaration::=
invoker_rights_clause::=
Purpose#
This statement is used to create a new stored function or replace an existing function with a new function.
parameter_declaration#
Refer to "parameter_declaration" in the explanation of the CREATE PROCEDURE
RETURN data_type#
Unlike stored procedures, stored functions return a single value after they are executed. Therefore, the data type of the return value must be specified.
DETERMINISTIC#
This function indicates that a function called by an identical parameter value always returns an identical result. Functions marked DETERMINSTIC can be used in function-based indexes along with Check Constraint. On omission, a non-deterministic function is marked.
invoker_rights_clause#
When executing a function, it can be specified whether to refer to the object by the constructor (DEFINER) permission or the executer (CURRENT_USER) permission. If this clause is commited, the fucntion executes with constructor privileges.
-
AUTHID CURRENT_USER
This executes a function by referring to an object owned by the user. -
AUTHID DEFINER
This executes a function with DEFINER permission by referring to the object of the funciton constructor.
Declaration Section#
Please refer to Declaring Local Variables in Chapter 3 of this manual.
Data Types#
Please refer to Declaring Local Variables in Chapter 3 of this manual.
Exception Handler#
Please refer to Chapter 9: Exception Handlers in this manual
Executing the CREATE FUNCTION Statement#
Refer to the corresponding description in Executing the CREATE PROCEDURE Statement.
Example#
Example 1#
CREATE TABLE t1(
seq_no INTEGER,
user_id VARCHAR(9),
rate NUMBER,
start_date DATE,
end_date DATE);
INSERT INTO t1 VALUES(0, '000000500', 200.50, '23-May-2002', '23-Apr-2002');
INSERT INTO t1 VALUES(0, '000000501', 190, '23-Nov-2002', '23-Dec-2002');
INSERT INTO t1 VALUES(0, '000000523', 100, '12-Dec-2001', '12-Jan-2001');
INSERT INTO t1 VALUES(0, '000000532', 100, '11-Dec-2001', '11-Jan-2002');
INSERT INTO t1(seq_no, user_id, start_date, end_date) VALUES(0, '000000524', '30-Oct-2001', '30-Nov-2001');
INSERT INTO t1 VALUES(0, '000000524', 200.50, '30-Apr-2002', '30-May-2002');
INSERT INTO t1 VALUES(0, '000000524', 200.50, '30-Apr-2002', '30-May-2002');
INSERT INTO t1 VALUES(1, '000000524', 100, '30-Apr-2002', '30-May-2002');
INSERT INTO t1 VALUES(1, '000000524', 115.0, '19-Jan-2002', '19-Mar-2002');
INSERT INTO t1 VALUES(0, '000000502', 120.0, '27-Jan-2002', '27-Feb-2002');
INSERT INTO t1 VALUES(1, '000000504', 150.0, '26-Nov-2001', '26-Dec-2001');
iSQL> SELECT * FROM t1;
T1.SEQ_NO T1.USER_ID T1.RATE T1.START_DATE
------------------------------------------------------------
T1.END_DATE
-----------------------
0 000000500 200.5 2002/05/23 00:00:00
2002/04/23 00:00:00
0 000000501 190 2002/11/23 00:00:00
2002/12/23 00:00:00
0 000000523 100 2001/12/12 00:00:00
2001/01/12 00:00:00
0 000000532 100 2001/12/11 00:00:00
2002/01/11 00:00:00
0 000000524 2001/10/30 00:00:00
2001/11/30 00:00:00
0 000000524 200.5 2002/04/30 00:00:00
2002/05/30 00:00:00
0 000000524 200.5 2002/04/30 00:00:00
2002/05/30 00:00:00
1 000000524 100 2002/04/30 00:00:00
2002/05/30 00:00:00
1 000000524 115 2002/01/19 00:00:00
2002/03/19 00:00:00
0 000000502 120 2002/01/27 00:00:00
2002/02/27 00:00:00
1 000000504 150 2001/11/26 00:00:00
2001/12/26 00:00:00
11 rows selected.
CREATE OR REPLACE FUNCTION get_rate
(p1 IN CHAR(30), p2 IN CHAR(30), p3 IN VARCHAR(9))
RETURN NUMBER
AS
v_rate NUMBER;
BEGIN
SELECT NVL(SUM(rate), 0)
INTO v_rate
FROM (SELECT rate
FROM t1
WHERE start_date = TO_DATE(p1)
AND end_date = TO_DATE(p2)
AND user_id = '000000' || p3
AND seq_no = 0);
RETURN v_rate;
END;
/
iSQL> VAR res NUMBER;
iSQL> EXECUTE :res := get_rate('30-Apr-2002', '30-May-2002', '524');
Execute success.
iSQL> PRINT res;
NAME TYPE VALUE
-----------------------------------------------
RES NUMBER 401
Example (AUTHID CURRENT_USER)#
Create object : user1#
iSQL> connect user1/user1;
Connect success.
iSQL> create table t1( c1 integer );
Create success.
iSQL> insert into t1 values ( 1 );
1 row inserted.
iSQL> create or replace function func1 return integer authid current_user as
cursor cur1 is select c1 from t1;
var1 integer;
begin
open cur1;
fetch cur1 into var1;
close cur1;
return var1;
end;
/
Create success.
iSQL> select proc_name , object_type , authid
2 from system_.sys_procedures_
3 where proc_name = 'FUNC1';
PROC_NAME
------------------------------------------------------------------------------------------------------------------------------------
OBJECT_TYPE AUTHID
---------------------------
FUNC1
1 1
1 row selected.
Create object: user2#
iSQL> connect user2/user2;
Connect success.
iSQL> create table t1( c1 integer );
Create success.
iSQL> insert into t1 values ( 100 );
1 row inserted.
Execute function: user1#
iSQL> var a integer;
iSQL> exec :a := func1;
Execute success.
iSQL> print a
NAME TYPE VALUE
-------------------------------------------------------
A INTEGER 1
iSQL> select func1 from dual;
FUNC1
--------------
1
1 row selected.
Execute function: user2#
iSQL> var a integer;
iSQL> exec :a := user1.func1;
Execute success.
iSQL> print a
NAME TYPE VALUE
-------------------------------------------------------
A INTEGER 100
iSQL> select user1.func1 from dual;
USER1.FUNC1
--------------
100
1 row selected.
Example 3 (AUTHID DEFINER)#
Create object: user1#
iSQL> connect user1/user1;
Connect success.
iSQL> create table t1( c1 integer );
Create success.
iSQL> insert into t1 values ( 1 );
1 row inserted.
iSQL> create or replace function func1 return integer authid definer as
cursor cur1 is select c1 from t1;
var1 integer;
begin
open cur1;
fetch cur1 into var1;
close cur1;
return var1;
end;
/
Create success.
iSQL> select proc_name , object_type , authid
from system_.sys_procedures_
where proc_name ='FUNC1';
PROC_NAME
-------------------------------------------------------------------------------
OBJECT_TYPE AUTHID
---------------------------
FUNC1
1 0
1 row selected.
Create object: user2#
iSQL> connect user2/user2;
Connect success.
iSQL> create table t1( c1 integer );
Create success.
iSQL> insert into t1 values ( 100 );
1 row inserted.
Execute function: user1#
iSQL> var a integer;
iSQL> exec :a := func1;
Execute success.
iSQL> print a
NAME TYPE VALUE
-------------------------------------------------------
A INTEGER 1
iSQL> select func1 from dual;
FUNC1
--------------
1
1 row selected.
Execute function: user2#
iSQL> var a integer;
iSQL> exec :a := user1.func1;
Execute success.
iSQL> print a
NAME TYPE VALUE
-------------------------------------------------------
A INTEGER 1
iSQL> select user1.func1 from dual;
USER1.FUNC1
--------------
1
1 row selected.
Note#
For functions used in constraints or function-based indexes, it is impossible to redefine functions since the return values of functions must not be modified. The user should also note that, if an invoked function within the function which the function-based indexes are built on is altered or deleted, DML operations can fail for the indexed table.
ALTER FUNCTION#
Syntax#
Purpose#
As with a stored procedure, a stored function can enter what is known as an invalid state when one or more of the database objects that it references are changed after the function is created.
In such circumstances, the ALTER FUNCTION statement is used to explicitly recompile the stored function and create an execution plan that is valid and optimized.
For a more detailed explanation, please refer to Purpose in the explanation of the ALTER PROCEDURE statement.
Example#
ALTER FUNCTION get_dept_name COMPILE;
DROP FUNCTION#
Syntax#
Purpose#
This statement removes a stored function from the database.
Note that this statement will execute successfully even if there are other stored procedures or stored functions that reference the stored function to be dropped.
When a stored procedure or stored function attempts to call a stored procedure or stored function that has already been dropped, an error is returned.
Example#
DROP FUNCTION get_dept_name;
Note#
The deletion of functions referenced by the constraints or the function-based indexes is impossible.