高级SQL介绍

字段类型/记录类型
变量拥有与表列相同的类型 表名.字段名%TYPE ; 将一个记录声明为具有相同类型的数据库
行的做法 ： 表名%ROWTYPE
游标（Cursor）
游标提供了一种对从表中检索出的数据进 行操作的灵活手段，就本质而言，游标实 际上是一种能从包括多条数据记录的结果 集中每次提取一条记录的机制。
VARCHAR2,不需要指定参数的长度。 打开游标时，必须为参数指定相应的值，以用于过滤相关的
数据行。
隐式游标
示例： DECLARE Dept_record Department%ROWTYPE; --记录变量 BEGIN SELECT * INTO dept_record FROM Department
insert：数据库插入会触发此触发器； update：数据库修改会触发此触发器； delete：数据库删除会触发此触发器。 表 名：数据库触发器所在的表。
for each row：对表的每一行触发器执行一次。如果没有这一选项，则只对整 个表执行一次。
触发器的用途
执行输入数据的验证 自动生成新插入的行的值 为交叉引用而从其他表读取 为审查记录而写入其他表 通过电子邮件信息支持警报
ON bob_emp TO bob; 通过这两条命令，BOBO只能在视图中看到这个表中的两个列。 行限制： 让我们更进一步的讨论一下安全性的概念。假设我们想要使得每个人都可以
查询EMP表，但是只是他们自己的记录。我可以写出如下的视图： CREATE VIEW my_emp AS SELECT * FROM emp WHERE ename=USER;
发器是不能被直接调用的。触发器名：触发器对象的名称。
由于触发器是数据库自动执行的，因此该名称只是一个名称，没有实质的用 途。
触发时间：指明触发器何时执行，该值可取：
before---表示在数据库动作之前触发器执行； after---表示在数据库动作之后出发器执行。 触发事件：指明哪些数据库动作会触发此触发器：
参数化游标
在对数据库查询时，经常制定查询条件，所以在游标的应 用中也常需要使用参数用来控制游标查询数据时对数据行 进行条件过滤，这类带参数的游标称为参数化游标
语法：CURSOR 游标名（参数声明） IS SELECT 语句； 例子： DECLARE CURSOR cur_dept(v_deptID Department.DEPTID%TYPE) IS SELECT * FROM Department WHERE deptID = v_deptID; 注意事项： 如果参数类型是VARCHAR2,则只需要指定参数的数据类型为
游标类型
（1）按照是否显式声明来分类： 显式游标：显式声明所需要的游标 隐式游标：不需声明，打开，关闭，系统自动完成
这些操作（只能有一条记录） （2）按照可配置分类： 静态游标：在声明是定义的游标变量，有其固定的
变量内容。 动态游标：可根据变量动态打开一个游标。 参数化游标：可以传参数 （3）声明游标变量
ORACLE系统预定义异常表(1)
Exception Oracle Error SQLCODE Value
ACCESS_INTO_NULL
ORA-06530
CASE_NOT_FOUND
ORA-06592
COLLECTION_IS_NULL
ORA-06531
CURSOR_ALREADY_OPEN ORA-06511
忽略该子句时，缺省值为1。 START WITH：指定生成的第一个序列号。在升序时，序列可从比最小值大的值开始，缺省值为
序列的最小值。对于降序，序列可由比最大值小的值开始，缺省值为序列的最大值 MAXVALUE：指定序列可生成的最大值。 NOMAXVALUE：为升序指定最大值为1027，为降序指定最大值为-1。 MINVALUE：指定序列的最小值。 NOMINVALUE：为升序指定最小值为1。为降序指定最小值为-1026。 NOCYCLE：一直累加，不循环
存储过程(Procedure)
存储过程就是作为可执行对象存放在数据库 中的一个或多个SQL命令。好处为：
第一、大大提高效率。存储过程本身的执 行速度非常快，而且，调用存储过程可以 大大减少同数据库的交互次数。
第二、提高安全性。假如将SQL语句混合在 ASP代码中，一旦代码失密，同时也就意味 着库结构失密。
FROM emp e, dept d WHERE e.deptno=d.deptno GROUP BY dname; SELECT dname,num_emps FROM my_view;
触发器(Trigger)

