oracle case ,loop,while ,for簡單一實例

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;