Oracle数据库——PLSQL和存储过程、触发器、oracleSQL优化方案

一、PLSQL

1.SQL99是什么

1）是操作所有关系型数据库的规则
2）第四代语言
3）是一种结构化查询语言
4）只需发出合法合理的命令，就有对应的结果显示

2.SQL特点

1）交互性强，非过程化
2）数据库操纵能力强，只需发送命令，无须关注如何实现
3）容易调试，错误提示，直接了当
4）SQL强调结果

3.PLSQL是什么

是专用于Oracle服务器，在SQL基础上，添加一些过程化控制语句，叫PLSQL
过程化包括：类型定义，判断，循环，游标，例外（异常）处理...
PLSQL强调过程

4.为什么用PLSQL

因为SQL第四代命令式语言，无法显示处理过程化的业务，所以得用过程化程序设计语言弥补SQL不足之处
SQL和PLSQL不是替代关系，而是弥补关系

5.PLSQL程序的完整组成结构

[declare]
	变量声明;
变量声明;
BEGIN
	DML/TCL操作;
DML/TCL操作;
[exception]
	例外处理;
例外处理;
END;
/
注意：在PLSQL程序中，;代表每条语句的结束，/表示整个PLSQL程序结束

6.PLSQL与SQL执行有什么不同

1）SQL是单条执行;
2）PLSQL是整体执行，不能单条执行，整个PLSQL结束用/，其中每条语句结束用;。

例子

--输出7369号员工姓名和工资，格式如下：7369号员工的姓名是XXX,薪水是XXX，语法：使用表名.字段名%type
DECLARE pname emp.ENAME%TYPE;
				psal EMP.ENAME%TYPE;
BEGIN SELECT ENAME, SAL INTO pname , psal FROM EMP WHERE EMPNO = 7369;
	dbms_output.put_line('7369号员工是:'||pname||'薪资是:'||psal);
END;
/
--第二种方法
DECLARE
EMP_abc EMP%rowtype;
BEGIN
SELECT * INTO EMP_abc FROM EMP WHERE EMPNO = 7369;
dbms_output.put_line('7369号员工是:'||EMP_abc.ENAME||'薪资是:'||EMP_abc.SAL);
END;
/
-- 输出hello world!!!
BEGIN
dbms_output.put_line('hello world!!!');
END;
/
-- 求10+100的和,要求:需要声明变量
DECLARE
	--声明三个变量
	i1 NUMBER := 10;  --声明变量i1保存10;变量的声明不能用=需要加个:才行(即:=为等于)
	i2 NUMBER := 100;  --声明变量i2保存100;
	sum_i NUMBER;       --声明变量sum_i为两数相加的和,没有初始值
BEGIN
	--计算即可
	sum_i := i1 + i2;
	dbms_output.put_line('10+100的和为:' || sum_i);
END;
/

7.判断

语法：
IF 变量 IN
THEN
ELSE
END IF;

--plsql中的判断
--使用if-else-end   if显示今天星期几,是工作日还是休息日,周一至周五是工作日,周六周日休息日

DECLARE
	to_day VARCHAR2(11) ;  --声明变量保存是星期几;
BEGIN
	--查询今天是星期几,首先查询今天的日期,在截取'day'
	SELECT TO_CHAR(SYSDATE,'day') INTO to_day FROM dual;
	--判断to_day是星期几
	IF to_day IN('星期六','星期日')
	THEN
	dbms_output.put_line(to_day||'休息日');
	ELSE
	dbms_output.put_line(to_day||'工作日');
	END IF;
END;
/
--从键盘接收值,使用if-elsif-else-end   if显示条件(这里条件是"age<16"，"age<30"，"age<60"，"age<80")
DECLARE
	age NUMBER(10) := &年龄;  --定义一个年龄是键盘输入(这里不能用,需要黑窗口)
BEGIN
	IF age <18 THEN
	dbms_output.put_line('未成年');
	ELSIF age <65 THEN
	dbms_output.put_line('青年人');
	ELSIF age <90 THEN
	dbms_output.put_line('老年人');
	ELSE 
	dbms_output.put_line('老而长寿');
	END IF;
END;
/

8.循环

