SQL Reference Guide

CREATE PROCEDURE

Description

Creates a stored procedure. Stored procedures contain a Java code “snippet” that is processed by FairCom DB SQL into a Java class definition and stored in the database in text and compiled form. FairCom DB SQL applications invoke stored procedures through the SQL CALL statement or the procedure-calling mechanisms of ODBC and JDBC.

For more detail on creating and using stored procedures, see the FairCom DB SQL Guide to Java Stored Procedures and Triggers.

Syntax

CREATE PROCEDURE [ IF NOT EXISTS ] [ owner_name. ] procname

( [ parameter_decl [ , ... ] ) ]

[ RESULT ( column_name data_type [ , ... ] ) ]

[ IMPORT

java_import_clause ]

BEGIN

java_snippet

END

parameter_decl ::

{ IN | OUT | INOUT } parameter_name data_type

Arguments

IF NOT EXISTS

This argument avoids failure by creating the procedure only if a procedure of the same name does not already exist.

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. c-treeSQL also uses procname in the name of the Java class that it creates from the Java snippet.

IN | OUT | INOUT

Specifies whether the 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 both have a value passed in and receive a new value during procedure processing

parameter_name data_type

Names a parameter and associates an FairCom DB SQL data type with it. The data_type must be one of the supported data types described in Data Types.

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 FairCom DB SQL Java class SQLResultSet.

Note that the column_name argument is not used within the stored procedure body. Instead, methods of the FairCom DB SQL Java classes refer to columns in the result set by ordinal number, not by name. (Interactive FairCom DB SQL uses the column names as headers when it displays procedure result sets.)

IMPORT
java_import_clause

Specifies standard Java classes to import. The IMPORT keyword must be upper case and on a separate line.

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 FairCom DB SQL creates and submits to the Java compiler.

The BEGIN and END keywords must be upper case and on separate lines. You cannot follow the END keyword with a semicolon.

Example

CREATE PROCEDURE new_sal (

IN deptnum INTEGER,

IN pct_incr INTEGER,

)

RESULT (

empname CHAR(20),

oldsal NUMERIC,

newsal NUMERIC

)

BEGIN

StringBuffer ename = new StringBuffer (20) ;

BigDecimal osal = new BigDecimal () ;

BigDecimal nsal = new BigDecimal () ;

SQLCursor empcursor = new SQLCursor (

"SELECT empname, sal, (sal * ( ? /100) + NVL (comm, 0)) total,

FROM emp WHERE deptnum = ? " ) ;

empcursor.setParam (1, pct_incr);

empcursor.setParam (2, deptnum);

empcursor.open () ;

do

{

empcursor.fetch ();

if (empcursor.found ())

{

empcursor.getValue (1, ename);

empcursor.getValue (2, osal);

empcursor.getValue (3, nsal) ;

SQLResultSet.set (1, ename);

SQLResultSet.set (2, osal);

SQLResultSet.set (3, nsal) ;

SQLResultSet.insert ();

}

} while (empcursor.found ()) ;

empcursor.close () ;

END

Authorization

• Users issuing the CREATE PROCEDURE statement must have the DBA privilege or RESOURCE privilege.
• The owner or users with the DBA privilege can execute or drop any stored procedure, and grant the EXECUTE privilege to other users.
• Users must have the DBA or EXECUTE privilege to invoke a stored procedure.
• Users invoking a stored procedure do not need privileges to database objects accessed by the procedure. When a user executes a stored procedure, FairCom DB SQL checks the privileges of the procedure owner, not the procedure user, on any objects that the procedure accesses. This enables a user to execute a procedure successfully even when that user does not have the privileges to directly access objects used by the procedure.

  SQL Compliance	SQL-93, ODBC Core SQL grammar
  Environment	Embedded SQL, interactive SQL, ODBC applications
  Related Statements	CALL, DROP PROCEDURE