Oracle11g指导手册

参考作者

• shangboerds
• https://blog.csdn.net/weixin_41968788/article/details/83659164

1. 相关概念

1.1 Oracle 版本

• Oracle 8 / Oracle 8i：只有 1CD大小，i 表示 internet，表示Oracle开始进军网络
• Oracle 9i：3CD大小，属于Oracle 8i的稳定版本，现在依然大范围使用
• Oracle 10g：630M大小，使用了网格计算的方式，提升了数据库的分布式访问性能
• Oracle 11g：1.7G大小，属于Oracle 10g的稳定版本，现在新项目使用的多（常用）
• Oracle 12c：指的是云服务的支持，不推荐使用

1.2 Oracle 模式

什么是模式（schema）

模式是一个逻辑容器，你可以把模式理解为文件夹，方便我们对一组数据库对象进行管理。通常，一个大的系统由许多小的系统组成，我们可以给每个小的系统创建一个模式，把该系统中用到的数据库对象都创建在这个模式中；

例如，每个公司都会有员工，我们可以创建一个模式 HR，然后在 HR 中创建一个表 EMPLOYEES 来维护员工信息，如果需要在其他模式中访问 EMPLOYEES 表，我们需要指定它的全名 HR.EMPLOYEES

模式隶属于某个数据库用户，模式名和数据库用户是相同的

模式中的对象

表（Table）, 视图（View）, 索引（Indexe）,触发起（Trigger）, 包（Package）,函数（Function）,存储过程（Procedure）,类型（Type）, 序列（Sequence）, 分区（Partition）, 同义（Synonym）等数据库对象

如何创建模式

创建数据库用户就是创建模式

补充：SYS 和 SYSTEM 模式

当安装 Oracle 的时候，系统自动创建了 SYS 和 SYSTEM 模式，它们拥有最高权限，用来管理数据库，SYSTEM 比 SYS 提供了更多的表

2. 基本知识

2.0 变量使用

1. 定义变量

语法

变量名 数据类型 [:= 默认值];

示例代码

-- 定义不赋值
v_name varchar2(10);

-- 定义不赋值，使用表中字段类型
v_name stu.sname%type;

-- 定义赋值
v_age number := 18;

2. 使用变量

declare
	v_id number := 0;
begin
	select * from stu where sid = v_id;
end;

2.1 数据类型

1. 数值类型

• NUMBER：可以存储小数和整数类型数据，格式为NUMBER(p,s)，其中 p 表示的是精度（既是位数的长度），s 表示的是小数点后的位数。例如：number(3,2) 表示的范围-9.99—9.99

2. 字符类型

• CHAR：固定长度的字符串，CHAR(n)，默认长度是1，当字符串长度小于n时，会自动右补空格，所以在取数据的时候要注意补空格

• VARCHAR2：该类型存储可变长度的字符串 VARCHAR2(n)，最大存储的长度为4000个字节。当字符串长度小于n时，不会补齐空格

• LONG：该类型存储可变长度的字符串，对于字段的存储长度可达2G，但只能存储最大32767字节，一个表中最多只有一个LONG列，LONG列不能有索引，不能出现完整性约束

3. 时间类型

• DATE：用于存储日期和时间信息，若要指定日期值，须用 TO_DATE() 将字符型的值或数值转化为日期型的值，日期型数据的默认格式为 DD-MON-YY
• TIMESTAMP：存储的不仅是日期和时间，还包含了时区

2.3 运算符

1. 关系运算符

作用说明

• 主要是进行大小关系比较操作使用的，包括 >，<，>=，<=，<>，!=

• 等号（=）可以比较数字，也可以比较字符串

示例代码

select * from emp where eid > 1

select * from emp where ename = '李白'

select * from emp where ename != '李白'

select * from emp where ename <> '李白'

2. 逻辑运算符

作用说明

• 主要用于多个条件的情况，包括 and，or，not

示例代码

select * from emp where eid > 1 and ename - '李白'

select * from emp where eid = 1 or ename = '李白'

select * from emp where (eid = 1 or ename = '李白') and eage > 18

-- 查询 id 不为 1 的记录
select * from emp where not eid = 1

3. 范围运算符

作为说明

• 用于查询范围数据，适用 日期，数值运算，between...and
• 与关系运算符的区别是，关系运算符是两个条件，范围运算符是一个条件

示例代码

select * from emp where eid between 1 and 5

select * from emp where etime between '01-1月-2020' and '30-12月-2020'

4. 空判断

定义说明

