Skip to main content
Previous section   Next section

Defining and Using Stored Procedures

This chapter describes how to define and use stored procedures in Caché SQL. It discusses the following:

Overview

An SQL routine is an executable unit of code that can be invoked by the SQL query processor. There are two types of SQL routines: functions and stored procedures. Functions are invoked from any SQL statement that supports functionname() syntax. Stored procedures can only be invoked by a CALL statement. Functions accept some number of input directed arguments and return a single result value. Stored procedures accept some number of input, input-output, and output arguments. A stored procedure can be a user-defined function, returning a single value. A function can also be invoked by a CALL statement.

Like most relational database systems, Caché allows you to create SQL stored procedures. A Stored Procedure (SP) provides a callable routine that is stored in the database and can be invoked within an SQL context (for example, by using the CALL statement or via ODBC or JDBC).

Unlike relational databases, Caché lets you define stored procedures as methods of classes. In fact, a stored procedure is nothing more than a class method that is made available to SQL. Within a stored procedure, you can use the full range of Caché object-based features.

  • You can defined a stored procedure as a query that returns a single result set of data by querying the database.

  • You can define a stored procedure as a function procedure that can serve as a user-defined function, returning a single value.

  • You can define a stored procedure as a method that can modify the database data and return either a single value or one or more result sets.

You can determine if a procedure already exists using the $SYSTEM.SQL.ProcedureExists() method. This method also returns the procedure type: “function” or “query”.

Defining Stored Procedures

As with most aspects of Caché SQL, there are two ways of defining stored procedures: using DDL and using classes. These are described in the following sections.

Defining a Stored Procedure Using DDL

Caché SQL supports the following commands to create a query:

  • CREATE PROCEDURE can create a query that is always projected as a stored procedure. A query can return a single result set.

  • CREATE QUERY creates a query that can optionally be projected as a stored procedure. A query can return a single result set.

Caché SQL supports the following commands to create a method or function:

  • CREATE PROCEDURE can create a method that is always projected as a stored procedure. A method can return a single value, or one or more result sets.

  • CREATE METHOD can create a method that can optionally be projected as a stored procedure. A method can return a single value, or one or more result sets.

  • CREATE FUNCTION can create a function procedure that can optionally be projected as a stored procedure. A function can return a single value.

The block of executable code specified within these commands can be written either in Caché SQL or ObjectScript. You can include Embedded SQL within an ObjectScript code block.

SQL to Class Name Transformations

When you use DDL to create a stored procedure, the name you specify is transformed into a class name. If the class does not exist, the system creates it.

  • If the name is unqualified and no FOR clause is provided: the system-wide default schema name is used as the package name, followed by a dot, followed by a generated class name consisting of the string ‘func’, ‘meth’, ‘proc’, or ‘query’, followed by the SQL name stripped of punctuation characters. For example, the unqualified procedure name Store_Name results in a class name such as the following: User.procStoreName. This procedure class contains the method StoreName().

  • If the name is qualified and no FOR clause is provided: the name of the schema is converted to a package name, followed by a dot, followed by the string ‘func’, ‘meth’, ‘proc’, or ‘query’, followed by the SQL name stripped of punctuation characters. If necessary, the specified package name is converted to a valid package name.

    If the name is qualified and a FOR clause is provided: the qualified class name specified in the FOR clause overrides the schema name specified in the function, method, procedure, or query name.

  • SQL stored procedure names follow identifier naming conventions. Caché strips punctuation characters from the SQL name to generate unique class entity names for the procedure class and its class methods.

The following rules govern the transformation of a schema name to valid package name:

  • If the schema name contains an underscore, this character is converted to a dot, denoting a subpackage. For example, the qualified name myprocs.myname creates the package myprocs. The qualified name my_procs.myname creates the package my containing the subpackage procs.

The following example shows how the punctuation differs in a class name and its SQL invocation. It defines a method with a class name containing two dots. When invoked from SQL, the example replace the first dot with an underscore character:

Class tmp.test.sql Extends %RegisteredObject 
 {  ClassMethod myfunc(dummy As %String) As %String [ SqlProc ] 
    { /* method code */
      Quit "abc" }
 }  
Copy code to clipboard
SELECT tmp_test.sql_myfunc(Name)
FROM Sample.Person
Copy code to clipboard

Defining a Method Stored Procedure using Classes

Class methods can be exposed as Stored Procedures. These are ideal for actions that do not return data, such as a Stored Procedure that calculates a value and stores it in the database. Almost all classes can expose methods as Stored Procedures; the exception is generator classes, such as a data type class ([ClassType = datatype]). Generator classes do not have a runtime context. It is only valid to use a datatype context within the runtime of some other entity, such as a property.

To define a method stored procedure, simply define a class method and set its SqlProc keyword:

