第十三章使用动态SQL（二）

原创

学无为 2022-01-13 15:58:44 ©著作权

文章标签 iris cache sql 动态sql sql语句 文章分类 R语言后端开发

©著作权归作者所有：来自51CTO博客作者学无为的原创作品，请联系作者获取转载授权，否则将追究法律责任

文章目录

第十三章使用动态SQL（二）
准备SQL语句

第十三章使用动态SQL（二）准备SQL语句

准备一条SQL语句将验证该语句，为后续执行做准备，并生成有关该SQL语句的元数据。

有三种使用％SQL.Statement类准备SQL语句的方法：

％Prepare（），它为后续的％Execute（）准备一条SQL语句（例如，查询）。
％PrepareClassQuery（），它准备对现有查询的调用语句。准备好之后，可以使用随后的％Execute（）执行此查询。
％ExecDirect（），它同时准备和执行一条SQL语句。 “执行SQL语句”中介绍了％ExecDirect（）。

也可以使$SYSTEM.SQL.Prepare()方法在不创建对象实例的情况下准备SQL语句。以下终端示例显示了Prepare（）方法：

/// d ##class(PHA.TEST.SQL).DynamicSQL8()
ClassMethod DynamicSQL8()
{
	SET topnum=5
	SET prep=$SYSTEM.SQL.Prepare("SELECT TOP :topnum Name,Age FROM Sample.Person WHERE Age=?")
	DO prep.%Display()
}

准备一条SQL语句将创建一个缓存的查询。使用缓存的查询可以使同一SQL查询多次执行，而无需重新准备SQL语句。高速缓存的查询可以由任何进程执行一次或多次。可以使用不同的输入参数值执行。

每次准备一条SQL语句时，InterSystems IRIS都会搜索查询缓存，以确定是否已经准备并缓存了相同的SQL语句。（如果两个SQL语句仅在文字和输入参数的值上不同，则认为它们是“相同的”。）如果查询缓存中不存在准备好的语句，则InterSystems IRIS将创建一个缓存的查询。如果查询缓存中已经存在准备好的语句，则不会创建新的缓存查询。因此，重要的是不要在循环结构内编写prepare语句。

%Prepare()

可以使用％SQL.Statement类的％Prepare（）实例方法准备一条SQL语句。 ％Prepare（）方法将SQL语句作为其第一个参数。可以将其指定为带引号的字符串或解析为带引号的字符串的变量，如以下示例所示：

  SET qStatus = tStatement.%Prepare("SELECT Name,Age FROM Sample.Person")

可以使用通过引用传递的带下标的数组来指定更复杂的查询，如以下示例所示：

  SET myquery = 3
  SET myquery(1) = "SELECT %ID AS id, Name, DOB, Home_State"
  SET myquery(2) = "FROM Person WHERE Age > 80"
  SET myquery(3) = "ORDER BY 2"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(.myquery)

查询可以包含重复的字段名称和字段名称别名。

提供给％Prepare（）的查询可以包含输入主机变量，如以下示例所示：

  SET minage = 80
  SET myquery = 3
  SET myquery(1) = "SELECT %ID AS id, Name, DOB, Home_State"
  SET myquery(2) = "FROM Person WHERE Age > :minage"
  SET myquery(3) = "ORDER BY 2"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(.myquery)

执行SQL语句时，InterSystems IRIS会为每个输入主机变量替换定义的文字值。但是请注意，如果将此代码作为方法调用，则必须将minage变量设为Public。默认情况下，方法是ProcedureBlocks。这意味着方法（例如％Prepare（））无法查看其调用方定义的变量。可以通过将类指定为[Not ProcedureBlock]，将方法指定为[ProcedureBlock = 0]或指定[PublicList = minage]来覆盖此默认值。

注意：在将输入变量插入SQL代码之前，始终确认输入变量包含适当的值是一种良好的程序习惯。

