Oracle基本搭建以及使用

安装

背景

在Linux中安装oracle非常麻烦，相信每个人也会遇到各种坑。为了一次装好，也方便将来直接可以导出镜像在各平台移植使用，所以选择用docker安装

OracleLinux

Oracle Linux，全称为Oracle Enterprise Linux，简称OEL，是Oracle公司在2006年初发布第一个版本，Linux发行版本之一，以对Oracle软件和硬件支持较好见长。

拉取镜像

在 DockerHub 上搜索 Oracle 可以找到 Oracle 的官方镜像，地址：https://hub.docker.com/

## 注意，这里使用 
docker pull oraclelinux 
##命令，是拉取不到镜像的，因为它没有 latest 标签。因此，只能手动指定它的版本号
##例如 
docker pull oraclelinux

用官方镜像下载速度非常慢，因此，我们可以使用阿里镜像 docker pull registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g

在 dockerHub 上可以搜到 (第二个)，可以参考这个配置

1
2
3

## 使用 
docker images 
## 查看镜像，确认已经下载完成

启动容器

默认启动容器的方式

1 2	`docker run -d -it -p 1521:1521 --name oracle11g --restart=always registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g`

持久化启动的方式

1	`docker run -d -it -p 1521:1521 --name oracle --restart=always --mount source=oracle_vol,target=/home/oracle/app/oracle/oradata registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g`

查看启动的线程

1	`netstat -tulnp`

查看运行状态

1	`docker ps -a`

容器内环境配置

## 进入容器 
docker exec -it oracle11g bash

## 切换到 root 用户 
su root，
## 密码为 
helowin

/** docker容器配置环境变量不是在 
/etc/profile 
中，容器启动不会走这个文件。
可以将环境变量的配置设置在
 /home/oracle/.bashrc 
文件下，这样可以省略掉软连接的创建 **/
ln -s $ORACLE_HOME/bin/sqlplus /usr/bin

编辑环境变量

1	`vi /home/oracle/.bashrc，`

在文件最后加入以下命令

export ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_2
export ORACLE_SID=helowin
export PATH=$ORACLE_HOME/bin:$PATH

wq 保存并退出。然后使用 source /home/oracle/.bashrc 刷新环境变量，并使之生效

进入 oracle 命令行

使用 sqlplus /nolog 进入oracle命令行

使用 “操作系统认证” 的方式，登录oracle

conn / as sysdba

如果直接使用默认的 root 用户登录，会报登录失败。这里必须使用

su - oracle 命令，将当前用户切换到 oracle，然后在执行登录命令

ORA-12514, TNS:listener does not currently know of service requested in connect descriptor

这个错误是由于数据库名用错了

su - oracle
sqlplus /nolog
conn / as sysdba
select instance_name from v$instance;
show user;

使用上述命令查出来的，就是所有可用的 “数据库名” 和 “用户名”

阿里的这个镜像，所有的密码都是统一的

helowin

system用户具有DBA权限，但是没有SYSDBA权限。平常一般用该帐号管理数据库。

而sys用户是Oracle数据库中权限最高的帐号，具有“SYSDBA”和“SYSOPER”权限，一般不允许从外部登录

配置防火墙

防火墙要允许 1521 端口，外部的数据库管理工具才能连的上

# 打开防火墙
systemctl start firewalld
# 查询端口状态
firewall-cmd --query-port=1521/tcp
# 永久性开放端口
firewall-cmd --permanent --zone=public --add-port=1521/tcp
# 重启防火墙
firewall-cmd --reload
firewall-cmd --query-port=1521/tcp

使用

一、创建表空间

1、Oracle表空间分类

永久表空间

表空间是数据库的逻辑划分，一个表空间只能属于一个数据库。所有的数据库对象都存放在指定的表空间中，但主要存放的是表，所以称作表空间。

临时表空间

主要用来做查询和存放一些缓冲区数据。临时表空间消耗的主要原因是需要对查询的中间结果进行排序。重启数据库可以释放临时表空间。

