oracle从入门到精通(PDF格式)-第13部分

快捷操作: 按键盘上方向键 ← 或 → 可快速上下翻页 按键盘上的 Enter 键可回到本书目录页 按键盘上方向键 ↑ 可回到本页顶部! 如果本书没有阅读完，想下次继续接着阅读，可使用上方 "收藏到我的浏览器" 功能 和 "加入书签" 功能！



　　　　　　　　　　　　　　　　　语句。　　

　　　　　　　例：　　

　　　　　　　declare　　

　　　　　　　cursor　empl_dep　is　　

　　　　　　　select　last_name；department_name　from　employees；departments　　

　　　　　　　where　employees。department_id=departments。department_id；　　

　　　　　　　empl_name　employees。last_name　％type；　　

　　　　　　　depart_name　departments。department_name　％type；　　

　　　　　　　begin　　

　　　　　　　open　empl_dep；　　

　　　　　　　fetch　empl_dep　into　empl_name；depart_name；　　

　　　　　　　dbms_output。put_line（empl_name　｜｜chr（32）｜｜'work　in'｜｜chr（32）｜｜depart_name）；　　

　　　　　　　while　empl_dep％found　loop　　

　　　　　　　fetch　empl_dep　into　empl_name；depart_name；　　

　　　　　　　dbms_output。put_line（empl_name　｜｜chr（32）｜｜'work　in'｜｜chr（32）｜｜depart_name）；　　

　　　　　　　end　loop；　　

　　　　　　　if　empl_dep％isopen　then　　

　　　　　　　dbms_output。put_line（empl_dep％rowcount）；　　

　　　　　　　end　if；　　

　　　　　　　close　empl_dep；　　

　　　　　　　end；　　



　　　　　5。FOR循环的游标使用：　　



　　　　　　　　　for　record_name　in　cursor_name　loop　　

　　　　　　　　　statement1；　　

　　　　　　　　　statement2；　　

　　　　　　　　　。。。。。。　　

　　　　　　　　　end　loop；　　　　

　　　　　　　游标不需要打开，FETCH和关闭，直接在循环中使用　record_name。col_name（子查询中的　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　第　86　页，共　106　页　


…………………………………………………………Page　87……………………………………………………………

资源来自网络，仅供学习！　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　Oracle　从入门到精通　



　　col_name）就可以。　　

　　　declare　　

　　　cursor　cur_empl_dep　is　　

　　　select　last_name；department_name　from　employees；departments　　

　　　where　employees。department_id=departments。department_id；　　

　　　begin　　

　　　for　re_empl_dep　in　cur_empl_dep　loop　　

　　　dbms_output。put_line（re_empl_dep。last_name｜｜'　work　in　　

　　'｜｜re_empl_dep。department_name）；　　

　　　end　loop；　　

　　　end；　　

　　　　

　　　begin　　

　　　for　re_empl_dep　in　（select　last_name；department_name　from　employees；departments　　

　　　where　employees。department_id=departments。department_id）　loop　　

　　　dbms_output。put_line（re_empl_dep。last_name｜｜'　work　in　　

　　'｜｜re_empl_dep。department_name）；　　

　　　end　loop；　　

　　　end；　　



6。带参数的游标：　　



　　　　　　　　　　　　处理串行化数据（一个执行完才能执行下一个）。　　

　　　　　　　　　　　　CURSOR　　cursor_name　＇（parameter_name　datatype；。。。。。）＇　　

　　　　　　　　　　　　IS　　

　　　　　　　　　　　　select_statement；　　

　　　　　　　　　　　　OPEN　cursor_name　＇（parameter_name　datatype；。。。。。）＇；　　

　　　　　限制：不能实现多个游标的并行打开。　　

　　例：　　

　　　declare　　

　　　cursor　dep_cur　　

　　　（dep_id　employees。department_id　％type）　　

　　　is　　

　　　select　employee_id；last_name；department_id　from　employees　　

　　　where　department_id=dep_id；　　

