02.oracle

1 基础

01.主键id自增
    Mysql中，在主键列上设置AUTO_INCREMENT即可实现主键自增长。
    Oracle相比较Mysql，需创建序列，通过使用序列来完成自增主键，在Mapper中需要使用序列来获取生成的主键。

02.对事务的提交
    MySQL默认是自动提交
    Oracle默认不自动提交，需要用户手动提交，需要在写commit;指令或者点击commit按钮

03.分页查询
    MySQL是直接在SQL语句中写"select... from ...where...limit  x, y",有limit就可以实现分页
    Oracle则是需要用到伪列ROWNUM和嵌套查询

04.事务隔离级别
    MySQL有4中隔离级别：读未提交，读已提交，可重复读，串行化
    Oracle只有2中隔离级别：读已提交、串行化

05.最重要的区别
     MySQL是轻量型数据库，并且免费，没有服务恢复数据，并且开源
     Oracle是重量型数据库，收费，Oracle公司对Oracle数据库有任何服务。

1.1 技巧

-- 格式化sql数据窗口
set linesize 300

-- 一页显示sql数据窗口
set pagesize 300

-- 设置某个字段的宽度：
字符 col KEYWORD for a10
数字 col LENGTH for 9999

-------------------------------------------------------------------------------------------------------------

-- 查看保留字：登录DBA账户  
sqlplus / as sysdba

-- 查看保留字:  
select *from v$reserved_words order by keyword asc ;

-------------------------------------------------------------------------------------------------------------

-- 打开执行时间:
set timing on/off

-- 文字录屏
开启 spool  e:\note.txt 
关闭 spool  off;

-------------------------------------------------------------------------------------------------------------

-- 切换用户
conn / as sysdba
conn scott/tiger

1.2 修改

-- 修改  c
SQL> select * form emp;
第 1 行出现错误:
ORA-00923: 未找到要求的 FROM 关键字
SQL> c /form/from
  1* select * from emp
SQL> /
     EMPNO ENAME                JOB                       MGR HIREDATE
---------- -------------------- ------------------ ---------- --------------
       SAL       COMM     DEPTNO
---------- ---------- ----------
      7369 SMITH                CLERK                    7902 17-12月-80


-- 修改 ed
SQL> select * form emp;
第 1 行出现错误:
ORA-00923: 未找到要求的 FROM 关键字
SQL> ed
已写入 file afiedt.buf
  1* select * from emp
SQL> /
     EMPNO ENAME                JOB                       MGR HIREDATE
---------- -------------------- ------------------ ---------- --------------
       SAL       COMM     DEPTNO
---------- ---------- ----------
      7369 SMITH                CLERK                    7902 17-12月-80

1.3 append

SQL> select empno,ename,sal from emp;

     EMPNO ENAME                       SAL
---------- -------------------- ----------
      7369 SMITH                       800
      7499 ALLEN                      1600
      7521 WARD                       1250
      7566 JONES                      2975
      7654 MARTIN                     1250
      7698 BLAKE                      2850
      7782 CLARK                      2450
      7788 SCOTT                      3000
      7839 KING                       5000
      7844 TURNER                     1500
      7876 ADAMS                      1100

     EMPNO ENAME                       SAL
---------- -------------------- ----------
      7900 JAMES                       950
      7902 FORD                       3000
      7934 MILLER                     1300

已选择14行。

SQL> a  order by sal desc;                     -- 多一个空格位
  1* select empno,ename,sal from emp order by sal desc
SQL> /

     EMPNO ENAME                       SAL
---------- -------------------- ----------
      7839 KING                       5000
      7902 FORD                       3000
      7788 SCOTT                      3000
      7566 JONES                      2975
      7698 BLAKE                      2850
      7782 CLARK                      2450
      7499 ALLEN                      1600
      7844 TURNER                     1500
      7934 MILLER                     1300
      7521 WARD                       1250
      7654 MARTIN                     1250

     EMPNO ENAME                       SAL
---------- -------------------- ----------
      7876 ADAMS                      1100
      7900 JAMES                       950
      7369 SMITH                       800

已选择14行。

1.4 常见问题

00.规则
    a.规则1
        字段少，列举
        字段多，*
        -----------------------------------------------------------------------------------------------------
        WHERE换行
        别名不使用abc
        -----------------------------------------------------------------------------------------------------
        = 单值、in 多值
        group by 列 = select 列
    b.规则2
        a.Name AS NAME       √
        a.Name AS a.NAME     ×
        -----------------------------------------------------------------------------------------------------
        in变为三表联立
        操作SELECT多列计算，得with另起临时表
        WITH t AS () SELECT * FROM TABLE，()可以单独运行，是临时表
        -----------------------------------------------------------------------------------------------------
        小表 left join 大表，【大表+WHERE】（不可取），【小表+WHERE】left join【大表】（乐趣）
        left join ratio AS r on 1=1，ratio的dual表，就1行不影响

01.报错：需要指定user_id要使用的字段，因为user_info和user表都有一个名为的字段user_id，SQL不会容忍这种歧义
    a.报错
        SELECT user.*, user_info.*
        FROM user
        INNER JOIN user_info ON user.user_id = user_info.user_id
        WHERE user_id='$user_id'
    b.修改后
        SELECT u.user_id, u.user_fudge, ui.foo, ui.bar
        FROM user u
        INNER JOIN user_info ui ON ui.user_id = u.user_id
        WHERE u.user_id = '$user_id';

02.报错：元素内容必须由格式正确的字符数据或标记组成
    a.报错
          <select id="getByStatusAndOrderTimeLT" resultType="com.sky.entity.Orders">
            select *
            from orders
            where status = #{status}
              and order_time
            <
            #{orderTime}
          </select>
    b.修改后
        原因：Mapper.xml文件里“< (小于)号，>(大于)号”，会被认为是括号
        解决：将<号换成&lt;  >号换成&gt; 
        -----------------------------------------------------------------------------------------------------
          <select id="getByStatusAndOrderTimeLT" resultType="com.sky.entity.Orders">
            select *
            from orders
            where status = #{status}
              and order_time
            &lt;
            #{orderTime}
          </select>

2 伪列

-- 伪列：不属于任何一张表，但是会被所有的表共享
rownum：逻辑序列   1 2 3 4 5 6 7 8 9   
rowid： 物理序列   真实的存放位置   AAASSuAAEAAAAITAAF
 