Class MyApp.Person Extends %Persistent [DdlAllowed]
{

/// This procedure finds total sales for a territory
ClassMethod FindTotal(territory As %String) As %Integer [SqlProc]
{
    // use embedded sql to find total sales
    &sql(SELECT SUM(SalesAmount) INTO :total 
            FROM Sales
            WHERE Territory = :territory
    )

    Quit total
}
}
Copy code to clipboard

After this class is compiled, the FindTotal() method will be projected to SQL as the stored procedure MyApp.Person_FindTotal(). You can change the name that SQL uses for the procedure using the SqlName keyword of the method.

The method 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.

%sqlcontext consists of properties for the SQLCODE error status, the SQL row count, an error message, and so forth, which are set using the corresponding SQL variables, as follows:

  SET %sqlcontext.%SQLCode=SQLCODE
  SET %sqlcontext.%ROWCOUNT=%ROWCOUNT
  SET %sqlcontext.%Message=%msg
Copy code to clipboard

There is no need to do anything with these values, but their values will be interpreted by the client. The %sqlcontext object is reset before each execution.

The method should return no value.

The maximum number of user-defined methods for a class is 2000.

For instance, suppose there is a CalcAvgScore() method:

ClassMethod CalcAvgScore(firstname As %String,lastname As %String) [sqlproc]
{
  New SQLCODE,%ROWID
  &sql(UPDATE students SET avgscore = 
    (SELECT AVG(sc.score) 
     FROM scores sc, students st
     WHERE sc.student_id=st.student_id 
       AND st.lastname=:lastname
       AND st.firstname=:firstname)
     WHERE students.lastname=:lastname
       AND students.firstname=:firstname)

  IF ($GET(%sqlcontext)'= "") {
    SET %sqlcontext.%SQLCODE = SQLCODE
    SET %sqlcontext.%ROWCOUNT = %ROWCOUNT
  }
  QUIT
}
Copy code to clipboard

Defining a Query Stored Procedure using Classes

Many Stored Procedures that return data from the database can be implemented through the standard query interface. This approach works well as long as the procedure can be written in embedded SQL. Note the use of the Embedded SQL host variable to supply a value to the WHERE clause in the following example:

Class MyApp.Person Extends %Persistent [DdlAllowed]
{

    /// This procedure result set is the persons in a specified Home_State, ordered by Name
    Query ListPersons(state As %String = "") As %SQLQuery [ SqlProc ]
    {
        SELECT ID,Name,Home_State
        FROM Sample.Person
        WHERE Home_State = :state
        ORDER BY Name
    }
}
Copy code to clipboard

To expose a query as a Stored Procedure, either change the value of the SQLProc field to True in the Studio Inspector’s entry for the query or add the following “[ SqlProc ]” string to the query definition:

Query QueryName() As %SQLQuery( ... query definition ... ) 
    [ SqlProc ]
Copy code to clipboard

After this class is compiled, the ListPersons query will be projected to SQL as the stored procedure MyApp.Person_ListPersons. You can change the name that SQL uses for the procedure using the SqlName keyword of the query.

When MyApp.Person_ListPersons is called from SQL, it will automatically return the result set defined by the query’s SQL statement.

The following example is a stored procedure using a result set:

Class apc.OpiLLS.SpCollectResults1 [ Abstract ]
{

/// This SP returns a number of rows (pNumRecs) from WebService.LLSResults, and updates a property for each record
Query MyQuery(pNumRecs As %Integer) As %Query(ROWSPEC = "Name:%String,DOB:%Date") [ SqlProc ]
{
}

/// You put initial code here in the Execute method
ClassMethod MyQueryExecute(ByRef qHandle As %Binary, pNumRecs As %Integer) As %Status
{
    SET mysql="SELECT TOP ? Name,DOB FROM Sample.Person"       
    SET rset=##class(%SQL.Statement).%ExecDirect(,mysql,pNumRecs)
            IF rset.%SQLCODE'=0 {QUIT rset.%SQLCODE}
    SET qHandle=rset
    QUIT $$$OK
}

/// This code is called by the SQL framework for each row, until no more rows are returned
ClassMethod MyQueryFetch(ByRef qHandle As %Binary, ByRef Row As %List, 
                         ByRef AtEnd As %Integer = 0) As %Status [ PlaceAfter = NewQuery1Execute ]
{
     SET rset=qHandle
     SET tSC=$$$OK 
      
     FOR {
        ///Get next row, quit if end of result set
        IF 'rset.%Next() {
                SET Row = "", AtEnd = 1
                SET tSC=$$$OK
                QUIT
                }
        SET name=rset.Name
        SET dob=rset.DOB
        SET Row = $LISTBUILD(name,dob)
        QUIT
        }         
        QUIT tSC
}