还可以使用？向查询提供文字值。输入参数。 InterSystems IRIS用一个文字值代替每个？输入参数，并使用提供给％Execute（）方法的相应参数值。在％Prepare（）之后，可以使用％GetImplementationDetails（）方法列出输入主机变量和？查询中的输入参数。

％Prepare（）方法返回％Status值：成功返回状态1（查询字符串有效；当前名称空间中存在引用的表）。失败返回以0开头的对象表达式，后跟编码的错误信息。因此，您无法执行status = 0的失败测试；您可以对错误执行$$$$ ISOK（status）= 0宏测试。

％Prepare（）方法使用前面定义的％SchemaPath属性来解析不合格的名称。

注意：只要有可能，使用完全限定的名称就可以显着提高动态SQL性能。

  SET myquery="SELECT TOP ? Name,Age FROM Sample.Person WHERE Age > ?"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(myquery)

为每个值指定一个值？执行查询时，％Execute（）实例方法中的输入参数。输入参数必须采用文字值或解析为文字值的表达式。输入参数不能采用字段名称值或字段名称别名。必须将输入参数声明为PUBLIC，以便SELECT语句直接引用它。

查询可以包含字段别名。在这种情况下，Data属性使用别名而不是字段名称访问数据。

不仅限于Dynamic SQL中的SELECT语句：可以使用％Prepare（）实例方法准备其他SQL语句，包括CALL，INSERT，UPDATE和DELETE语句。

可以使用％Display（）实例方法显示有关当前准备的语句的信息，如以下示例所示：

/// d ##class(PHA.TEST.SQL).DynamicSQL9()
ClassMethod DynamicSQL9()
{
	SET tStatement = ##class(%SQL.Statement).%New(,"Sample")
	SET myquery = 3
	SET myquery(1) = "SELECT TOP ? Name,DOB,Home_State"
	SET myquery(2) = "FROM Person"
	SET myquery(3) = "WHERE Age > 60 AND Age < 65"
	SET qStatus = tStatement.%Prepare(.myquery)
	IF qStatus'=1 {
		WRITE "%Prepare failed:" 
		DO $System.Status.DisplayError(qStatus) 
		QUIT
	}
	DO tStatement.%Display()
	WRITE !,"End of %Prepare display"
}

DHC-APP 2d0>d ##class(PHA.TEST.SQL).DynamicSQL9()
 
Implementation class: %sqlcq.DHCdAPP.cls373
           Arguments: ?,60,65
      Statement Text:
        SELECT TOP ? Name,DOB,Home_State
        FROM Sample.Person
        WHERE Age > 60 AND Age < 65
 
End of %Prepare display

此信息包括实现类，参数（实际参数的逗号分隔列表，即文字值或？输入参数）以及语句文本。

%PrepareClassQuery()

可以使用％SQL.Statement类的％PrepareClassQuery（）实例方法准备现有的SQL查询。 ％PrepareClassQuery（）方法采用两个参数：现有查询的类名和查询名。两者都指定为带引号的字符串或解析为带引号的字符串的变量，如以下示例所示：

SET qStatus = tStatement.%PrepareClassQuery("User.queryDocTest","DocTest")

％PrepareClassQuery（）方法返回％Status值：成功返回状态1。失败返回以0开头的对象表达式，后跟编码错误信息。因此，您无法执行qStatus = 0测试是否失败。可以针对错误执行一次$$$ ISOK（qStatus）= 0宏测试。

％PrepareClassQuery（）方法使用前面定义的％SchemaPath属性来解析不合格的名称。

％PrepareClassQuery（）使用CALL语句执行。因此，执行的类查询必须具有SqlProc参数。

下面的示例显示％PrepareClassQuery（）调用Sample.Person类中定义的ByName查询，并传递一个字符串以将返回的名称限制为以该字符串值开头的名称：

