静态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;