经常在论坛上看到有人问怎么通过一个存储过程来查询某个表中的某列 想写个通用些的过程 可是没写出来 呵呵 小尴尬一下…… 所以来求助一下 希望能完成它--这是个匿名块 功能倒是可以实现 不过不太好
SQL> set serveroutput on
SQL> declare
  2  cursor v_cur is select &&v_colname from &&v_tablename;
  3  begin
  4  for v_rec in v_cur loop
  5  dbms_output.put_line(v_rec.&&v_colname);
  6  end loop;
  7  end;
  8  /
Enter value for v_colname: dname
Enter value for v_tablename: dept
old   2: cursor v_cur is select &&v_colname from &&v_tablename;
new   2: cursor v_cur is select dname from dept;
old   5: dbms_output.put_line(v_rec.&&v_colname);
new   5: dbms_output.put_line(v_rec.dname);
ACCOUNTING
RESEARCH
SALES
OPERATIONSPL/SQL procedure successfully completed.--下面是存储过程 我想在里面通过查询两个数据字典 达到判断用户输入的表名是否存在 以及该表中的列是否存在
SQL> create or replace procedure tab_col(p_tablename in varchar2,p_colname in va
rchar2)
  2  is
  3  tab_flag number default 0;
  4  col_flag number default 0;
  5  no_table exception;
  6  no_col exception;
  7  type t_curtype is ref cursor;
  8  v_cur t_curtype;
  9  v_tablename varchar2(20);
 10  v_colname varchar2(20);
 11  begin
 12  v_tablename:=p_tablename;
 13  v_colname:=p_colname;
 14  select count(*) into tab_flag from user_tables where table_name=v_tablename
;
 15  if tab_flag=0 then
 16  raise no_table;
 17  end if;
 18  select count(*) into col_flag from user_tab_cols where table_name=v_tablena
me and column_name=v_colname;
 19  if col_flag=0 then
 20  raise no_col;
 21  end if;
 22  open v_cur for select v_colname from v_tablename;
 23  for v_rec in v_cur loop
 24  dbms_output.put_line(v_rec.v_colname);
 25  end loop;
 26  exception
 27  when no_table then
 28  dbms_output.put_line('the table is not exist');
 29  when no_col then
 30  dbms_output.put_line('the col is not exist');
 31  when others then
 32  dbms_output.put_line('some other errors occur');
 33  end;
 34  /Warning: Procedure created with compilation errors.SQL> show errors
Errors for PROCEDURE TAB_COL:LINE/COL ERROR
-------- -----------------------------------------------------------------
22/16    PL/SQL: SQL Statement ignored
22/38    PL/SQL: ORA-00942: table or view does not exist
23/1     PL/SQL: Statement ignored
23/14    PLS-00221: 'V_CUR' is not a procedure or is undefined
SQL>亲们不要骂我懒 虽然错误提示我看得懂 可是我已经改过很多次了 就是改不对 才来求助的 呵呵 PL/SQL不太好 见谅哈~