　　　begin　　

　　　for　j　in　1　。。　5　loop　　

　　　for　i　in　dep_cur（j*10）　loop　　

　　　dbms_output。put_line（i。employee_id｜｜'　：　'｜｜i。last_name｜｜'　in　'　｜｜i。department_id）；　　

　　　end　loop；　　

　　　end　loop；　　

　　　end；　　

　　　　

　　　　　在查询的同时锁定数据：　　

　　select　。。。。　from　。。。。　for　update　＇of　column　reference＇　＇nowait＇；　　　

　　＇nowait＇　如果得不到访问资源就立即返回。　　

　　如果要使用游标修改数据的话，一定要在游标定义中　SELECT　语句中加上　FOR　UPDATE。　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　第　87　页，共　106　页　


…………………………………………………………Page　88……………………………………………………………

资源来自网络，仅供学习！　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　Oracle　从入门到精通　



　　在游标执行过程中的　UPDATE　语句：　　

　　UPDATE。。。。SET。。。WHERE　CRRENT　OF　cursor_name；　　



7。异常处理　　



　　　　异常：在执行过程中出现的错误。　　

　　　　错误触发：语句执行错误（规则）。　　

　　　　显式触发：人为认定的异常，人为规定出现异常的数据范围（逻辑）。　　



8。预定义异常：　　



　　　　有编号，有名称。使用异常名称扑捉；　　

　　　　NO_DATA_FOUND　　

　　　　TOO_MANY_ROWS　　

　　　　INVALID_CURSOR　　

　　　　ZERO_DIVIDE　　

　　　　DUP_VAL_ON_INDEX　　在索引上出现重复数；　　　　

　　　　　　　　exception　　　

　　　　　　　　when　NO_DATA_FOUND　then　　

　　　　　　　　statement1；　　

　　　　　　　　statement2；　　

　　　　　　　　。。。。。。。。；　　

　　　　　　　　when　TOO_MANY_ROWS　then　　

　　　　　　　　statement1；　　

　　　　　　　　statement2；　　

　　　　　　　　。。。。。。。。。。。；　　

　　　　　　　　when　others　then　　

　　　　　　　　statement1；　　

　　　　　　　　statement2；　　

　　　　　　　　。。。。。。。。。。。；　　　　　　　　　　　　　



9。非预定义异常：　　



　　　　有编号，没名称。需要先定义名称再扑捉；　　

　　　　（1）在　DECLARE　部分定义一个异常的名称；　　　exc_name　EXCEPTION；　　

　　　　（2）把名称和异常编号连接起来；　　　　　　　　　　　　　　　　　　　PARGMA　EXCEPTION_INIT（exc_name；…exc_number）；　

　　　　　　



3、函数：　　



　　　　SQLCODE　：返回错误编号　　

　　　　SQLERRM　：返回错误信息　　

　　写入错误日志的内容：　用户，时间，对象，操作，错误编号，错误信息。　　

　　　　

　　　　　用户定义异常：　　

　　　　　　　　　　　　（1）命名；　　

　　　　　　　　　　　　（2）raise关键字在　BEGIN　后触发；　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　第　88　页，共　106　页　


…………………………………………………………Page　89……………………………………………………………

资源来自网络，仅供学习！　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　Oracle　从入门到精通　



　　　　　　　　　　　　（3）捕捉；　　

　　　　　语法：　　

　　　　exception　　　

　　　　when　exception1　or　exception2　then　　

　　　　statement1；　　

　　　　statement2；　　

　　　　。。。。。。。。；　　

　　　　when　exception3　or　exception4　then　　

　　　　statement1；　　

　　　　statement2；　　

　　　　。。。。。。。。。。。；　　

　　　　when　others　then　　

　　　　statement1；　　

　　　　statement2；　　

　　　　。。。。。。。。。。。；　　　　　　　　　　

　　　　

　　能够判断出的异常放到　WHEN　语句中，将那些无法预测的异常放在　OTHERS　中做成错误日志表。　　

