Oracle学习
一、环境：安装Oracle数据库与操作Oracle数据库软件PLSQL
- 什么是关系型数据库：数据库存储数据就像一个表格一样，比如：一个人的信息就是一个表格，我们的人的信息是有很多的，数据库对每一个人的信息都进行分类并且像词典一样方便查询与修改。关系型数据库就是把信息用表格的形式进行存储。这里要注意：我们数据库的表格与Excle的表格是有绝对的区别的，数据库的单元格中不仅仅只能存储文字，还能够存储图片等二进制的数据。
- Oracle数据库的安装。
- 数据库软件PLSQL安装。
- 配置PLSQL：登录后默认自动选中My Objects、常用设置列表（将table放到第一位）、双击即显示表数据、解决双击开打表报错、自定义快捷键…
二、概述
2.1、Oracle的体系结构
2.2、Oracle常见数据类型
三、创建用户并进行简单DDL语句操作
- PLSQL或语句建表和用户
- 用户管理常用命令：
1.创建用户：create userTest 用户名 identified by 密码--密码不能以数字开头
2.删除用户：drop userTest 用户名 cascade;
3.修改密码：alter userTest 用户名 identified by 新密码;
4.锁定用户：alter userTest 用户名 account lock;
5.解锁用户：alter userTest 用户名 account unlock;- 表空间操作：
1.创建表空间：create tablespace lxf--表空间名logging datafile **\**\**\Oracle\oradata\orcl\lxf.dbf'--（注释：这里是表空间数据文件存储位置，文件名最好与表空间一致） size 10m autoextend on next 50m maxsize 20480m extent management local;--设置表空间大小，大小10m自动增加50m直到最大20480m.
2.删除表空间：drop tablespace '表空间名' INCLUDING CONTENTS--指删除表空间中的segments。
3.drop tablespace '表空间名' INCLUDING CONTENTS AND DATAFILES--指删除segments和datafiles。
4.drop tablespace '表空间名' CASCADE CONSTRAINTS--删除所有与该空间相关的完整性约束条件。- 基本语句操作：
- 查询
select * from userTest;--查询userTest表的所有字段
select userTest.name，userTest.age from userTest;--查询userTest表的name和age字段- 添加
insert into userTest(id,name,sex,age) values(1,'刘一手','男',6);
insert into userTest values(1,'刘一手','男',6);- 修改
update userTest set name='刘二手',sex='女',age=55 where id=1;-->修改指定的某条数据的某些字段。
update userTest set name='刘三手',sex='女',age=18;--此语句会将所有用户的名字、性别和年龄进行修改- 删除
delete from userTest where id=3;--删除id为3的一条或多条数据。
delete from userTest;--直接删除userTest用户表中所有数据- 查看表结构
DESC userTest;--显示表结构。只能在命令窗口使用(cmd)- 创建自增ID(一个字段只要创建一次)
CREATE SEQUENCE STUDENT_ID_SEQ --序列名,自己创建的名字
MINVALUE 1 --最小值，这里是1
NOMAXVALUE --最大值，这里是没有最大值
INCREMENT BY 1 --增加间隔是1
START WITH 1 --从几开始，这里是从1开始
NOCACHE;举例：新建一个表并给这个表加上自增id
--建表
create table department(
id number primary key,
department_name varchar2(20)
)
--创建一个序列
create sequence d_id
start with 1
increment by 1;
--创建一个触发器
create or replace trigger tri_department
before insert
on department
for each row
begin
select d_id.nextval into :new.id from dual;
end;- 复制表
create table userTest2 as select * from userTest;--复制userTest表及其内容
create table userTest2 as select * from userTest where 1=2;--只复制表的结构不复制表的数据；- 事务控制语言
- COMMIT;提交事务。
- ROLLBACK [TO savepoint];回滚事务
- SAVEPOINT ; 设置保存点位置 。
- 示例：
--在userTest中先添加8个数据，在进行以下操作：
DELETE FROM userTest WHERE ROWNUM<=2;--一次性删除两条数据
SAVEPOINT a1;--设置一个保存点，保存之前的操作，a1自己设定的名字
DELETE FROM userTest WHERE ROWNUM<=2;
SAVEPOINT a2;
DELETE FROM userTest WHERE ROWNUM<=2;
SAVEPOINT a3;
ROLLBACK TO a1;--让事物回滚到a1，a1之后的所有操作无效
COMMIT;--提交所有事物四、Oracle约束
约束详解
4.1、查看表所有的约束
select constraint_name,table_name,constraint_type from userTest_constraints[where table_name='表名']4.2、主键约束（primary key
create table student(id number,name varchar2,constraint pk_userTest_id primary key(id));--方式一，在创建表的时候创建
alter table student add constraint pk_userTest_id primary key(id);--方式二（创建表后添加）4.3、外键约束（foreign key）
create table teacher(id number,name varchar2,constraint fk_teacher_id foreign key(id) references student(id));--方式一，在创建表的时候创建
alter table teacher add constraint fk_teacher_id foreign key(id) references studnet(id);--方式二（创建表后添加）4.4、检查约束（check）
create table userTest(id number,name varchar2,age number,constraint ck_userTest_age check(age between 0 and 100))--方式一，在创建表的时候创建
alter table userTest add constraint ck_userTest_age check(age>0 and age<100)4.5、是否为空（null|not null）
create table person(id number,name varchar2(20),constraint nn_person_id id not null)--方式一，在创建表的时候创建
alter table person modify id constraint nn_person_id not null;--方式二（创建表后添加）4.6、唯一键(unique)
create table employee(id number,name varchar2(20),constraint uk_employee_name unique(name))--方式一，在创建表的时候创建
alter table employee add constraint uk_employee_name unique(name);--方式二（创建表后添加）4.7、约束命名规范
非空约束 NN_表名_列名
唯一约束 UK_表名_列名
主键约束 PK_表名
外键约束 FK_表名_列名
条件约束 CK_表名_列名
默认约束 DF_表名_列名
适当缩写，简介明了4.8、修改约束名
ALTER TABLE employee RENAME CONSTRAINT uk_employee_name TO uk_em_name;--举例：修改4.6的约束名4.9、禁止约束
ALTER TABLE table_name DISABLE CONSTRAINT constaint_name [CASCAED];--CASCAED用于指定级联禁止从表的外部键4.10、激活约束
ALTER TABLE table_name ENABLE CONSTRAINT constraint_name;4.11、删除约束
ALTER TABLE table_name DROP CONSTRAINT constraint_name |PRIMARY KEY;
alter table employee drop constraint uk_em_name;--举例：删除4.8修改后的约束名五、高级查询
5.1、新建以下几个表（5.2、5.3也用到了）
employee表（员工表）
e_id 员工编号 Number
e_name 员工姓名 varchar2(10)
e_job 员工职位 varchar2(10)
e_senior_id 员工的上级编号 Number
e_entry_time 员工入职时间 varchar2(20)
e_salary 员工薪水 Number
e_bonus 奖金 Number
d_dept_id 员工部门编号 Number
department表（部门信息表）
d_dept_id 部门编号 number
d_dept_name 部门名 varchar2(10)
d_dept_address 部门地址 varchar2(50)5.2、高级查询：内连接、左外连接、右外连接、完全外连接查询、交叉连接：
1.内连接：
select * from department d inner join employee e on d.d_dept_id = e.d_dept_id;2.左外连接：*
select *
from department d
left join employee e
on d.d_dept_id = e.d_dept_id;3.右外连接:
select *
from department d
right join employee e
on d.d_dept_id = e.d_dept_id;4.完全外连接:
select *
from department d
full join employee e
on d.d_dept_id = e.d_dept_id;5.交叉连接：
select *
from department d
cross join employee e
where d.d_dept_id = e.d_dept_id;select *
from department d
cross join employee e5.3、高级查询：子查询、比较操作符、逻辑操作符、集合操作符
1.子查询（查找一名与刘二手在同一部门的员工信息）：
select *
from employee e
where e.d_dept_id =
(select e.d_dept_id from employee e where e.e_name = '刘二手')2.比较操作符(模糊查询like-通配符:%表示匹配任意个字符_表示匹配单个字符)：
--查询姓刘的员工
select * from employee e where e.e_name like '刘%';--查询项目开头的工作名，且工作名为四个字
select * from employee e where e.e_job like '项目__';3.逻辑操作符:
--查询工资大于10000或者奖金大于500的员工
select *
from employee e
where e.e_salary > 10000
or e.e_bonus > 500--查询工资大于10000并且奖金大于500的员工
select *
from employee e
where e.e_salary > 10000
and e.e_bonus > 5004.集合操作符:
--union合并两次查询结果:不显示重复项
select *
from employee
where rownum <= 6
union
select *
from employee
where rownum <= 3--union all合并两次查询结果:显示重复项
select *
from employee
where rownum <= 6
union all
select *
from employee
where rownum <= 3--返回两个查询都有的行
--Intersect（交集）
select *
from employee
where rownum <= 3
Intersect
select *
from employee
where rownum <= 6
--返回大查询减去小查询都有的行
--Minus（减集）
select *
from employee
where rownum <= 3
Minus
select *
from employee
where rownum <= 65.4、高级查询：分组语句、case…when…语句
1.分组语句group by
select d_dept_id, count(*) from employee group by d_dept_id2.case…when…语句
简单应用：
l_user表：
select lu.name,
case
when sex = '男' then
'1'
when sex = '女' then
'2'
else
'其它'
end num_sex
from l_user lu高级应用一：
已知有以下数据：
国家 | 人口 |
中国 | 600 |
美国 | 100 |
加拿大 | 100 |
英国 | 200 |
法国 | 300 |
日本 | 250 |
德国 | 200 |
墨西哥 | 50 |
印度 | 250 |
根据这个国家人口数据，统计亚洲和北美洲的人口数量。应该得到下面这个结果：
州 | 人口 |
亚洲 | 1100 |
北美洲 | 250 |
欧洲 | 700 |
sql代码：
select sum(cc.population) 人口,
case cc.country
when '中国' then '亚洲'
when '日本' then '亚洲'
when '印度' then '亚洲'
when '加拿大' then '北美洲'
when '美国' then '北美洲'
when '墨西哥' then '北美洲'
when '英国' then '欧洲'
when '法国' then '欧洲'
when '德国' then '欧洲'
else '其他' end 州
from case_country cc
group by
case cc.country
when '中国' then '亚洲'
when '日本' then '亚洲'
when '印度' then '亚洲'
when '加拿大' then '北美洲'
when '美国' then '北美洲'
when '墨西哥' then '北美洲'
when '英国' then '欧洲'
when '法国' then '欧洲'
when '德国' then '欧洲'
else '其他' end;高级应用二：
有以下数据：
国家 | 性别 | 人口 |
中国 | 1 | 340 |
中国 | 2 | 260 |
美国 | 1 | 45 |
美国 | 2 | 55 |
加拿大 | 1 | 51 |
加拿大 | 2 | 49 |
英国 | 1 | 40 |
英国 | 2 | 60 |
按照国家和性别进行分组，得出结果如下：
国家 | 男 | 女 |
中国 | 340 | 260 |
美国 | 45 | 55 |
加拿大 | 51 | 49 |
英国 | 40 | 60 |
sql代码：
select t.country,
sum(case t.sex when '1' then t.population else 0 end) 男,
sum(case t.sex when '2' then t.population else 0 end) 女
from table_a t
group by t.country;总结：绝大多数情况下sum、avg、min、max与group by连用
六、Oracle函数
6.1、日期函数
SYSDATE：返回系统日期
select sysdate from dualADD_MONTHS(<d>,<i>)：d:日期,加上i个月后的新日期(i正可负)。
select ADD_MONTHS(sysdate, 2) from dualLAST_DAY(<d>)：返回日期d所在的月的最后一天。
select last_day(sysdate) from dualMONTHS_BETWEEN(<d1>,<d2>)：返回日期d1比d2大多少月数。
select months_between(to_date('2020-03-02', 'yyyy-mm-dd'),
to_date('2020-01-02', 'yyyy-mm-dd')) from dual
--注意：如果两个日期差的不是整数月，会显示小数EXTRACT:用于提取日期时间类型的特定部分。
select extract(month from sysdate) from dualROUND：四舍五入
select round(sysdate, 'year') from dual6.2、字符函数
1. 绝对值函数Abs(n) ：
Select abs(-7) from dual; --结果：72.向上取整函数Ceil(n) ：
Select Ceil(7.7) from dual; --结果：83.向下取整函数Floor(n) ：
Select Floor(7.7) from dual; --结果：7**4.取余函数Mod(m,n) **
Select Mod(8,3) from dual; --结果：2**5.四舍五入函数Round(m,n) **
Select Round(7.5) from dual; --结果：86.次方函数Power(m,n)
Select Power(2，3) from dual; --结果：8**7.平方根函数Sqrt(n) **
Select Sqrt(4) from dual; --结果：26.3、转换函数
- TO_CHAR(xxx,‘format’)：将xxx转换成指定字符串。
Select to_char(sysdate,'yyyy-mm-dd') from dual;--举例输出：2020-06-25
Select to_char(123456,'$999,999') from dual;--举例输出：$123.456
SELECT TO_CHAR(sysdate,'YYYY"年"fmMM"月"fmDD"日" HH24:MI:SS') FROM dual;--举例输出：2020年06月25日 15:37:01- TO_NUMBER(xxx)：将字符串xxx转换成数字。
Select to_number('123') from dual;--输出123数字- TO_DATE(xxx,‘format’)：将字符串c转换成日期。
Select to_date('2009-7-8', 'yyyy-mm-dd') from dual--输出：2009/7/86.4、其他函数:
- NVL(x1,x2):如果x1为空返回x2，否则返回x1。
SELECT NVL(1, 0) FROM dual; --返回1
SELECT NVL(null, 0) FROM dual; --返回0- NVL2(x1,x2,x3):如果x1为空返回x3，否则返回x2.
SELECT NVL2(1, 0, 1) FROM dual; --返回0
SELECT NVL2(null, 0, 1) FROM dual; --返回1- NULLIF(x1,x2):如果x1=x2返回空，否则返回x1.
SELECT nullif(1, 1) FROM dual; --返回空
SELECT nullif(9, 1) FROM dual; --返回9- decode() 相当于case…when…语句:
select decode(10, 10, 'AAA', 20, 'BBB', 30, 'CCC') 结果 from dual; --返回AAA
select decode(20, 10, 'AAA', 20, 'BBB', 30, 'CCC') 结果 from dual; --返回BBB
select decode(30, 10, 'AAA', 20, 'BBB', 30, 'CCC') 结果 from dual; --返回CCC
select decode(0, 10, 'AAA', 20, 'BBB', 30, 'CCC') 结果 from dual; --返回空- Sum求和函数
- AVG求平均值函数
- MIN求最小值函数
- Count(*)统计所有的行
- Count(列)统计指定列的非空值的个数
- Count(distinct 列)统计指定列的非空值去除重复值的个数
七、Oralce数据库事务、锁、数据库对象、视图view、索引 index
7.1、事务
- 概念：事务是最小的工作单元，该单元由对数据库的若干操作组成，这些操作要么整体成功，要么整体失败，从而保证数据的一致性。
- 典型事例：
银行转帐操作：
帐户A把一定数量的款项转到帐户B上，这个操作包括两个步骤，一个是从帐户A上把存款减去一定数量，
二是在帐户B上把存款加上相同的数量。这两个步骤要么都完成，要么都取消，
否则银行就会受损失。显然，这个转帐操作中的两个步骤就构成一个事务。- 事务的特征(ACID)：
- 原子性(A)：是不可再分的最小单元。
- 一致性©：指事务中的操作要么都成功要么都失败。
- 隔离性(I)：指当前的事务与其他未完成的事务是隔离的事务和事务之间 相互不干扰。
- 永久性(D)：指对事务发出COMMIT命令后，事务的效果是永久的。
- Oracle中的事务：
- 1次连接 = 1个会话 = 1个事务
- 遇到commit或rollback则提交或回滚所有未提交的操作
- 一旦提交，则无法回滚
7.2、锁
一、概念：
- 生活中的锁：锁的作用就是保护用户私有的空间。
- Oracle中的锁：
- 锁是数据库用来控制共享资源并发访问的机制。
- 锁用于保护正在被修改的数据。
- 直到提交或回滚了事务之后，其他用户才可以更新数据。
二、优点：
- 一致性：一次只允许一个用户修改数据
- 完整性：为所有用户提供正确的数据。如果一个用户进行了修改并保存，所做的修改将反映给所有用户
- 并行性：允许多个用户访问同一数据
三、分类：
- 行级锁：
- 对正在被修改的行进行锁定，其他用户可以访问除被锁定的行以外的行
- 在使用insert，delete，update，select…for upadte语句时，Oracle会自动应用行级锁
- Select…for update语句允许用户一次锁定多条记录进行更新
- 用户必须通过commit或rollback语句释放锁
- 表级锁：
- 将整个表锁定
- Lock table 表名 in share mode;
四、阻塞：
- 概念：一个会话如果更新了数据库中的记录，其他会话是无法立刻更新的，要等待对方提交或回滚之后才允许更新。
- 示例：
- 如果数据库中有两个表A 和B，每个表中都有一行记录。
- 打开两个会话，在会话A 中更新表A，并在会话B 中更新表B。
- 现在，如果在会话B 中更新表A，就出现会阻塞。如果会话A 提交或回滚，会话B 就能继续。
五、死锁：
- 概念：当两个事务相互等待对方完成任务时，就会出现死锁。
- 示例：继续刚才的示例（已经阻塞）：
- 如果再在会话A中，视图更新表B，将会导致死锁
- 这个时候，Oracle会自动检测死锁，并通过终止两个事务处理之一来解决问题（另一个仍将阻塞）。
- 死锁在Oracle中一般很少出现，除非人为因素。
六、数据库对象：Oracle数据库对象又称模式（用户）对象，数据库对象是逻辑结构的集合，最基本的数据库对象是表。其他数据库对象包括：
- 创建序列：初值=1，增量=1：
create sequence sq_test;- 获取序号值：序列名.nextval：
select sq_test.nextval from dual;- 结合表使用，生成自动增长列：
create table test
(tid number,tname varchar2(10));
--在insert操作时，获取序列值插入表中
insert into test values(sq_test.nextval,'&tname');- 创建复杂序列：
create sequence sq_demo
start with 100 --初始值一般使用1
increment by -5 --增量，负数就是降序，一般使用1
maxvalue 100 --最大值，nomaxvalue 没有最大值，一般使用无最大值
minvalue 5 --最小值，nominvalue 没有最小值，一般是1
cycle --循环产生序号，nocycle 不循环产生
cache 0; --预先分配的序号数- 获取序列当前值，序列名.currval：
select 序列名.currval from dual;
--必须在执行一次nextval后才能得到currval- 删除序列：
drop sequence sq_demo;七、视图view，从不同的角度向用户展示数据，视图可以视为“虚拟表”或“存储的查询”，创建视图所依据的表称为“基表”。
- 优点：
- 简化SQL语句，隐藏敏感数据
- 视图是1个虚拟表，是存储的查询结果集，是对基表数据的引用
- 可以对视图进行增删改操作，且成功的操作会影响基表数据
- 实际开发中，视图一般用于查询
- 注意：视图不能直接进行插入/更新/删除字段信息
创建视图：
create view simple_user(视图名) as select u.name,u.sex,u.age from user u--假设user有很多个字段，这里是挑出三个字段新建一个视图
select * from simple_user;--这样就可以单独查询这三个字段了，删除视图：
drop view view_emp;八、索引 index，用于提高查询效率、优化查询。
- 理解：
Mysql索引的作用（里面讲了索引的作用以及有缺点，Mysql的索引和Oracle的索引是相似的） - 创建索引的条件：
- 大数据量
- 一般给经常用于where条件，排序，分组的列创建索引
- 索引分类：1个列上只允许创建1个索引
- 标准索引：
create index 索引名 on 表名(列名);- 唯一索引，唯一索引确保在定义索引的列中没有重复值。主键、唯一键自动创建唯一索引。
create unique index 索引名 on 表名(列名);- 组合索引，组合索引是在表的多个列上创建的索引，用于多条件的组合查询
create index 索引名 on 表名(列名1,列名2...);- 基于函数的索引，在应用了函数的字段上创建索引
create index 索引名 on 表名(函数名(列名));
create index ix_hiredate on emp(to_char(hiredate,'mm'));- 反向键索引，反向键索引反转索引列键值的每个字节，将数据按字节形式反转
create index 索引名 on 表名(列名) reverse;
--用于连续增长的列（序列），均衡访问
10001 10001
10002 20001
10003 30001
10004 40001
10005 50001
--用于经常从后匹配的列
ename like '%三' >> '三%'- 位图索引，用于低基数列
create bitmap index 索引名 on 表名(列名);
--实际数据
张三 北京
李四 湖南
王五 湖北
赵六 北京
田七 湖南
王八 湖北
--位图形式
北京 湖南 湖北
张三 1 0 0
李四 0 1 0
王五 0 0 1
赵六 1 0 0
田七 0 1 0
王八 0 0 1- 删除索引：
drop index 索引名;八、存储过程、游标、子程序、包
8.1、PL/SQL块：
[declare
声明变量、游标;]
begin
可执行代码;
[exception
when 异常类型 then 异常处理代码;]
end;
--注意：如果要在可执行部分使用变量，则变量必须在声明部分先声明演示示例：
1.简单的输出语句
begin
dbms_output.put_line('Hello,Oracle!');
dbms_output.put('this is my world!');--输出不换行
dbms_output.new_line();--新建一行
dbms_output.put_line('&info');--由自己手动输入info，然后输出在控制台
end;
2.定义变量、赋值输出语句
declare
l_name varchar2(10);
l_age number(3);
l_sex varchar2(3):='男';--赋值
begin
l_name:='lxf';--这里也可以赋值
l_age:='&年龄';--手动输入
dbms_output.put_line('姓名：'||l_name);
dbms_output.put_line('年龄：'||l_age);
dbms_output.put_line('性别：'||l_sex);
end;
3.根据员工编号查询员工编号和姓名并输出
declare
e_empno number(4);
e_ename varchar2(10);
begin
select e.empno,e.ename into e_empno,e_ename from emp e where e.empno='7788';
dbms_output.put_line('员工编号:'||e_empno);
dbms_output.put_line('员工姓名:'||e_ename);
end;
4.根据员工编号查询员工编号和姓名并输出,定义类型的时候，获取它在表格中的类型
declare
e_empno emp.empno%type;
e_ename emp.ename%type;
begin
select e.empno,e.ename into e_empno,e_ename from emp e where e.empno='&员工编号';
dbms_output.put_line('员工编号:'||e_empno);
dbms_output.put_line('员工姓名:'||e_ename);
end;5.将一条数据存储并打印
declare
e_emp emp%rowtype;
begin
select * into e_emp from emp e where e.empno='&员工编号';
dbms_output.put_line('员工编号:'||e_emp.empno);
dbms_output.put_line('员工姓名:'||e_emp.ename);
dbms_output.put_line('员工工资:'||e_emp.sal);
end;
6.使用异常捕获
declare
e_emp emp%rowtype;
begin
select * into e_emp from emp e where e.empno='&员工编号';
dbms_output.put_line('员工编号:'||e_emp.empno);
dbms_output.put_line('员工姓名:'||e_emp.ename);
dbms_output.put_line('员工工资:'||e_emp.sal);
exception
when no_data_found then
dbms_output.put_line('员工不存在!');
when others then
dbms_output.put_line('出错了');
end;
--输入一个不存在的员工编号,就会打印：员工不存在!
--输入不符合员工编号类型的值，就会打印:出错了boolean类型以及if…elseif…end if的使用
declare
v_bool boolean;
begin
v_bool:=false;
if(v_bool=null) then
dbms_output.put_line('v_bool是null');
elsif (v_bool=true) then
dbms_output.put_line('v_bool是true');
elsif (v_bool=false) then
dbms_output.put_line('v_bool是false');
end if;
end;8.日期时间类型：
declare
mydate date;
mytime timestamp;
begin
select sysdate into mydate from dual;
select sysdate into mytime from dual;
dbms_output.put_line(to_char(mydate,'yyyy-mm-dd'));
dbms_output.put_line(to_char(mydate,'yyyy-mm-dd HH:mi:ss'));
end;9.case…when语句
declare
score number(3):='&请输入成绩：';
grade varchar2(10);
begin
case
when score>=80 and score<=100 then grade:='优秀';
when score>=60 and score<80 then grade:='良好';
when score>=0 and score<60 then grade:='不及格';
else grade:='非法输入！';
end case;
dbms_output.put_line('输入成绩的等级为:'||grade);
end;10.loop…end loop循环语句
--打印1到10，exit跳出
declare
m_num number(3):=1;
begin
loop
dbms_output.put_line(m_num);
m_num:=m_num+1;
exit when m_num>10;
end loop;
end;
--打印1到10,while判断跳出
declare
m_num number(3):=1;
begin
while(m_num<=10)
loop
dbms_output.put_line(m_num);
m_num:=m_num+1;
end loop;
end;
--for循环打印1到10
begin
for m_num in 1..10
loop
dbms_output.put_line(m_num);
end loop;
end;8.2、游标
游标简介:
- 游标是PL/SQL程序中用来获取查询结果集的指针
- 游标能够逐行处理查询结果，以编程的方式访问数据
2.隐式游标：
- Oracle自动为增删改操作创建隐式游标，名字统一为SQL
- 用于获取最近的增删改操作对数据库的影响（必须在提交或回滚之前）
- 游标属性
- %rowcount 返回受影响的行数
- %found 如果影响了数据，则返回true否则返回false
- %notfound 如果没影响数据，则返回true否则返回false
- %isopen 如果游标已打开，则返回true否则返回false。（隐式游标的%isopen始终为false）
2.1.代码示例：
begin
update emp set sal=sal-100 where deptno=20;
if(SQL%isopen) then--默认关闭
dbms_output.put_line('游标已打开');
end if;
if(SQL%found) then
dbms_output.put_line('更新成功');
dbms_output.put_line('更新了'||SQL%rowcount||'条记录');
commit;--提交事务
elsif(SQL%notfound) then
dbms_output.put_line('更新失败');
rollback;--回滚
end if;
end;3.显式游标 cursor：
- 显式游标必须在PL/SQL块的声明部分手动定义
- 用于处理返回多行的查询结果集，方便用户以编程的方式逐行处理数据
- 游标属性
- %rowcount 返回当前游标对应的行号
- %found 如果fetch成功，则返回true否则返回false
- %notfound 如果fetch失败，则返回true否则返回false
- %isopen 如果游标已打开，则返回true否则返回false
代码示例：
--1.使用游标输出每个员工的姓名和薪水
declare
--定义两个存储数据的变量
e_name emp.ename%type;
e_sal emp.sal%type;
--定义一个显式游标。empCursor游标的名称。相当于ResultSet.
cursor empCursor
is
select e.ename,e.sal from emp e;--将查询结果集放入游标
begin
--如果游标没打开，打开游标，否则无法使用
if(empCursor%isopen=false) then
dbms_output.put_line('打开游标');
open empCursor;
end if;
--循环遍历游标
loop
fetch empCursor into e_name,e_sal;
dbms_output.put_line(empCursor%rowcount||'-'||e_name||'-'||e_sal);
exit when empCursor%notfound;
end loop;
--关闭游标
close empCursor;
end;
--2.遍历所有信息
declare
--定义存储所有信息的对象
e_emp emp%rowtype;
--定义一个显式游标。empCursor游标的名称。相当于ResultSet.
cursor empCursor
is
select * from emp e;--将查询结果集放入游标
begin
--如果游标没打开，打开游标，否则无法使用
if(empCursor%isopen=false) then
dbms_output.put_line('打开游标');
open empCursor;
end if;
--循环遍历游标
loop
fetch empCursor into e_emp;
dbms_output.put_line(empCursor%rowcount||'-'||e_emp.empno||'-'||e_emp.ename||'-'||e_emp.sal);--打印三个字段测试
exit when empCursor%notfound;
end loop;
--关闭游标
close empCursor;
end;
--3.for遍历
declare
--定义一个显式游标。empCursor游标的名称。相当于ResultSet.
cursor empCursor
is
select * from emp;--将查询结果集放入游标
begin
--循环遍历游标
for e_emp in empCursor
loop
dbms_output.put_line(e_emp.empno||'-'||e_emp.ename||'-'||e_emp.sal);--打印三个字段测试
end loop;
end;
--4.使用带参数的游标：提高游标的灵活性
declare
--定义游标的时候，给游标传递参数
cursor empCursor(e_deptno emp.deptno%type)
is
select * from emp e where e.deptno=e_deptno;
begin
for e_emp in empCursor('&请输入部门编号：')
loop
dbms_output.put_line(e_emp.ename||'-'||e_emp.job||'-'||e_emp.sal);
end loop;
end;
--5.使用游标更新数据：
declare
money number;
cursor myCursor
is
select * from emp for update nowait;
begin
for r in myCursor
loop
if(r.deptno=10) then--如果是10部门工资加100
money:=100;
elsif(r.deptno=20) then--如果是20部门工资加200
money:=200;
elsif(r.deptno=30) then--如果是30部门工资加300
money:=300;
end if;
update emp e set e.sal=e.sal+money where e.empno=r.empno;--更新当前行
dbms_output.put_line('更新成功');
end loop;
end;3.REF游标（动态游标）：
- 用于处理运行时才能确定的查询结果集（接收用户输入）
- REF游标必须在PL/SQL块中声明游标类型和游标变量
代码示例：
--根据用户输入的表名输出对应的信息
declare
tname varchar2(10);--动态表名参数
sqlstr varchar2(100);--动态sql字符串
r dept%rowtype;
--1.声明ref动态游标类型
type mytype is ref cursor;
--2.声明ref游标变量
myCursor mytype;
begin
tname:='&表名：';
--根据表名确定SQL语句
sqlstr:='select * from '||tname;
--给游标变量赋值：open 游标名 for 'SQL语句';
open myCursor for sqlstr;
loop
fetch myCursor into r;
exit when myCursor%notfound;
dbms_output.put_line(r.dname);
end loop;
close myCursor;
end;
-- 使用动态游标输出dept表格的所有信息sys_refcursor
declare
-- sys_refcursor系统默认的动态游标类型
deptref sys_refcursor;
dp_dept dept%rowtype;
begin
open deptref for select * from dept;
loop
fetch deptref into dp_dept;
dbms_output.put_line(dp_dept.deptno||'-'||dp_dept.dname);
exit when deptref%notfound;
end loop;
close deptref;
end;4.动态sql
--判断表是否存在，如果存在则删除，不存在则创建
declare
num number;
begin
select count(*) into num from user_tables where table_name='TEST';
if(num>0) then
execute immediate
'drop table test';
dbms_output.put_line('删除成功');
end if;
execute immediate
'create table test
(tid number,tname varchar2(10))';
dbms_output.put_line('创建成功');
end;
----根据员工编号查询员工姓名并输出，要求使用动态SQL实现
declare
emp_no number;
e_name emp.ename%type;
sqlstr varchar2(100);
begin
emp_no:='&empno';
sqlstr:='select ename from emp where empno=:1';-- :name表示占位符
execute immediate sqlstr
into e_name -- into表将查询结果赋值给变量
using emp_no;-- using表示给占位符填值
dbms_output.put_line(e_name);
end;8.3、子程序
子程序：命名的PL/SQL块，编译之后存储在数据库中，方便用户调用。
- 分类
- 过程 procedure，完成特定功能
- 函数 function，完成特定功能并返回1个结果
- 优点
- 模块化，用于封装复杂的业务逻辑
- 提高代码重用，易于维护
- 执行效率高，减少网络流量占用
- 安全性高
- 组成
- 声明部分（必须的） create or replace…
- 可执行部分（必须的） begin…end;
- 异常处理部分（可选的） exception…
- 注意：子程序的参数或返回值类型，不能类型长度
8.3.1、过程(procedure)
1.语法
create [or replace]
procedure 过程名[(参数列表)]
as
[变量列表;]
begin
业务逻辑代码;
[exception
when others then 异常处理代码;]
end;2.简单的存储过程
create or replace procedure add_dept
as
begin
--业务逻辑
insert into dept values(23,'天问一号','火星');
commit;
end;
--调用存储过程
begin
add_dept;
end;3.调用有参数的存储过程
create or replace procedure add_dept2(dept_no dept.deptno%type,dept_dname dept.dname%type,dept_loc dept.loc%type)
as
begin
--业务逻辑
insert into dept values(dept_no,dept_dname,dept_loc);
commit;
end;
begin
add_dept2(2,'兔子','月球');
end;4.调用有返回值的存储过程
create or replace procedure add_dept3(dept_no dept.deptno%type,dept_dname dept.dname%type,dept_loc dept.loc%type,d_loc out dept.loc%type)
as
begin
insert into dept values(dept_no,dept_dname,dept_loc);
select d.loc into d_loc from dept d where d.deptno=dept_no;
commit;
end;
declare
d_loc dept.loc%type;
begin
add_dept3(3,'猴子','水帘洞',d_loc);
dbms_output.put_line('location:'||d_loc);
end;8.3.2、函数(function)
1.语法
create [or replace]
function 函数名[(参数列表)]
return 返回类型
as
[变量列表;]
begin
业务逻辑代码;
return 返回值;
[exception
when others then 异常处理代码;
return null;]
end;2.练习
--查询部门名并返回
create or replace function get_name(dept_no dept.deptno%type)
return dept.dname%type
as
d_dname dept.dname%type;
begin
select d.dname into d_dname from dept d where d.deptno=dept_no;
return d_dname;
exception
when no_data_found then
return null;
end;
--在select 语句中调用
select get_name(2) from dual;
--在plsql块调用
declare
d_dname dept.dname%type;
begin
d_dname:=get_name(3);
dbms_output.put_line('部门名:'||d_dname);
end;8.3.2、其他操作
删除子程序
drop procedure 过程名;
drop function 函数名;权限控制：授予/撤销用户调用子程序的权限
- 授权
grant execute on 子程序名 to 用户名;- 撤销
revoke execute on 子程序名 from 用户名;8.4、包
- 程序包：用于封装过程、函数、游标、变量等
- 组成
- 规范 package：声明公共成员和子程序规范，相当于Java中的接口
- 主体 package body：声明私有成员和实现子程序，相当于定义类实现接口
- 子程序重载，必须在程序包
练习
--1-规范
create or replace
package mypack
as
--声明过程
procedure get_info(tname varchar2,mycursor out sys_refcursor);
end;
--2-主体
create or replace
package body mypack
as
--实现过程
procedure get_info(tname varchar2,mycursor out sys_refcursor)
as
sqlstr varchar2(100);
begin
sqlstr:='select * from '||tname;
open mycursor for sqlstr;
end;
end;
--调用游标的存储过程
declare
mycursor sys_refcursor;
e_dept dept%rowtype;
begin
mypack.get_info('dept',mycursor);
loop
fetch mycursor into e_dept;
dbms_output.put_line(e_dept.deptno||','||e_dept.dname);
exit when mycursor%notfound;
end loop;
close mycursor;
end;九、java调用存储过程
新建一个maven项目并导入依赖：
<!--oracle数据库驱动-->
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc6</artifactId>
<version>11.2.0.3</version>
</dependency>数据库连接生成connection类:
package com.lxf;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class JdbcUtils {
//链接oracle的信息
private static String url="jdbc:oracle:thin:@localhost:1521:orcl";
private static String username="scott";
private static String password="1234";
private static String driverStr="oracle.jdbc.driver.OracleDriver";
//连接数据库
static {
try {
//加载
Class.forName(driverStr);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
//获取数据库连接
public static Connection getConnection(){
Connection conn = null;
try {
conn = DriverManager.getConnection(url,username,password);
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException("oracle驱动注册失败");
}
return conn;
}
//关闭连接对象
public static void close(Connection conn){
if(conn!=null){
try {
conn.close();
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException("oracle连接关闭失败");
}
}
}
public static void close(Statement stmt){
if(stmt!=null){
try {
stmt.close();
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException("oracle连接关闭失败");
}
}
}
public static void close(ResultSet rs){
if(rs!=null){
try {
rs.close();
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException("oracle连接关闭失败");
}
}
}
}调用存储过程测试类：
package com.lxf;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;
/**
* 调用存储过程和函数
*/
public class OracleDao {
//调用存储过程
public void callProcedure() throws SQLException {
//获取连接
Connection conn = JdbcUtils.getConnection();
//拼接sql。调用存储过程使用call
String sql="call add_dept3(?,?,?,?)";
//创建专用于调用过程或函数的对象
CallableStatement cstmt=conn.prepareCall(sql);
//为？占位符设置in、out值
cstmt.setInt(1,13);
cstmt.setString(2,"猪八戒");
cstmt.setString(3,"高老庄");
//out值
cstmt.registerOutParameter(4, Types.VARCHAR);
cstmt.execute();//执行调用存储过程
//依次接收1个返回值。out值是第四个
String location=cstmt.getString(4);
//显示
System.out.println("地点是 = " + location);
//关闭连接对象
JdbcUtils.close(cstmt);
JdbcUtils.close(conn);
}
//调用存储函数
public void callFunction() throws SQLException {
Connection conn=JdbcUtils.getConnection();
String sql="{ ? = call get_name(?) }";
CallableStatement cstmt=conn.prepareCall(sql);
cstmt.setInt(2,2);
cstmt.registerOutParameter(1,Types.VARCHAR);
cstmt.execute();//执行调用存储函数
String name = cstmt.getString(1);
System.out.println("名字 = " + name);
//关闭连接对象
JdbcUtils.close(cstmt);
JdbcUtils.close(conn);
}
public static void main(String[] args) throws SQLException {
OracleDao oracleDao = new OracleDao();
//调用存储过程
//oracleDao.callProcedure();
//调用函数
oracleDao.callFunction();
}
}
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