-- rowid逻辑序列
不同SQL语句在执行时，rownum值不一致
相同SQL语句在执行时，rownum值不变
(在一次查询时，产生row，之后保持不变)
 
-- rowid根据插入的顺序，依次递增
SQL> select rownum, rowid, s.* from mystudent s;
    ROWNUM ROWID                   STUNO STUNAME                  STUAGE
---------- ------------------ ---------- -------------------- ----------
         1 AAASSuAAEAAAAITAAF          1 zs                           23
         2 AAASSuAAEAAAAITAAG          1 zs                           23
         3 AAASSuAAEAAAAITAAH          2 ls                           24
         4 AAASSuAAEAAAAITAAI          2 ls                           24
         5 AAASSuAAEAAAAITAAJ          3 ww                           25
         
-- rowid物理序列，18位
前6位：数据对象编号
依次往后数3位：数据文件编号
依次往后数6位：数据块编号
依次往后数3位：行号

select * from emp;
-- 假如除*还有其他字段，需要给表起"别名"
select  rowid, rownum, e.*  from emp e;

2.1 相同SQL语句在执行时，rownum值不变

查询工资最高的前3条员工信息
select ename, sal from emp
where rownum <=3
order by sal desc;      --临时表
 
-- 第一次查询结果
-- 相同SQL语句在执行时，rownum值不变 
select ename, sal from emp
where rownum <=3; 
rownum  ename   sal               ---> 结果为rownum 1 2 3
1		zs		7000
2		ls		6000
3		ww		5000
4		zl		9000
 
-- 第二次查询(order by sal desc; )
rownum  ename   sal               ---> 结果仍为rownum 1 2 3
4		zl		9000
1		zs		7000
2		ls		6000
3		ww		5000

2.2 子查询

-- 在使用rownum之前，解决排序（或使用到了临时表）的问题

2.2.1 order by

-- order by
select rownum,ename,sal from
(select * from emp order by sal desc) 
where rownum<=3  ;
 
分析：
-- 临时表 select * from emp order by sal desc 
name	sal
zl		9000
zs		7000
ls		6000
ww		5000
-- 在临时表上取前三个值
rownum	name	sal
1		zl		9000
2		zs		7000
3		ls		6000
4		ww		5000

2.2.2 top-n

-- top - n：前n个数据
select rownum, .... from 
( select * from xxx order by ...)
where rownum <=n ;

2.3 删除重复数据rownum、rowid

2.3.1 数据准备

-- 数据准备
create table mystudent(
stuno number,
stuname varchar2(10),
stuage number
);
 
insert into mystudent values(1, 'zs', 23);
insert into mystudent values(1, 'zs', 23);
 
insert into mystudent values(2, 'ls', 24);
insert into mystudent values(2, 'ls', 24);
 
insert into mystudent values(3, 'ww', 25);
 
 
SQL> select * from mystudent;
     STUNO STUNAME                  STUAGE
---------- -------------------- ----------
         1 zs                           23
         1 zs                           23
         2 ls                           24
         2 ls                           24
         3 ww                           25
 
SQL> select rownum, s.* from mystudent s;
    ROWNUM      STUNO STUNAME                  STUAGE
---------- ---------- -------------------- ----------
         1          1 zs                           23
         2          1 zs                           23
         3          2 ls                           24
         4          2 ls                           24
         5          3 ww                           25
         
SQL> select rownum, rowid, s.* from mystudent s;
    ROWNUM ROWID                   STUNO STUNAME                  STUAGE
---------- ------------------ ---------- -------------------- ----------
         1 AAASSsAAEAAAAITAAB          1 zs                           23
         2 AAASSsAAEAAAAITAAC          2 ls                           24
         3 AAASSsAAEAAAAITAAD          2 ls                           24
         4 AAASSsAAEAAAAITAAE          3 ww                           25

2.3.2 删除单条数据

-- 删除数据
-- rownum
SQL> delete from mystudent where rownum=1;
已删除 1 行。
 
-- rowid
SQL> delete from mystudent where rowid='AAASSsAAEAAAAITAAE';
已删除 1 行。

2.3.3 去重distinct错误示范

SQL> select * from mystudent;
     STUNO STUNAME                  STUAGE
---------- -------------------- ----------
         1 zs                           23
         1 zs                           23
         2 ls                           24
         2 ls                           24
         3 ww                           25
         
-- 去重distinct会导致全部数据丢失
①SQL> select distinct stuno from mystudent;
     STUNO
----------
         1
         2
         3
 
②SQL> delete from mystudent where stuno in (select distinct stuno from mystudent);
已删除5行。

2.3.4 删除多条数据

-- rowid物理序列，18位
前6位：数据对象编号
依次往后数3位：数据文件编号
依次往后数6位：数据块编号
依次往后数3位：行号
 
-- 思路：根据编号分组（将重复的数据放到一组），然后在每组中只保留一个rowid最小
select stuno，min(rowid) from mystudent 
group by stuno;
 
     STUNO MIN(ROWID)
---------- ------------------
         1 AAASSuAAEAAAAITAAF
         2 AAASSuAAEAAAAITAAH
         3 AAASSuAAEAAAAITAAJ
 
delete from mystudent 
where rowid not in 
(select min(rowid) from mystudent group by stuno);
已删除2行。

3 序列

3.1 创建序列

-- 序列：模拟自增,本质就是内存中的数组  （20）
[1,2,3...,20] [21,22,...,40]
 
-- 语法
create sequence 序列名
increment by 步长
start with 起始值
maxvalue | nomaxvalue
minvalue | nominvalue
cycle | nocycle           -- 循环
cache n | no cache ;      -- [1	 3  5]	7	9
 
-- 序列有2个属性
nextval:下一个值
currval:当前值
	1	2	3	4	5	....
*

3.2 查看序列

-- 查看序列
select * from user_sequences ;

3.3 增加序列

3.3.1 序列操作

-- 创建序列
create sequence myseq
 
-- 选取添加序列的数据
SQL> select empno, ename from emp;
     EMPNO ENAME
---------- --------------------
      3322 LISI
      1234 zs
      5555 kkk
      1221 LISI
      1223 LISI
      7369 hello
 
