精通Oracle10编程SQL(1-3)PLSQL基础

--只包含执行部分的PL/SQL块
--set serveroutput off
begin
  dbms_output.put_line('Hello,everyone!');
end;


select * from emp;

--包含定义部分和执行部分的PL/SQL块
declare
   v_ename varchar2(5);
begin
   select ename into v_ename from emp
   where empno=&no;
   dbms_output.put_line('雇员名：'||v_ename);
end;


--包含定义部分、执行部分和例外处理部分的PL/SQL块
declare
   v_ename varchar2(5);
begin
   select ename into v_ename from emp
   where empno=&no;
   dbms_output.put_line('雇员名：'||v_ename);
exception
   when NO_DATA_FOUND THEN
       dbms_output.put_line('请输入正确的雇员号!');
end;

select * from emp;

--PL/SQL块分类-匿名块
declare
   v_avgsal number(6,2);
begin
   select avg(sal) into v_avgsal from emp
   where deptno=&no;
   dbms_output.put_line('平均工资：'||v_avgsal);
end;



select * from emp;
select * from dept for update;

--PL/SQL块分类-命名块(具有特定名称标识的PL/SQL块)
--<<outer>>  --在PL/SQL Developer中不可执行
declare
   v_deptno number(2);
   v_dname varchar2(10);
begin
   --<<inner>>  --在PL/SQL Developer中不可执行
   begin
       select deptno into v_deptno from emp where lower(ename) = lower('&name');
   end; --<<inner>>
   select dname into v_dname from dept where deptno = v_deptno;
   dbms_output.put_line('部门名:'||v_dname);
end; --<<outer>>


--子程序-过程
create procedure update_sal(name varchar2,newsal number)
is
begin
  update emp set sal = newsal where lower(ename)=lower(name);
end;

select * from emp;

--exec update_sal('Test',40.5)

select * from emp;

--子程序-函数
create function annual_income(name varchar2)
return number is
   annual_salary number(7,2);
begin
   select sal*12 + nvl(comm,0) into annual_salary
   from emp where lower(ename)=lower(name);
   return annual_salary;
end;

--调用函数
declare
    income number(6,2);
begin
    income:=annual_income('Test2');
    dbms_output.put_line(income);
end;


--子程序-包
create package emp_pkg IS
  PROCEDURE update_sal(name varchar2,newsal number);
  FUNCTION annual_income(name varchar2) return number;
end;


create package body emp_pkg IS
  PROCEDURE update_sal(name varchar2,newsal number)
  is
  begin
      update emp set sal=newsal where lower(ename)=lower(name);
  end;
  function annual_income(name varchar2) return number
  is
     annual_salary number(7,2);
  begin
     select sal*12+nvl(comm,0) into annual_salary from emp where lower(ename)=lower(name);
     return annual_salary;
  end;
end;


select * from emp;

call emp_pkg.update_sal('Test2',1500);


--调用包中的函数
declare
    income number(6,2);
begin
    income:=emp_pkg.annual_income('Test2');
    dbms_output.put_line(income);
end;


--触发器
--触发器update_cascade用于实现级联更新，如果不建立该触发器，那么当更新dept表的deptno列数据时就会显示错误"ORA-02292:违反完整约束条件
--(SCOTT.FK_DEPTNO)-已找到子记录日志";而在建立了该触发器之后，当更新deptno列时，就会级联更新emp表的deptno列的相关数据
create OR REPLACE trigger update_cascade
   after update of deptno ON dept
   for each row
begin
   update emp set deptno=:new.deptno where deptno=:old.deptno;
end;


--使用标量变量
DECLARE
   v_ename VARCHAR2(5);
   v_sal NUMBER(6,2);
   c_tax_rate CONSTANT NUMBER(3,2):=0.03;
   v_tax_sal NUMBER(6,2);
BEGIN
   select ename,sal into v_ename,v_sal from emp where empno=&no;
   v_tax_sal:=v_sal*c_tax_rate;
   dbms_output.put_line('雇员名：'||v_ename);
   dbms_output.put_line('雇员工资：'||v_sal);
   dbms_output.put_line('所得税：'||v_tax_sal);
