动态SQL -CSDN博客

本文链接：https://blog.csdn.net/tutugogo/article/details/742350

1. SQL SERVER

(1)
普通SQL语句可以用Exec执行
eg: Select * from tableName
Exec('select * from tableName')
sp_executesql N'select * from tableName' -- 请注意字符串前一定要加N

(2)
字段名，表名，数据库名之类作为变量时，必须用动态SQL
eg:
declare @fname varchar(20)
set @fname = '[name]'
Select @fname from sysobjects -- 错误
Exec('select ' + @fname + ' from sysobjects') -- 请注意加号前后的单引号的边上要加空格
exec sp_executesql N' select ' + @fname + ' from sysobjects'
当然将字符串改成变量的形式也可
declare @s varchar(1000)
set @s = 'select ' + @fname + ' from sysobjects'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句会报错

declare @s Nvarchar(1000) -- 注意此处改为nvarchar(1000)
set @s = 'select ' + @fname + ' from sysobjects'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句正确，
(3)
3: 输出参数
eg:
declare @num,
@sqls
set @sqls='select count(*) from ' + @servername + '.a.dbo.b'
exec(@sqls)
我如何能将exec执行的结果存入变量@num中

declare @num int,
@sqls nvarchar(4000)
set @sqls='select @a=count(*) from '+@servername+'.a.dbo.b'
exec sp_executesql @sqls,N'@a int output',@num output
select @num

2. ORACLE
1.存储过程中构造动态SQL语句并执行

