01、oracle简介 数据存储量大、处理速度快、安全性高、容错性强
02、oracle安装 安装完PL/SQL Developer在对应Tools修改配置，整合连接oracle官方的软件（instantclient_12_1）及oci.dll 查询服务器IP地址：cmd-ipconfig oracle端口号：1521 默认数据库名称：orcl 配置对应的环境变量来指定连接oracle的信息
03、oracle体系结构 数据库：oracle数据库是数据的物理存储 实例：一个oracle实例有一系列的后台进程和内存结构组成，一个数据库可以有n个实例 用户：用户是在实例下建立的。不同实例可以建相同名字的用户（管理表的基本单位） 表空间：是oracle对物理数据库上相关数据文件（ORA或者DBF文件）的逻辑映射 数据文件：（dbf、ora）
04、创建表空间、用户及用户授权 --创建表空间 create tablespace itheima datafile 'c:\itheima.dbf' size 100m autoextend on next 10m; --删除表空间 drop tablespace itheima; --创建用户 create user itheima--用户名 identified by itheima--密码 default tablespace itheima; --给用户授权 --oracle数据库常用角色 connect--连接角色，基本角色 resource--开发者角色 dba--超级管理员角色 --给itheima用户授予dba角色 grant dba to itheima;
05、数据类型和表创建 varchar、varchar2(10)--表示字符串,常用varchar2，可变长度 number(n)--表示一个整数，长度是n number(m,n)--表示一个小数，总长度是m，小数是n，整数是m-n data--日期类型 clob--大对象，大文本数据类型（文本）可存4G blob--大对象，二进制数据（视频）可存4G --创建一个person表 create table person( pid number(20), pname varchar2(10) );
06、修改表结构 --添加一列 alter table person add (gender number(1)); --修改列类型 alter table person modify gender char(1); --修改列名称 alter table person rename column gender to sex; --删除一列 alter table person drop column sex;
07、数据的增删改查 --查询表中的记录 select * from person; --添加一条记录(在数据库操作增删改需要提交事物，否则为脏数据，spring操作不需要提交，默认已添加事物) insert into person (pid, pname) values (1, '小明'); commit; --修改一条记录 update person set pname = '小红' where pid = 1; commit; --三个删除 --删除表中的全部记录 delete from person; --删除表结构 drop table person; --先删除表，再次创建表。效果等同于删除表中的全部记录 --在数据量大的情况下，尤其在表中带有索引的情况下，该操作效率高 --索引可以提高查询效率，但是会影响增删改的效率 truncate table person;
08、序列的使用 --序列不是真的属于任何一张表,但是可以逻辑和表做绑定。 --序列：默认1开始，依次递增，主要用来给主键赋值使用 --dual:虚表，只是为了不全语法，没有任何意义 create sequence s_person; select s_person.currval from dual; (当前值) select s_person.nextval from dual;（下一个值） --添加一条带序列的记录 insert into person (pid, pname) values (s_person.nextval, '小明'); commit;
09、scott用户介绍（入门Oracle练习使用） --scott用户，密码tiger --解锁scott用户 alter user scott account unlock; --解锁scott用户的密码【也可用来重置密码】 alter user scott identified by tiger; --切换到scott用户下 Session-Log off-All；Session-Log on-xxx；然后Username=scott、Password=tiger；
10、单行函数 --单行函数：作用于一行，返回一个值 --字符函数 select upper ('yes') from dual; --YES 小写变大写 select lower ('YES') from dual; --yes 大写变小写 --数值函数 select round (26.16, 1) from dual; --26.1 四舍五入，后面的参数表示保留的位数 select trunc (26.16, -1) from dual; --20 直接截取，不在看后面位数的数字是否大于5 select mod (10, 3) from dual; ---1 求余 --日期函数 --查询出emp表所有员工入职距离现在几天 select sysdate-e.hiredate from emp e; --算出明天此刻(当前时间加一天) select sysdate+1 from dual; --查询出emp表所有员工入职距离现在几个月 select months_between (sysdate, e.hiredate) from emp e; --查询出emp表所有员工入职距离现在几年 select months_between (sysdate, e.hiredate)/12 from emp e; --查询出emp表所有员工入职距离现在几周 select round((sysdate-e.firedate)/7) from emp e; --转换函数 --日期转字符串 select to_char(sysdate, 'yyyy-mm-dd hh:mi:ss') from dual; --2019-09-29 10:28:56 select to_char(sysdate, 'fm yyyy-mm-dd hh:mi:ss') from dual; --2019-9-29 10:28:56 select to_char(sysdate, 'fm yyyy-mm-dd hh24:mi:ss') from dual; --2019-9-29 22:28:56 --字符串转日期 select to_char('2019-09-29 22:28:56', 'fm yyyy-mm-dd hh24:mi:ss') from dual; --通用函数 算出emp表中所有员工的年薪 --奖金里面有null值,如果null值和任意数字做算术运算，结果都是null，可以使用nvl(xxx, 0) select e.sal*12+nvl(e.comm, 0) from emp e;
11、条件表达式 --条件表达式的通用写法，mysql和oracle使用（推荐使用通用写法） --给emp表中的员工起中文名 select e.ename case e.ename when 'SMITH' then '张三' when 'ALLEN' then '李四' else '无名' end from emp e; --判断emp表员工工资，高于3000显示高收入，高于1500低于3000显示中等收入，其余显示低收入 select e.sal case when e.sal>3000 then '高收入' when e.sal>1500 then '中等收入' else '低收入' end from emp e; --oracle中除了起别名（用双引号），其他都用单引号 --oracle专用条件表达式 select e.ename decode(e.ename, 'SMITH', '张三' , 'ALLEN', '李四', '无名') "中文名" from emp e;
12、多行函数 --多行函数【聚合函数】：作用于多行，返回一个值 select count(1）from emp; --查询总数量 select sum(sal）from emp; --查询工资总和 select max(sal）from emp; --查询最大工资 select min(sal）from emp; --查询最低工资 select avg(sal）from emp; --查询平均工资
13、分组查询 --查询出每个部门的平均工资 select e.deptno, avg(e.sal) from emp e group by e.deptno; --查询平均工资高于2000的部门信息 select e.deptno, avg(e.sal) asal from emp e group by e.deptno having avg(e.sal)>2000; select e.deptno, avg(e.sal) asal from emp e group by e.deptno having asal >2000;--错误的，所有条件都不能使用别名来判断（先执行条件在执行select...） --查询出每个部门工资高于800的员工的平均工资 select e.deptno, avg(e.sal) asal from emp e where e.sal>800 group by e.deptno; --where是过滤分组前的数据，having是过滤分组后的数据 --表现形式:where必须在group by之前,having是在group by之后 --查询出每个部门工资高于800的员工的平均工资 --然后再查询每个平均工资高于2000的部门 select e.deptno, avg(e.sal) asal from emp e where e.sal>800 group by e.deptno having avg(e.sal)>2000;
14、多表查询 --笛卡尔积 select * from emp e, dept d; --等值连接 select * from emp e,dept d where e.deptno=d.deptno; --内连接 select * from emp e inner join dept d on e.deptno = d.deptno; --查询所有部门，以及部门下的员工信息【外连接】 select * from emp e right join dept d on e.deptno=d.deptno; --查询所有员工信息，以及员工所属部门的信息 select * from emp e left join dept d on e.deptno=d.deptno; --oracle中专用外连接 select * from emp e,dept d where e.deptno(+) = d.deptno;
15、自连接 --查询员工姓名，员工领导姓名 --自连接其实站在不同的角度把一张表看成多张表 select e1.ename, e2.ename from emp e1, emp e2 where e1.mgr = e2.empno; --查询员工姓名，员工部门名称，员工领导姓名，员工领导部门名称 select e1.ename, d1.dname, e2.ename, d2.dname from emp e1, emp e2, dept d1, dept d2 where e1.mgr = e2.empno and e1.deptno = d1.deptno and e2.deptno = d2.deptno;
16、子查询 --子查询返回一个值 --查询出工资和SCOTT一样的员工信息 --当自条件查询结果不为一条则出现问题 select * from emp where sal = (select sal from emp where ename = 'SCOTT') --子查询返回一个集合 --查询出工资和10号部门任意员工一样的员工信息 select * from emp where sal in (select sal from emp where deptno = 10); --自查询返回一张表 --查询每个部门的最低工资，和最低工资员工的姓名，和该员工所在部门名称 --1、先查询出每个部门最低工资 select deptno, min(sal) msal from emp group by deptno 2、三表联查，得到最终结果 select t.deptno, t,msal, e.ename, d.dname from （select deptno, min(sal) msal from emp group by deptno）t,emp e, dept d where t.deptno = e.deptno and t.msal = e.sal and e.deptno =d.deptno
17、分页查询 --rownum行号：每查询一行记录，就会在该行上加一个和行号，从1开始依次递增，不能跳着走 --排序操作会影响rownum的顺序 select rownum, e.* from emp e order by e.sal desc --如果涉及排序，但是要使用rownum的话，可以使用再次嵌套查询 select rownum, t.* from( select rownum, e.* from emp e order by e.sal desc) t; --emp表工资倒序排列后，每页五条记录，查询第二页 --rownum行号不能写上大于一个正数 select * from( select rownum rn, tt.* from ( select * from emp order by sal desc ) tt where rownum<11 ) where rn>5 --第二种写法（局限不能做order by操作） select * from (select rownum rn, * from emp) b where b.rn >5 and b.rn <11
18、视图 --概念：视图就是提供一个查询的窗口，所有数据来自于原表 --查询语句创建表 create table emp as select * from scott.emp; --创建视图必须有dba权限 create view v_emp as select ename, job from emp; --查询视图 select * from v_emp; --修改视图【不推荐】 update v_emp set job='CLERK' where ename='ALLEN'; commit; --创建只读视图 create view v_emp1 as select ename, job from emp with read only; --视图的作用：1、可以屏蔽敏感字段2、保证总部和分部的数据及时统一（总部查表，分部查视图）
19、索引 --概念：索引就是在表的列上构建一个二叉树（相当于书的目录） --达到大幅度的提高查询效率的目的，但是索引会影响增删改的效率 --单列索引 --创建单列索引 create index idx_ename on emp(ename); --单列索引触发规则，条件必须是索引列的原始值 --单行函数，模糊查询，都会影响索引的触发 select * from emp where ename='SCOTT'; -复合索引 --创建复合索引 create index idx_enamejob on emp(ename, job); --复合索引中第一列为优先检索列（ename） --如果要触发复合索引，必须包含优先检索列中的原始值 select * from emp where ename='SCOTT' and job='xx';--触发复合索引 select * from emp where ename='SCOTT' or job='xx';--不触发索引 select * from emp where ename='SCOTT';--有单列索引和复合索引，触发单列索引
20、pl/sql编程语言 --pl/sql编程语言是对sql语言的扩展，使得sql语言具有过程化的编程特性 --pl/sql编程语言比一般的过程化编程语言更加灵活高效 --pl/sql编程语言主要用来编写存储过程和存储函数等
--声明方法 --赋值操作可以使用:=也可以使用into查询语句赋值 declare i number(2) := 10; s varchar2(10) := '小明'; ena emp.ename%type;--引用型变量 emprow emp%rowtype;--记录型变量 begin dbms_output.put_line(i); dbms_output.put_line(s); select ename into ena from emp where empno = 7788; dbms_output.put_line(ena); select * into emprow from emp where empno = 7788; dbms_output.put_line(emprow.ename || '的工作为:' || emprow.job);--oracle数据库连接使用|| end;
21、pl/sql的if判断 --输入小于18的数字，输出未成年 --输入大于18小于40的数字，输出中年人 --输入大于40的数字，输入老年人 declare i number(3) := ⅈ begin if i<18 then dbms_output.put_line('未成年'); elsif i<40 then dbms_output.put_line('中年人'); else dbms_output.put_line('老年人'); end if; end;
22、pl/sql循环 --三种方式输出1到10是个数字 declare i number(2) := 1; begin while i<11 loop dbms_output.put_line(i); i := i+1; end loop; end; --exit退出循环(用的比较多) declare i number(2) := 1; begin loop exit when i>10; dbms_output.put_line(i); i := i+1; end loop; end; --for循环 declare
begin for i in 1..10 loop dbms_output.put_line(i); end loop; end;
23、pl/sql的游标 --游标：可以存放多个对象，多行记录 --输出emp表中所有员工的姓名 declare cursor c1 is select * from emp; emprow emp%rowtype; begin open c1; loop fetch c1 into emprow; exit when c1%notfound; dbms_output.put_line(emprow.ename); end loop; close c1; end --给指定部门员工涨工资 declare cursor c2(eno emp.deptno%type) is select empno from emp where deptno = eno; en emp.empno%type; begin open c2(10); loop fetch c2 into en; exit when c2%notfound; update emp set sal=sal+100 where empno=en; commit; end loop; close c2; end
24、存储过程 --存储过程：就是提前已经编译好的一段pl/sql语言，放置在数据库端，可以被直接调用（pl/sql一般都是固定步骤的业务） --给指定员工涨100块钱 create or replace procedure p1(eno emp.empno%type) is
begin update emp set sal=sal+100 where empno = eno; commit; end; --测试p1 declare begin p1(7788); end;
25、存储函数 --通过存储函数实现计算指定员工的年薪 --存储过程和存储函数的参数都不能带长度（eno empno%type、return number） --存储函数的返回值类型不能带长度 create or replace function f_yearsal(eno emp.empno%type) return number is s number(10); begin select sal*12+nvl(comm, 0) into s from emp where empno = eno; return s; end;
--测试f_yearsal declare s number(10); begin s :=f_yearsal(7788); dbms_output.put_line(s); end;
26、out类型参数使用 --使用存储过程来算年薪 create or replace procedure p_yearsal(eno emp.empno%type, yearsal out number) is s number(10); c emp.comm%type; begin select sal*12, nvl(comm, 0) into s, c from emp where empno = eno; yaersal := s+c; end; --测试p_yearsal declare yearsal number(10); begin p_yearsal(7788, yearsal); dbms_output.put_line(yearsal); end; --in和out类型参数的区别是什么？ --凡是涉及into查询语句赋值或者:=赋值操作的参数，都必须使用out来修饰
27、存储过程和存储函数的区别 --语法区别：关键字不一样 --存储含糊比存储过程多了两个return --本质区别：存储函数有返回值，而存储过程没有返回值。 --如果存储过程想实现有返回值的业务，我们必须使用out类型的参数 --即使存储过程使用了out类型的参数，其本质也不是真的有了返回值 --在存储过程内部给out类型参数赋值，在执行完毕，我们直接拿到输出类型参数的值
--使用存储函数有返回值的特性，来自定义函数 --而存储过程不能使用自定义函数 --案例需求：查询员工姓名，员工所在部门名称 --案例准备工作：把scott用户的dept表复制到当前用户下 create table dept as select * from scott.dept; --使用传统方式来实现案例需求 select e.ename, d.dname from emp e, dept d where e.deptno=d.deptno; --使用存储函数来实现提供一个部门编号，输出一个部门名称 create or replace function fdna(dno dept.deptno%type) return dept.dname%type is dna dept.dname%type; begin select dname into dna from dept where deptno = dno; return dna; end; --使用fdna存储函数来实现案例需求：查询出员工姓名，员工所在部门名称 select e.ename,fdna(e.deptno) from emp e;
28、触发器 --制定一个规则，在我们做增删改操作的时候，只要满足改规则，自动触发，无需调用。 --语句级触发器：不包含for each row --行级触发器：包含for each row的就是行级触发器 --加for each row是为了使用:old或者:new对象或者一行记录
29、触发器案例 --语句触发器 --插入一条记录，输出一个新员工入职 create or replace trigger t1 after insert on person declare
begin dbms_output.put_line('一个新员工入职'); end; --触发t1 insert into person values(1, '小红'); commit;
--行级触发器 --不能给员工降薪 -- raise_application_error(-20001~-20999, '错误提示信息'); create or replace trigger t2 before update on emp for each row declare
begin if :old.sal>:new.sal then raise_application_error(-20001, '不能给员工降薪'); end if; end; --触发t2 update emp set sal=sal-1 where empno = 7788; commit;
30、触发器实现主键自增【行级触发器】 --分析：在用户做插入操作之前，拿到即将插入的数据 --给该数据中的主键列赋值 create or replace trigger auid before insert on person for each row declare
begin select s_person.nextval into :new.pid from dual; end; --使用auid实现主键自增 insert into person (pname) values('a'); commit;
31、java调用存储过程环境准备 --oracle10g ojdbc14.jar --oracle11g ojdbc6.jar
32、环境测试 --加载数据库驱动 Class.forName("oracle.jdbc.driver.OracleDriver"); --得到Connection连接 Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.88.6:1521:orcl", "itheima", "itheima"); --得到预编译的Statement对象 PreparedStatement pstm = connection.prepareStatement("select * from emp where empno = ?"); --给参数赋值 pstm.setObject(1, 7788); --执行数据库查询操作 ResultSet rs = pstm.executeQuery(); --输出结果 while(rs.next()){ System.out.println(rs.getString("ename")); } --释放资源 rs.close(); pstm.close(); connection.close();
33、调用存储过程和存储函数 --java调用存储过程 @Test public void javaCallProcedure() throws Exception { --加载数据库驱动 Class.forName("oracle.jdbc.driver.OracleDriver"); --得到Connection连接 Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.88.6:1521:orcl", "itheima", "itheima"); --得到预编译的Statement对象 CallableStatement pstm = connection.prepareCall("{call p_yearsal(?, ?)}"); --给参数赋值 pstm.setObject(1, 7788); pstm.registerOutParameter(2, OracleTypes.NUMBER); --执行数据库查询操作 pstm.execute(); --输出结果[第二个参数] System.out.println(pstm.getObject(2)); --释放资源 pstm.close(); connection.close(); } --java调用函数 @Test public void javaCallFunction() throws Exception { --加载数据库驱动 Class.forName("oracle.jdbc.driver.OracleDriver"); --得到Connection连接 Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.88.6:1521:orcl", "itheima", "itheima"); --得到预编译的Statement对象 CallableStatement pstm = connection.prepareCall("{?= call f_yearsal(?)}"); --给参数赋值 pstm.setObject(2, 7788); pstm.registerOutParameter(1, OracleTypes.NUMBER); --执行数据库查询操作 pstm.execute(); --输出结果[第一个参数] System.out.println(pstm.getObject(1)); --释放资源 pstm.close(); connection.close(); }
