PL/SQL基礎

平時基本都要寫些小的PL/SQL程式，觸發器，函數，包等，下面做了些小例子，麻雀隨小，五髒俱全。可以舉一反三。

conn hr/hr

SQL>create table emp as select * from employees;

SQL>create table dept as select * from departments

 

PL/SQL 塊
Declare
      v_ename varchar2(20);
Begin
     select first_name into v_ename from employees where employee_id=&no;
     dbms_output.put_line('The name of employee is :' || v_ename);
Exception
       WHEN NO_DATA_FOUND THEN
       dbms_output.put_line('please input the right employee number');
End;
/

Stored Procedure:
create  or replace procedure update_sal(name varchar2,newsal number)
IS
BEGIN
     update emp set salary=newsal where lower(first_name)=lower(name);
     commit;
end;

函數：
create or replace function annual_income(name varchar2)
RETURN NUMBER IS
    annual_salary number(7,2);
BEGIN
    select salary*(12+nvl(COMMISSION_PCT,0)) into annual_salary from emp where lower(first_name)=lower(name);
    return annual_salary;
END;
/  

函數的執行
var income number
call annual_income('Donald') into :income;
print income

包：
主函數
Create or replace PACKAGE emp_pkg IS
procedure update_sal(name varchar2, newsal number);
Function annual_income(name varchar2) return number;
end;
/

函數體：
create or replace PACKAGE BODY emp_pkg IS
           procedure update_sal(name varchar2,newsal number) IS
                  BEGIN
                  update emp set salary=newsal where lower(first_name)=lower(name);
                 commit;
            end;
            function annual_income(name varchar2) RETURN NUMBER IS
                 annual_salary number(7,2);
                 BEGIN
                 select salary*(12+nvl(COMMISSION_PCT,0)) into annual_salary from emp where lower(first_name)=lower(name);
                 return annual_salary;
           end;
end;
/

調用例子：
SQL>call emp_pkg.update_sal('Donald','2700');

SQL>var income number
SQL>call emp_pkg.annual_income('Donald') into :income;
SQL>print income

 

觸發器
create or replace trigger update_cascade
After update of department_id on dept
for each row
begin
update emp set department_id=:new.department_id where department_id=:old.department_id;
end;
/

 

使用標量變數
SQL> set serveroutput on
Declare
   v_ename varchar2(20);
   v_sal number(8,2);
   c_tax_rate CONSTANT number(3,2):=0.03;
   v_tax_sal number(8,2);

Begin
   select first_name,salary into v_ename,v_sal from emp where employee_id=&eno;
   v_tax_sal:=v_sal*c_tax_rate;
   dbms_output.put_line('Employee name:'||v_ename);
   dbms_output.put_line('Employee salary:'||v_sal);
   dbms_output.put_line('Employee tax:'||v_tax_sal);
END;
/
上面的寫法的缺點是萬一資料庫的表的欄位長度發生了變化，則需要去修改上面的程式碼。
所以程式的維護性很差。

所以建議用%TYPE,它會按照資料庫的列或其他的變數來確定新變數的類型和長度。下面是一個例子：

Declare
   v_ename emp.first_name%TYPE;
   v_sal EMP.SALARY%TYPE;
   c_tax_rate CONSTANT number(3,2):=0.03;
   v_tax_sal v_sal%TYPE;

Begin
   select first_name,salary into v_ename,v_sal from emp where employee_id=&eno;
   v_tax_sal:=v_sal*c_tax_rate;
   dbms_output.put_line('Employee name:'||v_ename);
   dbms_output.put_line('Employee salary:'||v_sal);
   dbms_output.put_line('Employee tax:'||v_tax_sal);
END;
/

使用複合變數：指用於存放多個變數的變數。
它包括：PL/SQL記錄，PL/SQL表，巢狀表格以及VARRAY四種複合類型。
1，PL/SQL記錄
Declare
  TYPE emp_record_type IS RECORD(
  name emp.first_name%TYPE,
  salary emp.salary%TYPE,
  title  emp.job_id%TYPE);

emp_record emp_record_type;
BEGIN
   select first_name,salary,job_id into emp_record from emp where employee_id=190;
   dbms_output.put_line('first_name:' || emp_record.name);
END;
/

2,PL/SQL表
Declare
   TYPE ename_table_type IS TABLE OF emp.first_name%TYPE
       INDEX BY BINARY_INTEGER;
   ename_table ename_table_type;
BEGIN
   Select first_name into ename_table(-1) from emp where employee_id=190;
   dbms_output.put_line('Frist_name:'|| ename_table(-1));
END;
/
ename_table(-1) 則表示下表為-1的元素。

3，巢狀表格
4，VARRY

 

參照變數：指用於存放數值的指標的變數。在編寫PL/SQL程式時，可以使用遊標變數（REF CURSOR）和物件類型變數REF OBJ_TYPE等兩種參照變數類型。
1，REF CURSOR
Declare
   TYPE C1 IS REF CURSOR;
   EMP_CURSOR C1;
   V_ENAME EMP.FIRST_NAME%TYPE;
   V_SAL EMP.SALARY%TYPE;
BEGIN
  OPEN EMP_CURSOR FOR
    SELECT first_name ,salary from emp where department_id=30;
  LOOP
    FETCH EMP_CURSOR INTO V_ENAME,V_SAL;
  EXIT WHEN EMP_CURSOR%NOTFOUND;
  DBMS_OUTPUT.PUT_LINE(V_ENAME);
  END LOOP;
  CLOSE EMP_CURSOR;
END;
/

2,REF OBJ_TYPE

非PL/SQL變數：當在PL/SQL中引用非PL/SQL變數時，必須要在非PL/SQL變數前加冒號(:)
例如：
var name varchar2(10)
begin
   select first_name into :name from emp where employee_id=190;
end;
/

SQL> print name

NAME
--------------------------------
Timothy