PowerBuilder动态SQL语句及在组合查询中的应用.doc

PowerBuilder不仅支持在PowerScript中使用标准嵌入SQL语句，而且支持使用动态SQL语句。动态SQL语句可以解决嵌入SQL语句不支持DDL语句的问题，还能够在运行时构成SQL语句以解决在编译时不知道语句的具体格式或参数的问题。作者在“抚顺机动车辆管理信息系统”的开发过程中，对动态SQL语句的使用有了深刻的体会，车辆信息项目繁多而且组合条件复杂多变，使用该语句，不仅查询速度较高而且使用灵活可靠。下面就各动态SQL语句的使用格式、方法及各自的特点给予简单介绍，然后以查找车辆为例介绍动态SQL语句（格式4）在组合查询中的应用。一、态SQL语句格式PowerBuilder 有四种动态SQL格式，每种格式用于处理编译过程中的不同情况。格式1：既没有输入参数，也没有输出结果集格式2：有输入参数，但没有输出结果集格式3：输入参数和结果集的列已知格式4：输入参数和结果集在编译时有一个或都未知 1动态SQL语句格式格式EXECUTE IMMEDIATE SQLStatement USING TransactionObject ;参数描述SQLStatement是包括一条有效SQL语句的字符串。该字符串可以是常量或者冒号后面跟PowerBuilder变量（如:mysql）。该字符串必须只包括在一行上，且不能包含表达式。TransactionObject（可选项）数据库的事务对象名执行这种格式的SQL语句没有结果集、也不需要输入参数。这种格式可用来执行所有形式的数据定义语言（DDL）。以下是一段建立车辆临时视图的例子：string sqlsql=“CREATE VIEW temp_vehicle AS SELECT code , master FROM vehicle ”EXECUTE IMMEDIATE :sql USING SQLDA;2. 动态SQL语句格式格式PREPARE DynamicStagingArea FROM SQLStatement USING TransactionObject ;EXECUTE DynamicStagingArea USING ParameterList ;参数描述DynamicStagingAreaDynamicStagingArea变量名（通常为SQLSA）SQLStatement包含一条有效SQL语句的字符串。该字符串可以是常量或者冒号后面跟 PowerBuilder 变量（如:mysql）。在该语句中每个参数键入问号(?)。TransactionObject（可选项）定义数据库的事务对象名ParameterList （可选项）用冒号分隔的PowerScript变量列表 以下是一段使用格式2的脚本：int id = 156 /车辆IDString master /车主SetNull(master) / 设成NULL值PREPARE SQLSA FROM INSERT INTO vehicle VALUES (?,?) ;EXECUTE SQLSA USING :id, :master ; 用这种SQL语句格式执行没有结果集、但需要输入参数。可用来执行所有形式的数据库定义语言（DDL）。 3. 动态SQL语句格式格式DECLARE Cursor | Procedure DYNAMIC CURSOR | PROCEDURE FOR DynamicStagingArea ;PREPARE DynamicStagingArea FROM SQLStatement USING TransactionObject ;OPEN DYNAMIC Cursor USING ParameterList ;EXECUTE DYNAMIC ProcedureUSING ParameterList ;FETCH Cursor | Procedure INTO HostVariableList ;CLOSE Cursor | Procedure ;参数描述Cursor or Procedurecursor或procedure名DynamicStagingAreaDynamicStagingArea变量名（通常为SQLSA）。SQLStatement 一条有效SQL SELECT语句的字符串。该字符串可以是常量或变量。TransactionObject （可选项）定义数据库的事务对象名ParameterList （可选项）用豆号分隔的PowerScript变量列表HostVariableList把数据值提取到PowerScript变量的列表 以下是一段使用格式3的脚本：DECLARE my_cursor DYNAMIC CURSOR FOR SQLSA ; /声明动态游标integer Vehicle_id /查找的车辆IDstring Mark= BMW /厂牌是”BMW”string sqlstatementsqlstatement = SELECT id FROM vehicle +WHERE mark = ? /SQL语句PREPARE SQLSA FROM :sqlstatement ; /在SQLSA中准备SELECT语句OPEN DYNAMIC my_cursor using :Mark ; /执行SQL语句,结果存入动态游标my_cursor中FETCH my_cursor INTO :Vehicle_id ; /从my_cursor中提取结果CLOSE my_cursor ; /关闭游标 此种SQL语句的执行，需输入参数和知道结果集的列。4动态SQL语句格式格式DECLARE Cursor|Procedure DYNAMIC CURSOR|PROCEDURE FORDynamicStagingArea;PREPARE DynamicStagingArea FROM SQLStatement USING TransactionObject;DESCRIBE DynamicStagingArea INTO DynamicDescriptionArea;OPEN DYNAMIC Cursor | ProcedureUSING DESCRIPTOR DynamicDescriptionArea ;EXECUTE DYNAMIC Cursor | ProcedureUSING DESCRIPTOR DynamicDescriptionArea ;FETCH Cursor | Procedure USING DESCRIPTOR DynamicDescriptionArea ;CLOSE Cursor | Procedure ;参数描述Cursor or Procedurecursor 或 procedure名DynamicStagingAreaDynamicStagingArea变量名（通常为SQLSA）。SQLStatement SQL SELECT语句的字符串。该字符串可以是常量或变量。 在该语句中，给每个参数的值赋予问号(?)。TransactionObject （可选项）定义数据库的事务对象名DynamicDescriptionAreaDynamicDescriptionArea变量名(SQLDA)二、动态SQL语句格式4的使用方法 1输入参数可以设置PREPARE语句中每个输入参数的类型和值。在DESCRIBE语句执行时, PowerBuilder 填充SQLDA 属性NumInputs的值。可以使用SetDynamicParm 函数来设置指定参数的类型和值。输入参数是可选的，但如果使用它们，必须在执行OPEN 或EXECUTE语句之前为全部输入参数赋值。 2输出参数 可以存取PREPARE语句中出现的每个输出参数的类型和值。如果数据库支持输出参数描述，则在DESCRIBE 执行时，PowerBuilder 填充 SQLDA 属性NumOutputs的值，否则在FETCH 执行时填充 SQLDA 属性NumOutputs的值。FETCH 语句执行后，DynamicDescriptionArea变量(SQLDA)中可以被访问的属性有：NumInputs 输入参数的个数 InParmType 输入参数类型数组 NumOutputs 输出参数的个数OutParmType 输出参数类型数组 3用于获取输出参数值的函数GetDynamicDate GetDynamicDateTime GetDynamicNumber GetDynamicString GetDynamicTime 4输入/输出参数的数据类型TypeBoolean!TypeDate!TypeDateTime!TypeDecimal!TypeDouble!TypeInteger!TypeLong!TypeReal!TypeString!TypeTime!TypeUInt!TypeULong!TypeUnknown! 下面的程序段使用了格式4的动态SQL语句。string SqlstatementSqlstatement = SELECT id FROM vehicle +WHERE mark = ?PREPARE SQLSA FROM :Sqlstatement ;DESCRIBE SQLSA INTO SQLDA ; /如果DESCRIBE语句执行成功,则输入描述数组必须 / 在OPEN语句执行之前赋值.DECLARE my_cursor DYNAMIC CURSOR FOR SQLSA ;SetDynamicParm(SQLDA, 1, MA)OPEN DYNAMIC my_cursor USING DESCRIPTOR SQLDA ;FETCH my_cursor USING DESCRIPTOR SQLDA ; /FETCH成功,则输出描述数组含有结/果集CLOSE my_cursor; 5使用PowerBuilder数据类型DynamicStagingArea和DynamicDescriptionArea DynamicStagingArea 是PowerBuilder的一种数据类型。PowerBuilder用该类型变量保存在接下来的语句中使用的信息。DynamicStagingArea 是执行语句和事务对象之间的唯一联系者，它在PowerBuilder内部使用，用户无法存取其中的信息。PowerBuilder 提供一个名为SQLSA 的DynamicStagingArea 全局变量，用户需要使用时，调用SQLSA即可。 DynamicDescriptionArea 是PowerBuilder 的另一种数据类型。PowerBuilder 用该类型变量保存动态SQL 语句格式4中有关输入输出参数的信息。PowerBuilder 同样提供一个名为SQLDA的DynamicDescriptionArea 型全局变量，当需要使用时，可以用SQLDA 调用它。 使用动态 SQL语句格式4时，应该特别注意以正确的次序执行这些语句。三、实例 下面的图例是车辆管理系统中的组合查询界面，用来收集查询条件，条件表达式存于字符串变量condition_sum中。Sqlstatement = SELECT count(*) FROM vehicle where +condition_sumPREPARE SQLSA FROM :Sqlstatement ;DESCRIBE SQLSA INTO SQLDA ;DECLARE my_cursor DYNAMIC CURSOR FOR SQLSA ;OPEN DYNAMIC my_cursor USING DESCRIPTOR SQLDA ;FETCH my_cursor USING DESCRIPTOR SQLDA ;Find_num = GetDynamicNumber(SQLDA, 1) /取出满足条件的记录数CLOSE my_cursor ;Sqlstatement=SELECT id,code,master,tele + FROM vehicle where +condition_sumPREPARE SQLSA FROM :Sqlstatement ;DESCRIBE SQLSA INTO SQLDA ;DECLARE my_cursor DYNAMIC CURSOR FOR SQLSA ;OPEN DYNAMIC my_cursor USING DES