Oracle 索引 和 PL/SQL

--_________________________________________索引__________________________________________________

--1.创建索引 （要 create view index 或者 create any view index）
  create index index_name                --index_Name 创建的索引名称
  on table_name (column_list)            --table_name创建索引的表名   --colume_List  :创建索引的列明列表，可以基于多列创建索引
  [tablespace tablespace_name];          --为索引指定表空间

  --示例1
  create index idx_emp on emp(sal);      --为emp的sal 列创建索引；
  --示例2
  conn sys/admin@orcl;
  grant select on dba_objects to scott;  --个权限

  conn soctt/tiger@orcl;
  create table tb_idx_test
  as select * from dba_objects;
  where owner in ('Sys','System','Public','Scott');

  set autotrace trace explain
  select Owner,object_name from tb_idx_test where object_name='dba_indexs';  --没有创建索引前，查询是按全表扫描 

  --创建索引后
  create index idx_tb_idx_test on th_idx_test(Object_name);

  select owner,onject_name from tb_idx_test where object_name='dba_indexs';  --速度会提高 是ＣＰＵ的开销减少

--2.索引的分类
   --1.单列索引和复合索引 ：一个索引可以由一个或多个
       create index idx_emp_ename_job on emp(ename,job);
   --2.唯一和非唯一索引  ：唯一索引 列值不能有重复的值；非唯一的可以有多个重复的值或者Null值；oracle 默认的情况下 是非唯一索引
       create unique index idx_emp_ename on emp(ename);  --在插入的时候如果ename有重复的值 会报错
   --3.标准（B-tree index，B树） 索引：
         --1.是Oracle中最常见的索引；使用create index 就是B -tree 所用 ；
         --2.它包含（单列索引和复合索引，唯一和非唯一索引）
         --3.能够适应多种查询条件  有 "=" 、"Like" ," <" ,">"
         --4.局限在于 查询的数据范围超过表的10%后 就没有优势

   --4.位图索引  ：基数是某个列拥有不同重复值的个数 < 表的总行数 1% 就该创建 （如： 性别 （男、女） 婚姻状况（未婚，已婚，离异） 应该与其他位图索引结合） 
      create  bitmap index idx_bm_emp_job on emp(job);

   --5.函数索引 ：用于解决 某列与函数一起使用后，使该列值不存在索引中，造成Oracle被迫使用全表扫描  因此可以用该索引；
      create index idx_fun_emp_starttime on emp(extract(year from starttime)); --求值 --排序--存储索引


--3.合并索引 （由于不断的对表进行更新的操作，表的索引会产生越来越多的碎片，对索引的效率有影响）
      alert index idx_fun_emp_hiredate coalesce;

--4.重建索引 （由于不断的对表进行更新的操作，表的索引会产生越来越多的碎片，对索引的效率有影响）
      alert index idx_fun_emp_hiredate rebuild;

--5.监视索引（DBA一定要知道的）
      alert index idx_bm_emp_job monitoring usage;  --首先修改此索引 使其处于监视状态

      --执行查看状态的语句
      select a.index_name,a.monitoring,a.used,a.start_monitoring,a.end_monitoring from v$object_usage a;  --查看监视的初始状态  返回值中 Mon=yes ：表示处于监视状态   Use=No ：表示从监视开始到现在还没有被使用过 

      --执行SQL语句后
      select empno,ename,job from emp where job='analyst';

      --再执行查看 状态的语句
      select a.index_name,a.monitoring,a.used,a.start_monitoring,a.end_monitoring from v$object_usage a; --Use=yes:表示 已经使用过了



--

--__________________________________________PL/SQL____________________________________
--PL/SQL:是一种移植的高性能事务处理程序，它支持SQL 和面向对象的编程，提供良好的性能和高效的处理能力
   /*优点 （6）
           1.提高程序的运行性能
           2.提供模块化的程序设计功能
           3.允许定义标识符
           4.具有过程语言控制结构
           5.具有良好的兼容性
           6.处理运行错误

     PL/SQL块  ：PL/SQL 程序都是以快作为基本单位组成
           1.匿名块
           2.命名块
           3.子程序
           4.程序包
           5.触发器
   */

 --1.PL/SQL 程序块的组成部分 （定义部分， 执行部分 ，异常部分）
      declare  --(可选)
        --定义部分
      begin    --（必要）
         --执行部分
         Exception  --（可选）
           --异常部分
      end;  --（必要）

     --示例1
       set serveroutput on   --设置输出，显示 环境变量
       set verify off      --
       declare
         v_totalSal number(5);   --定义块变量
         v_deptNo number(2);
       begin
          select deptno into v_deptNo from dept where dname=&dname;   --执行业务逻辑
          select sum(sal) into v_totalsal from emp where deptno=v_deptNo;
          dbms_output.put_line('总工资是：'||v_totalSal);
       Exception
          when no_data_found then    --异常处理部分
          dbms_Output.put_line('输入的部门编号不存在！');
       end;