2、表空间创建

创建语句

1
2
3


## 先创建文件夹，再创建表空间  设定表空间名、对应文件、初始大小、递增大小。
create tablespace BGTEST datafile '/home/oracle/oradata/ORCL/BGTEST.DBF' size 512M autoextend on next 24M permanent online;

二、创建用户

1、用户权限分类

对象权限（Object privileges）
对象权限是指在指定的表、视图、序列上制定执行动作的权限
角色权限（Pole privileges）

角色是可以授权用户的相关权限的组，该方法时权限的授予撤回更加容易管理

系统权限（System privileges）

为用户分配创建表、创建用户、创建视图、创建存储过程的权限

2、创建用户分配权限

create user BGTEST identified by 123456 default tablespace BGTEST;
-- 直接赋予dba权限，或者自定义权限
grant dba to BGTEST;

其他的一些用户创建过程：


1.首先，创建（新）用户：
    create user username identified by password;
    username：新用户名的用户名
    password: 新用户的密码
也可以不创建新用户，而仍然用以前的用户，如：继续利用scott用户
 
2.创建表空间：
    create tablespace tablespacename datafile 'd:\data.dbf' size xxxm;
    tablespacename：表空间的名字
    d:\data.dbf'：表空间的存储位置
    xxx表空间的大小，m单位为兆(M)
3.将空间分配给用户：
   alert user username default tablespace tablespacename;
   将名字为tablespacename的表空间分配给username
 
4.给用户授权：
   grant create session,create table,unlimited tablespace to username;
 
5.然后再以楼主自己创建的用户登录，登录之后创建表即可。
conn username/password;6.查看服务名env |grep SID 7.授予dba权限grant dba to username7.使用上面的用户名、密码、sid登录plsql
 
 
每步执行的sql：（sjzx是数据库名、用户名、密码、表空间名）
（1）create user sjzx identified by sjzx
（2）create tablespace sjzx datafile 'D:\db\app\oradata\orcl\sjzx.dbf'
        size 100m
        autoextend on next 32m maxsize 2048m
 
（3）alter user sjzx default tablespace sjzx
 
（4）grant create session,create table,unlimited tablespace to sjzx
 
  
 
1.创建用户
create user user_name identified by "user_password"
default tablespace tbs_name
temporary tablespace temp profile DEFAULT;
 
2.授权
grant connect to user_name;
grant create indextype to user_name;
grant create job to user_name;
grant create materialized view to user_name;
grant create procedure to user_name;
grant create public synonym to user_name;
grant create sequence to user_name;
grant create session to user_name;
grant create table to user_name;
grant create trigger to user_name;
grant create type to user_name;
grant create view to user_name;
grant unlimited tablespace to user_name;
alter user user_name quota unlimited on tbs_name;

在oracle命令行中，查看oracle表空间数据文件位置
select t1.name,t2.name   from v$tablespace t1,v$datafile t2 where t1.ts# = t2.ts#;

三、ORACLE 数据库：表的创建、修改与删除、数据增删改

1、创建表：

CREATE TABLE 表名称(

字段名类型(长度) primary key,

字段名类型(长度),

…….

);

CREATE TABLE stu(
    id number primary key,
    name char(30),
    age number
);

2、修改表

①增加字段语法:

ALTER TABLE 表名称 ADD(列名 1 类型 [DEFAULT 默认值]，列名 1 类型

[DEFAULT 默认值]…)

1
2
3

ALTER TABLE stu ADD(
    sex char(30)
);

②修改字段语法：

ALTER TABLE 表名称 MODIFY(列名 1 类型 [DEFAULT 默认值]，列名 1 类型

[DEFAULT 默认值]…)

修改字段的类型
ALTER TABLE stu MODIFY(
    sex varchar2(30)
);

③.修改字段名语法：

ALTER TABLE 表名称 RENAME COLUMN 原列名 TO 新列名

1	`ALTER TABLE stu RENAME COLUMN sex TO gender;`

④删除字段名

