数据库存储过程的有关问题，

2012-12-15

数据库存储过程的问题，求助。（9）创建存储过程，要求将OrderMaster表中每一个订单所对应的明细数据信息按规

数据库存储过程的问题，求助。
（9）创建存储过程，要求将OrderMaster表中每一个订单所对应的明细数据信息按规定格式输出，
格式如图7-1所示。
--===================订单及其明细数据信息====================
---------------------------------------------------
订单编号      200801090001
---------------------------------------------------
商品编号         数量     价格
P20050001      2      222
P20050002      2       222
P20050003      2      1
-----------------------------------
合计订单总金额      3103.50

create procedure biaoge 
as
begin


declare @orderno char(20),@productno char(20),@quantity int,@price    numeric(7,2)
declare  @text2 char(100)

DECLARE NIHAO cursor for
select b.orderno,b.productno,b.quantity,b.price 
 
from OrderMaster a,OrderDetail b
where a.orderNo=b.orderNo

select @text2='===================订单及其明细数据信息==================== '
print @text2
select @text2='-------------------------------------'
print   @text2
select @text2='订单编号'
print  @text2
select @text2='-------------------------------------'
print   @text2
select @text2='商品编号    数量     价格'
print   @text2
select @text2='-------------------------------------'
print   @text2
select @text2='合计订单总金额'
print  @text2   
open nihao

fetch nihao into @orderno,@productno,@quantity,@price

while (@@FETCH_STATUS=0)
begin
select @text2=convert(char(20),@orderno)
print   @text2
select @text2=convert(char(20),@productno)+'  '+@quantity+'    '+@price
print   @text2
fetch  nihao into @orderno,@productno,@quantity,@price
end 
close nihao
deallocate nihao
end
exec biaoge

提示有错误：
消息 245，级别 16，状态 1，第 31 行
在将 varchar 值 'P20050001 ' 转换成数据类型 int 时失败。
[最优解释]
好多
[其他解释]
试试

WHILE ( @@FETCH_STATUS = 0 ) 
     BEGIN
         SELECT  @text2 = CONVERT(CHAR(20), @orderno)
         PRINT @text2
         SELECT  @text2 = CONVERT(CHAR(20), @productno) + '  ' + CONVERT(VARCHAR(20),@quantity)
                 + '    ' + CONVERT(VARCHAR(20),@price)
         PRINT @text2
         FETCH nihao INTO @orderno, @productno, @quantity, @price
     END

[其他解释]
附上原始数据表

set nocount on
set dateformat ymd
use master
go
  if not exists(select * from syslogins where name='user01')
  exec sp_addlogin user01,888888
go
/*create database*/
if exists(select *from sysdatabases where name='OrderDB')
     drop database OrderDB
go
create database OrderDB
on primary
(name='OrderDB1',
filename='d:\OrderDB1.mdf',
size=3,
maxsize=5,
filegrowth=1)
log on
(name=d20011812_log,
filename='d:\OrderLog1.ldf',
size=1,
maxsize=5,
filegrowth=1)
go

/*data mydatabase*/
use OrderDB
go

/* add my user*/
exec sp_adduser user01,user01
go

-------------------------------------------------------
-------------------------------------------------------