--2.常量与变量
   /*语法：identitfier_name [constant] data_type [not null]    
          [:=value_expression] | [default value_expression]；

          identifier_name:要声明的变量名 
          data_type:指定数据类型
          ：= 是赋值运算符   （或者使用 default）
          value_expression：是赋值的表达式
          如果有constant： 则表明声明的是一个常量;  常量就应该赋值，如果没有初始值 白哦是初始化为null
          如果有not null:表明声明的变量不能为空
   */

   --示例1
    set serveroutput on
    set verify off
    declare 
         v_pi constant number(6,5):=3.14;   --定义圆周率常量 并赋值
         v_r number(1) :=2;       --定义半径变量
         v_area number(6,2);     --定义变量，用于保存圆面积
    begin
       v_area:=v_pi*v_r;     --计算圆面积
       dbms_output.put_line('圆周率：'||v_pi)；  --输出结果
       dbms_output.put_line('半径：'||v_r);
       dbms_output.put_line('面积：'||v_area);
    end；

   --示例2
     declare
        v_sal number(7,2);
        v_comm number(7,2);
        v_totalsal number(7,2);
     begin
        select sal,comm into v_sal,v_comm from emp where empno=&empno;
        v_comm:= NVL(v_comm,0);
        v_totalsal:=v_sal+v_comm;
        dbms_output.put_line('基本工资：'||v_sal);
        dbms_output.put_line('补助：'||v_comm);
        dbms_output.put_line('总工资：'||v_totalsal);
     Exception
        when no_data_found then dbms_output.put_line('输入的员工编号不正确!');
     end;



