CREATE OR REPLACE PROCEDURE UPDATE_IFACE
AS
BEGIN BEGIN
INSERT INTO IFACE_IODETAIL
(branch_no,yyyymm,dept_no,stk_no,prod_no,source,IO,sales_no,qty_std,used_,
create_date)
select a.branch_no,a.year||a.month,a.COMEIN_DEPT_NO,a.storage_no,c.product_id,'01','I',b.IO_TYPE_NO,sum(b.COMEIN_AMOUNT_STD),'N',to_char(sysdate,'yyyymmdd')
from st_comein_bill a,st_comein_bill_detail b,goods c
where a.comein_bill_seq = b.comein_bill_seq
and substr(a.comein_date,1,6) = to_char(add_months(to_date((select conf_value from sys_config where conf_key = 'STOCK_AMOUNT_ACC_MONTH'),'yyyymm'),1),'yyyymm')
and b.goods_no = c.goods_id
and c.goods_type_id in ('3','4')
and a.STORAGE_NO <>'AA'
group by a.branch_no,a.year||a.month,a.comein_dept_no,a.storage_no,c.product_id,'01','I',b.IO_TYPE_NO;EXCEPTION
WHEN OTHERS
THEN
raise_application_error (-20199,
'UPDATE_IFACE存储过程处理失败:'
|| SQLERRM
);
END;
BEGIN INSERT INTO IFACE_IODETAIL
(branch_no,yyyymm,dept_no,stk_no,prod_no,source,IO,sales_no,qty_std,used_,
create_date)
select a.branch_no,a.year||a.month,a.LEAD_DEPT_NO,a.storage_no,c.product_id,'01','O',b.IO_TYPE_NO,sum(b.GOOUT_AMOUNT_STD),'N',to_char(sysdate,'yyyymmdd')
from st_goout_bill a,st_goout_bill_detail b,goods c
where a.goout_bill_seq = b.goout_bill_seq
and substr(a.goout_date,1,6) = to_char(add_months(to_date((select conf_value from sys_config where conf_key = 'STOCK_AMOUNT_ACC_MONTH'),'yyyymm'),1),'yyyymm')
and b.goods_no = c.goods_id
and c.goods_type_id = '3'
and a.STORAGE_NO <>'AA'
group by a.branch_no,a.year||a.month,a.LEAD_DEPT_NO,a.storage_no,c.product_id,'01','O',b.IO_TYPE_NO; EXCEPTION
WHEN OTHERS
THEN
raise_application_error (-20200,
'UPDATE_IFACE存储过程处理失败:'
|| SQLERRM
);
END;
BEGIN
insert into IFACE_TRADE (branch_no,yyyymm,dept_no,stk_no,prod_no,source,status,qty_std,qty_old_std,
used_,create_date)
select '3300',a.acc_year||a.acc_month,null,a.STORAGE_NO,b.product_id,'01','01',sum(a.SURPLUS_AMOUNT_STD),sum(a.INIT_AMOUNT_STD),'N',to_char(sysdate,'yyyymmdd')
from st_month_amount_acc a,goods b
where a.goods_no = b.goods_id
and a.acc_year||a.acc_month = to_char(add_months(to_date((select conf_value from sys_config where conf_key = 'STOCK_AMOUNT_ACC_MONTH'),'yyyymm'),1),'yyyymm')
and b.goods_type_id in ('3','4')torage_no,b.product_id,to_char(sysdate,'yyyymmdd'); EXCEPTION
and a.STORAGE_NO <>'AA'
group by a.acc_year||a.acc_month,a.s
WHEN OTHERS
THEN
raise_application_error (-20201,
'UPDATE_IFACE存储过程处理失败:'
|| SQLERRM
);
END;
COMMIT;
END;
/
DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'UPDATE_IFACE;'
,next_date => to_date('11-03-2006 08:00:00','dd/mm/yyyy hh24:mi:ss')
,interval => 'TRUNC(SYSDATE+1)+8/24'
,no_parse => TRUE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
END;
/commit;
这是我写的一个过程和一个JOB,在SQL/PLUS里面执行的时候老是报错,但我实在查不出错在什么地方了,那些对数据库的插入语句都是没错的,大侠帮我看看有没有语法上的错误。
谢谢啦!!能即使解决的话,再加100分!
AS
BEGIN BEGIN
INSERT INTO IFACE_IODETAIL
(branch_no,yyyymm,dept_no,stk_no,prod_no,source,IO,sales_no,qty_std,used_,
create_date)
select a.branch_no,a.year||a.month,a.COMEIN_DEPT_NO,a.storage_no,c.product_id,'01','I',b.IO_TYPE_NO,sum(b.COMEIN_AMOUNT_STD),'N',to_char(sysdate,'yyyymmdd')
from st_comein_bill a,st_comein_bill_detail b,goods c
where a.comein_bill_seq = b.comein_bill_seq
and substr(a.comein_date,1,6) = to_char(add_months(to_date((select conf_value from sys_config where conf_key = 'STOCK_AMOUNT_ACC_MONTH'),'yyyymm'),1),'yyyymm')
and b.goods_no = c.goods_id
and c.goods_type_id in ('3','4')
and a.STORAGE_NO <>'AA'
group by a.branch_no,a.year||a.month,a.comein_dept_no,a.storage_no,c.product_id,'01','I',b.IO_TYPE_NO;EXCEPTION
WHEN OTHERS
THEN
raise_application_error (-20199,
'UPDATE_IFACE存储过程处理失败:'
|| SQLERRM
);
END;
BEGIN INSERT INTO IFACE_IODETAIL
(branch_no,yyyymm,dept_no,stk_no,prod_no,source,IO,sales_no,qty_std,used_,
create_date)
select a.branch_no,a.year||a.month,a.LEAD_DEPT_NO,a.storage_no,c.product_id,'01','O',b.IO_TYPE_NO,sum(b.GOOUT_AMOUNT_STD),'N',to_char(sysdate,'yyyymmdd')
from st_goout_bill a,st_goout_bill_detail b,goods c
where a.goout_bill_seq = b.goout_bill_seq
and substr(a.goout_date,1,6) = to_char(add_months(to_date((select conf_value from sys_config where conf_key = 'STOCK_AMOUNT_ACC_MONTH'),'yyyymm'),1),'yyyymm')
and b.goods_no = c.goods_id
and c.goods_type_id = '3'
and a.STORAGE_NO <>'AA'
group by a.branch_no,a.year||a.month,a.LEAD_DEPT_NO,a.storage_no,c.product_id,'01','O',b.IO_TYPE_NO; EXCEPTION
WHEN OTHERS
THEN
raise_application_error (-20200,
'UPDATE_IFACE存储过程处理失败:'
|| SQLERRM
);
END;
BEGIN
insert into IFACE_TRADE (branch_no,yyyymm,dept_no,stk_no,prod_no,source,status,qty_std,qty_old_std,
used_,create_date)
select '3300',a.acc_year||a.acc_month,null,a.STORAGE_NO,b.product_id,'01','01',sum(a.SURPLUS_AMOUNT_STD),sum(a.INIT_AMOUNT_STD),'N',to_char(sysdate,'yyyymmdd')
from st_month_amount_acc a,goods b
where a.goods_no = b.goods_id
and a.acc_year||a.acc_month = to_char(add_months(to_date((select conf_value from sys_config where conf_key = 'STOCK_AMOUNT_ACC_MONTH'),'yyyymm'),1),'yyyymm')
and b.goods_type_id in ('3','4')torage_no,b.product_id,to_char(sysdate,'yyyymmdd'); EXCEPTION
and a.STORAGE_NO <>'AA'
group by a.acc_year||a.acc_month,a.s
WHEN OTHERS
THEN
raise_application_error (-20201,
'UPDATE_IFACE存储过程处理失败:'
|| SQLERRM
);
END;
COMMIT;
END;
/
DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'UPDATE_IFACE;'
,next_date => to_date('11-03-2006 08:00:00','dd/mm/yyyy hh24:mi:ss')
,interval => 'TRUNC(SYSDATE+1)+8/24'
,no_parse => TRUE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
END;
/commit;
这是我写的一个过程和一个JOB,在SQL/PLUS里面执行的时候老是报错,但我实在查不出错在什么地方了,那些对数据库的插入语句都是没错的,大侠帮我看看有没有语法上的错误。
谢谢啦!!能即使解决的话,再加100分!
本来我有PL/SQL DEVELOPER.但天不遂人愿
PL/SQL DEVELOPER也连不上数据库了,报无法处理服务名
本来好好的
严重郁闷.大家帮忙,小弟谢了!!!
报错:Warning: Procedure created with compilation errors.
为啥我的PL/SQL DEVELOPER也连不上
狂郁闷。
大侠看看啊。谢了
sqlplus中很难调试程序
1,可能是oracle的home的问题,如果在你机器上安装了好多oracle实例,oracle home出现了问题2,listener出现了问题
and a.STORAGE_NO <>'AA'
group by a.acc_year||a.acc_month,a.s
WHEN OTHERS
THEN
raise_application_error (-20201,
'UPDATE_IFACE存储过程处理失败:'
|| SQLERRM
);
END;
上面这段有错。
在sqlplus中创建存储过程,如果报Warning: Procedure created with compilation errors.
可以用show error查看具体的错误信息。
错误出现在这一行:
substr(a.comein_date,1,6) = to_char(add_months(to_date((select conf_value from sys_config where conf_key = 'STOCK_AMOUNT_ACC_MONTH'),'yyyymm'),1),'yyyymm')
这是我插入数据的一个条件
难道不让我用子查询?里面的子查询的可以执行的!
大家再帮我看看。弄了好几天了。。谢谢
interval => 'TRUNC(SYSDATE+1)+8/24'
这是间隔多长时间的?
interval => 'TRUNC(SYSDATE+1)+8/24'是每天的8点执行