Release 10.1A: OpenEdge Data Management:
SQL Reference

> ## CREATE PROCEDURE

> 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

> ``parameter_decl``

> This is the syntax for parameter_decl.

> Syntax
>      { IN | OUT | INOUT } parameter_name  data_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 following 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_name data_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, as shown in the following example:

>     IMPORT
>           java_import_clause

> Specifies standard Java classes to import. The `IMPORT` keyword must be uppercase and on a separate line, as shown in the following example:

>     BEGIN
>           java_snippet
>     END

> The body of the stored procedure. 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.

> Note: The `BEGIN` and `END` keywords must be uppercase and on separate lines.

> Example

>     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

> Note: See OpenEdge Data Management: SQL Development for more information on using the `CREATE` statement and stored procedures.

> ##### Authorization

> Must have `DBA` privilege, `RESOURCE` privilege, or ownership of procedure.

> ##### Related statements

> CALL, DROP PROCEDURE

Copyright © 2005 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095