--数据类型 （9）
  1. char :长度不够时使用空格来补充，最多可存储2000个字节
  2. varchar2:表示可变长度的字符串，最多可以存储4000个字节
  3. number：可以存储整数，负数，零，定点数 和精度为38位的浮点数。 格式为number(m,n);
  4. date:存储表中的日期和时间的数据，取值范围是公园4712-1-1——9999年12月31日，data类型的长度是7 表示 “世纪，年，月，日，时，分，秒“
  5. timestamp：存储日期的年 月 日 时 分 秒    其中秒值精确到小数点后6位，同时包含时区。
  6. clob: 大字符串对象： 最多可以存储4GB；
  7. BLOB：大二进制对象：最多可以存储4GB；
  8. %Type：待定类型（根据以后的赋值的类型来确定）

      --示例1
      declare 
        v_ename emp.ename%type;  --定义雇员姓名
        v_sal emp.sal%type;    --定义雇员的工资
        v_tax_sal v_sal%type;   --定义雇员的税后所得
        c_tax_rate constant number(3,2):=0.02;   --定义税率常量
      begin
         select ename,sal into v_ename,v_sal from emp where empno=&dempno;  --查询并赋值
         v_tax_sal:=v_sal*c_tax_sal;    --计算税后所得 
         Dbms_Output.put_line('雇员名称：'||v_ename);    --输出信息
         Dbms_Output.put_line('雇员工资：'||v_sal);
         Dbms_Output.put_line('雇员税后所得：'||v_tax_sal);
      Exception
       when no_date_found then DBMS_output.put_line('请输入正确的员工信息！');  --处理异常
      end；
 9. %rowtype ：表示一个表中一个行记录
     --示例1
     declare 
         v_emp_record dept%rowtype;   --定义一个变量 是 dept 表中的一个行记录
     begin
         select * into v_emp_record from dept
          where deptno=&deptno;    --添加条件  是查询出一行条件
         DBMS_output.put_line('部门编号：'||v_emp_record.deptno);  --输出这个行记录变量中的信息
         DBMS_outPut.put_line('部门名称：'||v_emp_record.dname);
         Dbms_Output.put_line('部门地区：'||v_emp_record.loc);
     end;
 10. record ：查询一行记录中某几列
     --示例1
     declare 
       type Emp_record_type is record   --是自定义一个记录类型
       (
         enmae emp.ename%type,
         sal emp.sal%type,
         comm emp.comm%type,
         total_sal sal%type
       ); 
       v_emp_record Emp_record_type;   --定义一个自定义类型的变量
      begin
       select ename,sal,NVL(comm,0),sal+NVL(comm,0) into v_emp_record from emp where empno=7521;  --赋值
       DBMS_output.put_line('员工名称:'||v_emp_record.ename);   --输出这个自定义类型变量中的值
       DBMS_output.put_line('员工的工资：'||v_emp_record.sal);
       Dbms_Output.put_line('奖金：'||v_emp_record.comm);
       DBMS_output.put_line('总工资：'||v_emp_record.total_sal);
     end;
  11. table ；索引表 相当与一个键值集合，键是唯一的，用于查找对应的值,键可以是数字或者字符串
     --示例1
     declare
        type Emp_table_type_empnos is table of number(4) index by binary_integer;    --是自定义一个索引表类型
        type Emp_tabel_type_enames is table of emp.ename%type index by binary_integer; --是自定义一个索引表类型
        v_emp_empnos Emp_table_type_empnos;   --自定义一个索引表类型 变量
        v_emp_enames Emp_tabel_type_enames;   --自定义一个索引表类型 变量
     begin
      v_emp_empnos(0):=7369;
      v_emp_empnos(1):=7521;
      v_emp_empnos(2):=7566;
      select ename into v_emp_enames(0) form emp where empno=v_emp_empnos(0);     
      select ename into v_emp_enames(1) form emp where empno=v_emp_empnos(1);
      select ename into v_emp_enames(2) form emp where empno=v_emp_empnos(2);

      DBMS_output.put_line('雇员编号：'||v_emp_empnos(0) ||'雇员名'||v_emp_enames(0));

      DBMS_output.put_line('雇员编号：'||v_emp_empnos(1) ||'雇员名'||v_emp_enames(1));
      DBMS_output.put_line('雇员编号：'||v_emp_empnos(2) ||'雇员名'||v_emp_enames(2));
     END;

     --示例2
     Declare 
       type Dept_table_type is table of dept%Rowtype index by binary_integer; --是自定义一个索引表类型
       v_dept_table Dept_table_type； --自定义一个索引表类型 变量
     Begin
       --查询 部门编号为10 20 30 的部门的编号 和 部门名称
       select deptno,dname into v_dept_table(0).deptno,v_dept_table(0).dname from dept where deptno=10;
       select deptno,dname into v_dept_table(1).deptno,v_dept_table(1).dname form dept where deptno=20;
       select deptno,dname into v_dept_table(2).deptno,v_dept_table(2).dname form dept where deptno=30;
       -- 输出信息
      Dbms_Output.put_line('部门编号   部门名称');
      DBMS_outPut.put_line(v_dept_table(0).deptno||'                          '||v_dept_table(0).dname);
      Dbms_Output.put_line(v_dept_table(1).deptno||'                          '||v_dept_table(1).dname);
      DBMS_output.put_line(v_dept_table(2).deptno||'                          '||v_dept_table(2).dname);
     END;  

12. varray：指具有相同数据类型的一组成员的集合；每个成员都有一个唯一的下标， 在PL/SQL中数组数据类型是Varray类型
   --示例1
    declare
       --1定义
       type Dept_varray_type is varray(3) of varchar2(10);  --自定义数组类型的变量 dept_varray_type 长度为 3  数组中成员的类型是vaechar2（10）
       --2声明                                  --3赋初始值
       v_dept_names_varray Dept_varray_type ：=Dept_varray_type(null,null,null); --使用构造函数赋初始值 
       --******这 1 2 3 步骤是不能少的 
    Begin
      v_dept_names_varray(1):='accounting';  --手动为 v_dept_names_varray 赋值
      v_dept_names_varray(2):='research';
      v_dept_names_varray(3):='salses';

      DBMS_output.put_line('===部门名称===');    --varray 数字中的下标是从“1” 开始
      DBMS_output.put_line(v_dept_names_varray(1));
      Dbms_Output.put_line(v_dept_names_varray(2));
      DBMS_output.put_ling(v_dept_names_varray(3));
    END;