第十一章 SQL命令 CREATE PROCEDURE（二）
2021-09-13 11:26:52

?

?

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

characteristics

用于創(chuàng)建方法的特征與用于創(chuàng)建查詢(xún)的特征不同。

如果指定的特征無(wú)效，系統(tǒng)將生成SQLCODE -47錯(cuò)誤。
指定重復(fù)的特征將導(dǎo)致SQLCODE -44錯(cuò)誤。

可用的方法特征關(guān)鍵字如下:

方法關(guān)鍵字	含義
FOR className	指定要在其中創(chuàng)建方法的類(lèi)的名稱(chēng)。如果這個(gè)類(lèi)不存在，它將被創(chuàng)建。還可以通過(guò)限定方法名來(lái)指定類(lèi)名。FOR子句中指定的類(lèi)名通過(guò)限定方法名重寫(xiě)指定的類(lèi)名。如果使用FOR my.class語(yǔ)法指定類(lèi)名， IRIS將用Sqlname=procname定義類(lèi)方法。因此，該方法應(yīng)該作為my.procname()調(diào)用(而不是my.class_procname())。
FINAL	指定子類(lèi)不能重寫(xiě)該方法。默認(rèn)情況下，方法不是final。FINAL關(guān)鍵字由子類(lèi)繼承。
PRIVATE	指定該方法只能由它自己的類(lèi)或子類(lèi)的其他方法調(diào)用。默認(rèn)情況下，方法是公共的，可以不受限制地調(diào)用。這個(gè)限制由子類(lèi)繼承。
RESULT SETS，DYNAMIC RESULT SETS [n]	指定創(chuàng)建的方法將包含ReturnResultsets關(guān)鍵字。這一特征短語(yǔ)的所有形式都是同義詞。
RETURNS datatype	指定調(diào)用該方法返回的值的數(shù)據(jù)類(lèi)型。如果省略RETURNS，則該方法不能返回值。這個(gè)規(guī)范由子類(lèi)繼承，并且可以由子類(lèi)修改。該數(shù)據(jù)類(lèi)型可以指定類(lèi)型參數(shù)，如MINVAL、MAXVAL和SCALE。例如RETURNS DECIMAL(19,4)。注意，當(dāng)返回一個(gè)值時(shí)， IRIS會(huì)忽略數(shù)據(jù)類(lèi)型的長(zhǎng)度;例如，RETURNS VARCHAR(32)可以接收由調(diào)用方法返回的任意長(zhǎng)度的字符串。
SELECTMODE mode	僅當(dāng)LANGUAGE為SQL(默認(rèn))時(shí)使用。當(dāng)指定時(shí)， IRIS將#SQLCOMPILE SELECT=mode語(yǔ)句添加到相應(yīng)的類(lèi)方法中，從而生成使用指定的SELECTMODE在方法中定義的SQL語(yǔ)句?？赡艿哪Ｊ街凳?code>LOGICAL、ODBC、RUNTIME和DISPLAY。默認(rèn)為LOGICAL。

可用的查詢(xún)特征關(guān)鍵字如下:

Query查詢(xún)關(guān)鍵字	含義
CONTAINID integer	指定返回ID的字段(如果有的話(huà))。將CONTAINID設(shè)置為返回ID的列的編號(hào)，如果沒(méi)有列返回ID，則設(shè)置為0。 IRIS不驗(yàn)證命名字段是否實(shí)際包含ID，因此此處的用戶(hù)錯(cuò)誤會(huì)導(dǎo)致數(shù)據(jù)不一致。
FOR className	指定要在其中創(chuàng)建方法的類(lèi)的名稱(chēng)。如果這個(gè)類(lèi)不存在，它將被創(chuàng)建。還可以通過(guò)限定方法名來(lái)指定類(lèi)名。FOR子句中指定的類(lèi)名通過(guò)限定方法名重寫(xiě)指定的類(lèi)名。
FINAL	指定子類(lèi)不能重寫(xiě)該方法。默認(rèn)情況下，方法不是final。FINAL關(guān)鍵字由子類(lèi)繼承。
RESULTS (result_set)	按照查詢(xún)返回的順序指定數(shù)據(jù)字段。如果指定RESULTS子句，則必須將查詢(xún)返回的所有字段用括號(hào)括起來(lái)的逗號(hào)分隔列表列出。在SQLCODE -76基數(shù)不匹配錯(cuò)誤中，指定比查詢(xún)結(jié)果返回的字段少或多。為每個(gè)字段指定一個(gè)列名(將用作列標(biāo)題)和一個(gè)數(shù)據(jù)類(lèi)型。如果使用SQL語(yǔ)言，則可以省略RESULTS子句。如果忽略RESULTS子句，則會(huì)在類(lèi)編譯期間自動(dòng)生成ROWSPEC。
SELECTMODE mode	指定用于編譯查詢(xún)的模式。可能的值是LOGICAL、ODBC、RUNTIME和DISPLAY。默認(rèn)是RUNTIME。

