10.程序包

一.示例1

--存储过程
create or replace procedure addemp( var_sal  in out number  )
is|as

begin

end；

--函数
create or replace function addemp(var_sal number) return number
is|as
  v_num number;
begin
      -------
    return v_num;
 
end；

--1.程序包包头=接口
所有成员全部是public 
所有的变量都是成员变量而且都是静态的==可以通过类名直接调用
所有的方法抽象方法==没有实现细节
create or replace package 包名
is|as
    声明变量
    声明(方法）函数、存储过程
end 包名；

--2.程序包包体==实现类
实现接口里所有的方法，
程序包中所有的方法和变量都是私有的
create or replace package body 包体名称(该名称必须和包名一样)
is|as
   begin
       //实现第一个函数或存储过程的细节
   end；
  begin
       //实现第二个函数或存储过程的细节
   end；
  .....

  begin
       初始化接口里的里成员变量；

end 包体名称;


--3.在plsql块调用程序包实现功能呢
declare

begin
   //调用包体里面的内容
   包体名称.函数或存储过程
end;

--1.创建程序包
create or replace package emp_package is
  --定义成员变量
  minsal emp.sal%type;
  maxsal emp.sal%type;

  --定义函数和存储过程
 --添加雇员信息
 procedure add_emp(p_eno number,p_name varchar2,p_sal number,p_dno number);

--通过员工编号获取工资
 function get_sal(p_eno number) return number;

end emp_package;
/

--2.创建程序包体
create or replace package body emp_package is
--(1)只要工资在最大值和最小值之间就添加员工信息
procedure add_emp(p_eno number,p_name varchar2,p_sal number,p_dno number) 
is
  v_info varchar2(40);
begin
   if p_sal between minsal and maxsal then
      insert into emp(empno,ename,sal,deptno) values(p_eno,p_name,p_sal,p_dno)
      returning empno||','||ename||','||sal||','||deptno into v_info;
      dbms_output.put_line(v_info);
   else
     dbms_output.put_line('工资不在指定范围内');
   end if;  
exception
    when dup_val_on_index then
      dbms_output.put_line('该员工已经存在');
end;

--(2)通过员工编号查询工资
function get_sal(p_eno number) return number
is
    v_sal emp.sal%type;  --用来接收查询到的值并返回
begin
   select sal into v_sal from emp where empno=p_eno;
   return v_sal;
exception
   when no_data_found then
     dbms_output.put_line('没有找到该员工信息');
end;

--(3)初始化部分
begin
     select min(sal),max(sal) into minsal,maxsal from emp;
end emp_package;
/

--3.调用程序包内容
--添加员工信息
set serverout on
begin
   --调用程序包中存储过程
   emp_package.add_emp(5001,'holly',3000,20);
   dbms_output.put_line('添加成功');
   commit;
end;
/
  
--根据员工编号查询员工工资
set timing on
set serverout on
declare
   v_sal number;
begin
   v_sal:=emp_package.get_sal(5000);
  dbms_output.put_line('5000雇员工资为：'||v_sal);
end;
/

--4.程序包中的游标使用
--创建程序包包头
create or replace  package emp_cursorPackage is
   --定义游标类型
   type type_emp_cursor is ref cursor;
end emp_cursorPackage;
/

--定义存储过程
create or replace procedure proc_emp(cur out emp_cursorPackage.type_emp_cursor)
as
begin
    open cur for
        select empno,sal from emp;
end proc_emp;
/

--调用存储过程
declare
   v_empno emp.empno%type;
   v_sal emp.sal%type;
   emp_sal emp_cursorPackage.type_emp_cursor; --定义游标变量
begin
   --调用存储过程
   proc_emp(emp_sal);

  loop
     fetch emp_sal into v_empno,v_sal;
    exit when emp_sal%notfound;
    dbms_output.put_line(v_empno||'的员工工资为：'||v_sal);
  end loop;
  close emp_sal;
end;
/
  

--触发器
分类：
(1)行级触发器：DML，如果操作批量数据，会出现多个影响语句
(2)语句级触发器:DML ，如果操作批量数据，会出现一个影响语句
(3)instead of 触发器
(4)模式触发器:DDL
(5)数据库级触发器 :数据库登陆、注销等操作
--行级触发器语法
create  or replace trigger  触发器名字
before|after  （动作update|insert|delete）
on 表名
for each row --行级触发器
begin
    --备份操作（插入删除的数据到备份表）
end;

--快速复制表结构并创建
--where条件为true或不写where条件复制表数据和表结构，
--where条件为false只复制表结构不复制表数据
create  table del_emp as select * from emp where 1=2；

--1.建立一个触发器，当删除某一条记录时，将删除数据插入到某张备份表
create or replace trigger tr_del_emp
   before delete  --触发的时机是删除前触发
   on emp    --指定操作的表
   for each row              --行级触发器
   when (old.deptno<>10)  --触发的条件
begin
   --删除前将数据插入到备份表
   insert into del_emp(deptno,empno,ename,job,mgr,sal,comm,hiredate)
      values( :old.deptno, :old.empno, :old.ename, :old.job, :old.mgr, :old.sal, :old.comm,
       :old.hiredate);
end;
 /

--删除5001数据
delete emp where empno=7902;

--查询备份表
select * from del_emp;

--回滚
rollback；

--删除触发器
drop trigger tr_del_emp; 

--2.语句触发器( 默认触发器)
create or replace trigger upda_emp
  after insert or update or delete
  on emp
begin
   if updating then
      dbms_output.put_line('数据已经更新');
  elsif deleting then
     dbms_output.put_line('数据已删除');
  elsif inserting then
    dbms_output.put_line('数据插入');
  end if;
end;   
/
--删除
delete from emp;

--回滚
rollback;

--删除触发器
drop trigger upda_emp;

conn system
>accp

--创建用户
create user user1 identified by user1；

--授权
grant connect,resource to user1;

--切换用户user1
conn user1

--创建记录表
create table event_ddl(event varchar2(20), username varchar2(10),owner varchar2(10),
   objname varchar2(20),objtype varchar2(10),time date);

--3.模式触发器DDL
create or replace trigger tr_ddl
  after ddl on user1.schema
begin
  insert into event_ddl values(ora_sysevent,
     ora_login_user,
     ora_dict_obj_owner,
     ora_dict_obj_name,
    ora_dict_obj_type,
   sysdate);
end;
/

--调用触发器
create table temp(a number);

--删除表
drop table temp;

--删除触发器
drop trigger tr_ddl;

--4.数据库级触发器
conn system

--创建数据库操作记录表
create table log_table(username varchar2(20), 
    login_time date,logoff_time date,address varchar2(20));

--创建触发器
create or replace trigger tr_login
   after logon on database
begin
   insert into log_table(username,login_time,address)
     values(ora_login_user,sysdate,ora_client_ip_address);
end;
/
--退出登录
exit；

--切换用户
conn scott

--切换用户
conn user1

--切换用户
conn system
--查表

二、示例2：

--一、程序包中使用子程序：存储过程和函数
--1.创建程序包头
create or replace package emp_package is
   minsal emp.sal%type;
   maxsal emp.sal%type;
   
   --(1).添加雇员信息,添加的员工工资不能低于最低，不能高于最高
   procedure add_emp(p_eno number,p_name varchar2,p_sal number,p_dno number) ;
   
   --(2)通过雇员编号修改雇员工资
   procedure upd_sal(p_eno number,p_sal number);
   
   --(3).通过雇员姓名修改雇员工资
   procedure upd_sal(p_name varchar2,p_sal number);
   
   --(4).通过雇员编号查询雇员工资
   function get_sal(p_eno number) return number;
end emp_package;
/

--2.创建程序包体
create or replace package body emp_package is
   --(1)添加雇员信息
   procedure add_emp(p_eno number,p_name varchar2,p_sal number,p_dno number)
   is
   begin
      if p_sal between minsal and maxsal then
         insert into emp(empno,ename,sal,deptno) values(p_eno,p_name,p_sal,p_dno);
      else
        dbms_output.put_line('工资不在范围内！');
      end if;
   exception
      when dup_val_on_index then
         dbms_output.put_line('该雇员已经存在');
   end; 
   
   
   --(2)通过雇员编号修改雇员工资
   procedure upd_sal(p_eno number,p_sal number)
   is
   begin
     if p_sal between minsal and maxsal then
        update emp set sal=p_sal where empno=p_eno;
        if sql%notfound then
          dbms_output.put_line('该雇员不存在');
        end if;
     else
        dbms_output.put_line('工资不在范围内');
     end if;
   end;
   
   --(3）.通过雇员姓名修改雇员工资
   procedure upd_sal(p_name varchar2,p_sal number)
   is
   begin
      if p_sal between minsal and maxsal then
         update emp set sal=p_sal where ename=p_name;
         if sql%notfound then
            dbms_output.put_line('该雇员不存在');
         end if;
      else
         dbms_output.put_line('工资不在范围内');
      end if;
   end;
   
   
   --（4）.通过雇员编号查询雇员工资
   function get_sal(p_eno number) return number
   is
      v_sal emp.sal%type;
   begin
     select sal into v_sal from emp where empno=p_eno;
     return v_sal;
   exception
     when no_data_found then
        dbms_output.put_line('该雇员不在');
   end;
   
   begin
     select min(sal),max(sal) into minsal,maxsal  from emp;
   end emp_package;
 /
 
 --3.调用程序包中子程序
 --（1）调用添加雇员信息
 set serverout on
 begin
   emp_package.add_emp(5000,'mary',300,20);
   dbms_output.put_line('添加成功');
   commit;
 end;
 /
 
 --（2）通过雇员编号查询雇员信息
 set serverout on
 declare
   v_sal emp.sal%type;
 begin
   v_sal:=emp_package.get_sal(1111);
   dbms_output.put_line('7369的雇员工资为：'||v_sal);
 end;
 /
 
--二、程序包中的游标使用
 --1.创建程序包包头
create or replace package emp_cursorPack is
  type type_emp_cursor is ref cursor;
end emp_cursorPack;
/

--2.创建存储过程并且游标作为输出参数
create or replace  procedure proc_emp(cur out emp_cursorPack.type_emp_cursor) as
  begin
      open cur for
        select empno,sal from emp;
end proc_emp;
/

--3.调用存储过程提取返回的游标内容
set serverout on
declare
  --定义员工编号
  v_empno emp.empno%type;

  --定义员工工资
  v_sal emp.sal%type;

  --定义游标类型的变量
  emp_sal emp_cursorPack.type_emp_cursor;
begin
  --调用存储过程，并将一堆数据存放在游标变量中
  proc_emp(emp_sal);

  --循环提取游标内容
  loop
     fetch emp_sal into v_empno,v_sal;
     exit when emp_sal%notfound;
     dbms_output.put_line(v_empno||'的薪水是'||v_sal);
  end loop;   
  close emp_sal;
end;
/