怎么在pl/sql中测试存储过程

[lzpwr5201314]

本人是测试，最近接手一个项目，涉及到很多存储过程。
想请问下怎么在pl/sql中测试存储过程，下面是开发提供的一个存储过程
create or replace package warning_ua_package as
procedure PRO_WARNING_UA_ACHG;
procedure PRO_WARNING_UA_RBT;
procedure PRO_WARNING_UA_RBT7;
procedure PRO_WARNING_UA_RBT30;
procedure PRO_WARNING_UA_CARD1;
procedure PRO_WARNING_UA_CARD7;
procedure PRO_WARNING_UA_CARD30;
end warning_ua_package ;
/

create or replace package body warning_ua_package as

procedure PRO_WARNING_UA_ACHG is

v_pre_total_amount number := 0; --当谈总金额
v_create_time date; --当天时间
v_pre_total_amount_old number := 0; --上一个交易日总金额
v_in_total_amount number := 0; --进来的金额，即增加
v_out_total_amount number := 0; --出去的金额，即减少
v_create_time_old date; --上一个交易日时间
v_sysdate date := sysdate; --当前系统时间

v_end_user_id number;
v_end_user_name varchar2(100);
v_end_user_amount number;
v_end_user_rebate number;
v_end_user_card_amount number;
v_end_user_credit number;
v_end_user_create_time date;

BEGIN
for rs in (select e.end_user_id,
e.end_user_name,
e.end_user_amount,
e.end_user_rebate,
e.end_user_card_amount,
e.end_user_credit,
e.end_user_create_time
into v_end_user_id,
v_end_user_name,
v_end_user_amount,
v_end_user_rebate,
v_end_user_card_amount,
v_end_user_credit,
v_end_user_create_time
from end_user_account_log e, end_user e
where trunc(e.create_time) = trunc(v_sysdate)
group by e.end_user_id) loop

-----计算当天的总金额
select pre_total_amount, create_time
into v_pre_total_amount, v_create_time
from (select (e2.pre_account_amount + e2.pre_rebate_amount +
e2.pre_card_amount) as pre_total_amount,
e2.create_time
from end_user_account_log e2
where e2.end_user_id = rs.end_user_id
and trunc(v_sysdate) = trunc(e2.create_time)
order by e2.create_time desc) aa
where rownum <= 1;

---计算上一个交易日（可能是昨天、前天。。。。）的总金额
select pre_total_amount_old, create_time
into v_pre_total_amount_old, v_create_time_old
from (select (e3.pre_account_amount + e3.pre_rebate_amount +
e3.pre_card_amount) as pre_total_amount_old,
e3.create_time
from end_user_account_log e3
where e3.end_user_id = rs.end_user_id
and trunc(e3.create_time) <> trunc(v_sysdate)
order by e3.create_time desc) aa
where rownum <= 1;

---计算这个时间内的增加金额
select sum(e4.account_amount + e4.rebate_amount + e4.card_amount)
into v_in_total_amount
from end_user_account_log e4
where e4.end_user_id = rs.end_user_id
and e4.direction = 1
and e4.create_time >= v_create_time_old
and e4.create_time < v_create_time;

---计算这个时间内的减少金额
select sum(e5.account_amount + e5.rebate_amount + e5.card_amount)
into v_out_total_amount
from end_user_account_log e5
where e5.end_user_id = rs.end_user_id
and e5.direction = 0
and e5.create_time >= v_create_time_old
and e5.create_time < v_create_time;

---如果金额不等，则发邮件出来告知大家
if v_pre_total_amount_old is not null and v_in_total_amount is not null and
v_out_total_amount is not null and v_pre_total_amount is not null and
v_pre_total_amount_old + v_in_total_amount - v_out_total_amount !=
v_pre_total_amount then
--- 发信给相关人员。不能留空否则报错

INSERT INTO EARLY_WARNING_USER_ACCOUNT
(ID,
END_USER_NAME,
END_USER_AMOUNT,
END_USER_REBATE,
END_USER_CARD_AMOUNT,
END_USER_CREDIT,
END_USER_CREATE_TIME,
EARLY_WARNING_LEVEL,
EXCEPTION_TYPE)
VALUES
(SEQ_EARLY_WARNING_U_ACCOUNT_ID.NEXTVAL,
v_end_user_name,
v_end_user_amount,
v_end_user_rebate,
v_end_user_card_amount,
v_end_user_credit,
v_end_user_create_time,
1,
'userAccount_1');
end if;
end loop;
end PRO_WARNING_UA_ACHG;

