Oracle数据库的应用 由浅到深

为orcale数据库的应用的学习，与orcale数据库管理无关

用户的创建与授权

用户类型

类型	描述	功能
sysdba	数据库管理员	打开数据库服务器，关闭数据库服务器，备份数据库，恢复数据库，日志归档，会话限制，管理功能，创建数据库
sysoper	数据库操作员	打开数据库服务器，关闭数据库服务器， 备份数据库，恢复数据库，日志归档，会话限制
normal	普通用户	无任何权限 （只有通过被授权之后才可以对数据库进行操作）

用户

默认解锁的用户

在orcale安装完成后本身会有三个没有上锁的用户可供操作，分别是：

用户名	密码	连接类型	描述
sys	安装时设定的密码	sysdba, sysoper	级别最高的账户
sysdba	安装时设定的密码	sysdba	级别最高的账户
system	安装时设定的密码	normal	普通用户，但拥有dba角色

三个用户的区别：

sys、sysdba 的类型为 sysdba，其本身就是用来控制数据库的启停，故他俩不依赖数据库的启动，数据库没启动这两个用户也可以登陆

system被授予了dba权限，system必须依赖于数据库的启动，数据库工作正常，其dba角色才能正常工作

创建用户

需要用 sys 或者 sysdba 用户登陆才能创建用户

-- 创建新用户
create user 用户名 identified by 密码;

 修改用户密码

-- 修改密码的两种方式，任选其一即可
alter user 用户名 identified by 新密码;

alter user 用户名 identified by 新密码 replace 旧密码;

删除用户

[cascade] ：当用户被删除时，用户所拥有的表、数据都会被级联删除

drop user 用户名 [cascade];

查询所有的用户

select * from all_users;

授权

新建的用户是不能直接用于登陆的，因为没有权限。

这里有几个概念：

• 权限：仅让特定的人访问特定的信息。给你写的权限才能写，不然会被拒绝
• 角色：权限的集合称为角色。每次都重复授予多个权限给不同的用户，效率太低。可先配置好权限的集合，再将权限的集合授予用户
• 系统权限：允许用户执行特定的数据库动作，如创建表、创建索引、连接实例等。
• 对象权限: 允许用户操纵一些特定的对象，如读取视图，可更新某些列、执行存储过程等。

-- 格式:权限或角色列表之间用英文逗号相连，如权限A和B，应该为A,B
-- [with admin option]为可选添加，表示该用户获得权限后，他也可以将权限赋予别的用户
grant 权限/角色列表 to 用户名 [with admin option];

-- 例子：将登陆权限赋给apple用户
grant create session to apple

常见的系统权限

可以通过命令查询所有系统权限：

select * from system_privilege_map;

常见的权限有以下（本文主要总结数据库应用，所以管理部分简单总结）：

权限名称	描述
create session	连接到数据库
create sequence	创建序列
create table	创建表
create any table	在任何模式中创建表
drop table	删除表
drop any table	删除任何模式中的表
create procedure	创建存储过程
execute any procedure	运行任务模式中的存储过程
create user	创建用户
alter user	修改用户
drop user	删除用户
create view	创建视图
create synonym	创建同义词
create public synonym	创建公用的同义词

常见的系统角色

角色名称	拥有权限
dba	拥有所有的权限
connect	create session 连接到数据库 alter session  修改会话 create sequence 建立序列 create synonym 建立同义词 create view 建立视图 create cluster  建立聚簇 create database link  建立数据库链接
resource	create trigger  建立触发器 create sequence   建立序列 create procedure  建立存储过程 create cluster  建立聚簇 create operator   建立操作员 create index  建立索引 create indextype  建立索引类型 create table  建立表

收回权限

-- 收回权限代码格式
revoke 权限/角色列表 session from 用户;

查询已拥有的权限

-- 查询所有用户和角色被授予的系统权限
select * from dba_sys_privs;

-- 查询当前登录用户被授予的系统权限
select * from user_sys_privs;

-- 查询当前登录用户拥有的角色
select * from user_role_privs;

对象的权限

对象指的是：表，视图，索引，同义词，存储过程，触发器，序列等等

• 不同的对象具有不同的对象权限
• 对象的拥有者拥有所有权限
• 对象的拥有者可以向外分配权限

