PLS-00103

[liangfu]

create or replace procedure sp_part_ticketdate(
i_startday in number,
i_endday in number
) is
v_partname varchar2(30);
v_fornum number;
begin
  
  if i_startday<i_endday then
    --v_fornum:=i_startday;
    v_partname:='part'||to_char(v_fornum);
    for v_fornum in i_startday..i_endday loop
      alter table tb_dw_ticketdate add partition v_partname values(to_char(v_fornum));

    end loop;
  end if;
end sp_part_ticketdate;

PROCEDURE SCOTT.SP_PART_TICKETDATE 编译错误

错误：PLS-00103: 出现符号 "ALTER"在需要下列之一时：
        ( begin case declare 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
行：13
文本：alter table tb_dw_ticketdate add partition v_partname values(to_char(v_fornum));

[无敌小二傻]

execute immediate 'alter table....';

[liangfu]

引用 1 楼 lyliu602 的回复:

execute immediate 'alter table....';

加上这就对了，能解释下这是为什么吗？？

[无敌小二傻]

引用 2 楼 liangfu 的回复:

[quote=引用 1 楼 lyliu602 的回复:] execute immediate 'alter table....';

加上这就对了，能解释下这是为什么吗？？[/quote] plsql的程序块/procedure/trigger...是不能加入ddl语句的，必须通过动态sql去执行

[yinan9]

create or replace procedure sp_part_ticketdate(
i_startday in number,
i_endday in number
) is
v_partname varchar2(30);
v_fornum number;
str_sql varchar2(250) :='';
begin

  if i_startday<i_endday then
    --v_fornum:=i_startday;
    v_partname:='part'||to_char(v_fornum);
    for v_fornum in i_startday..i_endday loop
     str_sql:= 'alter table tb_dw_ticketdate add partition'|| v_partname||' values('||to_char(v_fornum)||')';
     execute immediate str_sql; 
    end loop;
  end if;
end sp_part_ticketdate;