• 空判断，数据库中任何空的判断都使用这两个操作符判断，is null，is not null

示例代码

select * from emp where eid is not null

5. 谓词范围

定义说明

• 谓词范围，数值范围是可控的，in，not in

• 与范围运算符的区别是，范围运算符的值不可控，边界大，谓词范围的值可控，范围小

• 注意：当 not in 中包含 null 值时，不会返回任何记录，因为有 null 相当于查询全部记录

示例代码

select * from emp where eid in(1,2,3)

select * from emp where eid not in(4,5)

6. 模糊查询

定义说明

• 模糊查询：like
• 一个下划线（_）表示一个字符位置，百分号（%）表示多个字符位置

示例代码

select * from emp where ename like '_白'

select * from emp where ename like '%白'

select * from emp where ename like '%白%'

2.4 流程控制

1. IF-ELSE语句

使用时，if 可以单独使用，elsif，else 可省略

注意：注意 elsif语句少个 e 字母 ，不是 elseif

语法结构

if 条件1 then 
    --条件1成立执行体;
elsif 条件2 then
    --条件1不成立,条件2成立执行体;
else
    --条件都不成立执行体;
end if;

示例代码

begin
  if 1 < 3 then
    dbms_output.put_line(3);
  elsif 1 < 2 then
    dbms_output.put_line(2);
  else
    dbms_output.put_line(1);
  end if;
end;

2. CASE-WHEN-THEN

语法结构

begin
	case 选择体 
    	when 表达式1 then 执行体;
     	when 表达式2 then 执行体;
     	when 表达式3 then 执行体;
     	...
     	else 表达式n  then 执行体;
	end case;
end;

实例代码

begin
  case 1
    when 1 then dbms_output.put_line(1);
    when 2 then dbms_output.put_line(2);
    else dbms_output.put_line(3);
  end case;
end;

declare
ls_stuinfo stuinfo%rowtype;--学生信息表行
ls_number_26 number:=0;--26岁计数器
ls_number_27 number:=0;--27岁计数器
ls_number number:=0;--其它
begin
  for ls_stuinfo in (  select t.* from stuinfo t ) loop
    case ls_stuinfo.age
      when 26 then 
        ls_number_26:=ls_number_26+1;
      when 27 then 
        ls_number_27:=ls_number_27+1;
      else 
        ls_number:=  ls_number+1;
    end case;     
  end loop;
  ...
end;

2.5 循环控制

1. FOR循环

语法结构

-- 通过循环体直接进行loop循环
for 循环体别名 in (SELECT 条件查询数据) loop
    -- 循环执行体；
end loop;

-- 通过循环变量进行循环
for 循环变量 in 循环下限...循环上限 loop
end loop;

示例代码

begin
  for i in 1..5 loop
    dbms_output.put_line(i);
  end loop;
end;

begin
  for i in (select * from libai.emp) loop
    dbms_output.put_line(i.ename);
  end loop;
end;

2. WHILE循环

语法结构

while 条件  loop
    -- 循环执行体
end loop;

2.6 伪列值

伪列是Oracle表在存储的过程中或查询的过程中，表会有一些附加列，称为伪列。伪列就像表中的字段一样，但是表中并不存储。伪列只能查询，不能增删改。Oracle的伪列有：rowid、rownum

rowid 物理地址

Oracle表中的每一行在数据文件中都有一个物理地址， ROWID 伪列返回的就是该行的物理地址

select t.*,t.rowid from libai.emp t where t.rowid = 'AAASRFAAEAAAAIcAAA';

rownum 动态记录行号

表示的Oracle查询结果集的顺序，ROWNUM为每个查询结果集的行标识一个行号，第一行返回1，第二行返回2，依次顺序递增，这个行号是查询时生成的，也就是说每次查询记录的行号都是动态生成的

使用rownum可以做以下两件事：

• 返回第一条记录
• 返回前N条记录

select * from emp where rownum = 1;

select * from libai.emp where rownum < 2

select c.* from
(select e.eid,rownum rn from libai.emp e) c
where c.rn > 1

3. 数据表操作

3.1 创建数据表

3.2 修改数据表

添加字段

增加字段，语法

Alter Table 表名 Add 字段名称 字段类型;	-- 单个字段
Alter Table 表名 Add (字段名称 字段类型, 字段名称 字段类型)	-- 多个字段

增加字段，示例

Alter Table t_si_addr Add CHG_ADDR_FLAG number(1,0);	-- 单个字段
Alter Table t_si_addr Add (CHG_ADDR_FLAG number(1,0), ADD_BY_IOM Varchar2(2));	-- 多个字段

