Oracle子程序&游标&索引！

一、子程序
二、游标
- 显示游标实例
- 使用table结构提取游标数据
三、索引
- 创建索引
- 删除索引

一、子程序

子程序是指被命名的PL/SQL块，这种块可以带有参数，可以在不同应用中多次调用
PL/SQL有两种类型的子程序：过程和函数
- 过程用于执行特定操作，而函数则用于返回特定数据
  - 过程与函数的比较
都使用IN模式的参数传入数据，OUT模式的参数返回数据
输入参数都可以接收默认值，都可以传值
调用时的实参都可以使用位置表示法或名称表示法
都有声明部分，执行部分和异常处理部分
- 一般而言如果需要返回多个值或者不返回值，就使用过程
- 如果只需要返回一个值，就使用函数
- 虽然函数带OUT模式的参数也能返回多个值，但是一般都认为这种方法属于不好的编程习惯或风格
- 过程一般用于执行一个指定的动作，函数一般用于计算和返回一个值
  1、过程的创建与使用
  过程的整体结构
```
过程一般用于执行一个指定的操作，可以将常用的特定操作封装成过程
CREATE [OR REPLACE] PROCEDURE procedure_name
(argument1 [mode1] datatype1, argument2 [mode2] datatype2, ...)
IS [AS]
声明部分
BEGIN
执行部分
EXCEPTION
异常处理部分
END;
```
  创建一个简单的过程
```
--创建一个简单的过程
create or replace procedure pro_out_put as
begin
dbms_output.put_line('这是一个输出过程！');
end;
```
  删除一个过程
```
--删除一个过程
drop procedure pro_query_putsal;
```
  创建一个带有OUT参数的过程
```sql —创建一个带有OUT参数的过程 create or replace procedure pro_out_putsal(v_no in emp.empno%type,out_sal out number) as begin select sal into out_sal from emp where empno = v_no; exception
```
   when no_data_found then
         dbms_output.put_line('找不到该员工！');
```
end;

— 调用过程 declare v_no emp.empno%type; —输入参数 v_sal emp.sal%type; —输出参数 begin v_no:=&no; pro_out_putsal(v_no,v_sal); dbms_output.put_line(‘该员工薪水是’||v_sal); end;

<a name="SAR38"></a>
#### 创建一个带有IN OUT参数的过程
```sql
--创建一个带有IN OUT参数的过程
create or replace procedure pro_inout_putdata(param_num in out number)
as
begin
  select sal into param_num from emp where empno = param_num;
end;
--这个参数既是输入参数，又可以作为输出参数
declare
inout_num number;
begin
  inout_num:=&no;
  pro_inout_putdata(inout_num);
  dbms_output.put_line('工资是：'||inout_num);
end;

创建一个多参数传递的过程

--使用过程时多参传递
create or replace procedure 
       pro_add_dept(v_deptno number,v_dname varchar2,v_loc varchar2)
as
begin
       insert into dept values(v_deptno,v_dname,v_loc);
end;
--多参数过程调用 ，位置传递
call pro_add_dept(70,'研发部','北京');
--多参数过程调用 ，名称传递
call pro_add_dept(v_deptno=>80,v_dname=>'运维部',v_loc=>'杭州');

2、函数的创建与使用

函数的整体结构

 CREATE [OR REPLACE] FUNCTION function_name
   (argument1 [model] datatype1,
    argument2 [mode2] datatype2,
...)
RETURN datatype
 IS|AS
   声明部分
  BEGIN
     执行部分
 EXCEPTION 
  异常处理部分
END;

创建一个简单的函数

--创建一个简单函数
create or replace function fun_random_value
return number  --返回值类型
as
v_num number;  --返回参数及类型
begin
      v_num:=floor(dbms_random.value(1,10)); --生成一个[1-10)的数赋值给返回的参数
      return v_num;  --返回参数
end;
-- 调用函数
declare
      num number; --声明一个参数去接收函数生成的随机数
      begin
        num:=fun_random_value();
        dbms_output.put_line('一个随机数：'||num);
end;

创建一个带有输入参数的函数

--创建一个带有输入参数的函数
create function getDept(eno number) return varchar2
as
deptName dept.dname%type;
begin
  select dname into deptName from dept,emp where dept.deptno = emp.deptno and empno =eno;
  return deptname;
  end;
--根据传入的员工表的员工id，得到部门表中该员工部门的地址
select getDept(7788) from dual;
--根据传入的员工表的员工id，得到部门表中该员工部门的地址
declare 
deptName varchar(30);
begin
  deptName:=getDept(7788);
  dbms_output.put_line(deptName);
end;

创建一个带有输出参数的函数

--创建一个带有输出参数的函数
create function getDept_out_address(eno number,deptName out varchar2)
return varchar2 --声明返回值类型
as
--定义一个参数用于接收函数返回的结果
v_address varchar2(40);         
begin
  select dName,loc into deptName,v_address from dept,emp where dept.deptno=emp.deptno and empno =eno;
  return v_address;
  end;
--调用函数
declare
  address varchar2(30);
  deptName varchar2(20);
  noid number; --声明常量，变量，不可赋值
begin
  noid:=7654; --在这赋值
  address:=getDept_out_address(noid,deptName);
  dbms_output.put_line(noid||'部门名称:'||deptName);
  dbms_output.put_line('部门地址:'||address);
end;

3、包的创建与使用