-- 增加序列
-- 首次使用myseq.nextval
SQL> select myseq.nextval, myseq.currval, empno, ename from emp;
   NEXTVAL    CURRVAL      EMPNO ENAME
---------- ---------- ---------- --------------------
         1          1       3322 LISI
         2          2       1234 zs
         3          3       5555 kkk
         4          4       1221 LISI
         5          5       1223 LISI
         6          6       7369 hello
 
-- 序列会接着上一次的值继续使用
SQL> select myseq.nextval, myseq.currval, empno, ename from emp;
   NEXTVAL    CURRVAL      EMPNO ENAME
---------- ---------- ---------- --------------------
         7          7       3322 LISI
         8          8       1234 zs
         9          9       5555 kkk
         10         10      1221 LISI
         11         11      1223 LISI
         12         12      7369 hello

3.3.2 自增（默认步长1，起始值1）

-- 创建序列
create sequence myseq
 
-- 选取添加序列的数据
create table person(
    pid number primary key,
    pname varchar2(10)
);
insert into person values(myseq.nextval, 'ls4');
insert into person values(myseq.nextval, 'ws4');
insert into person values(myseq.nextval, 'zs4');
 
-- 查看表
select * from person;
       PID PNAME
---------- --------------------
        40 ls4
        41 ws4
        42 zs4

3.3.3 自增（步长2，起始值1000）

-- 创建序列
create sequence myseq1
increment by 2
start with 1000;
 
-- 选取添加序列的数据
SQL> select myseq1.nextval, empno, ename from emp;
   NEXTVAL      EMPNO ENAME
---------- ---------- --------------------
      1000       3322 LISI
      1002       1234 zs
      1004       5555 kkk
      1006       1221 LISI
      1008       1223 LISI

3.3.4 循环（最小1，最大9）

-- 创建序列
create sequence myseq2
increment by 2
start with 1
maxvalue 9
minvalue 1
cycle 
cache 3 ;
 
-- cache
[1	3	5]	7	9
cache元素的个数(3个) <= 循环元素个数(5个)
 
-- 选取添加序列的数据
-- 循环序列不能用于给主键/唯一约束的键赋值
SQL> select myseq2.nextval, empno, ename from emp;
   NEXTVAL      EMPNO ENAME
---------- ---------- --------------------
         1       3322 LISI
         3       1234 zs
         5       5555 kkk
         7       1221 LISI
         9       1223 LISI
         1       7369 hello
         3       7499 ALLEN
         5       7521 WARD

3.3.5 裂缝问题

-- 裂缝： [1,2,3...,20] [21,]
-- 断电、异常、回滚、多表使用同一个序列 ...
 
-- 裂缝
30 31 32 33 34 35 commit  36 37  rollback
最后变为：
30 31 32 33 34 35 38 39 40
 
-- 多表
1 2 3 4 5 
A  1 2 4
B  3

3.4 修改序列：不影响之前产生的序列

-- 修改序列
alter sequence myseq
increment by 2;
 
insert into person values(myseq.nextval, 'zs6');
insert into person values(myseq.nextval, 'zs7');
 
-- 查看表
SQL> select * from person;
       PID PNAME
---------- --------------------
        40 ls4
        41 ws4
        42 zs4
        43 ls3
        44 ws3
        45 zs3
        47 zs6
        49 zs7

3.5 删除序列

-- 删除序列 drop sequence 序列名 ;
drop sequence myseq2;

4 同义词（别名）

-- 数据库对象（表 视图 索引...）起别名（默认私有/专用）

4.1 私有/专有同义词

-- 查看其它用户的表，报错“表或视图不存在”：可能是权限不足，需要授权
SQL> conn / as sysdba;
SQL> grant select on hr.employees to scott;     -- 查看hr.employees
SQL> grant create synonym to scott;             -- 创建私有synonym同义词
 
-- 简化hr.employees，起别名为hremp
SQL> select count(*) from hr.employees;
  COUNT(*)
----------
       107
       
-- 同义词(别名)
SQL> create synonym hremp for hr.employees;
同义词已创建。
 
-- 再次利用同义词查询
SQL> select count(*) from hremp;
  COUNT(*)
----------
       107
 
-- 删除同义词
SQL> drop synonym hremp;

4.2 公有同义词（创建、删除，一般建议由管理员操作）

-- 加 public
-- 查看其它用户的表，报错“表或视图不存在”：可能是权限不足，需要授权
SQL> conn / as sysdba;
SQL> grant select on hr.employees to scott;     -- 查看hr.employees
SQL> grant public create synonym to scott;      -- 创建公有synonym同义词
 
-- 简化hr.employees，起别名为hremp
SQL> select count(*) from hr.employees;
  COUNT(*)
----------
       107
       
-- 同义词(别名)
SQL> create public synonym hremp for hr.employees;
同义词已创建。
 
-- 再次利用同义词查询
SQL> select count(*) from hremp;
  COUNT(*)
----------
       107
 
-- 删除同义词
SQL> drop public synonym hremp;                 -- public

5 PLSQL

-- PLSQL：可以对SQL进行编程
 
-- 开发工具
plsql developer
oracle sql developer
 
初始化 ctrl + c  停止当前任务，终结死循环
彻底删除文件 shift+文件
IP  ipconfig
 
更改密码
C:\Users\86151>sqlplus / as sysdba
SQL> alter user system identified by manager;

5.1 变量

-- 打开输出put_line功能
set serveroutput on ;
 
-- 变量、常量、光标（游标）、例外（自定义异常）
declare
	...
	...
	
-- 代码
begin
	...
	exception （捕获异常）
	dbms_output.put_line(); （输出）
	...
end ;

5.1.1 代码1（引用型变量 pname emp.ename%type;）

set serveroutput on ;
 
declare
    psex char(3) := '男';
    --pname VARCHAR2(10);  
    pname emp.ename%type;   --引用型变量，推荐使用
    
-- ||连接字符， select ename into pname中pname应与ename保持一致
begin
    select ename into pname from emp where empno = 3322;
    dbms_output.put_line('hello world:' || psex || '--' || pname);
end ;
 
-- 结果
hello world:男--LISI

5.1.2 代码2（记录型变量 emp_info emp%rowtype ; ）

-- 记录型变量: 相当于java 对象, 可以用于同时保存多个变量值
 
