1、视图
视图就是封装了一条复杂查询的语句
语法
CREATE VIEW 视图名称 AS 子查询
create view dep20 as select * from EMP where DEPTNO=20;
select * from dep20;
2、索引
索引是用于加速数据存取的数据对象。合理的使用索引可以大大降低 i/o 次数,从而提高数据访问性能。
单列索引
create index sex_index on student(sex);
select * from student where sex = 'F'; #根据索引查找
select * from student where id=2; #全表扫描
复合索引
复合索引是基于两个列或多个列的索引。在同一张表上可以有多个索引,
create index sex_age_index on student(sex,age);
删除索引
drop index sex_index;
索引的使用原则:
- 在大表上建立索引才有意义
- 在 where 子句后面或者是连接条件上的字段建立索引
- 表中数据修改频率高时不建议建立索引
3、pl/sql语法
什么是 PL/SQL?
PL/SQL(Procedure Language/SQL)
PLSQL 是 Oracle 对 sql 语言的过程化扩展,指在 SQL 命令语言中增加了过程处理语句(如分支、循
环等),使 SQL 语言具有过程处理能力。把 SQL 语言的数据操纵能力与过程语言的数据处理能力结合起来,使得 PLSQL 面向过程但比过程语言简单、高效、灵活和实用。
pl/sql 程序语法
declare
说明部分 (变量说明,游标申明,例外说明 〕
begin
语句序列 (DML 语句〕…
exception
例外处理语句
End;
3.1变量的声明
声明类型
a number;
b char;
c constant boolean:=true;
引用类型
name emp.ename%type;
记录类型
p emp%rowtype;
注意:
- 声明的类型只有被into 赋值之后才能使用,
- constant不能直接输出
示例
declare
a number;
b varchar2(30);
c constant boolean:=true;
name emp.ename%type;
begin
select e.ENAME into name from EMP e where e.EMPNO = 7900;
DBMS_OUTPUT.PUT_LINE(name);
select e.ENAME into b from EMP e where e.EMPNO = 7900;
DBMS_OUTPUT.PUT_LINE('b'||b);
if c=true then DBMS_OUTPUT.PUT_LINE('c');
end if ;
end;
declare
p emp%rowtype;
begin
select * into p from EMP a where a.EMPNO = 7900;
DBMS_OUTPUT.PUT_LINE(p.ENAME||p.SAL);
end;
3.2 if else分支语句
declare
sal_v emp.SAL%type;
begin
select e.SAL into sal_v from EMP e where e.EMPNO = 7566;
DBMS_OUTPUT.PUT_LINE(sal_v);
if sal_v< 2000 then
DBMS_OUTPUT.PUT_LINE('低收入');
elsif sal_v>=2000 and sal_v<=3000 then
DBMS_OUTPUT.PUT_LINE('中等收入');
else
DBMS_OUTPUT.PUT_LINE('高收入');
end if;
end;
3.3游标 Cursor
语法
CURSOR 游标名 [ (参数名 数据类型,参数名 数据类型,...)] IS SELECT 语句;
cursor pc is select * from emp;
游标的使用步骤:
- 打开游标: open c1; (打开游标执行查询)
- 取一行游标的值:fetch c1 into pjob; (取一行到变量中)
- 关闭游标: close c1;(关闭游标释放资源)
- 游标的结束方式 exit when c1%notfound
遍历
declare
cursor pc is select * from emp; -- 创建游标
pemp emp%rowtype; -- 声明pemp
begin
open pc; --打开游标
loop -- 开始循环
fetch pc into pemp; -- 获取游标中的标量
exit when pc%notfound ; --- 设置退出条件
DBMS_OUTPUT.PUT_LINE(pemp.ENAME||pemp.SAL); --- 输出
end loop;
close pc; ---关闭游标
end;
为不同收入的员工加薪
declare
cursor pc is select * from emp;
pemp emp%rowtype;
addsal emp.sal%type ;
begin
open pc;
loop
fetch pc into pemp;
exit when pc%notfound ;
-- DBMS_OUTPUT.PUT_LINE(pemp.ENAME||pemp.SAL);
if pemp.SAL< 2000 then
DBMS_OUTPUT.PUT_LINE('低收入');
addsal := 200;
elsif pemp.SAL>=2000 and pemp.SAL<=3000 then
DBMS_OUTPUT.PUT_LINE('中等收入');
addsal := 400;
else
DBMS_OUTPUT.PUT_LINE('高收入');
addsal := 6000;
end if;
-- 更新工资
update emp set sal = sal + addsal where emp.empno = pemp.empno;
end loop;
end;
4、存储过程
存储过程(Stored Procedure)是在大型数据库系统中,一组为了完成特定功能的 SQL 语句集,经
编译后存储在数据库中,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来
执行它。
简单理解 存储过程就是数据库中写好的脚本,我们可以通过sql语句调用存储过程,完成调用
语法
create [or replace] PROCEDURE 过程名[(参数名 in/out 数据类型)]
AS
begin
PLSQL 子程序体;
End;
无参存储过程调用
--- 声明
create or replace procedure hell is
begin
DBMS_OUTPUT.PUT_LINE('hells');
end;
---调用
begin
hell();
end;
有参存储过程调用
create or replace procedure say( str in varchar2) is
begin
DBMS_OUTPUT.PUT_LINE(str);
end;
begin
say('12312312');
end;
根据EMPNO为员工加薪
create or replace procedure addsal2( salnum in number,empid in number) is
curentsal emp.sal%type ;
begin
DBMS_OUTPUT.PUT_LINE(salnum);
select SAL into curentsal from EMP where EMPNO = empid;
DBMS_OUTPUT.PUT_LINE('当前薪水'||curentsal);
update emp set sal = sal + salnum where empid = emp.EMPNO;
commit ;
end;
--- 调用
begin
addsal2(100,7369);
end;
5、存储方法
存储方法和存储过程类,只是增加了返回值
语法
create or replace function 函数名(Name in type, Name in type, ...) return 数据类型 is
结果变量 数据类型;
begin
return(结果变量);
end 函数名;
create or replace function income(empid in number) return number is
account number;
begin
select ( SAL*12 + nvl(COMM,0)) into account from EMP where EMPNO = empid;
DBMS_OUTPUT.PUT_LINE(account);
return account;
end;
begin
DBMS_OUTPUT.PUT_LINE('调用方法:'||income(7369));
end;
存储过程和存储函数的区别?
一般来讲,过程和函数的区别在于函数可以有一个返回值;而过程没有返回值。
但过程和函数都可以通过 out 指定一个或多个输出参数。我们可以利用 out 参数,在过程和函数中实
现返回多个值
6、触发器
数据库触发器是一个与表相关联的、存储的 PL/SQL 程序。每当一个特定的数据操作语句
(Insert,update,delete)在指定的表上发出时,Oracle 自动地执行触发器中定义的语句序列。
作用
数据确认
实施复杂的安全性检查
做审计,跟踪表上所做的数据操作等
数据的备份和同步
语法
CREATE [or REPLACE] TRIGGER 触发器名
{BEFORE | AFTER}
{DELETE | INSERT | UPDATE [OF 列名]}
ON 表名
[FOR EACH ROW [WHEN(条件) ] ]
begin
PLSQL 块 End 触发器名
更新员工触发器
create or replace trigger test_triger2 ---创建触发器
after update on EMP --- 监控的动作
declare
begin
DBMS_OUTPUT.PUT_LINE('更新员工操作:'); 发生的行为
end test_triger2;
校验员工工资
create or replace trigger sal_triger
before update of sal on EMP for each row
declare
begin
DBMS_OUTPUT.PUT_LINE('更新员工操作:');
if :OLD.sal >= :NEW.sal then ---通过 :OLD.sal :NEW.sal获取前后的 值
raise_application_error(-20002,'涨工资前 必须 高于涨工资后'); -- 抛出异常
end if;
end;
---测试
update EMP set SAL = SAL -1 where EMP.EMPNO=7369;