有三种循环方式：loop 、while、for

--使用loop计算前100的和
DECLARE
	i1 NUMBER(4) :=1;
	sum_i NUMBER(4) :=0;
BEGIN
	LOOP
	--累加
	sum_i := i1 + sum_i;
		--循环变量自增
	i1 := i1 + 1;
	--当变量==101时候退出
	exit WHEN i1 = 101;
	END LOOP;
	dbms_output.put_line('前100项和为:'||sum_i);
END;
/
---------------------第二种写法
DECLARE
	i1 NUMBER(4) :=1;
	sum_i NUMBER(4) :=0;
BEGIN
	LOOP
	sum_i := sum_i + i1;
	i1 := i1+1;
	IF i1 = 101 THEN
		EXIT; 
	END IF; 
END LOOP;
	dbms_output.put_line('前100项和为:'||sum_i);
END;
/
--使用while循环向xxx_emp表中添加999条数据
CREATE TABLE xxx_emp AS SELECT EMPNO , ENAME FROM EMP;
SELECT * FROM xxx_emp;
DECLARE
	i NUMBER(4) := 1;--记录循环次数
BEGIN
	while i<1000 LOOP
	--添加数据
	insert INTO xxx_emp (EMPNO,ENAME) VALUES(i,'王鑫');
	--变量自增
	i :=i + 1;
END LOOP;
END;
/
--查看一共多少条数据
SELECT COUNT(*) FROM xxx_emp;
--使用for循环显示20-30
DECLARE
	i NUMBER(3) :=20;
BEGIN
	FOR i IN 20..30 LOOP
	dbms_output.put_line(i);
END LOOP;
END;
/
--清空xxx_emp中的数据
DELETE FROM xxx_emp;
DECLARE
	i NUMBER(11) :=1;
BEGIN
	FOR i IN 1..9999 LOOP
	INSERT INTO xxx_emp(EMPNO,ENAME) VALUES(i, '王小鑫');
END LOOP;
--提交事务
commit;
END;
/

9.游标

1.什么是游标
类似于JDBC中的ResultSet对象的功能，从上向下依次获取每一条纪录

游标分类
1）无参游标
2）有参游标
2.无参游标，查询所有员工薪水和姓名
创建语法：Cursor 游标名[(数据类型 参数,数据类型 参数,…)] IS SELECT 子句;
使用游标：open 游标名;
关闭游标：close 游标名;

--无参游标
DECLARE
	--声明游标
	cursor semp IS SELECT SAL,ename FROM EMP;
	--定义变量保存员工的名字和薪水
	pname EMP.ENAME%TYPE;
	psal EMP.SAL%TYPE;
BEGIN
	--开启游标
	open semp;
	--开启循环获取数据
	LOOP
	--游标指针下移
	FETCH semp INTO psal, pname;
	--没有数据时候退出
	exit WHEN semp%notfound;
	--输出数据
	dbms_output.put_line(pname||'的薪资是'||psal);
END LOOP;
	close semp;
END;
/
--有参游标,查询20/30/号部门的员工姓名和工资,工资大于1100
DECLARE
	--声明有参数的游标
	cursor cemp(pdeptno EMP.DEPTNO%TYPE , p_sal EMP.SAL%TYPE) IS SELECT ename , sal FROM EMP where DEPTNO=pdeptno AND SAL > p_sal;
	pname EMP.ENAME%TYPE;
	psal EMP.SAL%TYPE;
BEGIN
	--打开游标
	open cemp(20,1100);
	LOOP
	--游标指针下移
	FETCH cemp into pname,psal;
	exit WHEN	cemp%notfound;
	dbms_output.put_line(pname||'的薪资为'||psal);
END LOOP;
close cemp;
END;
/
SELECT * FROM EMP WHERE DEPTNO = 20;

--使用游标，真正给员工涨工资，ANALYST涨1000，MANAGER涨800，其它涨400，要求显示编号，姓名，职位，薪水
DECLARE 
	cursor cemp IS SELECT EMPNO ,ename,JOB,sal FROM EMP;
	pempno EMP.EMPNO%TYPE;
	pname EMP.ENAME%TYPE;
	pjob	EMP.JOB%TYPE;
	psal	EMP.SAL%TYPE;