-- java 对象
Person per = new Person(1,'zs',28,11...,...);
per.name

set serveroutput on ;
 
declare
   emp_info emp%rowtype ;   --记录型变量
   
begin
    select * into emp_info from emp where empno =7788;
    dbms_output.put_line( emp_info.empno  || '---' ||  emp_info.ename ||'--- '||  emp_info.job);
end ;
 
-- 结果
7788---SCOTT--- ANALYST

5.2 if语句

if    条件  then ... ;          -- 多个;
end if;
 
---------------------------
if    条件  then ... ;
else   ...          ;
end if;
 
---------------------------
if    条件  then ... ;
elsif 条件  then ... ;
else   ...
end if;

5.2.1 代码

set serveroutput on ;
declare
	pnum number := 3 ;
    
begin
    if pnum=1   then   dbms_output.put_line('一');
    elsif pnum=2  then   dbms_output.put_line('二');
    else dbms_output.put_line('其他');
    end if ;   
end ;
 
-- 结果
其他

5.3 循环while, do…while, for

-- while
loop 
...
end loop ;
 
-- while
loop 
...
   exit when i>5 ;     --exit when i>5, i>5退出循环 与do{}while(i<5)中i<5执行语句，相反
end loop;
 
 
-- for
for  i in  1 .. 10
loop
..
end loop ;

5.3.1 代码1

set serveroutput on ;
declare
    
begin
    for x in 1 .. 5
        loop
            dbms_output.put_line('x ');
        end loop;
end ;
 
-- 结果
x 
x 
x 
x 
x

5.3.2 代码2

set serveroutput on ;
declare
        pnum number:=1 ;
        psum number:= 0 ;
begin
        loop
            exit when pnum >5 ;
            psum := psum + pnum ;     -- sum+= i ;
            pnum := pnum +1 ;             -- i++;
        end loop ;
        dbms_output.put_line(psum);
end ;
 
-- 结果
15

5.4 游标（光标）cursor：集合，多个值

-- 语法：
cursor 光标名(参数列表)
is
select ....
 
-- 光标的属性
%isopen     --判断开启
%rowcount	--已经获取的数据
%found	    --判断下一行是否有数据
%notfound

5.4.1 代码1

-- 查询并打印全部员工的姓名、薪水
set serveroutput on ;
declare
    cursor cemp is select ename, sal from emp;
    pename emp.ename%type;
    psal emp.sal%type;
begin
    open cemp;   --1.打开光标
    loop         --2.循环，准备获取每一行数据 
        fetch cemp into pename, psal;   --3.一行一行获取光标的值
        exit when cemp%notfound;
        
        DBMS_OUTPUT.PUT_LINE(pename || '的工资是：' || psal);
    end loop;    
    close cemp;  --4.关闭光标
end ;
 
-- 结果
LISI的工资是：9998
LISI的工资是：9998

5.4.2 代码2

-- 总工资： president 1000，manager 800， 其他400
-- 注意区分普通sql语句与PLSQL语句  :=  =
-- ACID oracle read commit， 一边不提交，另一边访问不到
set serveroutput on ;
declare
    cursor cemp is select empno, job from emp;
    pempno emp.empno%type;
    pjob emp.job%type;
begin
    open cemp;
    loop
        fetch cemp into pempno, pjob;
        exit when cemp%notfound;
        
        --判断职位
        if pjob = 'PRESIDENT' 
            then update emp set sal=sal+1000 where empno= pempno;
        elsif pjob = 'MANAGER'
            then update emp set sal=sal+800 where empno= pempno;
        else
            update emp set sal=sal+400 where empno= pempno;
        end if;
    end loop;
    DBMS_OUTPUT.PUT_LINE('ok');
    close cemp;
    
    commit;      --ACID oracle read commit， 一边（终端）不提交，另一边访问不到
end ;

5.4.3 代码3

--查询某个部门的员工姓名，带参数的光标
set serveroutput on;
declare
    cursor cename(dno number) is select ename from emp where deptno=dno;
    pename emp.ename%type;
begin 
    open cename(20);
    loop
        fetch cename into pename;
        exit when cename%notfound;
        DBMS_OUTPUT.put_line(pename);
    end loop;
    close cename;
end; 
 
-- 结果
JONES
SCOTT
ADAMS
FORD

5.5 例外（异常）

5.5.1 系统例外

-- 系统例外
no_data_found 	
too_many_rows           --行数太多
zero_Divide				--0不能作为除数
value_error             --算术或转换错误
timeout_on_resource     --资源等待超时
 
 
set serveroutput on;
declare
    pnum number;
    
begin 
    pnum := 1/0;
    
    exception
    when zero_Divide then dbms_output.put_line('0不能作为除数');
    when too_many_rows then DBMS_OUTPUT.PUT_LINE('行数太多');
    when others then DBMS_OUTPUT.PUT_LINE('其他例外...');
end;

5.5.2 自定义例外

/**MyException**/
 
try{
	if(xxx) throw new MyException() ;
}catch(){}
catch(){}
catch(){}

set serveroutput on;
declare
    myexc exception;
    pnum number :=1;
begin 
    if pnum =1 then raise myexc;
    end if;
    
    exception
    when myexc then dbms_output.put_line('自定义例外');
end;

-- 是否存在编号50的部门，如果不存在，抛出一个例外；如果存在，将该部门的员工姓名
set serveroutput on;
declare
    cursor cemp(dno number) is select ename from emp where deptno = dno;
    pename emp.ename%type;
    no_emp_found exception;
begin 
    open cemp(50);
    fetch cemp into pename;            -- 先取一行数据
    if cemp%notfound then raise no_emp_found;
    else
        loop
            exit when cemp%notfound;
        end loop;
    end if;
    
    exception
        when no_emp_found then dbms_output.put_line('自定义例外,没有此部门');
    close cemp;
end;

5.5.3 代码1

-- 统计每年的入职人数
 
-- sql
select to_char(HIREDATE,'yyyy' ),count(1) from emp 
group by to_char(HIREDATE,'yyyy' );
 
-- plsql
java:每个人数据全获取，判断入职年份，如果1980, +1; +1
set serveroutput on;
declare
    cursor cemp is select to_char(HIREDATE, 'yyyy') from emp;
    phiredate varchar(4);  --保存四个变量
    count80 number :=0;
    count81 number :=0;
    count82 number :=0;
    count87 number :=0;
