Progress
SQL-92
Guide and Reference


CREATE PROCEDURE Statement

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

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 of the supported data types described in the "Data Types" section in SQL-92 Language Elements."

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.

IMPORT  
      java_import_clause 

Specifies standard Java classes to import. The IMPORT keyword must be uppercase 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 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 new_sal ( 
     IN  deptnum     INTEGER, 
     IN  pct_incr    INTEGER, 
) 
RESULT ( 
     empname CHAR(20), 
     oldsal   NUMERIC, 
     newsal   NUMERIC 
) 
BEGIN 
     String ename = new String (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 ()) 
          { 
               ename = (StringBugger) empcursor.getValue (1, CHAR); 
               osal = (BigDecimal) empcursor.getValue (2, NUMERIC); 
               nsal = (BigDecimal) empcursor.getValue (3, NUMERIC); 
  
               SQLResultSet.set (1, ename); 
               SQLResultSet.set (2, osal); 
               SQLResultSet.set (3, nsal) ; 
               SQLResultSet.insert (); 
          } 
     } while (empcursor.found ()) ; 
  
     empcursor.close () ; 
END  

AUTHORIZATION

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

SQL COMPLIANCE

SQL-92, ODBC Core SQL grammar

ENVIRONMENT

Embedded SQL, interactive SQL, ODBC applications, JDBC applications

RELATED STATEMENTS

CALL Statement, DROP PROCEDURE Statement


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