oracle03（视图、索引、游标、存储过程）

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;