procedure PRO_WARNING_UA_RBT is
v_end_user_name varchar2(100);
v_end_user_amount number;
v_end_user_rebate number;
v_end_user_card_amount number;
v_end_user_credit number;
v_end_user_create_time date;

CURSOR S_CURSOR IS
select eu.end_user_name,
eu.end_user_amount,
eu.end_user_rebate,
eu.end_user_card_amount,
eu.end_user_credit,
eu.end_user_create_time
from end_user eu
where eu.id in
(select END_USER_ID
from (SELECT sum(Rebate_Amount) as num1,
aa.END_USER_ID as END_USER_ID
from (select ref_id, END_USER_ID, EA.Rebate_Amount
FROM END_USER_ACCOUNT_LOG EA
WHERE trunc(EA.CREATE_TIME) = trunc(SYSDATE)
AND EA.DIRECTION = 0
and ref_id is not null
AND EA.Rebate_Amount > 0) aa
GROUP BY aa.END_USER_ID
HAVING sum(Rebate_Amount) >= 50));

BEGIN
OPEN S_CURSOR;
LOOP
FETCH S_CURSOR
INTO v_end_user_name, v_end_user_amount, v_end_user_rebate, v_end_user_card_amount, v_end_user_credit, v_end_user_create_time;
EXIT WHEN S_CURSOR%NOTFOUND;

INSERT INTO EARLY_WARNING_USER_ACCOUNT
(ID,
END_USER_NAME,
END_USER_AMOUNT,
END_USER_REBATE,
END_USER_CARD_AMOUNT,
END_USER_CREDIT,
END_USER_CREATE_TIME,
EARLY_WARNING_LEVEL,
EXCEPTION_TYPE)
VALUES
(SEQ_EARLY_WARNING_U_ACCOUNT_ID.NEXTVAL,
v_end_user_name,
v_end_user_amount,
v_end_user_rebate,
v_end_user_card_amount,
v_end_user_credit,
v_end_user_create_time,
1,
'userAccount_2');

END LOOP;
CLOSE S_CURSOR;
commit;
end PRO_WARNING_UA_RBT;

[lzpwr5201314]

procedure PRO_WARNING_UA_RBT7 is
v_end_user_name varchar2(100);
v_end_user_amount number;
v_end_user_rebate number;
v_end_user_card_amount number;
v_end_user_credit number;
v_end_user_create_time date;

CURSOR S_CURSOR IS
select eu.end_user_name,
eu.end_user_amount,
eu.end_user_rebate,
eu.end_user_card_amount,
eu.end_user_credit,
eu.end_user_create_time
from end_user eu
where eu.id in
(select endUserId
from (select count(e.id) cnt, e.end_user_id endUserId
from end_user_account_log e
where e.transaction_type = 68
and sysdate - e.create_time <= 7
group by e.end_user_id)
where cnt > 3
union
select endUserId
from (select sum(e.rebate_amount) sm, e.end_user_id endUserId
from end_user_account_log e
where e.transaction_type = 68
and sysdate - e.create_time <= 7
group by e.end_user_id)
where sm > 50);

BEGIN
OPEN S_CURSOR;
LOOP
FETCH S_CURSOR
INTO v_end_user_name, v_end_user_amount, v_end_user_rebate, v_end_user_card_amount, v_end_user_credit, v_end_user_create_time;
EXIT WHEN S_CURSOR%NOTFOUND;

INSERT INTO EARLY_WARNING_USER_ACCOUNT
(ID,
END_USER_NAME,
END_USER_AMOUNT,
END_USER_REBATE,
END_USER_CARD_AMOUNT,
END_USER_CREDIT,
END_USER_CREATE_TIME,
EARLY_WARNING_LEVEL,
EXCEPTION_TYPE)
VALUES
(SEQ_EARLY_WARNING_U_ACCOUNT_ID.NEXTVAL,
v_end_user_name,
v_end_user_amount,
v_end_user_rebate,
v_end_user_card_amount,
v_end_user_credit,
v_end_user_create_time,
1,
'userAccount_4');

END LOOP;
CLOSE S_CURSOR;
commit;
end PRO_WARNING_UA_RBT7;

procedure PRO_WARNING_UA_RBT30 is
v_end_user_name varchar2(100);
v_end_user_amount number;
v_end_user_rebate number;
v_end_user_card_amount number;
v_end_user_credit number;
v_end_user_create_time date;

