原来的系统 是用MS SQlSERVER开发的,用客要改为ORACLE,新学ORACLE不懂得调用各位多帮忙,
不知道我的存储过程是否有什么问题不:
CREATE OR REPLACE PROCEDURE ufanyi_TESTsa (
p_id IN OUT varchar2,
p_name OUT VARCHAR2
) AS
BEGIN declare
pay_bankacccode varchar2(20);
pay_bankcode varchar2(20);
pay_banknodecode varchar2(20);
rece_bankacccode varchar2(20);
capt_orgion varchar2(20);
pay_totalsum number(18,2);
sum_bill varchar2(20);
pay_or_return varchar2(20);
payment_type varchar2(20) ; cursor fmd is
select pay_bankacccode,pay_bankcode,pay_banknodecode,rece_bankacccode,capt_orgion,pay_totalsum,sum_bill,pay_or_return ,payment_type from bb_apply_bill where capt_orgion=1 and payment_type=p_id and pay_or_return=p_name;
begin
open fmd;
FETCH fmd into pay_bankacccode,pay_bankcode,pay_banknodecode,rece_bankacccode,capt_orgion,pay_totalsum,sum_bill,pay_or_return ,payment_type;
while fmd%found
loop
insert into UF_GKZZJF(pay_bankacccode,pay_bankcode,pay_banknodecode,rece_bankacccode,capt_orgion,pay_totalsum,sum_bill,pay_or_return ,payment_type)values(pay_bankacccode,pay_bankcode,pay_banknodecode,rece_bankacccode,capt_orgion,pay_totalsum,sum_bill,pay_or_return ,payment_type);
FETCH fmd into pay_bankacccode,pay_bankcode,pay_banknodecode,rece_bankacccode,capt_orgion,pay_totalsum,sum_bill,pay_or_return ,payment_type;
end loop;
close fmd;
end;
END ufanyi_TESTsa;
不知道我的存储过程是否有什么问题不:
CREATE OR REPLACE PROCEDURE ufanyi_TESTsa (
p_id IN OUT varchar2,
p_name OUT VARCHAR2
) AS
BEGIN declare
pay_bankacccode varchar2(20);
pay_bankcode varchar2(20);
pay_banknodecode varchar2(20);
rece_bankacccode varchar2(20);
capt_orgion varchar2(20);
pay_totalsum number(18,2);
sum_bill varchar2(20);
pay_or_return varchar2(20);
payment_type varchar2(20) ; cursor fmd is
select pay_bankacccode,pay_bankcode,pay_banknodecode,rece_bankacccode,capt_orgion,pay_totalsum,sum_bill,pay_or_return ,payment_type from bb_apply_bill where capt_orgion=1 and payment_type=p_id and pay_or_return=p_name;
begin
open fmd;
FETCH fmd into pay_bankacccode,pay_bankcode,pay_banknodecode,rece_bankacccode,capt_orgion,pay_totalsum,sum_bill,pay_or_return ,payment_type;
while fmd%found
loop
insert into UF_GKZZJF(pay_bankacccode,pay_bankcode,pay_banknodecode,rece_bankacccode,capt_orgion,pay_totalsum,sum_bill,pay_or_return ,payment_type)values(pay_bankacccode,pay_bankcode,pay_banknodecode,rece_bankacccode,capt_orgion,pay_totalsum,sum_bill,pay_or_return ,payment_type);
FETCH fmd into pay_bankacccode,pay_bankcode,pay_banknodecode,rece_bankacccode,capt_orgion,pay_totalsum,sum_bill,pay_or_return ,payment_type;
end loop;
close fmd;
end;
END ufanyi_TESTsa;
这是我的一个存储过程CREATE OR REPLACE Procedure UP_ORDER_SUB(LISTNO_IN In Varchar2 ,
OPERATER_IN In VARCHAR2)
Is
/*确认存储过程
|| AX 2004-08-27
*/
LS_LISTNO DD_ORDER_HEAD.LISTNO%type;
LS_STATUS DD_ORDER_HEAD.BILLSTATUS%type;
LS_BILLNO DD_ORDER_HEAD.BILLNO%type;
LN_COUNT NUMBER(2);Begin
SAVEPOINT myTrans; Select BILLNO, BILLSTATUS
Into LS_BILLNO, LS_STATUS
From DD_ORDER_HEAD
Where LISTNO = LISTNO_IN For Update; If LS_STATUS <> '6' Then
RAISE_APPLICATION_ERROR('-20001','无法下单,本单据状态已被其他用户修改!');
End If; Update DD_ORDER_HEAD
Set billstatus = '9'
Where listno = LISTNO_IN; writebilllog('YWTZD' , LISTNO_IN, 'SUBSCR' , OPERATER_IN , LS_BILLNO);EXCEPTION
When Others Then
Rollback to mytrans;
Raise;
End;当然直接把存储过程名输入之后,就可以设置参数了呀
2、修改连接或连接字符串;
3、修改ProcedureName属性;
4、双击参数属性,搞定。
5、如果有必要,修改类型、传递方向,一般不用。