Creates a stored procedure. Stored procedures contain a Java code snippet that is processed into a Java class definition and stored in the database in text and compiled form. SQL applications invoke stored procedures through the SQL CALL statement or the procedure-calling mechanisms of ODBC and JDBC.
Syntax
CREATE PROCEDURE [owner_name.]procname ( [parameter_decl[ , ...]] )
[ RESULT ( column_name data_type[ , ...] ) ] [ IMPORT
java_import_clause] BEGIN
java_snippet END
Parameters
parameter_decl
This is the syntax for parameter_decl:
{ IN | OUT | INOUT }parameter_namedata_type
owner_name
Specifies the owner of the procedure. If the name is different from the user name of the user executing the statement, then the user must have DBA privileges.
procname
Names the stored procedure. DROP PROCEDURE statements specify the procedure name defined here. SQL also uses procname in the name of the Java class that it creates from the Java snippet.
IN | OUT | INOUT
Specifies whether the parameter declaration is input, output, or both.
Calling applications pass values for input parameters in the CALL statement or CALL escape sequence.
Stored procedures assign values to output parameters as part of their processing.
INOUT parameters have both a value passed in and receive a new value during procedure processing.
parameter_name data_type
Names a parameter and associates an SQL data type with it. The data type must be one supported by OpenEdge.
RESULT ( column_namedata_type[ , ...] )
Specifies columns in the result set the procedure returns. If the CREATE PROCEDURE statement includes this clause, the Java snippet must explicitly insert rows into the result set using the Java class SQLResultSet.
Note that the column_name argument is not used in the body of the stored procedure. Instead, methods of the Java classes refer to columns in the result set by ordinal number, not by name. The IMPORT keyword must be uppercase and on a separate line. The body is a sequence of Java statements between the BEGIN and END keywords. The Java statements become a method in a class that SQL creates and submits to the Java compiler. The BEGIN and END keywords must be uppercase and on separate lines.
Notes
SQL limits recursive invocation of a stored procedure to five levels.
See OpenEdge Data Management: SQL Development for more information on using the CREATE statement and stored procedures.
Example
The following example illustrates the use of the CREATE PROCEDURE statement:
CREATE PROCEDURE get_sal ()
IMPORT
import java.math.*;
BEGIN
StringBuffer ename = new StringBuffer (20) ;
BigDecimal esal = new BigDecimal (2) ;
SQLCursor empcursor = new SQLCursor (
"SELECT name, sal FROM emp " ) ;
empcursor.open () ;
empcursor.fetch ();
while (empcursor.found ())
{ename = (StringBuffer) empcursor.getValue (1, CHAR);
esal = (BigDecimal) empcursor.getValue (2, NUMERIC);
// do something with the values here
}empcursor.close () ;
END
