4.1.1 if-then-else
declare
v_count number(10) :=0;
v_empno number(4) := 7888;
begin
select count(1) into v_count
from emp where empno = v_empno;
if v_count = 0
then
insert into emp
(empno,ename,job1,hiredate,sal,deptno)
values(v_empno,'张三','经理',trunc(sysdate),100,30);
else
update emp
set ename='张三',job1='经理',
hiredate =trunc(sysdate),
sal =1000,
deptno =30
where empno =v_empno;
end if;
commit;
exception
when others
then
dbms_output.put_line(sqlerrm);
end;
declare
v_sal number(7,2);
v_deptno number (2);
v_job varchar2(9);
begin
select deptno,v_job,sal
into v_deptno,v_job,v_sal from emp
where empno= :empno;--变量绑定
if v_deptno =20
then
if v_job = 'CLERK'
then
v_sal := v_sal*(1+0.12);
elsif v_job='ANALYST'
then
v_sal := v_sal*(1+0.19);
end if
else
dbms_output.put_line('仅部门编号为20的员工才能加薪!');
end if
end;
4.1.2 if-then-elsif语句
declare
v_character char(1) := &tmpVar;
begin
if v_character ='A'
then
dbms_output.put_line('当前输出字符串:'||v_character);
elsif v_character = 'B'
then
dbms_output.put_line('当前输出字符串:'||v_character);
elsif v_character = 'C'
then
dbms_output.put_line('当前输出字符串:'||v_character);
elsif v_character = 'D'
then
dbms_output.put_line('当前输出字符串:'||v_character);
else
dbms_output.put_line('不是A-D之间的字符');
end if;
end;
/
create or replace procedure sp_strcomp(p1 varchar2) --用存储过程来处理
as
v_character varchar2(2):=p1;
begin
if (v_character ='A')
then
dbms_output.put_line('当前输出字符串:'||v_character);
elsif ( v_character = 'B')
then
dbms_output.put_line('当前输出字符串:'||v_character);
elsif ( v_character = 'C')
then
dbms_output.put_line('当前输出字符串:'||v_character);
elsif ( v_character = 'D')
then
dbms_output.put_line('当前输出字符串:'||v_character);
else
dbms_output.put_line('不是A-D之间的字符');
end if;
end;
exec sp_strcomp('E');
4.1.3 case语句
declare
v_job varchar2(30);
v_empno number(4) := $empno;
begin
select job into v_job from emp where empno=v_empno;
case v_job
when 'CLERK'
then
update emp set sal = sal * (1+0.15)
where empno = v_empno;
dbms_output.put('为普通职员加薪15%');
when 'ANALYST'
then
update emp set sal = sal *(1+0.18)
where empno =v_empno;
dbms_output.put('为分析人员加薪18%');
when 'MANAGER'
then
update emp set sal =sal*(1+0.20)
where empno = v_empno;
dbms_output.put('为管理人员加薪20%');
when 'SALESMAN'
then
update emp set sal = sal *(1+0.22);
where empno = v_empno;
dbms_output.put('为销售人员加薪22%');
else --在case语句中如果不加else语句 会触发ora-6592异常
dbms_output.put('员工职级不在加薪行列。');
end case;
end;
declare
v_sal number(10,2);
v_empno number(10) :=&empno;
begin
select sal into v_sal from emp where empno = v_empno;
case --搜索型case语句示例
when v_sal between 1000 and 1500
then
dbms_output.put('员工级别:初级职员');
when v_sal between 1500 and 3000
then
dbms_output.put('员工级别:中级职员');
when v_sal between 3000 and 5000
then
dbms_output.put('员工级别:高级职员');
else
dbms_output.put('员工级别:不在级别范围之内');
end case;
end;
分支控制语句
最新推荐文章于 2023-07-03 16:44:12 发布