CURSOR S_CURSOR IS
select eu.end_user_name,
eu.end_user_amount,
eu.end_user_rebate,
eu.end_user_card_amount,
eu.end_user_credit,
eu.end_user_create_time
from end_user eu
where eu.id in
(select endUserId
from (select count(e.id) cnt, e.end_user_id endUserId
from end_user_account_log e
where e.transaction_type = 68
and sysdate - e.create_time <= 7
group by e.end_user_id)
where cnt > 5
union
select endUserId
from (select sum(e.rebate_amount) sm, e.end_user_id endUserId
from end_user_account_log e
where e.transaction_type = 68
and sysdate - e.create_time <= 7
group by e.end_user_id)
where sm > 100);

BEGIN
OPEN S_CURSOR;
LOOP
FETCH S_CURSOR
INTO v_end_user_name, v_end_user_amount, v_end_user_rebate, v_end_user_card_amount, v_end_user_credit, v_end_user_create_time;
EXIT WHEN S_CURSOR%NOTFOUND;

INSERT INTO EARLY_WARNING_USER_ACCOUNT
(ID,
END_USER_NAME,
END_USER_AMOUNT,
END_USER_REBATE,
END_USER_CARD_AMOUNT,
END_USER_CREDIT,
END_USER_CREATE_TIME,
EARLY_WARNING_LEVEL,
EXCEPTION_TYPE)
VALUES
(SEQ_EARLY_WARNING_U_ACCOUNT_ID.NEXTVAL,
v_end_user_name,
v_end_user_amount,
v_end_user_rebate,
v_end_user_card_amount,
v_end_user_credit,
v_end_user_create_time,
1,
'userAccount_5');

END LOOP;
CLOSE S_CURSOR;
commit;
end PRO_WARNING_UA_RBT30;

[lzpwr5201314]

procedure PRO_WARNING_UA_CARD1 is
v_end_user_name varchar2(100);
v_end_user_amount number;
v_end_user_rebate number;
v_end_user_card_amount number;
v_end_user_credit number;
v_end_user_create_time date;

CURSOR S_CURSOR IS
select eu.end_user_name,
eu.end_user_amount,
eu.end_user_rebate,
eu.end_user_card_amount,
eu.end_user_credit,
eu.end_user_create_time
from end_user eu
where eu.id in
(select endUserId
from (select count(e.id) cnt, e.end_user_id endUserId
from end_user_account_log e
where e.transaction_type = 58
and sysdate - e.create_time <= 1
group by e.end_user_id)
where cnt > 10
union
select endUserId
from (select sum(e.card_amount) sm, e.end_user_id endUserId
from end_user_account_log e
where e.transaction_type = 58
and sysdate - e.create_time <= 1
group by e.end_user_id)
where sm > 10000);

BEGIN
OPEN S_CURSOR;
LOOP
FETCH S_CURSOR
INTO v_end_user_name, v_end_user_amount, v_end_user_rebate, v_end_user_card_amount, v_end_user_credit, v_end_user_create_time;
EXIT WHEN S_CURSOR%NOTFOUND;

INSERT INTO EARLY_WARNING_USER_ACCOUNT
(ID,
END_USER_NAME,
END_USER_AMOUNT,
END_USER_REBATE,
END_USER_CARD_AMOUNT,
END_USER_CREDIT,
END_USER_CREATE_TIME,
EARLY_WARNING_LEVEL,
EXCEPTION_TYPE)
VALUES
(SEQ_EARLY_WARNING_U_ACCOUNT_ID.NEXTVAL,
v_end_user_name,
v_end_user_amount,
v_end_user_rebate,
v_end_user_card_amount,
v_end_user_credit,
v_end_user_create_time,
1,
'userAccount_7');

END LOOP;
CLOSE S_CURSOR;
commit;
end PRO_WARNING_UA_CARD1;

procedure PRO_WARNING_UA_CARD7 is
v_end_user_name varchar2(100);
v_end_user_amount number;
v_end_user_rebate number;
v_end_user_card_amount number;
v_end_user_credit number;
v_end_user_create_time date;

CURSOR S_CURSOR IS
select eu.end_user_name,
eu.end_user_amount,
eu.end_user_rebate,
eu.end_user_card_amount,
eu.end_user_credit,
eu.end_user_create_time
from end_user eu
where eu.id in
(select endUserId
from (select count(e.id) cnt, e.end_user_id endUserId
from end_user_account_log e
where e.transaction_type = 58
and sysdate - e.create_time <= 7
group by e.end_user_id)
where cnt > 40
union
select endUserId
from (select sum(e.card_amount) sm, e.end_user_id endUserId
from end_user_account_log e
where e.transaction_type = 58
and sysdate - e.create_time <= 7
group by e.end_user_id)
where sm > 40000);