begin 
    open cemp;
        loop
            fetch cemp into phiredate;
            exit when cemp%notfound;
            
            if phiredate = '1980' then count80 := count80+1 ;
            elsif phiredate = '1981' then count81 := count81+1 ;
            elsif phiredate = '1982' then count82 := count82+1 ;
            else   count87 := count87+1 ;
            end if ;
        end loop;
    close cemp;
    
   dbms_output.put_line('1980'||count80);
   dbms_output.put_line('1981'||count81);
   dbms_output.put_line('1982'||count82);
   dbms_output.put_line('1987'||count87);
   dbms_output.put_line('总人数'||(count80+count81+count82+count87));
end;

5.5.4 代码2

-- 涨工资。 每个10%，按入职时间顺序涨工资，且涨后的总工资不能超过5万。
-- 计算需要涨工资的人个数以及涨后的工资总额。
set serveroutput on ;
 
declare 
 cursor cemp is select empno,sal from emp order by hiredate asc ;
 pempno emp.empno%type ;
 psal emp.sal%type ;
 countEmp number :=0 ;
 salTotal number :=0 ;
  
begin 
    OPEN cemp ;
    loop 
        --exit when salTotal > 50000 ;--  5.1
        fetch cemp into pempno ,psal ;
        exit when cemp%notfound ;
        
        if salTotal + psal*1.1 < 50000
            --涨工资
           then  update emp set sal = sal*1.1 where empno = pempno;
           countEmp := countEmp +1;
            --salTotal: 50  ,51 
           salTotal := salTotal + psal*1.1 ;  --psal 是变量，sal是表的字段
        else 
            dbms_output.put_line('人数'||countEmp ||'--'|| '涨后的总额'||salTotal);
            exit  ;
        end if ;
    end loop ;
    close cemp ;
end ;

5.5.5 代码3

-- 统计各部门的工资情况。格式如下
部门编号  <2000的人数		2000-4000人数		>4000人数	     工资总额
		  count1			count2			count3		salTotal
set serveroutput on ;
declare 
	--保存10 20 30 40 四个部门编号
    cursor cdept is select deptno from dept ;
    pdeptno dept.deptno%type ; 
    --部门中员工的所有工资
    cursor cemp(dno number) is select sal from emp where deptno = dno;
    --工资保存到psal
    psal emp.sal%type ;    
    
    count1 number;
    count2 number;
    count3 number; 
    
    --各部门工资总额
    salTotal number ;
   
begin 
	--外层循环：遍历所有的部门编号
    open  cdept;
    loop
        fetch cdept into pdeptno ;
        exit when cdept%notfound ;
        
        count1 :=0 ;  --计数
        count2 :=0 ;
        count3 :=0 ;
        
        --内层循环：遍历某个部门的所有工资
        select sum(sal) into salTotal from emp where deptno =pdeptno;  --保存变量
        open cemp(pdeptno) ;
            loop
                fetch cemp into psal ;
                exit when cemp%notfound ;
                if psal<2000 then count1:=count1+1;
                elsif psal>=2000 and psal<4000 then  count2:=count2+1;
                else count3:= count3+1 ;
                end if ;
            end loop ;
        close cemp ;
        dbms_output.put_line(pdeptno||' '||count1||' '||count2||' '||count3||' '||salTotal);     
    end loop;   
    close cdept;
end ;

5.6 存储过程（方法）

5.6.1 无参存储过程

-- 无参
create or reaplace procedure 过程名()
as
begin 
end ;
 
-- 创建存储过程
set serveroutput on ;
create or replace procedure test1
as 
    pnum number:=10 ;
begin
    dbms_output.put_line('hello:'||pnum);
end ;
 
-- 调用存储过程
①exec test1();
或者
②begin
    test1();
    test1();
    test1();
end ;

5.6.2 有参存储过程

-- 有参：输入参数in  输出参数out （返回值）
create or reaplace procedure 过程名(pid in number)    --in类似java  out表示有返回值
as
begin 
end ;
 
-- 传入一个员工编号，给该员工涨500
-- 创建存储过程
create or replace procedure raiseSalary(pid in number) 
as 
    psal emp.sal%type ;
begin 
    select sal into psal from emp where empno=pid ;  --将sal保存到变量psal
    update emp set sal = sal + 500 where empno=pid ;
    dbms_output.put_line(psal || '--'|| (psal+500));
end ;
 
-- 调用存储过程
begin
    raiseSalary(4600);
    raiseSalary(9900);
end ;

5.7 存储函数（函数）

5.7.1 必须含有return

-- 存储函数:与存储过程的最大区别：必须有return 
create [or replace] function 函数名(参数列表)
	return 返回值类型                             -- 无;
as
 
begin 
	..
	return  返回值
end ;

5.7.2 代码

-- 查询某个员工的年收入
create or replace function getTotalSal(pid in number) 
    return number 
as 
    empSal emp.sal%type ;
    empComm emp.comm%type ;
begin 
    select sal,comm into empSal,empComm from emp where empno = pid ;
    dbms_output.put_line( empSal*12+  nvl( empComm,0));
    return empSal*12+nvl( empComm,0) ;       -- 注意null+XX -> null
end ;
 
-- 调试需要授权给scott
grant DEBUG CONNECT SESSION to scott;
grant DEBUG ANY PROCEDURE to scott;
 
-- 过程右键“运行”，通过更改初始值，和注释掉的打印
DECLARE
  PEMPNO NUMBER;
  PENAME VARCHAR2(10);
  PJOB VARCHAR2(9);
BEGIN
  PEMPNO := 7788;                               --1处
  GETEMPINFO(
    PEMPNO => PEMPNO,
    PENAME => PENAME,
    PJOB => PJOB
  );
  /* Legacy output: */                          --2处
DBMS_OUTPUT.PUT_LINE('PENAME = ' || PENAME); 
 
  :PENAME := PENAME;
  /* Legacy output: */ 
DBMS_OUTPUT.PUT_LINE('PJOB = ' || PJOB);        --3处
  :PJOB := PJOB;
--rollback; 
END;

5.8 存储过程/存储函数？