对象权限类别	表	视图	序列	过程
查询(select)	有	有	有
插入(insert)	有	有
更新(update)	有	有
删除(delete)	有	有
修改(alter)	有		有
索引(index)	有
关联(references)	有
执行(execute)				有

授予对象权限

-- 对象权限列表间用英文逗号相隔
-- [with grant option]为可选添加，表示该用户获得权限后，他也可以将权限赋予别的用户
grant 对象权限列表 on 对象名 to 用户名 [with grant option]

insert 和 update 可以只授权部分字段权限

-- 授予部分字段权限（insert,update）
grant 权限名称(字段列表) on 表名 to 用户名 [with grant option]

-- 如：为A用户赋予对表stu的对name字段插入的权限
grant insert(name) on stu to A;

查询已有的对象权限

-- 通常是爸爸查对象权限授给谁了
select * from user_tab_privs_made;

-- 查询当前用户拥有的对象权限
select * from user_tab_privs_recd;
-- 或
select * from user_tab_privs;

收回对象权限

-- 格式
revoke 权限列表 on 对象名 from 用户名;

不支持收回 insert 或 update 授予的部分字段权限，要收只能整个对象权限收回

数据类型

字段类型	中文说明	限制条件	其它
CHAR	固定长度字符串	最大长度2000 bytes
VARCHAR2	可变长度的字符串	最大长度4000 bytes	varchar2中文占2字节，英文占1字节。
NVARCHAR2	根据字符集而定的可变长度字符串	最大长度4000 bytes	Nvarchar2中英文占一样的字节，具体占多少字节因字符集而定。
DATE	日期（日-月-年），时间可有可无	YYYY-MM-DD（HH24:MI:SS）
TIMESTAMP(6)	时间戳 （年月日时分秒毫秒）	YYYY-MM-DD HH24:MI:SS.ff	与DATE数据类型相比，TIMESTAMP类型可以精确到微秒，微秒的精确范围为0-9，默认为6
LONG	超长字符串	最大长度2G	足够存储大部分著作
RAW	固定长度的二进制数据	最大长度2000 bytes	可存放多媒体图象声音等
LONG RAW	可变长度的二进制数据	最大长度2G	同上
BLOB	二进制数据	最大长度4G
CLOB	大字符数据	最大长度4G	保存单字节或多字节字符数据，最大值为4G
NCLOB	根据字符集而定的字符数据	最大长度4G	保存Unicode编码字符数据,最大值为4G。
BFILE	存放在数据库外的二进制数据	最大长度4G
NUMBER(P,S)	数字类型	P为所有有效数字的位数，S为小数位	oracle底层只有number为类型并没有decimal和integer这两个类型 这两个类型只为oracle和其它数据库之间方便迁移的。
DECIMAL(P,S)	数字类型	P为所有有效数字的位数，S为小数位
INTEGER	整数类型	小的整数
FLOAT	浮点数类型	NUMBER(38)，双精度	存储近似值
REAL	实数类型	NUMBER(63)，精度更高	存储近似值

DDL

DDL为数据定义语言，用于创建对象。如:CREATE TABLE ,ALTER TABLE,DROP TABLE,CREATE VIEW……

新建表

-- 建表语句
create table 表名(
    列名 类型,
    列名 类型,
    ......
);

还可以用已有数据来新建表：

create table 新表名 as select * from 数据表;

修改表

-- 修改表的名字
rename 旧表名 to 新表名;

-- 给表添加备注信息，提高可读性
comment on table 表名 is '备注';

-- 给表中的字段添加备注信息
comment on column 表名.字段 is '备注';

修改表结构：

-- 表中新增列
alter table 表名 add 列名 列类型;

-- 删除列
alter table 表名 drop column 列名;

-- 修改列名和列类型
alter table 表名 modify 列名 列类型;

-- 更改列名 
alter table 表名 rename column 旧列名 to 新列名;

删除表

-- 删除表格式
drop 表名;

-- 相当于将表删了重建了一次
truncate table 表名;

查询用户所有的表

select * from user_tables;

查看表结构

select table_name,column_name,data_type,data_length,data_precision
from user_tab_columns;

查看表备注

select * from user_tab_comments;

查看列备注

select * from user_col_comments;

DML

DML为数据操纵语言，如增、删、改等等

注意：DML要运行完要提交事务才生效​​​​​​​ 

插入数据

插入数据的语句执行完后，要提交事务，不然不会保存到表中

