1 建一个包create or replace package pak is
type cur is ref cursor;
procedure collist(name varchar2,colname varchar2,c2 in out cur);
end;create or replace package body pak is procedure collist(name varchar2,colname varchar2,c2 in out cur) is
str varchar2(200);
cursor c1(tbname varchar2) is select column_name col
from user_tab_columns where lower(table_name)=lower(tbname);
begin
for rec in c1(name) loop
if lower(rec.col)<>lower(colname) then
str:=str||rec.col||',';
end if;
end loop;
str:=substr(str,1,length(str)-1);
open c2 for 'select '||str||' from '||name;
end;
end;过程collist的参数说明:
name 表名,colname 要过滤掉的列名,c2 游标变量,用于传递结果集2 使用方法:sqlplus中var cur refcursor;
set autoprint on
set linesize 1000
exec pak.collist('employees','employee_id',:cur);
type cur is ref cursor;
procedure collist(name varchar2,colname varchar2,c2 in out cur);
end;create or replace package body pak is procedure collist(name varchar2,colname varchar2,c2 in out cur) is
str varchar2(200);
cursor c1(tbname varchar2) is select column_name col
from user_tab_columns where lower(table_name)=lower(tbname);
begin
for rec in c1(name) loop
if lower(rec.col)<>lower(colname) then
str:=str||rec.col||',';
end if;
end loop;
str:=substr(str,1,length(str)-1);
open c2 for 'select '||str||' from '||name;
end;
end;过程collist的参数说明:
name 表名,colname 要过滤掉的列名,c2 游标变量,用于传递结果集2 使用方法:sqlplus中var cur refcursor;
set autoprint on
set linesize 1000
exec pak.collist('employees','employee_id',:cur);
当然CodeMagic的方法也可以一试
在Delphi中可以实现
放一个数据集,一个网格
SELECT * FROM 表名 WHERE 1=0
然后用循环从网格中取得所有列的FieldName(字段名), 合成一个SQL语句中的查询列部分
再进行查询就OK了
呵呵,这当然是笨办法,不过效率也不是太慢哦,因为第一次查询不会有行返回
然后就是合成列的循环,列再多也不过几十个