create or replace procedure hha(p_balance in number(18,2), p_scale in number(18,2),
p_accountid in String,p_chfg in String,p_currency in String) is
begin
update TRD_ACCINFOCTROL set balance=p_balance,initProportion=p_scale where
accountId=p_accountid and chFlag=p_chfg and MargCurrency=p_currency;
end transition;我要写个存储过程,大家看上面代码就大概知道我要干什么了。我没写过,所以有错也不知道是哪里错了,请大家指示。
另外请大家告诉我,如果在一个存储过程里我要执行两个SQl语句,如何定义事物。
p_accountid in String,p_chfg in String,p_currency in String) is
begin
update TRD_ACCINFOCTROL set balance=p_balance,initProportion=p_scale where
accountId=p_accountid and chFlag=p_chfg and MargCurrency=p_currency;
end transition;我要写个存储过程,大家看上面代码就大概知道我要干什么了。我没写过,所以有错也不知道是哪里错了,请大家指示。
另外请大家告诉我,如果在一个存储过程里我要执行两个SQl语句,如何定义事物。
解决方案 »
- 求高手帮忙优化语句
- 长字节字段输入问题
- SSIS中,连结管理器无法连接到服务器的ORACLE数据库
- 我的job如何启动?????????
- .net访问oracle数据库问题
- 这段代码哪儿错了?
- 登陆Oracle数据库报错: ORA-01033 initialization or shutdown in progress
- 数据库设计内部培训的内容讨论(好的意见有分)
- 请各位师兄师姐师弟师妹们帮忙解决一个查询语句
- 哪位大侠能提供Oracle 10.2.0.3或10.2.0.4的下载地址,帮小弟解燃眉之急,跪谢!
- 紧急求助:请有METALINK帐号的朋友帮查一下 430146.1 万分感激
- sql语句执行计划优化,在线等~~~~~~
(p_balance in NUMBER,
p_scale in NUMBER,
p_accountid in String,
p_chfg in String,
p_currency in String)
is
begin
update TRD_ACCINFOCTROL set balance=p_balance,initProportion=p_scale where
accountId=p_accountid and chFlag=p_chfg and MargCurrency=p_currency;
end hha;IN/OUT参数只需要指定其类型,不能指定其长度/精度。
end后面要跟你的过程名,你什么过程名是hha,这里必须是hha,要么就不要跟,直接end;
CREATE OR REPLACE PROCEDURE HHA(P_BALANCE IN NUMBER,
P_SCALE IN NUMBER,
P_ACCOUNTID IN VARCHAR2,
P_CHFG IN VARCHAR2,
P_CURRENCY IN VARCHAR2) IS
BEGIN UPDATE TRD_ACCINFOCTROL
SET BALANCE = P_BALANCE, INITPROPORTION = P_SCALE
WHERE ACCOUNTID = P_ACCOUNTID
AND CHFLAG = P_CHFG
AND MARGCURRENCY = P_CURRENCY;
--sql2
UPDATE TRD_xxxxxxxxxxxx
SET BALANCE = P_BALANCE, INITPROPORTION = P_SCALE
WHERE ACCOUNTID = P_ACCOUNTID
AND CHFLAG = P_CHFG
AND MARGCURRENCY = P_CURRENCY;
END;
/
看看关于存储过程中exception捕捉异常的例子。这个过程没有捕捉,则如果执行失败,会有错误信息返回调用该过程的环境。
如果到网上去找资料,这样只能是一知半解。呵呵
大家一起学习。。
P_SCALE IN NUMBER,
P_ACCOUNTID IN VARCHAR2,
P_CHFG IN VARCHAR2,
P_CURRENCY IN VARCHAR2) IS
BEGIN UPDATE TRD_ACCINFOCTROL
SET BALANCE = P_BALANCE, INITPROPORTION = P_SCALE
WHERE ACCOUNTID = P_ACCOUNTID
AND CHFLAG = P_CHFG
AND MARGCURRENCY = P_CURRENCY;
--sql2
UPDATE TRD_xxxxxxxxxxxx
SET BALANCE = P_BALANCE, INITPROPORTION = P_SCALE
WHERE ACCOUNTID = P_ACCOUNTID
AND CHFLAG = P_CHFG
AND MARGCURRENCY = P_CURRENCY;
exception when others then
raise;
END;
/如果要返回成功失败标识,则加个out参数
CREATE OR REPLACE PROCEDURE HHA(P_BALANCE IN NUMBER,
P_SCALE IN NUMBER,
P_ACCOUNTID IN VARCHAR2,
P_CHFG IN VARCHAR2,
P_CURRENCY IN VARCHAR2,
o_ret OUT NUMBER) IS
BEGIN UPDATE TRD_ACCINFOCTROL
SET BALANCE = P_BALANCE, INITPROPORTION = P_SCALE
WHERE ACCOUNTID = P_ACCOUNTID
AND CHFLAG = P_CHFG
AND MARGCURRENCY = P_CURRENCY;
--sql2
UPDATE TRD_xxxxxxxxxxxx
SET BALANCE = P_BALANCE, INITPROPORTION = P_SCALE
WHERE ACCOUNTID = P_ACCOUNTID
AND CHFLAG = P_CHFG
AND MARGCURRENCY = P_CURRENCY;
o_ret :=1; --都成功返回1
exception when others then
o_ret :=-1; --失败返回-1。
END;
/另外,oracle的存储过程是原子性的,你不用启事务。它要么全成功要么全失败。不像mysql,最近被mysql的存储过程整死了。。