-- 指定要插入哪个列
insert into 表名(列名1,列名2,列名3...) values (列1的值,列2的值,列3的值...);

-- 对所有的列插入，注意要插入的列的位置、个数、类型都要和表结构一一对应
insert into 表名 values (列1的值,列2的值,列3的值…);

-- 将表B的查询结果插入表A，注意表结构
insert into 表A select * from 表B;

在插入数据时，Oracle不支持对数据进行自增。想要实现自增功能，我们可以结合序列来使用。可以先跳转到序列了解。

-- 插入数据时，对id列实现自增
insert into 表名(id,列名2,列名3...) values (序列名称.nextval,列2的值,列3的值...);

 更新数据

如果不写 where 条件，将会更新整个表中对应的字段

-- 更新基本格式
update 表名 set 字段1=字段1的值,字段2=字段2的值…… 
where 字段1=字段1的值 [and/or] 字段2=字段2的值……

-- 例如：更新id为1的这条数据，age变为20，name变为张三
update table_A set age=20,name=’张三’ where id=1

删除数据

如果不写 where 条件，将会删除整个表的数据

-- 基本格式
delete from 表名 where 字段1=字段1的值 [and|or] 字段2=字段2的值……

DQL

DQL为数据查询语言，如：SELECT……

查询语句

select 子句 from 子句 [where 子句 [order by cloumns [asc/desc]]]

查询中使用的运算符

• 转义：需要使用ESCAPE选项区分通配符，ESCAPE选项告知数据库如何区分通配符和要匹配的字符。如：SELECT ename  FROM  emp  WHERE ename  LIKE  '%\%%'  ESCAPE  '\'
• 通配符：下划线 “ _ ” 表示匹配1个字符。百分号 “ % ” 表示匹配任意个字符
• 连接符 || 或 concat(arg1,arg2)
• & 定义一个可变部分，执行 sql 时给变量值

.........

分页查询

首先有两个表字段需要了解：（Oracle中这两个字段在每个表都有）

• rowid：是物理存在的。真实存在表中，每个 rowid 是唯一的
• rownum：是逻辑存在的。相当于你执行了查询，系统自动给你新增一列，这列是有规律的数组

-- 查看表的rowid，一定要给表起别名才能运行，不然会报错
select 表别名.*,表别名.rowid from 表名 as 表别名;

 

-- 查看表中的rownum
-- 因为 rownum 是逻辑存在的，并不实际存在于表中，所以它前面不用加表别名.rownum
select 表别名.* , rownum from 表名 as 表别名;

那我们要如何实现分页查询呢？

-- 首先准备数据
-- 创建表
create table stu(
id number(9),
name varchar2(20),
sex number(1)
);

-- 循环插入数据
begin
  for i in 1..100
    loop
      insert into stu(id,name,sex) values (i,'帅哥' || i,0);
    end loop;
end;

-- 简单的就能想到between不就行了吗
select t.*,rownum from stu t
where rownum between 1 and 10

-- 但你会发现当起始页码不为1时，查出来就会没有数据
-- 因为rownum不是真是存在表的，是查询完了才新增上去的
select t.*,rownum from stu t
where rownum between 3 and 10

--在外面新增一条嵌套就可以解决，保存rownum到子查询中
select * from (
       select t.*,rownum rn from stu t
)
where rn between 3 and 10

 老要算起始行号和结束行号也太烦了，这里引出两条公式（自己推导也很简单）：

起始行号 = （当前页号 - 1）* 每页记录数 + 1

结束行号 = 当前页号 * 每页记录数

-- 最终代码
-- & 为定义了可变变量
select * from (
       select t.*,rownum rn from t_stu t
)
where rn between (&当前页号 - 1)* &每页记录数 + 1 and &当前页号 * &每页记录数

 

常用函数

单行函数

单行函数指处理单个行的函数，并给每行都返回一个结果

常用字符函数