/// d ##class(PHA.TEST.SQL).PrepareClassQuery()
ClassMethod PrepareClassQuery()
{
	SET statemt=##class(%SQL.Statement).%New()
	SET cqStatus=statemt.%PrepareClassQuery("Sample.Person","ByName")
	IF cqStatus'=1 {
		WRITE "%PrepareClassQuery failed:" 
		DO $System.Status.DisplayError(cqStatus) 
	QUIT}
	SET rset=statemt.%Execute("L")
	DO rset.%Display()
}

DHC-APP>d ##class(PHA.TEST.SQL).PrepareClassQuery()
Dumping result #1
ID      Name    DOB     SSN
146     LaRocca,David X.        42013   603-23-8852
54      Larson,Nataliya Z.      52896   277-65-7763
65      Lee,Zoe Z.      62253   548-60-1784
105     Leiberman,Nataliya F.   46706   624-13-9765
56      Lennon,Chelsea T.       54537   190-51-5484
106     Lennon,Imelda Z.        57911   594-60-9044
137     Lennon,Maureen M.       38392   746-77-6520
178     Lepon,Janice T. 45675   188-86-7267
29      Lepon,Jeff Z.   37144   212-43-4979
112     Lepon,Kevin N.  31575   929-85-8355
154     Lopez,Ralph W.  45541   391-39-9235
77      Love,Janice E.  33050   515-29-7228
 
12 Rows(s) Affected

下面的示例显示％PrepareClassQuery（）调用现有查询：

/// d ##class(PHA.TEST.SQL).PrepareClassQuery1()
ClassMethod PrepareClassQuery1()
{
	SET tStatement=##class(%SQL.Statement).%New()
	SET cqStatus=tStatement.%PrepareClassQuery("%SYS.GlobalQuery","Size")
	IF cqStatus'=1 {
		WRITE "%PrepareClassQuery failed:" 
		DO $System.Status.DisplayError(cqStatus) 
		QUIT
	}
	SET install=$SYSTEM.Util.DataDirectory()
	SET rset=tStatement.%Execute(install_"mgr\User")
	DO rset.%Display()
}

下面的示例显示％Prepare（）准备CREATE QUERY语句，然后％PrepareClassQuery（）调用该类查询：

/// 用sql 创建query 斌斌个查询
/// d ##class(PHA.TEST.SQL).PrepareClassQuery2()
ClassMethod PrepareClassQuery2()
{
	/* 创建Query */
	SET myquery=4
	SET myquery(1)="CREATE QUERY DocTest() SELECTMODE RUNTIME PROCEDURE "
	SET myquery(2)="BEGIN "
	SET myquery(3)="SELECT TOP 5 Name,Home_State FROM Sample.Person ; "
	SET myquery(4)="END"
	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()
	IF rset.%SQLCODE=0 { 
		WRITE !,"创建Query",! 
	} ELSEIF rset.%SQLCODE=-361 { 
		WRITE !,"存在Query : ",rset.%Message,! 
	} ELSE { 
		WRITE !,"创建QUERY错误: ",rset.%SQLCODE," ",rset.%Message   QUIT
	}
	/* 调用Query */
	WRITE !,"调用类查询"
	SET cqStatus = tStatement.%PrepareClassQuery("User.queryDocTest","DocTest")
	IF cqStatus'=1 {
		WRITE !,"%PrepareClassQuery 失败:" 
		DO $System.Status.DisplayError(cqStatus) 
		QUIT
	}
	SET rset = tStatement.%Execute()
	WRITE "Query 数据",!,!
	WHILE rset.%Next() {
		DO rset.%Print() 
	} 
	WRITE !,"数据结束"
	/* 删除Query */
	&sql(DROP QUERY DocTest)
	IF SQLCODE<0 {
		WRITE !,"删除Query错误:",SQLCODE," ",%msg  QUIT 
	} ELSE {
		WRITE !,"删除Query"
	}
}