ALTER TABLE 表名称 DROP COLUMN 列名

1	`ALTER TABLE stu DROP COLUMN gender;`

3、删除表

DROP TABLE 表名称

1	`DROP TABLE stu`

4、数据增删改

执行 ‘增删改’ 后要再执行 commit 提交事务。

1、插入数据

INSERT INTO 表名[(列名 1，列名 2，…)]VALUES(值 1，值 2，…)

insert into stu values(1,'张三',18,'男');
insert into stu values(2,'李四',30,'男');
insert into stu(id,name,sex) values(3,'王五','男');
 
commit;    --提交事务

2、修改数据

UPDATE 表名 SET 列名 1=值 1，列名 2=值 2，….WHERE 修改条件；

1
2
3

update stu set name='张三三' where id=1;
 
commit;

3、删除数据

DELETE FROM 表名 WHERE 删除条件;

1
2
3

delete from stu where id=1;
 
commit;

5、单表查询

1、简单条件查询

①精确查询

1	`select * from stu where id=1;`

②模糊查询

1	`select * from stu where name like '%张%';`

③and 运算符

1	`select * from stu where name like '%张%' and id=1;`

④or 运算符

1	`select * from stu where name like '%张%' or age=30;`

⑤范围查询

1
2
3

select * from stu where age>=10 and age<=20;
 
select * from stu where age between 10 and 20;

⑥空值查询

select  * from stu where age is null;
 
 
select  * from stu where age is not  null;

2、去掉重复记录

①不重复显示姓名

1	`select distinct name from stu;`

3、排序查询

①升序排序

1	`select * from stu order by age;`

②降序排序

1	`select * from stu order by age desc;`

4、基于伪列的查询

在 Oracle 的表的使用过程中，实际表中还有一些附加的列，称为伪列。

伪列就像表中的列一样，但是在表中并不存储。伪列只能查询，不能进行增删改操作。

① ROWID 返回的是该行的物理地址

1	`select rowid,s.* from stu s;`

②ROWNUM 为结果集中每一行标识一个行号

1	`select rownum,s.* from stu s;`

5、聚合统计

①求和 sum

1	`select sum(age) from stu;`

②求平均 avg

1	`select avg(age) from stu;`

③求最大值 max

1	`select max(age) from stu;`

④求最小值 min

1	`select min(age) from stu;`

⑤统计记录个数 count

1	`select count(*) from stu;`

⑥分组聚合 Group by

1 2	`--按性别分组统计年纪 select sex,sum(age) from stu group by sex;`

四、分页查询

1、简单分页

--显示前5条记录
select rownum,t.*   from stu s where rownum<=5;
 
 
--显示第6条到第10条的记录
select * from (select rownum r,t.*   from stu s where rownum<=10) r>5;

2、基于排序的分页

1
2
3

select * from 
(select rownum r,s.* from (select * from stu order by age desc) s where rownum>=10 ) 
where r>5 ;

五、单行函数

1、字符函数

①求字符串长度 LENGTH

1	`select length('1234') from dual;`

②求字符串的子串 SUBSTR

1 2	`--从第二位开始截取两个字符 select substr('1234',2,2) from dual;`

③字符串拼接 CONCAT

1
2
3

select concat('1234','abc') from dual;
 
select '1234' || 'abc' from dual;

2、数值函数

①四舍五入函数 ROUND

1	`select round(99.123) from dual;`

②截取函数 TRUNC

1 2	`--截取两位小数 select trunc(99.123,2) from dual;`

③取模 MOD

1	`select mod(10,2) from dual;`

3、日期函数

①系统时间 sysdate

1 2	`3、日期函数 ①系统时间 sysdate`

②加月函数 ADD_MONTHS ：在当前日期基础上加指定的月

1	`select add_month(sysdate,5) from dual;`

③ 求所在月最后一天 LAST_DAY

1	`select last_day(sysdate) from dual;`

④日期截取 TRUNC

--截取年
select trunc(sysdate,'yyyy') from dual;
 