• initcap(x)：将字符串转换为每个词首字母为大写，其他字母为小写
• instr(str , find_string , [ , start ] , [ , count ])：找子串 find_string 在 str 中的位置，可指定开始搜索的位置 start ，也可指定希望找到子串第几次 count 出现的位置，默认都是1
• lpad(x,  width  [,  pad_string]) 和 rpad(x,  width  [,  pad_string]) ：在字符串 x 的左 / 右填充长度 width 个 pad_string，pad_string不指定默认填充空格，中文占2个位
• ltrim(x [,  trim_string]) 和 rtrim(x [,  trim_string]) 和 trim(trim_string)：去除字符串 x 左边 / 右边 / 两边的 trim_string，trim_string不指定默认为空格
• nvl(x, value)：当 x 为 null 时返回 value，不为 null 时返回本身
• nvl2(x,  value1,  value2)：当 x 不为 null 返回 value1，当 x 为 null 返回 value2
• replace(x,  search_string,  replace_string)：将字符串 x 中的 search_string 替换为replace_string

常用数值函数

• ceil(value)：向上取整，如 ceil(6.3) 、ceil(-6.3) 结果分别为 7 和 -6
• floor(value)：向下取整，如 floor(6.3)、ceil(-6.3) 结果分别为 6 和 -7
• power(value,n)：返回value的n次幂
• sqrt(value)：对value进行开平方
• mod(m,n)：返回m和n取余数的结果
• trunc(value,n)：对value值进行截断，不会四舍五入。n>0 时，保留n位小数，不会四舍五入。n<0时，截断n位整数，如 trunc(530.2 , -2) 结果为 500
• round(value[,n])：对value进行四舍五入。n>0时，保留n位小数，四舍五入。n<0时，截断n为整数，会四舍五入。n默认为0

常用转换函数

