例如:
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 ;
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 ;
解决方案 »
- 请教下BULK COLLECT问题
- 存储过程 TEST_PACKEAGE.TEST_COUSE 是什么意思?!!!!!!!!!
- 高薪招聘,请进.
- 可以把primary 和 standby database 建在同一个装WIN200的机器上吗
- [参数设置] 日期函数--头疼
- 一个大伤脑筋的问题。。。请高手赐教,高分
- 如何安装Oracle Developer 10g???
- 高手进来瞧瞧,走过的路过的千万不要错过!!!
- 救命问题 怎么修改缺省数据库实例:----高分相送
- PL/SQL数据库怎么学?
- 各位位大哥知道数据库中的代理键是什么?怎么使用?能详细的给我讲讲么?回帖的都有分!3ks!
- 复杂问题。高手务必进入!求教!
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;
-- 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;
/
--变量定义部分
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;
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;
================================================================
★★