create or replace procedure sp_dw_launchdate(
i_startday in number,
i_endday in number,
o_msg out varchar2
) is
v_partname varchar2(30);
v_fornum number;
v_str varchar2(300);
date_error exception;
begin
if i_startday<i_endday then
for v_fornum in i_startday..i_endday loop
v_partname:='part_'||to_char(v_fornum);
v_str:='alter table tb_dw_launchdate add partition '||v_partname|| ' values('''||v_fornum||''')';
dbms_output.put_line(v_str);
execute immediate v_str;
o_msg:='执行成功!';
end loop;
end if;
if i_startday>=i_endday then
raise date_error;
end if;
--向表tb_dw_ticketdate插入数据
create table tb_dw_launchtemp as select * from tb_dw_newlaunchdata where 1<>1; execute immediate 'insert into tb_dw_launchtemp select * from tb_dw_newlaunchdata n where n.insertdate='||i_endday;
alter table tb_dw_launchtemp drop column ticketdate;
insert into tb_dw_launchdate select * from tb_dw_launchtemp;
drop table tb_dw_launchtemp; commit;
--异常处理
exception
when date_error then
o_msg := '时间参数输入错误!结束时间必须大于开始时间!!';
when others then
--记录异常信息
o_msg := '执行失败! ';
rollback;
end sp_dw_launchdate;PROCEDURE SCOTT.SP_DW_LAUNCHDATE 编译错误错误:PLS-00103: 出现符号 "CREATE"在需要下列之一时:
( begin case declare end
exception exit for goto if loop mod null pragma raise return
select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
continue close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe purge
行:28
文本:create table tb_dw_launchtemp as select * from tb_dw_newlaunchdata where 1<>1;
i_startday in number,
i_endday in number,
o_msg out varchar2
) is
v_partname varchar2(30);
v_fornum number;
v_str varchar2(300);
date_error exception;
begin
if i_startday<i_endday then
for v_fornum in i_startday..i_endday loop
v_partname:='part_'||to_char(v_fornum);
v_str:='alter table tb_dw_launchdate add partition '||v_partname|| ' values('''||v_fornum||''')';
dbms_output.put_line(v_str);
execute immediate v_str;
o_msg:='执行成功!';
end loop;
end if;
if i_startday>=i_endday then
raise date_error;
end if;
--向表tb_dw_ticketdate插入数据
create table tb_dw_launchtemp as select * from tb_dw_newlaunchdata where 1<>1; execute immediate 'insert into tb_dw_launchtemp select * from tb_dw_newlaunchdata n where n.insertdate='||i_endday;
alter table tb_dw_launchtemp drop column ticketdate;
insert into tb_dw_launchdate select * from tb_dw_launchtemp;
drop table tb_dw_launchtemp; commit;
--异常处理
exception
when date_error then
o_msg := '时间参数输入错误!结束时间必须大于开始时间!!';
when others then
--记录异常信息
o_msg := '执行失败! ';
rollback;
end sp_dw_launchdate;PROCEDURE SCOTT.SP_DW_LAUNCHDATE 编译错误错误:PLS-00103: 出现符号 "CREATE"在需要下列之一时:
( begin case declare end
exception exit for goto if loop mod null pragma raise return
select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
continue close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe purge
行:28
文本:create table tb_dw_launchtemp as select * from tb_dw_newlaunchdata where 1<>1;
解决方案 »
- SYS_REFCURSOR 和 CURSOR 的区别
- 从客户端登陆数据库服务器,使用SQL*PLUS worksheet 时,用同样的用户(SYSTEM)密码,和服务器名,可以以NORMAL身份登陆,确不能以DBA身
- 请问oracle8i与9i有什么区别啊?
- 大家好,我请教一个关于Group by 子句的选择题,考试用的模拟题
- 怎么判断某个日期在某段范围内
- .net 调用oracle 编写的存储过程,怎样得到表的集合
- linux 下oracle8.1.7的安装
- 急问:oracle中按位与的操作是什么呀?
- 怎么样才能加大日志组大小,增加日志组个数,增加归档的IO速度
- oracle insert into
- Forms Builder 怎么从Form1 ,进入Form2
- 求asp.net编程高手培训本人,培训费按天计算
alter table tb_dw_launchtemp drop column ticketdate;
drop table tb_dw_launchtemp; ddl语句都要放到动态sql中的
execute immediate 'create table tb_dw_launchtemp as select * from tb_dw_newlaunchdata where 1<>1';
execute immediate 'alter table tb_dw_launchtemp drop column ticketdate';
execute immediate 'drop table tb_dw_launchtemp';