oracle，表判断是否存在，不存在则先创建表，再执行语句，存在则执行语句

Question

create or replace
procedure proc_dept_monthhours as
v_sql varchar2(20000):='';
v_flag number(10,0):=0;

begin
select count(*) into v_flag from user_TABLES where table_name='HO_DEPT_MONTHHOURS';

if v_flag>0 then

begin
insert into test_dept_monthhours(yearmonth,branch,subtotal)
select yearmonth,branch,sum(dm)+sum(sm)+sum(smt)+sum(sup)+sum(ltnc)

from ehruser.HO_DEPT_MONTHHOURS@traininglink
group by branch,yearmonth;

else
v_sql='create table HO_DEPT_MONTHHOURS
(
yearmonth VARCHAR2(7),
branch NVARCHAR2(200),
subtotal NUMBER
) ';
v_sql='create unique index TRDHR.HO_DEPT_MONTHHOURS_BRANCH on HO_DEPT_MONTHHOURS (BRANCH)
tablespace TRAINING_DATA
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
)';
v_sql='tablespace TRAINING_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
)';
execute immediate v_sql;
insert into test_dept_monthhours(yearmonth,branch,subtotal)
select yearmonth,branch,sum(dm)+sum(sm)+sum(smt)+sum(sup)+sum(ltnc)

from ehruser.HO_DEPT_MONTHHOURS@traininglink
group by branch,yearmonth;
end;
end if;
commit;
end proc_dept_monthhours;为什么这个存储过程建好打叉？？？哪错了？？

Answer 1

oracle，表判断是否存在，不存在则先创建表，再执行语句，存在则执行语句

需要判断一下这个表是否存在，可以参考这个语句

--判断表是否存在，如果存在则删除
declare
num number;
begin
select count(1) into num from all_tables where TABLE_NAME = 'EMP' and OWNER='SCOTT';
if num=1 then
execute immediate 'drop table EMP';
end if;
end;