BEGIN
	open	cemp;
	LOOP
	--游标下移
	FETCH cemp INTO pempno ,pname, pjob, psal;
	exit WHEN cemp%notfound;
	IF pjob = 'ANALYST' 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;
	commit;
	close cemp;
END;
/
SELECT * FROM EMP;

10.例外

--例外:
--什么是例外:    就是java中的异常
--语法：
				--BEGIN
				--	EXECPTION
				--	  WHEN 例外名 then
				--  	处理;
				--	END;
				--	/
--使用oracle系统内置例外，演示除0例外【zero_divide】
DECLARE
	result NUMBER(5);--声明一个变量result保存商
BEGIN
	result :=10/0;
	dbms_output.put_line(result);
	EXCEPTION
		WHEN zero_divide THEN
		dbms_output.put_line('傻子，除0了！！');
END;
/
--使用oracle系统内置例外，查询100号部门的员工姓名，演示没有找到数据【no_data_found】
DECLARE
	pname VARCHAR2(20); --用到什么就声明什么
BEGIN
	SELECT ENAME INTO pname FROM EMP WHERE DEPTNO =100;
	dbms_output.put_line(pname);
	EXCEPTION 
		WHEN no_data_found THEN
		dbms_output.put_line('无这个部门员工');
END;
/

二、存储过程

什么是存储过程【procedure】？

是经过预编译存储在数据库服务中的sql语句集合，与方法一样可以实现一些功能，我们也可以调用存储过程

为什么要用存储过程？

（1）存储过程的语句都经过预编译了，执行效率是很快的
（2）存储过程可以永久的保存在数据库服务中
（3）存储过程可以被其他程序调用，

存储过程和PLSQL有什么区别？

PLSQL只能调用一次，而存储过程可以存放在数据库服务器中，我们可以通过存储过程名多次调用。

创建存储过程的语法：

CREATE [OR REPLACE] PROCEDURE 过程名
[AS]
[DECLARE]
	--声明变量或者游标等
BEGIN
	-- PLSQL或者DML语句
[EXCEPTION]

END;
/

1.创建无参存储过程hello，输出’我的第一个存储过程’；

	CREATE OR REPLACE PROCEDURE hello
	AS
	BEGIN 
		--输出语句
		dbms_output.put_line('我的第一个存储过程');
	END;
	/

2.删除存储过程hello，语法：drop procedure 过程名

drop procedure hello;

3.调用存储过程方式一，call 存储过程名

call hello();–navicat中可以使用
exec hello();–navicat中不能使用

4.调用存储过程方式二，PLSQL程序

begin
hello;
end;
/

5.调用存储过程方式三，Java程序

PrepareStatement。
提示：JDBC使用CallableStatement对象，调用存储过程。

6.创建有参存储过程raiseSalary(编号)，为7369号员工涨10%的工资

	-- 创建存储过程用来涨薪资
	CREATE OR REPLACE PROCEDURE raiseSalary(pempno EMP.empno%type,psal EMP.sal%type)
	AS
	BEGIN
		-- 根据编号修改工资
		UPDATE EMP SET SAL=SAL+psal WHERE empno=pempno;
		-- 提交事务
		commit;
	END;
	/

7.创建有参存储过程findEmpNameAndSalAndJob(编号)，查询7788号员工的的姓名，职位，月薪，返回多个值，演示out的用法

如果是过程接收的参数可以使用in关键字也可以不使用，过程返回的结果我们需要使用out定义

-- 定义过程
create or replace procedure

findEmpNameAndSalAndJob(pempno in number,pename out varchar2,pjob out varchar2,psal out number)
as
begin
	select ename,job,sal into pename,pjob,psal from emp where empno=pempno;
end;
/

-- 调用返回值的存储过程
declare 
	--保存存储过程返回的结果
	pename emp.ename%type;
	pjob emp.job%type;
	psal emp.sal%type;
begin
	--过程名
	findEmpNameAndSalAndJob(7788,pename,pjob,psal);
	dbms_output.put_line('7788号员工' || pename || '---' || pjob || '---' || psal);
end;
/

