第十一章 SQL命令 CREATE PROCEDURE（二）

characteristics

用于创建方法的特征与用于创建查询的特征不同。

如果指定的特征无效，系统将生成SQLCODE -47错误。
指定重复的特征将导致SQLCODE -44错误。

可用的方法特征关键字如下:

可用的查询特征关键字如下:

SELECTMODE子句用于SELECT查询操作以及INSERT和UPDATE操作。
它指定编译时选择模式。
为SELECTMODE指定的值添加在ObjectScript类方法代码的开头，如:#SQLCompile Select=mode。
- 在SELECT查询中，SELECTMODE指定返回数据的模式。
如果模式值为LOGICAL，则返回逻辑(内部存储)值。
例如，日期以$HOROLOG格式返回。
如果模式值为ODBC，则应用逻辑到ODBC的转换，并返回ODBC格式值。
如果模式值为DISPLAY，则应用逻辑到显示的转换，并返回显示格式值。
如果mode值为RUNTIME，则可以在执行时设置显示模式(LOGICAL、ODBC或display)。
- 在INSERT或UPDATE操作中，SELECTMODE RUNTIME选项支持将输入数据值从显示格式(display或ODBC)自动转换为逻辑存储格式。
只有当SQL代码执行时的选择模式设置为LOGICAL(这是所有 SQL执行接口的默认设置)时，才会应用这个已编译的从显示到逻辑的数据转换代码。

RESULTS子句指定查询的结果。
RESULTS子句中的SQL数据类型参数被转换为查询的ROWSPEC中相应的 IRIS数据类型参数。
例如，RESULTS子句RESULTS (Code VARCHAR(15))生成ROWSPEC规范:ROWSPEC = " Code:%Library.String(MAXLEN=15)"。

LANGUAGE

指定过程代码语言的关键字子句。可用的选项是:
- 语言OBJECTSCRIPT(用于OBJECTSCRIPT)或语言SQL。过程代码在code_body中指定。
- Language Java、Language Python或Language DotNet用于使用这些语言之一调用外部存储过程的SQL过程。外部存储过程的语法如下：

LANGUAGE langname EXTERNAL NAME external-routine-name

其中，langname是JAVA、PYTHON或DOTNET，而external-routine-name是一个引号括起来的字符串，包含指定语言中的外部例程的名称。
SQL过程调用现有的例程;
不能在CREATE PROCEDURE语句中用这些语言编写代码。
这些语言中的存储过程库存储在IRIS外部，因此不必在IRIS内打包、导入或编译。
下面是一个CREATE过程调用现有JAVA外部存储过程的示例:

CREATE PROCEDURE updatePrice (item_name VARCHAR, new_price INTEGER)
LANGUAGE JAVA  
EXTERNAL NAME 'Orders.updatePrice'

如果省略LANGUAGE子句，则默认为SQL。

code_body

要创建的方法或查询的程序代码。可以在SQL或ObjectScript中指定此代码。使用的语言必须与language子句匹配。但是，ObjectScript中指定的代码可以包含嵌入式SQL。IRIS使用提供的代码来生成方法或查询的实际代码。
- SQL程序代码以BEGIN关键字开头，后面跟着SQL代码本身。
在每个完整的SQL语句的末尾，指定一个分号(;)。
一个查询只包含一条SQL语句——一条SELECT语句。
还可以创建插入、更新或删除数据的过程。
SQL程序代码以END关键字结束。

输入参数在SQL语句中作为主机变量指定，形式为:name。
(注意，在SQL代码中不应该使用问号(?)来指定输入参数。
过程将成功构建，但在调用过程时，不能传递这些参数或接受默认值。)

• ObjectScript程序代码用花括号括起来:{code}。
  代码行必须缩进。
  如果指定了，标签或#Include预处理器命令必须以冒号作为前缀，并出现在第一列，如下所示:

CREATE PROCEDURE SP123()
  LANGUAGE OBJECTSCRIPT 
{
:Top
:#Include %occConstant
  WRITE "Hello World"
  IF 0=$RANDOM(2) { GOTO Top }
  ELSE {QUIT $$$OK }
}

