create or replace procedure DEMO_PRO(TBNAME IN VARCHAR2,FDNAME IN VARCHAR2, FDVALUE IN VARCHAR2)
as
type myrctype is ref cursor;
flag number:=-1;
s_fields varchar2(4000):='';
s_values varchar2(4000);
s_sql varchar2(4000):='';
cur myrctype;
begin
for t in (select column_name from user_tab_columns where table_name=upper(TBNAME)) loop
s_fields:=s_fields||'*'||t.column_name;
if t.column_name=upper(FDNAME) then
flag:=0;
end if;
if s_sql is null then
s_sql := 'select '||t.column_name;
else
s_sql := s_sql || '||''*''||' || t.column_name;
end if;
end loop;
if s_fields is null then
dbms_output.put_line('表"'||TBNAME||'"不存在');
else
dbms_output.put_line('表结构:');
dbms_output.put_line(substr(s_fields,2));
if flag<>0 then
dbms_output.put_line('此表中不存在字段"'||FDNAME||'"');
else
s_sql := s_sql || ' from ' || TBNAME || ' where ' || FDNAME || '=''' || FDVALUE || '''';
dbms_output.put_line('值:');
open cur for s_sql;
loop
fetch cur into s_values;
exit when cur%NOTFOUND;
dbms_output.put_line(s_values);
end loop;
end if;
end if;
end;
/
as
type myrctype is ref cursor;
flag number:=-1;
s_fields varchar2(4000):='';
s_values varchar2(4000);
s_sql varchar2(4000):='';
cur myrctype;
begin
for t in (select column_name from user_tab_columns where table_name=upper(TBNAME)) loop
s_fields:=s_fields||'*'||t.column_name;
if t.column_name=upper(FDNAME) then
flag:=0;
end if;
if s_sql is null then
s_sql := 'select '||t.column_name;
else
s_sql := s_sql || '||''*''||' || t.column_name;
end if;
end loop;
if s_fields is null then
dbms_output.put_line('表"'||TBNAME||'"不存在');
else
dbms_output.put_line('表结构:');
dbms_output.put_line(substr(s_fields,2));
if flag<>0 then
dbms_output.put_line('此表中不存在字段"'||FDNAME||'"');
else
s_sql := s_sql || ' from ' || TBNAME || ' where ' || FDNAME || '=''' || FDVALUE || '''';
dbms_output.put_line('值:');
open cur for s_sql;
loop
fetch cur into s_values;
exit when cur%NOTFOUND;
dbms_output.put_line(s_values);
end loop;
end if;
end if;
end;
/
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货