--截取月
select trunc(sysdate,'mm') from dual;

4、转换函数

①数字转字符串 TO_CHAR

1	`select to_char(123) from dual;`

② 日期转字符串 TO_CHAR

1	`select to_char(sysdate,'yyyy-mm-dd hh-mi-ss') from dual;`

③ 字符串转日期 TO_DATE

1	`select to_date('2022-10-01','yyyy-mm-dd') from dual;`

④字符串转数字 TO_NUMBER

1	`select to_number('1234') from dual;`

5、其它函数

①空值处理函数 NVL

NVL（检测的值，如果为 null 的值）；

1	`select nvl(null,0) from dual;`

②空值处理函数 NVL2

NVL2（检测的值，如果不为 null 的值，如果为 null 的值）；

1	`select nvl('男','woman','man') from stu;`

③条件取值 decode

decode(条件,值 1,翻译值 1,值 2,翻译值 2,…值 n,翻译值 n,缺省值)

【功能】根据条件返回相应值

select name,decode(sex,'男','man','女','woman','未知') from stu;
 
 
select naem ,(case sex
                when '男' then 'man'
                when '女' then 'woman'
                else '未知'
            end) from stu;
 
 
select naem ,(case
                when  sex='男' then 'man'
                when  sex='女' then 'woman'
                else '未知'
            end) from stu;

六、分析函数

① RANK 相同的值排名相同，排名跳跃

1 2	`--按年纪进行排序，相同的值排名相同，排名跳跃 select rank() over(order by age desc),name from stu;`

②DENSE_RANK 相同的值排名相同，排名连续

1 2	`--按年纪进行排序，相同的值排名相同，排名连续 select dense_rank() over(order by age desc),name from stu;`

③ ROW_NUMBER 返回连续的排名，无论值是否相等


--按年纪进行排序，返回连续的排名，无论值是否相等
select row_number() over(order by age desc),name from stu;
 
--分页查询 第6条到第10条记录
select * from 
(select row_number() over(order by age desc) rownumber,name from stu) 
where rownumber>5 and rownumber<=10;

七、集合运算

①UNION ALL(并集)，返回各个查询的所有记录，包括重复记录。

1
2
3

select * from stu where id<=10
union all
select * from stu where id>5

②UNION(并集)，返回各个查询的所有记录，不包括重复记录。

1
2
3

select * from stu where id<=10
union 
select * from stu where id>5

③INTERSECT(交集)，返回两个查询共有的记录。

1
2
3

select * from stu where id<=10
intersect
select * from stu where id>5

④MINUS(差集)，返回第一个查询检索出的记录减去第二个查询检索出的记录之后剩余的记录。

1
2
3

select * from stu where id<=10
minus
select * from stu where id>5

八、视图

视图是一种数据库对象，是从一个或者多个数据表或视图中导出的虚表，视图所对应的数据并不真正地存储在视图中，而是存储在所引用的数据表中，视图的结构和数据是对数据表进行查询的结果

更改了视图的数据，表的数据也会变化。

①创建或修改视图

语法：

/**

CREATE [OR REPLACE] [FORCE] VIEW view_nameAS subquery[WITH CHECK OPTION ][WITH READ ONLY]
OR REPLACE ：若所创建的试图已经存在，ORACLE 自动重建该视图；
FORCE ：不管基表是否存在 ORACLE 都会自动创建该视图；
subquery ：一条完整的 SELECT 语句，可以在该语句中定义别名；
WITH CHECK OPTION ：插入或修改的数据行必须满足视图定义的约束；
WITH READ ONLY ：该视图上不能进行任何 DML 操作。

**/

--创建视图 :学生性别为男
create or replace view view_stu 
as select * from stu where sex='男';
 
--使用
select * from view_stu ;
 
--更新一条语句
update view_stu set name='张三三' where id=1;

②删除视图

DROP VIEW view_name

九、物化视图

