一、视图
对一个查询结果的封装（视图里面所有的数据都是来自于它查询的那张表，视图里面不存储任何数据）
视图的好处：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 yuangongselect * 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 beginfor i in 1..5000000 loopinsert 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;
declarei varchar(10) := '张三'; begindbms_output.put_line(i); --类型java中的System.out.print end;--查询7369的工资，并打印 declarevsal emp.sal%type; --引用emp表中sal字段的类型 beginselect sal into vsal from emp where empno=7369; --将查询结果赋值给vsal dbms_output.put_line(vsal); end;--查询7369员工信息并打印 declarevrow emp%rowtype; --引用emp表一条记录的类型 beginselect * into vrow from emp where empno=7369;dbms_output.put_line('姓名:'||vrow.ename||' 工资:'||vrow.sal); end;
条件判断语句
/*条件判断语句：if ** thenelsif ** thenelseend if */ declare age number := &aa; beginif age < 18 thendbms_output.put_line('未成年');elsif age >= 18 and age <= 24 thendbms_output.put_line('青年');elsif age > 24 and age <= 40 thendbms_output.put_line('中年');elsedbms_output.put_line('老年');end if; end;
循环语句
/*循环语句for循环：for x in [reverse] 起始值..终止值 loop循环体end loopwhile循环：while 条件 loop循环体end looploop循环：loop循环体exit when 条件 --满足条件时退出 end loop */--for循环输出1-10 declarebeginfor i in reverse 1..10 loopdbms_output.put_line('未成年'||i);end loop; end;--while循环输出1-10 declareval number := 1; beginwhile val<=10 loopdbms_output.put_line(val);val := val+1;end loop; end;--loop循环输出1-10 declareval number := 1; beginloopdbms_output.put_line(val);val := val+1;exit when val>10;end loop; end;--输出菱形 declarebeginfor i in 1..5 loopfor j in 1..5 loopif(i+j>=4 and j<=i+2 and i+j<=8 and j>=i-2) thendbms_output.put('*');elsedbms_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 游标名
--输出所有员工姓名(不带参数游标) declarecursor vrows is select * from emp;vrow emp%rowtype; beginopen vrows --打开游标 loopfetch 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 beginopen vrows(10) --查询10号部门 loopfetch vrows into rowexit when vrows%notfounddbms_output.put_line('姓名:'||vrow.ename||' 工资:'||vrow.sal);end loop;close vrows; end;--for循环遍历游标，不需要打开关闭游标 declarecursor vrows is select * from emp; beginfor vrow in vrows loopdbms_output.put_line('姓名:'||vrow.ename||' 工资:'||vrow.sal);end loop; end; --涨工资，总裁涨1000，经理800，其他400 declarecursor vrows is select * from emp;vrow emp%rowtype; beginopen vrows;loopfetch vrows into vrow;exit when vrows%notfound;if vrow.job='PRESIDENT' thenupdate emp set sal=sal+1000 where empno=vrow.empno;elsif vrow.job='MANAGER' thenupdate emp set sal=sal+800 where empno=vrow.empno;elseupdate emp set sal=sal+400 where empno=vrow.empno;end if;end loop;close vrows;commit; end;/*异常 */ --捕获异常 declareval emp%rowtype; beginselect * into val from emp where empno=123;exceptionwhen others thendbms_output.put_line('捕获到异常'); end;--自定义异常 declarecursor vrows(dno number) is select * from emp where deptno=dno;val emp%rowtype;no_emp exception; beginopen vrows(123);fetch vrows into val;if vrows%notfound then --判断是否有值 raise no_emp;end if;close vrows; exceptionwhen no_emp thendbms_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) iscurrentSal number; beginselect 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 beginproc_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) isbeginselect sal*12+nvl(comm, 0) into yearSal from emp where empno=vempno; end; --测试存储过程 declare yearSal number; beginget_yearSal(7788, yearSal);dbms_output.put_line(yearSal); end;--查询员工年薪---使用存储函数 create or replace function fun_YearSal(vempno in number) return number isyearSal number; beginselect sal*12+nvl(comm, 0) into yearSal from emp where empno=vempno;return yearSal; end; --测试存储函数 declare yearSal number; beginyearSal:=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 begindbms_output.put_line('欢迎来到德莱联盟'); end;--数据校验，周二老板不在，不能办理入职手续 create or replace trigger tri_test2 before insert on emp declarevday varchar2(10); beginselect trim(to_char(sysdate,'day')) into vday from dual;if vday='星期二' thendbms_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 declarebegindbms_output.put_line('涨钱啦！'); end;update emp set sal = sal+10;--判断员工涨工资后的工资一定要大于更新后的工资 create or replace trigger tri_updateSal before update on emp for each row declarebeginif :old.sal>:new.sal thenraise_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 declarebegindbms_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;