Oracle 的case when then语句、cursor游标的使用、动态组织sql语句

-- Created on 2007-1-18 by ADMINISTRATOR
declare
  -- Local variables here
  i integer;
  v_id vip_condition.vip_id %type;
  v_min vip_condition.vip_min%type;
  v_max vip_condition.vip_max%type;
 
  v_sql varchar2(5000);
  cursor cursor_c is select c.vip_id,c.vip_min,c.vip_max from vip_condition c order by c.vip_min ;
begin
  -- Test statements here
  v_sql:='select ';
  v_sql:=v_sql||'case ';
 
  open cursor_c;
  loop
      fetch cursor_c into v_id,v_min,v_max;
          v_sql:= v_sql||' when c.customer_prem>='||v_min||' and c.customer_prem<'||v_max||' then '||v_id;
      exit when cursor_c%NOTFOUND;
     
  end loop; 
  v_sql:=v_sql||' end ';
  v_sql:=v_sql||' ,c.* ';
  v_sql:=v_sql||' from vip_customer c ';
  insert into DEBUG_INFO(infor,Insertime)values(v_sql,sysdate);
  commit;
  --dbms_output.put_line(v_sql);
  close cursor_c;       
end;

 

 

 

 

 

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

select * from user_all_tables t ;

create table vip_condition(
 vip_id number(20) not null,
 vip_min number(20) not null,
 vip_max number(20) not null
);

insert into vip_condition(vip_id,vip_min,vip_max)values(1,100,200);

insert into vip_condition(vip_id,vip_min,vip_max)values(2,200,300);

insert into vip_condition(vip_id,vip_min,vip_max)values(3,300,500);

select c.vip_id,c.vip_min,c.vip_max from vip_condition c;

 

drop table vip_customer;

create table vip_customer(
      
       customer_id number(10) not null,
       customer_name varchar(20) not null,
       customer_prem number(20,2) not null,
       customer_level varchar2(10)  null

);

insert into vip_customer(customer_id,customer_name,customer_prem)values(1,'Jarry',150);

insert into vip_customer(customer_id,customer_name,customer_prem)values(2,'Linda',270);

insert into vip_customer(customer_id,customer_name,customer_prem)values(3,'Kevin',180);

select
       case
            when c.customer_prem>=100 and c.customer_prem<200 then 'c=A'
            when c.customer_prem>=200 and c.customer_prem<300 then 'c=B'
            when c.customer_prem>=300 and c.customer_prem<400 then 'c=C'
            else ''
       end    ,c.*

from vip_customer c

create table DEBUG_INFO (

       infor varchar2(2000) null,
       insertime date
);

select * from DEBUG_INFO;
execute ('select count(*) from (select case  when c.customer_prem>=100 and c.customer_prem<200 then 1 when c.customer_prem>=200 and c.customer_prem<300 then 2 when c.customer_prem>=300 and c.customer_prem<500 then 3 when c.customer_prem>=300 and c.customer_prem<500 then 3 end  ,c.*  from vip_customer c )');