表,字段注释

注释语法

comment on column 表名.字段名 is '注释内容';	-- 单个字段
comment on table 表名 is '注释内容';	-- 表注释

注释示例

comment on column OPERATOR_INFO.MAIN_OPER_ID is '归属操作员';	-- 字段注释
comment on table OPERATOR_INFO is '操作员信息表';	-- 表注释

4. 单行函数

4.1 字符串函数

字符函数的输入为字符类型，其返回值是字符类型或者是数字类型

常用函数列表

函数	返回值	描述
lower/upper(str)	字符串	大小写转换
initcap(str)	字符串	把字符串中所有单词首字母转换为大写，其余小写
length(str)	整数	返回字符串的长度
concat(str1,str2)	字符串	返回连接两个字符串的结果
instr(str,nodestr)	整数	找字符串中指定字符串的位置，从1开始，没有找到返回0
replace(str,oldstr,newstr)	字符串	替换字符串指定字符串为新字符串
ltrim/rtrim(str[,Y])	字符串	去除字符串左右指定字符，不指定字符默认去除空格
lpad/rpad(str,len[,str2])	字符串	左右补位到指定长度，没有补位内容默认使用空格
substr(str,startindex[,len])	字符串	从指定位置截取字符串，没有指定长度截取全部

代码示例

-- 列值转换大写
select upper(e.ename) from libai.emp e

-- 返回长度大于3的记录
select * from libai.emp e where length(e.ename) > 3

4.2 数值类型函数

数值函数的输入参数和返回值都是数字类型

常用函数

函数	返回值	描述
ceil/floor(x)	整数	向上/下取整
mod(x,y)	整数	求x除以y的余数
round(x[,y])	整数	四舍五入，y不填时y=0，y大于0时，对小数部分操作，小于0时对整数
trunc(x[,y])	整数	直接截取，y不填时y=0，y大于0时，对小数部分操作，小于0时对整数

代码示例

select 
	round(7.816, 2),	-- 7.82
	round(7.816),		-- 8
	round(76.816, -1)	-- 80
	round(72.816, -1)	-- 70
from dual;

select 
  trunc(7.816, 2),		-- 7.81
  trunc(7.816),			-- 7
  trunc(76.816, -1)		-- 70
from dual;

4.3 日期类型函数

日期类型函数是操作日期、时间类型的相关数据，返回日期时间类型或数字类型结果

三个日期的操作公式

• 日期 + 数字 = 日期（表示若干天之后的日期）
• 日期 - 数字 = 日期（表示若干天之前的日期）
• 日期 - 日期 = 数字（天数）

常用函数

函数	返回值	描述
add_months(d,m)	日期	在指定日期上加上多少月，为负数表示减去的月份数
last_day(d)	日期	返回指定r日期的当前月份的最后一天日期
months_between(d1,d2)	数值	两个日期之间月份

示例代码

select months_between(to_date('2018-11-12', 'yyyy-mm-dd'),
                      to_date('2017-11-12', 'yyyy-mm-dd')) as zs, 	-- 12
       months_between(to_date('2018-11-12', 'yyyy-mm-dd'),
                      to_date('2017-10-11', 'yyyy-mm-dd')) as xs, 	-- 13.0322580645161
       months_between(to_date('2017-11-12', 'yyyy-mm-dd'),
                      to_date('2018-10-12', 'yyyy-mm-dd')) as fs 	-- 11
from dual;

4.4 类型转换函数

转换函数是进行不同数据类型转换的函数

常用函数

函数	返回值	描述
to_char(x[,f])	字符串	把字符串或时间类型x按格式f进行格式化转换为字符串
to_date(x[,f])	日期	把字符串x按照格式f进行格式化转换为时间类型
to_number(x[,f])	数值	把字符串x按照格式f进行格式化转换为数值类型

常用格式

数值格式

**参数 **	**示例 **	**说明 **
9	999	指定位置返回数字
.	99.9	指定小数点的位置
，	99,9	指定位置返回一个逗号
$	$99.9	指定开头返回一个美元符号
L	L99.9	指定开头返回一个本地符号
EEEE	9.99EEEE	指定科学计数法

日期时间格式

参数	示例	说明
yyyy	yyyy，yyy，yy，y	年
mm	mm，MM	月
dd	dd，DD	日
hh	hh，hh24	时
mi	mi	分
ss	ss	秒

示例代码