触发器是特定事件出现的时候，自动执行的代码块。类似于存储过程，
触发器与存储过程的区别在于:存储过程是由用户或应用程序显式调用的,而触
%NOTFOUND 逻辑值，判断游标是否指向数据 行，其值是%FOUND属性的非，本属性常用于控 制游标的结束
%ROWCOUNT 返回游标当前已将提取的记录的 行数，每成功提取一次，其值加1
异常处理(Exception)
类型： 系统预定义异常 自定义异常 使用方法：
（1）声明异常---异常名 EXCEPTION; （2）抛出异常---RAISE 异常名 ; （3）接收异常---WHEN 异常名 THEN …
视图的安全性
列限制： 视图也可以用于加强安全性。让我们假设一下，我只想要用户BOB看到EMP
表中的ENAME 和DEPNO两个列。我可以使用如下所示： GRANT select ON emp TO bob; 但是上面的命令会使BOB看到表的全部内容。我可以写下如下的一个视图，
使BOB只看到自己能够看到的列： CREATE VIEW bob_emp AS SELECT ename,deptno FROM emp; GRANT select
约束
唯一约束 唯一约束确保关键字的值在表中是唯一的
参考完整性约束 唯一键和外键的组合通常称为参考完整性 约束（如每个雇员都属于部门）
表检查约束 表检查约束制定对于表的每行都要进行判 定的条件
视图（View）
视图通常用于两个目的： （1）：增强安全性。 （2）：视图可以隐藏你的查询的复杂性
示例：
游标的使用步骤
步骤： 声明游标 打开游标 获取数据 关闭游标 要点：使用游标就必须使用循环语句，并且
一般都要声明变量（%Type,%Rowtype）
游标属性
%ISOPEN 逻辑值，判断游标是否打开，若未打 开其值为FALSE，否则为TRUE
%FOUND 逻辑值，判断游标是否指向数据行， 若游标当前指向一行数据则返回TRUE,否则为 FALSE.本属性用于控制游标的循环。
第三、有利于SQL语句的重用。
存储过程语法
CREATE OR REPLACE PROCEDURE 存储过程 名字 ( 参数1 IN NUMBER, 参数2 IN NUMBER ) IS 变量1 INTEGER :=0; 变量2 DATE; BEGIN
END 存储过程名字
函数(Function)
GRANT select ON my_emp TO public; 当一个用户查询MY_EMP表示，上述视图只会返回那些ENAME列值为他们自
己的用户名（userid，由USER psuedo 列定义 ）的数据行。用户没有直接对 EMP表进行SELECT查询的权限，但是可以通过视图来访问表。
视图隐藏查询的复杂性
-1476
序列（Sequence）
建立序列命令
CREATE SEQUENCE [user.]sequence_name
[increment by n]
[start with n]
[maxvalue n | nomaxvalue]
[minvalue n | nominvalue];
[NOCYCLE] INCREMENT BY：指定序列号之间的间隔，该值可为正的或负的整数，但不可为0。序列为升序。
分类： 内置函数（last_day ，to_date ，trunc ） 自定义函数
自定义函数
定义函数的语法如下： CREATE [OR REPLACE] FUNCTION function_name [(parameter_list)] RETURN 源自文库atatype {IS|AS} [local_declarations] BEGIN executable_statements [EXCEPTION exception_handlers] END [function_name]; 其中：function_name是函数的名称。 parameter_list是参数列表。 local_declarations是局部声明。 executable_statements是可执行语句。 exception_handlers是异常处理程序。
使用序列
下一个值：Nextval 当前值：Currval 使用方法（1）： INSERT INTO COUR_OPER_LOG
(SER_NO, OPER_USER, OPER_TIME, OPER_CONT) VALUES (SEQ_COUR_OPER_LOG.NEXTVAL,
触发器的语法
CREATE [OR REPLACE] TIGGER触发器名 触发时间 触发事件
ON表名 [FOR EACH ROW] BEGIN
pl/sql语句 END
触发器的类型
1、 语句触发器 2、 行触发器 3、 INSTEAD OF触发 4、 系统条件触发器 5、 用户事件触发器
SYS_INVALID_ROWID
ORA-01410
TIMEOUT_ON_RESOURCE ORA-00051
TOO_MANY_ROWS
ORA-01422
VALUE_ERROR
ORA-06502
ZERO_DIVIDE
ORA-01476
-6501 -6504
-30625 -6500 -6533
-6532 -1410 -51 -1422 -6502
高级SQL介绍
课程内容
关键字 约束 视图 触发器 存储过程 函数 游标 字段类型/行类型 异常处理 序列 动态SQL 同义词
关键字
唯一键 包含唯一键的列值不能为空，且不能重复
主键 主键是一种唯一键，一个表中只能有一个 主键
外键 一个表的外键与另一个表的主键相关联
DUP_VAL_ON_INDEX
ORA-00001
INVALID_CURSOR
ORA-01001
INVALID_NUMBER
ORA-01722
LOGIN_DENIED
ORA-01017
NO_DATA_FOUND
ORA-01403
NOT_LOGGED_ON
ORA-01012
-6530 -6592 -6531 -6511 -1 -1001 -1722 -1017 +100
-1012
ORACLE系统预定义异常表(2)
PROGRAM_ERROR
ORA-06501
ROWTYPE_MISMATCH
ORA-06504
SELF_IS_NULL
ORA-30625
STORAGE_ERROR
ORA-06500
SUBSCRIPT_BEYOND_COUNT ORA-06533
SUBSCRIPT_OUTSIDE_LIMIT ORA-06532
WHERE Dept_id = 1; DBMS_OUTPUT.PUT_LINE(dept_record.DEPTNAM
E || ‘的职责是：’ || dept_record.DESCRIPTION); END; /
声明游标变量
TYPE 游标变量类型名称 IS REF CURSOR [RETRUN 返回类型]
SELECT d.dname,count(*) as NUM_EMPS FROM emp e, dept d
WHERE e.deptno=d.deptno GROUP BY dname; CREATE OR REPLACE VIEW my_view as SELECT d.dname,count(*) as NUM_EMPS