if语句,null语句=====================
select * from scott.emp;
declare
v_job scott.emp.job%type;
begin
select job into v_job from scott.emp where lower(ename)=lower('&name');
if v_job='MANAGER' then
update scott.emp set comm=nvl(comm,0)+100 where lower(ename)=lower('&name');
elsif v_job='PRESIDENT' then
update scott.emp set comm=nvl(comm,0)+10 where lower(ename)=lower('&name');
else
null;
end if;
end;
//case表达式 ======================
select * from scott.emp
declare
v_salary scott.emp.sal%type;
bonus_amount number;
begin
select sal into v_salary from scott.emp where ename='&ename';
bonus_amount :=
case
when v_salary >=1000 and v_salary <=2000 then 150
when v_salary >2000 and v_salary <=3000 then 100
when v_salary >3000 then 50
else 0
end *10;
dbms_output.put_line(bonus_amount);
end;
//for循环=============reverse关键字为倒序插入
select * from temp4
create table temp4(
tId number(3) primary key
)
declare
begin
for i in 1..10
loop
insert into temp4 values(i);
end loop;
end;
//goto语句实现循环控制
select * from temp5
create table temp5(
tId number(3)
)
declare
i int :=1;
begin
loop
insert into temp5 values(i);
if i=10 then
goto end_loop;
end if;
i:=i+1;
end loop;
<<end_loop>>
dbms_output.put_line('dsa');
end;
select * from scott.emp;
declare
v_job scott.emp.job%type;
begin
select job into v_job from scott.emp where lower(ename)=lower('&name');
if v_job='MANAGER' then
update scott.emp set comm=nvl(comm,0)+100 where lower(ename)=lower('&name');
elsif v_job='PRESIDENT' then
update scott.emp set comm=nvl(comm,0)+10 where lower(ename)=lower('&name');
else
null;
end if;
end;
//case表达式 ======================
select * from scott.emp
declare
v_salary scott.emp.sal%type;
bonus_amount number;
begin
select sal into v_salary from scott.emp where ename='&ename';
bonus_amount :=
case
when v_salary >=1000 and v_salary <=2000 then 150
when v_salary >2000 and v_salary <=3000 then 100
when v_salary >3000 then 50
else 0
end *10;
dbms_output.put_line(bonus_amount);
end;
//for循环=============reverse关键字为倒序插入
select * from temp4
create table temp4(
tId number(3) primary key
)
declare
begin
for i in 1..10
loop
insert into temp4 values(i);
end loop;
end;
//goto语句实现循环控制
select * from temp5
create table temp5(
tId number(3)
)
declare
i int :=1;
begin
loop
insert into temp5 values(i);
if i=10 then
goto end_loop;
end if;
i:=i+1;
end loop;
<<end_loop>>
dbms_output.put_line('dsa');
end;