原始的存储过程如下:
CREATE OR REPLACE procedure sp_contract_tochecktable
(p_合同号 contracttable.合同号%TYPE) as
v_ContractTableRecord V_RCONTRACT%ROWTYPE;
v_CustomerRecord V_CUSTOMER.CUST_GRAD_NAME%TYPE;
begin
select a.* into v_ContractTableRecord from beijian.V_RCONTRACT a,beijian.v_customer b where b.CN_COMP_NAME(+)=a.供方 and a.合同号=p_合同号;
select b.CUST_GRAD_NAME into v_CustomerRecord from beijian.V_RCONTRACT a,beijian.v_customer b where b.CN_COMP_NAME(+)=a.供方 and a.合同号=p_合同号;
insert into beijian.CONTRACTCHECKTABLE(合同号,合同总金额,供方性质,交货期,状态) values(v_ContractTableRecord.合同号,v_ContractTableRecord.合同总金额,v_CustomerRecord,
v_ContractTableRecord.交货期,'A');commit;
end sp_contract_tochecktable;现在要修改该存储过程,只是在insert into beijian.CONTRACTCHECKTABLE(合同号,合同总金额,供方性质,交货期,状态) values(v_ContractTableRecord.合同号,v_ContractTableRecord.合同总金额,v_CustomerRecord,
中加插一个字段(原来没插):"说明"字段.
修改如下:
insert into beijian.CONTRACTCHECKTABLE(合同号,合同总金额,供方性质,交货期,状态,说明) values(v_ContractTableRecord.合同号,v_ContractTableRecord.合同总金额,v_CustomerRecord,v_ContractTableRecord.交货期,'A',...);该字段的内容是:
select distinct a.请购计划号 from beijian.plantotal a,beijian.plantocontract b where a.自动编号=b.自动编号(+) and a.自动编号 is not null and b.合同号='07MGS515E0327DE'(这里的'07MGS515E0327DE'相当于该存储中的参数p_合同号);
查询出来的结果可能有一个可能有多个,比如两个,我的要求是把查询出来的结果连接起来,中间用逗号分割,前面加上"计划号",比如查询出来结果是"1234","5678",那么省略号里面的内容就是"计划号:1234,5678". 但是我不知道如何用存储过程来表达,小弟才学,请各位老大指点,谢谢!!
CREATE OR REPLACE procedure sp_contract_tochecktable
(p_合同号 contracttable.合同号%TYPE) as
v_ContractTableRecord V_RCONTRACT%ROWTYPE;
v_CustomerRecord V_CUSTOMER.CUST_GRAD_NAME%TYPE;
begin
select a.* into v_ContractTableRecord from beijian.V_RCONTRACT a,beijian.v_customer b where b.CN_COMP_NAME(+)=a.供方 and a.合同号=p_合同号;
select b.CUST_GRAD_NAME into v_CustomerRecord from beijian.V_RCONTRACT a,beijian.v_customer b where b.CN_COMP_NAME(+)=a.供方 and a.合同号=p_合同号;
insert into beijian.CONTRACTCHECKTABLE(合同号,合同总金额,供方性质,交货期,状态) values(v_ContractTableRecord.合同号,v_ContractTableRecord.合同总金额,v_CustomerRecord,
v_ContractTableRecord.交货期,'A');commit;
end sp_contract_tochecktable;现在要修改该存储过程,只是在insert into beijian.CONTRACTCHECKTABLE(合同号,合同总金额,供方性质,交货期,状态) values(v_ContractTableRecord.合同号,v_ContractTableRecord.合同总金额,v_CustomerRecord,
中加插一个字段(原来没插):"说明"字段.
修改如下:
insert into beijian.CONTRACTCHECKTABLE(合同号,合同总金额,供方性质,交货期,状态,说明) values(v_ContractTableRecord.合同号,v_ContractTableRecord.合同总金额,v_CustomerRecord,v_ContractTableRecord.交货期,'A',...);该字段的内容是:
select distinct a.请购计划号 from beijian.plantotal a,beijian.plantocontract b where a.自动编号=b.自动编号(+) and a.自动编号 is not null and b.合同号='07MGS515E0327DE'(这里的'07MGS515E0327DE'相当于该存储中的参数p_合同号);
查询出来的结果可能有一个可能有多个,比如两个,我的要求是把查询出来的结果连接起来,中间用逗号分割,前面加上"计划号",比如查询出来结果是"1234","5678",那么省略号里面的内容就是"计划号:1234,5678". 但是我不知道如何用存储过程来表达,小弟才学,请各位老大指点,谢谢!!
10G参考如下WITH A AS (
SELECT 1123 ID,'2008-9-19' DAY,'9:7:47' TIME FROM DUAL UNION
SELECT 1123 ID,'2008-9-19' DAY,'19:7:47' TIME FROM DUAL UNION
SELECT 1123 ID,'2008-9-19' DAY,'13:7:47' TIME FROM DUAL UNION
SELECT 1123 ID,'2008-9-19' DAY,'12:7:47' TIME FROM DUAL )
select ID,DAY,WMSYS.WM_CONCAT(TIME) TIME FROM A GROUP BY ID,DAY
/ ID DAY TIME ---------- --------- ------------------------------------------------------------
1123 2008-9-19 12:7:47,13:7:47,19:7:47,9:7:47
CREATE OR REPLACE procedure sp_contract_tochecktable_back
(p_合同号 contracttable.合同号%TYPE) as
v_ContractTableRecord V_RCONTRACT%ROWTYPE;
v_CustomerRecord V_CUSTOMER.CUST_GRAD_NAME%TYPE;
v_Record1 varchar(200);
v_Record varchar(200);
--游标开始
CURSOR undealrecord IS
select distinct a.请购计划号 from beijian.plantotal a,beijian.plantocontract b where a.自动编号=b.自动编号(+) and a.自动编号 is not null and b.合同号=p_合同号;
begin
select a.* into v_ContractTableRecord from beijian.V_RCONTRACT a,beijian.v_customer b where b.CN_COMP_NAME(+)=a.供方 and a.合同号=p_合同号;
select b.CUST_GRAD_NAME into v_CustomerRecord from beijian.V_RCONTRACT a,beijian.v_customer b where b.CN_COMP_NAME(+)=a.供方 and a.合同号=p_合同号;
OPEN undealrecord;
LOOP
FETCH undealrecord INTO v_Record1;
EXIT WHEN undealrecord%NOTFOUND; v_Record:=v_Record+','+v_Record1;
END LOOP;
insert into beijian.CONTRACTCHECKTABLE(合同号,合同总金额,供方性质,交货期,状态,说明) values(v_ContractTableRecord.合同号,v_ContractTableRecord.合同总金额,v_CustomerRecord,
v_ContractTableRecord.交货期,'A',v_Record);
commit;
end sp_contract_tochecktable_back;但是好象执行不了啊,是什么原因呢,请老大指点!