数据库对象——函数，视图，同义词，游标，包

• 函数
  • • 定义函数的语法
    • 练习计算两数之和
    • 练习函数之添加数据
    • 练习函数之调用函数
• 视图
  • • 视图的特点
    • 视图的添加与删除
      • 添加视图
      • 删除视图
• 同义词SYNONYM
  • • 语法
    • 练习同义词
• 游标
  • • 游标的分类
    • 游标语法
    • 游标的属性
    • 练习游标的基本使用
    • 练习游标之更新语句
• 包
  • • 包的组成
    • 包的优点
    • 包规范的创建
    • 包体的创建
    • 包的调用
    • 练习包的主体部分
    • 练习包的调用

---

函数

函数（function）分为两种，一种是oracle数据库自身的函数，另一种是用户自己写的函数。

定义函数的语法

create or replace function 函数名
（   参数1 in | out | in out 数据类型,
    .......
    参数2 in | out | in out 数据类型)
as
begin 
    SQL语句
    return 结果；
end;

练习：计算两数之和

create or replace function sum_func
(num1 in number,num2 in number)
return NUMBER
as
begin
  return num1+num2;
exception
  when others then dbms_output.put_line('计算有误');
end;

测试：

select sum_func(12,13)from dual

练习：函数之添加数据

create or replace function car_add_func
(car car%rowtype)
return NUMBER
as
BEGIN
  INSERT Into car values(car_seq.nextval,car.type,car.price,car.create_date,car.exhaust);
  COMMIT;
  return 1;
EXCEPTION
  when OTHERS then rollback;
  dbms_output.put_line('添加失败');
  return 0;
END;

练习：函数之调用函数

set serveroutput on;
exec car_add_pro('福特蒙迪欧','2.0T',200000.00,'1-1月 -15');

declare car_ car%rowtype;
begin
  car_.type:='JEEP自由光';
  car_.price:=250000.00;
  car_.create_date:='25-9月 17';
  car_.exhaust:='2.5T';
  dbms_output.put_line('影响行数是'||car_add_func(car_));
end;

---

视图

视图是由已经存在的数据，通过一定的运算规则，来获得新的数据集合。这使得用户可以更加灵活的自定义数据集合，视图同时为数据安全性提供了一种控制策略。

视图的本质就是关系运算的定义。

视图的特点

（虚拟的表，目的简化查询）

视图的优点：

• 封装查询

  数据库虽然可以存储海量数据，但是在数据表设计上却不可能为每种关系创建数据表。例如：对于学生表，存储了学生信息，学生的属性包括学号、姓名、年龄、地址等信息；而学生成绩表只存储了学生学号、科目、成绩等信息，现需要获得学生姓名及成绩信息，那么久需要创建一个关系，该关系需要包含学生姓名、科目、成绩。但为该关系创建一个新的数据表，并利用实际信息进行填充，以备查询使用，是不合适的。因为这种做法很明显的造成了数据库中数据的大量冗余。

  视图则是解决该问题的最佳策略，因为视图可以存储查询定义（或者关系运算），那么，一旦使用视图存储了查询定义，就如同存储了一个新的关系。用户可以直接对视图中所存储的关系进行各种操作，就如同面对的是真实的数据表。

• 灵活的控制安全性

一个数据表可能含有很多列，但是这些列的信息，对于不同角色的用户，可访问的权限有可能不同。例如：在员工表中，可能存在着员工工号、姓名、年龄、职位、地址、社会关系等信息。对于普通用户，有可能需要访问员工表，来查看某个工号的员工的姓名、职位等信息，而不允许查看家庭地址、社会关系等信息；对于高级用户，则需要关注所有信息，那么，久涉及到数据表的安全性。

利用视图可以灵活的实现这一策略，例如：可以首先创建名为vw_employees的视图，该视图的查询定义为，选择员工表中员工工号、姓名、职位等3列，这相当于在员工关系中，进行投影运算，即选择员工工号、姓名、职位等3个属性，形成新的关系。

同样的，对于高级用户，可以创建名为vw_employees_hr的视图，该视图选择员工表中所有列。

然后，对于两种角色分别分配两个视图的查询权限，与实际的数据表employees隔离开来，从而控制数据访问的安全性。

1. 是一个数据库中虚拟的表
2. 经过查询操作形成的结果
3. 具有普通表的结构
4. 不能实现数据的存储
5. 对视图的修改将会影响实际的数据表

oracle中的视图，按照创建和使用方式的不同，可以分为四类：关系视图、内嵌视图、对象视图和物化视图。

视图的添加与删除

添加视图

create or replace view emp_dept_view
as select * from emp NATURAL JOIN dept;

删除视图

drop view emp_dept_view;

---

同义词（SYNONYM）

语法：

同义词的创建语法：

create [public] synonym 同义词的名称 for 用户名.表名称 ;

