一个关于存储过程的问题

[augly]

各位大虾，有个问题：我想在存储过程中，“内部”实现对于数据库中一个表的访问。通过一个内部循环（loop等)，将符合要求的数值一一赋予过程中已经定义的“内部”变量。请问如何实现。（注意，不是，赋予一个out参数，该变量不是用于返回数据，而是用于过程内部的数据处理）。

谢谢！

[bzszp]

例如：
str varchar2（4000）;
t_col tbname.col1%type;
cursor c_1 is
select col1 from tbname
where ...;
begin
for t_col in c_1 loop
str:=str||'and'||t_col;//放入字符串str中，用‘and’字符串连接
end loop;
end ;

[zhaoyongzhu]

create or replace procedure expert_procedure
as

--define a cursor
cursor v_Cursor is select DEPARTMENT.DEPARTMENT,
DEPARTMENT.DEPARTMENTNO,
EXPERT.EXPERTNAME,
EXPERT.EXPERTNO,
EXPERT.SPECIALITY,
EXPERT.SYNOPSIS,
EXPERT.TYPENUM,
EXPERT.ZC
FROM ZDWY.DEPARTMENT, ZDWY.EXPERT
WHERE ( ZDWY.DEPARTMENT.DEPARTMENTNO = ZDWY.EXPERT.DEPARTMENTNO ) ;

begin

--loop the cursor and output all record value
for v_tempCursor in v_Cursor loop
dbms_output.put_line('DEPARTMENT:' || v_tempCursor.department || ';');
dbms_output.put_line('DEPARTMENTNO:' || v_tempCursor.DEPARTMENTNO || ';');
dbms_output.put_line('EXPERTNAME:' || v_tempCursor.EXPERTNAME || ';');
dbms_output.put_line('EXPERTNO:' || v_tempCursor.EXPERTNO || ';');
dbms_output.put_line('SPECIALITY:' || v_tempCursor.SPECIALITY || ';');
dbms_output.put_line('SYNOPSIS:' || v_tempCursor.SYNOPSIS || ';');
dbms_output.put_line('TYPENUM:' || v_tempCursor.TYPENUM || ';');
dbms_output.put_line('ZC:' || v_tempCursor.ZC || ';');
end loop;

exception

--when exception is producted then output errors information
when others then
raise;
end;

[augly]

楼上的方法，好象不成

[888888888888]

同意bzszp(SongZip)^-^

[jlandzpa]

--比较表结构的差异
-- BEGIN PL/SQL BLOCK (do not remove this line) --------------------------------

create table table_added
(tablename varchar2(30) primary key
);

create table table_modified
(tablename varchar2(30),
sql_modified varchar2(100)
);

CREATE OR REPLACE PROCEDURE wffz_struct
as
cursor c1 is
select TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH
from user_tab_columns@wffz_new
where substr(TABLE_NAME,1,1) <> 'V'
order by TABLE_NAME,COLUMN_NAME;
ps_TABLE_NAME varchar2(30);
ps_COLUMN_NAME varchar2(30);
ps_DATA_TYPE varchar2(30);
pn_DATA_LENGTH number;
ps_rowid VARCHAR2(30);
ps_temp VARCHAR2(250);
pi_tmp integer;
pi_tmp0 integer;
pi_count integer := 0;
begin
open c1;
fetch c1 into ps_TABLE_NAME,ps_COLUMN_NAME,ps_DATA_TYPE,pn_DATA_LENGTH;
loop
exit when c1%notfound;
select count(*) into pi_tmp from user_tab_columns@wffz_old
where TABLE_NAME = ps_TABLE_NAME;
if pi_tmp = 0 then --新增表
pi_count := pi_count + 1;
begin
insert into table_added values(ps_TABLE_NAME);
commit;
exception when others then
null;
end;
else
select count(*) into pi_tmp from user_tab_columns@wffz_old
where TABLE_NAME = ps_TABLE_NAME and
COLUMN_NAME = ps_COLUMN_NAME;
if pi_tmp = 0 then --新增字段
pi_count := pi_count + 1;
begin
insert into table_modified values(ps_TABLE_NAME,
'alter table '||ps_TABLE_NAME||' add ('||ps_COLUMN_NAME||' '||ps_DATA_TYPE||'('||pn_DATA_LENGTH||'))');
commit;
exception when others then
null;
end;
else
select count(*) into pi_tmp from user_tab_columns@wffz_old
where TABLE_NAME = ps_TABLE_NAME and
COLUMN_NAME = ps_COLUMN_NAME and
DATA_TYPE = ps_DATA_TYPE and
DATA_LENGTH <> pn_DATA_LENGTH;
if pi_tmp > 0 then
pi_count := pi_count + 1;
begin
insert into table_modified values(ps_TABLE_NAME,
'alter table '||ps_TABLE_NAME||' modify ('||ps_COLUMN_NAME||' '||ps_DATA_TYPE||'('||pn_DATA_LENGTH||'))');
commit;
exception when others then
null;
end;
end if;
end if;
end if;
--dbms_output.put_line(ps_TABLE_NAME||':'||ps_COLUMN_NAME||':'||ps_DATA_TYPE||':'||pn_DATA_LENGTH);
/* where rowid = chartorowid(ps_rowid);
exception when others then
end ;
*/
fetch c1 into ps_TABLE_NAME,ps_COLUMN_NAME,ps_DATA_TYPE,pn_DATA_LENGTH;
end loop;
close c1;
dbms_output.put_line('pi_count:'||pi_count);
end;
/

[bowlder]

简单的例子，供参考。

TYPE REF_CURSOR_TYPE IS REF CURSOR;
MY_CURSOR REF_CURSOR_TYPE;
TYPE TAB_RECORD IS RECORD( NAME MY_TABLE.NAME%TYPE);
TAB_REC TAB_RECORD;

BEGIN
SQL_STR:='SELECT NAME FROM MY_TABLE';
OPEN MY_CURSOR FOR SQL_STR;
LOOP
FETCH MY_CURSOR INTO TAB_REC;
EXIT WHEN MY_CURSOR%NOFOUND;
DBMS_OUTPUT.PUT_LINE(TAB_REC.ID||TAB_REC.NAME);
END LOOP;
CLOSE MY_CURSOR;
END;
================================================================
★★

[qiuyang_wang]

create or replace procedure .. as
--变量定义部分
cursor c1 is select ..from ..
..
begin
open c1;
loop
fetch c1 into ..;
exit when c1%notfound;
--判断是否满足条件
if 满足条件 then
--数据处理
end if;
end loop
close c1;
--异常部分
EXCEPTION
WHEN OTHERS THEN
--处理语句
end;