SELECTMODE子句用于SELECT查詢(xún)操作以及INSERT和UPDATE操作。
它指定編譯時(shí)選擇模式。
為SELECTMODE指定的值添加在ObjectScript類(lèi)方法代碼的開(kāi)頭，如:#SQLCompile Select=mode。

• 在SELECT查詢(xún)中，SELECTMODE指定返回?cái)?shù)據(jù)的模式。
  如果模式值為LOGICAL，則返回邏輯(內(nèi)部存儲(chǔ))值。
  例如，日期以$HOROLOG格式返回。
  如果模式值為ODBC，則應(yīng)用邏輯到ODBC的轉(zhuǎn)換，并返回ODBC格式值。
  如果模式值為DISPLAY，則應(yīng)用邏輯到顯示的轉(zhuǎn)換，并返回顯示格式值。
  如果mode值為RUNTIME，則可以在執(zhí)行時(shí)設(shè)置顯示模式(LOGICAL、ODBC或display)。
• 在INSERT或UPDATE操作中，SELECTMODE RUNTIME選項(xiàng)支持將輸入數(shù)據(jù)值從顯示格式(display或ODBC)自動(dòng)轉(zhuǎn)換為邏輯存儲(chǔ)格式。
  只有當(dāng)SQL代碼執(zhí)行時(shí)的選擇模式設(shè)置為LOGICAL(這是所有 SQL執(zhí)行接口的默認(rèn)設(shè)置)時(shí)，才會(huì)應(yīng)用這個(gè)已編譯的從顯示到邏輯的數(shù)據(jù)轉(zhuǎn)換代碼。

RESULTS子句指定查詢(xún)的結(jié)果。
RESULTS子句中的SQL數(shù)據(jù)類(lèi)型參數(shù)被轉(zhuǎn)換為查詢(xún)的ROWSPEC中相應(yīng)的 IRIS數(shù)據(jù)類(lèi)型參數(shù)。
例如，RESULTS子句RESULTS (Code VARCHAR(15))生成ROWSPEC規(guī)范:ROWSPEC = " Code:%Library.String(MAXLEN=15)"。

LANGUAGE

指定過(guò)程代碼語(yǔ)言的關(guān)鍵字子句?？捎玫倪x項(xiàng)是:

• 語(yǔ)言OBJECTSCRIPT(用于OBJECTSCRIPT)或語(yǔ)言SQL。過(guò)程代碼在code_body中指定。
• Language Java、Language Python或Language DotNet用于使用這些語(yǔ)言之一調(diào)用外部存儲(chǔ)過(guò)程的SQL過(guò)程。外部存儲(chǔ)過(guò)程的語(yǔ)法如下：

LANGUAGE langname EXTERNAL NAME external-routine-name

其中，langname是JAVA、PYTHON或DOTNET，而external-routine-name是一個(gè)引號(hào)括起來(lái)的字符串，包含指定語(yǔ)言中的外部例程的名稱(chēng)。
SQL過(guò)程調(diào)用現(xiàn)有的例程;
不能在CREATE PROCEDURE語(yǔ)句中用這些語(yǔ)言編寫(xiě)代碼。
這些語(yǔ)言中的存儲(chǔ)過(guò)程庫(kù)存儲(chǔ)在IRIS外部，因此不必在IRIS內(nèi)打包、導(dǎo)入或編譯。
下面是一個(gè)CREATE過(guò)程調(diào)用現(xiàn)有JAVA外部存儲(chǔ)過(guò)程的示例:

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

如果省略LANGUAGE子句，則默認(rèn)為SQL。

code_body

要?jiǎng)?chuàng)建的方法或查詢(xún)的程序代碼?？梢栽?code>SQL或ObjectScript中指定此代碼。使用的語(yǔ)言必須與language子句匹配。但是，ObjectScript中指定的代碼可以包含嵌入式SQL。IRIS使用提供的代碼來(lái)生成方法或查詢(xún)的實(shí)際代碼。

• SQL程序代碼以BEGIN關(guān)鍵字開(kāi)頭，后面跟著SQL代碼本身。
  在每個(gè)完整的SQL語(yǔ)句的末尾，指定一個(gè)分號(hào)(;)。
  一個(gè)查詢(xún)只包含一條SQL語(yǔ)句——一條SELECT語(yǔ)句。
  還可以創(chuàng)建插入、更新或刪除數(shù)據(jù)的過(guò)程。
  SQL程序代碼以END關(guān)鍵字結(jié)束。

