Oracle索引、视图、存储过程、触发器、序列、函数和包

索引

　　目地：更快地检索数据。

　　特点：读取数据速度快，但添加数据速度慢.。

　　语法：

　　create index idx_stu_email on stu (email) ;

　　drop index idx_stu_email ;

　　select index_name from user_indexs ;

========================================================

视图

　　特点：视图相当于子查询。

　　语法：

　　create view v$;

　　select view_name from user_views ;

　　desc v$_dept_avg_sal_info ;

　　create view v$_stu as select id,name from stu ;

=========================================================

存储过程

　　语法：

　　1．创建不带参数的存储过程

　　create or replace procedure p

　　as

　　cursor c is

　　select * from emp for update;

　　begin

　　for v_emp in c loop

　　if(v_emp.deptno = 10) then

　　update emp set sal = sal +10 where current of c;

　　elsif (v_emp.deptno = 20) then

　　update emp set sal = sal + 20 where current of c;

　　else

　　update emp set sal = sal + 50 where current of c;

　　end if ;

　　end loop ;

　　commit ;

　　end ;

　　// 执行存储过程（两种方式）

　　· exec p;

 

　　· begin

　　p;

　　end;

 

　　2．创建带参数的存储过程

　　create or replace procedure p

　　(v_a in number,v_b number,v_ret out number)

　　is

　　begin

　　if(v_a > v_b) then

　　v_ret:=v_a ;

　　else

　　v_ret :=v_b ;

　　end if ;

　　end ;

　　// 执行带参数的存储过程

　　declare

　　v_a number:=3 ;

　　v_b number:=4 ;

　　v_ret number ;

 

　　begin

　　p(v_a,v_b,v_ret) ;

　　dbms_output.put_line(v_ret) ;

　　end ;

===============================================================

触发器

　　利用触发器实现的业务如下：

　　现有两个表：emp和operate_info表，当用户对emp表进行增删改操作时，触发你所创建的触发器，该触发器执行向operate_info表中插入一条信息，记录用户对emp表的操作。

　　语法：

　　创建emp表：

　　create table emp

　　(

　　uname varchar2(20),

　　action varchar2(10),

　　atime date

　　)

 

　　// 创建触发器

　　create or replace trigger trig

　　after insert or delete or update on emp for each row

　　begin

　　if inserting then

　　insert into emp_log values (USER,'insert',sysdate) ;

　　elsif updating then

　　insert into emp_log values (USER,'upate',sysdate) ;

　　elsif deleting then

　　insert into emp_log values (USER,'dalete',sysdate) ;

　　end if;

　　end ;

 

　　// 删除触发器

　　drop trigger trig ;

===========================================================

 序列（Sequence）：

        Sequence是数据库系统按照一定规则自动增加的数字序列。这个序列一般作为代理主键（因为不会重  复），没有其他任何意义。

        Sequence是数据库系统的特性，有的数据库实现了Sequence，有的则没有。比如Oracle、DB2、PostgreSQL数据库实现Sequence，MySQL、SQL Server、Sybase等数据库没有Sequence。

      Sequence 语法：

       定义一个seq_test，最小值为10000，最大值为99999999999999999，从20000开始，增量的步长为1，缓存为20的循环排序Sequence。

Oracle的定义方法：

create sequence seq_test

minvalue 10000

maxvalue 99999999999999999

start with 20000

increment by 1

cache 20

cycle

order;

=====================================================================

 函数

      函数用于返回特定的数据，当建立函数时，在函数头部必须包含 return 子句。
      而在函数体内必须包含 return 语句返回的数据。我们可以使用 create function
     来建立函数，实际案例：  
Sql 代码    

   1.  --输入雇员的姓名，返回该雇员的年薪    
   2.  create function annual_incomec(name varchar2)    
   3.  return number is   
   4.  annual_salazy number(7,2);    
   5.  begin   
   6.      --执行部分    
   7.      select sal*12+nvl(comm, 0) into annual_salazy from emp where ename=name;    
   8.      return annual_salazy;    
   9.  end;     
 10.  /   

     如果函数创建过程有编译错误，可以使用show error;命令显示错误  
     在 sqlplus 中调用函数  
Sql 代码    

   1.  SQL> var income number    
   2.  SQL> call annual_incomec('scott') into: income;    
   3.  SQL> print income   

 ======================================================================
 包 

     包用于在逻辑上组合过程和函数，它由包规范和包体两部分组成。  
     1、我们可以使用 create package 命令来创建包。  
     实例：  
      --创建一个包 sp_package  
      --声明该包有一个过程 update_sal  
      --声明该包有一个函数annual_income  
Sql 代码    

   1.  create package sp_package is   
   2.    procedure update_sal(name varchar2, newsal number);     
   3.    function annual_income(name varchar2) return number;    
   4.  end;   

  
     包的规范只包含了过程和函数的说明，但是没  过程和函数的实现代码。包体用于实现包规范中的过程和函数。  
     2、建立包体可以使用 create package body 命令  
           --给包 sp_package 实现包体  
Sql 代码    

   1.  create or replace package body sp_package is   
   2.    procedure update_sal(name varchar2, newsal number)      
   3.    is   
   4.    begin      
   5.         update emp set sal = newsal where ename = name;    
   6.    end;     
   7.         function annual_income(name varchar2) return number is   
   8.          annual_salary number;    
   9.    begin    
 10.          select sal * 12 + nvl(comm, 0) into annual_salary from emp where ename = name; 
 11.    return annual_salary;         
 12.    end;      
 13. end;      
 14.   /   

 
    3、如何调用包的过程或是函数  

    ----------------------- Page 73-----------------------

    当调用包的过程或是函数时，在过程和函数前需要带  包名，如果要访问其它方案的包，还需要在包名前加方案名。  
    如：  
    SQL> call sp_package.update_sal('SCOTT', 1500);  
    特别说明：  
    包是pl/sql 中非常重要的部分，我们在使用过程分页时，将会再次体验它的威力