oracle case ,loop,while ,for简单实例

[size=medium]
select * from employees emp where emp.salary = 3000
if语句
begin
if (10>50) then
dbms_output.put_line('da yu');
else
dbms_output.put_line('bu da yu');

end if;
end;
select * from employees emp where emp.employee_id=119
where
emp.department_id=30 and
salary < 250

DECLARE
v_sal number(10);
v_empid number(4);
BEGIN
v_empid := &nid ;
SELECT emp.salary into v_sal
FROM employees emp
WHERE emp.employee_id=v_empid;
IF v_sal <= 2500 THEN
UPDATE employees set salary=salary+200 WHERE employee_id=v_empid;
ELSIF v_sal>2500 and v_sal<3000 then
UPDATE employees set salary=salary+100 WHERE employee_id=v_empid;
ELSE
dbms_output.put_line('没有在加薪范围');
END IF;
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('没有找到改员工!');

END;


------------case 单值 等值比较----------------------

declare
str number;
begin
str:=&str;
case str
when 60 then
dbms_output.put_line('不及格');
when 70 then
dbms_output.put_line('优良');
when 80 then
dbms_output.put_line('优秀');
else
dbms_output.put_line('其他');
end case;
end;
------------case 范围 条件比较----------------------
declare
num number(6,2);
begin
num:=&num;
case
when num<60 then
dbms_output.put_line('不及格');
when num<80 then
dbms_output.put_line('优良');
when num<100 then
dbms_output.put_line('优秀');
end case;
exception
when case_not_found then
dbms_output.put_line('没有符合要求的case 语句:'||sqlerrm);
end;
--------------case 表达式---------------------------
--用在赋值语句中
declare
num number(5);
val varchar2(50);
begin
num:=&num;
val:=case num
when 1 then '第一组'
when 2 then '第二组'
when 3 then '第三组'
end || '是好样的' ;
dbms_output.put_line(val);
end;

--用在select 语句当中
select * from employees where employee_id=109
declare
str varchar2(200);
begin
select case
when salary between 2000 and 3000 then
'普通白领'
when salary between 6000 and 10000 then
'公司金领'
else
'职员'
end kkk into str from employees where employee_id=109;
dbms_output.put_line(str);
end;


select emp.first_name,emp.phone_number, case
when emp.salary between 2000 and 3000 then
'普通白领'
when emp.salary between 6000 and 10000 then
'公司金领'
else
'职员'
end as 职员类型
from employees emp
--------------------goto null---------------------------

declare
num number(5):=10;
begin
if num>5 then
goto label1;
else
-- dbms_output.put_line('nothing');
null;--不做任何事，其主要目的是为了确保程序结构的完整性。
end if;
dbms_output.put_line('welcome to you!');
<<label1>>
dbms_output.put_line('大于5');

end;
--------------------loop ------------------------------------

/*
特点:循环至少运行一次
循环语句的共同点: 都是有 loop end loop;构成
*/
create table tmp
(
tid number(6) primary key,
tname varchar2(10)
)

declare
i number(6):=1;
begin
loop
insert into tmp values(i,'值'||i);
exit when i!=0; --循环终止语句
i:=i+1;
end loop;
dbms_output.put_line('数据入库完毕');
end;

select * from tmp
--------------------while ---------------------------------

create table tmp
(
tid number(6) primary key,
tname varchar2(10)
)
delete from tmp;

declare
i number(6):=1;
begin
while i<=10
loop
insert into tmp values(i,'值'||i);
i:=i+1;
end loop;
commit;
end;
---------------------for----------------------------------

/*
循环次数是确定的
*/
declare
i number(5);
j number(5);
begin
for i in reverse 1..10
loop
for j in 1..i
loop
dbms_output.put('*');
end loop;
dbms_output.put_line('');
end loop;
end;
----------------------预定义异常-------------------------------

不需要定义，不需要手动抛出
select *
from employees emp
where emp.employee_id=1
declare
sal char(1);
e_integrity EXCEPTION;
PRAGMA EXCEPTION_INIT(e_integrity,-01422);
begin
select emp.salary into sal
from employees emp
where emp.employee_id<10000;
exception
when e_integrity then
dbms_output.put_line('值太多:'||sqlerrm);
when no_data_found then
dbms_output.put_line('没有值:'||sqlerrm);
-- when others then
-- dbms_output.put_line('赋值出错'||sqlerrm);
end;
----------------------非预定义------------------------
declare
e_integrity EXCEPTION;
PRAGMA EXCEPTION_INIT(e_integrity,-2291);
BEGIN
UPDATE employees SET employees.department_id=10
WHERE employees.employee_id=101;
EXCEPTION
WHEN e_integrity THEN
dbms_output.put_line('该部门不存在！'||sqlerrm);
when others then
dbms_output.put_line('该部门不存在--！'||sqlcode||' '||sqlerrm);
END;

select * from departments

----------------------自定义---------

--手动定义，手动抛出
select * from employees where employees.employee_id=1111
declare
ex exception;
begin
update employees set employees.salary=10000
where employees.employee_id=1111 ;
if sql%notfound then
raise ex;--注意:自定义异常一定要手动抛出
end if;
exception
when ex then

dbms_output.put_line('没有此条数据:'||sqlerrm);
--抛出自定义异常
--raise_application_error(-20001,'该雇员不存在！');
when others then
dbms_output.put_line('赋值出错');
end;


begin
raise_application_error(-20001,'该雇员不存在！');
end;

create or replace procedure Pro_test_exep
as
ex exception;
begin
update employees set employees.salary=10000
where employees.employee_id=1111 ;
if sql%notfound then
raise ex;
end if;
exception
when ex then

dbms_output.put_line('没有此条数据:'||sqlerrm);
--抛出自定义异常
raise_application_error(-20001,'该雇员不存在！');
when others then
dbms_output.put_line('赋值出错');
end;


declare
e_integrity EXCEPTION;
PRAGMA EXCEPTION_INIT(e_integrity,-20001);
begin
Pro_test_exep;
exception
when e_integrity then
dbms_output.put_line('该雇员不存在');
end;[/size]