PROCEDURE G_Search(P_YearNO      IN NUMBER,
                              P_ControlType IN NUMBER,
                              P_Progress    IN CHAR,
                              P_DepartID    IN VARCHAR2,
                              P_ProjectName IN NVARCHAR2,
                              C_Projects    OUT RefCursor   --为自定义游标类型
                         ) IS
        e_ErrInterruption EXCEPTION;
        v_ErrID       NUMBER; --Variable to hold the errorlog id
        v_ErrCode     NUMBER; --Variable to hold the error message code
        v_ErrText     VARCHAR2(512); --Variable to hold the error message text
        v_ErrProc     VARCHAR2(50) := 'G_Search';
        v_DepartID    VARCHAR2(16);
        v_ProjectName NVARCHAR2(128);
        v_SQL         VARCHAR2(512);
        v_Where       VARCHAR2(256);
    BEGIN

        v_SQL   := 'SELECT PROJECTID, PARENTID, PROJECTNAME ';
        v_SQL   := v_SQL || ' FROM PROJECTS A';
        v_Where := ' Where';

        -- 年度
        IF P_YearNO < 9999 THEN
            v_Where := v_Where || ' A.YearNO = ' || P_YearNO || ' And';
        ELSE
            v_Where := v_Where || ' A.YearNO < ' || P_YearNO || ' And';
        END IF;
        -- 控制类别
        IF P_ControlType = 9 THEN
            v_Where := v_Where || ' A.ControlType < 9 And';
        ELSE
            v_Where := v_Where || ' A.ControlType = ' || P_ControlType ||
                       ' And';
        END IF;
        -- 进度
        IF P_Progress < 'Z' THEN
            v_Where := v_Where || ' A.Progress = ''' || P_Progress || ''' And';
        ELSE
            v_Where := v_Where || ' A.Progress < ''' || P_Progress || ''' And';
        END IF;

        IF TRIM(P_DepartID) <> '%' THEN
            v_Where := v_Where || ' A.DepartID = ''' || P_DepartID || ''' And';
        ELSE
            v_Where := v_Where || ' A.DepartID Like ''' || P_DepartID ||
                       ''' And';
        END IF;
        --项目名称
        v_ProjectName := NVL(P_ProjectName,
                             '%');
        IF v_ProjectName <> '%' THEN
            v_ProjectName := '%' || P_ProjectName || '%';
        END IF;
        v_Where := v_Where || ' A.ProjectName Like ' || '''' || v_ProjectName ||
                   ''' And';

        v_SQL := v_SQL || v_Where;

        OPEN C_PROJECTS FOR v_SQL;

        --COMMIT;
    EXCEPTION
        --根据需要定义错误异常
        WHEN OTHERS THEN
            --ROLLBACK;
            v_ErrID   := SQLCODE;
            v_ErrText := SQLERRM;
            raise_application_error(v_ErrID,
                                    v_ErrText);
    END G_Search;

该方法只需要传递给存储过程一些参数，使用游标返回数据。参数传递效率较高，而且业务逻辑在存储过程中，调整比较方便。该方法关键的在下面的语句：
Open C_Projects For v_SQL;
它直接使用游标打开构造的查询字符串即可。

注意事项：

A）、构造的SQL语句最后不能带有分号；
B）、SQL语句中对于字符和字符串的条件需要用单引号包括起来
C）、最重要：动态SQL语句需要防止SQL注入攻击。我们采用最简单的办法，只允许一个关键词查询，将关键词中的所有空格去掉。对于多关键词，需要将他们用空格拆开，再构造。

(2)PL/SQL中使用动态SQL编程

在PL/SQL程序设计过程中，会遇到很多必须使用动态sql的地方，oracle系统所提供的DMBS_SQL包可以帮助你解决问题。
(一)介绍
DBMS_SQL系统包提供了很多函数及过程,现在简要阐述其中使用频率较高的几种:

function open_cursor:打开一个动态游标,并返回一个整型;

procedure close_cursor(c in out integer);关闭一个动态游标,参数为open_cursor所打开的游标;

procedure parse(c in integer, statement in varchar2, language_flag in integer):对动态游标所提供的sql语句进行解析,参数C表示游标,statement为sql语句,language-flag为解析sql语句所用oracle版本,一般有V6,V7跟native(在不明白所连database版本时,使用native);

procedure define_column(c in integer, position in integer, column any datatype, [column_size in integer]):定义动态游标所能得到的对应值,其中c为动态游标,positon为对应动态sql中的位置(从1开始),column为该值所对应的变量,可以为任何类型,column_size只有在column为定义长度的类型中使用如VARCHAR2,CHAR等(该过程有很多种情况,此处只对一般使用到的类型进行表述);

function execute(c in integer):执行游标,并返回处理一个整型,代表处理结果(对insert,delete,update才有意义,而对select语句而言可以忽略);

function fetch_rows(c in integer):对游标进行循环取数据,并返回一个整数,为0时表示已经取到游标末端;

procedure column_value(c in integer, position in integer, value):将所取得的游标数据赋值到相应的变量,c为游标,position为位置,value则为对应的变量;

procedure bind_variable(c in integer, name in varchar2, value):定义动态sql语句(DML)中所对应字段的值,c为游标,name为字段名称,value为字段的值;

以上是在程序中经常使用到的几个函数及过程,其他函数及过程请参照oracle所提供定义语句dbmssql.sql

(二)一般过程
对于一般的select操作,如果使用动态的sql语句则需要进行以下几个步骤:
open cursor--->parse--->define column--->excute--->fetch rows--->close cursor;
而对于dml操作(insert,update)则需要进行以下几个步骤:
open cursor--->parse--->bind variable--->execute--->close cursor;
对于delete操作只需要进行以下几个步骤:
open cursor--->parse--->execute--->close cursor;

(三)具体案例
下面就本人所开发系统中某一程序做分析
该过程为一股票技术曲线计算程序,将数据从即时数据表中取出,并按照计算曲线的公式,对这些数据进行计算,并将结果保存到技术曲线表中.
--**********************************
--procedure name:R_Ma_Main
--入口参数：PID股票代码,PEND时间,pinterval时间间隔,totab目标数据表
--调用函数：R_GetSql1,R_GetSql2
--功能：具体计算单支股票ma技术曲线
--时间：2001-06-20
--**********************************
create or replace procedure R_Ma_Main
(
pid varchar2,
pend varchar2,
pinterval varchar2,
totab varchar2
) is

--定义数组
type Date_type is table of varchar2(12) index by binary_integer;
type Index_type is table of number index by binary_integer;

TempDate Date_Type;--时间数组
TempIndex Index_Type;--股票收盘价数组
TempMa Index_Type;--ma技术曲线数据

cursor1 integer;--游标
cursor2 integer;--游标
rows_processed integer;--执行游标返回

TempInter integer;--参与计算数值个数
TempVal integer;--计算时间类型
TempSql varchar2(500);--动态sql语句
MyTime varchar2(12);--时间
MyIndex number;--数值
MidIndex number;--中间变量
i integer := 999;
j integer;
begin
TempInter := to_number(substr(pinterval,1,4));
TempVal := to_number(substr(pinterval,5,2));
TempSql := R_GetSql1(pid, pend, TempVal);--得到选择数据的sql语句

--得到当天的即时数据,并依次保存到数组中
cursor1 := dbms_sql.open_cursor; --创建游标
dbms_sql.parse(cursor1, TempSql, dbms_sql.native); --解析动态sql语句,取两个字段,时间及价格,其中时间以14位的varchar2表示
dbms_sql.define_column(cursor1, 1, MyTime, 12); --分别定义sql语句中各字段所对应变量
dbms_sql.define_column(cursor1, 2, MyIndex);
rows_processed := dbms_sql.execute(cursor1);
loop
if dbms_sql.fetch_rows(cursor1) > 0 then
begin
dbms_sql.column_value(cursor1, 1, MyTime);
dbms_sql.column_value(cursor1, 2, MyIndex);
TempDate(i) := MyTime;
TempIndex(i) := MyIndex;
i := i - 1;--按倒序的方法填入数组
end;
else
exit;
end if;
end loop;
dbms_sql.close_cursor(cursor1);

--如果取得的数据量不够计算个数,则跳出程序
if i > 999-TempInter then
goto JumpLess;
end if;

--初始化中间变量
MidIndex := 0;
TempIndex(i) := 0;
for j in i..i+TempInter-1 loop
MidIndex := MidIndex + TempIndex(j);
end loop;

--依次对当天数据计算ma值,并保存到ma数组中
for j in i+TempInter..999 loop
MidIndex := MidIndex - TempIndex(j-TempInter) + TempIndex(j);
TempMa(j) := MidIndex/TempInter;
end loop;

if TempVal < 6 then--如果计算的是分钟跟天的ma技术曲线
begin
cursor2 := dbms_sql.open_cursor;
TempSql := 'insert into ' || totab || ' values(:r_no, :i_interval, :i_time, :i_index)';
dbms_sql.parse(cursor2, TempSql, dbms_sql.native);
for j in i+TempInter..999 loop
dbms_sql.bind_variable(cursor2, 'r_no', pid);
dbms_sql.bind_variable(cursor2, 'i_interval', pinterval);
dbms_sql.bind_variable(cursor2, 'i_time', TempDate(j));
dbms_sql.bind_variable(cursor2, 'i_index', TempMa(j));
rows_processed := dbms_sql.execute(cursor2);--插入数据
end loop;
end;
end if;
commit;
dbms_sql.close_cursor(cursor2);
--数据量不足跳出
<>
null;

--exception处理,无关本话题
end;
/
注:oracle8i以后,一般建议使用execute immediate,和open ref cursor了,
使用dbms_sql效率相对来说比较低.

(3)动态SQL语句
执行动态 SQL 语句的两种形式：
1.使用 EXECUTE IMMEDIATE 语句
2.使用 OPEN-FOR, FETCH, CLOSE 语句
在下列情况下使用动态 SQL 语句：
1.在 PL/SQL 中执行 DDL 语句（如 CREATE）,DCL 语句（如 GRANT），或者是会话控制语句（如 ALTER SESSION）等；
2.想更灵活地编写程序，比如说，在运行时间才确定所要处理的对象的名称，或者在 SELECT 语句的 WHERE 子句中使用不同条件；
3.想获得比使用 DBMS_SQL 来执行动态 SQL 更好的性能。

EXECUTE IMMEDIATE dynamic_string
[INTO {define_variable[, define_variable]... | record}]
[USING [IN | OUT | IN OUT] bind_argument
[, [IN | OUT | IN OUT] bind_argument]...]
[{RETURNING | RETURN} INTO bind_argument[, bind_argument]...];

DECLARE
   sql_stmt    VARCHAR2(200);
   plsql_block VARCHAR2(500);
   emp_id      NUMBER(4) := 7566;
   salary      NUMBER(7,2);
   dept_id     NUMBER(2) := 50;
   dept_name   VARCHAR2(14) := 'PERSONNEL';
   location    VARCHAR2(13) := 'DALLAS';
   emp_rec     emp%ROWTYPE;
BEGIN
   EXECUTE IMMEDIATE 'CREATE TABLE bonus (id NUMBER, amt NUMBER)'; -- 执行 DDL 语句

   plsql_block := 'BEGIN emp_pkg.raise_salary(:id, :amt); END;'; -- 执行 PL/SQL 块
   EXECUTE IMMEDIATE plsql_block USING 7788, 500;

   sql_stmt := 'UPDATE emp SET sal = 2000 WHERE empno = :1
      RETURNING sal INTO :2';
   EXECUTE IMMEDIATE sql_stmt USING emp_id RETURNING INTO salary;

EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE TRUE'; -- 执行会话控制语句
END;

下面的例子使得程序可以在运行时间确定要删除的表的名称，以及条件。
CREATE PROCEDURE delete_rows (
   table_name IN VARCHAR2,
   condition IN VARCHAR2 DEFAULT NULL) AS
   where_clause VARCHAR2(100) := ' WHERE ' || condition;
BEGIN
   IF condition IS NULL THEN where_clause := NULL; END IF;
   EXECUTE IMMEDIATE 'DELETE FROM ' || table_name || where_clause;
EXCEPTION
   ...
END;

使用 OPEN-FOR, FETCH, CLOSE，包含动态 SQL 语句的动态游标。
DECLARE
   TYPE EmpCurTyp IS REF CURSOR;
   emp_cv   EmpCurTyp;
   emp_rec emp%ROWTYPE;
   sql_stmt VARCHAR2(200);
   my_job   VARCHAR2(15) := 'CLERK';
BEGIN
   sql_stmt := 'SELECT * FROM emp WHERE job = :j';
   OPEN emp_cv FOR sql_stmt USING my_job;
   LOOP
      FETCH emp_cv INTO emp_rec;
      EXIT WHEN emp_cv%NOTFOUND;
      -- process record
   END LOOP;
   CLOSE emp_cv;
END;

技巧和注意事项
1.考虑性能。下面的两个例子中，第一个的性能明显非常差，第二个由于使用了绑定变量而获得了较好的性能。
CREATE PROCEDURE fire_employee (emp_id NUMBER) AS
BEGIN
EXECUTE IMMEDIATE
'DELETE FROM emp WHERE empno = ' || TO_CHAR(emp_id);
END;

CREATE PROCEDURE fire_employee (emp_id NUMBER) AS
BEGIN
EXECUTE IMMEDIATE
'DELETE FROM emp WHERE empno = :num' USING emp_id;
END;

2.如何传递表名。第一个例子是不可执行的，如果想要向 PL/SQL 传递表名，就仿照第二个例子。
CREATE PROCEDURE drop_table (table_name IN VARCHAR2) AS
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE :tab' USING table_name;
END;

CREATE PROCEDURE drop_table (table_name IN VARCHAR2) AS
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE ' || table_name;
END;

3.应用实例
------------------------------------------------------
动态SQL语句在SQLServer中非固定行的转列应用
社区里有人提问一个行转列的应用，在SQLServer中都是用Case的，我随便答了一下，由于是非固定行，有网友给我发消息问怎么实现，详细来说一下。

Answer：
表
F1      F2
jack    book1
jack    book2
jack    book3
mary    book4
mary    book5
...

转化为
F1　　　　F2　　　　F3　　　　F4　　　　F5
jack       book1     book2     book3
mary      book4     book5
billy       book6     book7

--------------------------------------------

测试过程：
--------------------------------------------

create table Test
(F1 char(10),
F2 char(10))

--测试表

insert into Test
select 'jack' F1,'book1' F2
union
select 'jack' F1,'book2' F2
union
select 'jack' F1,'book3' F2
union
select 'Mary' F1,'book4' F2
union
select 'Mary' F1,'book5' F2
union
select 'Mike' F1,'book1' F2
union
select 'Mike' F1,'book5' F2
union
select 'Mike' F1,'book7' F2
union
select 'Mike' F1,'book9' F2

--插入数据

select id=identity(int,0,1),f1,f2 into #t from test

select a.f1,a.f2,a.id,cc ,N=
case when (id>cc) then cast(id-cc-minn+1 as Char(10))
when (id<=cc) then cast(id+1 as Char(10))
end
into #Temp
from #t a,
(select f1,cc,minn from
(select f1,count(*)as cc,min(id)-count(*) as minn from #t group by f1) t)b
where a.f1=b.f1

--构造两个临时表，由于要用到行号，所以必须要第一个临时表，第二个可以不用

select * from #Temp

--这个表笔原来的多一个字段，每个人的第n条记录

DECLARE @SQL VARCHAR(8000)
SET @SQL='SELECT f1 姓名'
SELECT @SQL= @SQL+ ',MIN(CASE WHEN N = ''' + N + ''' THEN F2 END) [F' + N + ']' FROM (SELECT DISTINCT N FROM #Temp) A
SET @SQL=@SQL+' FROM #Temp GROUP BY F1'
EXEC (@SQL)

--一条动态SQL语句

drop table #t
drop table #Temp
drop table Test

/*
jack       book1      1
jack       book2      2
jack       book3      3
Mary       book4      1
Mary       book5      2
Mike       book1      1
Mike       book5      2
Mike       book7      3
Mike       book9      4
--------Temp表数据*/

/*
jack       book1      book2      book3      NULL
Mary       book4      book5      NULL NULL
Mike       book1      book5      book7      book9

--------最终结果*/