[Oracle] PL/SQL學習筆記

-- 1. 使用一個變量
declare 
  -- Local variables here
  v_name varchar2(50);
begin
  -- Test statements here
  select t.user_name into v_name
  from pay_mer_order t
  where t.id=3530816;
  
  dbms_output.put_line(v_name);
end;


-- 2. 使用多個變量
declare 
  -- Local variables here
  v_name varchar2(50);
  v_trans_no varchar2(50);
  v_app_code varchar2(50);
begin
  -- Test statements here
  select t.user_name, t.pay_brh_trans_no, t.app_code 
         into v_name, v_trans_no, v_app_code
  from pay_mer_order t
  where t.id=3530816;
  
  dbms_output.put_line(v_name || ',' || v_trans_no || ',' || v_app_code);
end;

--3. 自定義記錄類型
declare 
  -- 自定義一個記錄類型
  type order_info is record(
  v_name varchar2(50),
  v_trans_no varchar2(50),
  v_app_code varchar2(50));
  --聲明自定義記錄類型的變量
  v_tmp_record order_info;
begin
  select t.user_name, t.pay_brh_trans_no, t.app_code 
         into v_tmp_record
  from pay_mer_order t
  where t.id=3530816;
  
  dbms_output.put_line(v_tmp_record.v_name || ',' || v_tmp_record.v_trans_no || ',' || v_tmp_record.v_app_code);
end;

-- 4. 使用%type定義變量，動態的獲取數據的聲明類型
declare 
  -- 定義一個記錄類型
  type order_info is record(
  v_name pay_mer_order.user_name%type,
  v_trans_no pay_mer_order.pay_brh_trans_no%type,
  v_app_code pay_mer_order.app_code%type);
  --聲明自定義記錄類型的變量
  v_tmp_record order_info;
begin
  select t.user_name, t.pay_brh_trans_no, t.app_code 
         into v_tmp_record
  from pay_mer_order t
  where t.id=3530816;
  
  dbms_output.put_line(v_tmp_record.v_name || ',' || v_tmp_record.v_trans_no || ',' || v_tmp_record.v_app_code);
end;

-- 5. 使用%rowtype定義變量，動態的獲取數據的聲明類型
declare 
  -- 聲明一個記錄類型的變量
  v_tmp_record pay_mer_order%rowtype;
begin
  select t.* 
         into v_tmp_record
  from pay_mer_order t
  where t.id=3530816;
  
  dbms_output.put_line(v_tmp_record.user_name || ',' || v_tmp_record.pay_brh_trans_no || ',' || v_tmp_record.app_code);
end;

-- 6. 賦值語句：通過變量實現查詢語句
declare 
  -- 聲明一個記錄類型的變量
  v_tmp_record pay_mer_order%rowtype;
  v_order_info_id pay_mer_order.id%type;
begin
    v_order_info_id := 3530816;
    select t.* 
         into v_tmp_record
    from pay_mer_order t
    where t.id=v_order_info_id;

    dbms_output.put_line(v_tmp_record.user_name || ',' || v_tmp_record.pay_brh_trans_no || ',' || v_tmp_record.app_code);
end;

--7. 通過變量實現DELET, INSERT, UPDATE等操作
declare 
  -- 聲明一個記錄類型的變量
  v_order_info_id pay_mer_order.id%type;
begin
    v_order_info_id := 3530816;
    delete
    from pay_mer_order t
    where t.id=v_order_info_id;
    commit;
end;

--8. 使用IF... THEN ... ELSIF ...THEN...ELSE...END IF;
--要求: 查詢出 150 號 員工的工資, 若其工資大於或等於 10000 則打印 'salary >= 10000';  若在 5000 到 10000 之間, 則打印 '5000<= salary < 10000'; 否則打印 'salary < 5000'
 --(方法一) 
declare
    v_salary employees.salary%type;
begin
    --通過 select ... into ... 語句為變量賦值
    select salary into v_salary
    from employees  
    where employee_id = 150;
    dbms_output.put_line('salary: ' || v_salary);
    -- 打印變量的值
    if v_salary >= 10000 then
        dbms_output.put_line('salary >= 10000');
    elsif v_salary >= 5000 then
        dbms_output.put_line('5000 <= salary < 10000');
    else     dbms_output.put_line('salary < 5000');
    end if; 
--(方法二)
declare
    v_emp_name employees.last_name%type;
    v_emp_sal employees.salary%type;
    v_emp_sal_level varchar2(20); 