物化视图与普通的视图相比的区别是物化视图是建立的副本，它类似于一张表，需要占用存储空间。而对一个物化视图查询的执行效率与查询一个表是一样的。

①创建物化视图

语法：

CREATE METERIALIZED VIEW view_name
[BUILD IMMEDIATE | BUILD DEFERRED ]
REFRESH [FAST|COMPLETE|FORCE]
[
ON [COMMIT |DEMAND ] | START WITH (start_time) NEXT
(next_time)
]
AS
subquery

BUILD IMMEDIATE 是在创建物化视图的时候就生成数据
BUILD DEFERRED 则在创建时不生成数据，以后根据需要再生成数据。

默认为 BUILD IMMEDIATE。
刷新（REFRESH）：指当基表发生了 DML 操作后，物化视图何时采用哪种方式和基表进行同步。
REFRESH 后跟着指定的刷新方法有三种：FAST、COMPLETE、FORCE。

FAST刷新采用增量刷新，只刷新自上次刷新以后进行的修改。

COMPLETE 刷新对整个物化视图进行完全的刷新。

如果选择 FORCE 方式，则 Oracle 在刷新时会去判断是否可以进行快速刷新，如果可以则采用 FAST 方式，否则采用 COMPLETE的方式。

FORCE 是默认的方式。


刷新的模式有两种：ON DEMAND 和 ON COMMIT。ON DEMAND 指需要手动刷新物化视图（默认）。

ON COMMIT 指在基表发生 COMMIT 操作时自动刷新。


--1、创建物化视图
create materialized view mv_stu
as
select * from stu ;
 
--插入一条数据
insert into mv_stu values(4,'赵六',18,'女');
 
--手动刷新物化视图
begin
dbms_mview.refresh('mv_stu','c')
end;
 
 
 
 
 
 
--2、创建自动刷新的物化视图
create materialized view mv_stu2
refresh
on commit
as
select * from stu;
 
 
 
 
 
--3、创建时不生成数据的物化视图
create materialized view mv_stu3
build deferred
refresh
on commit
as
select * from stu ;
 
--执行下列语句生成数据
begin
dbms_mview.refresh('mv_stu3','c')
end;
 
 
 
 
--4、创建增量刷新的物化视图
--首先创建物化视图日志
create materialized view log on stu with rowid;
--创建物化视图
create materialized view mv_stu4
refresh
on commit
as
select s.rowid rowid,s.* from stu s;

十、序列

①创建与使用简单序列

create sequence 序列名称

提取下一个值：select 序列名称.nextval from dual

提取当前值：select 序列名称.currval from dual

②创建复杂序列

CREATE SEQUENCE sequence //创建序列名称
[INCREMENT BY n] //递增的序列值是n 如果n是正数就递增,如果是负数就递减 默认是 1
[START WITH n] //开始的值,递增默认是 minvalue 递减是 maxvalue
[{MAXVALUE n | NOMAXVALUE}] //最大值
[{MINVALUE n | NOMINVALUE}] //最小值
[{CYCLE | NOCYCLE}] //循环/不循环
[{CACHE n | NOCACHE}];//分配并存入到内存中

注意：

开始值不能小于最小值。

循环的序列，第一次循环是从开始值开始循环，而第二次循环是从最小值开始循环。

循环的序列，必须指定最大值。

缓存设置的数必须小于每次循环的数。

create sequence seq_test1
increment by 10
start with 10
maxvalue 210
minvalue 5
cycle 
cache 20; -- 增长值是 10 ,20 次缓存提取出的数是 200

③修改和删除序列

使用 ALTER SEQUENCE 语句修改序列，不能更改序列的 START WITH 参数

修改：ALTER SEQUENCE 序列名称 MAXVALUE 5000 CYCLE;

删除：DROP SEQUENCE 序列名称;

十一、同义词

同义词实质上是指定方案对象的一个别名。通过屏蔽对象的名称和所有者以及对分布式数据库的远程对象提供位置透明性，同义词可以提供一定程度的安全性。同时，同义词的易用性较好，降低了数据库用户的 SQL 语句复杂度。