　　在程序内部的在程序内部捕捉，程序外部的程序外部捕捉，只有都没捕捉到，才会传到环境中。　　

　　　　

　　　　　抛出异常：　　

　　　　RAISE_APPLICATION_ERROR　　

　　　　RAISE_APPLICATION_ERROR（自定义错误编号，自定义信息）；　　

　　　　不会被　EXCEPTION　捕捉到，而是直接回显到界面上给用户看到。　　

　　　　在　EXCEPTION　。。。WHEN　。。。THEN　之前或之中都可以使用。　　

　　　　　函数：　　

　　　　不能独立出现，只能作为表达式来使用。　　

　　　　create　＇or　replace＇　function　fun_name　　　

　　　　＇（parameter1　＇mode＇　datatype；parameter2　＇mode＇　datatype；　。。。）＇　　

　　　　return　datetype　　

　　　　is/as　　

　　　　pl/sql　block；　　

　　　　　　

　　　　只能返回一个且必须有一个返回类型。　　

　　　　在　pl/sql　block　中必须有一个可以执行的　RETURN子句。　　

　　　　只允许接受　IN　模式的参数。　　

　　　　函数可以返回　BOOLEAN类型，但不能将返回　BOOLEAN　类型的函数应用到　SQL　语句中。　　　　

　　　　　　

　　　　　函数的限制：　　

　　　　只能出现查询语句；　　

　　　　不允许出现　DML　操作；　　

　　　　如果在对表　T　进行DML　语句操作调用函数　F，该函数　F　不能对编辑表　T　进行查询；　　

　　　　不允许出现　DDL　语句；　　

　　例：　　

　　create　or　replace　function　sal_m_fun（empl_sal　number）　return　number　is　　

　　Result　number；　　

　　begin　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　第　89　页，共　106　页　


…………………………………………………………Page　90……………………………………………………………

资源来自网络，仅供学习！　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　Oracle　从入门到精通　



　　　　if　empl_sal　　print　name　　

　　　　　　　　　　　　NAME　　

　　　　　　　　　　　　…………………………………………………………………………………　

　　　　　　　　　　　　King　　

　　　　

　　　　　　　　　　　　SQL》　print　sal　　

　　　　

　　　　　　　　　　　　　　　SAL　　

　　　　　　　　　　　　………………………　

　　　　　　　　　　　　　　　　24000　　

　　　　

　　　　　　

　　　　　　　　　　　　begin　　

　　　　　　　　　　　　　…Call　the　procedure　　

　　　　　　　　　　　　　query_emp（p_id　=》　：p_id；　　

　　　　　　　　　　　　　　　　　　　p_name　=》　：p_name；　　

　　　　　　　　　　　　　　　　　　　p_salary　=》　：p_salary；　　

　　　　　　　　　　　　　　　　　　　　　　　p_m　=》　：p_m）；　　

　　　　　　　　　　　　　　　end；　　

　　　　

　　　　　　　　3。　　inout　先传入后传出，可以直接赋予默认值；　　　　

　　　　编辑并存储；　　

　　　　SHO　ERR　查看编辑时错误的命令；　　

　　　　exec　执行存储过程命令；　　

　　删除：DROP　procedure；　　

　　　　　　



5、管理　PL/SQL　程序块：　　



管理　PL/SQL程序块：　　　　

　　　　　　　　在用户自己的方案下有　CREATE　权限　　

　　　　　　　　对于其他用户的方案有　CREATE　ANY　权限　　

　　存储过程，函数和包需要执行权限，触发器不需要。　　　　　　　　　　　　　　　　　　　　　　　　　　　　　

　　当用户使用其他用户的存储过程或函数去访问其他用户的对象，　　

　　能否访问到对象取决与该存储过程或函数的拥有者的权限。　　

　　TUTHID　CURRENT_USER　表示存储过程不依赖他的拥有者，而依赖与当前调用他的用户。　　　

　　在　CREATE　PROCEDURE　的最后添加。　　　　　

　　　　