-- 存储过程
建议：没有、多个返回值, 存储过程  in  out out out 
 
-- 存储函数
只有一个返回值, 存储函数  return 
 
-- 返回值：	
存储函数：out \ return 
存储过程：out

5.8.1 代码

--存储过程：传入员工编号，返回姓名、工作
create or replace procedure getEmpInfo(pempno in emp.empno%type, pename out emp.ename%type, pjob out emp.job%type )
as
   --本次变量emp.empno%type在此处使用，此处可写另外所需的变量
begin 
    select ename,job into pename,pjob from emp where empno = pempno;
end ;
 
 
--存储函数
create or replace FUNCTION getEmpFuncInfo2(pempno in emp.empno%type, pename out emp.ename%type, pjob out emp.job%type )
    return varchar2 
as
   
begin 
    select ename  ,job into pename ,pjob from emp where empno = pempno;
    return null ;
end ;

5.9 包

-- 查询某个部门的所有员工信息
select * from emp where deptno = 10 ;
 
-- 用“包”来返回光标，保存集合数据 ：out
-- 返回值/输出参数如果是光标类型： 包
 
包 = 包头+包体
 
public void aa() 包头
{
	包体
}

5.9.1 代码

-- 创建包头
create or replace 
PACKAGE MYPACKAGE AS 
	--定义光标，名字为empcursor
    type empcursor is ref cursor; 
    
    --empList返回值为光标empcursor
    procedure queryEmpList(dno in number, empList out empcursor);
END MYPACKAGE;
 
-- 创建包体，右键包
CREATE OR REPLACE
PACKAGE BODY MYPACKAGE AS
 
  procedure queryEmpList(dno in number , empList out empcursor)  AS
  BEGIN
        open empList for 
        select *from emp where deptno = dno;   
  END queryEmpList;
END MYPACKAGE;
 
-- 控制台验证
desc mypackage;
参数名     类型          输入/输出     默认值?   
------------------------------ -----------------------------------------
DNO       NUMBER        IN        
EMPLIST   REF CURSOR    OUT

6 触发器

-- 触发器
与表相关联的，PLSQL程序
当执行DML，自动执行触发器
 
-- 语法
create or replace trigger 触发器名
before|after
delete|insert |update [of 列名]
on 表
for each row [when (条件)]
...plsql 代码
/

6.1 语句级、行级触发器

--无论修改多少行，触发器只执行一次。
原因：默认（语句级触发器），作用于表，只执行一次。
 
-- 语句级触发器：
作用于表，只执行一次。
 
-- 行级触发器：
作用于每一行，每满足一次条件都执行一次；可以执行多次触发器
for each row [when 条件]

6.2 语句级触发器

6.2.1 插入

-- 触发器insert
create trigger logStudent
after 
insert 
on student
declare 
begin 
    dbms_output.put_line('增加成功');
end ;
/
 
-- 测试
set serveroutput on ;    -- 打印
insert into student(stuno,stuname,stuage) values(22,'zs22',222);

6.2.2 更新

-- 触发器sname
set serveroutput on ;
create trigger logupdateStudent
after 
update of sname
on student
declare
 
begin
    dbms_output.put_line('您修改了姓名...');
end ;
/
 
-- 测试，只要使用sname才触发
set serveroutput on;
update student set sname=20 where sno=3;
 
-- 结果
2行已更新
您修改了姓名...

6.3 行级触发器

-- 触发器insert
create or replace trigger logaddStudent
after
insert
on student
for each row
declare
begin
	dbms_output.put_line('增加成功！');
end;
 
-- 测试
-- 将emp中的empno,ename插入到student的student(sno,sname)
insert into student(sno,sname) select empno,ename from emp;
 
-- 结果
增加成功！
增加成功！
增加成功！
增加成功

6.3.1 代码1

-- 不能在非工作时间插入员工信息
周一 - 周五   9：00 - 18:00
星期几：select to_char(sysdate, 'day') from dual;  -- 获取当前时间
小时：select to_char(sysdate, 'hh24') from dual;   -- 获取当前时间
 
-- 触发器insert
create or replace trigger securityStudent
before  insert 
on student
begin
        --校验  --不正常
        if    to_char(sysdate,'day')  in ('星期六','星期日')  or  to_number( to_char(sysdate,'hh24') ) not between 9 and 18
        then 
            --禁止插入，例外
            raise_application_error(-20001,'禁止非工作时间插入学生'  );
        end if ;
end ;
/
 
-- 测试
insert into student(sno, sname) values(99, 'ls');
 
错误报告 -
ORA-20001: 禁止非工作时间插入学生
ORA-06512: 在 "SCOTT.SECURITYSTUDENT", line 6
ORA-04088: 触发器 'SCOTT.SECURITYSTUDENT' 执行过程中出错

6.3.2 代码2

-- 使用触发器确保：涨工资，涨后的工资 不能少于涨前的工资
create or replace trigger checkSalary
before update
on emp
for each row
begin
    if :new.sal < :old.sal  --涨前 < 涨后  :变量
    then
    raise_application_error(-20002 , '涨后的工资不能小于涨前的！');
    end if;
end;
/
 
-- 测试
update emp set sal=sal-1000 where empno=7788
 
错误报告 -
-- raise_application_error中编号的范围   -20000  -  -20999
ORA-21000: -10002 的 raise_application_error 错误号参数超出范围
ORA-06512: 在 "SCOTT.CHECKSALARY", line 4
ORA-04088: 触发器 'SCOTT.CHECKSALARY' 执行过程中出错
 
错误报告 -
ORA-20002: 涨后的工资不能小于涨前的！
ORA-06512: 在 "SCOTT.CHECKSALARY", line 4
ORA-04088: 触发器 'SCOTT.CHECKSALARY' 执行过程中出错

6.4 数据校验

-- 数据校验: 
web前端  onsubmit onblur       --> 
控制器 Servlet/Spring MVC  if() -->
数据库 触发器

7 数字字典

-- 数据字典（了解）：元数据，数据库的各种描述信息，系统自带很多表
select * from dictionary; --系统自带2000多..
 
dictionary、user_objects、user_tables、user_tab_columns;
all_tables
 
-- 数据字典的命名规范：
user：当前用户能够使用的
all：系统中全部的
dba:管理员
v$:性能相关
 
