Oracle数据库系列(六)、Oracle数据库对象

第六章、Oracle数据库对象

学习目标

• 使用函数实现数据处理
• 使用过程实现数据处理
• 使用包实现程序代码的组织
• 学会在Oracle中使用过程
• 学会在Oracle中使用函数
• 学会在Oracle中使用视图
• 学会在Oracle中使用序列

6.1、过程

• 创建存储过程练习1

  -------------创建存储过程（根据性别查询员工信息）
  create or replace procedure proc_emp_search_sex
  (
      ---此处参数的数据类型要和查询的表中字段类型保持一致（切记不可带指定长度）
      v_sex char
  )
  as
  ----声明部分
  Cursor cur_emp is select * from emp where sex = v_sex;
  begin
    ---for-loop-end loop
    for c_emp in cur_emp loop
      dbms_output.put_line(c_emp.ename||'---'||c_emp.sal||'---'||c_emp.sex);
    end loop;
  end;
  

• 调用存储过程1（也可以直接右键测试，建议自行调用，更清楚逻辑）

  ---------调用存储过程proc_emp_search_sex
  declare 
    v_sex char(3);
  begin
    v_sex := '男';
    proc_emp_search_sex(v_sex);
  end;
  

• 创建存储过程练习2

  ------------创建存储过程(根据部门编号获取部门员工人数)
  create or replace procedure proc_getEmpCount_byDeptno
  (
    v_deptno in number ,
     --返回值接收参数(可以在声明处，也可以在定义处定义返回参数)
    v_count out number
  )
  as
  begin
    select count(empno) into v_count from emp where deptno = v_deptno;
  end;
  
  ------------调用存储过程proc_getEmpCount_byDeptno
  declare
    v_deptno number := 20;
    v_count number;
  begin
    proc_getEmpCount_byDeptno(v_deptno,v_count);
    dbms_output.put_line(v_count);
  end;
  

• 创建存储过程练习3

  ------------创建存储过程(根据员工编号，获取员工工资)
  create or replace procedure proc_getEmpSal_byEmpno
  (
     v_empno in number,
     v_sal out number
  ) 
  as
  begin
    select sal into v_sal from emp where empno = v_empno;
  end;
  
  -----------调用存储过程proc_getEmpSal_byEmpno
  declare
    v_empno number := 7369;
    v_sal number;
  begin
    proc_getEmpSal_byEmpno(v_empno,v_sal);
    dbms_output.put_line('员工编号为'||v_empno||'的工资是'||v_sal);
  end;
  

• 创建存储过程练习4

  -----------创建存储过程proc_dept_insert
  create or replace procedure proc_dept_insert
  (
    v_deptno in number,  --输入参数
    v_dname in varchar2, --输入参数
    v_loc in varchar2,   --输入参数
    v_result out number, --输出参数(返回值)
    v_msg out varchar2   --输出参数(返回信息)
  )
  is
  begin
    insert into dept values(v_deptno,v_dname,v_loc);
    commit;--提交事务
    v_result := 1;
    v_msg := 'success';
    
    --如果插入信息失败
    Exception 
      when others then
        v_result := 0;
        v_msg := 'error';
  end;
  
  ----------调用存储过程proc_dept_insert
  declare
    v_deptno number(2) := 88;
    v_dname varchar2(20) := '测试5部';
    v_loc varchar2(20) := '洛阳';
    v_result number(1);
    v_msg varchar2(20);
  begin
    proc_dept_insert(v_deptno,v_dname,v_loc,v_result,v_msg);
    dbms_output.put_line(v_result||'---'||v_msg);
  end;
  

6.2、函数

• 创建自定义函数（通过部门号获取该部门员工人数）

  create or replace function func_getEmpCount_ByDeptno
  (
     v_deptno in number --输入参数
  )
  return number  --函数返回值类型
  as
    --声明参数
    func_count number; --记录部门人数
  begin
    select count(empno) into func_count from emp where deptno = v_deptno;
    return func_count;
  end;
  
  ----------调用函数func_getEmpCount_ByDeptno
  declare
    f_count number;
    v_deptno number := 20;
  begin
    f_count := func_getEmpCount_ByDeptno(v_deptno);
    dbms_output.put_line('部门'||v_deptno||'有'||f_count||'人');
  end;
  

6.3、过程与函数的比较

6.4、Oracle中的包

