151031

最新推荐文章于 2020-10-30 18:36:31 发布

转载最新推荐文章于 2020-10-30 18:36:31 发布 · 51 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/zhuxiaolin/p/4928722.html

本文介绍了一个使用PL/SQL编写的示例程序，包括条件判断、循环、异常处理等基本语法，并演示了如何通过过程调用进行数据操作。

create or replace procedure pr_test1 is
v_case number(3):= 100;
begin
  if 2>1 then
  
dbms_output.put_line('成立');

elsif 4>3 then
  if 7>6 then
    
  
dbms_output.put_line('不成立');

end if;
elsif 6>5 then
  dbms_output.put_line('也行');
  
  else
    dbms_output.put_line('也不成立');
    
    end if;
  

  
  
case v_case
  
when 1 then
  dbms_output.put_line('条件匹配= 1');
  when 100 then 
    dbms_output.put_line('条件匹配 = 100');
    
    else
      
    dbms_output.put_line('条件不匹配');
    
    end case;
    
    case
      
    when 8>7 then
      
    dbms_output.put_line('成立');
    
    when 9>8 then
      
    dbms_output.put_line('也成立');
    
    else
      dbms_output.put_line('都不成立');
      
      end case;
      <<loop1>>
      loop
       
      v_case := v_case - 1;
      
      dbms_output.put_line('v_vase ='|| v_case);
      
      --if(v_case = 90) then
      
      --dbms_output.put_line('退出循环');
     
      exit loop1 when v_case = 90;
      
     -- end if;
         
      end loop;
      dbms_output.put_line('v_vase ='|| '_________');
      for inx in reverse 1 ..10 loop 
        
        
        v_case := v_case + inx;
      
      dbms_output.put_line('v_vase ='|| v_case);
      
      end loop;
    
    while v_case>80
      
    loop
      v_case := v_case - 1;
      
      dbms_output.put_line('v_vase ='|| v_case);
      
    end loop;
end pr_test1;

create or replace procedure pr_test2(v_nl in varchar2 default'12') is
begin
  

update t_hq_ryxx set ruzrq = sysdate where nianl = v_nl;

commit;

end pr_test2;

create or replace procedure pr_test3(v_nl in varchar, v_xx in out varchar2) is
begin
  
select xingm into v_xx from t_hq_ryxx where nianl = v_nl and bum = v_xx;

exception
  
when no_data_found then
  
dbms_output.put_line('查不着');

  when others then
  
dbms_output.put_line('查找出错'); 
  





end pr_test3;

create or replace procedure pr_test4(v_nl in varchar2) is

v_xm t_hq_ryxx.xingm%type;

begin
  
v_xm :='101';

pr_test3(v_nl,v_xm );

dbms_output.put_line('v_xm ='||v_xm);

end pr_test4;

create or replace procedure pr_test5 is
begin
  

update t_hq_ryxx set bum = '101' where bum is null;

commit;

if sql%rowcount > 0 then
  
dbms_output.put_line('更新'|| sql%rowcount ||'条记录');

else
  dbms_output.put_line('更新0条记录');
end if;
end pr_test5;

转载于:https://www.cnblogs.com/zhuxiaolin/p/4928722.html