动态SQL问题，请高手赐教

Question

  朋友们，我想实现一个东东，但是自己写了很多种存储过程结果都有错。请高手帮忙呀。
我发现，动态拼接SQL语句的时候，FROM 后面不让加变量名或者 存储过程IN 的那个参数。很郁闷啊
环境：ORACLE10G WINDOWS平台。
要求：    
select count(*) from tablename where columnname is not null; --判断每个字段非NULL值的个数。
--这里不分组 也不要用 select count(columnname) from tablename where columnname is not null;这样有错。这里我不想说为什么，如果要试验，大家可以自己亲自试验一下。
                                   ---tablename 动态  columnname 动态
把数据写入这样一张表中create table TEST_COLCOUNT
(
  TABLE_NAME          VARCHAR2(200),   --表名
  TABLE_NAME_COMMENTS VARCHAR2(200),   --表名中文含义
  TABLE_NAME_NUM_ROWS VARCHAR2(200),   --表中共多少行数据  系统表 USER_TABLES 里面已经提供
  COLUMN_NAME         VARCHAR2(200),   --字段名
  COLUMN_COMMENTS     VARCHAR2(200),   --字段中文含义
  COLUMN_ISNOTNULL_COUNT        NUMBER, --IS NOT NULL 出来的值  （关键算这里的值）
  COLUMN_NULL_COUNT  NUMBER,            --IS NULL 出来的值       （关键算这里的值）
  EXECDATE            DATE              --执行时间 用SYSDATE 就可以了。
)
提示:  
select * from user_tables;   --能得到表明
select * from user_tab_comments; --能得到表明的中文含义
select * from user_tab_columns;  --能得到所有表中字段
select * from user_col_comments;  --得到表中字段的中文含义
我觉得我已经说的很详细了。这个事情说起来很容易，想起来很容易，但是真正实现，好像问题很多。
无论用任何方法实现，我希望朋友们能对自己贴出来的代码负责一点哈，最好自己测试过真的能实现。
我的分数不多。希望有时间和喜欢探讨ORACLE技术方面的朋友，帮忙看看。可以暂时不考虑效率，不过不要太夸张就行。
存储过程的开头 我先写一下吧。这样能让朋友们更能理解我的意思哈。CREATE OR REPLACE PROCEDURE CX_TEST (TABLENAME IN VARCHAR)
IS
--变量、游标 声明部分
BEGIN--DML OR DDL END CX_TEST;再次真诚请求高人赐教。如果哪位高手纯粹为了分而完成这个，我可以加分。

Answer 1

这个动态的SQL  MSSQL的我已经写出来了。ORACLE 里面临时表太难用。实在写不出来了。还请朋友们多帮忙想想办法呀。

Answer 2

   v_notnull_cnt  number := 0;
   execute immediate 'select count(*) from '||TABLENAME||' where '||columnname|| ' is not null' into v_notnull_cnt;
   execute immediate 'update test_colcount set COLUMN_ISNOTNULL_COUNT = '||v_notnull_cnt||' where TABLE_NAME = '||TABLENAME;

Answer 3

其他都与这个类似动态sql中，传入的变量需要单独拿出来用||隔开，然后在整句后使用into来承接查询的结果。如果结果是一个集合，那么使用sys_refcursor承接，
rst sys_refcursor;v_sql := 'select * from table';
open rst for v_sql;
loop
fetch rst into v1;
-- 处理;
end loop;
close rst;
像这样将结果传出并处理。这两个应该这个procedure中的关键问题，其他的看看oracle语法可以自己写了。

Answer 4

高人别走。
我给你写的那个改的差不多了。创建那个  目标插入表 的语法我都写在题目里面。
你只要有 USER_TABLE 你就可以调试。麻烦高人给写个完整的吧。
我这边给你那个语句改了一下 执行 出现错误，麻烦你看下
{
create or replace procedure cx_test5 (tablename in varchar2)
is
columnname varchar2(200);
v_notnull_cnt  number := 0;
begin
select columnname  into columnname from user_tab_columns;
   execute immediate 'select count(*) from '||TABLENAME||' where '||columnname|| ' is not null' into v_notnull_cnt;
   execute immediate 'update test_colcount set COLUMN_ISNOTNULL_COUNT = '||v_notnull_cnt||' where TABLE_NAME = '||TABLENAME;
end cx_test5;
}实际返回的行数 超出实际请求的行数。

Answer 5