　　　　　查看信息：　　

　　　　user_objects；　　

　　　　user_source；　　

　　　　user_errors；　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　第　91　页，共　106　页　


…………………………………………………………Page　92……………………………………………………………

资源来自网络，仅供学习！　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　Oracle　从入门到精通　



　　　　query_emp；对过程的描述。　　　　　　　　　　

　　　　



6、包（package）　　



　　一组相关类型的变量，常量，游标，存储过程，函数的集合。　　



1。组成：　　



　　　　包头：声明部分；只声明PUBLIC　PROCEDURE/VARIABLE；　　

　　　　包体：程序实体，包含　PRIVATE　PROCEDURE/VARIABLE　、PUBLIC　PROCEDURE/VARIABLE　和　LOCAL　　

　　VARIABLE；　　　　　

　　包头没有包体是可以独立存在的。　　

　　　　包头：　　

　　　　CREATE　＇OR　REPLACE＇　PACKAGE　package_name　　

　　　　IS　　

　　　　public_type　　　

　　　　PROCEDURE　procdeure_name　（parameter。。。。　）；　　

　　　　END　package_name；　　　　　　　

　　　　包体：　　

　　　　CREATE　＇OR　REPLACE＇　PACKAGE　BODY　package_name　　

　　　　IS　　　

　　　　private　type　and　item　declarations　　

　　　　subprogram　bodies　　

　　　　end　package_name；　　　　



2。构建没有包头的包：　　



　　　　为了在整个会话中的全局变量设计。　　

　　　　如果其他会话访问的话，和定义时候的回话得到的包的值有可能是不一样的。　　

　　包体的部分可以通过　WRAP　程序加密。　　

　　　　

　　只有局部过程和被打包的过程才能　OVERLOAD（重载）。　　

　　　　前项声明：　　

　　　　将过程或函数的名称和参数放在包体的头部声明；　　

　　　　

　　　　建立一次性的过程：　　

　　　　就是在包体中插入了一个匿名块，在包体执行的时候，该匿名块一定会先执行一次该匿名块，　　

　　　　然后再执行包体中的其他部分。　　



3。SQL中使用包函数的限制　　



　　　　函数中不能包含影响当前事务的语句；　　

　　　　如果在对表　T　进行DML　语句操作调用函数　F，该函数　F　不能对编辑表　T　进行查询；　　

　　　　　　

　　包中变量的稳定性：　　

　　PUBLIC　VARIABLE　发生改变的情况：　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　第　92　页，共　106　页　


…………………………………………………………Page　93……………………………………………………………

资源来自网络，仅供学习！　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　Oracle　从入门到精通　



　　　　　　　　　　　　重新建立会话　　

　　　　　　　　　　　　符合规则　　



4。与开发相关的系统包：　　



　　　　　DBMS_SQL　　

　　　　　　　　（1）能过生成动态的　SQL　语句；　　

　　　　　　　　（2）能过执行　DDL　语句；　　

　　　　　包使用的主要步骤：　　

　　　　　　　　　　　　OPEN_DURSOR　　

　　　　　　　　　　　　PARSE　　

　　　　　　　　　　　　BIND_VARIABLE　　

　　　　　　　　　　　　EXECUTE　　

　　　　　　　　　　　　FETCH_ROWS　　

　　　　　　　　　　　　CLOSE_CURSOR　　

　　　　EXECUTE　IMMEDIATE　　

　　　　　

　　　　　　　　EXECUTE　IMMEDIATE　dynamic_string　　

　　　　　　　　　　　　＇INTO　｛define_variable　　

　　　　　　　　　　　　　　　　＇；　define_variable＇　。。。　｜　record｝＇　　

　　　　　　　　＇USING　＇IN｜OUT｜IN　OUT＇　bind_argument　　

　　　　　　　　　　　　　　　　　　　＇；　＇IN｜OUT｜IN　OUT＇　bind_argument＇　。。。　＇；　　

　　　　　DBMS_DDL　　

