现有如下需求,
存在用户 A 和用户 B ,B中有表test,现要在 A 中通过存储过程实现:判断A是否有表 test,如果没有,使用create table test as (select * from B.test)将B中表结构及数据复制到 A 中,如果有 test ,则使用 delete 语句先清除 A 中 test 表记录,然后用 insert into select 语句把 B 中数据复制过来。本人oracle不是很好,请高手帮助,谢谢!
存在用户 A 和用户 B ,B中有表test,现要在 A 中通过存储过程实现:判断A是否有表 test,如果没有,使用create table test as (select * from B.test)将B中表结构及数据复制到 A 中,如果有 test ,则使用 delete 语句先清除 A 中 test 表记录,然后用 insert into select 语句把 B 中数据复制过来。本人oracle不是很好,请高手帮助,谢谢!
SQL> create or replace procedure gzd03 as
2 v_count number;
3 begin
4 select count(*)
5 into v_count
6 from user_tables r
7 where r.table_name = 'TEST';
8 if (v_count <> 0) then
9 execute immediate 'create table test as (select * from B.test)';
10 else
11 execute immediate 'truncate table a.test';
12 execute immediate 'insert into a.test select * from b.test';
13 end if;
14 end;
15 /Procedure createdSQL>
v_count number;
begin
select count(*) into v_count from user_tables r where r.table_name = 'bd_corp';
dbms_output.put_line(v_count);
if (v_count = 0) then
execute immediate 'create table ceshi.bd_corp as (select * from bbwnc.bd_corp)';
else
execute immediate 'truncate table ceshi.bd_corp';
execute immediate 'insert into ceshi.bd_corp select * from bbwnc.bd_corp';
end if;
end;在ceshi下创建并执行,但是在执行 execute immediate 'create table ceshi.bd_corp as (select * from bbwnc.bd_corp)';
时,报“表或视图不存在”
这个用户有没有权限访问bbwnc.bd_corp这个表的权限啊在bbwnc下执行
grant select on bd_corp to ceshi;
v_count number;
begin
select count(*) into v_count from user_tables r where r.table_name =upper('bd_corp');
dbms_output.put_line(v_count);
if (v_count = 0) then
execute immediate 'create table ceshi.bd_corp as (select * from bbwnc.bd_corp)';
else
execute immediate 'truncate table ceshi.bd_corp';
execute immediate 'insert into ceshi.bd_corp select * from bbwnc.bd_corp';
end if;
end;--bbwnc 下赋予ceshi
grant select on bbwnc.bd_corp to ceshi
grant all on test to A;
--2、在A中建存储过程
CREATE OR REPLACE PROCEDURE p_sync_from_b IS
cnt PLS_INTEGER;
IsExists BOOLEAN := TRUE;
BEGIN
BEGIN
SELECT COUNT(1) INTO cnt FROM test;
EXCEPTION
WHEN OTHERS THEN
IsExists := FALSE;
END;
IF NOT IsExists THEN
EXECUTE IMMEDIATE 'create table test as select * from b.test';
ELSE
DELETE FROM test;
INSERT INTO test
SELECT * FROM b.test;
COMMIT;
END IF;
END;
bd_corp 这个是bbwnc用户的 你要用bbwnc登录执行grant select on bd_corp to ceshi;