exception 不能写在循环里面吧,语法问题
解决方案 »
- 如何删除重复项,保留一条记录
- 请问有没有办法对指定用户设置Oracle的默认并行度(DOP,Degree of Parallel)?
- 关于Delphi排序问题
- 关于NVL2()函数的用法!!!!谁会???急!!!
- oracle 9i在客户端怎么配置啊?第一次接触oracle的东西,大家帮帮忙
- 新手提问,我装好了oracle9.0.1.1.1,不知道要打啥补钉之类的?
- oracel9i中文乱码问题?
- PL/SQL如何读取BLOB字段?谢谢!
- 如何实现在VC中使用OCI,实现调用存储过程返回多条记录。
- 在后台怎样定时执行一段代码?
- 为什么数据仓库上不建立索引
- Oracle 包(Package)全局变量疑惑
create or replace procedure proc_trade
as
v_cname user_tab_columns.COLUMN_NAME%type;
v_cvalue varchar2(50);
cursor t_columname is select column_name as b from user_tab_columns where table_name='MDM_CUSTEPMASTER';
begin
open t_columname;
loop
fetch t_columname into v_cname;
exit when t_columname%notfound;
begin
select v_cname into v_cvalue from mdm_custepmaster where trim(v_cname)='Y';
exception
when no_data_found then dbms_output.put_line('没有数据');
when too_many_rows then dbms_output.put_line(v_cname);
when others then dbms_output.put_line('错误情况不明');
end;
end loop;
close t_columname;
end;
显示成sql code,颜色表示就显示不正常了
procedure a is
v_cname user_tab_columns.COLUMN_NAME%type;
v_cvalue varchar2(50);
cursor t_columname is select column_name as b from user_tab_columns where table_name='MDM_CUSTEPMASTER';
begin
open t_columname;
loop
fetch t_columname into v_cname;
exit when t_columname%notfound;
begin
select v_cname into v_cvalue from mdm_custepmaster where trim(v_cname)='Y';
exception
when no_data_found then dbms_output.put_line('没有数据');
when too_many_rows then dbms_output.put_line(v_cname);
when others then dbms_output.put_line('错误情况不明');
end;
end loop;
close t_columname;
end;
我想要查出来这个表中哪个列中的值有N,可能是‘N ’,或者' N '之类的,只要是trim(属性值)=N的都要查出来,怎么办啊?
FUNCTION GetValue (P_SQL in varchar2) RETURN varchar2 IS
str varchar2(500);
mycursor INTEGER;
ignore INTEGER;
begin
mycursor := dbms_sql.open_cursor;
dbms_sql.parse(mycursor,P_SQL,1);
DBMS_SQL.DEFINE_COLUMN(mycursor,1,str,500);
ignore := dbms_sql.execute(mycursor);
if DBMS_SQL.FETCH_ROWS(mycursor)<> 0 then
DBMS_SQL.COLUMN_VALUE(mycursor,1,str);
end if;
dbms_sql.close_cursor(mycursor);
return str;
END;
这行改为
v_cvalue:=GetValue('select '||v_cname||' from mdm_custepmaster where trim('||v_cname||')=''Y''');
create or replace procedure proc_trade
as
v_cname user_tab_columns.COLUMN_NAME%type;
v_cvalue varchar2(50):=' ';
cursor t_columname is select column_name from user_tab_columns where table_name='MDM_CUSTEPMASTER';
begin
open t_columname;
loop
fetch t_columname into v_cname;
exit when t_columname%notfound;
v_cvalue:=GetValue('select '||v_cname||' from MDM_CUSTEPMASTER where trim('||v_cname||')=''N''');
if v_cvalue<>' ' then
DBMS_OUTPUT.PUT_LINE(v_cname);
end if;
end loop;
close t_columname;
end;
create or replace function GetValue(PL_SQL in varchar2) return varchar2 is
str varchar2(500);
mycursor INTEGER;
ignore INTEGER;
begin
mycursor := dbms_sql.open_cursor;
dbms_sql.parse(mycursor,PL_SQL,1);
DBMS_SQL.DEFINE_COLUMN(mycursor,1,str,500);
ignore := dbms_sql.execute(mycursor);
if DBMS_SQL.FETCH_ROWS(mycursor)<> 0 then
DBMS_SQL.COLUMN_VALUE(mycursor,1,str);
end if;
dbms_sql.close_cursor(mycursor);
return(str);
end GetValue;