8.什么情况下用call调用，什么情况下用PLSQL调用存储过程？

call调用适合用于过程无返回值
plsql调用适合用于过程有返回值

9.用存储过程，写一个计算个人所得税的功能

--8000-5000=3000
-- 定义过程，也就是个人所得税的公式
create or replace procedure get_rax(sal in number,rax out number)
as
	-- bal表示超出的部分
	bal number;
begin
	bal := sal-5000;
	if bal <= 3000 then
		rax := bal * 0.03-0;
	elsif bal <= 12000 then
		rax := bal*0.1-210;
	elsif bal <= 25000 then
		rax := bal*0.2-1410;
	elsif bal <= 35000 then
		rax := bal*0.25-2660;
	elsif bal <= 55000 then
		rax := bal*0.3-4410;
	elsif bal <= 80000 then
		rax := bal*0.35-7160;
	else
		rax := bal*0.45-15160;
	end if;
end;
/
--调用
declare 
	-- 需交的税
	rax number;
begin
	get_rax(50000,rax);
	dbms_output.put_line('你需要交税' || rax);
end;
/ 

三、触发器

什么是触发器【Trigger】？

不同的DML操作（select/update/delete/insert）操作，触发器能够进行一定的拦截，符合条件的操作，方可操作基表，否则不可操作基表，类似于javaweb中的filter，框架中的interceptor

为什么要用触发器？

如果没有触发器，那么DML所有操作，均可无限制的操作基表，可能不符合业务需求

语法：

CREATE [OR REPLACE] TRIGGER 触发器名
{BEFORE|AFTER}-- 操作前|后触发
{INSERT|DELETE|---语句级
UPDATE OF 列名}----行级
ON 表名
[FOR EACH ROW]
PLSQL块 [declare ... begin ... end;/]

简单例子

--创建语句级触发器insertEmpTrigger，当对表【emp】进行增加【insert】操作前【before】
--添加数据触发器
CREATE OR REPLACE TRIGGER insertEmpTrigger
before	--之前
	--dbms_output.put_line('Holle World');
INSERT	--添加
ON EMP	--操作的表为emp
BEGIN
	--输出语句
	dbms_output.put_line('添加成功');
END;
/
INSERT INTO EMP (EMPNO,ENAME,SAL) VALUES (3333,'aaa',7000);
--删除数据触发器
CREATE OR REPLACE TRIGGER delectEmpTrigger
before--之前
DELETE--之后
ON EMP--操作的表为emp
BEGIN
	--输出语句
	dbms_output.put_line('删除成功');
END;
/


--删除触发器insertEmpTrigger，语法：drop trigger 触发器名
--drop trigger insertEmpTrigger;

带有逻辑的触发器

--星期一到星期五，且9-20点能向数据库emp表插入数据，否则使用函数抛出异常,提示用户不是工作时间请休息
--抛出异常语法：raise_application_error('-20000','例外原因')
CREATE OR REPLACE TRIGGER securityTrigger--创建触发器，并起名为securityTrigger
before  --执行之前
INSERT	--添加操作
ON EMP	--操作的表为EMP
DECLARE
	pday VARCHAR2(10);--保存周几
	phour NUMBER(2);	--保存时间，几点
BEGIN
	--获取星期几
	SELECT TO_CHAR(SYSDATE,'day') INTO pday FROM dual;
	--获取时间
	SELECT TO_CHAR(SYSDATE,'hh24') INTO phour	FROM dual;
	--业务逻辑
	IF pday IN('星期六','星期日') OR phour NOT BETWEEN 9 AND 20 THEN
		--抛出例外
		raise_application_error('-20000','非工作时间，不能向数据添加数据');
		END IF;
END;
/

--创建行级触发器checkSalaryTrigger，涨后工资这一列，确保大于涨前工资，
--语法：for each row
CREATE OR REPLACE TRIGGER checkSalaryTrigger
before
UPDATE  OF SAL--修改是，针对字段名
ON EMP
FOR each ROW	--修改行时需要写该条语句
BEGIN
	IF : new.SAL <= old.SAL THEN
	--抛出例外
	raise_application_error('-20001','工资上涨，不能越来越低呀');
END;
/