系统自动包含%occInclude。
如果程序代码包含 IRIS Macro Preprocessor语句(# commands， ## functions，或$$$ Macro references)，则这些语句的处理和扩展是过程方法定义的一部分，并在方法编译时进行处理和扩展。

IRIS在生成过程时提供额外的代码行，该过程将SQL嵌入到ObjectScript“包装器”中，提供过程上下文处理程序，并处理返回值。
下面是iris生成的包装器代码的示例:

   NEW SQLCODE,%ROWID,%ROWCOUNT,title
   &sql(
        -- code_body
       )
   QUIT $GET(title)

如果指定的代码是OBJECTSCRIPT，则必须显式定义“包装器”(该NEWs变量并使用QUIT val在完成时返回一个值。

示例

下面的示例分为使用SQL code_body的示例和使用ObjectScript code_body的示例。

使用SQL代码的示例

下面的示例创建了一个名为PersonStateSP的简单查询，该查询作为存储过程公开。
它没有声明任何参数，并接受特征和语言的默认值:

ClassMethod CreateProcedure()
{
    &sql(
        CREATE PROCEDURE PersonStateSP() 
        BEGIN
            SELECT Name,Home_State FROM Sample.Person ;
        END
    )
    if SQLCODE=0 { 
        w !,"创建存储过程" 
    } elseif SQLCODE=-361 { 
        w !,"存储过程已经存在" 
    } else { 
        w !,"SQL 错误 ",SQLCODE 
    }
}

可以转到Management Portal，选择Classes选项，然后选择SAMPLES名称空间。
在这里，将找到上述示例创建的存储过程:User.procPersonStateSP.cls。

Class User.procPersonStateSP Extends %Library.RegisteredObject [ ClassType = "", DdlAllowed, Owner = {yx}, Not ProcedureBlock ]
{

Query PersonStateSP() As %Library.SQLQuery [ SqlName = PersonStateSP, SqlProc ]
{
    SELECT Name,Home_State FROM Sample.Person
}

}

在重新运行上面的程序示例之前，可以从这个显示中删除这个过程。
当然，可以使用DROP PROCEDURE来删除一个过程:

ClassMethod CreateProcedure1()
{
    &sql(
        DROP PROCEDURE PersonStateSP
    )
    if SQLCODE=0 { 
        w !,"删除储存过程" 
    } elseif SQLCODE=-362 { 
        w !,"存储过程不存在" 
    } else { 
        w !,"SQL错误r: ",SQLCODE 
    }
}

下面的示例创建一个更新数据的过程。它使用CREATE PROCEDURE在Sample.Employee类中生成方法UpdateSalary：

CREATE PROCEDURE UpdateSalary ( IN SSN VARCHAR(11), IN Salary INTEGER )
   FOR Sample.Employee
   BEGIN
     UPDATE Sample.Employee SET Salary = :Salary WHERE SSN = :SSN;
   END

使用ObjectScript代码的示例

下面的示例创建生成随机大写字母的RandomLetterSP()存储过程方法。然后，可以在SELECT语句中将此方法作为函数调用。提供了一个删除RandomLetterSP()方法的删除过程。

CREATE PROCEDURE RandomLetterSP()
RETURNS INTEGER
LANGUAGE OBJECTSCRIPT
{
:Top
 SET x=$RANDOM(90)
 IF x<65 {GOTO Top}
 ELSE {QUIT $CHAR(x)}
}

SELECT Name FROM Sample.Person
WHERE Name %STARTSWITH RandomLetterSP()

DROP PROCEDURE RandomLetterSP

下面的CREATE PROCEDURE示例使用ObjectScript调用Execute()，Fetch()。和Close()方法。此类过程还可以包含FetchRows()和GetInfo()方法调用：

CREATE PROCEDURE GetTitle()
    FOR Sample.Employee
    RESULTS (ID %Integer)
    CONTAINID 1
    LANGUAGE OBJECTSCRIPT
    Execute(INOUT qHandle %Binary)
    {  QUIT 1 }
    Fetch(INOUT qHandle %Binary, INOUT Row %List, INOUT AtEnd %Integer)
    {  QUIT 1 }
    Close(INOUT qHandle %Binary)
    {  QUIT 1 }

下面的CREATE PROCEDURE示例使用ObjectScript调用%SQL.Statement结果集类：

CREATE PROCEDURE Sample_Employee.GetTitle(
    INOUT Title VARCHAR(50) )
    RETURNS VARCHAR(30)
    FOR Sample.Employee
    LANGUAGE OBJECTSCRIPT
  {
  SET myquery="SELECT TOP 10 Name,Title FROM Sample.Employee"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(myquery)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  DO rset.%Display()
  WRITE !,"End of data"
  }

如果ObjectScript代码块将数据提取到局部变量(例如，Row)中，则必须以行set Row=""结束代码块，以指示数据结束条件。

下面的示例将CREATE PROCEDURE与调用嵌入式SQL的ObjectScript代码一起使用。它在Sample.Employee类中生成方法GetTitle，并将Title值作为参数传出：

CREATE PROCEDURE Sample_Employee.GetTitle(
   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(%sqlcontext)'= "" {
           SET %sqlcontext.%SQLCODE=SQLCODE
           SET %sqlcontext.%ROWCOUNT=%ROWCOUNT }
           QUIT
     }

它使用%sqlcontext对象，并使用相应的SQL变量设置它的%SQLCODE和%ROWCOUNT属性。
注意，在过程的LANGUAGE ObjectScript关键字后面的花括号中包含ObjectScript代码。
在ObjectScript代码中有嵌入式SQL代码，用&sql标记，用括号括起来。