　　　　　　　　ALTER_PILE（object_type；owner；object_name）；强制编辑　　

　　　　　DBMS_JOB　　　

　　　　　　　　设置用户的任务；　　

　　　　　　　　在某个指定的时刻执行一定的操作；　　

　　　　　　　　手动强制执行；　　

　　　　　　　　挂起任务；　　　　　　　　　　　　

　　　　　　　　提交　　

　　　　　　　　删除　　

　　　　　　　　修改　　

　　　　　　　　要执行什么，可以跟语句或过程　　

　　　　　　　　下一次的执行时间　　

　　　　　　　　任务执行的时间间隔　　

　　　　　　　　挂起　　

　　　　　　　　手动强制执行　　

　　　　　　　　查看：　　

　　　　　　　　　　　　　USER_JOBS；　　　

　　　　　DBMS_OUTPUT　　

　　　　　　　　PUT　　　　　输出多行数据　　

　　　　　　　　NEW_LINE　　　起一个新行　　　　

　　　　　　　　PUT_LINE　　　输出一行数据　　

　　　　　　　　GET_LINE　　　得到一行信息　　　

　　　　　　　　GET_LINES　　得到多行信息　　　　　　　　　　　　

　　　　　UTL_FILE　　

　　　　　　　　对操作系统文件进行操作；　　　　　　　　　　　　　　　　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　第　93　页，共　106　页　


…………………………………………………………Page　94……………………………………………………………

资源来自网络，仅供学习！　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　Oracle　从入门到精通　



　　　　DBMS_LOB　　

　　　　　　　对　LOB　对象的读和写　　　　

　　　　UTL_HTTP　　　　　　　

　　　　　　　可以把指定的网页的内容摘取下来　　　　　　　　　　　　　　　　　　　　　　　　　　　　

　　　　　　　　　　　　　　　　　　　　　　　　

　　　　静态的　SQL语句的执行过程：　　

　　　　分析　　　　　PARSE　　　　

　　　　绑定　　　　　BIND　　

　　　　执行　　　　　EXECUTE　　

　　　　取操作　　FETCH　　

　　用户可以通过　DML　语句对　LONG　类型的数据可以直接访问；　　



7、触发器　　



　　　　触发器：　　

　　　　　　　　　不能直接调用，必须得事件触发，一般情况下是与对象、数据库和方案有关。　　

　　　　　　　1。　　数据库触发器　　

　　　　　　　2。　　应用触发器　　

　　　　　　　　　　　当一个操作与另一个操作有密切关系的时候；　　

　　　　　　　　　　　触发器不要太大，如果代码量大，要把触发器放到一个可执行的存储过程中；　　

　　　　　　　　　　　不要在一个对象上建立太多的触发器；　　　　

　　　　　　　3。　　表触发器　　

　　　　　　　　　　　　　　　BEFORE　做数据校验　　

　　　　　　　　　　　　　　　AFTER　　在操作以后对操作和数据进行记录　　

　　　　　　　4。　　视图触发器　　

　　　　　　　　　　　　　　　INSTEAD　OF　对视图的操作替换成对视图的基表的操作　　　

　　　　　　　5。　　行级触发器　　

　　　　　　　　　　　　　　　对多行的操作，每操作一行都会触发触发器。　　

　　　　　　　6。　　语句级触发器　　

　　　　　　　　　　　　　　　当一条语句执行的时候触发，执行一次触发一次，与操作的行数无关。　　

　　　　　　　　　　　WHEN　CLAUSE　触发器执行条件　　　



1。语句级：　　　　　　　　　　　　　　



　　　　CREATE　＇OR　REPLACE＇　TRIGGER　trigger_name　　　

　　　　　　　timing（before/after）　　

　　　　　　　　　event1　＇or　event2　or　event3＇　ON　table_name　　

　　　　trigger_body　　

　　在同一个方案下不允许重名。　　　　　　　　　　　　　　　　　　　　　　　　　　　

　　CREATE　OR　REP