--------------------------------------------------------视图---------------------------------------------------------------
/*视图：从一张表或多张表中检索数据，其内部形式为sql语句的定义。
视图划分为：简单视图和复杂视图
简单视图：从单表获取数据
复杂视图：从多表获取数据
通常情况下，只对视图做查询，不做增删改
create or replace view 视图名 as 查询语句的定义
*/
--授予SCOTT创建视图的权限
grant create view to scott;
----创建简单视图
create or replace view vw_emp as
select * from emp;
----使用视图：查询vw_emp
select * from vw_emp;
--常见复杂视图：查询部门及其对应的员工信息
create or replace view vw_deptandemp
as
select d.deptno,d.dname,e.empno,e.ename from dept d inner join emp e on d.deptno=e.deptno;
select * from vw_deptandemp;
-----------------------------------------------------序列----------------------------------------------------------------------
--建议每张表建一个自己的序列--使用序列（oracle不像MySQL支持自增，需要实现序列）
insert into emp(empno,ename) values(seq_emp.nextval,'2222');
----------------------------------------------------触发器----------------------------------------------------------------------
/*触发器：模式触发器，数据级触发器、DML触发器
-------------
1、复杂的安全性检查
2、数据确认
3、实现审计功能
4、完成数据的备份和同步
-------------
DML触发器：
语句级触发器：无论如何操作影响多少行记录，触发器只被调用一次
*/
--当用户向表中增删改数据时，将用户的操作记录到日志表
insert into emp(empno,ename) values(seq_emp.nextval,'55555');
update emp set ename='7777' where empno=3;
---创建语句级触发器
create or replace trigger tri_emp_log
after --触发时机
update or insert on emp
begin
insert into emp_log values(seq_log.nextval,'插入信息',sysdate);
end;
---创建一个行级触发器：
create or replace trigger tri_emp_log1
after --触发时机
update or insert on emp
for each row
begin
insert into emp_log values(seq_log.nextval,'插入信息',sysdate);
end;
------删除emp表中记录时，将被删除的记录备份到emp_back表中
-------创建触发器实现将表中数据在删除前备份到emp_back中
create or replace trigger tri_emp_back
before
delete on emp
for each row
begin
insert into emp_back
values(:old.empno,:old.ename,:old.job,:old.mgr,:old.hiredate,:old.sal,:old.comm,:old.deptno);
end;
delete from emp where empno=3;
---作业：向部门表插入一条记录，同时将该条记录备份到dept_back中
create or replace trigger tri_dept_back
before
insert on dept
for each row
begin
insert into dept_back
values(:new.deptno,:new.dname,:new.loc);
end;
insert into dept
values(seq_deptno.nextval,'技术部'，'二楼');
/*
instead of触发器
代替触发器
语法：create or replace trigger 触发器名
instead of insert or update or delete
on 视图名
for each row
begin
//通过sql语句操作每一张和视图相关的表
end;
*/
create or replace trigger tri_deptandemp_back
instead of insert -- or update or delete
on vw_deptandemp --视图名
for each row
begin
insert into dept(deptno,dname)
values(:new.deptno,:new.dname);
insert into emp(empno,ename,deptno)
values(:new.empno,:new.ename,:new.deptno);
end;
insert into vw_deptandemp
values(18,'销售部',9999，'啊啊啊');
select * from vw_deptandemp;
/*
系统事件触发器：应用对象为数据库
*/
--------特点： 启动数据后将执行操作
create or replace trigger tr_qd
after startup
on database
begin
insert into scott.emp_log values (scott.seq_log.nextval,'用户启动数据库',sysdate);
end;
-----------------------------------------------------------
create or replace trigger tr_qd1
before shutdown
on database
begin
insert into scott.emp_log values (scott.seq_log.nextval,'关闭启动数据库',sysdate);
end;
--事务：保证数据一致性
/*事务：用户定义的一系列操作步骤（一条或者多条语句），这些操作要么全部执行成功，要么全部执行失败。
*/
--循环插入记录，并提交事务
--事务隐式执行
begin
for i in 1..5 loop
insert into dept values(seq_deptno.nextval,'部门'||seq_deptno.nextval,seq_deptno.nextval||'楼');
end loop;
commit; --提交事务（事务结束）
end;
--模拟aa给cc转账，每次转500，当余额不足时，提醒无法转账
declare
--声明一个异常对象
err_money exception;
--通知编译器使用exception_init()将异常对象与oracle错误代码绑定
pragma exception_init(err_money,-02290);
begin
dbms_output.put_line('转账开始~~~~~~~~~~~~~~~');
update account set amoney = amoney + 500 where aname='cc';
update account set amoney = amoney - 500 where aname='aa';
commit;
dbms_output.put_line('转账结束~~~~~~~~~~~~~~');
exception
when err_money then
dbms_output.put_line('余额不足，无法转账');
rollback;
end;
--------------------向dept表插入10条记录
declare
--声明一个异常对象
err_dept exception;
--通知编译器使用exception_init()将异常对象与oracle错误代码绑定
pragma exception_init(err_dept,-00001);
begin
insert into dept values(seq_deptno.nextval,'部门'||seq_deptno.nextval,seq_deptno.nextval||'楼');
insert into dept values(seq_deptno.nextval,'部门'||seq_deptno.nextval,seq_deptno.nextval||'楼');
insert into dept values(seq_deptno.nextval,'部门'||seq_deptno.nextval,seq_deptno.nextval||'楼');
savepoint sp; --定义事务保存点
insert into dept values(seq_deptno.nextval,'部门'||seq_deptno.nextval,seq_deptno.nextval||'楼');
insert into dept values(seq_deptno.nextval,'部门'||seq_deptno.nextval,seq_deptno.nextval||'楼');
insert into dept values(seq_deptno.nextval,'部门'||seq_deptno.nextval,seq_deptno.nextval||'楼');
insert into dept values(seq_deptno.nextval,'部门'||seq_deptno.nextval,seq_deptno.nextval||'楼');
insert into dept values(seq_deptno.nextval,'部门'||seq_deptno.nextval,seq_deptno.nextval||'楼');
insert into dept values(seq_deptno.nextval,'部门'||seq_deptno.nextval,seq_deptno.nextval||'楼');
insert into dept values(10,'部门'||10,10||'楼');
commit; --提交事务（事务结束）
exception
when err_dept then
dbms_output.put_line('失败');
rollback to sp;
end;
/*
事务影响DML：
read only: 只读操作，即只允许事务内使用select
read write: 可读写操作，属于默认值，即允许事务内使用
select insert update delete
read only: 只读操作，即只允许事务内使用select
read write: 可读写操作，属于默认值，即允许事务内使用
select insert update delete
*/
set transaction read only;
--update emp set ename = '';
select * from emp;
--commit;
set transaction read write;
update emp set ename = 'aaaa';
commit;
--隔离性
/*
事务的隔离级别：
oracle提供了两种事务隔离级别1、read committed（已提交读取）：禁止脏读，但允许不可重复和幻读，属于默认隔离级别
2、serializable（串行化读取）：禁止脏读，禁止不可重复读，禁止幻觉读，尽量少使用，以为会降低数据库的性能
*/
------------------------------------------------------函数------------------------------------------------------------------------------
/*
oracle函数：
1、内置函数： to_date()
2、自定义函数
语法：
create or replace function 函数名[（参数1，参数2）]
return 函数返回值类型（必须有）
is/as --取代了declare
声明变量等
begin
函数执行体
return 返回具体的值
end；
*/
--查询人和总工资
select ename,(sal+comm) from emp;
---使用nvl(字段，‘新值’)对null值进行转换为一个自定义的新值
select ename,(nvl(sal,0)+nvl(comm,0)) from emp;
--创建函数 first_day(),用来返回每个月第一天
select * from student where substr(to_char(sdate,'yyyy-mm-dd'),9,2) = '01';
----------------------------自己写函数---------------------------------------
/*
使用函数：
1、直接在DML语句中被使用
2、在PL/SQL块中使用
*/
create or replace function first_day1(fdate date)
return date
as
v_date date;
v_year varchar2(10);
v_month varchar2(10);
v_day varchar2(10):='01';
begin
v_year:=to_char(fdate,'yyyy');
v_month:=to_char(fdate,'mm');
v_date:=to_date(v_year||v_month||v_day,'yyyy-mm-dd');
return v_date;
end;
--测试
select first_day(hiredate) from emp;
select * from emp where hiredate=first_day1(hiredate);
-----------------------------------------------------------------------------------------------------------------------------
create or replace function first_day(fdate date)
return date
as
v_date date;
begin
--v_date:=to_date(substr(to_char(fdate,'yyyy-mm-dd'),1,8)||'01','yyyy-mm-dd');
--v_date:=last_day(fdate-1)+1;
v_date:=last_day(add_months(fdate,-1))+1;
-- dbms_output.put_line(v_date);
return v_date;
end;
--测试
select * from emp where hiredate=first_day(hiredate);
declare
v_count number;
v_ename emp.ename%type;
v_hiredate emp.hiredate%type;
begin
select count(*) into v_count from emp where hiredate=first_day(hiredate);
dbms_output.put_line(v_count||'条信息');
for i in 1 .. v_count loop
select ename,hiredate into v_ename,v_hiredate from
(select rownum as rn,ename,hiredate from emp where hiredate=first_day(hiredate))
where rn=i;
dbms_output.put_line('生日在月初的人是：'||v_ename||' 生日是： '||v_hiredate);
end loop;
end;
-----使用函数根据员工编号查询员工的总工资，
create or replace function total_sal(v_empno emp.empno%type) --名字在前，类型在后
return emp.sal%type
as
v_sal emp.sal%type;
begin
select nvl(sal,0)+nvl(comm,0) into v_sal from emp where empno=v_empno;
return v_sal;
end;
--测试
begin
dbms_output.put_line(total_sal(7499));
end;
------------------------------------------------------存储过程-----------------------------------------------------------------------------
/*存储过程是将一组sql预编译（预编译对象就是把一些格式固定的SQL编译后，
存放在内存池中即数据库缓冲池，当我们再次执行相同的SQL语句时就不需要预编译的过程）之后，
并且经过优化后直接存储于数据库中，使用时无需再次编译，提高了工作效率。
函数侧重于对复杂过程的处理结果，能直接写在SQL语句
存储过程侧重于对SQL语句的批量操作，不能应用在SQL语句中
存储过程的参数通过out\inout返回值
语法：
create [or replace] .
procedure 过程名(参数1 in number,参数2 in varchar2)
--参数的数据类型只需要指明类型名即可，不需要指定宽度。
as --可以理解为pl/sql的declare关键字，用于声明变量
begin
--sql语句
end;
*/
-------------------------------创建存储过程，无参
create or replace procedure pro_empsal
is
begin
update emp set sal=10000;
commit;
end;
------------------------------一、创建存储过程，将emp表中‘aaaa’的deptno改为10， 通过in参数
create or replace procedure pro_emp(v_ename in emp.ename%type,v_deptno emp.deptno%type) --in可以缺省
as
begin
update emp set deptno=v_deptno where ename=v_ename;
commit;
end;
--execute pro_emp('aaaa',10);
-------测试
begin
pro_emp('aaaa',10);
end;
-----------------------二、创建存储过程，通过out参数，将emp表中‘cccc’的deptno改为50，并返回修改后的年龄进行输出显示
create or replace procedure pro_emp1(v_ename in emp.ename%type,v_deptno in emp.deptno%type,v_out_result out number)
as
err_notfound exception;
--未找到记录的错误代码： 100 ------------------加载找不到的异常
pragma exception_init(err_notfound,100);
begin
update emp set deptno=v_deptno where ename=v_ename;
select deptno into v_out_result from emp where ename=v_ename;
commit;
exception
when err_notfound then
dbms_output.put_line('没有找到记录');
end;
-----------------测试1
方式一： SQL> var a number; --在命令行声明变量 需要使用 var 关键字
SQL> execute pro_stu1(50,‘张三’,:a); --在调用存储过程中一定要为输出参数指定输出的变量名，将存储过程的输出结果利用“ ：”与 变量绑定，运行后直接输出
-------测试2
declare
v_deptno emp.deptno%type;
begin
pro_emp1('dddd',50,v_deptno);
dbms_output.put_line(v_deptno);
end;
------------------------怪异想法测试----------select到null值之后的异常问题，可以用if_else代替处理
create or replace procedure pro_nullvalues(v_ename in emp.ename%type,v_deptno in emp.deptno%type,v_out_result out number)
as
v_count number;
begin
select deptno into v_count from emp where ename=v_ename;
if v_count = 0 then
dbms_output.put_line('没有找到记录');
else
update emp set deptno=v_deptno where ename=v_ename;
select deptno into v_out_result from emp where ename=v_ename;
end if;
commit;
end;
-------测试
declare
v_deptno emp.deptno%type;
begin
pro_emp1('dddd',50,v_deptno);
dbms_output.put_line(v_deptno);
end;
-----------------------三、创建存储过程，通过in out参数，将emp表中‘cccc’的deptno改为30
create or replace procedure pro_emp2(v_inout_ename in out emp.ename%type, v_inout_deptno in out emp.deptno%type)
as
begin
update emp set deptno=v_inout_deptno where ename=v_inout_ename;
select deptno into v_inout_deptno from emp where ename=v_inout_ename;
commit;
end;
--------------测试
declare
v_ename emp.ename%type:='cccc';
v_deptno emp.deptno%type :=30;
begin
pro_emp2(v_ename,v_deptno);
dbms_output.put_line(v_deptno);
end;
---------------------------------------------------程序包--------------------------------------------------------------------------
/*如果某个功能模块需要定义大量的自定义函数和存储过程，为了对这些函数和过程管理上的方便，可以使用程序包来组织和管理这些函数和过程
程序包：简称包，由规范和主体组成
规范：只有函数和过程的声明，没有具体实现
主体：必须遵从规范，实现规范中所定义的函数和过程
*/
--需要在包中定义函数和过程
create or replace package pk_first
as
function fun_checkename(v_empno number)
return varchar2; --声明一个函数
procedure pro_updatesal(v_empno number); --声明一个过程
end pk_first;
--------------------创建主体
create or replace package body pk_first
as
--函数：根据编号查询姓名
function fun_checkename(v_empno number)
return varchar2
is
v_enam varchar2(50);
begin
select ename into v_enam from emp where empno=v_empno;
return v_enam;
--函数在主体中的实现在end后需要添加函数名
end fun_checkename;
--根据编号修改工资
procedure pro_updatesal(v_empno number)
is
begin
update emp set sal=sal+1000 where empno=v_empno;
commit;
end pro_updatesal;
end pk_first;
begin
----使用包中的函数
dbms_output.put_line(pk_first.fun_checkename(7499));
--调用包中的过程
pk_first.pro_updatesal(7399);
end;
select * from emp where empno=7499;
---------------------------------------------同义词-----------------------------------------------
/*同义词：是现有对象的一个别名，然后存储于数据库中（作为一个对象来使用）
共有两种类型：私有同义词、共有同义词
sys有这个权限
--作用：
1、简化sql语句
2、隐藏对象的名称和所有者
3、提供了对对象的公共访问
语法：
create [public] synonym 同义词名称
for 模式.对象
*/
grant create synonym to scott;
grant create public synonym to scott;
---私有同义词
create or replace synonym emp1 for emp;
select * from emp1;
/*
创建公有同义词的步骤：
1、由于同义词映射的是某个对象，所以需要对其他普通用户授予对象权限
2、创建公有同义词
*/
----公有同义词 ----用户看不见
create public synonym public_sy_emp for scott.emp;
grant select on emp to xiyou;
select * from public_sy_emp;
-------------------------------------------------索引------------------------------------------------------------
/*索引：数据库中内部编排数据的方法，默认情况下创建的索引为B-Tree索引
优点：加快访问速度，加强行的唯一性，适合数据的繁琐查询
缺点：带索引的表需要更多的存储空间，增删改操作将会变得缓慢
normal：非唯一索引（一般索引），即索引列上的值允许重复，通常用户可以自己定义要建立索引的列，也是默认索引方式
--------
unique：唯一索引，与非唯一索引最大的区别就是列上增加了一层唯一约束，即列值不允许重复
--------
通常主键或唯一约束建立时，唯一索引将被自动建立
bitmap:位图索引，数据以一种压缩格式存放，因此占用的磁盘空间比标准索引要小的多，
使用索引的原则：
1、在需要经常搜索的列上创建索引
2、需要经常排序的列上创建索引
3、经常用于where字句的列上创建索引
4、主键或唯一约束建立时，唯一索引将被自动建立
语法：
create normal\unique\bitmap index 索引名称 on emp (列名1，列名2···)
nocompress | compress prefix_length nosort | reverse local
*/
/*
1、4、5、6、7、8、9 二分查找
先排序
*/
--对员工姓名列创建索引
create index index_ename on emp(ename) nocompress;
------------------------------------------------------
select * from emp where ename='aaaa';
begin
for i in 1 .. 100 loop
insert into emp(empno,ename,deptno) values(i,dbms_random.string('w',3),30);
end loop;
commit;
end;
select * from emp where ename like '%G%';
delete from emp_back;
update emp set deptno=30;
-------------------------------------------------------------------------------作业
--1、编写存储过程。根据雇员编号，查询该雇员的姓名和薪水，并通过输出参数输出
--2创建存储过程 ，交换两个变量的值？通过调用存储过程传入实参，最后输出！
create or replace procedure pro_emp_enameandsal(v_empno in emp.empno%type,v_ename out emp.ename%type,v_sal out emp.sal%type)
as
err_notfound exception;
--未找到记录的错误代码： 100 ------------------加咋找不到的异常
pragma exception_init(err_notfound,100);
begin
select ename,sal into v_ename,v_sal from emp where empno=v_empno;
commit;
exception
when err_notfound then
dbms_output.put_line('没有找到记录');
end;
-------测试1
declare
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
pro_emp_enameandsal(7499,v_ename,v_sal);
dbms_output.put_line(v_ename);
dbms_output.put_line(v_sal);
end;
------------------------------------------------------------------------------
create or replace procedure pro_ab(v_inout_a in out number, v_inout_b in out number)
as
v_t number;
begin
v_t := v_inout_a;
v_inout_a := v_inout_b;
v_inout_b := v_t;
commit;
end;
--------------测试
declare
v_a number := 2;
v_b number := 8;
begin
pro_ab(v_a,v_b);
dbms_output.put_line(v_b);
end;