sqlserver、db2、oracle存储过程动态sql语句示例-白红宇

sqlserver、db2、oracle存储过程动态sql语句示例

阅读量：4188 次

发布时间：2019-05-26

本文共 3037 字，大约阅读时间需要 10 分钟。

                         
CREATE
OR
REPLACE
PROCEDURE
a_test
AS
    
t_sql   VARCHAR2(2000);
    
t_a     VARCHAR2(20);
    
t_b     VARCHAR2(20);
    
t_c     VARCHAR2(20);
    
t_d     VARCHAR2(20);
BEGIN
    
t_c   := 
'f'
;
    
t_d   := 
'g'
;
    
    
    
--这里可为insert 等任何sql语句.
    
t_sql := 
'SELECT MAX(a), MAX(b) FROM t1 WHERE c = :tempC OR c = :tempD'
;
    
    
EXECUTE
IMMEDIATE t_sql
    
INTO
t_a, t_b  
--如果不需要查询赋值，这里不用写 INTO XXX语句.
    
USING t_c, t_d  
-- 如果不需要使用变量，不用写USING XXX语句.
    
;
END
a_test;
/
        
      

                         
--带返回游标的动态执行语句.
CREATE
OR
REPLACE
PROCEDURE
a_test2
(
    
o_cursor 
OUT
SYS_REFCURSOR
)
AS
    
t_sql   VARCHAR2(2000);
    
t_a     VARCHAR2(20);
    
t_b     VARCHAR2(20);
    
t_c     VARCHAR2(20);
    
t_d     VARCHAR2(20);
BEGIN
    
t_c   := 
'f'
;
    
t_d   := 
'g'
;
    
    
    
--这里可为insert 等任何sql语句.
    
t_sql := 
'SELECT * FROM t1 WHERE c = :tempC OR c = :tempD'
;
    
OPEN
o_cursor 
FOR
t_sql
    
USING t_c, t_d   
-- 同样如果不需要使用变量，不用写USING XXX语句.
    
;
END
a_test2;
/
        
      

SQL Server

                         
CREATE
PROCEDURE
a_test
AS
    
DECLARE
@t_sql   NVARCHAR(2000);  
--sql server 动态语句要申明为NVARCHAR类型.
    
DECLARE
@t_a     
VARCHAR
(20);
    
DECLARE
@t_b     
VARCHAR
(20);
    
DECLARE
@t_c     
VARCHAR
(20);
    
DECLARE
@t_d     
VARCHAR
(20);
BEGIN
    
SET
@t_c = 
'f'
;
    
SET
@t_d = 
'g'
;
    
    
--这里可为insert 等任何sql语句.
    
SET
@t_sql = 
'SELECT @tempA = MAX(a), @tempB = MAX(b) FROM t1 WHERE c = @tempC OR c = @tempD'
;
    
    
--不可在EXECUTE SP_EXECUTESQL后边拼接字符串
    
EXECUTE
SP_EXECUTESQL @t_sql
    
    
--如果不带参数，以下申明变量及传入参数都不需要写.
    
--申明变量类型及出入参.必须一行写完，不能换行.
    
,N
'@tempA VARCHAR(20) OUT, @tempB VARCHAR(20) OUT, @tempC VARCHAR(20), @tempD VARCHAR(20)'
    
-- 参数值.  传入变量的顺序要与申明变量的顺序一致，
    
,@t_a 
OUT
, @t_b 
OUT
, @t_c, @t_d
    
;
END
GO
--sql server返回游标与普通的语句一样，直接将t_sql赋值成 select * from XXX 即可.
        
      

Db2

                         
CREATE
PROCEDURE
a_test
(
    
v_c           
VARCHAR
(20)
)
BEGIN
    
DECLARE
t_sql VARCHAR2(2000);
    
DECLARE
t_a   VARCHAR2(20);
    
DECLARE
t_b   VARCHAR2(20);
    
DECLARE
t_c   VARCHAR2(20);
    
DECLARE
t_d   VARCHAR2(20);
    
-- FOR 后边的t_stmt要与下边的 prepare后的变量一致，其类型为 statement.
    
DECLARE
t_cur 
CURSOR
FOR
t_stmt;
    
SET
t_c = 
'f'
;
    
SET
t_d = 
'g'
;
    
    
--这里可为insert 等任何sql语句.
    
SET
t_sql = 
'SELECT MAX(a), MAX(b) FROM t1 WHERE c = :tempC OR c = :tempD'
;
    
    
--我目前在这里没找到其它方式来替代，只能使用游标读取：
    
PREPARE
t_stmt 
FROM
t_sql;
    
    
OPEN
t_cur
    
--如果不需要使用变量，不用写USING XXX语句.
    
USING t_c, t_d
    
;
    
    
FETCH
t_cur 
INTO
t_a, t_b;
    
CLOSE
t_cur;
END
        
      

                         
/*
带游标返回的动态语句与普通语句一样，只是申明游标时，要申明返回类型的游标
并且在begin关键字之前需要插入：
DYNAMIC RESULT SETS 1
LANGUAGE SQL
*/
CREATE
PROCEDURE
a_test2
(
    
v_c           
VARCHAR
(20)
)
DYNAMIC
RESULT SETS 1
LANGUAGE SQL
BEGIN
    
DECLARE
t_sql VARCHAR2(2000);
    
DECLARE
t_a   VARCHAR2(20);
    
DECLARE
t_b   VARCHAR2(20);
    
DECLARE
t_c   VARCHAR2(20);
    
DECLARE
t_d   VARCHAR2(20);
    
-- FOR 后边的t_stmt要与下边的 prepare后的变量一致，其类型为 statement.
    
DECLARE
t_cur 
CURSOR
WITH
RETURN
FOR
t_stmt;
    
SET
t_c = 
'f'
;
    
SET
t_d = 
'g'
;
    
    
--这里可为insert 等任何sql语句.
    
SET
t_sql = 
'SELECT MAX(a), MAX(b) FROM t1 WHERE c = :tempC OR c = :tempD'
;
    
    
PREPARE
t_stmt 
FROM
t_sql;
    
    
OPEN
t_cur
    
--如果不需要使用变量，不用写USING XXX语句.
    
USING t_c, t_d
    
;
END
        
      