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 [email protected]; grant select on dba_objects to scott; --个权限 conn [email protected]; 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;