end;


select * from emp;

--使用%TYPE属性
--变量v_ename,v_sal与EMP表的ename列、sal列的数据类型和长度完全一致，而变量v_tax_sal与变量v_sal的数据类型和长度完全一致。
--这样，当ename列和sal列的类型和长度发生改变时，该PL/SQL块将不需要进行任何修改。
DECLARE
   v_ename emp.ename%TYPE;
   v_sal emp.sal%TYPE;
   c_tax_rate CONSTANT NUMBER(3,2):=0.03;
   v_tax_sal v_sal%TYPE;
BEGIN
   select ename,sal into v_ename,v_sal from emp where empno=&eno;
   v_tax_sal:=v_sal*c_tax_rate;
   dbms_output.put_line('雇员名：'||v_ename);
   dbms_output.put_line('雇员工资：'||v_sal);
   dbms_output.put_line('所得税：'||v_tax_sal);
end;


select * from emp for update;

--复合变量-PL/SQL记录
--emp_record_type是PL/SQL记录类型，并且该PL/SQL记录类型包含了三个成员(name,salary,title);emp_record是记录变量;
--emp_record.name则表示引用记录变量emp_record的成员name.
declare
   type emp_record_type is record(
        name emp.ename%TYPE,
        salary emp.sal%TYPE,
        title emp.job%TYPE);
   emp_record emp_record_type;
begin
   select ename,sal,job into emp_record from emp where empno=7788;
   dbms_output.put_line('雇员名：'||emp_record.name);
end;

--复合变量-PL/SQL表
declare
   type ename_table_type is table of emp.ename%TYPE
        INDEX BY BINARY_INTEGER;
   ename_table ename_table_type;
begin
   select ename into ename_table(-1) from emp where empno=7788;
   dbms_output.put_line('雇员名：'||ename_table(-1));
end;


--复合变量-嵌套表
create or replace type emp_type as object(
   name varchar2(10),salary number(6,2),
   hiredate date);
   
create or replace type emp_array is table of emp_type;

--使用嵌套表类型作为表列时，必须要为其指定专门的存储表，如下所示
create table department(
   deptno number(2),dname varchar2(10),
   employee emp_array
)nested table employee store as employee;


--复合变量-VARRAY
--VARRAY(变长数组)类似于嵌套表，它可以作为表列和对象类型属性的数据类型。
--但需要注意，嵌套表的元素个数没有限制，而VARRAY的元素个数是有限制的。
--注意：嵌套表列数据需要存储在专门的存储表中，而VARRAY数据则与其他数据一起存放在表段中。
create type article_type as object(
   title varchar2(30),pubdate date
);

create type article_array is varray(20) of article_type;

create table author(
   id number(6),name varchar2(10),article article_array
);


select * from emp;

--参照变量-REF CURSOR
DECLARE
  TYPE cl IS REF CURSOR;
  emp_cursor cl;
  v_ename emp.ename%TYPE;
  v_sal emp.sal%TYPE;
BEGIN
  OPEN emp_cursor FOR
     select ename,sal from emp where deptno = 3;
  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;


--参照变量-REF obj_type
create or replace type home_type as object(
   street varchar2(50),city varchar2(20),
   state varchar2(20),zipcode varchar2(6),
   owner varchar2(10)
);

create table homes of home_type;

insert into homes values('呼伦北路12号','呼和浩特','内蒙','010010','马鸣');
insert into homes values('呼伦北路13号','呼和浩特','内蒙','010010','秦斌');
commit;

select * from homes

--对象表homes存放着家庭所在地以及户主姓名。
--为了使得同一家庭的每个家庭成员可以共享家庭地址，可以使用REF引用home_type对象类型，从而降低占用空间。
create table person(
   id number(6) primary key,
   name varchar2(10),addr ref home_type
);

select * from person;

insert into person select 1,'马嗣',ref(p) from homes p where p.owner='马鸣';
insert into person select 2,'马真',ref(p) from homes p where p.owner='马鸣';
insert into person select 3,'王敏',ref(p) from homes p where p.owner='马鸣';

commit;