/*员工人事表*/
print'creat table Employee'
go
CREATE TABLE Employee(
  employeeNo   char(8)        not null             /*员工编号*/
     check(employeeNo like '[E][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
  employeeName varchar(10)    not null,             /*员工姓名*/
  sex          char(1)        not null,             /*员工性别*/
  birthday     datetime       null,                 /*员工生日*/
  address      varchar(50)    null,                 /*员工住址*/
  telephone    varchar(20)    null,                 /*员工电话*/
  hireDate     datetime       not null,             /*雇佣日期*/
  department   varchar(30)    not null,             /*所属部门*/
  headShip     varchar(10)    not null,             /*职务*/
  salary       numeric(8,2)   not null,             /*薪水*/
    constraint EmployeePK primary key (employeeNo)
)
go

go

/*人事表（employee）数据：*/
insert Employee values('E2005001','喻自强','M','19650415','南京市青海路18号',       '13817605008', '20050206','财务科','科长',5800.80)
insert Employee values('E2005002','张小梅','F','19731101','上海市北京路8号',        '13607405016', '20050328','业务科','职员',2400)
insert Employee values('E2005003','张小娟','F','19730306','上海市南京路66号',       '13707305025', '20050328','业务科','职员',2600)
insert Employee values('E2005004','张露',  'F','19670105','南昌市八一大道130号',    '15907205134', '20050328','业务科','科长',4100)
insert Employee values('E2005005','张小东','M','19730903','南昌市阳明路99号',       '15607105243', '20050328','业务科','职员',1800)
insert Employee values('E2006001','陈辉',  'M','19651101','南昌市青山路100号',      '13607705352', '20060328','办公室','主任',4000)
insert Employee values('E2006002','韩梅',  'F','19731211','上海市浦东大道6号',      '13807805461', '20061128','业务科','职员',2600)
insert Employee values('E2006003','刘风',  'F','19730521','江西财经大学5栋1-101室', '15907805578', '20060228','业务科','职员',2500)
insert Employee values('E2007001','吴浮萍','M','19730912','南昌高新开发区12号',       null,  '20070628','业务科','职员',2500)
insert Employee values('E2007002','高代鹏','M','19730102','南昌高新开发区56号',       null,  '20071128','办公室','文员',2000)

insert Employee values('E2008001','陈诗杰','M','19680106','江西财经大学12栋3-304室',  null,  '20081206','财务科','出纳',3200)
insert Employee values('E2008002','张良',  'M','19720216','上海市福州路135号',        null,  '20080228','业务科','职员',2700)
insert Employee values('E2008003','黄梅莹','F','19720515','上海市九江路88号',         null,  '20080228','业务科','职员',3100)
insert Employee values('E2008004','李虹冰','F','19721013','南昌市中山路1号',          null,  '20080528','业务科','职员',3400)
insert Employee values('E2008005','张小梅','F','19701106','深圳市阳关大道10号',       null,  '20081118','财务科','会计',5000)
go

-------------------------------------------------------
-------------------------------------------------------
/*客户表*/
print 'create Customer'
go
create table  Customer(
  customerNo    char(9)      not null primary key,/*客户号*/
           check(customerNo like '[C][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
  customerName  varchar(40)  not null,            /*客户名称*/
  telephone     varchar(20)  not null,            /*客户电话*/
  address       char(40)     not null,            /*客户住址*/
  zip           char(6)      null                 /*邮政编码*/
)
go

/*客户表（customer）数据：*/
insert Customer values('C20050001','统一股份有限公司',  '022-3566021',  '天津市', '220012')
insert Customer values('C20050002','兴隆股份有限公司',  '022-3562452',  '天津市', '220301')
insert Customer values('C20050003','上海生物研究室',    '010-2121000',  '北京市', '108001')
insert Customer values('C20050004','五一商厦',          '021-4532187',  '上海市', '210100')
insert Customer values('C20060001','大地商城',          '010-1165152',  '北京市', '100803')
insert Customer values('C20060002','联合股份有限公司',  '021-4568451',  '上海市', '210100')
insert Customer values('C20070001','南昌市电脑研制中心','0791-4412152', '南昌市', '330046')
insert Customer values('C20070002','世界技术开发公司',  '021-4564512',  '上海市', '210230')
insert Customer values('C20070003','万事达股份有限公司','022-4533141',  '天津市', '220400')
insert Customer values('C20080001','红度股份有限公司',  '010-5421585',  '北京市', '100800')
go

-------------------------------------------------------
-------------------------------------------------------
/*产品名称表*/
print 'create Product'
go
create table Product(
  productNo     char(9)       not null primary key, /*商品编号*/
  productName   varchar(40)   not null,             /*商品名称*/

productClass varchar(20) not null, /*商品类别*/
productPrice numeric(7,2) not null, /*商品定价*/
)
go

go

[其他解释]

go
/*产品名称表（product）数据：*/
insert Product values('P20050001','32M DRAM',             '内存',80.70)
insert Product values('P20050002','17寸显示器',           '显示器',700.00)
insert Product values('P20050003','120GB硬盘',            '存储器',300.00)
insert Product values('P20050004','3.5寸软驱',            '设备',35.00)
insert Product values('P20050005','键盘',                 '设备',100.60)
insert Product values('P20060001','VGA显示卡',            '显示器',200.60)
insert Product values('P20060002','网卡',                 '设备',66.00)
insert Product values('P20060003','Pentium100CPU',        '处理器',1200.00)
insert Product values('P20070001','1G DDR',               '内存',256.00)
insert Product values('P20070002','52倍速光驱',           '设备',200.00)
insert Product values('P20070003','计算机字典',           '图书',100.00)
insert Product values('P20070004','9600bits/s调制解调',   '设备',320.00)
insert Product values('P20080001','Pentium主板',          '主板',890.00)
insert Product values('P20080002','硕泰克SL—K8AN-RL主板','主板',1100.00)
insert Product values('P20080003','龙基777FT纯平显示器',  '显示器',900.00)

go

-------------------------------------------------------
-------------------------------------------------------
/*订单主表*/
print 'create OrderMaster'
go
create table OrderMaster(
  orderNo     char(12)     not null primary key,/*订单编号*/
  customerNo  char(9)      not null,            /*客户号*/
  salerNo     char(8)      not null,            /*业务员编号*/
  orderDate   datetime     not null,            /*订货日期*/
  orderSum    numeric(9,2) not null,            /*订单金额*/
  invoiceNo   char(10)     not null,            /*发票号码*/
  constraint OrderMasterFK1 foreign key(customerNo) references Customer(customerNo),
  constraint OrderMasterFK2 foreign key(salerNo) references Employee(employeeNo)

)
go

go
/*订单主表（sales）数据：*/
insert OrderMaster values('200801090001','C20050001','E2005002','20080109',0.00,'I000000001')
insert OrderMaster values('200801090002','C20050004','E2005003','20080109',0.00,'I000000002')
insert OrderMaster values('200801090003','C20080001','E2005002','20080109',0.00,'I000000003')
insert OrderMaster values('200802190001','C20050001','E2005003','20080219',0.00,'I000000004')
insert OrderMaster values('200802190002','C20070002','E2008002','20080219',0.00,'I000000005')
insert OrderMaster values('200803010001','C20070002','E2007001','20080301',0.00,'I000000006')
insert OrderMaster values('200803020001','C20050004','E2008003','20080302',0.00,'I000000007')
insert OrderMaster values('200803090001','C20070003','E2008004','20080309',0.00,'I000000008')
insert OrderMaster values('200805090001','C20060002','E2008002','20080509',0.00,'I000000009')
insert OrderMaster values('200806120001','C20050001','E2005002','20080612',0.00,'I000000010')
insert OrderMaster values('200901010001','C20050001','E2005002','20090101',0.00,'I000000011')
insert OrderMaster values('201206070001','C20050004','E2005003','20120607',0.00,'I000000012')
insert OrderMaster values('201206070002','C20070002','E2008002','20120607',0.00,'I000000013')
go

-------------------------------------------------------
-------------------------------------------------------
/*订单明细表*/
print 'create OrderDetail'
go
create table OrderDetail(
  orderNo   char(12)      not null,            /*订单编号*/
  productNo char(9)       not null,            /*产品编号*/
  quantity  int           not null,            /*销售数量*/
  price     numeric(7,2)  not null,            /*成交单价*/
  constraint OrderDetailPK primary key clustered(orderNo,productNo),
  constraint OrderDetailFK1 foreign key(orderNo) references OrderMaster(orderNo),
  constraint OrderDetailFK2 foreign key(productNo) references Product(productNo)
)
go

go
/*订单明细表（OrderDetail）数据：*/
insert OrderDetail values('200801090001','P20050001',5 , 80.70)
insert OrderDetail values('200801090001','P20050002',3 , 700.00)
insert OrderDetail values('200801090001','P20050003',2 , 300.00)

insert OrderDetail values('200802190001','P20060003',4 , 1200.00)
insert OrderDetail values('200802190001','P20070001',2 , 256.00)
insert OrderDetail values('200802190001','P20070002',5 , 200.00)

insert OrderDetail values('200801090002','P20080001',2 , 890.00)
insert OrderDetail values('200801090002','P20080003',2 , 900.00)
insert OrderDetail values('200801090002','P20060002',5 , 66.00)

insert OrderDetail values('200801090003','P20060001',3 , 200.60)
insert OrderDetail values('200801090003','P20050001',5 , 80.70)

insert OrderDetail values('200802190002','P20050003',2 , 300.00)

insert OrderDetail values('200802190002','P20050005',3 , 100.60)

insert OrderDetail values('200803010001','P20070001',4 , 256.00)
insert OrderDetail values('200803010001','P20050001',8 , 80.70)

insert OrderDetail values('200803020001','P20050001',2 , 80.70)
insert OrderDetail values('200803020001','P20070003',3 , 100.00)
insert OrderDetail values('200803020001','P20050002',1 , 700.00)

insert OrderDetail values('200803090001','P20070002',5 , 200.00)
insert OrderDetail values('200803090001','P20050003',4 , 300.00)
insert OrderDetail values('200803090001','P20070001',2 , 256.00)
insert OrderDetail values('200803090001','P20050004',5 , 35.00)
insert OrderDetail values('200803090001','P20070004',3 , 320.00)

insert OrderDetail values('200805090001','P20060003',8 , 1200.00)
insert OrderDetail values('200805090001','P20070001',1 , 256.00)
insert OrderDetail values('200805090001','P20070002',2 , 200.00)
insert OrderDetail values('200805090001','P20070003',5 , 100.00)

insert OrderDetail values('200806120001','P20050002',1 ,700.00)
insert OrderDetail values('200806120001','P20050003',3 , 300.00)
insert OrderDetail values('200806120001','P20050004',2 , 35.00)
insert OrderDetail values('200806120001','P20050005',3 , 100.60)

insert OrderDetail values('200901010001','P20050002',2 , 700.00)
insert OrderDetail values('200901010001','P20050004',11 , 35.00)
insert OrderDetail values('200901010001','P20050005',9 , 100.60)
insert OrderDetail values('200901010001','P20080002',2 , 1100.00)

insert OrderDetail values('201206070001','P20050003',10 , 300.00)
insert OrderDetail values('201206070001','P20060002',5 , 66.00)
insert OrderDetail values('201206070001','P20060003',4 , 1200.00)

insert OrderDetail values('201206070002','P20060002',6 , 66.00)
insert OrderDetail values('201206070002','P20070001',2 , 256.00)
insert OrderDetail values('201206070002','P20070002',2 , 200.00)
insert OrderDetail values('201206070002','P20080001',3 , 890.00)

/*为便于调整订单成交价格，下列数据建议在调价触发器生效后插入*/

--insert OrderDetail values('200802190001','P20070003',3 , 100.00)
--insert OrderDetail values('200802190001','P20070004',2 , 320.00)
--insert OrderDetail values('200802190001','P20080001',2 , 890.00)
--insert OrderDetail values('200802190001','P20080002',3 , 1100.00)
--insert OrderDetail values('200802190001','P20080003',1 , 900.00)

--insert OrderDetail values('200802190002','P20070003',4 , 100.00)
--insert OrderDetail values('200802190002','P20070004',1 , 320.00)
--insert OrderDetail values('200802190002','P20080001',3 , 890.00)

--insert OrderDetail values('200801090002','P20080002',4 , 1100.00)

--insert OrderDetail values('200806120001','P20060001',3 , 1200.60)
--insert OrderDetail values('200806120001','P20060002',2 , 66.00)

--insert OrderDetail values('200901010001','P20080003',1 , 900.00)

------其他解决方案--------------------

什么年代了还有用游标的?
[其他解释]
你的执行语句呢？也贴出来看看，是执行还是编译的时候报错？
[其他解释]
执行语句就是

create procedure biaoge  asbegin    declare @orderno char(20),@productno char(20),@quantity int,@price    numeric(7,2)

......
[其他解释]
告诉你一个秘密,你不要对别人说:
这儿,用代码贴内容的话,复制下来是一长串字符,不换行.
[其他解释]
就是create proc就报错是吧？也就是创建不成功？我用你的上面3个语句，在我本机是没问题的。但是还是想知道你的具体情况
[其他解释]
不是，就是出现这个错误
消息 245，级别 16，状态 1，第 31 行
在将 varchar 值 'P20050001 ' 转换成数据类型 int 时失败。
我也不知道为什么，反正就是

while (@@FETCH_STATUS=0) beginselect @text2=convert(char(20),@orderno) print   @text2 select @text2=convert(char(20),@productno)+'  '+@quantity+'    '+@price print   @text2 fetch  nihao into @orderno,@productno,@quantity,@price end

这一段出现了错误，我是新手，试了好几次没成功!!!

热点排行

SQL Server

数据库存储过程的有关问题，