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

?

?

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

characteristics

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

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

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

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

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

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

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

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

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

LANGUAGE

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

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

LANGUAGE langname EXTERNAL NAME external-routine-name

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

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

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

code_body

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

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

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

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

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

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

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代碼的示例

下面的示例創(chuàng)建了一個名為PersonStateSP的簡單查詢，該查詢作為存儲過程公開。
它沒有聲明任何參數(shù)，并接受特征和語言的默認(rèn)值:

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

可以轉(zhuǎn)到Management Portal，選擇Classes選項(xiàng)，然后選擇SAMPLES名稱空間。
在這里，將找到上述示例創(chuàng)建的存儲過程: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)行上面的程序示例之前，可以從這個顯示中刪除這個過程。
當(dāng)然，可以使用DROP PROCEDURE來刪除一個過程:

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

下面的示例創(chuàng)建一個更新數(shù)據(jù)的過程。它使用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代碼的示例

下面的示例創(chuàng)建生成隨機(jī)大寫字母的RandomLetterSP()存儲過程方法。然后，可以在SELECT語句中將此方法作為函數(shù)調(diào)用。提供了一個刪除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調(diào)用Execute()，Fetch()。和Close()方法。此類過程還可以包含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é)果集類：

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