创建与使用同义词

create [public] SYNONYM synooym for object;
 synonym 表示要创建的同义词的名称。
object 表示表，视图，序列等，要创建同义词的对象的名称。


--1、私有同义词:只允许特定用户或者有基对象访问权限的用户进行访问。
create synonym syn_stu for stu;
 
 
--2、公有同义词:所有用户都能访问
create public synonym syn_stu2 for stu;

十二、索引

索引是用于加速数据存取的数据对象。合理的使用索引可以大大降低 i/o 次数,从而提高数据访问性能。


①create index 索引名称 on 表名(列名);
--根据 name 列创建索引
create index index_stu_name on stu(name);

②唯一索引：create unique index 索引名称 on 表名(列名);

③复合索引：create index 索引名称 on 表名(列名,列名.....);

④反向键索引：create index 索引名称 on 表名(列名) reverse;

⑤位图索引：create bitmap index 索引名称 on 表名(列名);

十三、PL/SQL

PL/SQL（Procedure Language/SQL）是 Oracle 对 sql 语言的过程化扩展，指在 SQL 命令语言中增加了过程处理语句（如分支、循环等），使 SQL 语言具有过程处理能力。

①语法：

[declare
-- 声明变量
]
begin
-- 代码逻辑
[exception
-- 异常处理
]
end;

声明变量的语法：变量名类型（长度）;

变量赋值的语法：变量名:=变量值;

declare
price number;
sum number;
 
begin
price:=9.9;
sum:=price*10;
dbms_output.put_line('总价'||sum);
 
end;

②Select into 方式赋值：select 列名 into 变量名 from 表名 where 条件;

③属性类型

%TYPE 引用型：引用某表某列的字段类型

%ROWTYPE 记录型 : 标识某个表的行记录类型

declare
 
name stu.name%type;
 
begin
 
name:='张三';
 
dbms_output.put_line('姓名：'||name);
 
end;
 
 
--------------------
declare
 
t_stu stu%rowtype;
 
begin
 
select * into t_stu from stu;--赋值
 
end;

④异常

1
2
3

exception
    when 异常类型 then
        异常处理逻辑

declare
 
t_name varchar2(30);
 
begin
 
t_name:='张三';
select * from stu where name=t_name;
 
exception
when NO_DATA_FOUND then
dbms_output.put_line('未找到数据');
 
end;

⑤条件判断

if 条件 then
业务逻辑
end if;
 
 
if 条件 then
业务逻辑
else
业务逻辑
end if;
 
 
if 条件 then
业务逻辑
elsif 条件 then
业务逻辑
else
业务逻辑
end if;

⑥循环

--1、无条件循环
loop
-- 循环语句
end loop;
 
 
--2、条件循环
while 条件
loop
end loop;
 
 
--3、for循环
for 变量 in 起始值..终止值
loop
end loop;

--输出从1开始的100个数
declare
v_num number:=1;
begin
loop
dbms_output.put_line(v_num);
v_num:=v_num+1;
exit when v_num>100;
end loop;
end ;
 
 
 
declare
v_num number:=1;
begin
while v_num<=100
loop
dbms_output.put_line(v_num);
v_num:=v_num+1;
end loop;
end ;
 
 
begin
for v_num in 1..100
loop
dbms_output.put_line(v_num);
end loop;
end;

⑦游标

游标是系统为用户开设的一个数据缓冲区,存放 SQL 语句的执行结果。可以把游标理解为 PL/SQL 中的结果集。

声明：cursor 游标名称 is SQL 语句;

使用游标：

open 游标名称
loop
fetch 游标名称 into 变量
exit when 游标名称%notfound
end loop;
close 游标名称

declare
t_stu stu%rowtype;
cursor cur_stu is select * from stu where id=1 ;-- 定义游标
 
begin
 
open cur_stu; -- 打开游标
loop
fetch cur_stu  into t_stu ;-- 提取游标到变量
exit when cur_stu  $notfound;-- 当游标到最后一行下面退出循环
dbms_output.put_line(t_stu.id,t_stu.name);
end loop;
close cur_stu ;-- 关闭游标
 