DHC-APP>d ##class(PHA.TEST.SQL).PrepareClassQuery2()
 
创建Query
 
调用类查询Query 数据
 
yaoxin WI
xiaoli
姚鑫
姚鑫
姚鑫
 
数据结束
删除Query

要显示由存储的查询检索到的数据行，可以使用％Print（）方法，如本示例所示。若要显示由存储的查询检索到的特定列数据，必须使用％Get（“ fieldname”）或％GetData（colnum）方法.

如果查询定义为接受参数，则可以使用“？”在SQL语句中指定输入参数。 “ 特点。为每个值指定一个值？执行查询时，在％Execute（）方法中输入参数。必须将输入参数声明为PUBLIC，以便SELECT语句直接引用它。

可以使用％Display（）方法显示有关当前准备的查询的信息，如以下示例所示：

/// d ##class(PHA.TEST.SQL).PrepareClassQuery3()
ClassMethod PrepareClassQuery3()
{
	/* Creating the Query */
	SET myquery=4
	SET myquery(1)="CREATE QUERY DocTest() SELECTMODE RUNTIME PROCEDURE "
	SET myquery(2)="BEGIN "
	SET myquery(3)="SELECT TOP 5 Name,Home_State FROM Sample.Person ; "
	SET myquery(4)="END"
	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()
	IF rset.%SQLCODE=0 { 
		WRITE !,"Created a query",! 
	} ELSEIF rset.%SQLCODE=-361 { 
		WRITE !,"Query exists: ",rset.%Message 
	} ELSE { 
		WRITE !,"CREATE QUERY error: ",rset.%SQLCODE," ",rset.%Message   
		QUIT
	}
	/* 准备和显示有关Query的信息 */
	WRITE !,"Preparing a class query"
	SET cqStatus = tStatement.%PrepareClassQuery("User.queryDocTest","DocTest")
	IF cqStatus'=1 {
		WRITE !,"%PrepareClassQuery failed:" DO $System.Status.DisplayError(cqStatus) QUIT
	}
	DO tStatement.%Display()
	WRITE !,"End of %Prepare display"
	/* 删除Query */
	&sql(DROP QUERY DocTest)
	IF SQLCODE<0 {
		WRITE !,"删除Query错误:",SQLCODE," ",%msg  QUIT 
	} ELSE {
		WRITE !,"删除Query"
	}
}

DHC-APP>d ##class(PHA.TEST.SQL).PrepareClassQuery3()
 
Created a query
 
Preparing a class query
Implementation class: %sqlcq.DHCdAPP.cls376
           Arguments: <<none>>
      Statement Text: call SQLUser.DocTest()
 
End of %Prepare display
删除Query

此信息包括实现类，参数（实际参数的逗号分隔列表，即文字值或？输入参数）以及语句文本。

成功准备的结果

成功完成准备工作（％Prepare（），％PrepareClassQuery（）或％ExecDirect（））后您可以调用％SQL.Statement％Display（）实例方法或％GetImplementationDetails（）实例方法以返回当前准备的详细信息陈述。例如：

%Display():

/// d ##class(PHA.TEST.SQL).PrepareClassQuery4()
ClassMethod PrepareClassQuery4()
{
	SET myquery = "SELECT TOP 5 Name,Age FROM Sample.Person WHERE Age > 21"
	SET tStatement = ##class(%SQL.Statement).%New()
	SET qStatus = tStatement.%Prepare(myquery)
	IF qStatus'=1 {
		WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT
	}
	DO tStatement.%Display()
	SET rset = tStatement.%Execute()
}

DHC-APP>d ##class(PHA.TEST.SQL).PrepareClassQuery4()
 
Implementation class: %sqlcq.DHCdAPP.cls376
           Arguments: 5,21
      Statement Text: SELECT TOP 5 Name,Age FROM Sample.Person WHERE Age > 21

%GetImplementationDetails():

