一，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;