user_sequences ;
user_synonyms;
user_关键字s;
user_tab_comments --注释
all_tab_comments 
 
SQL> comment on table emp is '员工表';
注释已创建。
 
comment on column emp.ename is '员工姓名';
注释已创建。

8 DBCA

-- DBCA（以管理员身份运行，否则权限不足）
DBCA(Database Configuration Assistant)
 
-- 数据仓库：分析数据用的，只查询，不DML。
 
-- 默认密码
sys  change_on_install
system manager
scott tiger
DBSNMP 4023615
SYSMAN 4023615

-- ASM：外设磁盘
 
-- 快速恢复区 flash_recovery_area ：闪回区大小   3G
 
-- 闪回区越大，可以恢复的数据越多，但是会影响性能
 
-- 备份
   热备份 ：联机备份 ，归档模式
   冷备份 ：脱机备份
   
-- 示例方案：scott   sh   hr 
-- 制定脚本：在数据库创建完毕时，自动执行的脚本
   insert into ....   ->  aa.sql
   
-- 连接模式 专用服务器 共享服务器
 
-- 监听出错：
监听程序 -> 忽略错误，原因：在创建第一个数据库时，已经有了监听器，因此不用在创建新的监听器
一个监听器，监听两个数据库
 
--协议适配器错误，myoracle卸载不干净，需指定具体的实例表
sqlplus scott/tiger@orcl

9 闪回

-- 作用：将错误的DML 并且commit，撤销已提交的事务；
还原删除的表 drop table xxx ;
获取表上的历史记录
		
-- 类型：
闪回表中的数据：将表中的数据回退到历史的某一个点
闪回删除的表：还原表
闪回事务查询： undo_sql
闪回数据库（了解）：将数据库 中的数据  回退到历史的某一个点
闪回归档日志（了解）

-- 闪回表中的数据：将表中的数据，回退到历史的某一个点	
必须sys
show parameter undo;
		
假设闪存区3G
undo_retention：900 ，超过闪存区的数据 必须在900秒内闪回
 
alter system set undo_retention=1200 scope = both ;
both | memory 当前数据库有效，重启无效 | spfile  当前数据库无效，重启有效
 
闪回：时间点SCN
闪回：必须知道回退的时间点或者SCN,时间->SCN
	 select  timestamp_to_scn(sysdate) scn from dual ;

9.1 闪回数据

create table testfb (
	id number ,
	name varchar(10)
);
insert into testfb values(1,'a');
insert into testfb values(2,'b');
insert into testfb values(1,'c');
commit ;
此时SCN:3607794
 
删除id=1
commit ;
 
闪回：
flashback table testfb to scn 2089834 ;
 
-- 如果某个用户在闪回时权限不足，需要授权：
sys: grant flashback any table to scott;
-- 未启用行移动功能, 不能闪回表
alter table testfb enable row movement;
-- 闪回可以跨越commit回退
 
-- 要闪回：必须知道时间点

1commit  commit  commit
  -- "2019-8-8  20：00" -> date ..  ->scn
2commit
3commit
 
-- 要闪回：必须知道时间点

9.2 闪回表

-- 闪回删除的表（还原oracle回收站中的表 ）
		
-- 查看回收站 show recyclebin;
默认情况下：如果回收站中重名，闪回的是最近一次删除的表
如果闪回的表和库中目前的表重名，则冲突，必须 重命名rename to..
 
flashback table  表名  to before drop ;
flashback table  "回收站中的名字"  to before drop ;
 
清空回收站： purge recyclebin;
彻底删除某一张表（不过回收站）： drop table testfb2 purge ;
 
-- 普通用户：删除->回收站 ->  清空|还原
-- 管理员：删除表不过回收站，直接彻底删除。
 
 
-- 是否闪回表中的触发器：
A(触发器) -> 删除->回收站->闪回表时是否连同触发器一起闪回
 
 
 
flashback table 表明 to before drop  [rename to 新表名] enable|disable（默认） triggers ;
 
create table testfb4
(
id number ,
name varchar(10)
);
 
flashback table enable triggers  testfb4  to before drop;

9.3 闪回事务

-- 闪回事务查询
闪回dml+commit：依赖时间  ->scn
闪回事务：依赖的是提交次数（undo_sql）
 
-- 数据准备
create table tb_version(id number,name varchar2(10)) ;
 
insert into tb_version values(11,'a1');
insert into tb_version values(12,'a1');
insert into tb_version values(13,'a1');
commit;
 
insert into tb_version values(21,'b1');
insert into tb_version values(22,'a1');
insert into tb_version values(23,'a1');
commit;
 
insert into tb_version values(31,'c1');
insert into tb_version values(32,'a1');
insert into tb_version values(33,'a1');
commit;
 
-- 提交次数？闪回版本查询(事务编号)
闪回版本查询: 版本信息-伪列
select  id,name,versions_xid,versions_operation,versions_starttime,
versions_endtime from tb_version versions between timestamp  minvalue and maxvalue;

-- 事务闪回的核心：查询undo_sql，执行即可
需要授权： grant select any transaction to scott;
 
-- undo_sql存在于表：desc flashback_transaction_query;
 
-- 查询undo_sql前，需要在sys中修改设置：alter database add supplemental log data; 
 
-- 查询undo_sql
select OPERATION ,UNDO_SQL from  flashback_transaction_query   
where  XID  ='0A000500C1060000';
 
-- bugdml操作数据时，不要太快（批量复制 ）
凡是能够撤销的事务，都必须提前开日日志：在sys中执行alter database add supplemental log data;

日志：打开日志，alter database add supplemental log data;

undo_sql：修改历史 300 → 99

delete：修改现在 300 → 299

10 导入导出

-- 向导
exp
imp

10.1 导出

-- 导出：exp ，直接cmd操作 
exp不是sql语句，是一个工具，直接在cmd中执行
 
-- 表方式 
exp scott/tiger@127.0.0.1/ORCL file=d:/back/bk2.dmp log=d:/back/log.log tables=emp,dept
 
-- 用户方式：
exp scott/tiger@127.0.0.1/ORCL file=d:/back/bk2.dmp log=d:/back/log2.log 
	
-- 全库方式:DBA角色的（sys/system）
exp system/manager@127.0.0.1/ORCL file=d:/back/bk2.dmp log=d:/back/log2.log full=y

