第三章 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;
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/