Oracle知识点总结(18600字)

来源:m.ttfanwen.com时间:2016.4.18

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

更多类似范文
┣ ORACLE PLSQL知识点整理 1200字
┣ Oracle总结 10300字
┣ Oracle基础知识学习笔记(自己整理的) 13300字
┣ oracle plsql常用方法汇总 9200字
┣ 更多oracle知识点总结
┗ 搜索类似范文