高手，你再看下，我用你给的语句改了一下出来的。编译成功，但是和我实际做的时候遇到的问题一样。
提示表名 无效 FROM 后面不能跟变量 或者 存储过程 IN 进来的那个常量。
麻烦你自己亲自试验一下。真的不行。create or replace procedure cx_test5(tablename in varchar2) is
  columnname    varchar2(200);
  v_notnull_cnt number := 0;
  cursor getcolname is
    select t.COLUMN_NAME into columnname from user_tab_columns t;begin
  open getcolname;
  loop
    fetch getcolname
      into columnname;
    exit when getcolname%notfound;
    execute immediate 'select count(*) from ' || TABLENAME || ' where ' ||
                      columnname || ' is not null'
      into v_notnull_cnt;
    execute immediate 'update test_colcount set COLUMN_ISNOTNULL_COUNT = ' ||
                      v_notnull_cnt || ' where TABLE_NAME = ' || TABLENAME;
  end loop;
  close getcolname;
end cx_test5;

Answer 6

高手。麻烦你认真看下。这个事从系统表里面取的数据。任何ORACLE 数据库里面都通用的。
你可以在自己库里面试验一下。
我题目上说了。这个问题想起来容易，做起来难。会提示很多奇怪的错误的。麻烦你给实验一下。
真诚等待高手回话。

Answer 7

我不是高手select columnname  into columnname from user_tab_columns;
这条返回结果不止一行，不能用一个变量来承接，需要用cursor cursor getcolname is 
    select t.COLUMN_NAME into columnname from user_tab_columns t; 这句，cursor中不能使用into，因为你已经用cursor来放数据了，为什么还要使用into呢

Answer 8

create table TEST_COLCOUNT 
( 
  TABLE_NAME          VARCHAR2(200),  --表名 
  TABLE_NAME_COMMENTS VARCHAR2(200),  --表名中文含义 
  TABLE_NAME_NUM_ROWS VARCHAR2(200),  --表中共多少行数据  系统表 USER_TABLES 里面已经提供 
  COLUMN_NAME        VARCHAR2(200),  --字段名 
  COLUMN_COMMENTS    VARCHAR2(200),  --字段中文含义 
  COLUMN_ISNOTNULL_COUNT        NUMBER, --IS NOT NULL 出来的值  （关键算这里的值） 
  COLUMN_NULL_COUNT  NUMBER,            --IS NULL 出来的值      （关键算这里的值） 
  EXECDATE            DATE              --执行时间 用SYSDATE 就可以了。 
) 你现在你的数据库里面创建一张这样的表
然后用存储过程来实现我说的那个动态SQL试试。
关键是 FROM 后面跟变量名 或者跟 IN 进来的那个参数，就报错。
麻烦你试验一下。你肯定能写出完整的代码。
麻烦你写一下完整代码，谢谢了。

Answer 9

CREATE OR REPLACE PROCEDURE CX_TEST5(V_TABLENAME IN VARCHAR2) IS
  V_NOTNULL_CNT NUMBER := 0;
  V_SQL         VARCHAR2(200);
BEGIN
  --循环取表字段
  FOR REC IN (SELECT COLUMN_NAME, TABLE_NAME
                INTO COLUMNNAME
                FROM USER_TAB_COLUMNS
               WHERE TABLE_NAME = V_TABLENAME) LOOP
    --统计字段非空记录数
    V_SQL := 'SELECT COUNT(*) FROM ' || REC.TABLE_NAME || ' WHERE ' ||
             REC.COLUMN_NAME || ' IS NOT NULL';
    EXECUTE IMMEDIATE V_SQL
      INTO V_NOTNULL_CNT;
    --记录字段非空记录
    UPDATE TEST_COLCOUNT
       SET COLUMN_ISNOTNULL_COUNT = V_NOTNULL_CNT
     WHERE TABLE_NAME = REC.TABLE_NAME;
    COMMIT;
  END LOOP;
END CX_TEST5;

Answer 10

create or replace procedure cx_test5(tablename in varchar2) is 
  columnname      varchar2(200); 
  v_notnull_cnt   number := 0; 
  v_null_cnt      number := 0;
  getcolname      sys_refcursor;
  v_sql           varchar2(2000);begin 
   v_sql := 'select t.COLUMN_NAME from user_tab_columns t where TABLE_NAME = '||tablename;
   open getcolname for v_sql; 
   loop 
      fetch getcolname into columnname; 
      exit when getcolname%notfound; 
      execute immediate 'select count(*) from ' || tablename || ' where ' || columnname || ' is not null' into v_notnull_cnt; 
      execute immediate 'update test_colcount set COLUMN_ISNOTNULL_COUNT = ' || v_notnull_cnt || ' where TABLE_NAME = ' || tablename; 
   end loop; 
   close getcolname; 
exception
   when others then
      dbms_output.put_line('ERROR');
      rollback;
end cx_test5; 
/把你的代码改了下，家里没oracle不能测试，
建议用#12的代码

Answer 11

12 楼 13楼的代码都没有实现12楼的代码 你看下 V_NOTNULL_CNT NUMBER := 0; 两位高人的代码都没有UPDATE 到行。