-- 输出当前时间（字符串）
select to_char(sysdate,'yyyy-mm-dd') from dual; -- 2021-07-21

-- 格式化数字格式
select to_char(123.46,'999.9') from dual; 		-- 123.5
select to_char(123.46,'L99,99') from dual;		-- ￥1,23

4.5 其它函数

null 值处理

nvl 函数，当值（不限类型，值只处理null值）为null时，使用指定默认值

select e.id,nvl(e.name,'空'),nvl(e.age,18),nvl(e.logtime,sysdate) from emp;

5. 序列 SEQUENCE

序列(sequence)：是一种用于自动生成唯一数字的数据库对象。主要用于提供主键值

5.1 创建序列

创建序列的语法格式

CREATE SEQUENCE 序列名 -- 序列名
[INCREMENT BY n] -- 每次增长的数值（步长），默认值为1（n为正数，则自增；n为负数，则自减）
[START WITH n] -- 从哪个值开始（初始值），默认值为1
[MAXVALUE n | NOMAXVALUE] -- 默认值为 NOMAXVALUE
[MINVALUE n | NOMINVALUE]-- 默认值为 NOMINVALUE
[CYCLE | NOCYCLE] -- 是否循环，默认值为NOCYCLE
[CACHE n | NOCACHE] -- 是否缓存，默认为不缓存

代码示例

创建序列【task】，初始值【1000】，最大值【9999】，增量【1】，缓存值【10个】

create sequence task
increment by 1
start with 1000
maxvalue 9999
cache 10;

5.2 使用序列

当使用序列时，必须通过伪列NEXTVAL和CURRVAL来引用序列，注意的是刚创建的序列是没有当前值的

• NEXTVAL用于引用返回下一个序列值
• CURRVAL用于引用返回当前序列值

select task.nextval from dual;    -- 序列下一个值
select task.currval from dual;    -- 序列当前值

5.3 删除序列

drop sequence 序列名;

5.4 注意事项

如果指定cache值，可提高访问效率，但是使用cache也会出现跳号的可能，即序列出现缺口

序列在下列情况下回出现序列缺口（裂缝）：回滚、系统异常、多个表同时使用同一序列

6. 游标 CURSOR

游标是SQL的一个内存工作区，由系统或者用户以变量的形式定义，用于临时存储从数据库中提取的数据块，通俗的来讲，游标就是一个结果集

游标的类型分为显式游标和隐式游标，这里将的是显式游标

6.1 显式游标

显式游标处理的四个步骤

• 定义游标：cursor cursor_name[(parameter_name datatype)] is select_statement

• 打开游标：open cursor_name

• 提取数据：fetch cursor_name into variable1[, variable2, …]

• 关闭游标：close cursor_name

FETCH语句的说明：把当前指针指向的记录返回，将指针指向下一条记录

显式游标的四个属性

• %FOUND：该属性用于检测游标结果集是否存在数据，如果存在数据，返回TRUE

• %NOTFOUND：该属性用于检测结果集是否存在数据，如果不存在数据，返回TRUE

• %ISOPEN：该属性用于检测游标是否已经打开，如果已经打开返回TURE

• %ROWCOUNT：该属性用于返回已经提取的实际行数

6.2 代码示例

示例一：无参数游标

DECLARE
    -- 定义游标
    CURSOR emp_cursor IS SELECT empno,ename FROM emp;
    v_empno emp.empno%TYPE;
    v_ename emp.ename%TYPE;
BEGIN
    -- 打开游标，执行查询
    OPEN emp_cursor;
    -- 提取数据
    LOOP
     FETCH emp_cursor INTO v_empno, v_ename;
     DBMS_OUTPUT.PUT_LINE('员工号：' || v_empno || '，姓名' || v_ename);
     -- 没有记录时退出循环
     EXIT WHEN emp_cursor%NOTFOUND;
    END LOOP;
    -- 关闭游标
    CLOSE emp_cursor;
END;

示例二：参数游标

参数游标是指带有参数的游标，通过使用参数游标，使用不同参数值可以生成不同的游标结果集

CURSOR cursor_name (parameter_name datatype) IS select_statement;
OPEN cursor_name (parameter_value);

代码演示：显示10号部门的所有员工

DECLARE 
	CURSOR emp_cursor(dno NUMBER) IS 
	SELECT empno,ename FROM emp WHERE deptno = dno;
BEGIN
	FOR emp_record IN emp_cursor(10) LOOP
		DBMS_OUTPUT.PUT_LINE('员工号：' || v_empno || '，姓名' || v_ename);
	END LOOP;