删除触发器，表还在吗？
在
将表丢到回收站，触发器还在吗？
在
当闪回表后，触发器会在吗？
在，可以用
彻底删除表，触发器会在吗？
不在

总结：
触发器可以在对表做增删改时自动触发
做修改时需要些 for each row。

Oracle优化方案

为什么要Oracle优化：

随着实际项目的启动，Oracle经过一段时间的运行，最初的Oracle设置，会与实际Oracle运行性能会有一些差异，这时我们就需要做一个优化调整。

Oracle优化这个课题较大，可分为四大类：

   》主机性能
   》内存使用性能
   》网络传输性能
   》SQL语句执行性能【程序员】

下面列出一些OracleSQL优化方案：

（01）选择最有效率的表名顺序（笔试常考）
Oracle的解析器按照从右到左的顺序处理FROM子句中的表名，
from子句中写在最后的表将被最先处理，
在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表放在最后，
如果有3个以上的表连接查询,那就需要选择那个被其他表所引用的表放在最后。

 1)如果三个表是完全无关系的话，将记录和列名最少的表，写在最后，然后依次类推
2)如果三个表是有关系的话，将引用最多的表，放在最后，然后依次类推

（02）WHERE子句中的连接顺序（笔试常考）
ORACLE采用自右而左的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之左,
那些可以过滤掉最大数量记录的条件必须写在WHERE子句的之右。
例如：查询员工的编号，姓名，工资，部门名
select emp.empno,emp.ename,emp.sal,dept.dname
from emp,dept
where (emp.deptno = dept.deptno) and (emp.sal > 1500)

（03）SELECT子句中避免使用*号
ORACLE在解析的过程中,会将*依次转换成所有的列名，这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间
select empno,ename from emp;

（04）使用DECODE函数来减少处理时间
使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表

（05）整合简单，无关联的数据库访问

（06）用TRUNCATE替代DELETE

（07）尽量多使用COMMIT
因为COMMIT会释放回滚点

（08）用WHERE子句替换HAVING子句
WHERE先执行，HAVING后执行

（09）多使用内部函数提高SQL效率

（10）使用表的别名
salgrade s

（11）使用列的别名
ename e

（12）用索引提高效率
在查询中，善用索引

（13）字符串型，能用=号，不用like
因为=号表示精确比较，like表示模糊比较

（14）SQL语句用大写的
因为Oracle服务器总是先将小写字母转成大写后，才执行
在eclipse中，先写小写字母，再通过ctrl+shift+X转大写;ctrl+shift+Y转小写

（15）避免在索引列上使用NOT
因为Oracle服务器遇到NOT后，他就会停止目前的工作，转而执行全表扫描

（16）避免在索引列上使用计算
WHERE子句中，如果索引列是函数的一部分，优化器将不使用索引而使用全表扫描，这样会变得变慢
例如，SAL列上有索引，
低效：
SELECT EMPNO,ENAME
FROM EMP
WHERE SAL*12 > 24000;
高效：
SELECT EMPNO,ENAME
FROM EMP
WHERE SAL > 24000/12;

（17）用 >= 替代 >
低效：
SELECT * FROM EMP WHERE DEPTNO > 3
首先定位到DEPTNO=3的记录并且扫描到第一个DEPT大于3的记录
高效：
SELECT * FROM EMP WHERE DEPTNO >= 4
直接跳到第一个DEPT等于4的记录

（18）用IN替代OR
select * from emp where sal = 1500 or sal = 3000 or sal = 800;
select * from emp where sal in (1500,3000,800);

（19）总是使用索引的第一个列
如果索引是建立在多个列上，只有在它的第一个列被WHERE子句引用时，优化器才会选择使用该索引
当只引用索引的第二个列时，不引用索引的第一个列时，优化器使用了全表扫描而忽略了索引
create index emp_sal_job_idex
on emp(sal,job);
----------------------------------
select *
from emp
where job != ‘SALES’;

（20）避免改变索引列的类型，显示比隐式更安全
当字符和数值比较时，ORACLE会优先转换数值类型到字符类型
select 123 || ‘123’ from dual;

总之，Oracle优化得在长期工作实践中，进行反复测试与总结，日后好好领会