动态sql参数
‘壹’ 动态sql的使用
在介绍动态SQL前我们先看看什么是静态SQL
静态SQL
静态 SQL 语句一般用于嵌入式 SQL 应用中，在程序运行前，SQL 语句必须是确定的，例如 SQL 语句中涉及的列名和表名必须是存在的。静态 SQL 语句的编译是在应用程序运行前进行的，编译的结果会存储在数据库内部。而后程序运行时，数据库将直接执行编译好的 SQL 语句，降低运行时的开销。
动态SQL
动态 SQL 语句是在应用程序运行时被编译和执行的，例如，使用 DB2 的交互式工具 CLP 访问数据库时，用户输入的 SQL 语句是不确定的，因此 SQL 语句只能被动态地编译。动态 SQL 的应用较多，常见的 CLI 和 JDBC 应用程序都使用动态 SQL。
动态SQL作用
动态SQL执行方法
使用EXEC(EXECUTE的缩写)命令和使用SP_EXECUTERSQL。
EXEC命令执行
语法
注：EXECUTE 命令有两个用途，一个是用来执行存储过程，另一个是执行动态SQL
不带参数示例
在变量@SQL中保存了一个字符串，该字符串中包含一条查询语句，再用EXEC调用保存在变量中的批处理代码，我们可以这样写SQL：
EXEC ('SELECT * FROM Customers')
结果如下：
与我们直接执行SELECT * FROM Customers一样。
带参数示例
还是上面的示例，我们换一种写法
DECLARE @SQL AS VARCHAR(100);
DECLARE @Column AS VARCHAR(20);
SET @Column = '姓名'
SET @SQL = 'SELECT ' + @Column + ' FROM Customers'
EXEC (@SQL)
结果如下：
SP_EXECUTERSQL执行
语法
注意：SP_EXECUTERSQL是继EXEC后另一种执行动态SQL的方法。使用这个存储过程更加安全和灵活，因为它支持输入和输出参数。注意的是，与EXEC不同的是,SP_EXECUTERSQL只支持使用Unicode字符串作为其输入的批处理代码。
示例
构造了一个对Customers表进行查询的批处理代码，在其查询过滤条件中使用一个输入参数@CusID
DECLARE @SQL AS NVARCHAR(100);
SET @SQL=N'SELECT * FROM Customers
WHERE 客户ID=@CusID;'
EXEC SP_EXECUTESQL
@STMT=@SQL,
@PARMS=N'@CusID AS INT',
@CusID=1;
结果如下：
代码中将输入参数取值指定为1，但即使采用不同的值在运行这段代码，代码字符串仍然保存相同。这样就可以增加重用以前缓存过的执行计划的机会
‘贰’ 存储过程中如何执行带输出参数的动态SQL
SQL Server存储过程中执行带输出参数的动态sql是很多人经常碰到的问题，比如根据一些条件查询列表，并返回记录数等。下面是一个参考示例，查询用户列表，它可以利用临时表实现翻页，并带有死锁和超时检测功能。 CREATE PRocere pUserList ( @UserType char(2), @pagenum int, @perpagesize int, @pagetotal int out, @rowcount int out ) as set nocount on DECLARE @Err INT,@ErrCounter INT declare @sql nvarchar(2000) --声明动态sql执行语句 declare @pagecount int --当前页数 declare @sWhere nvarchar(200) declare @sOrder nvarchar(100) set @sWhere = ' where 1=1 ' if not(@UserType is null) set @sWhere = @sWhere + ' and UserType = ' + @UserType set @sOrder = ' order by UserID ' --取得当前数据库的记录总数 declare @row_num int LockTimeOutRetry: --创建临时表，作为数据过滤 create table #change (T_id int) set @sql = 'select @row_num=count(*) from dbo.[User]' + @sWhere exec sp_executesql @sql,N'@row_num int output', @row_num output if @row_num % @perpagesize =0 set @pagetotal = @row_num/@perpagesize else set @pagetotal = @row_num/@perpagesize + 1 set @rowcount = @row_num if @row_num > @perpagesize begin set @row_num = @pagenum * @perpagesize if @row_num = @perpagesize begin set @sql = N'select top ' + cast(@perpagesize as varchar) + ' UserID,LoginName,RealName from dbo.[User]' + @sWhere + @sOrder exec sp_executesql @sql SET @Err = @@ERROR IF @Err <> 0 GOTO ErrorHandler return 0 end else begin set @row_num = (@pagenum-1) * @perpagesize set @pagecount = @row_num set @sql=N'insert #change (T_id) select top ' + cast(@pagecount as varchar) + ' UserID from dbo. [User] '+@sWhere+' and UserID not in (select T_id from #change)' + @sOrder exec sp_executesql @sql set @sql = N'select top ' + cast(@perpagesize as varchar) + ' UserID,LoginName,RealName from dbo.[User] '+@sWhere+' and UserID not in (select T_id from #change)' + @sOrder exec sp_executesql @sql SET @Err = @@ERROR IF @Err <> 0 GOTO ErrorHandler return 0 end end else begin set @sql = 'select UserID,LoginName,RealName from dbo.[User]' + @sWhere + @sOrder exec sp_executesql @sql SET @Err = @@ERROR IF @Err <> 0 GOTO ErrorHandler return 0 end ErrorHandler: IF (@Err = 1222 OR @Err = 1205) AND @ErrCounter = 5 BEGIN RAISERROR ('Unable to Lock Data after five attempts.', 16,1) return -100 END IF @Err = 1222 OR @Err = 1205 -- Lock Timeout / Deadlock BEGIN WAITFOR DELAY '00:00:00.25' SET @ErrCounter = @ErrCounter + 1 GOTO LockTimeOutRetry END -- else unknown error RAISERROR (@err, 16,1) WITH LOG return -100 GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO
‘叁’ 动态sql中如何执行 带参数的存储过程
exec
sp_executesql
@sqlGetCount,N'@Counts
int
out
',@Counts
out
实际上
就是通过这个sp_executesql来执行存储过程时，会定义变量
@Counts
，然后执行你的语句把值放到@Counts
里，最后返回。
另外，本质上这个和exec语句是一样的都是动态执行sql。
‘肆’ mybatis 动态sql和参数
动态SQL，就是根据条件拼接的条件吧。
比如：if、where等等条件，都是动态sql的标签。
参数：就是#$两个标签，注意下$可能会导致sql注入的问题就可以了。
‘伍’ 存储过程中动态SQL中的传入参数为datetime类型的问题
你先调试一下，少了个@和begin、end
另外，拼接变量不能在字符串内啊，这样没用
create procere [dbo].[baobiao1]
@date1 datetime,
@date2 datetime
as
set nocount on
begin
Set XACT_ABORT ON;
Begin Tran
Declare @SQL1 varchar(8000)
Declare @SQL2 varchar(8000)
--Set @SQL='Create view A as select ....'
--Exec(@SQL)
if exists (SELECT * FROM sysobjects WHERE name = 'report1')
begin
drop view report1
end
set @SQL1='create view report1 as select s.englishName,s.belongzu,count(*) kaitai,
jiachan=(case s.shiftName when ''甲'' then sum(s.realproc) else 0 end),
jiachaochan=(case s.shiftName when ''甲'' then sum(s.realproc-s.singleplan*s.workminute/60) else 0 end),
yichan=(case s.shiftName when ''乙'' then sum(s.realproc) else 0 end),
yichaochan=(case s.shiftName when ''乙'' then sum(s.realproc-s.singleplan*s.workminute/60) else 0 end),
bingchan=(case s.shiftName when''丙''then sum(s.realproc) else 0 end),
bingchaochan=(case s.shiftName when''丙''then sum(s.realproc-s.singleplan*s.workminute/60) else 0 end),
dingchan=(case s.shiftName when''丁''then sum(s.realproc) else 0 end),
dingchaochan=(case s.shiftName when''丁''then sum(s.realproc-s.singleplan*s.workminute/60) else 0 end),
(case when s.shiftName =''甲''then sum(p.cibushu) else 0 end) jiacibu,
(case when s.shiftName =''乙''then sum(p.cibushu) else 0 end) yicibu,
(case when s.shiftName =''丙''then sum(p.cibushu) else 0 end) bingcibu,
(case when s.shiftName =''丁''then sum(p.cibushu) else 0 end) dingcibu,
(case when s.shiftName =''甲''then sum(p.kaijianshu) else 0 end) jiakaijian,
(case when s.shiftName =''乙''then sum(p.kaijianshu) else 0 end) yikaijian,
(case when s.shiftName =''丙''then sum(p.kaijianshu) else 0 end) bingkaijian,
(case when s.shiftName =''丁''then sum(p.kaijianshu) else 0 end) dingkaijian
from HistoryPro1 s left outer join kaijianjicibu2 p on s.wholeDate = p.wholeDate AND
s.MachineID = p.MachineID AND
s.shiftName = p.shiftName where s.wholeDate >=‘+@date1+‘ and s.wholeDate <=’+@date2=‘ group by s.englishName,s.belongzu,s.shiftName'
exec(@SQL1)
commit Tran
end
set nocount on
GO
‘陆’ 如何支持动态拼接SQL的参数化查询求解答
remarkvarchar(100))go--写入部分测试数据insertintooswica_test_table_1select1,
'oswica',''insertintooswica_test_table_1select2,
'stone',''insertintooswica_test_table_1select3,
'nana',''insertintooswica_test_table_1select4,
'nana',''go--建立存储过程createprocoswica_proc_querytestdata(@whereSqlnvarchar(max),
@paramNameListnvarchar(max),
@paramValueListnvarchar(max))asbegindeclare@exesqlnvarchar(max)
--生成查询语句set@exesql='EXECsp_executesqlN'
'select*fromoswica_test_table_1'
+@whereSql+''
',N'''+@paramNameList+''
','
+@paramValueList+''
print@exesqlexec(@exesql)endgo--执行存储过程execoswica_proc_querytestdataN
'whereid=@idandname=@name',N
'@idint,@namevarchar(50)',N
'@id=1,@name=''oswica'''go--结果
--EXECsp_executesqlN'select*fromoswica_test_table_1whereid=@idandname=@name',N'@idint,@namevarchar(50)',@id=1,@name='oswica'
--idnameremark
-------------------------------------------------------------------------------------------------------------------------------------------------------------------1oswica--说明
--实际上我们不推荐直接在存储过程中这么使用。因为这里还是存在执行代码里面有拼接sql的情况。
--最好的办法是在程序代码里面动态拼接好然后使用参数方式动态的根据实际参数个数传入参数，已
--实现即能够兼容参数化查询又能够拼接字符串。----建议
--按照上面的说法，我们可以拼接如下sql：
--select*fromoswica_test_table_1whereid=@idandname=@name
--然后按照拼接的参数个数使用SqlCommand的Parameters动态的添加2个参数：@id和@name来达到
--目的。而在编程语言中实现动态添加参数相对比较容易，或者通过实现sqlserver的CLR存储过程来
--达到这一目的。
‘柒’ Oracle 动态SQL中如何参数化表名
表名可用变量，但一般需要用到动态sql，举例如下：
declare
v_date varchar2(8);--定义日期变量
v_sql varchar2(2000);--定义动态sql
v_tablename varchar2(20);--定义动态表名
begin
select to_char(sysdate,'yyyymmdd') into v_date from al;--取日期变量
v_tablename := 'T_'||v_date;--为动态表命名
v_sql := 'create table '||v_tablename||'
(id int,
name varchar2(20))';--为动态sql赋值
dbms_output.put_line(v_sql);--打印sql语句
execute immediate v_sql;--执行动态sql
end;
执行以后，就会生成以日期命名的表。
‘捌’ 关于Java的sql动态参数
String sql = "select * from stuInfo where id = ? and name = ?";
Object[] params = {id,name};
public int executeSQL(String sql,Object[] params){
PreparedStatement psmt =conn.prepareStatement(sql);
if(params != null){
for(int i = 0 ;i<params.length;i++){
psmt.setObject((i+1),params[i]);
}
}
}
‘玖’ 动态sql语句如何输出多个参数
DECLARE @FORM_KIND VARCHAR(50)
DECLARE @FORM_NO VARCHAR(50)
DECLARE @Sql NVARCHAR(1000)
declare @X nvarchar(500)
declare @Y nvarchar(500)
set @form_kind='SYS.FORM.008';
set @form_no=12500;
SET @Sql ='select 1 as num, tah.travel_purpose || ''$$'' || tah.bu_code || ''$$'' || tah.ou_code document_summary from bpm.bpm_tes_flow f,bpm.bpm_tes_ta_head tah where tah.ta_head_id=f.source_id and f.source_category=''TA''
and f.form_kind='''+@form_kind+''' and f.form_no=12500';
print @Sql
SET @Sql = 'SELECT @A=document_summary,@B=num FROM OPENQUERY(ORACLE_ERP, ''' + REPLACE(@Sql, '''', '''''') + ''')'
exec sp_executesql @Sql,N'@A NVARCHAR(500) output,@B NVARCHAR(50) OUTPUT',@X output,@Y OUTPUT
PRINT @X+'============'+@Y