同义词的删除：

drop synonym emp;

练习：同义词

同义词（synonym）：相当于对象的一个别名。

--设置同义词可以把不属于本账号的表空间下的表共享，可以在其他表空间下进行操作，
--公共同义词可以在任意表空间下操作
--创建同义词
--create synonym access_ for sys.ACCESS$;

--创建公共同义词
create public synonym access_ for sys.ACCESS$;

--删除同义词
drop synonym access_;

--根据同义词查找另一个表的内容
select * from access_;

---

游标

• 游标用来处理从数据库中检索的多行记录（使用select语句）。

• 利用游标，程序可以逐个地处理和遍历一次检索返回的整个记录集。

游标的分类

• 静态游标：结果集已经确定。
  
  • 隐式游标：所有的dml语句为隐式游标。
  • 显式游标：用户显示声明。
• 动态游标

游标语法

声明游标：

cursor cursor_name is
<select statements>
（当使用for循环时，不用open和close游标）

打开游标：

open cursor_name;

取得结果放入PL/SQL变量中：

fetch cursor_name into list_of variables;  （显式，必须使用open和close打开和关闭）

关闭游标：

close cursor_name;

游标的属性

PS：

使用found或者notfound时，必须fetch … into ….
–备份一个新表
create table emp1 as select * from emp;

练习：游标的基本使用

set serveroutput on;
--查询所有的员工信息，并打印信息
declare
  CURSOR emp_info is select * from emp;
  emp_ emp%rowtype;
begin
  open emp_info;--打开游标
  loop
    fetch emp_info into emp_;--把游标数据(结果集)放入到变量中
    exit when emp_info%notfound;--当不存在下一条数据时就结束循环
    dbms_output.put_line('员工编号是'||emp_.empno||',员工姓名是'||emp_.ename||',基本工资是'||emp_.sal);
  end loop;
  close emp_info;--关闭游标
end;

declare 
  cursor emp_info is select * from emp;
  emp_ emp%rowtype;
begin
  for emp_ in emp_info loop
    dbms_output.put_line('员工编号是'||emp_.empno||',员工姓名是'||emp_.ename||',基本工资是'||emp_.sal);
  end loop;
end;

练习：游标之更新语句

–编写一个PL/SQL程序块，对名字以‘A’或‘S’开始的所有雇员按他们的基本薪水(sal)的10%给他们加薪(对emp1表进行修改操作)

create table emp1 as select * from emp;--把一张表的数据备份到新表中
set serveroutput on;
declare 
  cursor e_cur is select * from emp where ename like 'A%' or ename like 'S%' ;
  emp_ emp%rowtype;
begin
  for emp_ in e_cur loop
    emp_.sal:=emp_.sal*1.1;
    update emp1 set sal=emp_.sal where empno=emp_.empno;
  end loop;
  commit;
exception 
  WHEN others then rollback;
end;

---

包

包就是一个把各种逻辑相关的类型、常量、变量、异常和子程序组合在一起的模式对象。包通常由两个部分组成：包说明和包体，但有时包体是不需要的。说明(简写为spec)是应用程序接口；它声明了可用的类型、变量、常量、异常、游标和子程序，包体部分完全定义游标和子程序，并对说明中的内容加以实现。

• 包是有存储在一起的相关对象组成的PL/SQL结构.

• 用于逻辑组合相关的自定义类型、变量、游标、过程和函数.

包的组成

• 包的规范（又称包头）

  1. 用于定义常量、变量、游标、过程和函数等用于与程序的接口
  2. 可以在保内引用，也可以被外部程序调用

• 包的主体

  1. 是包规范的实现，包括变量、游标、过程和函数等。

  2. 包体内的内容不能被外部应用程序调用。

---

包的优点

模块化、方便应用程序设计、信息隐藏、附加功能和良好的性能。

包规范的创建

创建包的规范
--包的规范
create or replace package test_package
as
--声明一个存储过程 
procedure add_emp_pro(emp_ emp1%rowtype);

--声明一个函数 
function sum_func(num1 number,num2 number)
return number;

end test_package;

---

包体的创建

---

包的调用

练习：包的主体部分

create or replace PACKAGE body test_package
as
-- 实现存储过程
PROCEDURE add_emp_pro(emp_ in emp1%rowtype)
as
begin
  dbms_output.put_line('成功添加一条数据');
end;

--实现函数 
function sum_func(num1 number,num2 number)
return NUMBER
as
begin
  return num1+num2;
end;

end test_package;

练习：包的调用

set serveroutput on;

declare 
  emp_ emp1%rowtype;
begin
  emp_.empno:=9527;
  emp_.ename:='老张';
  test_package.add_emp_pro(emp_);
end;

begin
  dbms_output.put_line('两数之和是'||test_package.sum_func(3,5));
end;