一、视图
对一个查询结果的封装（视图里面所有的数据都是来自于它查询的那张表，视图里面不存储任何数据）
视图的好处：1.能够封装复杂的查询结果
2.屏蔽表中的细节
语法：create [or replace] view 视图名字 as 查询语句 [with read only]
or replace--如果存在就覆盖
with read only--只允许读，不允许修改，一般都会加
同义词--就是给表、视图等取另外一个名字
--创建一个视图
create or replace view view_test1 as select ename,job,deptno from emp;
--通过视图修改数据
update view_test1 set ename='SIMITH2' where ename = 'smith2';
--创建一个只读视图
create or replace view view_test2 as select ename,job,deptno from emp with read only;
--同义词概念
create synonym yuangong for view_test2;
select * from yuangong
select * from view_test1;
select * from emp;
二、序列
序列：生成类似于auto_increament（mysql中的id自增长）的ID
语法：create sequence 序列名称
start with 起始值
increament by 增长量
maxvalue 最大值 | nomaxvalue
minvalue 最小值 | nominvalue
cycle | nocycle 是否在max和min之间循环
cache 缓存数量 | nocache
用法：sequencename.currval 获得当前值（至少在使用一次nextval之后才能使用）
sequencename.nextval 获得下一个值
最常用的写法：careate sequence seq_test;
--创建一个1,3,5....30 切循环的序列
create sequence seq_test1
start with 1
increment by 2
maxvalue 30
cycle
cache 3;
--最常用的序列写法
create sequence seq_test2; --默认从1开始，每次增长1，无最大值，不循环，不缓存--1,2,3,4,5,6....
select seq_test1.nextval from dual;
select seq_test1.currval from dual;
三、索引
索引：相当于一本书的目录，能够提高我们的查询效率。如果某一列经常作为查询条件，就有必要创建索引（数据量大的情况下）
语法：create index 索引名称 on 表名(列名)
原理：btree balanceTree 平衡二叉树
注意：主键约束自带主键索引，唯一约束自带唯一索引。如果某列作为查询条件的时候创建索引，查询会变快，但修改，插入的时候会变慢索引创建好之后，每过一段时间，DBA都回去重构索引
--五百万条数据测试
create table wubaiwu(
name varchar(20),
address varchar(20)
);
declare
begin
for i in 1..5000000 loop
insert into wubaiwu values('姓名'||i,'地址'||i);
end loop;
commit;
end;
--在没有索引的情况下查询姓名为 "姓名3000000" 的数据
select * from wubaiwu where name='姓名3000000';
--创建索引，再查询
create index idx_wubaiwu on wubaiwu(name);
select * from wubaiwu where name='姓名3000000';
select * from wubaiwu where name='姓名3000000';
四、PLSQL编程
PLSQL编程：procedure Languege——过程语言，Oracle对SQL的一个扩展
语法：declare --声明变量
变量名 变量类型;
变量名 变量类型 := 初始值
vsal emp.sal%type; --引用表字段类型%%%%
vrow emp%rowtype; --引用表中一条记录的类型
begin
业务逻辑
end;
declare
i varchar(10) := '张三';
begin
dbms_output.put_line(i); --类型java中的System.out.print
end;
--查询7369的工资，并打印
declare
vsal emp.sal%type; --引用emp表中sal字段的类型
begin
select sal into vsal from emp where empno=7369; --将查询结果赋值给vsal
dbms_output.put_line(vsal);
end;
--查询7369员工信息并打印
declare
vrow emp%rowtype; --引用emp表一条记录的类型
begin
select * into vrow from emp where empno=7369;
dbms_output.put_line('姓名:'||vrow.ename||' 工资:'||vrow.sal);
end;
条件判断语句
/*
条件判断语句：
if ** then
elsif ** then
else
end if
*/
declare
age number := &aa;
begin
if age < 18 then
dbms_output.put_line('未成年');
elsif age >= 18 and age <= 24 then
dbms_output.put_line('青年');
elsif age > 24 and age <= 40 then
dbms_output.put_line('中年');
else
dbms_output.put_line('老年');
end if;
end;
循环语句
/*
循环语句
for循环：
for x in [reverse] 起始值..终止值 loop
循环体
end loop
while循环：
while 条件 loop
循环体
end loop
loop循环：
loop
循环体
exit when 条件 --满足条件时退出
end loop
*/
--for循环输出1-10
declare
begin
for i in reverse 1..10 loop
dbms_output.put_line('未成年'||i);
end loop;
end;
--while循环输出1-10
declare
val number := 1;
begin
while val<=10 loop
dbms_output.put_line(val);
val := val+1;
end loop;
end;
--loop循环输出1-10
declare
val number := 1;
begin
loop
dbms_output.put_line(val);
val := val+1;
exit when val>10;
end loop;
end;
--输出菱形
declare
begin
for i in 1..5 loop
for j in 1..5 loop
if(i+j>=4 and j<=i+2 and i+j<=8 and j>=i-2) then
dbms_output.put('*');
else
dbms_output.put(' ');
end if;
end loop;
dbms_output.put_line(' ');
end loop;
end;
select sal from emp where empno=7369
五、游标
游标：数据的缓冲区，从表中查询出结构集，指定访问一条记录，类似于数组。游标还可以用于抛出自定义异常
语法：cursor 游标名 [(参数名, 参数类型)] is 查询结果集 --声明游标
使用方式：1.声明游标
2.打开游标 --open 游标名
3.从游标中获取数据：fetch 游标名 into 变量
游标名%found --找到数据
游标名%nofound --没有找到数据
4.关闭游标 --close 游标名
--输出所有员工姓名(不带参数游标)
declare
cursor vrows is select * from emp;
vrow emp%rowtype;
begin
open vrows --打开游标
loop
fetch vrows into vrow;
exit when vrows%ontfound --没有找到的时候退出
dbms_output.put_line(vrow.ename || ' ---' || vrow.sal);
end loop;
close vrows;
end;
--输出指定部门下的员工姓名和工资(带参数的游标)
declare
--声明游标
cursor vrows(dno number) is select * from emp where deptno=dno;
--声明变量记录每一行数据
vrow emp%rowtype
begin
open vrows(10) --查询10号部门
loop
fetch vrows into row
exit when vrows%notfound
dbms_output.put_line('姓名:'||vrow.ename||' 工资:'||vrow.sal);
end loop;
close vrows;
end;
--for循环遍历游标，不需要打开关闭游标
declare
cursor vrows is select * from emp;
begin
for vrow in vrows loop
dbms_output.put_line('姓名:'||vrow.ename||' 工资:'||vrow.sal);
end loop;
end;
--涨工资，总裁涨1000，经理800，其他400
declare
cursor vrows is select * from emp;
vrow emp%rowtype;
begin
open vrows;
loop
fetch vrows into vrow;
exit when vrows%notfound;
if vrow.job='PRESIDENT' then
update emp set sal=sal+1000 where empno=vrow.empno;
elsif vrow.job='MANAGER' then
update emp set sal=sal+800 where empno=vrow.empno;
else
update emp set sal=sal+400 where empno=vrow.empno;
end if;
end loop;
close vrows;
commit;
end;
/*
异常
*/
--捕获异常
declare
val emp%rowtype;
begin
select * into val from emp where empno=123;
exception
when others then
dbms_output.put_line('捕获到异常');
end;
--自定义异常
declare
cursor vrows(dno number) is select * from emp where deptno=dno;
val emp%rowtype;
no_emp exception;
begin
open vrows(123);
fetch vrows into val;
if vrows%notfound then --判断是否有值
raise no_emp;
end if;
close vrows;
exception
when no_emp then
dbms_output.put_line('捕获到自定义异常');
end;
六、存储过程和存储函数
存储过程：实际上是封装在服务器上的一段PLSQL代码，已经编译好了的代码。客户端调用存储过程，执行效率就会非常高兴。
语法：create [or replace] procedure 存储过程名称 (参数名 in|out 参数类型，参数名 in|out 参数类型) --in 代表输入参数，out 代表输出参数。
as | is
--声明部分
begin
--业务逻辑
end;
存储函数：和存储过程差不多，只是多了一个返回值。但有了out参数，好像存储函数就没啥用了。
语法：create [or replace] procedure 存储过程名称 (参数名 in|out 参数类型) return 返回值类型
.....其他和储存过程一样
--创建一个存储过程，给指定员工涨工资，并输出涨工资前后的工资，，，输入参数in--vempno,vsal
create or replace procedure proc_updateSal(vempno in number, vsal in number)
is
currentSal number;
begin
select sal into currentSal from emp where empno=vempno;
dbms_output.put_line('涨薪前的工资：'||currentSal);
update emp set sal = sal + vsal where empno=vempno; --更新工资
dbms_output.put_line('涨薪前的工资：'||(currentSal+vsal));
commit;
end;
--调用存储过程
--方式一
call proc_updateSal(7788, 10);
--方式二，也是最长用的方式
declare
begin
proc_updateSal(7788,-100);
end;
--查询员工年薪
select sal*12+nvl(comm, 0) from emp where empno=7788;
create or replace procedure get_YearSal(vempno in number, yearSal out number)
is
begin
select sal*12+nvl(comm, 0) into yearSal from emp where empno=vempno;
end;
--测试存储过程
declare
yearSal number;
begin
get_yearSal(7788, yearSal);
dbms_output.put_line(yearSal);
end;
--查询员工年薪---使用存储函数
create or replace function fun_YearSal(vempno in number) return number
is
yearSal number;
begin
select sal*12+nvl(comm, 0) into yearSal from emp where empno=vempno;
return yearSal;
end;
--测试存储函数
declare
yearSal number;
begin
yearSal:=fun_yearSal(7788);
dbms_output.put_line(yearSal);
end;
七、触发器
触发器：当用户执行了insert，update，delete等操作后，可以触发一系列其他动作
语法：create [or replace] trigger 触发器名字
before | after
insert | update | delete
on 表名
[for each row] --是否应用到每一行
declare
...
begin
...
end;
触发器分类：
语句级触发器： 不管影响多少行，都只触发一次，也就是不加 for each row
行级触发器： 影响了多少行就触发多少行。
其他： :old 更新前的记录
:new 更新或的记录
--新员工入职后，输出一句话：欢迎来到德莱联盟
create or replace trigger tri_test1
before
insert
on emp
declare
begin
dbms_output.put_line('欢迎来到德莱联盟');
end;
--数据校验，周二老板不在，不能办理入职手续
create or replace trigger tri_test2
before
insert
on emp
declare
vday varchar2(10);
begin
select trim(to_char(sysdate,'day')) into vday from dual;
if vday='星期二' then
dbms_output.put_line('周二老板不在，不能办理入职手续');
--抛出系统异常
raise_application_error(-20001,'周二老板不在，不能办理入职手续');
end if;
end;
insert into emp(empno,ename) values(1188,'德莱厄斯')
--更新所有员工的工资，并输出一句话
create or replace trigger tri_test3
after
update
on emp
for each row
declare
begin
dbms_output.put_line('涨钱啦！');
end;
update emp set sal = sal+10;
--判断员工涨工资后的工资一定要大于更新后的工资
create or replace trigger tri_updateSal
before
update
on emp
for each row
declare
begin
if :old.sal>:new.sal then
raise_application_error(-20002,'这不是涨工资是扣工资');
end if;
end;
update emp set sal = sal-1;
模拟mysql中的id只增长属性 auto_increment
--创建测试表
create table person(
pid number primary key,
pname varchar2(20)
);
--创建自增长序列
create sequence seq_person_pid;
--创建触发器
create or replace trigger tri_add_pid
before
insert
on person
for each row
declare
begin
dbms_output.put_line('嘻嘻'||:new.pname);
select seq_person_pid.nextval into :new.pid from dual;
end;
;
insert into person values(null,'张三');
select seq_person_pid.nextval from dual;
select * from person;
select * from emp;