• to_char(x [,  format ])：将x转化为字符串。 format为转换的格式，可以为数字格式或日期格式
• to_date(x [ , format ])：将x字符串转换为日期，如 to_date('2020-3-15','YYYY-MM-DD‘)
• to_number(x [,  format ])：将x转换为数字
• cast(x  AS  type)：将x转换为指定的兼容的数据库类型

to_char 详解：

数字格式：如 select to_char(12345.67, '99,999.999') from dual; 结果为12,345.670

确定宽度只能用 0 和 9

数字转换格式	作用
9	确定宽度几个整数几个小数，前导不够补空，后导不够补0
0	确定宽度，前后导都补0
$	美元，select to_char(12.34, '$99.99') from dual;结果为$12.34
L	当地的货币符号，中国是￥
.	英文小数点
,	分隔符（英文逗号）

日期格式：如select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual

sysdate为当前时间

日期转换格式	作用
q	显示1-4季度
ww	当前周是年里面的第几周
sysdate - interval '7' minute	当前时间减去7分钟的时间
sysdate - interval '7' hour	当前时间减去7小时的时间
sysdate - interval '7' day	当前时间减去7天的时间
sysdate - interval '7' month	当前时间减去7月的时间
sysdate - interval '7' year	当前时间减去7年的时间
sysdate - 8*interval '7' hour	时间间隔乘以一个数字

聚集函数

聚集函数可对行集进行操作，并且为每组给出一个结果

• avg(x)：返回 x 的平均值
• count(x)：返回统计的行数
• max(x)：返回x的最大值
• min(x)：返回x的最小值
• sum(x)：返回x的总计值
• median(x)：返回中间值

数据完整性

定义：数据完整性是指数据的精确性和可靠性，它是防止数据库中存在不符合语义规定的数据和因错误信息的输入造成无效操作或错误信息而提出的。

数据完整性就是对列加约束。有以下三种约束类型：

• 实体完整性约束：要求每一个表中的主键字段都不能为空（非空）或者重复的值（唯一）。对应为 primary key （主键约束）和 unique （唯一约束）
• 域完整性约束：指列的值域的完整性。如数据类型、格式、值域范围、是否允许空值等。对应位 check （检查约束）和 default （设置默认值）和 not null （非空约束）
• 参照完整性约束：当更新、删除、插入一个表中的数据时，通过参照引用相互关联的另一个表中的数据，来检查对表的数据操作是否正确。对应为 foreign key （外键约束）

添加约束

两种创建方式。一种为随表创建，一种为建表后添加

-- 建表后再创建
alter table 表名 add constraint 约束名称 约束类型 (列名);

-- 随表创建，除not null和default约束外一般使用第一第二种方式，可以自定义约束名称
-- 第一种方式 []为可选
create table 表名(
    列名 类型 [constraint 约束名称] 约束类型
);

-- 第二种方式
create table 表名(
    列名 类型,
    [constraint 约束名称] 约束类型(列名)
);

-- 第三种方式，这种方式一般适合添加 not null 或者 default 约束
create table 表名(
    列名 类型 约束类型
);

删除约束

-- 格式
alter table 表名 drop constraint 约束名称;

启用和禁用约束

--enable 为启用，disable为禁用
alter table 表名 modify constraint 约束名称 enable;
alter table 表名 modify constraint 约束名称 disable;

-- not null 为启用非空,对应 null 为禁用非空
alter table 表名 modify 列名 not null;
alter table 表名 modify 列名 null;

-- default 值为启用默认指定值，对应改为 default null 则相当于禁用该约束
alter table 表名 modify 列名 default 值;
alter table 表名 modify 列名 default null;

查询约束

select TABLE_NAME , CONSTRAINT_NAME，CONSTRAINT_TYPE,STATUS from USER_CONSTRAINTS;
-- 或者
select * from USER_CONS_COLUMNS;

级联

有三个可选项：

这里举例两个表来方便理解

{

学生表 student ( id (主键) , sname , teacher_id (外键) )

教师表 teacher ( teacher_id (主键) , tname )

}

• not action：不执行。当你想删除教师表中的数据时，如果学生表中也有 teacher_id 和它相等的数据时，删除失败。
• cascade：级联删除。当你想删除教师表中的数据时，如果学生表中也有 teacher_id 和它相等的数据时，学生表中有该 teacher_id 的数据行也被跟着删除。
• set bull：级联更新。当你想删除教师表中的数据时，如果学生表中也有 teacher_id 和它相等的数据时，学生表中的该 teacher_id 更新为 null。

序列

序列是有顺序的数组，如123456...... 或 13579..... 都是序列

序列的特点为不走回头路，只向前，不后退

创建序列:

[ minvalue 1 maxvalue 999999999999999999999999999 increment by 1 start with 1]:可选填

默认：最小值从1到最大值999999999999999999999999999，步长为1，从1开始

create sequence 序列名称[ minvalue 1 maxvalue 999999999999999999999999999 increment by 1 start with 1]

使用序列：

-- 每执行一次序列就加1
select 序列名称.nextval from DUAL;

-- 查询当前序列的值,执行多少次序列都不会增加
select 序列名称.currval from DUAL;

解释：

nextval 就是 Next Value，下一个值。

currval 就是 Current Value，当前值。

DUAL 可认为是系统的虚表，是不存在的表。因为这条SQL不知道要作用在哪个表，当不知道该作用在哪张表时就写DUAL。

PL/SQL

PL/SQL(Procedural Language/Structured Query Language，过程语言/结构化查询语言)

PL/SQL的基本格式

PL/SQL的块由变量声明、程序代码和异常处理代码3部分组成：

declare (声明)

  声明一些变量、常量、用户定义的数据类型及游标:

  name varchar(30);  --声明时不设置值

  name varchar(30) := ‘Jack’;  --声明带有默认值

  name preson.name%type;  --直接引用一个表的数据类型

begin （主体）

  主程序体，在这里可以加入各种合法语句

exception （异常处理）

  异常处理程序，当程序中出现错误时执行这一部分

end （结束）

变量/常量的声明

变量类型

char，varchar2，date，number，boolean，long，integer，type，rowtype

如果要赋值要用 := 号

常量类型

常量定义后，在以后的使用中其值不再改变

常量名 constant 数据类型 := 值;

输出

• dbms_output.put()：不换行输出，不能单独工作要结合 put_line 使用

• dbms_output.put_line()：换行输出

-- 例子：
set severoutput on --如果您的工具为SQL*Plus则需要执行, PL/SQL Developer不需要执行该条

declare
    name varchar(20):='HelloWorld';
begin
    dbms_output.put('你好!');
    dbms_output.put_line(name);
end;

接受参数

只需在参数前加 & 符号，执行命令时，就可接受用户对其变量的赋值

-- 例子
declare
begin
    dbms_output.put_line('您的年龄是:' || '&age');
end;

批量赋值

当我们想要将 selec t查询出来的值赋给变量，此时就不能用 := 符号了，要使用 into

-- 例子
declare
    v_id number;
    v_name varchar2(50);    
begin
    -- 注意这里 v_id，v_name 的位置顺序要和 id，name 一样
    select id,name into v_id,v_name from student where id = 1;
    dbms_output.put_line('id的值:'||id ||',name的值:'|| name);
end;

流程控制

if...then...

基本格式为：

if...then...

elsif... then...

else...

end if;

case...when...then...

基本格式为：（可直接用在SQL，也可用在PL/SQL）

case  变量名 

when … then    

when … then  

else

end

for 循环

基本格式为：

for 变量 in 起始值..结束值

loop

end loop;

loop 循环

有两种方式，一种为设置条件自动退出循环，另一种为手动退出

-- 当符合条件时，退出循环

loop

        exit when 条件;

end loop;

-- 当exit被执行时，退出条件。一般结合 if 使用

loop  

//exit;  

end loop;

while 循环

基本格式为：

while 结束条件

loop

.....

end loop;

异常处理 Exception

有两种异常类型

• 预定义异常：系统能够发现的异常，系统已经定义过了
• 自定义异常：系统没有定义过，需要自己自定义的异常

预定义异常

基本格式为：

declare

begin

......

exception

        when 异常名称 then

        -- 异常处理语句

        ........

        -- 异常可使用SQLCODE输出错误编码，以及使用SQLERRM来输出错误信息：

        dbms_output.put_line('值类型错误' || SQLCODE || SQLERRM);

        when 异常名称 then

        -- 异常处理语句

        ………

        when others then

        -- 异常处理语句

end;

系统定义异常种类	原因
ACCESS_INTO_NULL	在未初始化对象时出现
CASE_NOT_FOUNF	在CASE语句中的选项与用户输入的数据不匹配时出现
COLLECTION_IS_NULL	在给尚未初始化的表或数组赋值时出现
CURSOR_ALREADY_OPEN	用户试图重新打开已经打开的游标时出现。在重新打开游标前必须先将其关闭
INVALID_CURSOR	在执行非法游标运算(如fetch一个尚未打开的游标)时出现
LOGIN_DENIED	输入的用户名或密码无效时出现
SOTRAGE_ERROR	在内存损坏或PL/SQL耗尽内存时出现
DUP_VAL_ON_INDEX	用户试图将重复的值存储在使用唯一索引的数据库列中时出现
INVALID_NUMBER	将字符串类型转换为数字时出现
NO_DATA_FOUND	在表中不存在请求的行时出现
TOO_MANY_ROWS	在执行SELECT INTO语句后返回多行时出现
VALUE_ERROR	变量中的列值超出变量设的大小或类型
ZERO_DIVIDE	以零做除数时出现

自定义异常

Oracle允许自定义的错误编码的SQLCODE范围为-20000 -- -20999，即有一千个可自定义

第一种自定义异常方式：

1. 在 PL/SQL 结构的 begin 后编写代码
2. 通过RAISE_APPLICATION_ERROR(SQLCODE,SQLERRM) 来主动抛出异常
3. 在 exception 后通过 when others then 来捕获

如：

declare

begin

         RAISE_APPLICATION_ERROR(-20001,'hello ，error!!!');

exception

          when others then

                  dbms_output.put_line( SQLCODE ||'      '|| SQLERRM);    

end;

第二种自定义异常方式：(更优雅)

1. 在 PL/SQL 结构的 declare 后定义 exception 变量
2. 在 begin 后编写代码，自行决定什么时候 RAISE 抛出异常
3. 在 exception 后通过 when 自定义异常变量名 then 来捕获

如：

declare

myexp exception;

begin

RAISE myexp;

exception

         when myexp then

                RAISE_APPLICATION_ERROR(-20001,'hello ，error!!!');

end;

两种方式的区别：

当异常代码在程序中重复执行，若是第一种方式，会不断的向程序抛出和错误编码相关的信息，当你想修改错误编码和信息的绑定时就要改多次，而第二种方式只用改一处

存储过程

定义：存储在数据库中供用户程序调用的程序叫存储过程。

就像定义了一个方法，可供随时调用

基本格式：

create [or replace] procedure 过程名 [(参数名称1 in/out 参数类型1,参数名称2 in/out 参数类型2...)]

as

实参定义

begin

...

end;

形参声明：

基本格式：参数名称 in/out 参数类型

• in类型为输入类型的参数，out类型为输出类型的参数
• 过程没有返回值,in类型的参数，只可以接收值，不能再给in类型的参数设置新的值
• 利用out参数在过程中实现返回多个值
• 声明接收参数的只声明类型，不声明大小

实参声明：

​​​​​​​在 as 和 begin 之间定义

调用存储过程：

begin 过程名称[(参数1，参数2.....)] end;

-- 例子：对年龄进行修改
create or replace procedure add_age (nid in number)
as
begin
  update t_stu set age = age + 1 where id = nid; 
  commit;
end;

-- 调用该存储过程，当id为17时
create or replace procedure add_age (nid in number)
as
begin
  update t_stu set age = age + 1 where id = nid; 
  commit;
end;

游标

游标是 cursor ，游标可遍历数据表中的每一行，每一次只能指向一行。常用于存储过程和函数当中。游标有两种类型：静态游标和动态游标

静态游标

在 declare 中确定结果集的游标，结果不会随着时间或用户的不同而发生变化。静态游标又分为两类：隐式游标和显式游标

隐式游标

定义：是Oracle数据库默认使用的游标，由 select 语句自动创建，但只能在SQL语句中使用不能在PL/SQL中使用，每次返回一行数据

隐式游标不需要声明、不需要打开/关闭、不用赋值，全部是系统自动完成

隐式和显式Cursor都具有的属性:

属性	返回类型	作用
SQL%ROWCOUNT	整形	代表DML语句成功执行的数据行数
SQL%FOUND	布尔型	值为TRUE代表操作成功
SQL%NOTFOUND	布尔型	与SQL%FOUND相反
SQL%ISOPEN	布尔型	DML执行过程中为真，结束后为假

显示游标

定义：结果集在定义游标时就已经确定，不会随时间或用户的不同而发生变化。并且显式游标是由程序员在PL/SQL代码中显式声明和使用的游标类型。它可以在程序中读取和处理多行数据

显式游标的运用的四个步骤：

1. 定义游标：在 declare 中 cursor 游标名 is;
2. 打开游标：在 begin 中 open 游标名;
3. 操作数据：fetch 游标名  into 行对象;可理解为推进一行
4. ​​​​​​​关闭游标：close 游标名;

-- 显示游标例子1：
declare
 cursor student_c is select * from student where id = 1;
 rs student%rowType;
begin
   open student_c;
   fetch student_c into rs;
   dbms_output.put_line(rs.id || ' ' || rs.name); 
   close student_c;
end;

-- 例子2 多行游标：
declare
 cursor student_c is select * from student order by id;
begin
   --无需打开和关闭，自动打开和关闭
   for rs in student_c 
   loop
      dbms_output.put_line(rs.id || ' ' || rs.name); 
   end loop;
end;

动态游标

定义：动态游标（Ref cursor）是在PL/SQL中使用，可以在运行中动态的构建SQL查询语句，使用起来更灵活

Ref cursor的使用步骤:

1. Type 自定义游标类型 is ref cursor; 自定义了一个游标类型，并声明它是一个动态游标
2.  游标名称 自定义游标类型; 声明一个游标 它是自定义类型
3. open 游标名称 for sql语句;
4. fetch 游标名称  into 行对象;
5.  close 游标名称;

-- 例子：
create or replace procedure sigleRowCoursor(id_p in varchar2) is
  cursor student_c is select * from student where id = id_p;
  rs student%rowType;
  type ref_cursor_type is ref cursor;--自定义了一个游标类型，并声明它是一个动态游标
  college_c ref_cursor_type;
  rs_college college%rowType;
  v_sql varchar2(100);
begin
   open student_c;
   fetch student_c into rs;
   dbms_output.put_line(rs.id || ' ' || rs.name);
   v_sql := 'select * from college where id = ' || rs.col_id;-- 这里只是字符串不做操作
   close student_c;
   open college_c for v_sql;-- 打开动态游标
   fetch college_c into rs_college;
   dbms_output.put_line(rs_college.id || ' ' || rs_college.name);
   close college_c;
end;

视图

视图是临时表，不存数据，存储在数据库的数据字典中。每次查询视图时，数据库管理系统会执行视图的定义查询来动态生成结果集。因此，视图更像是一个窗口，通过它可以查看和操作基础表的数据，同时保护基础表的数据安全，简化复杂的查询操作‌

数据库的设计和结构不会受到视图中的函数、where 或 join 语句的影响

-- 基本格式
create view 
as
select ...

同义词

同义词就是给表起别名。使用同义词访问相同的对象，可以为表、视图、存储过程、函数甚至给同义词创建同义词，方便访问其它用户的对象，隐藏了对象的身份。

-- 格式
create synonym 同义词 for 原对象名;