begin
    select last_name,salary into v_emp_name,v_emp_sal 
    from employees 
    where employee_id = 150;

    if(v_emp_sal >= 10000) then 
        v_emp_sal_level := 'salary >= 10000';
    elsif(v_emp_sal >= 5000) then
        v_emp_sal_level := '5000<= salary < 10000';
    else v_emp_sal_level := 'salary < 5000';
    end if;
    
    dbms_output.put_line(v_emp_name||','||v_emp_sal||','||v_emp_sal);
end;  

--9. 使用 CASE ... WHEN ... THEN ...ELSE ... END 完成上面的任務  
declare
    v_sal employees.salary%type;
    v_msg varchar2(50); 
    begin
    select salary into v_sal        
        from employees       
        where employee_id = 150;                
    --case 不能向下面這樣用        
    /*        
    case v_sal 
        when salary >= 10000 then 
            v_msg := '>=10000'                    
        when salary >= 5000 then 
            v_msg := '5000<= salary < 10000'                   
        else v_msg := 'salary < 5000'        
    end;
    */          
    v_msg :=
        case trunc(v_sal / 5000)                   
            when 0 then 'salary < 5000'                   
            when 1 then '5000<= salary < 10000'                   
            else 'salary >= 10000'              
        end;                
    dbms_output.put_line(v_sal ||','||v_msg); 
end; 

 --10. 使用 CASE ... WHEN ... THEN ... ELSE ... END; 
 --要求: 查詢出 122 號員工的 JOB_ID, 若其值為 'IT_PROG', 則打印 'GRADE: A';  'AC_MGT', 打印 'GRADE B',  'AC_ACCOUNT', 打印 'GRADE C';  否則打印 'GRADE D'  
declare        
--聲明變量        
    v_grade char(1);        
    v_job_id employees.job_id%type; 
begin        
    select job_id into v_job_id        
        from employees        
        where employee_id = 122;                
    dbms_output.put_line('job_id: ' || v_job_id);                
    --根據 v_job_id 的取值, 利用 case 字句為 v_grade 賦值        
v_grade :=                  
    case v_job_id 
        when 'IT_PROG' then 'A'                              
        when 'AC_MGT' then 'B'                              
        when 'AC_ACCOUNT' then 'C'                              
        else 'D'                 
    end;                         
    dbms_output.put_line('GRADE: ' || v_grade); 
end;

--11. 使用循環語句打印1-100.（三種方式）
--a. LOOP...EXIT WHEN ... END LOOP
declare
    v_i number(3) :=1;
begin
    loop
        dbms_output.put_line(v_i);
        exit when v_i = 100;
        v_i := v_i + 1;
    end loop;
end;
--b. WHILE...LOOP...END LOOP
declare 
    v_i number(3) := 1;
    while v_i <= 100 loop
        dbms_output.put_line(v_i);
        v_i := v_i + i;
    end loop;
end;
--c. FOR...IN...LOOP
begin
    for i in 1..100 loop
        dbms_oupput_put_line(i);
    end loop;
end;

--12. 綜合使用if, while語句，打印1-100之間的所有素數
--（素數：有且公有兩個正約數的整數，2， 3， 5，7，11，13...）
declare
    v_i number(3) := 2;
    v_j number(3) := 2;
    v_flag number(1) := 0;
begin
    while v_i < 101 loop
        v_j := 2;
        while v_j < v_i loop
            if(mod(v_i, v_j) = 0) then 
                v_flag := 1;
            end if;
            exit when v_flag = 1;
        end loop;
        if(v_flag = 0) then
            dbms_output.put_line(v_i);
        end if;
        v_i := v_i + 1;
    end loop;
end;

--13. 使用for in實現12
declare
    v_flag number(1) := 0;
begin
    for i in 2..100 loop
        for j in 2...i loop
            if(mod(i, j) = 0) then
                v_flag := 1;
            end if;
            exit when v_flag = 1;
        end loop;
        if(v_flag = 0) then
            dbms_output.put_line(i);
        end if;
    end loop;
end;

--14. goto
declare
    v_flag number(1) := 0;
begin
    for i in 2..100 loop
        v_flag := 1;
        for j in 2..sqrt(i) loop
            if i mod j = 0 then
                v_flag := 0;
                goto label;
            end if;
        end loop;
        <<label>>
        if v_flag = 1 then 
            dbms_output.put_line(i);
        end if;
    end loop;
end;

---------------------------------------------------------------------------- 