ClassMethod MyQueryClose(ByRef qHandle As %Binary) As %Status [ PlaceAfter = NewQuery1Execute ]
{
        KILL qHandle   //probably not necesary as killed by the SQL Call framework
        QUIT $$$OK
}

}
Copy code to clipboard

If it is possible to write the query as a simple SQL statement and create it through the Query Wizard, it is not necessary to know anything about the underlying methods that implement the query.

Behind the scenes, for each query the class compiler generates methods based on the name of the Stored Procedure, including:

  • stored-procedure-nameExecute()

  • stored-procedure-nameFetch()

  • stored-procedure-nameFetchRows()

  • stored-procedure-nameGetInfo()

  • stored-procedure-nameClose()

If the query is of type %SQLQuery, the class compiler automatically inserts some embedded SQL into the generated methods. Execute() declares and opens a stored cursor for the SQL. Fetch() is called repeatedly until it returns an empty row (SET Row=""). You can, optionally, also have Fetch() return an AtEnd=1 boolean flag to indicate that the current Fetch constitutes the last row and the next Fetch is expected to return an empty row. However, an empty row (Row="") should always be used as the test to determine when the result set has ended; Row="" should always be set when setting AtEnd=1.

FetchRows() is logically equivalent to repeated calls to Fetch(). GetInfo() is called to return details of the signature for the Stored Procedure. Close() closes the cursor.

All these methods are called automatically when a Stored Procedure is invoked from a client, but could in theory be called directly from ObjectScript running on the server.

To pass an object from the Execute() to a Fetch(), or from a Fetch() to the next invocation of Fetch(), you can set the query handler to the object reference (oref) of the object you wish to pass. To pass multiple objects, you can set qHandle as an array:

  SET qHandle(1)=oref1,qHandle(2)=oref2
Copy code to clipboard

It is possible to create a result set stored procedure that is based on custom-written code (not an SQL statement).

The maximum number of user-defined queries for a class is 200.

Customized Class Queries

For complex queries, or for Stored Procedures that do not fit the query model, it is often necessary to customize the query by replacing some or all of its methods. You can use %Library.Query, as described in this section.

It is often easier to implement the query if you choose type %Query (%Library.Query) instead of %SQLQuery (%Library.SQLQuery). This generate the same five methods, but now the FetchRows() is simply a repeated invocation of Fetch() (%SQLQuery has some optimization that causes other behavior). GetInfo() simply gets information from the signature, so it is very unlikely that the code will need to be changed. This reduces the problem to creating class methods for each of the other three. Note that when the class is compiled, the compiler detects the presence of these methods, and does not overwrite them.

The methods need specific signatures: They all take a Qhandle (query handler) of type %Binary. This is a pointer to a structure holding the nature and state of the query. This is passed by reference to Execute() and Fetch() and by value to Close():

ClassMethod SP1Close(qHandle As %Binary) As %Status
{
   // ... 
}

ClassMethod SP1Execute(ByRef qHandle As %Binary,
    p1 As %String) As %Status
{
   // ...
}

ClassMethod SP1Fetch(ByRef qHandle As %Binary, 
    ByRef Row As %List, ByRef AtEnd As %Integer=0) As %Status
{
   // ...
}

Query SP1(p1 As %String) 
   As %Query(CONTAINID=0,ROWSPEC="lastname:%String") [sqlproc ]
{
}
Copy code to clipboard

The code usually includes declaration and use of an SQL cursor. Cursors generated from queries of type %SQLQuery automatically have names such as Q14. You must ensure that your queries are given distinct names.

The class compiler must find a cursor declaration, before making any attempt to use the cursor. Therefore the DECLARE statement (usually in Execute) must be in the same MAC routine as the Close and Fetch and must come before either of them. Editing the source directly, use the method keyword PLACEAFTER in both the Close and the Fetch definitions to make sure this happens.

Error messages refer to the internal cursor name, which typically has an extra digit. Therefore an error message for cursor Q140 probably refers to Q14.

Using Stored Procedures

You can use stored procedures in two distinct ways:

  • You can invoke a stored procedure using the SQL CALL statement; see the CALL statement in the Caché SQL Reference for more details.

  • You can use a stored function (that is, a method-based stored procedure that returns a single value) as if it were a built-in function within an SQL query.

Note:

When executing a stored procedure that takes an SQL function as a argument, invoke the stored procedure using CALL, as in the following example:

CALL sp.MyProc(CURRENT_DATE)
Copy code to clipboard

A SELECT query does not support executing a stored procedure with an SQL function argument. SELECT does support executing a stored function with an SQL function argument.

xDBC does not support executing a stored procedure with an SQL function argument using either SELECT or CALL.

Stored Functions

A stored function is a method-based stored procedure that returns a single value. For example, the following class defines a stored function, Square, that returns the square of a given value:

Class MyApp.Utils Extends %Persistent [DdlAllowed]
{
ClassMethod Square(val As %Integer) As %Integer [SqlProc]
{
    Quit val * val
}
}
Copy code to clipboard

A stored function is simply a class method with the SqlProc keyword specified.

Note:

For a stored function, the ReturnResultsets keyword must either be not specified (the default) or prefaced by the keyword Not.

You can use a stored function within an SQL query as if it were a built-in SQL function. The name of the function is the SQL name of the stored function (in this case “Square”) qualified by the schema (package) name in which it was defined (in this case “MyApp”).

The following query uses the Square function:

SELECT Cost, MyApp.Utils_Square(Cost) As SquareCost FROM Products
Copy code to clipboard

If you define multiple stored functions within the same package (schema), you must make sure that they have unique SQL names.

The following example defines a table named Sample.Wages that has two defined data fields (properties) and two defined stored functions, TimePlus and DTime:

Class Sample.Wages Extends %Persistent [ DdlAllowed ]
{  
  Property Name As %String(MAXLEN = 50) [ Required ];
  Property Salary As %Integer;
  ClassMethod TimePlus(val As %Integer) As %Integer [ SqlProc ]
  {
   QUIT val * 1.5
  }
  ClassMethod DTime(val As %Integer) As %Integer [ SqlProc ]
  {
   QUIT val * 2
  }
} 
Copy code to clipboard

The following query uses these stored procedures to return the regular salary, time-and-a-half, and double time salary rates for each employee in the same table, Sample.Wages:

SELECT Name,Salary,
       Sample.Wages_TimePlus(Salary) AS Overtime,
       Sample.Wages_DTime(Salary) AS DoubleTime FROM Sample.Wages
Copy code to clipboard

The following query uses these stored procedures to return the regular salary, time-and-a-half, and double time salary rates for each employee in a different table, Sample.Employee:

SELECT Name,Salary,
       Sample.Wages_TimePlus(Salary) AS Overtime,
       Sample.Wages_DTime(Salary) AS DoubleTime FROM Sample.Employee
Copy code to clipboard

Privileges

To execute a procedure, a user must have EXECUTE privilege for that procedure. Use the GRANT command or the %SYSTEM.SQL GrantObjPriv() method to assign EXECUTE privilege for a specified procedure to a specified user.

You can determine if a specified user has EXECUTE privilege for a specified procedure by invoking the $SYSTEM.SQL.CheckPriv() method.

To list all the procedures for which a user has EXECUTE privilege, go to the Management Portal. From System Administration select Security, then select either Users (System, Security Management, Users) or Roles (System, Security Management, Roles). Select Edit for the desired user or role, then select the SQL Procedures tab. Select the desired Namespace from the drop-down list.

Listing Procedures

The INFORMATION.SCHEMA.ROUTINES persistent class displays information about all routines and procedures in the current namespace.

When specified in Embedded SQL, INFORMATION.SCHEMA.ROUTINES requires the #include %occInclude macro preprocessor directive. This directive is not required for Dynamic SQL.

The following example returns the routine name, method or query name, routine type (PROCEDURE or FUNCTION), routine body (SQL=class query with SQL, EXTERNAL=not a class query with SQL), the return data type, and the routine definition for all routines in the schema “Sample” in the current namespace:

SELECT ROUTINE_NAME,METHOD_OR_QUERY_NAME,ROUTINE_TYPE,ROUTINE_BODY,SQL_DATA_ACCESS,IS_USER_DEFINED_CAST,
DATA_TYPE||' '||CHARACTER_MAXIMUM_LENGTH AS Returns,NUMERIC_PRECISION||':'||NUMERIC_SCALE AS PrecisionScale,
ROUTINE_DEFINITION 
FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='Sample'
Copy code to clipboard

The INFORMATION.SCHEMA.PARAMETERS persistent class displays information about input and output parameters for all routines and procedures in the current namespace.

The following example returns the routine name, parameter name, whether it is an input or output parameter, and the parameter data type information for all routines in the schema “Sample” in the current namespace:

SELECT SPECIFIC_NAME,PARAMETER_NAME,PARAMETER_MODE,ORDINAL_POSITION,
DATA_TYPE,CHARACTER_MAXIMUM_LENGTH AS MaxLen,NUMERIC_PRECISION||':'||NUMERIC_SCALE AS PrecisionScale
FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_SCHEMA='Sample'
Copy code to clipboard

You can display much of the same information for a single procedure using the Catalog Details tab in the Management Portal SQL Interface. The Catalog Details for a procedure include the procedure type (query or function), class name, method or query name, the description, and the number of input and output parameters. The Catalog Details Stored Procedure Info display also provides an option to run the stored procedure.