一,Oracle简介
Oracle Database,又名Oracle RDBMS,或简称Oracle。是甲骨文公司的一款关系数据库管理系统。它是在数据库领域一直处于领先地位的产品。可以说Oracle数据库系统是目前世界上流行的关系数据库管理系统,系统可移植性好、使用方便、功能强,适用于各类大、中、小、微机环境。它是一种高效率、可靠性好的、适应高吞吐量的数据库方案。
特点:
(1)支持多用户、大事务量的事务处理
(2)数据安全性和完整性控制
(3)支持分布式数据处理
(4)可移植性
二,Oracle的下载
官网地址:https://www.oracle.com/cn/database/technologies/oracle-database-software-downloads.html–11g
三,Oracle的体系结构
1.数据库:Oracle是一种关系型的数据库管理系统;
2.数据库实例:数据库实例其实就是用来访问和使用数据库的一块进程,它只存在于内存中,就像Java中new出来的实例对象一样。一个数据库可以有n个实例;
3.表空间:Oracle数据库是通过表空间来存储物理表的,一个数据库实例可以有N个表空间,一个表空间下可以有N张表,可以有N个数据文件;
4.数据文件:数据文件是数据库的物理存储单位。数据库的数据是存储再表空间中的,真正是在某一个或者多个数据文件中,而一个表空间可以由一个或多个数据文件组成,一个数据文件只能属于一个表空间,一旦数据文件被加入到某个表空间后,就不饿能删除这个文件,如果要删除某个数据文件,只能删除其所属与的表空间才行;
5.用户:Oracle数据库建好后,要想在数据库里建表,必须先为数据库建立用户,并为用户指定表空间;
四,Oracle的窗口化连接
既然是使用窗口化连接,那我们就需要一个窗口化连接的工具,这里我们使用的是代码补全提示比较好的PLSQL Developer。
需要进项配置才能使用
如果orcle是直接下载本地的,则这步不需要.
找到这个路径的文件tnsnames,修改下此文件
五,准备工作
–创建表空间
create table waterboss
datafile ‘c:\waterboss.dbf’
size 100m --大小
autoextend on–自动扩展
next 10m;
–创建用户
create user wateruser
identified by itcast
default tablespace waterboss;
–赋予dba权限 (可以用此用户登入PLSQL)
grant dba to wateruser
六,数据类型
1.字符型
1.1CHAR:固定长度的字符类型,最多存储2000个字节
1.2VARVCHAR2:可变长度的字符类型,最多4000个字节
1.3LONG:打文本类型,最大可存储2个G
2.数值型
NUMBER:数值类型
例如:NUMBER(5) 最大可以存的数为99999
NUMBER(5,2) 最大可以存的数为999.99–(5为总位数)
3.日期型
3.1DATE:日期时间型,精确到秒
3.2TIMESTAMP:精确到秒的小数点后9位
4.二进制(大数据类型)
4.1CLOB:存储字符,最大4G
4.2BLOB:存储图像、声音、视频等二进制数据,最多4个G
七,DDL语言
7.1创建表
create table 表名(
列名 列的类型 [列的约束],
列名 列的类型 [列的约束]
);
7.2复制表
create table 表名 as 查询语句;
7.3删除表
方式一:drop table 表名;
方式二:truncate table 表名;
7.4修改表
7.41添加一列
格式:alter table 表名 add 列名 列的类型;
演示:alter table users add phone varchar2(11);
7.42修改列名
格式:alter table 表名 rename column 旧列名 to 新列名;
演示:alter table users rename column phone to mobile;
7.43修改类型
格式:alter table 表名 modify 列名 列的类型;
演示:alter table users modify mobile char(11);
7.44删除一列
格式:alter table 表名 drop column 列名;
演示:alter table users drop column mobile;
7.45修改表名
格式:rename 旧表名 to 新表名;
演示:rename users to myusers;
八, DML语言
8.1插入语句
格式:insert into 表名(列名1,列名2,…) values(值1,值2,…);
演示:insert into category(cid,cname) values(1,‘电视’);
注意:commit;
8.2修改语句
格式:update 表名 set 列名1=值1,列名2=值2,… where 查询条件;
演示:update category set cname=‘汽车’ where cid = 1;
注意:commit;
8.3删除语句
格式:delete from 表名 where 查询条件;
演示:delete from category where cid = 1;
注意:commit;
truncate table 表名; --先摧毁表结构,在重构.不需要commit且无法回滚
九,数据的导出与导入
9.1整库的导出与导入
导出:exp system/itcast file=文件名 full=y(如不指定文件,默认导出为为EXPDAT.dmp)
导入:imp system/itcast full=y(如不指定文件,默认从EXPDAT.dmp文件导入)
9.2按用户导出与导入
导出:exp system/itcast owner=wateruser file=wateruser.dmp
导入:imp system/itcast file=wateruser.dmp fromuser=wateruser
9.3按表导出与导入
导出:exp wateruser/itcast file=a.dmp tables=t_account,a_rea
导入:imp wateruser/itcast file=a.dmp tables=t_account,a_rea
十,DQL查询
完整语法:
select [TOP|DISTINCT] [选择列表]|[*]
from 数据源
[where 查询条件]
[group by 分组条件]
[having 过滤条件]
[order by 排序条件 asc|desc nulls first|last];
10.1简单查询
并列查询
and/or and的优先级比or大,
范围查询
</> /between and
空值查询
is null/is not null
去重查询
distinct
例子:
----对addressid和ownertypeid两个字段并列去重
select distinct addressid,ownertypeid from t_owners;
10.2排序查询
升序:order by 字段 (asc);默认asc可省
降序:order by 字段 desc;
伪列rowid
select rowid,t.* from t_owners t --要给表起别名
伪列rownum
select rownum,t.* from t_owners t --要给表起别名
10.3聚合函数
求和 sum
平均 avg
最大最小 max/min
统计个数 count
分组聚合统计(select后一定是分组聚合的条件或者是聚合函数)
select areaid,sum(money) from t_account group by areaid
分组后条件查询 having
10.4连接查询
(1)内连接
隐式内连接:select * from emp e1, dept d1 where e1.deptno = d1.deptno;
显示内连接:select * from emp e1 inner join dept d1 on e1.deptno = d1.deptno;
(2)外连接
左外连接
隐式左外连接:select * from emp e1,dept d1 where e1.deptno = d1.deptno(+);
显示左外连接:select * from emp e1 left outer join dept d1 on e1.deptno = d1.deptno;
右外连接
隐式右外连接:select * from emp e1,dept d1 where e1.deptno(+) = d1.deptno;
显示右外连接:select * from emp e1 right outer join dept d1 on e1.deptno = d1.deptno;
全外连接:
select * from emp e1 full outer join dept d1 on e1.deptno = d1.deptno;
(3)交叉连接
隐式交叉连接:select * from emp, dept;
显示交叉连接:select * from emp e1 cross join dept d1;
10.5子查询
单行子查询:>、>=、<、<=、!=、<>、=、<=>
多行子查询:in、not in、any、some、all、exits
1、in的使用
--查询所有经理的信息
select * from emp where empno in (select mgr from emp where mgr is not null);
2、not in的使用
--查询不是经理的信息
select * from emp where empno not in (select mgr from emp where mgr is not null);
3、any的使用
--查询出比10号部门任意一个员工薪资高的员工信息
select * from emp where sal > any (select sal from emp where deptno = 10);
4、some的使用
--查询出比10号部门任意一个员工薪资高的员工信息
select * from emp where sal > some (select sal from emp where deptno = 10);
5、all的使用
--查询出比20号部门所有员工薪资高的员工信息
select * from emp where sal > all (select sal from emp where deptno = 20);
6、exits的使用
--查询有员工的部门的信息
select * from dept d1 where exists (select * from emp e1 where e1.deptno = d1.deptno);
7、from子查询
select * from (select * from emp e1 where e1.deptno = d1.deptno);
8、select子句中的子查询
select子句中的子查询必须为单行子查询
select id,name,(select name from t_address where id=addressid) addressname from t_owners;
10.6分页查询
简单分页
--rownum只能用<或<=,不能使用> 或>=
select rownum,t.* from t_account t where rownum<10;
或用子查询
--查询前10条员工的信息
--注意:Oracle中不支持limit,需要在原始表加上一列:行号,然后使用子查询来实现分页
select *
from (select rownum r,e.* from t_account t)
where r >=1 and r <= 10;
基于排序的分页
--两层子查询
select * from (select rownum r,t.*from (select * from T_account t order by usenum desc ) t) where r>=1 and r<10;
十一,函数大全
11.1字符函数
--dual 是伪表
--求字符串长度
select length('ABCD') from dual;
--求字符串子串
select substr('ABCD',2,2) from dual;
--求字符串拼接
select concat(concat('ABCD',EF'),'F') from dual;
select 'ABCD'||'E'||'F' from dual
11.2数值型函数
--四舍五入
select round (100.567,2) from dual
--数字截取
select trunc (100.567,2) from dual
11.3日期函数
--加月
select add_month(sysdate,2) from dual
--求当月最后一天
select last_day(sysdate) from dual
--日期截取
select trunc(sysdate) from dual--按日截取(把时间截掉)
select trunc(sysdate,'mm') from dual--按月截取(日默认为1)
11.4转换函数
--数字转字符串
select to_char(100) || '分' from dual;
--日期转字符串
select to_char(sysdate,'yyyy-mm-dd') from dual;
--字符串转日期
select to_date('2016-03-10','yyyy-mm-dd') from dual
--字符串转数字
select to_number('100')+ 10 from dual;
11.5其他函数
例子:
--空值处理
select nvl(null,0) from dual--返回0
select nvl(100,0) from dual--返回100 第一个值为null,返回第二个值,否则返回第一个值
--decode 条件判断
select decode(100,1,2,3,4,100,200) from dual;--返回200
select name,decode(ownertypeid,1,'居民',2,'行政',3,'商业') from t_owners;--根据id匹配信息
--case when then else end
select name,(case ownertypeid
when 1 then '居民'
when 2 then '行政'
when 3 then '商业'
else '其他'
end) from t_owners;
select name,(case
when ownertypeid=1 then '居民'
when ownertypeid=2 then '行政'
when ownertypeid=3 then '商业'
else '其他'
end) from t_owners;--可以修改=号,适用于复杂情况
11.6行列转换
--行列转换,月份本来是行的信息变成了列的信息
select (select name from t_area where id=areaid) 区域,
sum(case when month='01' then money else 0 end) 一月,
sum(case when month='02' then money else 0 end) 二月,
sum(case when month='03' then money else 0 end) 三月,
sum(case when month='04' then money else 0 end) 四月
from t_account where year ='2022' group by areaid;
select (select name from t_area where id=areaid) 区域,
--case when 条件 and 条件 then else end
sum(case when month>='01' and month<=03 then money else 0 end) 第一季度,
sum(case when month>='04' and month<=06 then money else 0 end) 第二季度,
sum(case when month>='07' and month<=09 then money else 0 end) 第三季度,
sum(case when month>='10' and month<=12 then money else 0 end) 第四季度
from t_account where year ='2022' group by areaid;
11.7分析函数
--分析函数
--值相同,排名相同,序号跳跃 rank
select rank() over(order by usenum desc) 排名,t.* from t_account t
--值相同,排名相同,序号连续 dense_rank
select dense_rank() over(order by usenum desc) 排名,t.* from t_account t
--序号连续,不管值是否相同 row_number
select row_number() over(order by usenum desc) 排名,t.* from t_account t
11.8集合运算
并集运算:union all(保留重复记录)
并集运算:union(去掉重复记录)
交集运算:intersect
差集运算:minus(注意前后顺序)
十二,Orcle对象-视图
12.1视图的定义和优点
视图是对查询结果的一个封装,视图里面所有的数据,都是来自于它查询的那张表,视图本身不存储任何数据,但是可以修改原数据,但是不建议这样使用
优点:
1.简化数据的操作
2.着重于特定数据
3.提供了一种简单的安全机制
4.提供向后兼容性
12.2语法
创建语法:
create [or replace] [force] view 视图名称
as 查询语句
[with check option]--检查约束
[with read only];--只读约束
删除视图语法:
drop VIEW 视图名称
12.3案例
--创建简单视图
creat view view_owners1 as
select *from t_owners where ownertypeid=1
--查询简单视图
select * from view_owners1 where addressid=1
--修改视图数据
update view owners1 set name='范小冰' where id=1;
commit;
select *from t_owners; --返回范的name为范小冰
--结论:视图和表数据一起变化
--带检测约束的视图
creat view view_adress2 as
select *from t_adress where areaid=2
with check option
--无法修改,该视图的条件是areaid=2
update view view_adress2 set areaid=3 where id=4
--只读约束的视图
creat or replace view view_owners1 as
select *from t_owners where ownertypeid=1
with read only; --不可修改了
--创建带错误的视图
create force view view_test as
select *from t_test--此表不存在,可以先建视图成功,后再建表
复杂视图
--复杂视图--多表关联
create or replace view view_owners ad
select ow.id 业主编号,ow.name 业主名称,ot.name 业主类型 from t_owners ow,t_ownertype ot
where ow.ownertypeid=ot.id;
--查询复杂视图
select * from view_owners
--修改复杂视图(多表关联)的数据
update view_owners set 业主名称='林玲玲' where 业主编号=4;--可修改
update view_owners set 业主类型='商业' where 业主编号=4;--不可修改
--结论:键保留表;把主键保留下来的那个表.只有键保留表的内容可以修改,其他不可修改
聚合统计的复杂视图
--聚合统计的复杂视图--不可修改
create view view_accountsum as
select year,month,sum(money) from t_account
group by year,month order by year,month
update view_accountsum set month='04' where year='2012' and month ='03';--不可修改
十三,物化视图(oracle独有)
13.1什么是物化视图
视图是一个虚拟表(可以认为是一条语句),基于它创建时指定的查询语句返回的结果集.每次访问它都会导致这个查询语句被执行一次.为了避免每次都执行这个查询.可以将这个查询结果存储到一个物化视图(也叫实体化视图).物化视图相当于一张真实的表,需要建立副本.
优点:提高效率 缺点:占用空间
13.2创建物化视图语法
CREATE METERIALIZED VIEW view_name
[BUILD IMMEDIATE | BUILD DEFERRED]
REFRESH [FAST|COMPLETE|FORCE] --刷新方式,默认是force自动选择
[
ON [COMMIT|DEMAND] |START WITH (start_time) NEXT (next_time)
]
AS 查询语句
--build immediate 立刻(默认)
--build deferred 延期
--complete 完全刷新
--fast 增量更新
--force 自动选择(默认)
--on commit 在基表做提交操作是刷新物化视图,自动刷新
--on demand 手动刷新(默认)
删除视图
drop meterizlized view 物化视图名称
13.3案例
手动刷新的物化视图
--创建手动刷新的物化视图
create meterialized view mv_address1 as
select ad.id,ad.name,ar.name arname from t_address ad,t_area ar
where ad.areaid=ar.id;
--查询物化视图
select* from mv_address1;
--执行下列语句进行刷新
begin
DBMS_MVIEW.refresh('mv_address1','c');
end;
--结论 默认手动刷新 如过基表新增数据 需要执行刷新sql刷新数据
创建自动刷新的物化视图
--创建自动刷新的物化视图
create meterialized view mv_address2
refresh
on commit
as
select ad.id,ad.name,ar.name arname from t_address ad,t_area ar
where ad.areaid=ar.id;
--基表新增数据,视图实时可以查到
创建时延期生成数据的物化视图
create meterialized view mv_address3
build deferred
refresh
on commit
as
select ad.id,ad.name,ar.name arname from t_address ad,t_area ar
where ad.areaid=ar.id;
--查询视图
select * from mv_address3; --无数剧
--必须手动执行刷新,执行后变自动
begin
DBMS_MVIEW.refresh('mv_address3','c');
end;
创建增量刷新的物化视图
--创建增量刷新的物化视图
--前提1 是必须创建物化视图日志;记录哪些基表发生哪些变化,用这些记录去更新物化视图
create materialized view log on t address with rowid
--前提2 创建物化视图的语句中,必须有基表的rowid
create meterialized view mv_address4
refresh fast
as
select ad.rowid adrowid,ad.id,ad.name,ar.rowid arriwid,ar.name arname from t_address ad,t_area ar
where ad.areaid=ar.id;
十四,序列(oracle独有)
14.1什么是序列
序列是Oracle提供的用意产生一系列唯一数字的数据库对象
14.2创建或使用简单序列
创建序列语法:
create sequence 序列名称;
14.3案例
创建简单序列
--创建简单序列
create sequence seq_test;
--查询序列的下一个值
select seq_test.nextval from dual;
--查询序列的当前值
select seq_test.currval from dual;
创建复杂序列语法:
CREATE SEQUENCE 序列名称
[increment by n]--递增的序列值是n,如果n是正数就递增,如果是负数就递减,默认是1
[start with]--开始得值,递增默认是minvalue 递减是maxvalue
[maxvalue|nomaxvalue]--最大值
[minvalue|nominvalue]--最小值
[cycle|nocycle]--循环/不循环
[cache n|nocache]--分配并存入内存中
--
create sequence seq_test3
increment by 10
start with 10
minvalue 5
maxvalue 100
cycle
十五,同义词(oracle独有)
15.1含义
同义词就是别名的意思和视图的功能类似,就是一种映射关系.相当于表的别名
15.2语法
--创建--不加public是私有,仅此客户可查
create [public] synonym 同义词名称 for 对象的名称;
--修改
create or replace [public] synonym 同义词名称 for 对象的名称;
--删除
drop [public] synonym 同义词名称;
十六,索引
16.1什么是索引
索引相当于是一本书的目录,能够提高我们的查询效率
16.2普通索引
create index 索引名 on 表名(列名);
--性能测试 创建一个表
create table t_indextest(id number,name varchar(30));
--向表中插入100万条记录
begin
for x in 1..1000000
loop
insert into t_indextest value (x,'AA'||x);
end loop;
commit;
end;
--创建索引
create index index_test on t_indextest(name);
select * from t_indextest where id=765432;
16.3唯一索引
--创建唯一索引,此列数据唯一,相当于加了个唯一约束
create unique index 索引名 on 表名(列名);
16.4复合索引
--复合索引 列不止一列
create index 索引名 on 表名(列名1,列名2,...);
16.5反向键索引
应用场景:
当某个字段的值为连续增长的值,如果构建标准索引,会形成歪脖子树,这样会增加查询的层数,性能会下降.简历反向键索引,可以使索引的值变的不规则,从而使索引树均匀分布.
--反向键索引
create index 索引名称 on 表名(列名) reverse;
16.6位图索引
使用场景:位图索引适合创建在低基数列上
优点:减少响应时间
--位图索引,不能做范围查询,只能用=号条件查询
create bitmap index 索引名称 on 表名(列名) reverse;
十七,PLSQL
17.1什么是PLSQL
PL/SQL(Procedure Language)是Oracle对sql语言的过程化扩展,指在SQL命令语言中增加了过程处理语句(如分支、循环等),使SQL语言具有过程处理能力.把SQL语言的数据操纵能力与过程语言的数据处理能力结合其来,使得PLSQL面向过程但比过程语言简单、高效、灵活和使用.
基本语法:
declare
--声明变量
begin
--业务逻辑
[exception
--异常处理
]
end;
17.2变量
声明变量的语法:
变量名 类型(长度);
变量赋值的语法:
变量名:=变量值;
--select into 从数据库中查询
declare
v_price number(10,2);--单价
v_usenum number;--水费字数
v_usenum2 number(10,2);--吨数
v-money number(10,2);--金额
v_num0 number;--上月
v_num1 number;--本月水表数
begin
v_price:=2.45;--单价
select usenum into v_usenum from t_account
where year ='2022' and month='01' and owneruuid=1;
v_usenum2 :=round(v_usenum/1000,2);--吨数
v-money :=v_price*v_usenum2;--金额
DBMS_OUTPUT.put_line('金额:'||v-money);
end;
17.3属性类型
作用:引用某表某列的字段类型.
--属性类型(引用型 表名.列名%type)
declare
v_price number(10,2);--单价
v_usenum t_account.usenum%type ;--水费字数
v_usenum2 number(10,2);--吨数
v-money number(10,2);--金额
v_num0 t_account.num0%type;--上月
v_num1 t_account.num1%type;--本月水表数
begin
v_price:=2.45;--单价
select usenum into v_usenum from t_account
where year ='2022' and month='01' and owneruuid=1;
v_usenum2 :=round(v_usenum/1000,2);--吨数
v-money :=v_price*v_usenum2;--金额
DBMS_OUTPUT.put_line('金额:'||v-money);
end;
--属性类型(记录型 表名%rowtype)
declare
v_price number(10,2);--单价
v_usenum t_account.usenum%type ;--水费字数
v_usenum2 number(10,2);--吨数
v_money number(10,2);--金额
v_account t_account%rowtype;--台账行记录类型
begin
v_price:=2.45;--单价
select * into v_account from t_account
where year ='2022' and month='01' and owneruuid=1;
v_usenum2 :=round(v_account.usenum/1000,2);--吨数
v-money :=v_price*v_usenum2;--金额
DBMS_OUTPUT.put_line('字数:'|| v_account.usenum '金额:'||v-money);
end;
17.4异常
异常有两种类型
预定义异常和用户定义异常
预定义异常种类:
语法结构:
expection
when 异常类型 then
异常处理逻辑
案例:
expection
when no_data_found then
DBMS_OUTPUT.put_line('没有找到账务数据')
17.5条件判断
--语法1
if 条件1 then
业务逻辑
end if;
--语法2
if 条件1 then
业务逻辑
else
业务逻辑
end if;
--语法3
if 条件1 then
业务逻辑
elsif 条件2 then
业务逻辑
else
业务逻辑
end if;
17.6循环
1.无条件循环
语法:
loop
业务逻辑
end loop
2.有条件循环
语法:
while 条件
loop
业务逻辑
end loop
3.for循环
语法:
for 变量 in [reverse] 起始值..结束值
loop
业务逻辑
end loop
17.7游标
1什么是游标
游标是用来操作查询结果集,相当于是JDBC中ResultSet,它可以对查询的结果一行一行的获取.
2语法结构及案例
语法:
--声明游标
cursor 游标名[(参数 参数类型)] is 查询语句;
--使用游标
open 游标名[(参数 参数类型)];
loop
fetch 游标名 into 变量;
exit when 游标名称%notfound
end loop;
--关闭游标
close 游标名;
3案例
不带参数的游标
---不带参数的游标
select * from t_pricetable where ownertypeid=1
--游标 输出结果集
declare
cursor cur_pricetable is select * from t_pricetable where ownertypeid=1;--声明游标
v_pricetable t_pricetable%rowtype;
begin
open cur_pricetable;--打开游标
loop
fetch cur_pricetable into v_pricetable;--提取游标
exit when cur_pricetable%notfound;--退出循环游标
DBMS_OUTPUT.put_line('价格:'||v_pricetable.price||'吨位:'v_pricetable.minnum||'-'||v_pricetable.maxnum);
end loop
close cur_pricetable; --关闭游标
end;
带参数的游标
---带参数的游标
declare
cursor cur_pricetable(v_ownertype number) is select * from t_pricetable where ownertypeid=v_ownertype;--声明游标
v_pricetable t_pricetable%rowtype;
begin
open cur_pricetable(1);--打开游标()内为参数
loop
fetch cur_pricetable into v_pricetable;--提取游标
exit when cur_pricetable%notfound;--退出循环游标
DBMS_OUTPUT.put_line('价格:'||v_pricetable.price||'吨位:'v_pricetable.minnum||'-'||v_pricetable.maxnum);
end loop
close cur_pricetable; --关闭游标
end;
for循环
---for循环 带参数的游标
declare
cursor cur_pricetable(v_ownertype number) is select * from t_pricetable where ownertypeid=v_ownertype;--声明游标
-- v_pricetable t_pricetable%rowtype;
begin
for v_pricetable in cur_pricetable(2)
loop
DBMS_OUTPUT.put_line('价格:'||v_pricetable.price||'吨位:'v_pricetable.minnum||'-'||v_pricetable.maxnum);
end loop
close cur_pricetable; --关闭游标
end;
十八,存储函数
18.1什么是存储函数
存储函数又称自定义函数.可以接受参数,返回结果
18.2语法结构
create function 函数名称
(参数名 in|out 参数类型,参数名 in|out 参数类型,...)
return 返回的参数类型
is|as
--声明部分
begin
--业务逻辑
return 结果变量;
[exception
异常处理部分]
end;
18.3案例:
--存储函数的创建
create or replace function fn_getaddress
(v_id number)
return varchar2
is
v_name varchar2(30);
begin
--查询地址表
select name into v_name from t_address where id=v_id;
return v_name;
end;
--查询测试结果
select fn_getaddress(3) from dual;
十九,存储过程
19.1什么是存储过程
存储过程实际上是封装在服务器上一段PLSQL代码片断,它已经编译好了,如果客户端调用存储过程,执行效率就会非常高效.
存储过程与存储函数的区别:
1.存储函数中有返回值,且必须返回;而存储过程没有返回值,可以通过传出参数返回多个值.
2.存储函数可以在select语句中直接使用,而存储过程不能.过程多数是被应用程序所调用.
3.存储函数一般都是封装一个查询结果,而存储过程一般都封装一段事务代码.
19.2存储过程语法结构
--创建或修改语法
create [or replace] procedure 存储过程名称
(参数名 in|out 参数类型,参数名 in|out 参数类型,...) --不写默认为in
is|as
--声明部分
begin
--业务逻辑
[expection
异常处理]
end;
--删除语法
drop procedure 存储过程名称;
19.3案例
1.创建不带传出参数的存储过程
--创建不带传出参数的存储过程
create sequence seq_owners start with 11;--业主序列
create or replace procedure pro_owners_add
(v_name varchar2,--名称
v_addressid number,--地址编号
v_housenumber varchar2,--门牌号
v_watermeter varchar2,--水表号
v_ownertypeid number--业主类型
)
is
begin
insert into t_owners values(seq_owners.nextval,v_name,v_addressid,v_housenumber,v_watermeter,sysdate,v_ownertypeid)
commit;
end
--调用不带传出参数的存储过程
--方法1
call pro_owners_add('hhh','1','2','3','1');
--方法2
begin
pro_owners_add('hhh','1','2','3','1');
end;
2.JDBC调用不带传出参数的存储过程
3.
--创建带传出参数的存储过程
create sequence seq_owners start with 11;--业主序列
create or replace procedure pro_owners_add
(v_name varchar2,--名称
v_addressid number,--地址编号
v_housenumber varchar2,--门牌号
v_watermeter varchar2,--水表号
v_ownertypeid number,--业主类型
v_id out number
)
is
begin
--对传出参数赋值
select seq_owners.nextval into v_id from dual;
--新增业主
insert into t_owners values(seq_owners.nextval,v_name,v_addressid,v_housenumber,v_watermeter,sysdate,v_ownertypeid)
commit;
end
--调用带传出参数的存储过程--只能用begin end
declare
v_id number;
begin
pro_owners_add('hhh2','1','2','3','1',v_id);
dbms_output.put_line(v_id)
end;
4.JDBC调用带传出参数的存储过程
二十,触发器
20.1什么是触发器
当用户执行了 insert | update | delete 这些操作之后,可以触发一系列其它的动作、业务逻辑,使用触发器可以协助应用在数据库端确保数据的完整性、日志记录 、数据校验等操作。使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容,这与其他的数据库是相似的。现在Oracle触发器不仅支持行级触发,还支持语句级触发.
用于:
1.数据确认
2.实施复杂的安全性检查
3.做审计
4.数据的备份和同步
触发器的分类:
1.前置触发器
2.后置触发器
或者分为行级和语句级
20.2创建触发器的语法
create trigger 触发器名称
before|after
insert|[or]update|[or]delete [of 列名]
on 表名称
[for each row]--行级触发器
[when {条件}]--条件
declare
--声明部分
begin
--业务逻辑
end;
--删除触发器
drop tigger 触发器名称;
在触发器中触发语句与伪记录变量的值
触发语句 (行级才有语句级没有)
触发语句 | :old | :new |
---|---|---|
insert | 所有字段都是空(null) | 将要插入的数据 |
update | 更新以前该行的值 | 更新后的值 |
delete | 删除以前该行的值 | 所有字段都是空(null) |
20.3案例
--前置触发器
create or replace tigger tri_account_num1
before
update of num1
on t_account
for each row
declare
begin
--通过伪记录变量修改usenum字段的值
:new.usenum:=new.num1=:new.num2;
end
--后置触发器
--创建日志表,记录业主名称修改前和修改后的名称
create table t_owners_log(
updatetime date,
ownerid number,
oldname varchar2(30),
newname varchar2(30)
);
create or replace tigger tri_owners_log
after
update of name
on t_owners
for each row
declare
begin
--向日志表插入记录
insert into t_owners_log values(sysdate,:new.id,:old.name,:new.name)
:new.usenum:=new.num1=:new.num2;
end
update t_owners set name='林霖霖'where id=4;