--游標的使用
--游標1. 打印出80部門的所有的員工的工資
declare
    --a. 定義游標
    cursor salary_cursor is select salary from employees where department_i = 80;
    v_salary employees.salary%type;
begin
    --b. 打開游標
    open salary_cursor;
    
    --c. 提取游標
    fetch salary_cursor into v_salary;
    
    --d. 對游標進行循環操作：判斷游標中是否有下一條記錄
    while salary_cursor%found loop
        dbms_output.put_line('salary: ' || v_salary);
        fetch salary_cursor into v_salary;
    end loop;

    --e. 關閉游標
    close salary_cursor;
end;

--游標2. 打印出80部門的所有員工的工資：Xxx's salary is: xxx
declare
    cursor salary_cursor is select e.salary, e.last_name from employees e where department_i = 80;
    v_sal number(10);
    v_name varchar2(20);
begin
    open salary_cursor;
    fetch salray_cursor into v_sal, v_name;
    while salary_cursor%found loop
        dbms_output.put_line(v_name || '、s salary is: ' || v_sal);
        fetch salary_cursor into v_sal, v_name;
    end loop;
    close salary_cursor;
end;
--游標3：打印同manage_id為100的員工的last_name, email, salary信息（使用游標，記錄類型）
declare
    type v_emplyee is record(
        name emplyees.last_name%type,
        mail emplyees.email%type,
        sal emplyees.salary%type);
    
    v_employee_info v_employee;
    cursor salary_cursor is select e.last_name, e.email, e.salary from employees e where e.manage_id = 100;
begin
    open salary_cursor;
    fetch salary_cursor into v_emplyeee_info;
    while salary_cursor%found loop
        dbms_output.put_line(v_employee_info.name || '、s salary is: ' || v_employee_info.mail ', email is: ' || v_employee_info.mail);
        fetch salary_cursor into v_employee_info;
    end loop;
    close salary_cursor;
end;
--游標4：使用for .. in
declare
    type v_emplyee is record(
        name emplyees.last_name%type,
        mail emplyees.email%type,
        sal emplyees.salary%type);
    
    v_employee_info v_employee;
    cursor salary_cursor is select e.last_name, e.email, e.salary from employees e where e.manage_id = 100;
begin
    for v_employee_info in salary_cursor loop
        dbms_output.put_line(v_employee_info.name || '、s salary is: ' || v_employee_info.mail ', email is: ' || v_employee_info.mail);
    end loop;
end;
--游標5：利用游標，調整公司中員工的工資
--0~5000 5%, 5000~10000 3%, 10000~15000 2%, 150~ 1%
declare
    salary_info employees&rowtype
    cursor salary_adjust_cursor is select e.* from employees
    v_adjust number(4, 2);
begin
    for salary_info in salary_adjust_cursor loop
        if(salary_info.salary <= 5000) then
            v_adjust := 0.05;
        elsif(salary_info.salary <= 10000) then
            v_adjust := 0.03;
        elsif(salary_info.salary <= 15000) then
            v_adjust := 0.02;
        else
            v_adjust := 0.01;
        end if;
        update employees e set e.salary=(s.salary + e.salary * v_adjust) where t.id = salary_info.id;
    end loop;
end;

--游標6：帶參數的游標
declare
    salary_info employees&rowtype
    cursor salary_adjust_cursor(dept_id number, sal number) is
        select salary + 1000 sal, employee_id id from employees e
        where t.department_id = dept_id and salary > sal;
    v_adjust number(4, 2);
begin
    for salary_info in salary_adjust_cursor(sal => 4000, dept_id => 80) loop
        if(salary_info.salary <= 5000) then
            v_adjust := 0.05;
        elsif(salary_info.salary <= 10000) then
            v_adjust := 0.03;
        elsif(salary_info.salary <= 15000) then
            v_adjust := 0.02;
        else
            v_adjust := 0.01;
        end if;
        update employees e set e.salary=(s.salary + e.salary * v_adjust) where t.id = salary_info.id;
    end loop;
end;

--游標7：隱式游標，更新指定員工salary漲10%,如果該員工沒找到，則打印“查無此人”
begin
    update employees e set salary = salary + salary * 0.1
        where e.employees_id = 1055;
    if sql%notfound then
        dbms_output.put_line('查無此人！');
    end if;
end;
---------------------------------------------------------------------------- 

--異常處理1
declare 
    v_sal employees.salary%type;
begin
    select salary into v_sal
        from employees e where e.employee_id > 100;
    dbms_output.put_line(v_sal);
    exception when Too_many_rows then dbms_output.put_line('輸出的行數太多了');
