PB动态SQL语句(温故而知新)
PowerBuilder的嵌入式SQL语句只能支持一些固定的标准的SQL语句，即在进行程序代码编译处理时这些SQL语句必须是确定的，另外这种方式也不能执行像Creat
Table,Creat Database等这些数据库定义的语句(DDL)。
因此这种嵌入式SQL语句在实际应用中有一定的局限性。为克服这种方式的局限性，可以使用动态SQL语句，这种方式可以完成嵌入式SQL语句无法实现的功能。如建库、建表这一类的数据库定义语句(DDL)；其次，由于动态SQL语句允许在执行时才确定到底要执行怎样的SQL语句，因此使用动态SQL语句可以使程序实现参数化设计，具有很大的灵活性和通用性。
一、动态SQL语句的应用分析
PowerBuilder提供了四种格式的动态SQL语句，每种格式都有自己不同的特点和作用。
(一)第一种格式
当执行没有输入参数并且没有返回结果集的SQL语句时可以使用这种格式，这种格式使用比较简单，其实现的操作也比较少。
EXECUTE IMMEDIATE SQLStatement{USING TransactionObject}；
其中SQLStatement是要执行的SQL语句，可以直接用引号将要执行的SQL引起来用，或者用字符串变量的形式提供SQL语句。通过使用字符串变量可以在具体执行的时候才指定要执行什么样的SQL语句。TransactionObject是用户所使用的事务对象，缺省为SQLCA。
2 应用实例
①建立一张数据库表(base)，SQL语句的表述用引号引起来直接使用。
EXECUTE IMMEDIATE‘CREATE TABLE base(code char(6),name
char(30))’USING SQLCA；
②执行对数据库记录的操作，在表base中插入一条记录，SQL语句通过字符串变量传递执行语句。
STRING lsSQL
LsSQL=”INSERT INTO TABLE base VALUES(’320201’，’市中区支行’)”
EXECUTE IMMEDIATE :lsSQL；
(二)第二种格式
当执行带输入参数但没有返回结果集时的SQL语句可以采用第二种格式。该格式不仅可以动态地指定要执行的SQL语句，同时还可以动态地确定SQL语句所需要的参数值。
PREPARE DynamicStagingArea FROM SQLStatement{USING
TransactionObject}；
EXECUTE DynamicstagingArea{USING Parameterlist}；
其中：DynamicstagingArea是PowerBuilder提供的一种数据类型。PowerBuilder本身提供了一个名字为SQLSA的DynamicstagingArea类型的全局变量，用于保存要执行的动态SQL语句信息。
2 应用实例
删除base表中的满足一定条件的记录。
STRING lsCode
lsCode=”320101”
PREPARE SQLSA FROM“DELETE base WHERE code=?”；
EXECUTE SQLSA USING：lsCode；
(三)第三种格式
当执行有输入参数并且返回结果集的格式在编译时可以确定的SQL语句时可以使用第三种格式。这种格式语法比较复杂，但要比前面两种功能强，可以返回结果集。在返回结果时由于不知道满足过滤条件的记录到底有多少条，因此第三种格式通常采用游标的形式。
DECLARE cursor DYNAMIC CURSOR FOR DynamicStagingArea；
PREPARE DynamicStagingArea FROM SQLStatement{USING
TransactionObject};
OPEN DYNAMIC cursor {USING Parameterlist};
FETCH cursor INTO VariableL
其中cursor是用户所定义的游标的名字。
2 应用实例
将表base中的code字段中间两位为”01”的所有记录读取出来并分别进行相应处理。
STRING lsSQL,lsCode,lsName,lsFilter
LsFilter=”01”
LsSQL=”SELECT code，name FROM base WHERE
substring(code，3，2)=?”
DECLARE cursor_base DYNAMIC CURSOR FOR SQLSA；
PREPARE SQLSA FROM：lsSQL；
OPEN DYNAMIC cursor_base USING:lsF
FETCH cursor_base INTO:lsCode,:lsN
DO WHILE SQLCA.SQLCODE=0
　∥对满足条件的记录根据要求分别进行处理
　FETCH cursor_base INTO:lsCode,:lsN
CLOSE cursor_
(四)第四种格式
当执行有输入参数并且返回结果集的格式在编译时无法确定的SQL语句时可以使用第四种格式。有时候我们执行一些SQL语句时，不仅带有参数而且返回的结果集中有多少个字段，每个字段是什么类型的数据都不确定，这时只能使用第四种格式来实现。
PREPARE DynamicStagingArea FROM SQLStatement{USING
TransactionObject}；
DESCRIB DynamicStagingArea INTO DynamicDescriptionObject；
DECLARE cursor DYNAMIC CURSOR FOR DynamicDescriptionObject；
OPEN DYNAMIC cursor USING DESCRIPTOR
DynamicDescriptionObject；
FETCH cursor USING DESCRIPTOR DynamicDescriptionObject；
CLOSE cursor；
其中：DynamicDescriptionObject是PowerBuilder提供的一个数据类型，在PowerBuilder中提供了一个DynamicDescriptionObject类型的全局数据类型SQLDA，用来存放动态SQL语句的输入输出参数。
2 应用实例
将一个表中满足过滤条件的记录的所有字段取出来分别进行处理，表名在程序运行中由字符串变量传递，字段信息是不确定的。这里我们假设通过字符串变量中传递的表名是base。
STRING lsString,lsSQL,lsTable,lsColumn
DATETIME liTime
LsSQL=”SELECT*FROM base WHERE code like?”
PREPARE SQLSA FROM lsSQL;
DESCRIB SQLSA INTO SQLDA;∥SQLDA中含有输入参数的描述
DECLARE cursor_base DYNAMIC CURSOR FOR SQLSA;
SetDynamicparm(SQLDA,1,”32％”)∥传递参数值
OPEN DYNAMIC cursor_base USING DESCRIPTOR SQLDA;
FETCH cursor_base USING DESCRIPTOR SQLDA;
DO WHILE SALCA.SQLCODE=0
　FOR liInt=1 TO SQLDA.NumOutPuts
　CHOOSE CASE SQLDA.OutParmType[liInt]
　CASE Typestring!
lsString=GetDynamicString(SQLDA,liInt)
∥处理该字符型的字段
CASE TypeDateTime
LsDateTime=GetDynamicDateTime(SQLDA,liInt)
∥处理该日期型的字段
∥处理其他类型的字段
END CHOOSE
∥将一条记录的所有字段取完后作相应的处理
FETCH cursor_base USING DESCRIPTOR SQLDA;
CLOSE cursor_base；
二、结束语
从上面的说明和举例中可以看出动态SQL的特点是功能强，使用灵活，完全可以在程序代码中动态地生成具体要执行的SQL语句，是嵌入式SQL无法比拟的。当然动态SQL语句在运行速度上可能要比嵌入式SQL语句略慢一点，但随着计算机处理速度的大幅度提高，现在来说这一点已不成什么问题。因此，使用动态SQL语句仍是一种非常可取的处理方法
已投稿到：
以上网友发言只代表其个人观点，不代表新浪网的观点或立场。Michael McLaughlin 是爱达荷州杨百翰大学商业与通信学院计算机信息技术系的教授。他也是McLaughlin Software，LLC的创始人，同时活跃于犹他Oracle用户组。
他还是其他8本OraclePress图书的作者，例如《Oracle Database11g& MySQL 5.6 开发手册》、《Oracle Database11gPL/SQL 程序设计》和《Oracle
Database11gPL/SQL 编程实战》(清华大学出版社引进并出版)。
Michael曾在Oracle公司工作过8年多，从事顾问、开发和支持工作。在Oracle时，他领导了用于Oracle Applications 11i (11.5.8和11.5.9)的直接路径CRM升级的版本工程，还领导了PL/SQL的向前兼容性测试，对象是Oracle Applications 11i 和Oracle Database 9i。
录 第Ⅰ部分
Oracle PL/SQL第1章
Oracle PL/SQL程序开发概览 31.1
PL/SQL的历史和背景 41.2
Oracle开发架构 61.2.1
数据库 71.2.2
PL/SQL语言 81.2.3
Oracle处理架构 101.2.4
两层模型 111.2.5
n层模型 121.3
本章小结 131.4
精熟测验 13第2章
新特性 152.1
SQL新特性 162.1.1
用数据目录DIRECTORY限定LIBRARY对象 162.1.2
使用有效时间(VT)来定义表 172.1.3
增强的Oracle原生LEFT OUTER JOIN语法 182.1.4
基于序列的列的默认值 182.1.5
显式NULL插入的默认值 192.1.6
标识列 202.1.7
STRING和RAW类型大小限制的增加 212.1.8
将SQL语句的结果传递给外部程序 212.1.9
查询行限制和偏移的原生SQL支持 232.1.10
MySQL应用程序的Oracle数据库驱动程序 262.1.11
SQL CROSS APPLY、OUTER APPLY和LATERAL 262.1.12
BEQUEATH CURRENT_USER视图 282.2
PL/SQL新特性 282.2.1
调用者权限函数的缓存 292.2.2
添加PL/SQL程序单元调用者到白名单 292.2.3
直属事业部
扫描关注官方微博
扫描关注官方微信
版权所有(C)2014 清华大学出版社有限公司 京ICP备号 京公网安备48号一般的PL/SQL程序设计中，在DML和事务控制的语句中可以直接使用SQL，但是DDL语句及系统控制语句却不能在PL/SQL中直接使用，要想实现在PL/SQL中使用DDL语句及系统控制语句，可以通过使用动态SQL来实现。　　首先我们应该了解什么是动态SQL，在Oracle数据库开发PL/SQL块中我们使用的SQL分为：静态SQL语句和动态SQL语句。所谓静态SQL指在PL/SQL块中使用的SQL语句在编译时是明确的，执行的是确定对象。而动态SQL是指在PL/SQL块编译时SQL语句是不确定的，如根据用户输入的参数的不同而执行不同的操作。编译程序对动态语句部分不进行处理，只是在程序运行时动态地创建语句、对语句进行语法分析并执行该语句。　　Oracle中动态SQL可以通过本地动态SQL来执行，也可以通过DBMS_SQL包来执行。下面就这两种情况分别进行说明：　　一、本地动态SQL　　本地动态SQL是使用EXECUTE IMMEDIATE语句来实现的。　　1、本地动态SQL执行DDL语句：　　需求：根据用户输入的表名及字段名等参数动态建表。
create&or&replace&procedure&proc_test(table_name&in&varchar2,&--表名field1&in&varchar2,&--字段名datatype1&in&varchar2,&--字段类型field2&in&varchar2,&--字段名datatype2&in&varchar2&--字段类型)&asstr_sql&varchar2(500);beginstr_sql:=’create&table&’||table_name||’(’||field1||’&’||datatype1||’,’||field2||’&’||datatype2||’)’;execute&immediate&str_&--动态执行DDL语句exceptionwhen&others&thenend&;
　　以上是编译通过的存储过程代码。下面执行存储过程动态建表。
SQL&&execute&proc_test(’dinya_test’,’id’,’number(8)&not&null’,’name’,’varchar2(100)’);PL/SQL&procedure&successfully&completedSQL&&desc&dinya_Name&Type&Nullable&Default&Comments----&-------------&--------&-------&--------ID&NUMBER(8)NAME&VARCHAR2(100)&YSQL&
　　到这里，就实现了我们的需求，使用本地动态SQL根据用户输入的表名及字段名、字段类型等参数来实现动态执行DDL语句。　　2、本地动态SQL执行DML语句。　　需求：将用户输入的值插入到上例中建好的dinya_test表中。
create&or&replace&procedure&proc_insert(id&in&number,&--输入序号name&in&varchar2&--输入姓名)&asstr_sql&varchar2(500);beginstr_sql:=’insert&into&dinya_test&values(:1,:2)’;execute&immediate&str_sql&using&id,&--动态执行插入操作exceptionwhen&others&thenend&;
　　执行存储过程，插入数据到测试表中。
SQL&&execute&proc_insert(1,’dinya’);PL/SQL&procedure&successfully&completedSQL&&select&*&from&dinya_ID&NAME1&dinya
　　在上例中，本地动态SQL执行DML语句时使用了using子句，按顺序将输入的值绑定到变量，如果需要输出参数，可以在执行动态SQL的时候，使用RETURNING INTO 子句，如：
declarep_id&number:=1;v_count&beginv_string:=’select&count(*)&from&table_name&a&where&a.id=:id’;execute&immediate&v_string&into&v_count&using&p_end&;
　　更多的关于动态SQL中关于返回值及为输出输入绑定变量执行参数模式的问题，请读者自行做测试。　　二、使用DBMS_SQL包　　使用DBMS_SQL包实现动态SQL的步骤如下：A、先将要执行的SQL语句或一个语句块放到一个字符串变量中。B、使用DBMS_SQL包的parse过程来分析该字符串。C、使用DBMS_SQL包的bind_variable过程来绑定变量。D、使用DBMS_SQL包的execute函数来执行语句。　　1、使用DBMS_SQL包执行DDL语句　　需求：使用DBMS_SQL包根据用户输入的表名、字段名及字段类型建表。
create&or&replace&procedure&proc_dbms_sql(table_name&in&varchar2,&--表名field_name1&in&varchar2,&--字段名datatype1&in&varchar2,&--字段类型field_name2&in&varchar2,&--字段名datatype2&in&varchar2&--字段类型)asv_cursor&&--定义光标v_string&varchar2(200);&--定义字符串变量v_row&&--行数beginv_cursor:=dbms_sql.open_&--为处理打开光标v_string:=’create&table&’||table_name||’(’||field_name1||’&’||datatype1||’,’||field_name2||’&’||datatype2||’)’;dbms_sql.parse(v_cursor,v_string,dbms_sql.native);&--分析语句v_row:=dbms_sql.execute(v_cursor);&--执行语句dbms_sql.close_cursor(v_cursor);&--关闭光标exceptionwhen&others&thendbms_sql.close_cursor(v_cursor);&--关闭光标
　　以上过程编译通过后，执行过程创建表结构：
SQL&&execute&proc_dbms_sql(’dinya_test2’,’id’,’number(8)&not&null’,’name’,’varchar2(100)’);PL/SQL&procedure&successfully&completedSQL&&desc&dinya_test2;Name&Type&Nullable&Default&Comments----&-------------&--------&-------&--------ID&NUMBER(8)NAME&VARCHAR2(100)&YSQL&
　　2、使用DBMS_SQL包执行DML语句　　需求：使用DBMS_SQL包根据用户输入的值更新表中相对应的记录。　　查看表中已有记录：
SQL&&select&*&from&dinya_test2;ID&NAME1&Oracle2&CSDN3&ERPSQL&
　　建存储过程，并编译通过：
create&or&replace&procedure&proc_dbms_sql_update(id&number,name&varchar2)asv_cursor&&--定义光标v_string&varchar2(200);&--字符串变量v_row&&--行数beginv_cursor:=dbms_sql.open_&--为处理打开光标v_string:=’update&dinya_test2&a&set&a.name=:p_name&where&a.id=:p_id’;dbms_sql.parse(v_cursor,v_string,dbms_sql.native);&--分析语句dbms_sql.bind_variable(v_cursor,’:p_name’,name);&--绑定变量dbms_sql.bind_variable(v_cursor,’:p_id’,id);&--绑定变量v_row:=dbms_sql.execute(v_cursor);　　　　　　　　　　　--执行动态ＳＱＬdbms_sql.close_cursor(v_cursor);&--关闭光标exceptionwhen&others&thendbms_sql.close_cursor(v_cursor);&--关闭光标
　　执行过程，根据用户输入的参数更新表中的数据：
SQL&&execute&proc_dbms_sql_update(2,’csdn_dinya’);PL/SQL&procedure&successfully&completedSQL&&select&*&from&dinya_test2;ID&NAME1&Oracle2&csdn_dinya3&ERPSQL&
　　执行过程后将第二条的name字段的数据更新为新值csdn_dinya。这样就完成了使用dbms_sql包来执行DML语句的功能。　　使用DBMS_SQL中，如果要执行的动态语句不是查询语句，使用DBMS_SQL.Execute或DBMS_SQL.Variable_Value来执行，如果要执行动态语句是查询语句，则要使用DBMS_SQL.define_column定义输出变量，然后使用DBMS_SQL.Execute, DBMS_SQL.Fetch_Rows, DBMS_SQL.Column_Value及DBMS_SQL.Variable_Value来执行查询并得到结果。　　总结说明：　　在Oracle开发过程中，我们可以使用动态SQL来执行DDL语句、DML语句、事务控制语句及系统控制语句。但是需要注意的是，PL/SQL块中使用动态SQL执行DDL语句的时候与别的不同，在DDL中使用绑定变量是非法的（bind_variable(v_cursor,’:p_name’,name)），分析后不需要执行DBMS_SQL.Bind_Variable，直接将输入的变量加到字符串中即可。另外，DDL是在调用DBMS_SQL.PARSE时执行的，所以DBMS_SQL.EXECUTE也可以不用，即在上例中的v_row:=dbms_sql.execute(v_cursor)部分可以不要。
阅读(...) 评论()