17,377
社区成员
发帖
与我相关
我的任务
分享执行语句报错
写了一个函数如下:
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
...全文
请发表友善的回复…
发表回复
zkvistor 2009-07-10
- 打赏
- 举报
why??
zkvistor 2009-07-01
- 打赏
- 举报
[Quote=引用 19 楼 cab1225 的回复:]
指向returnProName:=returnProName ||' ' || v_result;这一行
可能是指向returnProName的长度已经超过了定义的长度了。
[/Quote]
我只查三条记录，返回结果不会超过定义的长度：
函数如下：
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 可以得到我想要的结果，。。。。。
指向returnProName:=returnProName ||' ' || v_result;这一行
可能是指向returnProName的长度已经超过了定义的长度了。
[/Quote]
我只查三条记录，返回结果不会超过定义的长度：
函数如下：
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 可以得到我想要的结果，。。。。。
zkvistor 2009-07-01
- 打赏
- 举报
select orderhist.crdt as 订购日期,orderhist.orderid as 订单号,
contact.contactid as 客户编号,contact.name as 客户姓名, NewFormatPhone(Contact.contactid) as 客户电话,
address.zip as 邮编,fun_getprovincename(orderhist.provinceid) as 省份,orderhist.prodprice as 货运总额,
orderhist.mailprice as 运费,orderhist.totalprice as 总金额,grp.grpname as 组名,
usr.name as 员工姓名,Fun_GetDDFK(orderhist.result) as 订单反馈,fun_getcpzt(orderhist.status) as 订单状态,
orderhist.fbdt as 反馈日期,fun_getpsfs(orderhist.mailtype) as 订购方式,Fun_GetFKFS(orderhist.paytype) as 付款方式,
decode(orderhist.bill, '1', '需要', '不需要') as 要发票,address.address as 客户地址,orderhist.payer as 发票抬头,
topic.dsc as 客户备注,orderhist.mailid as 邮件编号, orderhist.note as 订单备注,orderhist.parcdt as 投递日期
From orderhist, CONTACT, ems, PHONE, Address, Topic, usr, grp
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
and orderhist.crdt >= to_date('2009-06-29 09:00:00', 'yyyy-mm-dd hh24:mi:ss')
and orderhist.crdt <= to_date('2009-06-29 09:10:00', 'yyyy-mm-dd hh24:mi:ss')
and usr.usrid = orderhist.crusr
and usr.defgrp = grp.grpid 这样执行就不会得到重复记录，查询得到3条记录
结果格式如下：
订购日期 订单号 客户姓名 客户电话 邮编 省份 货运总额 运费 总金额 组名 员工姓名 订单反馈 订单状态 订购方式 。。。
2009-06-29 56 李四 13009876096 152300 黑龙江 333 0 333 订购7502 张三 无反馈 订购 宅急送 。。。
但是加上 NewFormatName(orderhist.orderid) as 品名,还是报同样的错误：
写了个函数，运行函数没问题，但是在SQL中调用的时候报错：
ORA-06502 PL/SQL 数字或值错误，ORA-06512
指向returnProName:=returnProName ||' ' || v_result;这一行
看来函数本身还是有些问题
contact.contactid as 客户编号,contact.name as 客户姓名, NewFormatPhone(Contact.contactid) as 客户电话,
address.zip as 邮编,fun_getprovincename(orderhist.provinceid) as 省份,orderhist.prodprice as 货运总额,
orderhist.mailprice as 运费,orderhist.totalprice as 总金额,grp.grpname as 组名,
usr.name as 员工姓名,Fun_GetDDFK(orderhist.result) as 订单反馈,fun_getcpzt(orderhist.status) as 订单状态,
orderhist.fbdt as 反馈日期,fun_getpsfs(orderhist.mailtype) as 订购方式,Fun_GetFKFS(orderhist.paytype) as 付款方式,
decode(orderhist.bill, '1', '需要', '不需要') as 要发票,address.address as 客户地址,orderhist.payer as 发票抬头,
topic.dsc as 客户备注,orderhist.mailid as 邮件编号, orderhist.note as 订单备注,orderhist.parcdt as 投递日期
From orderhist, CONTACT, ems, PHONE, Address, Topic, usr, grp
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
and orderhist.crdt >= to_date('2009-06-29 09:00:00', 'yyyy-mm-dd hh24:mi:ss')
and orderhist.crdt <= to_date('2009-06-29 09:10:00', 'yyyy-mm-dd hh24:mi:ss')
and usr.usrid = orderhist.crusr
and usr.defgrp = grp.grpid 这样执行就不会得到重复记录，查询得到3条记录
结果格式如下：
订购日期 订单号 客户姓名 客户电话 邮编 省份 货运总额 运费 总金额 组名 员工姓名 订单反馈 订单状态 订购方式 。。。
2009-06-29 56 李四 13009876096 152300 黑龙江 333 0 333 订购7502 张三 无反馈 订购 宅急送 。。。
但是加上 NewFormatName(orderhist.orderid) as 品名,还是报同样的错误：
写了个函数，运行函数没问题，但是在SQL中调用的时候报错：
ORA-06502 PL/SQL 数字或值错误，ORA-06512
指向returnProName:=returnProName ||' ' || v_result;这一行
看来函数本身还是有些问题
inthirties 2009-06-30
- 打赏
- 举报
那有可能是returnProName超过了定义的长度了。你可以把 dbms_output.put_line(returnProName); 拿走，加个判断，就是大于1500(定义的长度)才打出来信息。看有没有这条信息打出来。
加了，没有任何信息
在sqlplus里执行set serveroutput on;
或者在plsql里加入dbms_output.enable();
是信息可以输出。
cab1225 2009-06-30
- 打赏
- 举报
指向returnProName:=returnProName ||' ' || v_result;这一行
可能是指向returnProName的长度已经超过了定义的长度了。
可能是指向returnProName的长度已经超过了定义的长度了。
inthirties 2009-06-30
- 打赏
- 举报
[Quote=引用 17 楼 inthirties 的回复:]
引用 16 楼 zkvistor 的回复:
加上 and orderhist.crdt>=to_date('2009-06-29 09:00:56', 'yyyy-mm-dd hh24:mi:ss')
and orderhist.crdt <=to_date('2009-06-29 09:10:00', 'yyyy-mm-dd hh24:mi:ss')
得到的就是29号 9点到9点10分的订单，总共有13条数据， 但是加上 之后一执行 得到了很多重复的记录 ，不知为什么，郁闷中。。。。。
是这里的问题么？
感觉不像，是不是其他的问题呀。
[/Quote]
检查一下其他的连接条件。
引用 16 楼 zkvistor 的回复:
加上 and orderhist.crdt>=to_date('2009-06-29 09:00:56', 'yyyy-mm-dd hh24:mi:ss')
and orderhist.crdt <=to_date('2009-06-29 09:10:00', 'yyyy-mm-dd hh24:mi:ss')
得到的就是29号 9点到9点10分的订单，总共有13条数据， 但是加上 之后一执行 得到了很多重复的记录 ，不知为什么，郁闷中。。。。。
是这里的问题么？
感觉不像，是不是其他的问题呀。
[/Quote]
检查一下其他的连接条件。
inthirties 2009-06-30
- 打赏
- 举报
[Quote=引用 16 楼 zkvistor 的回复:]
加上 and orderhist.crdt>=to_date('2009-06-29 09:00:56', 'yyyy-mm-dd hh24:mi:ss')
and orderhist.crdt <=to_date('2009-06-29 09:10:00', 'yyyy-mm-dd hh24:mi:ss')
得到的就是29号 9点到9点10分的订单，总共有13条数据， 但是加上 之后一执行 得到了很多重复的记录 ，不知为什么，郁闷中。。。。。
[/Quote]
是这里的问题么？
感觉不像，是不是其他的问题呀。
加上 and orderhist.crdt>=to_date('2009-06-29 09:00:56', 'yyyy-mm-dd hh24:mi:ss')
and orderhist.crdt <=to_date('2009-06-29 09:10:00', 'yyyy-mm-dd hh24:mi:ss')
得到的就是29号 9点到9点10分的订单，总共有13条数据， 但是加上 之后一执行 得到了很多重复的记录 ，不知为什么，郁闷中。。。。。
[/Quote]
是这里的问题么？
感觉不像，是不是其他的问题呀。
zkvistor 2009-06-30
- 打赏
- 举报
原因查到了，的确是 超过了长度
select orderhist.crdt as 订购日期,NewFormatName(orderhist.orderid) as 品名, orderhist.orderid as 订单号,contact.contactid as 客户编号,contact.name as 客户姓名,
NewFormatPhone(Contact.contactid) as 客户电话, address.zip as 邮编,fun_getprovincename(orderhist.provinceid) as 省份,
orderhist.prodprice as 货运总额,orderhist.mailprice as 运费,orderhist.totalprice as 总金额,grp.grpname as 组名,
usr.name as 员工姓名,Fun_GetDDFK(orderhist.result) as 订单反馈,fun_getcpzt(orderhist.status) as 订单状态,
orderhist.fbdt as 反馈日期,fun_getpsfs(orderhist.mailtype) as 订购方式,Fun_GetFKFS(orderhist.paytype) as 付款方式,
decode(orderhist.bill, '1', '需要', '不需要') as 要发票,address.address as 客户地址,orderhist.payer as 发票抬头,
topic.dsc as 客户备注,orderhist.mailid as 邮件编号,orderhist.note 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
and orderhist.crdt>=to_date('2009-06-29 09:00:56', 'yyyy-mm-dd hh24:mi:ss')
and orderhist.crdt <=to_date('2009-06-29 09:10:00', 'yyyy-mm-dd hh24:mi:ss')
最后得到的结果格式如下：
订购日期 订单号 客户姓名 客户电话 邮编 省份 货运总额 运费 总金额 组名 员工姓名 订单反馈 订单状态 订购方式 。。。
2009-06-29 56 李四 13009876096 152300 黑龙江 333 0 333 订购7502 张三 无反馈 订购 宅急送 。。。
如果只执行：
select orderhist.crdt as 订购日期,NewFormatName(orderhist.orderid) as 品名, orderhist.orderid as 订单号,contact.contactid as 客户编号,contact.name as 客户姓名,
NewFormatPhone(Contact.contactid) as 客户电话, address.zip as 邮编,fun_getprovincename(orderhist.provinceid) as 省份,
orderhist.prodprice as 货运总额,orderhist.mailprice as 运费,orderhist.totalprice as 总金额,grp.grpname as 组名,
usr.name as 员工姓名,Fun_GetDDFK(orderhist.result) as 订单反馈,fun_getcpzt(orderhist.status) as 订单状态,
orderhist.fbdt as 反馈日期,fun_getpsfs(orderhist.mailtype) as 订购方式,Fun_GetFKFS(orderhist.paytype) as 付款方式,
decode(orderhist.bill, '1', '需要', '不需要') as 要发票,address.address as 客户地址,orderhist.payer as 发票抬头,
topic.dsc as 客户备注,orderhist.mailid as 邮件编号,orderhist.note 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
得到的是所有的订单信息
加上 and orderhist.crdt>=to_date('2009-06-29 09:00:56', 'yyyy-mm-dd hh24:mi:ss')
and orderhist.crdt <=to_date('2009-06-29 09:10:00', 'yyyy-mm-dd hh24:mi:ss')
得到的就是29号 9点到9点10分的订单，总共有13条数据， 但是加上 之后一执行 得到了很多重复的记录 ，不知为什么，郁闷中。。。。。
select orderhist.crdt as 订购日期,NewFormatName(orderhist.orderid) as 品名, orderhist.orderid as 订单号,contact.contactid as 客户编号,contact.name as 客户姓名,
NewFormatPhone(Contact.contactid) as 客户电话, address.zip as 邮编,fun_getprovincename(orderhist.provinceid) as 省份,
orderhist.prodprice as 货运总额,orderhist.mailprice as 运费,orderhist.totalprice as 总金额,grp.grpname as 组名,
usr.name as 员工姓名,Fun_GetDDFK(orderhist.result) as 订单反馈,fun_getcpzt(orderhist.status) as 订单状态,
orderhist.fbdt as 反馈日期,fun_getpsfs(orderhist.mailtype) as 订购方式,Fun_GetFKFS(orderhist.paytype) as 付款方式,
decode(orderhist.bill, '1', '需要', '不需要') as 要发票,address.address as 客户地址,orderhist.payer as 发票抬头,
topic.dsc as 客户备注,orderhist.mailid as 邮件编号,orderhist.note 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
and orderhist.crdt>=to_date('2009-06-29 09:00:56', 'yyyy-mm-dd hh24:mi:ss')
and orderhist.crdt <=to_date('2009-06-29 09:10:00', 'yyyy-mm-dd hh24:mi:ss')
最后得到的结果格式如下：
订购日期 订单号 客户姓名 客户电话 邮编 省份 货运总额 运费 总金额 组名 员工姓名 订单反馈 订单状态 订购方式 。。。
2009-06-29 56 李四 13009876096 152300 黑龙江 333 0 333 订购7502 张三 无反馈 订购 宅急送 。。。
如果只执行：
select orderhist.crdt as 订购日期,NewFormatName(orderhist.orderid) as 品名, orderhist.orderid as 订单号,contact.contactid as 客户编号,contact.name as 客户姓名,
NewFormatPhone(Contact.contactid) as 客户电话, address.zip as 邮编,fun_getprovincename(orderhist.provinceid) as 省份,
orderhist.prodprice as 货运总额,orderhist.mailprice as 运费,orderhist.totalprice as 总金额,grp.grpname as 组名,
usr.name as 员工姓名,Fun_GetDDFK(orderhist.result) as 订单反馈,fun_getcpzt(orderhist.status) as 订单状态,
orderhist.fbdt as 反馈日期,fun_getpsfs(orderhist.mailtype) as 订购方式,Fun_GetFKFS(orderhist.paytype) as 付款方式,
decode(orderhist.bill, '1', '需要', '不需要') as 要发票,address.address as 客户地址,orderhist.payer as 发票抬头,
topic.dsc as 客户备注,orderhist.mailid as 邮件编号,orderhist.note 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
得到的是所有的订单信息
加上 and orderhist.crdt>=to_date('2009-06-29 09:00:56', 'yyyy-mm-dd hh24:mi:ss')
and orderhist.crdt <=to_date('2009-06-29 09:10:00', 'yyyy-mm-dd hh24:mi:ss')
得到的就是29号 9点到9点10分的订单，总共有13条数据， 但是加上 之后一执行 得到了很多重复的记录 ，不知为什么，郁闷中。。。。。
zkvistor 2009-06-29
- 打赏
- 举报
[Quote=引用 5 楼 welyngj 的回复:]
能不能给创建的语法？
[/Quote]
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,
能不能给创建的语法？
[/Quote]
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,
zkvistor 2009-06-29
- 打赏
- 举报
[Quote=引用 6 楼 inthirties 的回复:]
指向returnProName:=returnProName ||' ' || v_result;这一行
可能是指向returnProName的长度已经超过了定义的长度了。
[/Quote]
恩，我看看
指向returnProName:=returnProName ||' ' || v_result;这一行
可能是指向returnProName的长度已经超过了定义的长度了。
[/Quote]
恩，我看看
zkvistor 2009-06-29
- 打赏
- 举报
[Quote=引用 13 楼 zxf_feng 的回复:]
能把表的定义给全了吗，
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 or…
[/Quote]
完整的SQL语句：
select orderhist.crdt as 订购日期,NewFormatName(orderhist.orderid) as 品名, orderhist.orderid as 订单号,contact.contactid as 客户编号,contact.name as 客户姓名,
NewFormatPhone(Contact.contactid) as 客户电话, address.zip as 邮编,fun_getprovincename(orderhist.provinceid) as 省份,
orderhist.prodprice as 货运总额,orderhist.mailprice as 运费,orderhist.totalprice as 总金额,grp.grpname as 组名,
usr.name as 员工姓名,Fun_GetDDFK(orderhist.result) as 订单反馈,fun_getcpzt(orderhist.status) as 订单状态,
orderhist.fbdt as 反馈日期,fun_getpsfs(orderhist.mailtype) as 订购方式,Fun_GetFKFS(orderhist.paytype) as 付款方式,
decode(orderhist.bill, '1', '需要', '不需要') as 要发票,address.address as 客户地址,orderhist.payer as 发票抬头,
topic.dsc as 客户备注,orderhist.mailid as 邮件编号,orderhist.note 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
and orderhist.crdt>=to_date('2009-06-29 09:00:56', 'yyyy-mm-dd hh24:mi:ss')
and orderhist.crdt<=to_date('2009-06-29 09:10:00', 'yyyy-mm-dd hh24:mi:ss')
怕太复杂，所以把其中的一些显示字段给去掉了，
create table TOPIC
(
CONTACTID VARCHAR2(16) not null, （contact表的主键 contact联系人表）
TOPICID VARCHAR2(10) not null,
ENTITYID VARCHAR2(16),
TYPE VARCHAR2(128),
DSC VARCHAR2(500)
)
comment on column TOPIC.CONTACTID
is '客户ID';
comment on column TOPIC.TOPICID
is '客户描述ID';
comment on column TOPIC.ENTITYID
is '客户所属公司编号';
comment on column TOPIC.TYPE
is '客户描述类型';
comment on column TOPIC.DSC
is '客户描述信息';
create table USR 用户信息表
(
USRID VARCHAR2(10) not null,
NAME VARCHAR2(20),
PASSWORD VARCHAR2(10),
TITLE VARCHAR2(10),
DEFGRP VARCHAR2(10),
ACDGROUP VARCHAR2(10) default 5001
)
comment on column USR.USRID
is '话务员ID';
comment on column USR.NAME
is '话务员名称';
comment on column USR.PASSWORD
is '话务员口令';
comment on column USR.TITLE
is '职务（NAMES表的ID）';
comment on column USR.DEFGRP
is '默认工作组';
create table GRP （组）
(
GRPID VARCHAR2(10) not null,组编号
GRPNAME VARCHAR2(20),组名
GRPMGR VARCHAR2(10),
AREACODE VARCHAR2(6),
TYPE VARCHAR2(10),
GRPDSC VARCHAR2(50)
)comment on column GRP.GRPID
is '工作组ID';
comment on column GRP.GRPNAME
is '工作名';
comment on column GRP.GRPMGR
is '工作组管理员ID';
comment on column GRP.AREACODE
is '所属电话中心';
comment on column GRP.TYPE
is 'IN:INBOUND,OUT:OUTBOUND';
能把表的定义给全了吗，
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 or…
[/Quote]
完整的SQL语句：
select orderhist.crdt as 订购日期,NewFormatName(orderhist.orderid) as 品名, orderhist.orderid as 订单号,contact.contactid as 客户编号,contact.name as 客户姓名,
NewFormatPhone(Contact.contactid) as 客户电话, address.zip as 邮编,fun_getprovincename(orderhist.provinceid) as 省份,
orderhist.prodprice as 货运总额,orderhist.mailprice as 运费,orderhist.totalprice as 总金额,grp.grpname as 组名,
usr.name as 员工姓名,Fun_GetDDFK(orderhist.result) as 订单反馈,fun_getcpzt(orderhist.status) as 订单状态,
orderhist.fbdt as 反馈日期,fun_getpsfs(orderhist.mailtype) as 订购方式,Fun_GetFKFS(orderhist.paytype) as 付款方式,
decode(orderhist.bill, '1', '需要', '不需要') as 要发票,address.address as 客户地址,orderhist.payer as 发票抬头,
topic.dsc as 客户备注,orderhist.mailid as 邮件编号,orderhist.note 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
and orderhist.crdt>=to_date('2009-06-29 09:00:56', 'yyyy-mm-dd hh24:mi:ss')
and orderhist.crdt<=to_date('2009-06-29 09:10:00', 'yyyy-mm-dd hh24:mi:ss')
怕太复杂，所以把其中的一些显示字段给去掉了，
create table TOPIC
(
CONTACTID VARCHAR2(16) not null, （contact表的主键 contact联系人表）
TOPICID VARCHAR2(10) not null,
ENTITYID VARCHAR2(16),
TYPE VARCHAR2(128),
DSC VARCHAR2(500)
)
comment on column TOPIC.CONTACTID
is '客户ID';
comment on column TOPIC.TOPICID
is '客户描述ID';
comment on column TOPIC.ENTITYID
is '客户所属公司编号';
comment on column TOPIC.TYPE
is '客户描述类型';
comment on column TOPIC.DSC
is '客户描述信息';
create table USR 用户信息表
(
USRID VARCHAR2(10) not null,
NAME VARCHAR2(20),
PASSWORD VARCHAR2(10),
TITLE VARCHAR2(10),
DEFGRP VARCHAR2(10),
ACDGROUP VARCHAR2(10) default 5001
)
comment on column USR.USRID
is '话务员ID';
comment on column USR.NAME
is '话务员名称';
comment on column USR.PASSWORD
is '话务员口令';
comment on column USR.TITLE
is '职务（NAMES表的ID）';
comment on column USR.DEFGRP
is '默认工作组';
create table GRP （组）
(
GRPID VARCHAR2(10) not null,组编号
GRPNAME VARCHAR2(20),组名
GRPMGR VARCHAR2(10),
AREACODE VARCHAR2(6),
TYPE VARCHAR2(10),
GRPDSC VARCHAR2(50)
)comment on column GRP.GRPID
is '工作组ID';
comment on column GRP.GRPNAME
is '工作名';
comment on column GRP.GRPMGR
is '工作组管理员ID';
comment on column GRP.AREACODE
is '所属电话中心';
comment on column GRP.TYPE
is 'IN:INBOUND,OUT:OUTBOUND';
阿三 2009-06-29
- 打赏
- 举报
能把表的定义给全了吗，
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
这里的topic,usr,grp表如何定义的，还有能给几条数据吗？
单独这们执行select NewFormatName(orderhist.orderid) as 品名
From orderhist
没有什么问题呀
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
这里的topic,usr,grp表如何定义的，还有能给几条数据吗？
单独这们执行select NewFormatName(orderhist.orderid) as 品名
From orderhist
没有什么问题呀
banana_97 2009-06-29
- 打赏
- 举报
ORA-06502 PL/SQL 数字或值错误應該是類型的問題
inthirties 2009-06-29
- 打赏
- 举报
[Quote=引用 10 楼 fosjos 的回复:]
引用楼主 zkvistor 的帖子:
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;
改成from orderdet od where od.orderid = sorderid 吧
否则不是什么都没过滤？
[/Quote]
确实你的程序好像没有用到传进来的sorderid哟。
from orderdet od,orderhist oh where od.orderid=oh.orderid; 这个逻辑对不对呀? 里面的orderhist oh表好像也没有用到
确定一下逻辑对不对
如果像ls的sql可以用的话。结果集少了，字符串不超长了，也不会出现原来的错了。
引用楼主 zkvistor 的帖子:
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;
改成from orderdet od where od.orderid = sorderid 吧
否则不是什么都没过滤？
[/Quote]
确实你的程序好像没有用到传进来的sorderid哟。
from orderdet od,orderhist oh where od.orderid=oh.orderid; 这个逻辑对不对呀? 里面的orderhist oh表好像也没有用到
确定一下逻辑对不对
如果像ls的sql可以用的话。结果集少了，字符串不超长了，也不会出现原来的错了。
fosjos 2009-06-29
- 打赏
- 举报
[Quote=引用楼主 zkvistor 的帖子:]
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;
[/Quote]
改成from orderdet od where od.orderid = sorderid 吧
否则不是什么都没过滤？
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;
[/Quote]
改成from orderdet od where od.orderid = sorderid 吧
否则不是什么都没过滤？
inthirties 2009-06-27
- 打赏
- 举报
[Quote=引用 6 楼 inthirties 的回复:]
指向returnProName:=returnProName ||' ' || v_result;这一行
可能是指向returnProName的长度已经超过了定义的长度了。
[/Quote]
你可以在循环里output一下returnProName的长度，看是循环到什么时候操过长度的。
指向returnProName:=returnProName ||' ' || v_result;这一行
可能是指向returnProName的长度已经超过了定义的长度了。
[/Quote]
你可以在循环里output一下returnProName的长度，看是循环到什么时候操过长度的。
inthirties 2009-06-27
- 打赏
- 举报
指向returnProName:=returnProName ||' ' || v_result;这一行
可能是指向returnProName的长度已经超过了定义的长度了。
可能是指向returnProName的长度已经超过了定义的长度了。
welyngj 2009-06-27
- 打赏
- 举报
能不能给创建的语法？
zkvistor 2009-06-27
- 打赏
- 举报
product(产品表:)
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) 产品优惠价
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) 产品优惠价
zkvistor 2009-06-27
- 打赏
- 举报
[Quote=引用 2 楼 welyngj 的回复:]
给一个表定义吧
[/Quote]
?
给一个表定义吧
[/Quote]
?
加载更多回复（2）
