PLSQL语句中将变量的值传递给静态SQL语句做查询条件的问题

本文链接：https://blog.youkuaiyun.com/zxl881203/article/details/88818841

本文探讨了在PLSQL中遇到的问题，即如何将变量的值作为静态SQL查询条件。错误示例展示了直接使用局部变量导致的失败，强调了静态SQL语句不支持局部变量，但允许全局变量。解决方案仍在探索中，作者计划在找到答案后分享。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

静态SQL语句里面禁止插入局部变量（全局变量且初始化后的则可以）

错误写法：


DECLARE
 TABLEFROM VARCHAR2(200):= 'T_CRCHARGER';
 TABLETO VARCHAR2(200):= 'T_MAINLANDSELLCONTRACT';
 CURSOR TESTC1 IS SELECT  A.COLUMN_NAME ,A.DATA_TYPE FROM user_tab_cols A  WHERE TABLE_NAME = TABLEFROM;
 COLNAME user_tab_cols.COLUMN_NAME % TYPE;
 COLTYPE  user_tab_cols.DATA_TYPE %  TYPE;
 COLNAME_B user_tab_cols.COLUMN_NAME % TYPE;
 COLTYPE_B user_tab_cols.DATA_TYPE % TYPE;
 SQLSTR   VARCHAR2(200);
 SQLSTRALL   VARCHAR2(200);
BEGIN
OPEN TESTC1;
    SQLSTR := '';
    LOOP
    FETCH TESTC1 INTO COLNAME,COLTYPE;
    EXIT WHEN TESTC1 % NOTFOUND;
        dbms_output.put_line('新数据:'||COLNAME_B||',新类型:'||COLTYPE_B );
        SELECT A.COLUMN_NAME,A.DATA_TYPE INTO COLNAME_B,COLTYPE_B FROM user_tab_cols A  WHERE TABLE_NAME =  TABLETO AND COLUMN_NAME = TESTC1.COLNAME;
    END LOOP;
CLOSE TESTC1;
    
COMMIT;
END;

寻找错误位置：变量 aaaa 就可以传递进去

declare

 aaaa  VARCHAR2(20)  := 'ZKHH';
  COLNAME_B user_tab_cols.COLUMN_NAME % TYPE;
 COLTYPE_B user_tab_cols.DATA_TYPE % TYPE;
begin

    SELECT A.COLUMN_NAME,A.DATA_TYPE into COLNAME_B,COLTYPE_B FROM user_tab_cols A  WHERE TABLE_NAME = 'T_MAINLANDSELLCONTRACT' AND COLUMN_NAME= aaaa ;
    dbms_output.put_line('新数据:'||COLNAME_B||',新类型:'||COLTYPE_B );

end;

百思不得其解，中文搜索不给力，最后 StackOverFlow有解决思路来，我还在摸索，写好了在公布

DECLARE
 --TYPE EMP_REC IS TABLE OF user_tab_cols%ROWTYPE;
 TYPE EMP_REC111 IS RECORD         --声明记录类型
   (
      COLUMN_NAME      user_tab_cols.COLUMN_NAME%TYPE
     ,DATA_TYPE      user_tab_cols.DATA_TYPE%TYPE
   );
 TYPE EMP_REC IS TABLE OF EMP_REC111; --记录类型的表类型
 EMP_REC2 EMP_REC;
 TABLEFROM VARCHAR2(200):= 'T_CRCHARGER';
 TABLETO VARCHAR2(200):= 'T_MAINLANDSELLCONTRACT';
 CURSOR TESTC1 IS SELECT  A.COLUMN_NAME ,A.DATA_TYPE FROM user_tab_cols A  WHERE TABLE_NAME = TABLEFROM;
 COLNAME user_tab_cols.COLUMN_NAME % TYPE;
 COLTYPE  user_tab_cols.DATA_TYPE %  TYPE;
 COLNAME_B user_tab_cols.COLUMN_NAME % TYPE;
 COLTYPE_B user_tab_cols.DATA_TYPE % TYPE;
 SQLSTR1   VARCHAR2(200);
 SQLSTR2   VARCHAR2(200);
 SQLSTRALL   VARCHAR2(2000);
 stringtemp varchar2(300) := '';
 SQLSTR varchar2(2000) :='';
    procedure test1(colnametest in VARCHAR2) as 
    begin
        dbms_output.put_line('colnametest is' ||colnametest);
    end test1;
BEGIN
OPEN TESTC1;
    SQLSTR2 := '';
    LOOP
    FETCH TESTC1 INTO COLNAME,COLTYPE;
    EXIT WHEN TESTC1 % NOTFOUND;
    --dbms_output.put_line('数据:'||COLNAME||',类型:'||COLTYPE );
    --SELECT A.COLUMN_NAME,A.DATA_TYPE INTO COLNAME_B,COLTYPE_B FROM user_tab_cols A  WHERE TABLE_NAME =  TABLETO AND COLUMN_NAME = COLNAME;
    stringtemp := 'SELECT COLUMN_NAME,DATA_TYPE FROM user_tab_cols  WHERE TABLE_NAME = :TABLETO AND COLUMN_NAME = :COLNAME';    
    EXECUTE IMMEDIATE stringtemp BULK COLLECT 
        into EMP_REC2
        using TABLETO,COLNAME;
    FOR I IN 1 .. EMP_REC2.COUNT LOOP
            --DBMS_OUTPUT.PUT_LINE('EMPLOYEE_ID = ' || EMP_REC2(I).COLUMN_NAME);
                                 COLNAME_B:= EMP_REC2(I).COLUMN_NAME;
                                 COLTYPE_B := EMP_REC2(I).DATA_TYPE;
        END LOOP;

    --dbms_output.put_line('SELECT A.'||COLUMN_NAME||', A.'||DATA_TYPE|| 'INTO  COLNAME_B ,  COLTYPE_B FROM '|| user_tab_cols ||' A'||  ' WHERE TABLE_NAME ='||  TABLETO||' AND COLUMN_NAME ='|| COLNAME);
    --IF colname <> COLNAME_B AND COLTYPE_B <> COLTYPE THEN
    --IF COLTYPE_B <> COLTYPE THEN
    IF colname <> COLNAME_B THEN
        dbms_output.put_line('新数据:'||COLNAME_B||',新类型:'||COLTYPE_B );
        dbms_output.put_line('OLD数据:'||COLNAME||',OLD类型:'||COLTYPE );
    end if;    
    --SQLSTR := SQLSTR ||COLNAME_B || ',';
    END LOOP;
    --SQLSTRALL := 'SELECT' ||' '|| SQLSTR ||' '|| 'INTO' ||' '|| TABLETO || 'FROM' || TABLEFROM;
    --dbms_output.put_line(SQLSTRALL );
CLOSE TESTC1;
COMMIT;
END;