解决方案 »

  1.   

     22  open v_cur for select v_colname from v_tablename;
    这里可能要用动态sql来fetch到游标。
    var_sql varchar2(200);
    var_sql := 'select '||v_colname||'  from '|| v_tablename;
    open v_cur for var_sql;想不到兔子也会犯错误噢,呵呵 
      

  2.   

    小小汗一个..
    前一个直接select COLNAME from TABNAME;
    后一个select * from user_tab_cols where ..查询。都要比写过程方便得多啊,不知道写过程的原因是什么...
      

  3.   

      7  type t_curtype is ref cursor;
      8  v_cur t_curtype;游标定义貌似也不用这么复杂啊
    v_cur SYS_REFCURSOR;
      

  4.   

    那怎么办
    像上面crazy说的那样使用动态SQL?
      

  5.   

    我去把crazylaa说的几个地方改了试试
      

  6.   

    是的,动态
    如果在客户端中或其他第三方工具中,过程还是没有必要,表不存在或列不存在也会有提示,而且用dbms_output输出也不太方便
    如果在前台程序端调用的话,可以将错误提示和查询结果分别替换为传出字符串和传出游标
      

  7.   

    呵呵 就是突发奇想想要写出这么个过程来
    因为是在SQL*PLUS里执行么 所以想要使用dbms_output来打印出错误信息
      

  8.   

    SQL> create or replace procedure tab_col(p_tablename in varchar2,p_colname in va
    rchar2)
      2  is
      3  tab_flag number default 0;
      4  col_flag number default 0;
      5  no_table exception;
      6  no_col exception;
      7  v_cur sys_refcursor;
      8  v_tablename varchar2(20);
      9  v_colname varchar2(20);
     10  v_sql varchar2(1000);
     11  begin
     12  v_tablename:=p_tablename;
     13  v_colname:=p_colname;
     14  select count(*) into tab_flag from user_tables where table_name=v_tablename
    ;
     15  if tab_flag=0 then
     16  raise no_table;
     17  end if;
     18  select count(*) into col_flag from user_tab_cols where table_name=v_tablena
    me and column_name=v_colname;
     19  if col_flag=0 then
     20  raise no_col;
     21  end if;
     22  v_sql:='select '||v_colname||' from '||v_tablename;
     23  open v_cur for v_sql;
     24  for v_rec in v_cur loop
     25  dbms_output.put_line(v_rec.v_colname);
     26  end loop;
     27  exception
     28  when no_table then
     29  dbms_output.put_line('the table is not exist');
     30  when no_col then
     31  dbms_output.put_line('the col is not exist');
     32  when others then
     33  dbms_output.put_line('some other errors occur');
     34  end;
     35  /Warning: Procedure created with compilation errors.SQL> show errors
    Errors for PROCEDURE TAB_COL:LINE/COL ERROR
    -------- -----------------------------------------------------------------
    24/1     PL/SQL: Statement ignored
    24/14    PLS-00221: 'V_CUR' is not a procedure or is undefined这个错误是什么原因
    游标使用方法错了吗
      

  9.   

    for v_rec in v_cur 。这个不行
    refcursor不能用for .. in形式
    改成
    loop
    fetch .. into ...;
    exit when ...;
    end loop;
      

  10.   

    create or replace
    procedure tab_col(p_tablename in varchar2,p_colname in varchar2)
       is
       tab_flag number default 0;
       col_flag number default 0;
       no_table exception;
       no_col exception;
     --type t_curtype is sys_refcursor;
      v_cur  sys_refcursor;
       v_tablename varchar2(20);
      v_colname varchar2(20);
      v_sql varchar2(200);
      begin
       v_tablename:=p_tablename;
       v_colname:=p_colname;
       v_sql := 'select'|| v_colname|| ' from'|| v_tablename;
       select count(*) into tab_flag from user_tables where table_name=v_tablename;
       if tab_flag=0 then
       raise no_table;
       end if;
       select count(*) into col_flag from user_tab_cols where table_name=v_tablename and column_name=v_colname;
       if col_flag=0 then
       raise no_col;
     end if;
       open v_cur for v_sql;
       loop
       fetch v_cur into v_colname;--用fetch into
       exit when v_cur%notfound;
      dbms_output.put_line(v_colname);
       end loop;
      exception
       when no_table then
       
      dbms_output.put_line('the table is not exist');
      when no_col then
      dbms_output.put_line('the col is not exist');
      when others then
       dbms_output.put_line('some other errors occur');
      end;
      

  11.   

    ……
    其实我当时写的时候就想用fetch的
    可是我怎么fetch?不知道该列的类型 没办法定义该列类型的变量啊
      

  12.   

    好像是噢定义变量估计也不能用 tableName.colName%TYPE也是未知数
    汗,兔子你搞出个问题来了。。
      

  13.   

    你查询出来的游标包含的是例名呀!你查询出来的又不是该列的数据类型。
    列名当然是字符类型(varchar2)的了呀!
      

  14.   

    能不能把所有列的值都用统一的varchar2来处理?即:把查询出来的所有列的值转换成varchar2,再显示出来。
      

  15.   

    ...一般从游标里取数据不会不知道数据类型的
    反正你fetch后要打印,用to_char转了吧,fetch到varchar型变量中
    to_char的format部分,可以根据user_tab_cols里查到的字段类型来写
      

  16.   

    其实如果只是为了显示的话
    不管什么类型 都fetch到一个字符型变量中好像也可以
    SQL> create or replace procedure tab_col(p_tablename in varchar2,p_colname in va
    rchar2)
      2  is
      3  tab_flag number default 0;
      4  col_flag number default 0;
      5  no_table exception;
      6  no_col exception;
      7  v_cur sys_refcursor;
      8  v_tablename varchar2(20);
      9  v_colname varchar2(20);
     10  v_sql varchar2(1000);
     11  v_value varchar2(200);
     12  begin
     13  v_tablename:=p_tablename;
     14  v_colname:=p_colname;
     15  select count(*) into tab_flag from user_tables where table_name=v_tablename
    ;
     16  if tab_flag=0 then
     17  raise no_table;
     18  end if;
     19  select count(*) into col_flag from user_tab_cols where table_name=v_tablena
    me and column_name=v_colname;
     20  if col_flag=0 then
     21  raise no_col;
     22  end if;
     23  v_sql:='select '||v_colname||' from '||v_tablename;
     24  open v_cur for v_sql;
     25  loop
     26  fetch v_cur into v_value;
     27  exit when v_cur%notfound;
     28  dbms_output.put_line(v_value);
     29  end loop;
     30  exception
     31  when no_table then
     32  dbms_output.put_line('the table is not exist');
     33  when no_col then
     34  dbms_output.put_line('the col is not exist');
     35  when others then
     36  dbms_output.put_line('some other errors occur');
     37  end;
     38  /Procedure created.
    SQL> exec tab_col('DEPT','DNAME'); --DNAME是varchar2的
    ACCOUNTING
    RESEARCH
    SALES
    OPERATIONSPL/SQL procedure successfully completed.SQL> exec tab_col('DEPTNO','DNAME'); 
    the table is not existPL/SQL procedure successfully completed.SQL> exec tab_col('DEPT','DEPTNO'); --DEPTNO是NUMBER的
    10
    20
    30
    40PL/SQL procedure successfully completed.SQL> desc dept
     Name                                      Null?    Type
     ----------------------------------------- -------- ---------------------------- DEPTNO                                    NOT NULL NUMBER(2)
     DNAME                                              VARCHAR2(14)
     LOC                                                VARCHAR2(13)
      

  17.   

    你意思在dbms_output的时候使用to_char转一下?
    这样会不会太麻烦了
    查询user_tab_cols肯能会有那么多种数据类型
    我还要进行判断
    根据每种不同的类型来给出相应的格式字符串进行转换呵呵 上面那样直接fetch到varchar2偷个懒好像也可以哦??我又试了下date类型
    SQL> create table t1(time date);Table created.SQL> insert into t1 values(sysdate);1 row created.SQL> select * from t1;TIME
    ---------------
    10-JAN-10SQL> exec tab_col('T1','TIME');
    10-JAN-10PL/SQL procedure successfully completed.SQL> exec tab_col('T1','TIME1');
    the col is not existPL/SQL procedure successfully completed.
      

  18.   

    不是,我是说在动态语句里转,和变量类型保持一致,fetch出来。
    上面的语句可以是因为进行了隐式转换。这样也可以,如果是date或timestamp型等,隐式转换的结果可能会不是你想看到的,总体差不多吧,只是为了显示的话。其他类型例如long,clob等就不用考虑了..