写了一个函数如下:
create or replace function NewFormatName(sorderid in orderhist.orderid%type)
return varchar2 is returnProName varchar2(3000);
v_proName varchar2(500);
v_num number(10);
v_result varchar2(1000);
cursor c_proName is
select decode(od.prodname,'','',od.prodname) || '*' ||decode(od.upnum,0,'',od.upnum) || decode(od.spnum,0,'',od.spnum) from orderdet od,orderhist oh where od.orderid=oh.orderid;
begin
open c_proName;
loop
fetch c_proName into v_result;
exit when c_proName%notfound;
returnProName:=returnProName ||' ' || v_result;
end loop;
close c_proName;
return (returnProName);
end NewFormatName; od.upnum为number(10)类型写了个函数,运行函数没问题,但是在SQL中调用的时候报错:
ORA-06502 PL/SQL 数字或值错误,ORA-06512
指向returnProName:=returnProName ||' ' || v_result;这一行 调用的SQL:
select NewFormatName(orderhist.orderid) as 品名,orderhist.parcdt as 投递日期
From orderhist, CONTACT, ems, PHONE, Address, Topic, usr, grp,orderdet
where orderhist.contactid = contact.contactid
and orderhist.spellid = ems.spellid(+)
and phone.phoneid =
(select min(phoneid) from phone where phone.contactid = orderhist.getcontactid)
and orderhist.contactid = address.contactid
and topic.contactid(+) = contact.contactid
create or replace function NewFormatName(sorderid in orderhist.orderid%type)
return varchar2 is returnProName varchar2(3000);
v_proName varchar2(500);
v_num number(10);
v_result varchar2(1000);
cursor c_proName is
select decode(od.prodname,'','',od.prodname) || '*' ||decode(od.upnum,0,'',od.upnum) || decode(od.spnum,0,'',od.spnum) from orderdet od,orderhist oh where od.orderid=oh.orderid;
begin
open c_proName;
loop
fetch c_proName into v_result;
exit when c_proName%notfound;
returnProName:=returnProName ||' ' || v_result;
end loop;
close c_proName;
return (returnProName);
end NewFormatName; od.upnum为number(10)类型写了个函数,运行函数没问题,但是在SQL中调用的时候报错:
ORA-06502 PL/SQL 数字或值错误,ORA-06512
指向returnProName:=returnProName ||' ' || v_result;这一行 调用的SQL:
select NewFormatName(orderhist.orderid) as 品名,orderhist.parcdt as 投递日期
From orderhist, CONTACT, ems, PHONE, Address, Topic, usr, grp,orderdet
where orderhist.contactid = contact.contactid
and orderhist.spellid = ems.spellid(+)
and phone.phoneid =
(select min(phoneid) from phone where phone.contactid = orderhist.getcontactid)
and orderhist.contactid = address.contactid
and topic.contactid(+) = contact.contactid
prodid(varchar2) 产品ID 主键
prodname(varchar2) 产品名称
hprice number(10,2)价钱 prodid prodname hrpice
129004 嘉源V300水墨黑 699 orderhist(订单表)
orderid 订单ID 主键
mailid 邮件编号
contactid 联系人ID
result 订单反馈结果 (上述字段均为varchar2类型)
。 orderdet(订单详情表)
orderdetid 订单详情ID
orderid 订单ID(外键)
prodid 产品ID
prodname 产品名称
contactid 订购人ID
upnum(number) 订购个数
uprice(number) 产品原价
orderde(date) 订购日期
pnum number(10) 原价订购个数
spnum number(10)优惠价订购个数
sprice number(10,2) 产品优惠价
你可以在循环里output一下returnProName的长度,看是循环到什么时候操过长度的。
create table ORDERHIST
(
ORDERID VARCHAR2(16) not null,
MAILID VARCHAR2(20),
ENTITYID VARCHAR2(16),
SPELLID NUMBER(5) default 99999,
PROVINCEID VARCHAR2(16),
CITYID VARCHAR2(10),
CONTACTID VARCHAR2(16) not null,
PAYCONTACTID VARCHAR2(16),
GETCONTACTID VARCHAR2(16),
)
-- Add comments to the columns
comment on column ORDERHIST.ORDERID
is '订单ID';
comment on column ORDERHIST.MAILID
is '邮件编号';
comment on column ORDERHIST.ENTITYID
is '送货公司';
comment on column ORDERHIST.SPELLID
is 'SPELLID';
comment on column ORDERHIST.PROVINCEID
is '省';
comment on column ORDERHIST.CITYID
is '城市';
comment on column ORDERHIST.CONTACTID
is '联系人ID';
comment on column ORDERHIST.PAYCONTACTID
is '付款人编号';
comment on column ORDERHIST.GETCONTACTID
is '收货人编号';
comment on column ORDERHIST.CRUSR
is '记录创建人';
comment on column ORDERHIST.MDUSR
is '修改人';
comment on column ORDERHIST.MDDT
is '修改日期';
comment on column ORDERHIST.CBCRUSR
is 'CALLBACK创建者';
comment on column ORDERHIST.PARCELNM
is '送货单号';
comment on column ORDERHIST.STATUS
is '订单状态';
comment on column ORDERHIST.ACCOUNT
is '帐务信息';
comment on column ORDERHIST.RESULT
is '订单反馈结果';
comment on column ORDERHIST.ORDERTYPE
is '订单类型';
comment on column ORDERHIST.MAILTYPE
is '顾客订购方式';
comment on column ORDERHIST.PAYTYPE
is '顾客付款方式';
comment on column ORDERHIST.URGENT
is '紧急订单标志';
comment on column ORDERHIST.CONFIRM
is '索权标志';
comment on column ORDERHIST.CRDT
is '订购日期';
comment on column ORDERHIST.SENDDT
is '交寄日期';
comment on column ORDERHIST.FBDT
is '反馈日期';
comment on column ORDERHIST.OUTDT
is '分拣日期';
comment on column ORDERHIST.ACCDT
is '结帐日期';
comment on column ORDERHIST.TOTALPRICE
is '订单总价';
comment on column ORDERHIST.MAILPRICE
is '总运费';
comment on column ORDERHIST.PRODPRICE
is '商品总价';
comment on column ORDERHIST.NOWMONEY
is '已收货款';
comment on column ORDERHIST.POSTFEE
is '投递费';
comment on column ORDERHIST.CLEARFEE
is '实际结算费';
comment on column ORDERHIST.BILL
is '需要发票';
comment on column ORDERHIST.NOTE
is '订单备注';
comment on column ORDERHIST.CARDTYPE
is '信用卡类型';
comment on column ORDERHIST.CARDNUMBER
is '信用卡号码';
comment on column ORDERHIST.MEDIA
is '媒体调查ID';
comment on column ORDERHIST.CALLID
is '通话编号';
comment on column ORDERHIST.CALLBACKID
is 'CALLBACK表ID';
comment on column ORDERHIST.PARENTID
is '父订单编号';
comment on column ORDERHIST.CHILDID
is '子订单编号';
comment on column ORDERHIST.STARTTM
is '订单操作开始时间';
comment on column ORDERHIST.ENDTM
is '订单操作结束时间';
comment on column ORDERHIST.LASTSTATUS
is '订单上次状态';
comment on column ORDERHIST.REMARK
is '挂账付钱标志';
comment on column ORDERHIST.CARDRIGHTNUM
is '索权号';
comment on column ORDERHIST.EMSCLEARSTATUS
is 'EMS结帐状态';
comment on column ORDERHIST.REFUSE
is '是否收到退包标志';
comment on column ORDERHIST.PARCDT
is '邮寄包裹发出日期';
-- Create/Recreate indexes create index ORDER_GETCONTACT on ORDERHIST (GETCONTACTID)
tablespace IAGENT
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Create table
create table ORDERDET
(
ORDERDETID VARCHAR2(16) not null,
ORDERID VARCHAR2(16) not null,
PRODID VARCHAR2(16) not null,
CONTACTID VARCHAR2(16) not null,
PRODSCODE VARCHAR2(50),
PRODNAME VARCHAR2(50),
SOLDWITH VARCHAR2(10),
STATUS VARCHAR2(10),
RECKONING VARCHAR2(10),
RECKONINGDT DATE,
FBDT DATE,
UPRICE NUMBER(10,2),
UPNUM NUMBER(10),
SPRICE NUMBER(10,2),
SPNUM NUMBER(10),
PAYMENT NUMBER(10,2),
FREIGHT NUMBER(10,2),
POSTFEE NUMBER(10,2) default 0,
CLEARFEE NUMBER(10,2),
ORDERDT DATE default sysdate,
PROVINCEID VARCHAR2(10),
STATE VARCHAR2(10), MAGAZINEID VARCHAR2(20)
)
-- Add comments to the columns
comment on column ORDERDET.ORDERDETID
is '订单详细ID';
comment on column ORDERDET.ORDERID
is '订单ID';
comment on column ORDERDET.PRODID
is '产品ID';
comment on column ORDERDET.CONTACTID
is '订购人';
comment on column ORDERDET.PRODSCODE
is '产品代码';
comment on column ORDERDET.PRODNAME
-- Create table
create table CONTACT
(
CONTACTID VARCHAR2(16) not null,
NAME VARCHAR2(20),
SEX VARCHAR2(10),
TITLE VARCHAR2(10),
DEPT VARCHAR2(10),
CONTACTTYPE VARCHAR2(2),
EMAIL VARCHAR2(128),
WEBADDR VARCHAR2(128),
CRDT DATE,
CRTM DATE,
CRUSR VARCHAR2(10),
MDDT DATE,
否则不是什么都没过滤?
确实你的程序好像没有用到传进来的sorderid哟。from orderdet od,orderhist oh where od.orderid=oh.orderid; 这个逻辑对不对呀? 里面的orderhist oh表好像也没有用到
确定一下逻辑对不对如果像ls的sql可以用的话。结果集少了,字符串不超长了,也不会出现原来的错了。
From orderhist, CONTACT, ems, PHONE, Address, Topic, usr, grp,orderdet
where orderhist.contactid = contact.contactid
and orderhist.spellid = ems.spellid(+)
and phone.phoneid =
(select min(phoneid) from phone where phone.contactid = orderhist.getcontactid)
and orderhist.contactid = address.contactid
and topic.contactid(+) = contact.contactid这里的topic,usr,grp表如何定义的,还有能给几条数据吗?单独这们执行select NewFormatName(orderhist.orderid) as 品名
From orderhist没有什么问题呀
那有可能是returnProName超过了定义的长度了。你可以把 dbms_output.put_line(returnProName); 拿走,加个判断,就是大于1500(定义的长度)才打出来信息。看有没有这条信息打出来。
加了,没有任何信息
在sqlplus里执行set serveroutput on;
或者在plsql里加入dbms_output.enable(); 是信息可以输出。
我只查三条记录,返回结果不会超过定义的长度:
函数如下:create or replace function NewFormatName(sorderid in orderhist.orderid%type)
return varchar2 is returnProName varchar2(4000);
v_proName varchar2(500);
v_num number(10);
v_result varchar2(3000);
cursor c_proName is
select decode(od.prodname,'','',od.prodname) || '*' || to_char(decode(od.upnum,0,'',od.upnum)) || to_char(decode(od.spnum,0,'',od.spnum)) from orderdet od,orderhist oh where od.orderid=sorderid;
begin
open c_proName;
loop
fetch c_proName into v_result;
exit when c_proName%notfound;
returnProName:=returnProName ||'' || v_result;
end loop;
close c_proName;
return (returnProName);
end NewFormatName;最终执行还是报同样的错误:但是单独执行:select decode(od.prodname,'','',od.prodname) || '*' || to_char(decode(od.upnum,0,'',od.upnum)) || to_char(decode(od.spnum,0,'',od.spnum)) from orderdet od,orderhist oh 可以得到我想要的结果,。。