这是我的包规范
create package fs_atm_trans_pkg
is
procedure p_status_update;
function f_get_status(i_customer_id number, i_pin_num number, i_account_balance number, v_customer_id number, v_pin_num number, v_account_balance number)
return varchar;
end fs_atm_trans_pkg;
并成功创建了包规范。
这是我的包裹正文。
create package body fs_atm_trans_pkg is
procedure p_status_update(v_result_cust varchar2, v_balance_nr number,i_trans_id number) is
cursor cur_tran is --select all data from transactions along with stored customer data, LEFT JOIN
select * from bank_trans t left join bank_customer c
on t.cust_id = c.customer_id left join bank_acct a on c.customer_pk=a.customer_fk
for update of status;
v_result_cust varchar2(50);
begin
for each_tra in cur_tran loop
v_result_cust:= f_get_status(each_tra.CUST_ID,
each_tra.PIN_NUMBER,
each_tra.ACCT_BALANCE,
each_tra.customer_id,
each_tra.pin_num,
each_tra.account_balance);
if v_result_cust='Successful' then
update bank_trans set bank_trans.acct_balance = v_balance_nr - bank_trans.acct_balance
where i_trans_id = bank_trans.transaction_id;
update bank_trans set status = v_result_cust
where i_trans_id = bank_trans.transaction_id;
else
update bank_trans set status = v_result_cust
where i_trans_id = bank_trans.transaction_id;
end if;
end loop;
end;
FUNCTION f_get_status(i_customer_id number,
i_pin_num number,
i_account_balance number,
v_customer_id number,
v_pin_num number,
v_account_balance number)
RETURN VARCHAR2 IS
r_trans_status varchar2(100);
BEGIN
IF i_customer_id = v_customer_id then
if i_pin_num = v_pin_num then
if i_account_balance < v_account_balance
then r_trans_status := 'Successful';
else r_trans_status := 'Insufficient Fund';
end if;
else r_trans_status := 'Invalid Pin';
end if;
else r_trans_status := 'Invalid ID';
END IF;
RETURN r_trans_status;
end;
end fs_atm_trans_pkg;
当我运行我的包体时,它给出了如下错误:
不允许RECORD、TABLE或参数列表中的重复字段
子程序或游标"p_STATUS_UPDATE"在包规范中声明,并且必须在包主体中定义
子程序或游标"F_GET_STATUS"在包规范中声明,并且必须在包主体中定义
我应该如何修复这三个错误?如果每个人都能参加,我将不胜感激。
包规范具有:
procedure p_status_update;
而在体内是
procedure p_status_update(v_result_cust varchar2, v_balance_nr number,i_trans_id number)
两者需要匹配。
f_get_status
的问题是它在包规范中声明为返回varchar
,但在主体中声明为varchar2
。
光标cur_tran
似乎多次具有相同的列名。最好明确列出您想要的列,而不是使用select *
。
顺便说一句,参数命名由您决定,但通常v_
用于v变量,因此给v_customer_id
这样的参数命名可能会在以后引起混乱。