Creates a method
or query which is exposed as an SQL stored procedure.
Synopsis
CREATE PROCEDURE procname(parameter_list) characteristics language
code_body
CREATE PROC procname(parameter_list) characteristics language
code_body
The
CREATE PROCEDURE statement creates a method or
a query which is automatically exposed as an SQL stored procedure. Stored
procedures are inherited by subclasses.
-
If LANGUAGE SQL, the
code_body must contain
a SELECT statement.
-
If LANGUAGE OBJECTSCRIPT, the
code_body must
call EXECUTE and FETCH methods. It may also call GETINFO, FETCHODBC, FETCHROWS,
and CLOSE methods.
To create a method not exposed as a stored procedure, use the
CREATE METHOD or
CREATE
FUNCTION statement. To create a query not exposed as a stored procedure,
use the
CREATE QUERY statement. These
statements can also be used to create a method or query exposed as a stored
procedure by specifying the PROCEDURE characteristic keyword.
In order to create a procedure, you must have %CREATE_PROCEDURE administrative
privilege, as specified by the
GRANT command.
The name of the method or query to be created as a stored procedure.
This name may be unqualified (StoreName) or qualified by specifying the class
name (Patient.StoreName). The name of a procedure must be followed by parentheses.
A list of parameters used to pass values to the method or query. The
parameter list is enclosed in parentheses, and parameter declarations in the
list are separated by commas. The parentheses are mandatory, even if you specify
no parameters.
Each parameter declaration in the list consists of (in order):
-
A keyword specifying whether the parameter mode is IN (input
value), OUT (output value), or INOUT (modify value).
-
-
The datatype of the parameter.
-
Optional: The keyword DEFAULT followed
by a default value for the parameter, specified as a string enclosed in single
quotes.
Here is an example of a procedure with three parameters:
CREATE PROCEDURE UpdatePay
(IN Salary FLOAT DEFAULT '0',
IN Name VARCHAR(50),
INOUT PayBracket VARCHAR(50) DEFAULT 'NULL')
BEGIN
UPDATE Sample.Person SET Salary = :Salary
WHERE Name=:Name ;
END
Because the method or query is exposed as a stored procedure, it uses
a procedure context handler to pass the procedure context back and forth between
the procedure and its caller (for example, the ODBC server). This procedure
context handler is automatically generated by Caché (as %qHandle:%SQLProcContext)
using the
%sqlcontext object.
You may explicitly declare a procedure context handler as the first
parameter, with the type %SQLProcContext, as follows:
INOUT pHandle %SQLProcContext
The attributes of %SQLProcContext are SQLCode and RowCount. These are
usually set to the SQL variables SQLCODE and %ROWCOUNT, as follows:
SET pHandle.SQLCode=SQLCODE,pHandle.RowCount=%ROWCOUNT
The values of SQLCODE and %ROWCOUNT are automatically set by the execution
of an SQL statement.
Different
characteristics are used for creating a
method than those used to create a query.
A keyword clause specifying the language you are using for
code_body.
Permitted clauses are LANGUAGE OBJECTSCRIPT (for Caché ObjectScript)
or LANGUAGE SQL. If the LANGUAGE clause is omitted, SQL is the default.
The program code for the method or query to be created. You specify
this code in either SQL or Caché ObjectScript. The language used must
match the LANGUAGE clause. However, code specified in Caché ObjectScript
can contain embedded SQL. Caché uses the code you supply to generate
the actual code of the method or query.
SQL program code is prefaced with a BEGIN keyword, followed by the SQL
code itself. At the end of each complete SQL statement, specify a semicolon
(;). A query contains only one SQL statement—a
SELECT statement.
SQL program code concludes with an END keyword.
Caché ObjectScript program code is enclosed within curly braces:
{
code }.
Caché provides additional lines of code when generating the procedure
that embed the SQL in a Caché ObjectScript “wrapper,”
provide a procedure context handler, and handle return values. The following
is an example of this Caché-generated wrapper code:
NEW SQLCODE,%ROWID,%ROWCOUNT,title
&sql( code_body )
QUIT $GET(title)
If the code you specify is OBJECTSCRIPT, you must explicitly define
the “wrapper” (which NEWs variable and uses QUIT exit to return
a value upon completion).
When creating a query, if the code you specify is SQL, it must consist
of a single
SELECT statement. If the code you specify is
OBJECTSCRIPT, it must contain calls to the EXECUTE and FETCH methods provided
by Caché, and may contain CLOSE, FetchRows, FetchODBC, and GetInfo
method calls.
The following example creates a simple query, named PersonStateSP, exposed
as a stored procedure. It declares no parameters and takes default values
for
characteristics and
language:
WRITE !,"Creating a procedure"
&sql(CREATE PROCEDURE PersonStateSP() BEGIN
SELECT Name,Home_State FROM Sample.Person ;
END)
IF SQLCODE=0 { WRITE !,"Created a procedure" }
ELSEIF SQLCODE=-361 { WRITE !,"Procedure already exists" }
ELSE { WRITE !,"SQL error: ",SQLCODE }
You can go into
Caché Explorer and
in the SAMPLES database Classes folder you will find the stored procedure
created by the above example:
SQLUser.procPersonStateSP.
You can use
Caché Explorer to delete this
procedure before rerunning the above program example. You can, of course,
use
DROP PROCEDURE to delete a procedure:
WRITE !,"Deleting a procedure"
&sql(DROP PROCEDURE SAMPLES.PersonStateSP)
IF SQLCODE=0 { WRITE !,"Deleted a procedure" }
ELSEIF SQLCODE=-362 { WRITE !,"Procedure did not exist" }
ELSE { WRITE !,"SQL error: ",SQLCODE }
The following example uses
CREATE PROCEDURE to generate
the method UpdateSalary in the class Sample.Employee:
CREATE PROCEDURE UpdateSalary ( IN SSN VARCHAR(11), IN Salary FLOAT )
FOR Sample.Employee
BEGIN
UPDATE Sample.Employee SET Salary = :Salary WHERE SSN = :SSN;
END
The following example uses
CREATE PROCEDURE with
Caché ObjectScript code to generate the method GetTitle in the class
Sample.Employee and returns a
Title value:
CREATE PROCEDURE Sample_Employee.GetTitle(
INOUT pHandle %SQLProcContext,
IN SSN VARCHAR(11),
INOUT Title VARCHAR(50) )
RETURNS VARCHAR(30)
FOR Sample.Employee
LANGUAGE OBJECTSCRIPT
{
NEW SQLCODE,%ROWCOUNT
&sql(SELECT Title INTO :Title FROM Sample.Employee
WHERE SSN = :SSN)
IF $GET(pHandle)'=$$$NULLOREF {
SET pHandle.SQLCode=SQLCODE
SET pHandle.RowCount=%ROWCOUNT }
QUIT
}
It uses the procedure handler pHandle, and sets this handler's SQLCode
and RowCount attributes using the corresponding SQL variables. Note the curly
braces enclosing the Caché ObjectScript code following the procedure's
LANGUAGE OBJECTSCRIPT keyword. Within the Caché ObjectScript code there
is embedded SQL code, marked by
&sql and enclosed in
parentheses.