Answer 12

你把update语句的where加上columnname限定不就完了吗update test_colcount set COLUMN_ISNOTNULL_COUNT = ' || v_notnull_cnt || ' where TABLE_NAME = ' || tablename ||' and column_name = '||columnname;#12那个update改下应该也可以

Answer 13

两位高人，语句可以执行了。但是执行出来的内容不对啊。 
UPDATE 的值不对

Answer 14

不行。语句还是不能实现啊。根本给那个字段UPDATE 不了值啊。
执行完还是空的。

Answer 15

先创建一张临时表create global temporary table tmp_transaction on commit delete rows 
as select * from test_colcount where 1=0然后CREATE OR REPLACE PROCEDURE CX_TEST(P_TABLENAME IN VARCHAR2)  ASv_tablename varchar2(100);
v_tablecomment varchar2(200);
v_numrows varchar2(10);
v_nullnum number(4);
v_notnullnum number(4);
v_sql varchar2(200);cursor column_comments is select table_name, column_name, comments from user_col_comments where table_name = P_TABLENAME;
BEGIN --查询表注释
    select table_name, comments into v_tablename, v_tablecomment from user_tab_comments where table_name = P_TABLENAME;--查询表行数
    select num_rows into v_numrows from sys.user_tables where table_name = v_tablename;--插入表名,表注释，列数，列名
    insert into tmp_transaction t 
    (TABLE_NAME,TABLE_NAME_COMMENTS,TABLE_NAME_NUM_ROWS,COLUMN_NAME) 
    select table_name,
           v_tablecomment,
           v_numrows,
           column_name 
      from user_tab_columns
     where table_name = v_tablename;
--插入列注释,空行数，非空行数
    for v_tmp in column_comments loop
    
      v_sql := 'select count(1) from '||v_tablename||' where '||v_tmp.column_name||' is null';
      execute immediate v_sql into v_nullnum;
      
      v_sql := 'select count(1) from '||v_tablename||' where '||v_tmp.column_name||' is not null';
      execute immediate v_sql into v_notnullnum;
      
      update tmp_transaction t
         set t.column_comments = v_tmp.comments,
             t.column_isnotnull_count = v_notnullnum,
             t.column_null_count = v_nullnum,
             t.execdate = sysdate
       where t.table_name = v_tmp.table_name
         and t.column_name = v_tmp.column_name;
    end loop;--删除目标表中原有数据,插入新算数据
    delete from TEST_COLCOUNT where table_name = v_tablename;
    
    insert into test_colcount
    select * from tmp_transaction;
    
    commit;
EXCEPTION
 WHEN OTHERS THEN
   dbms_output.put_line('真该死，出错了。CX_TEST');END CX_TEST;执行
    表名        表注释         列数 列名            列注释  不为空行数 空行数       日期
1 DIC_CLCF 中图分类号字典表   22  ID                     22 0 2009/7/21 22:21:47
2 DIC_CLCF 中图分类号字典表   22  CLCF_ID               22 0 2009/7/21 22:21:47
3 DIC_CLCF 中图分类号字典表   22  CLCF_NAME       22 0 2009/7/21 22:21:47
4 DIC_CLCF 中图分类号字典表   22  NOTE       备注      0        22 2009/7/21 22:21:47不知道是不是这个

Answer 16

1.是否因为动态sql后未commit？
2.目标表中是否有该表的该字段记录？

Answer 17