END;

示例三：使用FOR循环的游标

当使用游标FOR循环时，Oracle会隐含地打开游标，提取数据并关闭游标

DECLARE 
	CURSOR emp_cursor IS SELECT empno,ename FROM emp;
BEGIN
	FOR item IN emp_cursor LOOP 
		DBMS_OUTPUT.PUT_LINE('员工号：' || item.empno || '，姓名' || item.ename);
	END LOOP;
END;

BEGIN
	FOR item IN (SELECT empno,ename FROM emp) LOOP 
		DBMS_OUTPUT.PUT_LINE('员工号：' || item.empno || '，姓名' || item.ename);
	END LOOP;
END;

7. 异常 EXCEPTION

7.1 异常处理

异常处理是为了提高程序的健壮性，使用异常处理部分可以有效地解决程序正常执行过程中可能出现的各种错误，使得程序正常运行

异常处理的语法格式

EXCEPTION 
	WHEN first_exception THEN 
		statement1;
		......
	WHEN second_exception THEN 
		statement1;
		......
	WHEN OTHERS THEN
		statement1;
		......

异常处理代码的PL/SQL块中的位置

DECLARE
    -- 声明部分--声明变量、常量、复杂数据类型、游标等
BEGIN
    -- 执行部分--PL/SQL语句和SQL语句
EXCEPTION
	-- 异常处理部分,处理运行错误
END; -- 块结束标记

7.2 异常的分类

预定义异常

指由PL/SQL所提供的系统异常，Oracle提供了20多个预定义异常，每个预定义异常对应一个特定的Oracle错误，当PL/SQL块出现这些Oracle错误时，会隐含地触发相应的预定义异常；

对于预定义异常情况的处理，无需在程序中定义，只需要在PL/SQL块中的异常处理部分，直接引用相应的异常情况名，并对其完成相应的异常错误处理即可

非预定义异常

用于处理预定义异常所不能够处理的ORACLE错误，此种异常需要在程序中定义；

个人理解，为预定义异常起了个别名

非预定义异常的处理步骤：

• 在PL/SQL块中定义部分定义异常情况：exname EXCEPTION

• 将其定义好的异常情况与标准的ORACLE错误联系起来：PRAGMA EXCEPTION_INIT(exname,excode)

• 在PL/SQL块的异常情况处理部分对异常情况做出相应的处理

自定义异常

自定义异常的处理步骤：

• 在PL/SQL块中定义部分定义异常情况：exname EXCEPTION
• 引发异常：raise exname
• 在PL/SQL块的异常情况处理部分对异常情况做出相应的处理

7.3 代码示例

示例一：预定义异常

示例代码：根据输入的工资，查询员工的姓名，并输出员工的姓名以及工资

declare
    v_name emp.ename%TYPE;
	v_sal emp.sal%TYPE := &salary;
begin
	select ename into v_name from emp where sal = v_sal;
	DBMS_OUTPUT.put_line(v_name || '的工资是：' || v_sal);
exception
	when NO_DATA_FOUND then
		DBMS_OUTPUT.put_line('没有该工资的员工');
	when TOO_MANY_ROWS then
        DBMS_OUTPUT.put_line('多个员工具有该工资');
    when OTHERS then
		DBMS_OUTPUT.put_line('其他错误');
end;

示例二：非预定义异常

declare
    -- 1.定义非预定义异常的标识符
	e_fk exception;
	-- 2.把Oracle错误和异常信息建立关联
	pragma exception_init(e_fk, -2292);
begin
		delete from dept where deptno = &deptno;
exception
	when e_fk then
		-- 3.捕捉并处理异常
		dbms_output.put_line('此部门下有员工，不能删除！');
	when OTHERS then
		dbms_output.put_line(SQLCODE || '###' || SQLERRM);
end;

示例三：自定义异常

declare
    -- 1.定义异常
    e_no_result exception;
begin
	update emp set sal = sal + 100 where empno = 1;
		if sql%notfound then
			-- 2.引发因此
			raise e_no_result;
        else
            commit;
        end if;
exception
	-- 3. 处理异常
	when e_no_result then
		dbms_output.put_line('数据更新失败！');
	when others then
		dbms_output.put_line('其他错误');
 end;

8. 触发器 TRIGGER

提示：以下只涉及DML触发器，其它另行参阅

8.1 DML触发器

DML触发器是指基于DML操作所建立的触发器，可用于实现数据安全保护、数据审计、数据完整性、参照完整性、数据复制等功能