BEGIN
OPEN S_CURSOR;
LOOP
FETCH S_CURSOR
INTO v_end_user_name, v_end_user_amount, v_end_user_rebate, v_end_user_card_amount, v_end_user_credit, v_end_user_create_time;
EXIT WHEN S_CURSOR%NOTFOUND;

INSERT INTO EARLY_WARNING_USER_ACCOUNT
(ID,
END_USER_NAME,
END_USER_AMOUNT,
END_USER_REBATE,
END_USER_CARD_AMOUNT,
END_USER_CREDIT,
END_USER_CREATE_TIME,
EARLY_WARNING_LEVEL,
EXCEPTION_TYPE)
VALUES
(SEQ_EARLY_WARNING_U_ACCOUNT_ID.NEXTVAL,
v_end_user_name,
v_end_user_amount,
v_end_user_rebate,
v_end_user_card_amount,
v_end_user_credit,
v_end_user_create_time,
1,
'userAccount_8');

END LOOP;
CLOSE S_CURSOR;
commit;
end PRO_WARNING_UA_CARD7;

[lzpwr5201314]

procedure PRO_WARNING_UA_CARD30 is

v_end_user_name varchar2(100);
v_end_user_amount number;
v_end_user_rebate number;
v_end_user_card_amount number;
v_end_user_credit number;
v_end_user_create_time date;

CURSOR S_CURSOR IS
select eu.end_user_name,
eu.end_user_amount,
eu.end_user_rebate,
eu.end_user_card_amount,
eu.end_user_credit,
eu.end_user_create_time
from end_user eu
where eu.id in
(select endUserId
from (select count(e.id) cnt, e.end_user_id endUserId
from end_user_account_log e
where e.transaction_type = 58
and sysdate - e.create_time <= 30
group by e.end_user_id)
where cnt > 80
union
select endUserId
from (select sum(e.card_amount) sm, e.end_user_id endUserId
from end_user_account_log e
where e.transaction_type = 58
and sysdate - e.create_time <= 30
group by e.end_user_id)
where sm > 80000);

BEGIN
OPEN S_CURSOR;
LOOP
FETCH S_CURSOR
INTO v_end_user_name, v_end_user_amount, v_end_user_rebate, v_end_user_card_amount, v_end_user_credit, v_end_user_create_time;
EXIT WHEN S_CURSOR%NOTFOUND;

INSERT INTO EARLY_WARNING_USER_ACCOUNT
(ID,
END_USER_NAME,
END_USER_AMOUNT,
END_USER_REBATE,
END_USER_CARD_AMOUNT,
END_USER_CREDIT,
END_USER_CREATE_TIME,
EARLY_WARNING_LEVEL,
EXCEPTION_TYPE)
VALUES
(SEQ_EARLY_WARNING_U_ACCOUNT_ID.NEXTVAL,
v_end_user_name,
v_end_user_amount,
v_end_user_rebate,
v_end_user_card_amount,
v_end_user_credit,
v_end_user_create_time,
1,
'userAccount_9');

END LOOP;
CLOSE S_CURSOR;
commit;
end PRO_WARNING_UA_CARD30;

end warning_ua_package;

[opps_zhou]

plsql developer 工具，有一个专门的测试窗口

技巧就是，鼠标点到存储过程名字上，点右键，弹出来的菜单里选择“测试(test)”
然后在弹出来的窗体下方，是输入参数的

[我心飞翔]

楼主的意思是在SQL*Plus中测试存储过程吧。具体如下：
SQL>DECLARE
2 var1 类型1;
3 var2 类型2;
4 BEGIN
5 PRO_WARNING_UA_ACHG(var1, var2);
6 END;
/

var1, var2为你要给存储过程传递的参数变量
当该PL/SQL块执行时，要提示用户输入变量的具体值。

[m540920181]

[Quote=引用楼主 lzpwr5201314 的回复:]
本人是测试，最近接手一个项目，涉及到很多存储过程。
想请问下怎么在pl/sql中测试存储过程，下面是开发提供的一个存储过程
create or replace package warning_ua_package as
procedure PRO_WARNING_UA_ACHG;
procedure PRO_WARNING_UA_RBT;
procedure PRO_WARNING_UA……
[/Quote]
在
declare
begin
中调用函数，存储过程
end;