当前位置: > 数据库 > Oracle >

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

时间:2019-04-04 15:47来源:linux.it.net.cn 作者:IT
一、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优化得在长期工作实践中,进行反复测试与总结,日后好好领会
 





(责任编辑:IT)
------分隔线----------------------------