Oracle知识点总结
1、Oracle数据库的安装和配置
⑴Oracle Internet级数据库
SQLServer 中小企业级数据库
Access 桌面级数据库
⑵Oracle的安装
注意： 来源和目标的目录不允许有中文或空格
⑶Oracle数据库启动时必需开启的后台服务
①OracleOrahome90TNSListener 使第三方的软件或语言访问 ②OracleServiceETC Oracle的实例 CRUD 增删改查
注意：②中的ETC是你起的实例的名字
⑷Oracle的开发工具
①DOS界面的开发平台 -> 运行->sqlplus
②Oracle本身基于DOS的平台 ->运行-> sqlplusw
③Oracle Enterprise Manager Console
④PL/SQL Developer7.1.5
⑸创建一个表的完整流程
①创建一个数数库
例子：创建一个数据库 ETC , 物理文件放在F:\ ，初始化1m ,自增长开启
create tablespace etc
datafile 'f:\etc.dbf'
1
size 1m
autoextend on;
删除表空间
drop tablespace 数据库名称;
②为该数据库创建一个帐号和密码
语法： create user 用户名称
identified by 密码
default tablespace 数据库名称
注意：1、在Oracle中账号和数据库one2one绑定
2、Oracle的密码不允许为纯数字或空
3、需要在system账号完成动作
修改Oracle的账号密码
语法：alter user 用户
identified by 新密码
③用grant 权限 to 用户 为该帐户授权
语法： grant 权限名称 to 用户;
撤销权限
语法：revoke 权限名称 from 用户名;
④在该帐号下创建表
⑹Oracle中的事务处理
2
Transacation 事务
特点：整体提交(commit) 整体回滚(rollback)
事务的四个特性
①原子性 不可分割
②持久性 -> 当数据整整写入到数据库物理文件中后，该数据被 持久化
③隔离性 -> 事务之间相互独立互不干扰
④一致性 -> 数据安全
⑺Oracle中的保留点(还原点)
关键字：savepoint
使用方法：savepoint 名称;
如进行保留点回滚
rollback to mark2
⑻Oracle备份数据表数据
①create table 备份表名称 as 查询语句;
②Oracle备份表结构
create table emp_bak as select * from emp where 1=2;
2、Oracle的函数
⑴function （系统预定义函数）
函数和存储过程相比，在于函数必须有返回值，而存储过程只有输出参数。 语法：create or replace function 函数名称
return 返回类型
3
as
PL/SQL语句块
例子：输入一个员工工号，返回该员工所在部门平均工资
create or replace function fun_demo1(eno emp.empno%type) return emp.sal%type
as
avgsal emp.sal%type;
begin
select avg(sal) into avgsal from emp where empno=eno; return avgsal;
end;
调用：select fun_demo1(7788) from dual;
public int add(int num1,int num2){
int res = 0;
res = num1+num2;
return res;
}
⑵单行函数(单值函数) ：函数有且只有返回1个值
|-字符函数 substr instr
①字符串连接函数：concat
4
concat(字符串1,字符串2)
Oracle中字符连接操作符号 " || "
②字符串对齐函数：lpad -> left padding 左填充 /rpad
语法：lpad(字符串,预留位置,填充字符)
SQL: select lpad(ename,10,' ') from emp;
③字符串截取函数：substr -> substring
语法：substr(字符串,截取的起始位置,截取个数)
substr(字符串,截取的起始位置)
SQL:> select substr(ename,1,3) frome emp;
④字符查找函数：instr -> inner string
语法：instr(字符串,待找的字符,查找的起始位置,出现 次数) SQL:> select ename,instr(ename,'T',1,1) from emp; 注意：当返回值为0时，字符不存在
⑤函数：initcap -> initial captor
语法：initcap(字符串)
SQL:> select initcap(ename) from emp;
⑥函数：length
语法：length(字符串)
SQL:> select * from emp where length(ename)=5;
⑦函数：lower / upper
语法：lower(字符串)
SQL:> select lower(ename) from emp;
5
|-日期函数 sysdate add_months
①函数：sysdate
SQL:> select sysdate from dual; dual-无实际意 义，函数测试或其他测试使用
②月份差：months_between
语法：months_between(日期1,日期2)
SQL: > select * from emp where
months_between(sysdate,hiredate)>=144
③函数：last_day
语法：last_day(日期数据)
SQL:> select * from emp where
last_day(hiredate)-2=hiredate;
④函数：add_months
语法：add_months(时间,添加月份)
SQL：>select add_months(sysdate,3) from dual;
|-转换函数 to_char to_date
①转换函数 to_char
语法：to_char(时间数据,制定格式)
SQL:> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss d ddd day') from dual;
yyyy-mm-dd 年月日
6
hh24:mi:ss 时分秒(24)
d 一周第几天
ddd 一年的第几天
day 星期几
②to_date: 将文本类型数据按照制定格式转换成时间类型数据 语法：to_date('具体的时间字符数据','时间字符串格式')
to_date('2010-01-19','yyyy-mm-dd')
注意：Oracle中的date字段类型及其特殊
|-数学函数 abs ceil floor
abs(n) 绝对值
ceil(n) 向上取值 ceilling天花板 ceil(1.1) -> 2
floor(n) 向下取值 floor地板 floor(2.9) -> 2
mod(m,n) 求模取余 mod(5,2) -> 1
power(m,n) m的n次方 power(2,8) ->256
round(m,n) 四舍五入 m待处理数据 n精度 round(3.1415,2) ->
3.14
trunc(m) 整数截取 trunc(3.1415) = 3
sign(m) 符号
sqrt(m) 平方根
|-混合函数 user nvl
①user : 返回当前登录账号
SQL> select user from dual;
7
②nvl : 替空函数
语法：nvl(字符串,替换后的数据)
注意：替换后的数据必须与该字段类型保持一致
SQL> select ename,nvl(comm,0) from emp;
⑶分析函数：数据分析和挖掘功能 Data Mining rank dense_rank
作用：主要用于排名使用
函数：rank
①语法：rank() over (order by 字段名称)
SQL:> select rank() over(order by sal desc),ename,sal from emp; 若排名相同下一个将跳转
SQL:> select dense_rank() over(order by sal desc),ename,sal from emp; 连续
②语法：rank(参数1，参数2) within group(order by 字段1，字段2)
SQL:> select ename,rank(2850,'BLAKE') within group(order by sal,ename) from emp;
⑷分组函数：类似于SQLServer中的聚合函数 sum avg count
group by 分组关键字 having 条件
3、Oracle数据库的查询
⑴制定区间查询
Oracle中伪列：Oracle为每张数据表(包括用户自定义创建的数据表)都
自动创建两个位列分别是：rowid、rownum
8
通过命令：SQL> desc 表名; --查看表结构
rowid: 由18个字符组成唯一标识每一行
rownum: 行号
⑵介绍 问题：使用Oracle分层查询以数据结构显示emp表中的人员关系 语法：select lpad(ename,level*5,' ') from emp
connect by prior empno=mgr
start with mgr is null;
⑶并集：查询部门10的办事员和部门20的经理
关键字：union all
select * from emp where deptno=10 and job='CLERK'
union all
select * from emp where deptno=20 and job='MANAGER';
注意：union all不会消除查询结果中的重复数据, union会消除查询结果中的重复记录
⑷交集：查询部门10和部门20都有的工作类型
关键字：intersect
select job from emp where deptno=10
intersect
select job from emp where deptno=20;
⑸差集：查询部门30中有，而部门10中没有的工作类型
关键字：minus
select job from emp where deptno=30
9
minus
select job from emp where deptno=10;
4、Oracle数据库的数据对象
Oracle的数据库对象：用户、表、约束、序列、视图、同义词和索引 定义：但凡使用 "create" 开头创建的对象称之为数据库对象。
⑴锁定/解除用户
语法：alter user 用户名称 account lock;
注意：该指令一般在system账号下输入
解除用户锁定
语法：alter user 用户名称 account unlock;
SQL > alter user scott account unlock;
注意：Oracle 11g 中 scott账号是默认锁定的，需要进行解锁处理。 ⑵用户授权/撤销
grant 权限名称 to 用户名称;
撤销用户权限
revoke 权限名称 from 用户名;
⑶表
数据字段的类型
①数字类型：number
语法：number(长度,精度)
number(5,2) 数字长度为3，精度为2 范围：-999.99 ~ 999.99 10
number(5) 数字长度为5，精度为0 范围：-99999 ~ 99999 number 精度默认为0 范围：-32767 ~ 32768
②字符类型：varchar2 可变长度 char 字符类型
语法：varchar2(长度)
③日期类型：date
④long数据类型： 该字段最大存储空间为2GB，该字段不允许添加索引
LOB数据类型：该字段最大存储空间为4GB，该字段不允许添加索引 CLOB ：大字符类型 -> 文章(小说，cnki)
BLOB ：大二进制类型 -> 图片、音频、视频.....多媒体文件 FLOB ：文件定位器 -> 内存指针
⑷创建数据表的语法结构：
create table 表名
(
字段名称1 类型 [约束],
字段名称1 类型 [约束],
??
字段名称n 类型 [约束]
)
①使用 desc 表名 查看表结构
②给表添加字段address和telephone
语法：alter table 表名 add(字段名称 类型);
11
③删除表字段address
语法：alter table 表名 drop column 字段名称;
④修改表中已有字段的类型
语法：alter table 表名 modify(字段名称 新类型);
⑤修改表名称
语法：rename 原表名 to 新表名;
约束
完整性约束 = 准确性 + 一致性
⑸ 约束的四大分类：
①实体完整性约束 (行约束) -> 尽量减少数据表中数据的冗余(重复的数据)
技术实施：主键约束、唯一约束
语法：alter table 表名 add primary key(字段名称);
altet table 表名 add unique(字段名称);
添加非空约束
语法：alter table 表名 modify(字段名称 not null);
②域完整性约束 (列约束) -> 达到数据的准确性，控制数据的大小或范围或格式
技术实施：check检查约束
alter table 表名 add constraint 约束名称 check(条件);
③引用完整性约束 (表间约束) -> 达到数据的一致性
技术实施：外键约束
12
添加外键约束
alter table 外键表 add constraint 约束名称 foreign key(外键字段) references 主键表(主键字段);
删除外键约束
语法：alter table 表名 drop constraint 约束名称;
删除匿名约束
语法：alter table 表名 modify(字段名称 null);
④自定义完整性约束 -> 以上三种数据库内置约束不能满足开发人 员的需求是，需要开发人员自定一些约束条件
技术实施：触发器
⑹使用sql脚本批量插入数据
使用命令 SQL > @路径+文件名称
SQL > @f:/a.sql;
⑺序列
定义：Oracle中使用sequence来实现字段的自增长功能，和SQLServer中的identity属性类型
语法：create sequence 序列名称
start with 起始数字
increment by 增长量;
用法：序列对象通过两个重要的属性进行访问取值
.nextval -> next value -> 下一个值
13
例子：select seq_1.nextval from dual;
.currval -> current value -> 当前值
例子：select seq_1.currval from dual;
如何实现其自增长功能
通过语法结构将序列对象与对应的数据表进行绑定，实现其自增长功能。
例子： create table users(userid number(2),username varchar2(10));
create sequence seq_users start with 1 increment by 1; insert into users values(seq_users.nextval,'alvin'); 注意：Oracle建议一个序列对象尽与一张数据表进行绑定
技巧：create sequence 序列名称; 默认从1每次增长1
⑻视图
作用：1、简化复杂的SQL语句
2、提高数据的访问安全性
语法：create or replace view 视图名称
as
复杂的SQL查询语句
视图是一张虚拟的数据表，在Table对象中不存在，只存在于内存中
注意：若视图由1张基表组成，修改视图将会修改基表数据，若视图 由多张表组成，则不会修改基表数据
⑼同义词
14
作用：Oracle中的同义词提供各种数据库对象(表)的别名，目的在于提高数据表访问的安全性，尤其多用户并发访问时。
语法：create synonym 表的别名 for 表名;
SQL > create synonym bak1 for emp;
扩展：公有同义词 public
语法：create public synonym 别名 for 表名;
⑽索引
作用：提高SQL查询语句按照制定字段查询的效率
语法：create index 索引的名称 on 数据表(字段)
例子：为hiredate字段添加索引，提高按日期查询的SQL语句的效率
create index idx_hire on emp(hiredate)
原理：空间换效率
SQL语句索引优化规则
①不带where条件的SQL语句一定不能使用索引
②在where条件中有索引的字段不能使用函数
例子：假设我们查询在19xx年参加工作的所有员工
SQL >select * from emp where to_char(hiredate,'yyyy')='1987'; SQL >select * from emp where hiredate=to_date('1987','yyyy'); ③在where条件中有索引的字段不能参与运算
例子：查询在10000天以前参加工作的员工信息
SQL >select * from emp where sysdate-hiredate>10000;
15
SQL >select * from emp where hiredate<sysdate-10000;
④尽量少用 not in , not exists , like '%' 等关键字
⑤书写查询语句的时候，应该首先考虑关联查询、其次考虑集合查询，最后考虑子查询
⑥where条件中限制性强的条件应该写在where最后的一个条件
where条件从右向左经行条件解析
⑦多表查询中，驱动表应该是返回数据少的表
例子：查询部门account 的所有员工信息
SQL > select e.*,d.dname from emp e,dept d where e.deptno=d.deptno and d.dname='ACCOUNTING';
⑾Oracle中常用的数据字典
Oracle是以中以表管表的模式，同时数据字典是有表或视图组成。 数据字典的分类：
USER_xxx ： 表示当前用户所拥有的数据库对象
ALL_xxxx ： 表示当前用户与权力查看的数据库对象
DBA_xxxx ： 表示数据中所拥有的全部对象，只有在超级管理员级别下才可查看。
xxxx ：代表数据库对象的复数形式，例如：tables indexes .......
16
5、Oracle数据库的多表查询
⑴等连接：select emp.ename,dept.dname from emp,dept where
emp.deptno=dept.deptno;
使用内连接模式编写：
⑵内连接：inner join ......on......
select emp.ename,dept.dname from emp inner join dept on
emp.deptno=dept.deptno;
分析：部门为主显示字段，dept为主表
姓名为辅助显示字段，emp为辅助表
⑶外连接：select d.dname,e.ename from emp e,dept d where
e.deptno(+)=d.deptno;
6、Oracle数据库的游标
游标 cursor
游标的分类： 隐式游标 ： Oracle自动应以一个隐式游标名称为 SQL ,该游标不被程序员控制，自动开启、操作及结束。
显式游标 ： 可供程序员自己创建及操作
|--- 静态游标
|--- 动态游标
⑴静态游标 loop循环游标 / for循环游标
17
====loop循环游标=====
步骤1：创建一个游标
cursor 游标名称 is SQL查询语句;
步骤2：开启游标
open 游标名称; -> 执行定义的SQL查询语句并将结果集合存放到游标中
步骤3：使用loop循环遍历游标中的数据并进行相应处理
loop
fetch 游标名称 into 变量;
exit when 游标名称%notfound;
.....
end loop;
步骤4：关闭游标
close 游标名称;
例子：显示部门编号为10的员工姓名
declare
--步骤1：创建一个游标
cursor mycur is select ename from emp where deptno=10;
en emp.ename%type;
18
begin
--步骤2：开启游标
open mycur;
--步骤3：使用loop循环遍历游标中的数据并进行相应处理 loop
fetch mycur into en;
exit when mycur%notfound;
dbms_output.put_line(en);
end loop;
--步骤4：关闭游标
close mycur;
end;
=======for循环游标=======
declare
--步骤1：创建一个游标
cursor mycur is select * from emp where deptno=10;
erow emp%rowtype;
begin
--步骤3：使用for循环遍历游标中的数据并进行相应处理 for erow in mycur
loop
dbms_output.put_line(erow.ename);
19
end loop;
end;
⑵动态游标(扩展)
作用：使用游标变量高度重用(只能使用loop循环) 语法结构：
步骤1：声明一个动态游标类型
type 类型名称 is ref cursor;
步骤2：使用声明号的类型创建一个游标变量 游标名称 类型名称;
步骤3：打开游标并且绑定SQL语句
open 游标名称 for SQL查询语句
步骤4：使用循环遍历游标中的数据并进行处理 loop
fetch 游标名称 into 变量;
exit when 游标名称%notfound
.....数据处理......
end loop;
步骤5：重复3~4
步骤6：关闭游标
20
close 游标名称;
例子：显示部门30的员工姓名及dept表中全部部门名称 declare
--声明一个游标类型
type cur is ref cursor;
--声明游标变量
mycur cur;
--声明变量
erow emp%rowtype;
drow dept%rowtype;
begin
--打开游标
open mycur for select * from emp where deptno=30; loop
fetch mycur into erow;
exit when mycur%notfound;
dbms_output.put_line(erow.ename);
end loop;
dbms_output.put_line('============');
--打开游标
open mycur for select * from dept;
loop
21
fetch mycur into drow;
exit when mycur%notfound;
dbms_output.put_line(drow.dname);
end loop;
--关闭游标
close mycur;
end;
⑶游标所有属性
游标名称%属性名称
属性名称 含义
notfound 当游标中没有遍历的数据时返回1 found 当游标中存在有遍历的数据时返回1 isopen 当游标开启状态时返回1
rowcount 返回游标影响行数
7、Oracle数据的PL/SQL
⑴PL/SQL 块
语法格式：declare 声明
...... 变量声明区 begin 程序开始
...... 执行语句区
exception 异常处理部分 22
.....
end; 程序结束; ①变量的声明
标量标识符，不区分大小写，先声明再使用 变量声明的语法结构：标量名称 类型; 例子：declare
eno number(4);
edate date;
??
begin
??
end;
② ★属性引用类型
|- 字段引用类型
例子：declare
eno emp.ename%type;
|- 行引用类型
例子：declare
erow emp%rowtype;
③变量的赋值
四种方式：
◆声明时赋值
23
赋值运算符 “:=”
例子：声明一个变量赋值为2
declare
num1 number(2):=2;
.....
begin
.....
end;
◆执行区赋值
例子：声明一个变量赋值为2
declare
num1 number(2);
.....
begin
num1:=2;
.....
end;
◆对话框赋值-> 程序与用户之间进行数据交互 例子：声明一个变量赋值为2
declare
num1 number(2);
.....
24
begin
num1:=&请输入数字;
.....
end;
注意点：当输入字符串类型数据的使用书写格式为：name:='&姓名'; 当输入数字类型数据的使用书写格式为：age:=&年龄;
◆select....into.... 赋值模式
例子：将emp表中simth的工资复制到变量esal中
declare
esal emp.sal%type;
begin
select sal into esal from emp where ename='SMITH';
??
end;
④Oracle的PL/SQL中屏幕输出语句
语法：dbms_output.put_line('xxxxxxx'); 将信息输出并换行 dbms_output.put('xxxxxxx'); 输出不换行
注意：Oracle默认关闭屏幕输出功能，需要开发人员输入指令将其功能开启
SQL > set serveroutput on;
⑤PL/SQL中的异常处理
语法：exception
25
when 异常名称 then
....处理方式.....
异常：others -> 任何异常都可捕获
⑵执行语句部分
① 循环的使用：for while loop
for语法结构：for 循环变量 in 起始数字..结束数字 loop
....循环体....
end loop;
例子1：显示数字10~1 (reverse)
declare
i number;
begin
for i in reverse 1..10
loop
dbms_output.put_line(i);
end loop;
end;
例子1：显示乘法口诀
declare
i number;
26
j number;
begin
--外层循环控制行数
for i in 1..9
loop
--内层循环控制个数
for j in 1..i
loop
dbms_output.put(j||'*'||i||'='||i*j||' '); end loop;
--换行
dbms_output.put_line(' ');
end loop;
end;
②while循环
语法：while 进入条件
loop
....循环体.....
end loop;
③loop 循环
语法：loop
exit when 推出条件;
27
...循环体....
end loop;
⑶ 条件判断
语法： if 条件 then
??
elsif 条件 then
??
else
??
end if;
例子：模拟一个登录结构，用户名为admin密码为chinasoft
declare
username varchar2(10);
password varchar2(10);
begin
--用户输入账号及密码
username:='&账号';
password:='&密码';
--逻辑判断
if username='admin' and password='chinasoft' then dbms_output.put_line('欢迎登录：'||username); 28
else
dbms_output.put_line('账号或密码错误！');
end if;
end;
⑷处理自定义异常
例子：若用户输入的数字不再1~10之内则抛出异常
步骤1：创建一个异常对象
在变量声明区输入一下代码
exp exception; --声明一个异常对象，名称为exp 步骤2：编写条件判断结构抛出异常
在语句执行区编写一下代码
if ..... then
raise 异常对象名称; --抛出异常
end if;
步骤3：异常捕获
在语句执行区编写
exception
when 异常对象名称 then
......处理方式........
declare
29
n number; --用来接收用户输入的数字
exp exception; --创建一个异常对象
begin
n:=&数字;
--判断
if n<1 or n>10 then
raise exp; --抛出异常
end if;
dbms_output.put_line(n);
exception
when exp then
dbms_output.put_line('数字范围不再1~10之内'); end;
⑸
8、Oracle数据库中的存储过程、函数、数据包
⑴Oracle中的存储过程
Oracle中的PL/SQL语句块，没有名字，成为匿名的PL/SQL块，代码重用性低。需要为PL/SQL块起名字，起名后的PL/SQL块统称为子程序。 子程序的分类：存储过程、自定义函数
①存储过程
语法：create or replace procedure 存储过程名称
as
30
PL/SQL块
②如何调用存储过程
两种方式：1、指令方式 -> exec 存储过程名称;
2、使用PL/SQL块调用
begin
存储过程名称;
end;
③带参数的存储过程
两大类：a) 带输入参数
b) 带输出参数
a) 带输入参数的存储过程
语法：create or replace procedure 存储过程名称(参数名称1 in 类型, .... ,参数名称n 类型)
as
PL/SQL块
b) 带输出参数的存储过程
语法：create or replace procedure 存储过程名称(参数名称1 out 类型, .... ,参数名称n out 类型)
as
PL/SQL块
例子：显示部门10的平均工资(使用输出参数)
31
create or replace procedure pro_demo3(avgsal out emp.sal%type)
as
begin
select avg(sal) into avgsal from emp where deptno=10; end;
调用： 只能使用PL/SQL块
declare
getsal emp.sal%type;
begin
pro_demo3(getsal);
dbms_output.put_line(getsal);
end;
/1、输入指定员工的员工编号，输出其所在部门的其他姓名(存储过程、带参数的游标、输入参数)
思路：*1、使用SQL语句查询7788员工所在部门的其他员工信息 select * from emp where deptno=(select deptno from emp where empno=7788) and empno<>7788;
*2、修改成PL/SQL块
declare
cursor mycur(eno emp.empno%type) is select * from emp where deptno=(select deptno from emp where empno=eno) and empno<>eno; 32
erow emp%rowtype;
tmp emp.empno%type;
begin
tmp:=&工号;
for erow in mycur(tmp)
loop
dbms_output.put_line(erow.ename);
end loop;
end;
* 3、修改成为存储过程
create or replace procedure pro_demo4(tmp emp.empno%type) as
cursor mycur(eno emp.empno%type) is select * from emp where deptno=(select deptno from emp where empno=eno) and empno<>eno; erow emp%rowtype;
begin
for erow in mycur(tmp)
loop
dbms_output.put_line(erow.ename);
end loop;
end;
*4、调用
33
exec pro_demo4(7788);
/2、输出制定员工所在部门的平均工资(存储过程、输入参数、输出参数) 思路：*1、编写SQL语句
select avg(sal) from emp where deptno=(select deptno from emp where empno=7788)
* 2、PL/SQL语句
declare
eno emp.empno%type;
esal emp.sal%type;
begin
eno:=&编号;
select avg(sal) into esal from emp where deptno=(select deptno from emp where empno=eno);
dbms_output.put_line(esal);
end;
* 3、修改为存储过程
create or replace procedure pro_demo5(eno in
emp.empno%type,esal out emp.sal%type)
as
begin
select avg(sal) into esal from emp where deptno=(select 34
deptno from emp where empno=eno);
end; * 4、调用
declare
tmp emp.sal%type;
begin
pro_demo5(7788,tmp);
dbms_output.put_line(tmp);
end;
⑵程序包
程序包是一个函数或存储过程的集合。 程序包的结构：第一部分：程序包的声明 create or replace package 包名称
as
存储过程的声明;
自定义函数的声明;
end;
第二部分：程序包主体
create or replace package body 包名称 as
存储过程的实现;
35
自定义函数的实现;
例子：完成对EMP表进行增删改的操作
part1:
create or replace package emp_pack
procedure add_emp(eno emp.empno%type,enm emp.ename%type,dno emp.deptno%type);
procedure update_emp(eno emp.empno%type,esal emp.sal%type); procedure delete_emp(eno emp.empno%type);
end;
part2:
create or replace package body emp_pack
as
--添加
procedure add_emp(eno emp.empno%type,enm emp.ename%type,dno emp.deptno%type)
as
begin
insert into emp(empno,ename,sal) values(eno,enm,dno); dbms_output.put_line('add is ok');
end;
--修改
procedure update_emp(eno emp.empno%type,esal emp.sal%type) 36
as
begin
update emp set sal=esal where empno=eno;
dbms_output.put_line('update is ok');
end;
--删除
procedure delete_emp(eno emp.empno%type)
as
begin
delete from emp where empno=eno;
dbms_output.put_line('delete is ok');
end;
end;
如何调用 exec 程序包名称.过程名称(参数);
select 程序包名称.函数名称(参数) from dual;
⑶触发器
关键字：trigger -> 扳机、目标
当需要级联操作的时候可能用到触发器
语法：create or replace trigger 触发器名称
before|after insert or update or delete [of 字段名称] on 表 begin
37
PL/SQL语语句
案例：当对Emp进行CUD操作时候后经行出发 create or replace trigger tri_emp
after insert or update or delete on emp begin
if inserting then
dbms_output.put_line('add is ok'); elsif updating then
dbms_output.put_line('update is ok'); else
dbms_output.put_line('delete is ok'); end if;
end;
触发器的分类：
分为：表级触发
行及触发 for each row
例子：更新Emp表中部门为10的员工工资 表级触发
create or replace trigger tri_demo1
after update on emp
begin
38
if updating then
dbms_output.put_line('更新 is ok');
end if;
end;
行级触发
create or replace trigger tri_demo1
after update on emp
for each row
begin
if updating then
dbms_output.put_line(:old.empno||'更新 is ok');
end if;
end;
在触发器中:old代表原数据对象(一行对象),:new代表新的数据对象
案例：删除Emp中指定数据的时候，将删除的数据自动备份到另外一张表中。
步骤1：创建一张备份表
create table emp_bak2 as select * from emp where 1=2; 39
步骤2：编写触发器自动完成数据备份功能
create or replace trigger tri_demo2
before delete on emp
for each row
begin
insert into emp_bak2
values(:old.empno,:old.ename,:old.job,:old.mgr,:old.hiredate,:old.sal,:old.comm,:old.deptno);
dbms_output.put_line('数据备份成功');
end;
9、Oracle数据库中的备份与还原
10、Oracle 数据库优化
40