-- 表方式 
imp system/manager@127.0.0.1/ORCL  file=d:/back/bk.dmp log=d:/back/log.log tables=emp,dept  fromuser=scott touser=hr commit=y ignore=y 
 
-- 用户方式：(DBA角色), 从scott导入到system/manager
imp system/manager@127.0.0.1/ORCL  file=d:/back/bk2.dmp log=d:/back/log2.log   fromuser=scott touser=hr commit=y ignore=y 
 
-- 全库方式:
imp system/manager@127.0.0.1/ORCL  file=d:/back/bk2.dmp log=d:/back/log2.log    commit=y ignore=y destroy=y

11 分布式数据库

大->小 
-- 物理上分开存放，逻辑上一个整体
-- 独立性：客户端不必关心数据如何分割和存储 ，只需要关心数据本身即可
 
-- 分布式数据库三种操作：
a.本地操作
b.远程操作 sqlplus scott/tiger@192.168.2.128/orcl
注意：i. 关闭远程防火墙
	 ii.远程：将2个文件（tnsnames.ora、listener.ora）的Host值改成IP地址或计算机名
	 
c.分布操作, 借助于数据库链路：
其中l2vm是链路名，remoteORCL是服务名，用于连接远程的服务（orcl）
 
-- 创建服务名：net manager
-- 创建链路：（链路的单向的）
create database link l2vm  connect to  scott identified by tiger  using 'remoteORCL';
 
-- 同时操作本地和远端 ，关联查询
select ename,dname from emp@l2vm e,dept d where  e.deptno = d.deptno ;
emp@l2vm-> remoteemp
select ename,dname from emp@l2vm e,dept d where  e.deptno = d.deptno ;
 
-- 创建同义词屏蔽掉分布式访问的关键字
create synonym remoteemp for emp@l2vm ;
select ename,dname from remoteemp  e,dept d where  e.deptno = d.deptno ;
 
-- 视图
create view empremoteview
as 
select ename,dname from remoteemp  e,dept d where  e.deptno = d.deptno ;
select * from empremoteview;
查看星期的显示格式： select to_char(sysdate,'day') from dual;

11.1 快照

-- 快照：可以备份远程数据库（快照有一定延迟）
create snapshot empdeptss
refresh start with sysdate 
next next_day(sysdate,'星期三')   
as
select * from emp@l2vm 
;
 
-- 触发器（实时备份） 
工资问题：如果本地数据库中修改工资了，则同步到远程
create or replace trigger update_emp
after update on emp 
for each row
begin
	update emp@l2vm  set sal=:new.sal where empno=:new.empno ;
end ;
/
update后注意commit;

12 EM管理器

-- EM管理器（了解）
1.服务：OracleDBConsocleorcl
2.关闭远程防火墙  本地->VM
 
-- https://192.168.2.128:1158/em
 
http://192.168.1.11
临时表：中间产物，用完就自动删除
select *from emp order by sal ;
 
-- 延迟：约束
name: check( length(name) >2 )  ->延迟
a
b
commit ;  延迟报错
 
-- 用户权限：
登录验证
密码校验sqlplus scott/tiger  
 
 
-- 外部校验 
条件： 当前系统用户必须是 dba角色 查看：右键计算机- 本地用户和组 ）
外部校验 sqlplus / as sysdba （自动屏蔽密码校验）
全局校验：生物认证、 token（令牌环，银行卡：  U盾）
 
-- 预定义账户：
scott
sys : 最高权限（一切权限）
system 	：DBA权限
 
-- 权限：
系统权限： 允许用户执行对【数据库】的特定行为  （创建用户、创建表）
对象权限： 允许用户执行一个特定的【对象】  （ scott查看hr的一张表）
 
-- 系统权限：
创建用户
create user  yq identified by yq ;  没有任何权限（包含登录权限）
赋予yq登录权限grant  CREATE SESSION to yq;
 
-- 对象权限：让yq可以查询scott用户的emp表
grant select on scott.emp to yq ;
	
-- yq查看：
select *from scott.emp;

TroyeKK小站

Explorer

02.oracle

Table of Contents

1 基础

1.1 技巧

1.2 修改

1.3 append

1.4 常见问题

2 伪列

2.1 相同SQL语句在执行时，rownum值不变

2.2 子查询

2.2.1 order by

2.2.2 top-n

2.3 删除重复数据rownum、rowid

2.3.1 数据准备

2.3.2 删除单条数据

2.3.3 去重distinct错误示范

2.3.4 删除多条数据

3 序列

3.1 创建序列

3.2 查看序列

3.3 增加序列

3.3.1 序列操作

3.3.2 自增（默认步长1，起始值1）

3.3.3 自增（步长2，起始值1000）

3.3.4 循环（最小1，最大9）

3.3.5 裂缝问题

3.4 修改序列：不影响之前产生的序列

3.5 删除序列

4 同义词（别名）

4.1 私有/专有同义词

4.2 公有同义词（创建、删除，一般建议由管理员操作）

5 PLSQL

5.1 变量

5.1.1 代码1（引用型变量 pname emp.ename%type;）

5.1.2 代码2（记录型变量 emp_info emp%rowtype ; ）

5.2 if语句

5.2.1 代码

5.3 循环while, do…while, for

5.3.1 代码1

5.3.2 代码2

5.4 游标（光标）cursor：集合，多个值

5.4.1 代码1

5.4.2 代码2

5.4.3 代码3

5.5 例外（异常）

5.5.1 系统例外

5.5.2 自定义例外

5.5.3 代码1

5.5.4 代码2

5.5.5 代码3

5.6 存储过程（方法）

5.6.1 无参存储过程

5.6.2 有参存储过程

5.7 存储函数（函数）

5.7.1 必须含有return

5.7.2 代码

5.8 存储过程/存储函数？

5.8.1 代码

5.9 包

5.9.1 代码

6 触发器

6.1 语句级、行级触发器

6.2 语句级触发器

6.2.1 插入

6.2.2 更新

6.3 行级触发器

6.3.1 代码1

6.3.2 代码2

6.4 数据校验

7 数字字典

8 DBCA

9 闪回

9.1 闪回数据

9.2 闪回表

9.3 闪回事务

10 导入导出

10.1 导出

11 分布式数据库