创建包
- 用于组合逻辑相关的PL/SQL类型，PL/SQL项和PL/SQL子程序
- 通过使用PL/SQL包，不仅可以简化应用设计，提高应用性能，还可以实现信息隐藏，子程序重载等功能
- 包由包规范和包体两部分组成
- 当创建包时，首先需要先创建包规范，然后再创建包体
  - 包规范相当于接口，包体相当于接口的实现

创建包规范

包规范是包与应用程序之间的接口，用于定义包的公用组件，包括常量，变量，游标，过程和函数
在包规范中所定义的公用组件不仅可以在包内引用，而且还可以由其他的子程序引用

创建包规范时U需要注意的是：为了实现信息隐藏，不应该将所有的组件全部放在包规范处定义，而应该只定义公用组件

包的整体结构

========创建包规范的结构=======
CREATE [OR REPLACE] PACKAGE 包名（package_name）
IS|AS
public type and item declarations
subprogram specifications
END 包名（package_name）;
~~~~~~~~~~~~~~~~~~~~~~~~
create or replace package dbutil_package is  --包规范开始
pi constant number(10,7):=3.1415926; --定义一个常量PI
function getarea(radius number) return  number;  --函数
procedure print_area;        --过程
end dbutil_package;  --包规范结束
========创建包体的结构=======
CREATE [OR REPLACE] PACKAGE  BODY package_name
IS | AS
private type and item declarations
subprogram bodies
END package_name;
~~~~~~~~~~~~~~~~~~~~~~~~~
create or replace package  body dbutil_package as --包体开始
area number(10);            --声明一个用于计算的变量
function getarea(radius number) return number is  --函数
begin                    --与包规范里边定义的常量进行计算
  area:=pi*radius*radius;        
  return area;        --返回计算结果
end;
procedure print_area is  --一个过程
begin
  dbms_output.put_line('圆的面积是：'||area);
end;
end dbutil_package; --包体结束
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~调用包的组件
declare
area number(10,7);
begin
area:=dbutil_package.getarea(3);   --调用包体中的函数
dbms_output.put_line('由function返回的面积：'||area);
dbutil_package.print_area;  --调用过程
end;

查看子程序的源码

--显示当前用户的所有子程序及其源代码
select text from user_source where name='PACK_UTIL';

删除子程序

drop procedure 子程序名;

查看包源代码

--显示当前用户的包及其源代码
select  text  from  user_source  where  TYPE='PACKAGE' and name='PACK_UTIL';

删除包

如果只删除包体，则可以使用DROP PACKAGE BODY命令
如果同时删除包规范和包体，则可以使用DROP PACKAGE命令

二、游标

游标分为显式游标和隐含游标两种
- 隐含游标用于处理SELECT INTO 和DML语句
- 显式游标则用于处理SELECT语句返回的多行数据
使用显式游标
- 定义游标：crusor cursor_name is select_statement;
- 打开游标：open cursor_name;
- 提取数据：fetch cursor_name into v_data;
- 关闭游标：close cursor_name;

游标判断条件

%isopen
- 用于判断游标是否已经打开不，打开返回true，否则返回false
%found
- 检查是否从结果集中提取数据，如果提取到数据，返回true，否则返回false
%notfound
- 与%found属性恰好相反，如果提取到数据，则返回值为false，否则返回true

%rowcount

返回当前行为已经提取到的实际行数

显示游标实例

根据条件查询并输入部门信息
declare
v_dept dept%rowtype;
cursor dept_cursor is select * from dept where deptno>10;  --定义游标
begin
open dept_cursor;  --打开游标
loop
 fetch dept_cursor into v_dept;  --提取数据
 exit when dept_cursor%notfound;  --判断循环退出条件
 dbms_output.put_line('编号：'||v_dept.deptno 
      ||'  名称：'||v_dept.dname||'  地址：'||v_dept.loc);
end loop;
close dept_cursor;  --关闭游标
end;

使用table结构提取游标数据

declare
type v_dept is table of dept%rowtype  index by binary_integer;
cursor dept_cursor is select * from dept;
v_dept_table v_dept;
begin
open dept_cursor;
--bulk collect：表示一次性提取全部
fetch dept_cursor bulk collect into v_dept_table;
close dept_cursor;
--从第一个开始存储到最后一个
for i in v_dept_table.first..v_dept_table.last loop
 dbms_output.put_line(v_dept_table(i).deptno||'  
 '||v_dept_table(i).dname||'  '||v_dept_table(i).loc);
end loop;  
end;

三、索引

创建索引的原则
- 一般不需要为数据量很小的表创建索引
- 对于数据量比较大的表，如果经常需要查询的记录数小于表中所有记录数的10%，则可以考虑为该表创建索引
- 应该为大部分列值不重复的列创建索引
- 对于取值范围较大的列（如ename列），应该创建B树索引；对于取值范围较小的列（如sex列），应该创建位图索引
- 对于包含很多个NULL值，但是经常需要查询所有非NULL值记录的列，应当创建索引
- 不能在CLOB或BLOB等大对象数据类型的列上创建索引
- 如果在大部分情况下只需要对表执行只读操作，就可以为该表创建更多的索引以提高查询速度
- 如果在大部分情况下需要对表执行更新操作，则应该为少创建一些索引，以提高更新速度
  创建索引
```
create bitmap index 索引名 on 表名(字段名);
```
  删除索引
```
drop bitmap index 索引名
```

一、子程序

1、过程的创建与使用

过程的整体结构

创建一个简单的过程

删除一个过程

创建一个带有OUT参数的过程