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