create procedure dbo.UPDATEGUID as
BEGIN Declare @pid VARCHAR(20)
declare @guid VARCHAR(500)
declare @p_path VARCHAR(500)if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..##temp_PB_ARCHIVES') and type='U')
drop table ##temp_PB_ARCHIVES
if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..##temp_PB_ARCHIVES_FILE') and type='U')
drop table ##temp_PB_ARCHIVES_FILEselect * into ##temp_PB_ARCHIVES_FILE from dbo.PB_ARCHIVES_FILE
select * into ##temp_PB_ARCHIVES from dbo.PB_ARCHIVES DECLARE MyCursor CURSOR
FOR
select distinct t2.PB_ARCHIVES_ID,LEFT(t2.PB_GUID,36) from ##temp_PB_ARCHIVES t1,##temp_PB_ARCHIVES_FILE t2 where t1.PB_ARCHIVES_ID=t2.PB_ARCHIVES_ID OPEN MyCursor
FETCH NEXT FROM MyCursor
into @pid , @guid WHILE @@FETCH_STATUS = 0
BEGIN
begin
set @p_path = dbo.getFullPathName(@pid)
set @p_path=left(@p_path, len(@p_path)-1)
update ##temp_PB_ARCHIVES_FILE set PB_GUID = @p_path where PB_ARCHIVES_ID= @pid
end
FETCH NEXT FROM MyCursor
into @pid, @guid
End CLOSE MyCursor
DEALLOCATE MyCursor End
go其中,需要建立2个临时表,然后调用函数更新数据
create global temporary table temp_table(id number) on commit preserve rows;先建好临时表,不需每次删除表,每次删除数据就OK了其它的主要是游标处理,下面是个oracle的例子
DECLARE
CURSOR C1 IS
SELECT VIEW_NAME FROM ALL_VIEWS WHERE ROWNUM <= 10 ORDER BY VIEW_NAME;
VNAME VARCHAR2(40);
BEGIN
OPEN C1;
FETCH C1 INTO VNAME;
WHILE C1%FOUND LOOP
FETCH C1 INTO VNAME;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(C1%ROWCOUNT) || '' || VNAME);
END LOOP;
CLOSE C1;
END;
if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..##temp_PB_ARCHIVES') and type='U')
drop table ##temp_PB_ARCHIVES 如果数据库中存在临时表##temp_PB_ARCHIVES则删除if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..##temp_PB_ARCHIVES_FILE') and type='U')
drop table ##temp_PB_ARCHIVES_FILE 如果数据库中存在临时表##temp_PB_ARCHIVES_FILE 则删除
select * into ##temp_PB_ARCHIVES_FILE from dbo.PB_ARCHIVES_FILE
从dbo.PB_ARCHIVES_FILE表中数据插入到新临时表##temp_PB_ARCHIVES_FILE 中
select * into ##temp_PB_ARCHIVES from dbo.PB_ARCHIVES
从dbo.PB_ARCHIVES 表中数据插入到新临时表##temp_PB_ARCHIVES 中
DECLARE MyCursor CURSOR --定义游标
FOR
select distinct t2.PB_ARCHIVES_ID,LEFT(t2.PB_GUID,36) from ##temp_PB_ARCHIVES t1,##temp_PB_ARCHIVES_FILE t2 where t1.PB_ARCHIVES_ID=t2.PB_ARCHIVES_ID
--2个临时表关联找到t2.PB_ARCHIVES_ID,LEFT(t2.PB_GUID,36)OPEN MyCursor --打开游标
FETCH NEXT FROM MyCursor
into @pid , @guid 取数到@pid , @guid 中WHILE @@FETCH_STATUS = 0
BEGIN
begin
set @p_path = dbo.getFullPathName(@pid) --给@p_path 赋值(dbo.getFullPathName为函数)
set @p_path=left(@p_path, len(@p_path)-1) --@p_path取右边第一个去掉
update ##temp_PB_ARCHIVES_FILE set PB_GUID = @p_path where PB_ARCHIVES_ID= @pid
end --更新##temp_PB_ARCHIVES_FILE
FETCH NEXT FROM MyCursor
into @pid, @guid ---取下一个值到 @pid, @guid End CLOSE MyCursor --关闭游标
DEALLOCATE MyCursor --删除游标End
SUBSTR(dbo.getFullPathName(t2.PB_ARCHIVES_ID),1, LENGTH(dbo.getFullPathName(t2.PB_ARCHIVES_ID) - 1))
from dbo.PB_ARCHIVES t1,
dbo.PB_ARCHIVES_FILE t2 where t1.PB_ARCHIVES_ID=t2.PB_ARCHIVES_ID;
is
pid VARCHAR2(20);
guid VARCHAR2(500);
p_path VARCHAR2(500);
isExist number;
str varchar2(1500);
BEGIN
select count(1) into isExist from user_tables where table_name= 'temp_PB_ARCHIVES';
if isExist=0 then
str:='CREATE GLOBAL TEMPORARY TABLE temp_PB_ARCHIVES ON COMMIT PRESERVE ROWS as select * from PB_ARCHIVES';
execute immediate str;
end if;
select count(1) into isExist from user_tables where table_name= 'temp_PB_ARCHIVES_FILE';
if isExist=0 then
str:='CREATE GLOBAL TEMPORARY TABLE temp_PB_ARCHIVES_FILE ON COMMIT PRESERVE ROWS as select * from _PB_ARCHIVES_FILE';
execute immediate str;
end if;
DECLARE
CURSOR MyCursor IS
select distinct t2.PB_ARCHIVES_ID,substr(t2.PB_GUID,0,36) from temp_PB_ARCHIVES t1,temp_PB_ARCHIVES_FILE t2 where t1.PB_ARCHIVES_ID=t2.PB_ARCHIVES_ID; BEGIN
OPEN MyCursor;
FETCH MyCursor INTO pid,guid;
WHILE MyCursor%FOUND LOOP
FETCH MyCursor INTO pid,guid;
begin
p_path := WEBPOWER.getFullPathName(pid);
p_path :=substr(p_path,0, length(p_path)-1);
update temp_PB_ARCHIVES_FILE set PB_GUID = p_path where PB_ARCHIVES_ID= pid ;
end;
END LOOP;
CLOSE MyCursor;
END;
END;
/编译提示:24/88 PL/SQL: ORA-00942: 表或视图不存在
24/5 PL/SQL: SQL Statement ignored
35/24 PL/SQL: ORA-00942: 表或视图不存在
35/17 PL/SQL: SQL Statement ignored
/* Translation Extracted DDL For Required Objects */
CREATE GLOBAL TEMPORARY TABLE tt_temp_PB_ARCHIVES_FILE
AS (
SELECT *
FROM PB_ARCHIVES_FILE
);
CREATE GLOBAL TEMPORARY TABLE tt_temp_PB_ARCHIVES
AS (
SELECT *
FROM PB_ARCHIVES
);CREATE OR REPLACE PROCEDURE UPDATEGUID
AS
CURSOR MyCursor
IS SELECT DISTINCT t2.PB_ARCHIVES_ID,
SUBSTR(t2.PB_GUID, 0, 36)
FROM tt_temp_PB_ARCHIVES t1,
tt_temp_PB_ARCHIVES_FILE t2
WHERE t1.PB_ARCHIVES_ID = t2.PB_ARCHIVES_ID;
v_pid VARCHAR2(20);
v_guid VARCHAR2(500);
v_p_path VARCHAR2(500);
v_temp NUMBER(1, 0) := 0;
BEGIN
BEGIN
SELECT 1 INTO v_temp
FROM DUAL
WHERE EXISTS ( SELECT *
FROM tt_sysobjects
WHERE id = NULL/*TODO:object_id(N'tempdb..##temp_PB_ARCHIVES')*/
AND TYPE = 'U' );
EXCEPTION
WHEN OTHERS THEN
NULL;
END; IF v_temp = 1 THEN
EXECUTE IMMEDIATE ' TRUNCATE TABLE tt_temp_PB_ARCHIVES '; END IF; BEGIN
SELECT 1 INTO v_temp
FROM DUAL
WHERE EXISTS ( SELECT *
FROM tt_sysobjects
WHERE id = NULL/*TODO:object_id(N'tempdb..##temp_PB_ARCHIVES_FILE')*/
AND TYPE = 'U' );
EXCEPTION
WHEN OTHERS THEN
NULL;
END; IF v_temp = 1 THEN
EXECUTE IMMEDIATE ' TRUNCATE TABLE tt_temp_PB_ARCHIVES_FILE '; END IF; DELETE FROM tt_temp_PB_ARCHIVES_FILE; INSERT INTO tt_temp_PB_ARCHIVES_FILE (
SELECT *
FROM PB_ARCHIVES_FILE ); DELETE FROM tt_temp_PB_ARCHIVES; INSERT INTO tt_temp_PB_ARCHIVES (
SELECT *
FROM PB_ARCHIVES ); OPEN MyCursor; FETCH MyCursor INTO v_pid,v_guid; WHILE sqlserver_utilities.fetch_status(MyCursor%FOUND) = 0
LOOP
BEGIN
BEGIN
v_p_path := getFullPathName(v_pid); v_p_path := SUBSTR(v_p_path, 0, LENGTH(v_p_path) - 1); UPDATE tt_temp_PB_ARCHIVES_FILE
SET PB_GUID = v_p_path
WHERE PB_ARCHIVES_ID = v_pid; END;
FETCH MyCursor INTO v_pid,v_guid; END;
END LOOP; CLOSE MyCursor;END;