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)
一、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) |