end;
 
 
-------带参数的游标
 
declare
t_stu stu%rowtype;
cursor cur_stu(t_id number) is select * from stu where id=v_id ;-- 定义游标
 
begin
 
open cur_stu(1); -- 打开游标
loop
fetch cur_stu  into t_stu ;-- 提取游标到变量
exit when cur_stu  $notfound;-- 当游标到最后一行下面退出循环
dbms_output.put_line(t_stu.id,t_stu.name);
end loop;
close cur_stu ;-- 关闭游标
 
end;
 
 
------for 循环提取游标值
declare
t_stu stu%rowtype;
cursor cur_stu(t_id number) is select * from stu where id=v_id ;-- 定义游标
 
begin
 
for t_stu  in cur_stu(1)
loop
dbms_output.put_line(t_stu.id,t_stu.name);
end loop;
end;

十四、存储函数

储函数又称为自定义函数。可以接收一个或多个参数，返回一个结果。在函数中我们可以使用 P/SQL 进行逻辑的处理。

①创建或修改存储过程

CREATE [ OR REPLACE ] FUNCTION 函数名称
（参数名称 参数类型, 参数名称 参数类型, ...）
RETURN 结果变量数据类型
IS
变量声明部分;
BEGIN
逻辑部分;
RETURN 结果变量;
[EXCEPTION
异常处理部分]
END;

--根据id查询姓名
create function fn_getstu(t_id number)
return varchar2
is 
t_name varchar2(30);--变量声明
 
begin
select name into t_name from stu where id=t_id;
return name;
end;
 
--调用
select fn_getstu(1) from dual;

十五、存储过程

存储过程是被命名的 PL/SQL 块，存储于数据库中，是数据库对象的一种。应用程序可以调用存储过程，执行相应的逻辑。

存储过程与存储函数都可以封装一定的业务逻辑并返回结果，存在区别如下：

1、存储函数中有返回值，且必须返回；而存储过程没有返回值，可以通过传出参数返回多个值。

2、存储函数可以在 select 语句中直接使用，而存储过程不能。过程多数是被应用程序所调用。

3、存储函数一般都是封装一个查询结果，而存储过程一般都封装一段事务代码。

①创建或修改存储过程

CREATE [ OR REPLACE ] PROCEDURE 存储过程名称
（参数名 类型, 参数名 类型, 参数名 类型）
IS|AS
变量声明部分;
BEGIN
逻辑部分
[EXCEPTION
异常处理部分]
END;

过程参数的三种模式：

1
2
3

IN 传入参数（默认）
OUT 传出参数 ，主要用于返回程序运行结果
IN OUT 传入传出参数

--1、不带传出参数的存储过程：
create or replace procedure pro_stu_add(
t_id number,
t_name varchar(30),
t_age number,
t_sex varchar(30)
);
is
 
begin
insert into stu values(t_id,t_name,t_age,t_sex);
commit;
end;
 
--调用
call pro_stu_add(15,'小张',18,'男');
 
 
---2、 创建带传出参数的存储过程
create sequence seq_stu start with 15;
 
create or replace procedure pro_stu_add1(
t_name varchar(30),
t_age number,
t_sex varchar(30)，
t_id out number
);
is
select seq_stu.nextval insert into t_id from dual;
begin
insert into stu values(t_id,t_name,t_age,t_sex);
commit;
end;
 
--调用
declare
id number;-- 定义传出参数的变量
begin
 pro_stu_add1('小王',20,'女',id);
 DBMS_OUTPUT.put_line('增加成功,ID:'||id);
end;

十六、触发器

数据库触发器是一个与表相关联的、存储的 PL/SQL 程序。每当一个特定的数据操作语句(Insert,update,delete)在指定的表上发出时，Oracle 自动地执行触发器中定义的语句序列。

①创建触发器