DML触发器类型

• 语句触发器：在指定操作语句之前或者之后执行一次，不管这条语句影响了多少行

• 行触发器：触发语句作用的每一条记录都被触发，在行级触发器中使用:old和:new伪记录变量，识别值的状态

说明：:old表示操作该行之前，这一行的值；:new 表示操作该行之后，这一行的值；在SQL语句和PLSQL语句中，伪记录变量需要加上冒号，而在WEHN这样的限制性条件语句当中，则不需要

创建DML触发器的语法格式

CREATE [OR REPLACE] TRIGGER trigger_name 
{BEFORE | AFTER}
{DELETE | INSERT | UPDATE [ OF 列名]}
ON 表名
	[FOR EACH ROW [WHEN (条件）])
PLSQL块

8.2 代码示例

示例一：语句触发器，实现数据安全保护（数据的安全性检查）

需求：禁止在休息日（周六、周日）改变emp表的数据

create or replace trigger emp_trigger
before
insert or update or delete
on emp
begin
	if to_char(sysdate, 'day') in ('星期六','星期日') then
		RAISE_APPLICATION_ERROR(-20006, '不能在休息日改变员工信息！');
	end if;
end;

示例二：行（记录）触发器，实现数据审计

需求：记录删除的员工姓名，删除时间

create or replace trigger emp_trigger
after
delete
on emp
begin
	insert info emp_log(uname,udeltime)
	values(:old.ename,sysdate);
end;

需求：员工年龄是否合法

create or replace trigger emp_trigger
before
update
or age
on emp
for each row
when (new.age < 18 or new.age > 100)
begin
	RAISE_APPLICATION_ERROR(-20028, '年龄不合法');
end;

需求：级联更新DEPT表的主键以及EMP表的外部键列

create or replace trigger emp_trigger
after
update
or deptno
on emp
for each row
begin
	UPDATE emp SET deptno = :new.deptno WHERE deptno = :old.deptno;
end;

9. 存储过程

9.1 语法结构

存储过程包含三部分：过程声明，执行过程部分，存储过程异常（可选）

• is 和 as 使用一样，并无太大区别，推荐无声明变量时使用 is，有声明变量使用 as
• 声明存储过程的语句最后（end）推荐以 存储过程名 结束
• 定义变量时需要指定值范围，定义存储过程参数时不需要
• into 关键字，把左侧结果赋给右侧变量
• in 表示输入参数，是参数的默认模式，不写默认 in
• out 表示输出参数，只能在过程体内部赋值

方式一：创建存储过程，无变量声明

create procedure 存储过程名 is
begin
  -- 执行的操作
end 存储过程名;

方式二：创建或替换存储过程，有变量声明

create or replace procedure 存储过程名 as
  变量名 类型;
begin
  -- 执行的操作
end;

方式三：创建有输入参数的存储过程

create or replace procedure 存储过程名(参数名 in 类型) is
begin
  -- 执行的操作
end demo;

create or replace procedure 存储过程名(参数名 类型) is
begin
  -- 执行的操作
end demo;

9.2 无参存储过程

无参，无变量，无执行操作（空存储过程）

create procedure demo2 as
begin
  null
end;

无参，无变量

create or replace procedure demo is
begin
  dbms_output.put_line('li2');
end;

无参，有变量，手动赋值

create or replace procedure demo as
  v_id number(4);
begin
  v_id := 1;
  select * from libai.emp e where e.eid = v_id;
end demo;

无参，有变量，查询赋值

create or replace procedure demo as
  v_count number(4);
begin
  select count(e.eid) into v_count from libai.emp e;
  dbms_output.put_line(v_count);
end demo;

9.2 有参存储过程

输入参数

create or replace procedure demo(nv in varchar2) is
begin
  dbms_output.put_line(nv);
end demo;

输出参数

create or replace procedure demo(nv out varchar2) as
begin
  nv := 'libai';
end demo;

输入，输出参数

create or replace procedure demo(pa varchar2,nv out varchar2) as
begin
  nv := pa || 'libai';
end demo;

9.3 异常错误处理

create or replace procedure 存储过程名 as
begin
  -- 执行的逻辑
  exception
    when too_many_rows then dbms_output.put_line('返回值多于1行');
    ...
    when others then dbms_output.put_line('未知异常');
end 存储过程名;

异常列表

异常	原因
ACCESS_INTO_NULL	未定义对象
CASE_NOT_FOUND	CASE 中若未包含相应的 WHEN ，并且没有设置
NO_DATA_FOUND	使用 select into 未返回行，或应用索引表未初始化的
TOO_MANY_ROWS	执行 select into 时，结果集超过一行
ZERO_DIVIDE	除数为 0
VALUE_ERROR	赋值时，变量长度不足以容纳实际数据

还有跟多异常，更多另行查询

9.4 调用存储过程

调用无参存储过程

begin
  demo();
end;

调用，输入参数存储过程

begin
  demo('libai');
end;

调用，输出参数存储过程

declare
  v_nv varchar2(20);
begin
  demo(v_nv);
  dbms_output.put_line(v_nv);
end;

调用，输入/输出参数存储过程

declare
  v_nv varchar2(20);
begin
  demo('name:',v_nv);
  dbms_output.put_line(v_nv);
end;

9.5 删除存储过程

drop procedure 存储过程名;

10. 自定义函数

Oracle 中函数必须有返回值，且只有一个

10.1 函数定义

无参（函数不可加括号）

create or replace function show return varchar2 is
begin
  dbms_output.put_line('无参');
  return 'ok';
end;

有参

create or replace function show(p_val varchar2, p_val2 number)
  return number is
  l_id number := 2;
begin
  dbms_output.put_line('有参-' || p_val);
  return p_val2 + l_id;
end;

11. 包（package）

包可以将任何出现在块声明的语句(过程,函数,游标,游标,类型,变量)放于包中,相当于一个容器.将声明语句放入包中的好处是:用户可以从其他PL/SQL块中对其进行引用,因此包为PL/SQL提供了全程变量

注意的是，包头和包体的命名要一模一样

11.1 定义-包头

语法结构

create or replace package 包名 is
  -- 过程,函数等
end 包名;

示例代码

create or replace package tesk is
  -- 定义函数
  function show(p_val varchar2) return varchar2;
  -- 定义存储过程
  procedure print(p_val varchar2, o_id out number);
end tesk;

11.2 实现-包体

语法结构

create or replace package body 包名 is
  -- 函数，过程实现
end 包名;

示例代码

create or replace package body tesk is
  function show(p_val varchar2) return varchar2 is
  begin
    dbms_output.put_line(p_val);
    return p_val;
  end;

  procedure printl is
  begin
    dbms_output.put_line('ws');
  end;
end tesk;

11.3 调用执行

内部调用：即在指的包里调用，直接调，不用写包名

外部调用：包名.函数，或者包名.存过

select tesk.show('libai') from dual;

begin
  tesk.printl();
end;

12. Oracle 用户

12.1 初始用户

• SYS：数据库中具有最高权限的数据库管理员，可以启动、修改、关闭数据库，拥有数据字典
• SYSTEM：是一个辅助的数据库管理员，不能启动和关闭数据库，但可以进行其他一些管理工作，如创建用户、删除用户等
• PUBLIC：是一个用户组，数据库中任何一个用户都属于该组成员；要为数据库中每个用户都授予某个权限，只需要把权限授予 PUBLIC 即可

12.2 登录方式

有两种登录方式，一：用户名+密码，二：用户名+角色

用户名+角色（以超级管理员角色登录）

sqlplus / as sysdba

用户名+密码（普通用户登录）

sqlplus zhangsan/123

12.3 锁定/解锁用户

以 sys 超级用户名，dba 角色，即超级管理员身份操作

查看当前用户

show user

解锁用户

alter user 用户名 account unlock;

锁定用户

alter user 用户名 account lock;

设置，重置密码

alter user 用户名 identified by 密码;

普通用户修改密码

password

13. 扩展补充

13.0 对象管理

1. 查询用户拥有对象

查询当前用户下的所有对象，使用tab表

select * from tab;

2. 调用系统命令

在SQLPLUS工具中执行系统命令时加上 host

host cls;	-- 清屏
host ipconfig;	-- ip配置信息

13.1 基本知识

0. SQL语句介绍

常用的SQL语句大致可以分为五类：

• 数据定义语言（DDL），包括 CREATE（创建）命令、 ALTER（修改）命令、 DROP（删除）命令等
• 数据操纵语言（DML），包括 INSERT（插入）命令、 UPDATE（更新）命令、 DELETE（删除）命令、 SELECT … FOR UPDATE（查询）等
• 数据查询语言（DQL），包括基本查询语句、 Order By 子句、 Group By 子句等
• 事务控制语言（TCL），包括 COMMIT（提交）命令、 SAVEPOINT（保存点）命令、ROLLBACK（回滚）命令
• 数据控制语言（DCL）， GRANT（授权）命令、 REVOKE（撤销）命令

1. 引用数据类型

引用数据类型是PL/SQL程序语言特有的数据类型，是用来引用数据库当中的某一行或者某个字段作为数据类型的声明，其中有两种引用类型：%TYPE 和 %ROWTYPE

获取表中列的类型

引用数据库中表的某列的类型作为某变量的数据类型，或直接引用PL/SQL程序中某个变量作为新变量的数据类型

declare
	name stu.stuname%type;	-- 声明变量 name，类型为 stu 表，stuname 列的类型
begin
  ...
exception
  ...
end;

用数据库表中的一行作为数据类型

PL/SQL程序引用数据库表中的一行作为数据类型，即 RECORD 类型（记录类型）表示一条数据记录

declare
  v_row libai.emp%rowtype;
begin
  select top 1 * into v_row from libai.emp;
  dbms_output.put_line(r_row.id);
end;

2. 单引号,双引号

• 别名使用双引号
• 在 oracle 中单引号表示字符串类型或者是日期类型

3. 伪表，哑表

伪表，没有列，没有数据

select sysdate from dual;

4. 字符串拼接

使用 || 拼接字符串

select ename||'f' from libai.emp;

5. 注释

-- 单行注释
/*
   多行注释
   多行注释
*/

6. 转义符号

如果有内容中有特殊记录，则需要使用转义字符 \

select * from emp where ename like '%\%'
select * from emp where ename like '%\__'

7. 查询优化

统计时不推荐使用 * 号

* 号适用于表字段较少的情况下，如果字段较多，推荐使用某一个字段

14. 常用示例

14.1 创建自动增长列

方式一：使用序列生成序号

创建序列,从10000开始计数,增量为1

create sequence USER_ID
minvalue 1
maxvalue 9999999999999999999999999999
start with 10000 --从10000开始生成序列
increment by 1 --增量为1
cache 20; --预存20个序列值在内存中,这样可以提高访问序列的速度

在建完序列后直接在插数据的时候在主键的地方直接使用user_id.nextval代替

insert into test values(user_id.nextval,'zhangyi'23);

方式二：使用序列+触发器

创建序列,从10000开始计数,增量为1

create sequence USER_ID
minvalue 1
maxvalue 9999999999999999999999999999
start with 10000 --从10000开始生成序列
increment by 1 --增量为1
cache 20; --预存20个序列值在内存中,这样可以提高访问序列的速度

创建触发器，为表创建一个before insert的触发器,在插入之前将表的主键设置为上面的序列值

CREATE OR REPLACE TRIGGER "USER_ID_TRIGGER" BEFORE
INSERT ON tbluser FOR EACH ROW --一定要加上For each row,表示该触发器适应于每一条记录
declare   
      mid number;   
begin   
      select user_id.nextval into mid from dual; --user_id.nextval是获得上面定义的序列ORDER_ID的下一个值
      :new.id:=mid; --将得到的序列值赋给表user的主键id, 可以用":new"引用将要插入的一行数据
            --SELECT  USER_ID.Nextval INTO :new.ID FROM dual;
end;

当执行插入记录的时候，会调用上面创建的触发器将序列值赋值给主键ID

14.2 常用函数

DECODE 函数：相当于 WHEN THEN 用法

-- 语法
decode(条件,值1,返回值1[,值2,返回值2,…值n,返回值n,缺省值])

示例一：翻译值

select t.name,decode(t.sex, '1', '男生', '2', '女生', '其他') as sex from user t;

示例二：比较大小（sign(value) 函数会根据 value 的值为0，正数，负数，分别返回0，1,-1）

select t.name,
       decode(sign(t.age - 20),1,'20以上',-1,'20以下',0,'正好20','未知') as age from user t;

示例三：判断是否为 null

select t.name,decode(t.sex,NULL,'暂无数据',t.sex) as sex from user t;

WM_CONCAT 函数：用于列转行，逗号分隔

示例一：行转列

-- 查询订单号，和订单的商品
select 
	o.orderNo,
	wm_concat(select s.shopName from tb_shop s) shop
from tb_order o;
-- 结果
/*
	orderNo		shop
	1001		苹果13
	1002		华为P40,小米X
*/

示例二：分组合并

-- 查询男女姓名
select sex,wm_concat(name) name from user group by sex;
-- 结果
/*
	sex		name
	男		李白
	女		貂蝉,王昭君
*/