procedure tran_get_money(
cardId IN VARCHAR2,
moneyIN number)ASbegin
select fees,min_money,max_money from info_admin where user_id=userId; 我是想从上面的语句总查出 fees min_money max_money
然后让fees和money相乘,在与min_money和max_money比较
我主要的问题是怎么从上面的语句中求出fees, min_money
money=money*fees; update Account set fees_money=money where user_id = userId;
end;
先谢谢大侠们了,本人还在学习中,请多多指教;
cardId IN VARCHAR2,
moneyIN number)ASbegin
select fees,min_money,max_money from info_admin where user_id=userId; 我是想从上面的语句总查出 fees min_money max_money
然后让fees和money相乘,在与min_money和max_money比较
我主要的问题是怎么从上面的语句中求出fees, min_money
money=money*fees; update Account set fees_money=money where user_id = userId;
end;
先谢谢大侠们了,本人还在学习中,请多多指教;
CREATE OR REPLACE PROCEDURE TRAN_GET_MONEY(CARDID IN VARCHAR2,
MONEY IN NUMBER) IS
V_FEES INFO_ADMIN.FEES%TYPE;
V_MIN_MONEY INFO_ADMIN.MIN_MONEY%TYPE;
V_MAX_MONEY INFO_ADMIN.MAX_MONEY%TYPE;
BEGIN
SELECT FEES,MIN_MONEY,MAX_MONEY
INTO V_FEES, V_MIN_MONEY, V_MAX_MONEY
FROM INFO_ADMIN WHERE USER_ID=USERID;
IF MONEY*V_FEES > V_MIN_MONEY AND MONEY*V_FEES < V_MAX_MONEY THEN
UPDATE ACCOUNT SET FEES_MONEY=MONEY*V_FEES WHERE USER_ID = USERID;
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||' : '||SQLERRM);
END ;
/--试一下:CREATE OR REPLACE procedure tran_get_money(userid IN VARCHAR2,money IN number) as
cursor c1 is select fees from info_admin where user_id=userId;
cursor c2 is select money from info_admin where user_id=userId;
l_fees number:=0;
l_money number:=0;
begin
open c1;
fetch c1 into l_fees;
open c2;
fetch c2 into l_money;
money=money*fees;
update Account set fees_money=money where user_id = userId;
close c1;
close c2;
end;
SQL> select * from info_admin; USERID MONEY FEES
---------- ---------- ----------
1 80 2
2 100 1
3 150 1SQL> select * from account; USERID FEES_MONEY
---------- ----------
1 160
2 100
3 150SQL>
SQL> CREATE OR REPLACE procedure tran_get_money(user_id IN VARCHAR2) as
2 cursor c1 is select fees from info_admin where userid=user_id;
3 cursor c2 is select money from info_admin where userid=user_id;
4 cursor c3 is select max(money) from info_admin;
5 l_fees number:=0;
6 l_money number:=0;
7 l_maxmoney number:=0;
8 l_compmax number:=0;
9 begin
10 open c1;
11 fetch c1 into l_fees;
12 open c2;
13 fetch c2 into l_money;
14 open c3;
15 fetch c3 into l_maxmoney;
16 l_money:=l_money*l_fees;
17 if l_maxmoney>=l_money then
18 l_compmax:=l_maxmoney;
19 else
20 l_compmax:=l_money;
21 end if;
22 update Account set fees_money=l_compmax where userid = user_id;
23 close c1;
24 close c2;
25 close c3;
26 end;
27 /Procedure createdSQL> exec tran_get_money(2);PL/SQL procedure successfully completedSQL> select * from account; USERID FEES_MONEY
---------- ----------
1 160
2 150
3 150SQL>