Oracle 测试语句

一、简单测试

declare l_card_id number;
        v_result  varchar2(1000);
begin 
   select t.card_id into l_card_id from (
          select t.rowid from sup_card t 
          where t.card_id = 41
          order by t.card_No desc,t.up_load_date asc
          ) R 
    inner Join sup_card t on R.Rowid = t.rowid 
          where rownum < 2;
    
    if (l_card_id is null) then
       v_result := 'It is null';
    elsif (l_card_id = 0) then 
       v_result := 'Zero';
    else
       v_result := l_card_id;
    end if;
    
    dbms_output.put_line(v_result); 
end;

View Code

split_type的理解、使用：

declare
  l_auto_i   integer;
  l_sup_list split_type;
begin

  l_sup_list := em_f_split(&liststr);

  l_auto_i := l_sup_list.first;

  while l_auto_i is not null loop
  
    dbms_output.put_line(l_auto_i || ':' || l_sup_list(l_auto_i));
  
    -- 进1
    l_auto_i := l_sup_list.next(l_auto_i);
  end loop;

  dbms_output.put_line('END');
end;

View Code

问题：1. 如果查询card_id的数据没有，那么t.card_id into l_card_id时，将抛出异常

2. elseif 的写法是 elsif，少了个e.

二、参数是游标的测试

 1 declare
 2   xx bp_platform_order%rowtype;
 3   cursor cur is
 4     select * from bp_platform_order o where o.product_no = '4';
 5 begin
 6   open cur;
 7   loop
 8     fetch cur
 9       into xx;
10     exit when cur%notfound;
11     dbms_output.put_line('**=' || xx.platform_order_id);
12   end loop;
13   close cur;
14 end;

游标行

 1 declare
 2   xx varchar2(1000);
 3   yy number;
 4   cursor cur is
 5     select o.platform_order_id,o.order_status from bp_platform_order o where o.product_no = '4';
 6 begin
 7   open cur;
 8   loop
 9     fetch cur
10       into xx,yy;
11     exit when cur%notfound;
12     dbms_output.put_line('**=' || xx);
13   end loop;
14   close cur;
15 end;

游标列

三、可以输入值得测试

 1 declare
 2   -- Non-scalar parameters require additional processing 
 3   result   split_type;
 4   l_auto_i integer;
 5 begin
 6   -- 必须用&标记输入变量
 7   -- 在弹出的框中输入测试值时，字符串必须用‘’包起来
 8   result   := bp_f_split(&p_str, &p_delimiter);
 9   l_auto_i := result.first;
10   while l_auto_i is not null loop
11   
12     dbms_output.put_line(to_char(l_auto_i) || '=>' ||
13                          to_char(result(l_auto_i)));
14     if (result(l_auto_i) is null) then
15       dbms_output.put_line(to_char(l_auto_i) || '****null');
16     end if;
17     l_auto_i := result.next(l_auto_i);
18   end loop;
19 end;

可以输入值

四、 while循环

 1 declare
 2   l_card_id number;
 3   v_result  varchar2(1000);
 4   l_date    date := trunc(sysdate, 'mm');
 5   l_result1 number;
 6   l_result2 number;
 7   l_result3 number;
 8 begin
 9   while l_date > to_date('20140701', 'yyyymmdd') loop
10   
11     begin
12     
13       l_date := l_date - 1;
14 
15       dbms_output.put_line(to_char(l_date, 'yyyy-mm-dd: ') ||
16                            to_char(l_result1) || '' || to_char(l_result2) ||
17                            to_char(l_result3));
18     
19     end;
20   
21   end loop;
22 
23   dbms_output.put_line('over!!');
24 end;

While按日期循环