oracle_pl_sql 实例精解--3-5章

第三章 pl/sql中的sql
1、select into 进行变量初始化

2、使用序列
create sequence eseq
increment by 1;
create table test(col1 number);
begin
insert into test values (eseq.nextval);
commit;
end;
/
select * from test;
3、使用savepoint;
select eseq.currval from dual;
begin
insert into test values (eseq.nextval);
savepoint a ;
insert into test values (eseq.nextval);
savepoint b ;
insert into test values (eseq.nextval);
savepoint c ;
insert into test values (eseq.nextval);
savepoint d ;
rollback to b;
commit;
end;
/
select * from test;


第四章 if语句
if condition then
end if;
if condition then
else if condition then
else
end if;


declare
v_day varchar2(10):=trim(to_char(sysdate,'DAY'));
begin
if v_day = 'TUESDAY'  then
dbms_output.put_line('today is TUESDAY');
else
dbms_output.put_line('go to play');
end if;
end;
/
第五章 case语句
case selector
when expression then 
statment ;
when expression then
statment ;
else 
statment ;
end case;


declare
v_day varchar2(10):=trim(to_char(sysdate,'DAY'));
begin
case v_day
when 'MONDAY' then 
dbms_output.put_line('today is MONDAY');
when 'FRIDAY' then
dbms_output.put_line('today is FRIDAY');
else 
dbms_output.put_line('today is 2,3,4,6,7');
end case;
end;


declare
v_day number :=to_char(sysdate,'D');
begin
case  
when v_day >6 then 
dbms_output.put_line('today is xingqi6');
when v_day >5 then 
dbms_output.put_line('today is xingqi5');
when v_day >4 then 
dbms_output.put_line('today is xingqi4');
when v_day >3 then 
dbms_output.put_line('today is xingqi3');
else 
dbms_output.put_line('today is 1,2,7');
end case;
end;


declare
v_day number :=to_char(sysdate,'D');
v_c varchar(20);
begin
 v_c :=
  case
  when v_day >6 then 'xingqi6'
  when v_day >5 then 'xingqi5'
  when v_day >4 then 'xingqi4'
  when v_day >3 then 'xingqi3'
  else '1,2,7'
 end;
 dbms_output.put_line('today is'||v_c);
end;


declare
v_day number :=to_char(sysdate,'D');
v_c varchar(20);
begin
select
  case
  when sal >2000 then 'caogao'
  when sal >1000 then 'gao'
  when sal >500 then 'zhong'
  when sal >100 then 'di'
  else 'di'
 end
 into v_c
from scott.emp where empno=7369;
 dbms_output.put_line('gongzhi is '||v_c);
end;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22193071/viewspace-1355438/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22193071/viewspace-1355438/