|
BEGIN ORACLE数据库之约束、索引、权限、角色
1、 数据完整性：1实体完整性 2、创建与管理索引
1、
2、
3、 索引的缺点： 在如下情况下建立索引是不恰当的
在costomer表的name列建立索引 Create index nameIndex on customer(name); 索引重载：create index emp_idx1 on emp(ename,job) create index emp_idx1 on emp(job,name) 索引管理 如： 显示索引列
如 3、管理权限和角色 2、查看系统所有的对象权限 3、授予系统权限 4、授予对象权限 命令如下： Grant privilege_name on
t_name（column1,column2…） 回收对象权限 命令如：Revoke privilege_name on table_name(column1,column2) from user_name; 角色：
1、
2、
3、
1、
2、 pl-sql编程 （重中之重） pl/sql sql第四代语言结合了第三代语言里像C++，java里的一些常变量、选择循环等 pl\sql将第四代语言的强大功能和灵活性和第三代语言的过程结构融化为一体 pl\sql Block Set serveroutput
on/off ACID指数据库事务正确执行的四个基本要素的缩写。包含：原子性(Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。一个支持事务（Transaction）的数据库系统，必需要具有这四种特性，否则在事务过程（Transaction processing）当中无法保证数据的正确性，交易过程极可能达不到交易方的要求。 原子性 整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。 一致性 在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。 隔离性 两个事务的执行是互不干扰的，一个事务不可能看到其他事务运行时，中间某一时刻的数据。 持久性 在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。 数据类型与类型转换 Number(m,n) :
m表示有效数字的位数，n是小数点以后的位数。N 若数有效位超过m，则发生错误 正数表示：小数点往右舍入 负数表示：小数点往左舍入 请看下面示例：
PLS_Integer与Binary_Integer及number的比较 PLS_Integer,Binary_Integer只能存储整数类型的数值，而number则兼容 当PLS_Integer发生溢出时，系统报出异常 当Binary_Integer发生溢出时，系统会自动指定一个number类型来代替这个Binary_Integer,此Number拥有最大的值范围 简单的类型转换 To_char To_date To_number 变量的命名规范：
1、
2、
3、
4、 变量属性 使用格式如下：varName t_name.col_name%TYPE 说明：这样就把t_name表里的col_name字段的属性类型交给了变量varName 流程控制
1、 IF ELSIF ELSE END IF; 在编写pl/sql程序时，可以定义变量和常量；在pl/sql程序中包括有
1、
2、
3、
4、
2、 BEGIN
CASE
END
3、 LOOP ----sequence of statements END
4、 FOR Loop_counter IN[REVERSE]
low..high END
5、 WHILE Sequences of statements; END
6、 语法 可以在语句块的任何位置定义这样的
7、 BEGIN
WHEN
NO_DATA_FOUND DOsomething……… 存储过程带输入型的参数时 其中只需指定v1,v2的类型即可，而不需要为其指定特定大小。这点需要注意 过程调用：call/execute p_name; 包是组合存储过程与函数的逻辑集合 包的定义声明： 包体实现 Return annual_salary; DECLARE result varchar2(20); begin end; 游标 游标CURSOR是oracle系统内存中开辟的一个查询结果的工作区，其中存放着select语句的查询结果。其结果可以是单条记录、多条记录，也可以是零条记录，游标工作工中存在着一个指针POINTER在初始状态它指向 查询结果的首记录 显示游标的操作
1、
2、
3、
4、
5、
6、
7、
8、
9、 declare --定义游标类型 type sp_emp_cursor is ref cursor; --定义游标变量 test_cursor sp_emp_cursor; 定义变量 提供接受 v_ename emp.ename%type; v_sal emp.sal%type; begin --将游标变量与一个特定的查询语句相关联起来 open test_cursor for select ename,sal from emp where deptno=&no; --通过循环取出test_cursor里的数据 loop --通过fetch test_cursor
into fetch test_cursor into v_ename,v_sal; --exit when
cursor_name%notfound exit when test_cursor%notfound; --打印记录 dbms_output.put_line('名字:
'||v_ename || ' end loop; end; / 游标变量与select结合取回其结果集
包： create or replace package mypackage as type test_cursor is ref cursor; end mypackage; / 存储过程： create or replace procedure sp_java (spNo in number,p_cursor out mypackage.test_cursor) is Begin open p_cursor for select * from emp where deptno=spNo; end; /
标注变量在java程序中调用使用，得到结果集合
分页查询存储过程 --分页查询封闭于存储过程中 create or replace procedure pagination (tableName in varchar2, pageSize in number,--一页显示记录数 pageNow in number, rows out number,--总记录数 pageCount out number,--总页数 p_cursor out mypackage.test_cursor --返回的记录数 ) is v_sql varchar2(500); v_begin number:=(pageNow-1)*pageSize+1; v_end number:=pageNow*pageSize; begin v_sql:= 'select * from (select t1.*,rownum rn from (select * from '|| tableName ||') t1 where rownum<='||v_end||') where rn>='||v_begin; open p_cursor for v_sql; v_sql:='select count(*) from '||tableName; execute immediate v_sql into rows; if mod(rows,pageSize)=0 then pageCount:=rows/pageSize; else pageCount:=rows/pageSize+1; end if; end; /
视图 视图就是一个虚假的不存在的表这个东西实在是太好了。但是也有其缺点、知道是什么呢、因为它是虚拟的。所以它的真实存在是在内存里的。因为性能可并不好。oK.又一次体现了两面性。 补充知识:
ORACLE SQL语句在ORACLE数据库里的执行步骤：
1、
2、
3、
4、
5、 SQL语句的执行计划包括下面三点
1、
2、
3、 Oracle
OPTIMIZER的管理模式 1：rule 2：CHOOSE Oracle默认情况下的优化方式。指的是当一个表有或索引有统计信息时，则走CBO方式；如果表或索引没有统计信息，表又不是特别小，而且相应的列有索引时，那就走过些，采用RBO的方法 3:first_rows方法 与CHOOSE方式相似，所不同的是，当一个表或索引有统计信息时，它将以最快的方式返回查询的最先几行数据，从总体上减少了响应时间 4：all_rows 执行管理计划的工作原理 在11G之前，我们可以使用存储大纲stored outline和SQL profile来帮助我们固定特定的SQL语句的执行计划，防止由于执行计划更改而导致的性能下降。不过这样的工作似乎烦重了。因为这两项目工作都是需要DBA手动完成的； 11G开始
引入了执行计划管理器的概念 为实现执行计划管理，优化器会为所有执行次数超过一次的SQL语句维护此SQL语句的每个执行计划的历史列表PLAN HISTROY。优化器通过维护一个语句执行的日志条目来识别此SQL语句是否为第二次执行。一旦优化器认出此SQL语句为第二次执行； Plan baseline是plan histroy里的一个子集，plan baseline里的执行计划是用来性能比较好坏的一个依据。 ORACLE参数文件管理 参数文件包含了所有的数据库配置信息，它非常重要;参数文件里的信息有点像map对象，都是通过key_value来进行保存的. 在oracle 9i之前，参数文件只有一种那就是基于文本的pfile文件 说是文本文件当然了可以通过文本等编辑器直接修改。 而在9i之后，数据库参数文件的性提高了。采用了基于服务器的参 数文件即spfile(server parameter file)，它是一种二进制文件， 它只能通过数据库连接之后，再获得相应的权限之后，才能对此文 件做出修改。当然了一些的软件产品都必须要考虑到向后兼容这一特点。当然了保留了pfile的功能，在有需要的时候，可以通过create spfile=’’ from pfile=’’反之亦然创建相应需要的文件类型。 Show parameter或select * from V$parameter数据字典表查询得知 或show parameter XXX进行like式的模糊查询。 只有在知道了参数文件的前提下才知道了控制文件,因为参数文件里的一个参数control_files记录了控制文件存入的绝对路径即位置. OK 1 2 3 Alter system set control_files=’’ scope=spfile; Oracle文件必须包含 1 2控制文件 3数据文件 4 Startup restrict这时数据库只有restricted session权限的用户才可以连接上数据库 这样有有利于数据库的管理和维护 数据库关闭方式 1/shutdown
normal 2/shutdown
transactional ,当所有用户都断开连接,数据库开始关闭 3/shutdown
immediate 4/shutdown abort直接杀死Oracle进程（）这个其实是很有风险的一件事，除非到了不得不用的地步。否则不要用shutdown abort强制关闭数据库连接 数据库设计准则（第一、第二、第三范式说明） I、关系数据库设计范式介绍1.1 说明：在任何一个关系数据库中，第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库。 1.2 1.3 II、范式应用实例剖析 首先我们确定一下要设计的内容包括那些。学号、学生姓名、年龄、性别、课程、课程学分、系别、学科成绩，系办地址、系办电话等信息。为了简单我们暂时只考虑这些字段信息。我们对于这些信息，说关心的问题有如下几个方面。 学生有那些基本信息 学生选了那些课，成绩是什么 每个课的学分是多少 学生属于那个系，系的基本信息是什么。 2.1 2.1.1 2.1.2 学生：Student(学号，姓名, 课程：Course(课程名称, 选课关系：SelectCourse(学号, 2.2 触发器的概念 触发器是命名块的一种。 触发器的执行是自动进行的，当相应事件发生时就会激发触发器的执行。 触发器不接受任何参数 触发器（TRIGGER）作用 维护那些通过创建表时的声明约束不可能实现的复杂的完整性约束 通过记录已进行的改变及是谁进行了该项改变来检查一个表中的信息。 当一个表发生改变时，自动向其他程序发送需要采取行动的信号 在一个发布-预定环境中发布关于各种事件的信息。 触发器类型 DML触发器 INSERT、DELETE、UPDATE INSTEAD-OF 只可以定义为视图的触发器 系统触发器 数据库启动或关闭之类的系统事件发生时触发 在执行诸如创建表之类的DDL操作时触发 触发器组成 触发器由触发器头部和触发器体两个部分组成，主要包括以下参数： 作用对象：表、视图、数据库、模式 触发事件：DML、DDL、数据库系统事件 触发时间：BEFORE、AFTER 触发级别：语句级、行级 触发条件：WHEN条件 触发操作：SQL语句、PL/SQL块 DML触发器的种类以及执行顺序 语句级前触发器 行级前触发器 DML操作（触发事件） 行级后触发器 语句级后触发器 CREATE [OR REPLACE] TRIGGER trigger_name BEFORE|AFTER triggering_event [OF column_name] ON table_name [FOR EACH ROW] [WHEN trigger_condition] DECLARE BEGIN EXCEPTION END [trigger_name]; 创建一个触发器，禁止在休息日改变雇员信息， create or replace trigger tr_sec_emp before insert or update or delete on emp begin end; 判断当前执行的触发器到底是有那个DML操作激发的。
为emp表创建一个触发器，当执行插入操作时，统计操作后员工人数；当执行更新工资操作时，统计更新后员工平均工资；当执行删除操作时，统计删除后各个部门的人数。 CREATE OR REPLACE TRIGGER trg_emp_dml AFTER INSERT OR UPDATE OR DELETE ON emp DECLARE v_sal BEGIN END trg_emp_dml; 行级触发器 是指执行DML操作时，每操作一记录，触发器就执行一次，一个DML操作涉及到多少个记录，触发器就执行多少次。 在行级触发器中可以使用WHEN条件，进一步控制触发器的执行。 在触发器体中，可以对当前操作的记录进行访问和操作。 标识符
old.field和:new.field old.field 在不同操作中的意义 为emp表创建一个触发器，当插入新员工时显示新员工的员工号、员工名；当更新员工工资时，显示修改前后员工工资；当删除员工时，显示被删除的员工号、员工名。 CREATE OR REPLACE TRIGGER trg_emp_dml_row BEFORE INSERT OR UPDATE OR DELETE ON emp FOR EACH ROW BEGIN END trg_emp_dml_row; 在行级触发器中，可以使用WHEN子句进一步控制触发器的执行。 修改员工工资时，保证修改后的工资高于修改前的工资。 CREATE OR REPLACE TRIGGER trg_emp_update_row BEFORE UPDATE OF sal ON emp FOR EACH ROW WHEN(new.sal<=old.sal) BEGIN END trg_emp_update_row; 特点 只能定义在视图上 Instead-of触发器是行级触发器 Instead-of 作用 修改一个本来不可以修改的视图 修改视图中某嵌套表列的列 如果视图中包含下列任何一项，则该视图不可修改 集合操作符 聚集函数 GROUP BY、CONNECT
BY DISTINCT 连接（部分包含连接的视图） 系统触发器事件 DDL事件 CREATE、ALTER、DROP） 数据库事件触发 服务器启动/关闭、用户登陆/注销以及服务器错误
将每个用户的登录信息写入到temp_table表中。 触发器管理 修改触发器 CREATE OR REPLACE TRIGGER trigger_name 重新编译触发器 ALTER TRIGGER
trigger_name 禁用、启用触发器 ALTER TRIGGER trigger_name DISALBLE|ENABLE 禁用、启用某个表相关的所有触发器： ALTER TABLE table_name 删除触发器 DROP
TRIGGER 读者可能会问，为什么要使用集合呢？毕竟，使用带有外键的两个表已经允许创建数据之间的关联。答案是相对于使用两个表，存储在集合里的数据可以被数据库更快地访问。
1、
2、
3、 大家可能都知道Oracle数据库的核心代码是用C编写的，而一些辅助的工具是通过JAVA语言写的。 而index
by TYPE table_type IS TABLE OF TYPE INDEX BY BINARY_INTEGER; Index by 在index
by 嵌套表的声明与index by声明十分相似，具体声明如下： TYPE table_type IS TABLE OF TYPE; 嵌套表是结构有序的，嵌套表是只读的， 可变数据VARYING ARRAY TYPE table_name IS VARRAY|VARYING ARRAY(maximun size) OF TYPE NOT NULL 可变数组与C数组相同，结构有序有，大小固定。 |
|
|