/// d ##class(PHA.TEST.SQL).PrepareClassQuery5()
ClassMethod PrepareClassQuery5()
{
	SET myquery = "SELECT TOP ? Name,Age FROM Sample.Person WHERE Age > 21 AND Name=:fname"
	SET tStatement = ##class(%SQL.Statement).%New()
	SET qStatus = tStatement.%Prepare(myquery)
	IF qStatus'=1 {
		WRITE "%Prepare failed:" 
		DO $System.Status.DisplayError(qStatus) 
		QUIT
	}
	SET bool = tStatement.%GetImplementationDetails(.pclassname,.ptext,.pargs)
	IF bool=1 {
		WRITE "Implementation class= ",pclassname,!
		WRITE "Statement text= ",ptext,!
		WRITE "Arguments= ",$LISTTOSTRING(pargs),!  
	} ELSE { // returns "?,?,c,21,v,fname
		WRITE "%GetImplementationDetails() failed",!
	}
	SET rset = tStatement.%Execute()
}

DHC-APP>d ##class(PHA.TEST.SQL).PrepareClassQuery5()
Implementation class= %sqlcq.DHCdAPP.cls377
Statement text= SELECT TOP ? Name,Age FROM Sample.Person WHERE Age > 21 AND Name=:fname
Arguments= ?,?,c,21,v,fname

这些方法提供以下信息：

Implementation class：与缓存查询相对应的类名称。例如：％sqlcq.SAMPLES.cls49。
Arguments：按指定顺序的查询参数列表。如果参数用双括号括起来以禁止字面量替换，则参数不包含在参数列表中。

％Display（）显示以逗号分隔的查询参数列表。每个参数可以是文字值，输入主机变量的名称（不带冒号）或输入参数的问号（？）。如果没有参数，则此项显示<< none >>。指定多个值的谓词（例如IN或％INLIST）将每个值作为单独的参数列出。

％GetImplementationDetails（）以％List结构形式返回查询参数。每个参数由一对元素，一个类型和一个值表示：类型c（常量）后跟一个文字值；类型v（变量）后跟输入主机变量的名称（不带冒号）；类型 ？是输入参数，后跟第二个问号。如果没有参数，则参数列表为空字符串。指定多个值的谓词（例如IN或％INLIST）将每个值作为单独的类型和值对列出。

Statement Text：查询文本，与指定的完全相同。保留字母大小写，主机变量和输入参数显示为已写，默认模式未显示。例如，对于％Prepare（），请选择SELECT TOP：n来自客户端的名称。例如对于％PrepareClassQuery（），请调用Sample.SP_Sample_By_Name（？）。

`preparse()`方法

可以使用preparse（）方法返回查询参数的％List结构，而无需准备SQL查询。查询参数以与％GetImplementationDetails（）相同的格式返回。

preparse（）方法还返回查询文本。但是，与％Display（）和％GetImplementationDetails（）完全返回指定的查询文本不同，preparse（）方法将每个查询参数替换为？。字符，删除注释并规范空白。它不提供默认架构名称。以下示例中的preparse（）方法返回查询文本的解析版本和查询参数的％List结构：


/// d ##class(PHA.TEST.SQL).PrepareClassQuery6()
ClassMethod PrepareClassQuery6()
{
	SET myq=2
	SET myq(1)="SELECT TOP ? Name /* first name */, Age "
	SET myq(2)="FROM Sample.MyTable WHERE Name='Fred' AND Age > :years -- end of query"
	DO ##class(%SQL.Statement).preparse(.myq,.stripped,.args)
	WRITE "preparsed query text: ",stripped,!
	WRITE "arguments list: ",$LISTTOSTRING(args)
}

DHC-APP>d ##class(PHA.TEST.SQL).PrepareClassQuery6()
preparsed query text:  SELECT TOP ? Name , Age FROM Sample . MyTable WHERE Name = ? AND Age > ?
arguments list: ?,?,c,Fred,v,years