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

Question

本人是测试，最近接手一个项目，涉及到很多存储过程。
想请问下怎么在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 asprocedure 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;

Answer 1

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;

Answer 2

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;

Answer 3

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;

Answer 4

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

Answer 5

楼主的意思是在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块执行时，要提示用户输入变量的具体值。
  
 

Answer 6

在
declare
begin
 中调用函数，存储过程
end;