我这里有另外一份，编译成功，测试的时候，提示 OPEN第二个游标的时候有错，麻烦高手给看下。应该怎么改
create or replace procedure p_printf_tableinfo_44
(
tablename_in in varchar  --参数IN
) is  type c_cursor is ref cursor;
  cursor_isnotnull c_cursor;
  cursor_isnull    c_cursor;
  --c_cursor_columns c_cursor;  v_tablename_in        varchar(200);
  v_column_name         varchar2(100);
  v_sql                 varchar2(1024);
  v_sqla                varchar2(1000);
  v_recnumber           number(15, 0);
  v_recnumbera          number(15, 0);
  a_table_name          varchar2(200);
  a_table_name_comments varchar2(200);
  a_table_name_num_rows varchar2(200);
  a_column_comments     varchar2(200);
  a_sysdate             date;  CURSOR c_cursor_columns is
  
    SELECT DISTINCT A.TABLE_NAME,
                    C.comments TABLE_NAME_COMMENTS,
                    A.NUM_ROWS TABLE_NAME_NUM_ROWS,
                    B.COLUMN_NAME,
                    D.comments COLUMN_COMMENTS,
                    SYSDATE
      FROM USER_TABLES       A,
           USER_TAB_COLUMNS  B,
           USER_TAB_COMMENTS C,
           USER_COL_COMMENTS D
     WHERE A.TABLE_NAME = B.TABLE_NAME
       AND A.TABLE_NAME = C.table_name
       AND A.TABLE_NAME = D.table_name
       AND B.COLUMN_NAME = D.column_name
     ORDER BY A.TABLE_NAME;begin  v_tablename_in := tablename_in;  dbms_output.put_line('v_tablename_in=' || v_tablename_in);  open c_cursor_columns;
  --select column_name  from user_tab_columns  where table_name = upper(v_tablename_in);
  loop
    fetch c_cursor_columns
      into a_table_name, a_table_name_comments, a_table_name_num_rows, a_column_comments, a_sysdate, v_column_name;
    exit when c_cursor_columns%notfound;
  
    v_sql  := 'select count(*)  rec_number from ' || v_tablename_in ||
              ' where     ' || v_column_name || '  is not null';
    v_sqla := 'select count(*)  rec_number from ' || v_tablename_in ||
              ' where     ' || v_column_name || '  is  null';    open cursor_isnotnull for v_sql; --错误在这里。这里被标注为黄色 提示:无效的关系运算符
  
    loop
      fetch cursor_isnotnull
        into v_recnumber;
      exit when cursor_isnotnull%notfound;
    
      insert into test_colcount
        (table_name,
         table_name_comments,
         table_name_num_rows,
         column_name,
         column_comments,
         column_isnotnull_count)
      values
        (v_tablename_in,
         a_table_name_comments,
         a_table_name_num_rows,
         v_column_name,
         a_column_comments,
         v_recnumber);
      commit;
close cursor_isnotnull;
    end loop;
    
    open cursor_isnull for  v_sqla;
    loop
      fetch cursor_isnull
        into v_recnumbera;
      exit when cursor_isnull%notfound;
      /*
      insert into test_colcount (table_name,column_name,column_isnull_count)
      values(v_tablename_in,v_column_name,v_recnumbera);
      commit;*/
    
      update test_colcount t
         set t.column_isnull_count = v_recnumbera
       where t.column_name = v_column_name;
      commit;
    
    close cursor_isnull;
      --dbms_output.put_line('tablename =  '||v_tablename_in ||' coulumn = '||v_column_name||' recnumber = '||v_recnumber);
    end loop;
  
    --v_numberout:=v_recnumber;
  close c_cursor_columns;
  end loop;end p_printf_tableinfo_44;

Answer 18

感谢19楼的代码，不幸的是。你的代码 编译没有错误。
但是执行完 表中没有数据。
TEST_COUNT 表中 一行数据都没有，很汗。
我上面贴这份代码可以完成这个任务。但是编译的时候提示 OPEN 那个游标的地方 无效的关系运算符

Answer 19

感谢19楼的代码，不幸的是。你的代码 编译没有错误。
但是执行完 表中没有数据。
TEST_COUNT 表中 一行数据都没有，很汗。
我上面贴这份代码可以完成这个任务。但是编译的时候提示 OPEN 那个游标的地方 无效的关系运算符

Answer 20

create or replace procedure CX_TEST(iv_tabname in varchar2) 
is
v_sql     varchar2(200);
v_str     varchar2(200);
tablename varchar2(200);
v_tab_comments varchar2(200);
v_col_name     varchar2(200);
v_col_comments varchar2(200);
v_count   varchar2(200);
v_notnull_count varchar2(200);
v_null_count    varchar2(200);
type cur is ref cursor;
col_cur cur;
begin
     tablename := upper(iv_tabname);
     v_sql := 'select comments from user_tab_comments where table_name = '''||tablename||'''';
     execute immediate v_sql into v_tab_comments;
     v_sql := 'select count(*) from '||tablename;
     execute immediate v_sql into v_count;
     v_sql := 'select column_name,comments from user_col_comments where table_name = '''||tablename||'''';
     open col_cur for v_sql;
     loop
         fetch col_cur into v_col_name,v_col_comments;
         exit when col_cur%notfound;
         v_str := 'select count(*) from '||tablename||' where '||tablename||'.'||v_col_name||' is not null';
         execute immediate v_str into v_notnull_count;
         v_str := 'select count(*) from '||tablename||' where '||tablename||'.'||v_col_name||' is null';
         execute immediate v_str into v_null_count;
         
         insert into test_colcount
         (
                table_name, 
                table_name_comments, 
                table_name_num_rows, 
                column_name, 
                column_comments, 
                column_isnotnull_count, 
                column_null_count, 
                execdate
         )
         values
         (
               tablename, 
               v_tab_comments, 
               v_count, 
               v_col_name, 
               v_col_comments, 
               v_notnull_count, 
               v_null_count, 
               sysdate
         );
         commit;
     end loop;
     close col_cur;exception
         when others then
              dbms_output.put_line('error='||sqlcode||';'||sqlerrm);  
end CX_TEST;