大家好,很高兴大家能够关系我的帖子。
问题:有两个嵌套的游标(如:cursor1和cursor2)cursor2在cursor1内部,外层游标从字典表中取表名,作为内层游标的变量。
这就涉及到游标中变量的使用,(我知道我描述的很不明确)所以下面我把代码贴出来,请大家指教。 描述的很不清楚,请多看下面的代码。 create or replace procedure com_check_citynull is
begin
---游标,循环mdl_class_js(字典表)
declare cursor fetch_mdl_class is
select distinct table_name,name from mdl_class_js; --table_name 为字典表中存储其他表表名的字段
tbname fetch_mdl_class%rowtype;
table_name varchar2(100);
begin
open fetch_mdl_class;
loop
fetch fetch_mdl_class into tbname;
exit when fetch_mdl_class%notfound;
strSql := 'select city from ' || tbname.table_name; --内层游标要从那张表中查找,并不确定,所以用变量代替。
--tbname.table_name是外层游标中找出的表 --内层游标,从外层游标中获取的表中查找。
declare cursor fetch_tbname is
execute immediate strSql; --注意,此处是问题的关键,因为在这条sql语句中表名是用变量来表示的,总是出错
city_value fetch_tbname%rowtype;
begin
open fetch_tbname;
loop
fetch fetch_tbname into city_value;
exit when fetch_tbname%notfound;
if city_value is null then
--其他操作...
end if;
end loop;
end;
end loop;
end;
end com_check_citynull;
谢谢。
问题:有两个嵌套的游标(如:cursor1和cursor2)cursor2在cursor1内部,外层游标从字典表中取表名,作为内层游标的变量。
这就涉及到游标中变量的使用,(我知道我描述的很不明确)所以下面我把代码贴出来,请大家指教。 描述的很不清楚,请多看下面的代码。 create or replace procedure com_check_citynull is
begin
---游标,循环mdl_class_js(字典表)
declare cursor fetch_mdl_class is
select distinct table_name,name from mdl_class_js; --table_name 为字典表中存储其他表表名的字段
tbname fetch_mdl_class%rowtype;
table_name varchar2(100);
begin
open fetch_mdl_class;
loop
fetch fetch_mdl_class into tbname;
exit when fetch_mdl_class%notfound;
strSql := 'select city from ' || tbname.table_name; --内层游标要从那张表中查找,并不确定,所以用变量代替。
--tbname.table_name是外层游标中找出的表 --内层游标,从外层游标中获取的表中查找。
declare cursor fetch_tbname is
execute immediate strSql; --注意,此处是问题的关键,因为在这条sql语句中表名是用变量来表示的,总是出错
city_value fetch_tbname%rowtype;
begin
open fetch_tbname;
loop
fetch fetch_tbname into city_value;
exit when fetch_tbname%notfound;
if city_value is null then
--其他操作...
end if;
end loop;
end;
end loop;
end;
end com_check_citynull;
谢谢。
2: declare cursor fetch_tbname is execute immediate strSql; -- Oracle 应该不能这么用,这是SQL Server的语法吧?
直接改成这样就好了: open fetch_tbname for strSql; -- fetch_tbname 要首先定义: fetch_tbname sys_refcursor;
3: 所有定义语句要放在Declare和Begin的中间,比如: city_value fetch_tbname%rowtype;
4: 用完游标后要及时关闭游标,除非你返回某个游标变量错位输出参数。
5: 其他的要看运行的情况了。
CREATE OR REPLACE PROCEDURE COM_CHECK_CITYNULL IS
CURSOR FETCH_MDL_CLASS IS SELECT DISTINCT TABLE_NAME,NAME FROM MDL_CLASS_JS;
TBNAME FETCH_MDL_CLASS%ROWTYPE;
CITY_VALUE FETCH_TBNAME%ROWTYPE;
TABLE_NAME VARCHAR2(100);
FETCH_TBNAME SYS_REFCURSOR;BEGIN
OPEN FETCH_MDL_CLASS;
LOOP
FETCH FETCH_MDL_CLASS INTO TBNAME;
EXIT WHEN FETCH_MDL_CLASS%NOTFOUND;
STRSQL := 'SELECT CITY FROM ' || TBNAME.TABLE_NAME;
OPEN FETCH_TBNAME FOR STRSQL;
LOOP
FETCH FETCH_TBNAME INTO CITY_VALUE;
EXIT WHEN FETCH_TBNAME%NOTFOUND;
IF CITY_VALUE IS NULL THEN
-- YOUR OPERATION
END IF;
END LOOP;
END LOOP;
IF FETCH_TBNAME%ISOPEN THEN
CLOSE FETCH_TBNAME;
END IF;
IF FETCH_TBNAME%ISOPEN THEN
CLOSE FETCH_TBNAME;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_lINE(SQLCODE||' : '||SQLERRM);
END COM_CHECK_CITYNULL;
CREATE OR REPLACE PROCEDURE COM_CHECK_CITYNULL IS
CURSOR FETCH_MDL_CLASS IS SELECT DISTINCT TABLE_NAME,NAME FROM MDL_CLASS_JS;
TBNAME FETCH_MDL_CLASS%ROWTYPE;
CITY_VALUE FETCH_TBNAME%ROWTYPE;
TABLE_NAME VARCHAR2(100);
FETCH_TBNAME SYS_REFCURSOR;BEGIN
OPEN FETCH_MDL_CLASS;
LOOP
FETCH FETCH_MDL_CLASS INTO TBNAME;
EXIT WHEN FETCH_MDL_CLASS%NOTFOUND;
STRSQL := 'SELECT CITY FROM ' || TBNAME.TABLE_NAME;
OPEN FETCH_TBNAME FOR STRSQL;
LOOP
FETCH FETCH_TBNAME INTO CITY_VALUE;
EXIT WHEN FETCH_TBNAME%NOTFOUND;
IF CITY_VALUE IS NULL THEN
-- YOUR OPERATION
END IF;
END LOOP;
END LOOP;
IF FETCH_TBNAME%ISOPEN THEN
CLOSE FETCH_TBNAME;
END IF;
IF FETCH_MDL_CLASS%ISOPEN THEN
CLOSE FETCH_MDL_CLASS;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_lINE(SQLCODE||' : '||SQLERRM);
END COM_CHECK_CITYNULL;