end;

--非預定義異常2
declare
    v_sal employees.salary%type;
    --聲明一個異常
    delete_mgr_excep exception;
    --把自定義的異常和oracle的錯誤關聯起來
    PRAGMA EXCEPTION INIT(delete_mgr_excep, -2292);
begin
    delete from employees e where e.employee_id = 100;
    
    select salary into v_sal
    from employees where employee_id > 100;
    dbms_output.put_line(v_sal);
    
    exception
        when Too_many_rows then dbms_output.put_line('輸出的行數太多了');
        when delete_mgr_excep then dbms_output.put_line('Manager不能直接被刪除');
end;

--用戶自定義異常3
declare
    v_sal employees.salary%type;
    --聲明一個異常
    delete_mgr_excep exception;
    --把自定義的異常和oracle的錯誤關聯起來
    PRAGMA EXCEPTION INIT(delete_mgr_excep, -2292);
    --聲明一個異常
    too_high_sal exception;
begin    
    select salary into v_sal
    from employees where employee_id > 100;
    dbms_output.put_line(v_sal);
    
    if(v_sql > 1000) then 
        raise too_high_sal;
    end if;
    
    exception
        when Too_many_rows then dbms_output.put_line('輸出的行數太多了');
        when delete_mgr_excep then dbms_output.put_line('Manager不能直接被刪除');
        --處理異常
        when too_high_sal then dbms_output.put_line('工資過高了');
end;

--異常的基本程序4
declare
    v_sal employees.salary%type;
begin
    select salary into v_sal from employees where employee_id=1000;
    dbms_output.put_line('salary: ' || v_sal);
    exception when No_data_found then dbms_output.put_line('未找到數據');
end;
---------------------------------------------------------------------------- 
--存儲過程和函數
--存儲函數：有返回值，創建完成後，通過select function() from dual;執行
--存儲過程：由於沒有返回值，創建完成後，不能使用select語句，只能使用pl/sql塊執行
--函數的聲明（有參數的寫在小括號裡）
create or replace function func_name(v_param varchar2)
--返回值類型
return varchar2 is
--PL/SQL塊變量、記錄類型、游標的聲明（類似於前面的declare的部分）
begin
--函數體（可以實現crud操作，返回值需要return)
return 'hello world' || v_param;
end;

--存儲函數1：helloworld
create or replace function hello_func
return varchar2 is
begin
    return 'hello world';
end;
--執行函數
begin
    dbms_output.put_line(hello_func());
end;
--或者
select hello_func() from dual;

--存儲函數2：參數輸入
create or replace function hello_func(v_logo varchar2)
return varchar2
is
begin
    return 'hello world ' || v_logo;
end;
--存儲函數3：使用OUT型的參數，因為函數只能有一個返回值，PL/SQL程序可以通過OUT型的參數實現多個返回值
--要求: 定義一個函數: 獲取給定部門的工資總和 和 該部門的員工總數(定義為 OUT 類型的參數).
--要求: 部門號定義為參數, 工資總額定義為返回值.
create or replace function sum_sql(dept_id number, total_count out number)
    return number
is
    cursor sal_cursor is select salary from employees 
        where department_id = dept_id;
    v_sum_sal number(8) := 0;
begin
    total_count := 0;
    for c in sal_cursor loop
        v_sum_sal := v_sum_sal + c.salary;
        total_count := total_count + 1;
    end loop;
return v_sum_sal;
end;
--執行函數
declare v_total number(3) := 0;
begin
    dbms_output.put_line(sum_sal(80, v_total));
    dbms_output.put_line(v_total);
end; 
---------------------------------------------------------------------------- 

--觸發器1：helloworld觸發器
create or replace trigger hello_trigger
    after update on employees
begin
    dbms_output.put_line('hello world..');
end;
--在執行以下更新語句之後會打出hello world
update employees set salary = salary + 1000;
--觸發囂2：行觸發器
create or replace trigger employees_trigger
    after update on employees for each row
begin
    dbms_output.put_line('修改了一條記錄');
end;
--觸發囂2：語句級觸發器：一個update/delete/insert語句只使觸發器執行一次
create or replace trigger employees_trigger
    after update on employees
begin
    dbms_output.put_line('修改了一條記錄');
end;
--觸發囂3：使用:new, :old修飾符
create or replace trigger employees_trigger
    after update on employees for each row
begin
    dbms_output.put_line('old salary: ' || :old.salary || ', new salary: ' || :new.salary);
end;