輸入?yún)?shù)在SQL語(yǔ)句中作為主機(jī)變量指定，形式為:name。
(注意，在SQL代碼中不應(yīng)該使用問(wèn)號(hào)(?)來(lái)指定輸入?yún)?shù)。
過(guò)程將成功構(gòu)建，但在調(diào)用過(guò)程時(shí)，不能傳遞這些參數(shù)或接受默認(rèn)值。)

• ObjectScript程序代碼用花括號(hào)括起來(lái):{code}。
  代碼行必須縮進(jìn)。
  如果指定了，標(biāo)簽或#Include預(yù)處理器命令必須以冒號(hào)作為前綴，并出現(xiàn)在第一列，如下所示:

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

系統(tǒng)自動(dòng)包含%occInclude。
如果程序代碼包含 IRIS Macro Preprocessor語(yǔ)句(# commands， ## functions，或$$$ Macro references)，則這些語(yǔ)句的處理和擴(kuò)展是過(guò)程方法定義的一部分，并在方法編譯時(shí)進(jìn)行處理和擴(kuò)展。

IRIS在生成過(guò)程時(shí)提供額外的代碼行，該過(guò)程將SQL嵌入到ObjectScript“包裝器”中，提供過(guò)程上下文處理程序，并處理返回值。
下面是iris生成的包裝器代碼的示例:

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

如果指定的代碼是OBJECTSCRIPT，則必須顯式定義“包裝器”(該NEWs變量并使用QUIT val在完成時(shí)返回一個(gè)值。

示例

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

使用SQL代碼的示例

下面的示例創(chuàng)建了一個(gè)名為PersonStateSP的簡(jiǎn)單查詢(xún)，該查詢(xún)作為存儲(chǔ)過(guò)程公開(kāi)。
它沒(méi)有聲明任何參數(shù)，并接受特征和語(yǔ)言的默認(rèn)值:

ClassMethod CreateProcedure()
{
	&sql(
		CREATE PROCEDURE PersonStateSP() 
		BEGIN
			SELECT Name,Home_State FROM Sample.Person ;
		END
	)
	if SQLCODE=0 { 
		w !,"創(chuàng)建存儲(chǔ)過(guò)程" 
	} elseif SQLCODE=-361 { 
		w !,"存儲(chǔ)過(guò)程已經(jīng)存在" 
	} else { 
		w !,"SQL 錯(cuò)誤 ",SQLCODE 
	}
}

可以轉(zhuǎn)到Management Portal，選擇Classes選項(xiàng)，然后選擇SAMPLES名稱(chēng)空間。
在這里，將找到上述示例創(chuàng)建的存儲(chǔ)過(guò)程: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
}

}

在重新運(yùn)行上面的程序示例之前，可以從這個(gè)顯示中刪除這個(gè)過(guò)程。
當(dāng)然，可以使用DROP PROCEDURE來(lái)刪除一個(gè)過(guò)程:

ClassMethod CreateProcedure1()
{
	&sql(
		DROP PROCEDURE PersonStateSP
	)
	if SQLCODE=0 { 
		w !,"刪除儲(chǔ)存過(guò)程" 
	} elseif SQLCODE=-362 { 
		w !,"存儲(chǔ)過(guò)程不存在" 
	} else { 
		w !,"SQL錯(cuò)誤r: ",SQLCODE 
	}
}

下面的示例創(chuàng)建一個(gè)更新數(shù)據(jù)的過(guò)程。它使用CREATE PROCEDURE在Sample.Employee類(lèi)中生成方法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代碼的示例

下面的示例創(chuàng)建生成隨機(jī)大寫(xiě)字母的RandomLetterSP()存儲(chǔ)過(guò)程方法。然后，可以在SELECT語(yǔ)句中將此方法作為函數(shù)調(diào)用。提供了一個(gè)刪除RandomLetterSP()方法的刪除過(guò)程。

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調(diào)用Execute()，Fetch()。和Close()方法。此類(lèi)過(guò)程還可以包含FetchRows()和GetInfo()方法調(diào)用：

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調(diào)用%SQL.Statement結(jié)果集類(lèi)：

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代碼塊將數(shù)據(jù)提取到局部變量(例如，Row)中，則必須以行set Row=""結(jié)束代碼塊，以指示數(shù)據(jù)結(jié)束條件。

下面的示例將CREATE PROCEDURE與調(diào)用嵌入式SQL的ObjectScript代碼一起使用。它在Sample.Employee類(lèi)中生成方法GetTitle，并將Title值作為參數(shù)傳出：

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對(duì)象，并使用相應(yīng)的SQL變量設(shè)置它的%SQLCODE和%ROWCOUNT屬性。
注意，在過(guò)程的LANGUAGE ObjectScript關(guān)鍵字后面的花括號(hào)中包含ObjectScript代碼。
在ObjectScript代碼中有嵌入式SQL代碼，用&sql標(biāo)記，用括號(hào)括起來(lái)。