一、视图
对一个查询结果的封装（视图里面所有的数据都是来自于它查询的那张表，视图里面不存储任何数据）
视图的好处：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;