CREATE OR REPLACE Procedure PSendSMS(PMsg Varchar2, PNum Varchar2,PPlanTime Date default sysdate,iRes out number)
As
iCount Number;
iPart Number;
strPart Varchar2(140);
Begin
iCount:=0;
if PNum='*' then
for c in (Select * from MI_Order Where CancelTime is Null) loop
iPart:=0;
While ((iPart*140+1)<=Length(PMsg)) Loop
strPart:=substr(PMsg,(iPart*140)+1,140);
Insert Into MI_SM_Send(Num,MsgContent,PlanSendTime)
Values(c.Num,strPart,PPlanTime);
iCount:=iCount+1;
iPart:=iPart+1;
end loop;
End Loop;
end if;
iRes:=iCount;
End;declare
iRes number;
begin
psendsms('拿到批示,几个部门商量怎么办。结果这个酒吧停业整顿30天,两名陪酒女郎行政拘留了15天。如此这番,几个部门依然不敢怠慢,又联合多家单位,制定了一个个体酒吧和餐厅的制度,将全市的酒吧和餐厅的老板组织起来,分批办了学习班。从那以后,上海的酒吧和餐厅的宰客现象得到了根本控制。','*',sysdate,ires);
dbms_output.put_line(iRes);
end;ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "T43.PSENDSMS", line 12
ORA-06512: at line 4
As
iCount Number;
iPart Number;
strPart Varchar2(140);
Begin
iCount:=0;
if PNum='*' then
for c in (Select * from MI_Order Where CancelTime is Null) loop
iPart:=0;
While ((iPart*140+1)<=Length(PMsg)) Loop
strPart:=substr(PMsg,(iPart*140)+1,140);
Insert Into MI_SM_Send(Num,MsgContent,PlanSendTime)
Values(c.Num,strPart,PPlanTime);
iCount:=iCount+1;
iPart:=iPart+1;
end loop;
End Loop;
end if;
iRes:=iCount;
End;declare
iRes number;
begin
psendsms('拿到批示,几个部门商量怎么办。结果这个酒吧停业整顿30天,两名陪酒女郎行政拘留了15天。如此这番,几个部门依然不敢怠慢,又联合多家单位,制定了一个个体酒吧和餐厅的制度,将全市的酒吧和餐厅的老板组织起来,分批办了学习班。从那以后,上海的酒吧和餐厅的宰客现象得到了根本控制。','*',sysdate,ires);
dbms_output.put_line(iRes);
end;ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "T43.PSENDSMS", line 12
ORA-06512: at line 4
--字符串缓冲不够,长度设大点
CREATE OR REPLACE Procedure PSendSMS(PMsg Varchar2, PNum Varchar2,PPlanTime Date default sysdate,iRes out number)
As
iCount Number;
iPart Number;
strPart Varchar2(1400); -- 这里将变量长度设置大点
Begin
iCount:=0;
if PNum='*' then
for c in (Select * from MI_Order Where CancelTime is Null) loop
iPart:=0;
While ((iPart*140+1)<=Length(PMsg)) Loop
strPart:=substr(PMsg,(iPart*140)+1,140);
Insert Into MI_SM_Send(Num,MsgContent,PlanSendTime)
Values(c.Num,strPart,PPlanTime);
iCount:=iCount+1;
iPart:=iPart+1;
end loop;
End Loop;
end if;
iRes:=iCount;
End;declare
iRes number;
begin
psendsms('拿到批示,几个部门商量怎么办。结果这个酒吧停业整顿30天,两名陪酒女郎行政拘留了15天。如此这番,几个部门依然不敢怠慢,又联合多家单位,制定了一个个体酒吧和餐厅的制度,将全市的酒吧和餐厅的老板组织起来,分批办了学习班。从那以后,上海的酒吧和餐厅的宰客现象得到了根本控制。','*',sysdate,ires);
dbms_output.put_line(iRes);
end;
iRes number;
-------------这里怎么又多了DECLARE
iPart Number;这2个定义的是否有问题呢