sql语句练习

select instr('xtr','x') from dual t   --instr,函数，返回某字符出现的位置
SELECT instr('syran ma','a',1,2) FROM dual;

select To_char(sysdate,'yyyy-MM-dd hh24:mi:ss') from dual
select To_date('2010-12-29 17:53:38','yyyy-MM-dd hh24:mi:ss') from dual

select upper('wmch') from dual
select lower('WMCH') from dual

-- Create table WMC_CUST_INFO
create table WMC_CUST_INFO
(
  ACTIVE_ID    VARCHAR2(100) not null,
  BATCH_ID     VARCHAR2(32),
  FLAG_ID      VARCHAR2(2),
  CUST_SOURCE  VARCHAR2(200),
  PRODUCT_NAME VARCHAR2(100),
  CUST_NUM     NUMBER
)
tablespace TPS_USER1
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
 

create table wmc_cust_info2 as
select * from tb_sv_cust_info

select * from wmc_cust_info
select * from wmc_cust_info2

--存储过程例子1
create or replace procedure wmc_proA
(activeId in wmc_cust_info.active_id%type)
is
var_active_id wmc_cust_info.active_id%type;
var_batch_id wmc_cust_info.batch_id%type;
begin
  var_active_id := activeId;
  var_batch_id := '1';

insert into wmc_cust_info(ACTIVE_ID,BATCH_ID,FLAG_ID,CUST_SOURCE,PRODUCT_NAME,CUST_NUM)
select var_active_id,var_batch_id,'1','2010年王明超抢票活动','彩信',1300 from dual;
commit;
DBMS_OUTPUT.put_line(var_active_id||'测试');
end;

call wmc_proa('王明超抢票活动2')


--存储过程例子2
create or replace procedure wmc_proB
is
begin
 update wmc_cust_info t set t.batch_id='3' where t.active_id='S0145-短信抢票活动';
 if SQL%Found then
   dbms_output.put_line('update successfully');
   commit;
   else
     dbms_output.put_line('update failure');
     end if;
     end;
    

call wmc_prob()

--存储过程例子3
create or replace procedure wmc_proC

is
cursor curOne is            --定义游标
select *
from wmc_cust_info t
where t.active_id='王明超抢票活动';

varCurInfo wmc_cust_info%rowtype;    --定义游标变量

begin
  open curOne;
  loop
    fetch curOne into varCurInfo;

    exit when curOne%notfound;

    dbms_output.put_line(varCurInfo.active_id||'>>'||varCurInfo.BATCH_ID||'>>'||varCurInfo.CUST_SOURCE);

    end loop;

    exception
      when others then
        close curOne;
        Dbms_Output.put_line('failure');

        if curOne%isopen then
          close curOne;
          end if;
          end;
         
--存储过程例子4
create or replace procedure wmc_proD

is
cursor curOne is            --定义游标
select t.active_id,t.cust_source
from wmc_cust_info t
where t.active_id='王明超抢票活动';

varActiveInfo wmc_cust_info.active_id%type;    --定义游标变量
varCustSource wmc_cust_info.cust_source%type;

begin
  open curOne;
  loop
    fetch curOne into varActiveInfo,varCustSource;

    exit when curOne%notfound;

    dbms_output.put_line(varActiveInfo||'>>'||varCustSource);

    end loop;

    exception
      when others then
        close curOne;
        Dbms_Output.put_line('failure');

        if curOne%isopen then
          close curOne;
          end if;
          end;
         
--存储过程例子5
create or replace procedure wmc_proE

is
cursor curOne is            --定义游标
select *
from wmc_cust_info t
where t.active_id='王明超抢票活动';

begin
  open curOne;
  for curInfo in curOne loop

    exit when curOne%notfound;

    dbms_output.put_line(curInfo.active_id||'>>'||curInfo.cust_source);

    end loop;

    exception
      when others then
        close curOne;
        Dbms_Output.put_line('failure');

        if curOne%isopen then
          close curOne;
          end if;
          end;
         
         
--存储过程例子6
create or replace procedure wmc_proF

is

type curType is ref cursor;
curOne curType;
sqlStr varchar2(500);
curInfo wmc_cust_info%rowtype;

begin

--定义动态sql
sqlStr := 'select t.* from wmc_cust_info t where t.active_id=''王明超抢票活动''';

open curOne for sqlStr;
loop
  fetch curOne into curInfo;
  exit when curOne%notfound;
  dbms_output.put_line(curInfo.active_id||'*'||curInfo.cust_source);
  end loop;

  close curOne;

end;


    

call wmc_proF()