6.4.1、创建包头（关键字package）

create or replace package pack_emp
as

  --根据部门编号插叙部门人数
  procedure proc_getdeptcount_bydeptno(v_deptno in number,v_count out number);
  
  --根据员工编号查询员工工资
  procedure proc_getempsal_byempno(v_empno in number,v_sal out number);
  
  --自定义函数，根据部门编号插叙部门人数
  function func_getempcount_bydeptno(v_deptno in number) return number;

end pack_emp;

6.4.2、创建包体（关键字package body）

create or replace package body pack_emp
as
   --根据部门编号插叙部门人数
  procedure proc_getdeptcount_bydeptno(v_deptno in number,v_count out number)
  as
  begin
    select count(empno) into v_count from emp where deptno = v_deptno;
  end;
  
  --根据员工编号查询员工工资
  procedure proc_getempsal_byempno(v_empno in number,v_sal out number)
  as
  begin
    select sal into v_sal from emp where empno = v_empno;
  end;
  
  --自定义函数，根据部门编号插叙部门人数
  function func_getempcount_bydeptno(v_deptno in number) return number
  as
    --声明
    func_count number;
  begin
     select count(empno) into func_count from emp where deptno = v_deptno;
     return func_count;
  end;

end pack_emp;

6.2.3、调用包下的对象

declare
  v_count1 number;
  v_sal number;
  v_count2 number;
begin
  ---存储过程proc_getdeptcount_bydeptno
  pack_emp.proc_getdeptcount_bydeptno(20,v_count1);
  dbms_output.put_line('存储过程查询---部门号为20的员工人数是'||v_count1);
  
  --存储过程pack_emp.proc_getempsal_byempno
  pack_emp.proc_getempsal_byempno(7369,v_sal);
  dbms_output.put_line('存储过程查询---员工编号为7369的员工工资是'||v_sal);
  
  --自定义函数pack_emp.func_getempcount_bydeptno
  v_count2 := pack_emp.func_getempcount_bydeptno(20);
  dbms_output.put_line('自定义函数查询---部门号为20的员工人数是'||v_count2);
  
end;

6.5、视图

以scott用户为例，要想创建视图，必须要有dba权限。首先就是要system管理员为scott用户授予dba权限，然后才可以创建视图。

--授予scott用户dba权限（授予后仍然要在角色权限勾选可授予）
grant dba to scott;

• 也可以直接在角色权限中添加dba权限，并勾选可授予（推荐）
  
• 创建视图view_emp

  --根据部门编号，查询员工信息、部门名称和地址，并以员工编号升序排序
  create or replace view view_emp
  as
    select e.*,d.dname,d.loc from emp e,dept d where e.deptno = d.deptno
    order by empno
  

• 通过视图查询数据

  --通过视图查询数据
  select * from view_emp where dname = 'ACCOUNTING';
  

6.6、序列

• 创建序列

  --创建序列
  create sequence seq_empid
  start with 1     --初始值 
  increment by 1   --增长间隔值
  minvalue 1       --最小值
  maxvalue 9999    --最大值
  cache 20         --缓冲池（提高效率）
  

• 查询和修改序列

  --查询下一个序列值
  select seq_empid.nextval from dual;
  
  --查询当前序列值
  select seq_empid.currval from dual;
  
  --修改增长间隔值
  alter sequence seq_empid
  increment by 2;
  
  --再次查询下一个序列值（增长值为2）
  select seq_empid.nextval from dual;
  

6.7、数据库链

• 数据库链的创建

  create database link link_qq
  connect to qq identified by "123456"
  using '(DESCRIPTION =
      (ADDRESS_LIST =
           (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)))
           (CONNECT_DATA =(SERVICE_NAME = ORCL))
      )';
  

  小贴士：
  跨用户查询，此处远程用户的密码最好使用双引号引起来，因为11g数据库会默认把密码中的字符转化为大写，双引号的作用就是指定字符的大小写。
  

• 使用远程数据库完整路径创建数据库链
  

  小贴士：
  服务器路径可以是远程的，也可以是本地的。

• 调用数据库链

  select * from userinfo@link_qq;
  

  小贴士：
  如果查询的远程用户的表中含有LOB字段，是不可以查询的，可以指定LOB外的字段查询，也可以创建临时表查询（自行了解）。
  

• 指定字段远程查询

  select userid,username,userpwd,money from userinfo@link_qq;