Question

oracle，表判断是否存在，不存在则先创建表，再执行语句，存在则执行语句，但是为什么建好打叉报错

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

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
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
begin
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 2

报什么错，把错误信息贴出来

追问

我现在oracle出了点问题，你可以粘到oracle试试，就是建好存储过程上面打一个红叉，不能运行

追答

//把你的SP修改了一下。编译看看，如果没错，就运行下，看报什么错误。代码太长，已经私信给你。

追问

还是这错