CREATE [or REPLACE] TRIGGER 触发器名
BEFORE | AFTER
[DELETE ][[or] INSERT] [[or]UPDATE [OF 列名]]
ON 表名
[FOR EACH ROW ][WHEN(条件) ]
declare
……
begin
PLSQL 块
End ；

BEFORE | AFTER 前置触发器、后置触发器

前置触发器，修改后，不提交自动更新表

前置触发器，修改后，使用commit提交才更新表

-- 创建日志表 : 用于记录更改前后的名称
create table stu_log
(
update date,
id number,
oldname varchar(30),
newname varchar(30)
);
 
-- 创建后置触发器，自动记录更改前后日志
create trigger tri_stu_log
after
update of name
on stu
for each row
declare
begin
insert into stu_log values(sysdate,:old.id,:old.name,:new.name);
end;
 
 
-- 更新数据
update stu set name='张三三' where id=1;
commit;
 
-- 查询日志表
select * from stu_log;

查看执行计划

执行计划（Execution Plan）也叫查询计划（Query Plan），它是数据库执行SQL语句的具体步骤和过程。SQL查询语句的执行计划主要包括：

● 访问表的方式。数据库通过索引或全表扫描等方式访问表中的数据。

● 多表连接的方式。数据库使用什么连接算法实现表的连接，包括多个表的先后访问顺序。

● 分组聚合以及排序等操作的实现方式。

虽然不同数据库对于SQL查询的执行过程采用了不同的实现方式，但是一个查询语句大致需要经过分析器、优化器以及执行器的处理并返回最终结果，同时还可能利用各种缓存来提高访问性能。

简单来说，一个查询语句从客户端的提交开始，直到服务器返回最终的结果，整个过程大致如图所示。

首先，客户端提交SQL语句。在此之前客户端必须连接到数据库服务器，图中的连接器就是负责建立和管理客户端连接的组件。

然后，分析器（解析器）解析SQL语句的各个组成部分，进行语法分析，并检查SQL语句的语法是否符合规范。

例如，以下语句中的FROM关键字错写成了FORM：

在这种情况下，所有的数据库管理系统都会返回一个语法错误。

然后，优化器会利用数据库收集到的统计信息决定SQL语句的最佳执行方式。例如，是通过索引还是通过全表扫描的方式访问单个表，使用什么顺序连接多个表，如何实现数据的排序等。

优化器是决定查询性能的关键组件，而数据库的统计信息是优化器判断的基础。

最后，执行器根据优化之后的执行计划调用相应的执行模块来获取数据，并将结果返回客户端。

执行计划的查看方式

方法一：语句查看

在Oracle数据库中，我们同样可以使用EXPLAIN PLAN FOR命令生成执行计划，不过需要执行两条命令：

EXPLAIN PLAN FOR
 
SELECT * FROM  T_USER1 t where t.user_name = 'Admin';
 
SELECT * FROM  TABLE(dbms_xplan.display);

使用EXPLAIN PLAN FOR命令生成执行计划，并将其存储到系统表PLAN_TABLE中，然后通过一个查询语句显示生成的执行计划。

其中dbms_xplan.display是一个Oracle系统函数。返回的结果显示，该语句在Oracle中是通过’SYS_C0016771’索引范围扫描来查找数据的。

方法二：利用工具查看

在常用的Oracle数据库开发工具PL/SQL中，选择一段SQL脚本，按F5键，即可自动显示该脚本的执行计划信息，跟方法一查询出的结果相同。

在Oracle数据库中，我们可以通过系统表user_indexes和user_ind_columns查询索引以及相关的字段信息

1
2
3

SELECT * FROM  user_indexes;
 
SELECT * FROM  user_ind_columns;

中间件

#Oracle

Oracle基本搭建以及使用

https://cai-qichang.github.io/2022/03/21/Oracle基本搭建以及使用/

作者

caiqichang

发布于

2022年3月21日

许可协议

BY-蔡奇倡

Java基础上一篇

Springboot整合rocketmq 下一篇