ORACLE开发进阶


ORACLE 开发进阶 内部讲师:伍瑞平 东南融通 版权所有 2 知识点:  SQL  PL/SQL  SQLPLUS  Sqlldr、exp/imp  思考一个问题:  进行ORACLE开发需要掌握那些知识? 3 本次培训的“问题”和“方法”  旨在解决的问题  如何进行ORACLE开发?  理解上述问题解决过程的根本方法:  使用ORACLE SQL和PL/SQL语言解决ORACLE应用中的 绝大部分问题。 4 目录  第一节 SQL基础  第二节 PL/SQL概述  第三节 SQL*PLUS工具  第四节 数据的加载与卸载  第五节 开发应用中的性能优化 5 第一节 SQL基础  SQL概念  Oracle SQL语句的类型  Oracle的内臵函数 6 第一节 SQL基础  3-1 SQL 的概念  SQL(Structured Query Language)是一种定义和操 作关系型数据库管理系统的标准结构化语言。  用户通过 SQL 描述其目标,之后 SQL 语言编译器自 动地生成执行过程,控制数据库执行用户所期望的操 作。一个SQL语句必需表达一段完整的SQL语法。  对Oracle数据库的所有操作是通过SQL语句实现的。 7 第一节 SQL基础  3-2 Oracle SQL语句的类型  数据操作语言语句 (DML)  数据定义语言语句 (DDL)  事务控制语句  会话控制语句  系统控制语句  嵌入 SQL 语句 8 第一节 SQL基础  3-2 Oracle SQL语句的类型  数据操作语言语句 (DML) 查询或操作已有方案对象内的数据:  SELECT  INSERT、 UPDATE、 DELETE  MERGE  EXPLAIN PLAN  LOCK TABLE 9 第一节 SQL基础  3-2 Oracle SQL语句的类型  数据定义语言语句 (DDL) 定义或修改方案对象的结构,以及移除方案对象:  CREATE,ALTER,DROP  RENAME  TRUNCATE  GRANT,REVOKE  AUDIT,NOAUDIT  COMMENT DDL 语句将隐式地提交之前的操作并开始一个新事务。 10 第一节 SQL基础  3-2 Oracle SQL语句的类型  事务控制语句 管理 DML 语句对数据的修改,以及将逻辑上相关的 DML 语句组 织为事务:  COMMIT  ROLLBACK  SAVEPOINT  SET TRANSACTION 11 第一节 SQL基础  3-2 Oracle SQL语句的类型  会话控制语句  用于控制当前会话的属性。ALTER SESSION 和 SET ROLE。  系统控制语句  用于改变Oracle数据库实例的属性。ALTER SYSTEM 。  嵌入 SQL 语句  将DDL、DML语句嵌入存储过程中,并被Oracle编译后存储。嵌入 式 SQL 语句包括 OPEN,CLOSE,FETCH,和 EXECUTE。 12 第一节 SQL基础  3-3 Oracle的内臵函数  数值函数  日期函数  字符串函数  类型转换函数  聚组与分析函数 13 第一节 SQL基础  3-3 Oracle的内臵函数  数值函数  ABS(n) (取绝对值)  SIGN(n)(符号函数)  CEIL(n)(取不小于n的最小整数)  FLOOR(n)(取不大于n的最大整数)  ROUND(n, [m])(按m精度对n进行4舍5入)  TRUNC(n, [m])(按m精度对n进行截取)  MOD(m, n)(取m除以n的余数) 14 第一节 SQL基础  3-3 Oracle的内臵函数  日期函数  SYSDATE(取当前的数据库系统时间)  ADD_MONTHS(加减指定的月份)  MONTHS_BETWEEN(取两个日期之间相隔的月数)  LAST_DAY(取指定日期所在月的最后一天)  ROUND(date, ‘format_string’) 功能:按format_string的格式对给定的日期date进行舍/入。  TRUNC(date, ‘format_string’) 功能:按format_string的格式对给定的日期date进行截取。 15 第一节 SQL基础  3-3 Oracle的内臵函数  字符串函数(一)  INITCAP,UPPER,LOWER 大小写处理函数  CONCAT(字符串连接函数,也可用 || 替代)  ASCII(取字符的编码)  CHR(将编码转换为对应的字符)  LTRIM ,RTRIM ,TRIM (去除空格/去除指定字符)  LPAD,RPAD(添充空格/指定字符)  LENGTH,LENGTHB(取字符串的长度) 16 第一节 SQL基础  3-3 Oracle的内臵函数  字符串函数(二)  SUBSTR 截取子字符串  INSTR 查找子字符串的位置  REPLACE 子串替换  TRANSLATE(字符转换)  NVL ,NVL2 ,NULLIF ,COALESCE (空值转换)  DECODE 条件函数 17 第一节 SQL基础  3-3 Oracle的内臵函数  类型转换函数  TO_CHAR(n)(将数值n转换为字符类型)  TO_NUMBER(‘string’)(将字符串string转换为数值类型)  TO_CHAR(date, ‘format’),TO_DATE(‘string’, ‘format’) 日期型数据与字符型数据按照format的格式相互转换  CHARTOROWID,ROWIDTOCHAR 字符数据类型与ROWID类型相互转换 18 第一节 SQL基础  3-3 Oracle的内臵函数  聚组与分析函数  SUM,AVG 求和、求平均  MAX,MIN 最大、最小  COUNT 数量值  ROLLUP,CUBE,GROUPING 自动汇总  RANK,DENSE_RANK,ROW_NUMBER 评级函数 19 提问:想想看  如何用SQL语句高效删除数据库表中的重复记录? create table t as select obj#,name from sys.obj$; insert into t1 select * from t1 ;  使用ROW_NUMBER函数是一种好办法 : 方法一: Delete from t1 a where rowid>(select min(rowid) from t1 b where a.obj#=b.obj#); 方法二:  delete from t1 where rowid in (select row_id from ( select rowid row_id,row_number() over (partition by obj# order by rowid ) rn from t1 ) where rn <> 1); 20 提问:找找茬  判断证件类型为空,下面的代码有问题吗? IF( LENGTH(TRIM(p_cert_type)) = 0 OR p_cert_type IS NULL )  其实可以更简单些:  IF( TRIM(p_cert_type) IS NULL ) 21 提问:  有两张表auth表和article表,两表通过author_code关联。 auth(author_code,name,sex) article(article_code,article_name,author_code) 已知某作家名为王达琳,需查询该作家的作家编号和性别;如果该作家为女 作家,则需要继续查询她发表文章的数量。怎么做?  ORACLE提供了PL/SQL语言来解决刚才的问题。 22 第二节 PL/SQL概述  PL/SQL概念  PL/SQL的数据类型  PL/SQL的控制结构  游标  异常处理  本地动态SQL  构建可存储的PL/SQL程序 23 第二节 PL/SQL概述  7-1 PL/SQL概念  PL/SQL 是 Oracle 在 SQL 的基础上扩展而成的过程化语言。  PL/SQL 程序存储并运行于服务端,可以由 Pro*C 或 Oracle Call Interface 等多种 Oracle 客户端调用,也可以由 web来 调用。  PL/SQL 程序结构包括存储过程(stored procedure),函数 (function),包(package),触发器(trigger),自治事务 (autonomous transaction)。 24 第二节 PL/SQL概述  7-1 PL/SQL概念  PL/SQL 最主要的特性。  块结构  变量与常量  控制结构  游标  模块化(存储过程、函数、包) 25 第二节 PL/SQL概述  7-1 PL/SQL概念  块结构 PL/SQL程序的基本执行单元。一个标准的块结构如下: DECLARE –定义部分(可选项) - 变量,常数,游标, BEGIN – 块的执行部分 - SQL 语句 - PL/SQL 控制语句 - 这里必须至少包含一条可执行语句 EXCEPTION –异常处理 (可选项) - 例外处理 END; 26 第二节 PL/SQL概述  7-1 PL/SQL概念  块结构 块的分类:  无名块  命名块  子程序  触发器 27 第二节 PL/SQL概述  7-1 PL/SQL概念  变量与常量  PL/SQL和数据库之间是通过变量来进行数据交换的。变量是 一个存储空间,它是在程序中取出数据或传入数据给程序。 程序间进行数据交换的变量又叫程序中的参数。  变量是在定义部分声明的。每个变量都有一个类型,这个类 型决定该变量应该存储什么数据。  常量和变量的定义类似,只是在定义时必须增加一个关键字 constant,并且同时给它一个值,这以后就不能再给常量进行 赋值了。 28 调用PL/SQL的处理过程 29 PL/SQL引擎的处理过程 30 第二节 PL/SQL概述  7-2 PL/SQL的数据类型  标量类型  复合类型  引用类型  LOB类型  数据类型转换 31 第二节 PL/SQL概述  7-2 PL/SQL的数据类型  标量类型  Numeric  Character  Date  Boolean 32 第二节 PL/SQL概述  7-2 PL/SQL的数据类型  复合类型  记录  集合 33 第二节 PL/SQL概述  7-2 PL/SQL的数据类型  引用类型  游标变量 REF CURSOR  对象引用类型 REF object_type 34 第二节 PL/SQL概述  7-2 PL/SQL的数据类型  LOB类型  BLOB  CLOB  NCLOB  BFILE 35 第二节 PL/SQL概述  7-2 PL/SQL的数据类型  数据类型转换  显式转换  隐式转换 36 提问:想想看  客户编号变更情况登记表 TBA304,表结构有如下两个字段 TRANTYPE VARCHAR2(1), CREATED_TS TIMESTAMP(6) 建立了一个基于TRANTYPE的普通索引,交易应用中有一个应用使 用如下语句对TBA304做查询操作: SELECT * FROM TBA304 WHERE TRANTYPE = 2 AND ROWNUM < 10 ORDER BY CREATED_TS ASC 监控应用发现该语句在做全表扫描。为什么?  都是隐式转换惹的祸。 37 第二节 PL/SQL概述  7-3 PL/SQL的控制结构  选择控制语句  循环控制语句  顺序控制语句 38 第二节 PL/SQL概述  7-3 PL/SQL的控制结构  选择控制语句  IF-THEN语句  IF-THEN-ELSE语句  IF-THEN-ELSIF语句  CASE语句 39 第二节 PL/SQL概述  7-3 PL/SQL的控制结构  选择控制语句  CASE语句 CASE selector WHEN expression1 THEN sequence_of_statements1; WHEN expression2 THEN sequence_of_statements2; ... WHEN expressionn THEN sequence_of_statementsn; [ELSE sequence_of_statementsN+1;] END CASE ; 没有ELSE,PL/SQL则隐式添加ELSE RAISE CASE_NOT_FOUND; 40 第二节 PL/SQL概述  7-3 PL/SQL的控制结构  选择控制语句  搜寻式CASE语句 CASE WHEN search_condition1 THEN sequence_of_statements1; WHEN search_condition2 THEN sequence_of_statements2; ... WHEN search_conditionn THEN sequence_of_statementsn; [ELSE sequence_of_statementsN+1;] END CASE; WHEN子句只能包含结果为布尔类型的表达式。 41 第二节 PL/SQL概述  7-3 PL/SQL的控制结构  循环控制语句  LOOP  WHILE-LOOP  FOR-LOOP 42 第二节 PL/SQL概述  7-3 PL/SQL的控制结构  循环控制语句  LOOP 43 第二节 PL/SQL概述  7-3 PL/SQL的控制结构  循环控制语句  WHILE-LOOP 44 第二节 PL/SQL概述  7-3 PL/SQL的控制结构  循环控制语句  FOR-LOOP 45 第二节 PL/SQL概述  7-3 PL/SQL的控制结构  循环控制语句 PL/SQL没有break和continue语句。  EXIT语句 -退出循环  RETURN语句 -退出子程序  GOTO语句 46 第二节 PL/SQL概述  7-3 PL/SQL的控制结构  顺序控制语句  GOTO语句  NULL语句 47 第二节 PL/SQL概述  7-3 PL/SQL的控制结构  顺序控制语句  GOTO语句  语法:goto label  约束:  不能跳转到IF、CASE、LOOP或子块  不能跳出子程序  不能跳转到异常控制程序,同样的,不能从异常控制 部分跳入当前块 48 提问:找找茬  下面的代码有问题吗? open v_cur_cts for sqlstr; loop fetch v_cur_cts bulk collect into vrowid,vecif_cus_num limit 5000; exit when v_cur_cts%notfound; << continue_label >> begin 代码段 exception when others then goto continue_label ; end; end loop; close v_cur_cts; 49 第二节 PL/SQL概述  7-4 游标 Oracle使用工作区(work area)来执行SQL语句,并保存 处理信息。游标是指向工作区的指针或句柄。 游标的类型有两种:  隐式游标:  PL/SQL为所有的SQL数据操作语句(DML语句)都自动声明了 一个隐式的游标,其中也包括只返回一条记录的查询。  显式游标:  用于定义返回多行记录的查询语句  游标的声明放在PL/SQL块、子程序或包的声明部分。 50 第二节 PL/SQL概述  7-4 游标  显式游标  声明游标 声明游标就是指定游标名和与之关联的SELECT语句。 语法如下: CURSOR cursor_name [(parameter[, parameter]...)] [RETURN return_type] IS select_statement; return_type必须是记录或是数据表的行类型。 游标的形式参数都必须是IN模式。 51 第二节 PL/SQL概述  7-4 游标  显式游标  打开游标 打开游标所做的处理是先查找绑定在游标中的变量, 然后按照该变量的值确定查询结果集。 语法如下: OPEN cursor_name [(parameter[, parameter]...)] cursor_name是已声明的游标名, parameter是绑定的实参。 打开一个已打开的游标是合法的,此时会先自动关闭游标,再打 开。也可以一次打开多个不同的游标。 52 第二节 PL/SQL概述  7-4 游标  显式游标  推进游标(一) 使用FETCH语句来推进游标,每次只返回查询结果集 中的一行,游标向下移动,指向当前记录的下一行。 语法如下: FETCH cursor_name INTO list_of_variables|PL/SQL_record ; 对于游标查询返回的每一个字段值,在INTO列表中都必须有一 个与之对应且类型兼容的变量。 53 第二节 PL/SQL概述  7-4 游标  显式游标  推进游标(二) BULK COLLECT子句能让我们批量的绑定数据。这样我 们就能一次性从结果集中取得所有的或指定数目的行。 语法如下: FETCH cursor_name BULK COLLECT INTO list_of_variables_array|PL/SQL_record_array [limit num] ; 此时,在INTO列表中的变量必须为集合变量。 54 第二节 PL/SQL概述  7-4 游标  显式游标  关闭游标 当结果集检索完毕,应关闭游标,释放游标所占用的 资源(结果集所占用的资源空间)。 语法如下: close cursor_name ; 游标被关闭后,可以重新打开它。操作(fetch、close)一个已 关闭的游标会抛出预定义异常ORA-1001 INVALID_CURSOR。 55 第二节 PL/SQL概述  7-4 游标  处理游标流程 DECLARE Create a named SQL area. OPEN Identify the active set. Load the current row into variables. FETCH Test for existing rows. Return to FETCH if rows found. EMPTY? No Release the active set. Yes CLOSE 56 第二节 PL/SQL概述  7-4 游标  游标属性  通过属性获取游标操作的状态信息 %ISOPEN 布尔值 游标打开则返回true,否则fasle %FOUND 布尔值 如果FETCH语句返回一行,则返回 true,否则fasle %NOTFOUND 布尔值 与%FOUND意思相反 %ROWCOUNT NUMBER 返回游标推进的行数 57 第二节 PL/SQL概述  7-4 游标  游标循环  LOOP循环语法 OPEN cursor; LOOP Fetch cursor into…… Exit when cursor%NOTFOUND …… End LOOP; CLOSE cursor; 58 第二节 PL/SQL概述  7-4 游标  游标循环  WHILE LOOP循环语法 OPEN cursor; WHILE cursor%FOUND LOOP …… Fetch cursor into …… End LOOP; CLOSE cursor; 59 游标LOOP循环的一个例子 CREATE OR REPLACE PROCEDURE sample_cursor IS CURSOR cust_cursor IS SELECT customer_id, cust_last_name FROM customers; cust_record cust_cursor%ROWTYPE; BEGIN OPEN cust_cursor; LOOP FETCH cust_cursor INTO cust_record; EXIT WHEN cust_cursor%NOTFOUND; INSERT INTO temp_list(cust_id, custname) VALUES(cust_record.customer_id, cust_record.cust_last_name); END LOOP; COMMIT; CLOSE cust_cursor; END sample_cursor; 60 第二节 PL/SQL概述  7-4 游标  游标循环  游标的FOR循环 自动控制游标的打开、推进和关闭,而不必再使用OPEN、 FETCH和CLOSE语句。游标FOR循环隐式地声明了一个 %ROWTYPE类型的记录作为它的循环索引,打开游标, 然后反复执行把结果集中的行放到索引中去,最后在所有 行都被处理完成后关闭游标。记录索引的有效作用范围只在 循环内,我们不能在循环的外部引用它。 61 第二节 PL/SQL概述  7-4 游标  游标循环  游标的FOR循环 语法如下: FOR recoder_idx in cursor_name LOOP …… END LOOP 62 游标FOR循环的一个例子 CREATE OR REPLACE PROCEDURE cust_pack (p_crd_limit_in NUMBER, p_acct_mgr_in NUMBER) IS v_credit_limit NUMBER := 1500; CURSOR cust_cursor (p_crd_limit NUMBER, p_acct_mgr NUMBER) IS SELECT customer_id, cust_last_name, cust_email FROM customers WHERE credit_limit = p_crd_limit AND account_mgr_id = p_acct_mgr; BEGIN FOR cust_record IN cust_cursor(p_crd_limit_in, p_acct_mgr_in) LOOP -- implicit open and fetch ... END LOOP; -- implicit close ... END; 63 第二节 PL/SQL概述  7-4 游标  隐式游标 隐式游标也叫SQL游标。SQL游标不能显式的打开和关 闭,由PL/SQL隐式打开并关闭。 SQL游标也有与显式游标类似的属性。用于返回执行 INSERT,DELETE,UPDATE,SELECT INTO语句的信息。 64 第二节 PL/SQL概述  7-4 游标  游标变量  游标变量同C或Pascal语言中的指针类似,它指向一块内存地 址。它的数据类型是REF CURSOR 。  游标变量和游标不同,游标是静态的,而游标变量是动态的。  REF CURSOR分两种,Strong REF CURSOR 和 Weak REF CURSOR。强类型REF CURSOR需要指定返回类型。 65 第二节 PL/SQL概述  7-4 游标  游标变量 声明游标变量:  Strong REF CURSOR:  Weak REF CURSOR: TYPE strongcurtyp IS REF CURSOR RETURN emp%ROWTYPE; emp_cv strongcurtyp; TYPE weakcurtyp IS REF CURSOR; weak_cv weakcurtyp; 或 any_cv SYS_REFCURSOR --使用SYS_REFCURSOR定义Weak REF CURSOR变量。 66 Strong VS Weak Cursors  Strong cursor:  受限制  指定返回记录类型  用于特定的查询  较少出现错误  Weak cursor:  非限制  用于任意查询  更灵活 67 CREATE OR REPLACE PACKAGE emp_data AS TYPE empcurtyp IS REF CURSOR RETURN emp%rowtype; --定义TYPE as Strong REF CURSOR PROCEDURE open_emp_cv(emp_cv IN OUT empcurtyp, choice INT); --根据不同的choice选 择不同的CURSOR END emp_data; 强类型游标变量的一个例子 例子: 68 弱类型游标变量的一个例子 例子: create or replace procedure open_cv(choice IN INT, return_cv OUT SYS_REFCURSOR) is --参数return_cv为weak REF CURSOR,利用SYS_CURSOR 来定义 begin if choice = 1 then open return_cv for 'select * from emp'; elsif choice = 2 then open return_cv for 'select * from dept'; end if; end open_cv 69 第二节 PL/SQL概述  7-4 游标  游标变量规则  不能在包中声明游标变量。  处于另外一个服务器上的远程子程序不能接受游标变量的值。  游标变量不能比较大小。  不能为游标变量赋空值。  不能在CREATE TABLE OR VIEW语句中把字段类型指定为REF CURSOR类型。  不能把REF CURSOR类型作为集合的元素类型。  游标和游标变量不能互相替换。 70 第二节 PL/SQL概述  7-5 异常处理 在PL/SQL中,警告或错误被称为异常。 异常只能在PL/SQL块、子程序或包的声明部分声明。 作用域: 在同一个块内,异常不能声明两次。但可以在不同的块 声明相同的异常,本地声明会覆盖全局声明。 语法: DECLARE exception_name EXCEPTION; 71 第二节 PL/SQL概述  7-5 异常处理  异常类型  预定义Oracle异常  非预定义Oracle异常  用户自定义异常 72 第二节 PL/SQL概述  7-5 异常处理  异常类型  预定义Oracle异常 PL/SQL在STANDARD包中声明了全局预定义异常,不建议重新声明 预定义异常。常用的有:  NO_DATA_FOUND  TOO_MANY_ROWS  DUP_VAL_ON_INDEX  VALUE_ERROR  ZERO_DIVIDE  COLLECTION_IS_NULL  INVALID_CURSOR 73 第二节 PL/SQL概述  7-5 异常处理  异常类型  非预定义Oracle异常 使用OTHERS处理器或编译指示EXCEPTION_INIT,来控制没有预定 义名称的错误(通常为 ORA- 消息)。 编译指示EXCEPTION_INIT 的语法: DECLARE exception_name EXCEPTION; PRAGMA EXCEPTION_INIT(exception_name, -Oracle_error_number); 74 第二节 PL/SQL概述  7-5 异常处理  异常类型  用户自定义异常 过程RAISE_APPLICATION_ERROR 。应用程序只能从一个正在执行的 存储子程序或方法中调用此过程。 语法如下: raise_application_error(error_number,message[,{TRUE|FALSE}]); error_number是一个范围在-20000至-20999之间的负整数, message是最大长度为2048字节的字符串。 75 第二节 PL/SQL概述  7-5 异常处理  处理PL/SQL异常  如何抛出异常  异常的传递  编写异常处理程序 76 第二节 PL/SQL概述  7-5 异常处理  处理PL/SQL异常  如何抛出异常 使用RAISE语句显式抛出异常 。 语法: RAISE exception_name;  预定义Oracle异常 (系统隐式抛出异常,可显式抛出)  非预定义ORACLE异常(系统隐式抛出异常,可显式抛出)  用户自定义异常 (必须显式抛出异常) 77 第二节 PL/SQL概述  7-5 异常处理  处理PL/SQL异常  异常的传递 异常会把它自身传递到后继的封闭块直到找到异常处理程序或是 再也没有可以搜索到的块为止。在后一种情况下,PL/SQL会向主 环境抛出一个未捕获异常。 异常是不能通过远程过程调用(RPC)来传递的。 78 异常的传递 79 异常的传递 80 第二节 PL/SQL概述  7-5 异常处理  处理PL/SQL异常  编写异常处理程序 语法如下: EXCEPTION WHEN exception1 [OR exception3 ...] THEN -- handler sequence_of_statements1 WHEN exception2 [OR exception4 ...] THEN -- another handler sequence_of_statements2 ... WHEN OTHERS THEN -- optional handler sequence_of_statements3 END; 81 异常处理程序 BEGIN DECLARE v_num CONSTANT NUMBER(3) :=5000; -- raises an exception BEGIN ... EXCEPTION WHEN VALUE_ERROR THEN INSERT INTO ... --might raise DUP_VAL_ON_INDEX WHEN OTHERS THEN -- cannot catch the exception ... END; EXCEPTION WHEN OTHERS THEN -- can catch the exception dbms_output.put_line(‘errnum:’||SQLCODE||‘ ,errmsg:’||SQLERRM); raise; END; 82 第二节 PL/SQL概述  7-5 异常处理  处理PL/SQL异常  编写异常处理程序 内臵函数:  SQLCODE:返回Oracle错误编号。异常返回一个负数。 例外: no_data_found 100  SQLERRM:返回对应的错误消息。最大长度是512个字符, 其中包括错误编号、嵌套消息和具体表和字段的名称。  没有异常抛出,SQLCODE返回0,SQLERRM返回消息"ORA- 0000: normal, successful completion"。 83 提问:想想看  把错误编号传递给SQLERRM,会返回对应的错误消息。如果一个正数 传递给SQLERRM,会得到什么?  user-defined exception 或non-ORACLE exception  测试脚本: set serveroutput on; DECLARE err_msg VARCHAR2(512); begin err_msg := sqlerrm('2'); dbms_output.put_line(err_msg); end; 84 第二节 PL/SQL概述  7-5 异常处理  错误控制技巧  模拟TRY..CATCH..块  反复执行的事务  使用定位变量标记异常发生点  异常时继续循环 85 第二节 PL/SQL概述  7-5 异常处理  错误控制技巧  模拟TRY..CATCH..块 DECLARE pe_ratio NUMBER(3, 1); BEGIN SELECT price / NVL(earnings, 0) INTO pe_ratio FROM stocks WHERE symbol = 'XYZ'; INSERT INTO stats(symbol, ratio) VALUES ('XYZ', pe_ratio); EXCEPTION WHEN ZERO_DIVIDE THEN ... END; 86 DECLARE pe_ratio NUMBER(3, 1); BEGIN BEGIN -- sub-block begins SELECT price / NVL(earnings, 0) INTO pe_ratio FROM stocks WHERE symbol = 'XYZ'; EXCEPTION WHEN ZERO_DIVIDE THEN pe_ratio := 0; END;-- sub-block ends INSERT INTO stats(symbol, ratio) VALUES ('XYZ', pe_ratio); EXCEPTION WHEN OTHERS THEN ... END; 模拟TRY..CATCH..块 87 第二节 PL/SQL概述  7-5 异常处理  错误控制技巧  反复执行的事务 实现方法: 1. 把事务装入一个子块中。 2. 把子块放入一个循环,然后反复执行事务 3. 在开始事务之前标记一个保存点。如果事务执行成功的话,就 提交事务并退出循环。如果事务执行失败,控制权就会交给异 常处理程序,事务回滚到保存点,然后重新尝试执行事务。 4. 注意别造成死循环。可以用FOR或WHILE语句来限制重做的次数 。 88 反复执行的事务 BEGIN LOOP -- could be FOR i IN 1..10 LOOP to allow ten tries BEGIN -- sub-block begins SAVEPOINT start_transaction; -- mark a savepoint DELETE FROM results WHERE answer1 = ’no’; INSERT INTO results VALUES (‘tony’, ’ans1’, ’ans2’, ’ans3’); COMMIT; EXIT; EXCEPTION WHEN DUP_VAL_ON_INDEX THEN ROLLBACK TO start_transaction; -- undo changes END;-- sub-block ends END LOOP; END; 89 第二节 PL/SQL概述  7-5 异常处理  错误控制技巧  使用定位变量标记异常发生点 BEGIN SELECT ... SELECT ... EXCEPTION WHEN NO_DATA_FOUND THEN ... -- Which SELECT statement caused the error? END; 90 使用定位变量标记异常发生点 DECLARE stmt INTEGER := 1; -- designates 1st SELECT statement BEGIN SELECT ... stmt := 2;-- designates 2nd SELECT statement SELECT ... EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO errors VALUES ('Error in statement ' || stm t); END; 91 第二节 PL/SQL概述  7-5 异常处理  错误控制技巧  异常时继续循环  使用GOTO语句 92 异常时继续循环 open v_cur_cts for sqlstr; loop fetch v_cur_cts bulk collect into vrowid,vecif_cus_num limit 5000; exit when v_cur_cts%notfound; begin 代码段1; exception when others then goto continue_label ; end; 代码段2; << continue_label >> null; end loop; close v_cur_cts; 93 第二节 PL/SQL概述  7-6 本地动态SQL  使用场景  EXECUTE IMMEDIATE  使用游标处理动态多行查询  动态批量绑定  技巧与陷阱 94 第二节 PL/SQL概述  7-6 本地动态SQL  使用场景  执行DDL语句或会话控制语句  为了获取更多的灵活性  使用动态语句来替代DBMS_SQL,获取更好的性能, 支持DBMS_SQL包不支持的功能 95 第二节 PL/SQL概述  7-6 本地动态SQL  EXECUTE IMMEDIATE 能分析要执行动态的SQL语句或是匿名PL/SQL块。  语法如下: EXECUTE IMMEDIATE dynamic_string [INTO {define_variable[, define_variable]... | record}] [USING [IN | OUT | IN OUT] bind_argument [, [IN | OUT | IN OUT] bind_argument]...] [{RETURNING | RETURN} INTO bind_argument[, bind_arg ument]...]; 96 动态SQL实例  EXECUTE IMMEDIATE 'CREATE TABLE bonus (id NUMBER, amt NUMB ER)';  sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)'; EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location;  sql_stmt := 'SELECT * FROM emp WHERE empno = :id'; EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;  plsql_block := 'BEGIN emp_pkg.raise_salary(:id, :amt); END;'; EXECUTE IMMEDIATE plsql_block USING 7788, 500; 97 动态SQL实例  sql_stmt := 'UPDATE emp SET sal = 2000 WHERE empno = :1 RETURNING sal INTO :2'; EXECUTE IMMEDIATE sql_stmt USING emp_id RETURNING INTO salary;  EXECUTE IMMEDIATE 'DELETE FROM dept WHERE deptno = :num' USING dept_id;  EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE TRUE';  EXECUTE IMMEDIATE ‘DELETE FROM ’ || table_name; 98 第二节 PL/SQL概述  7-6 本地动态SQL  使用游标处理动态多行查询 使用三种语句来处理动态多行查询:OPEN-FOR, FETCH和CLOSE。。  语法如下: OPEN {cursor_variable | :host_cursor_variable} FOR dyna mic_string [USING bind_argument[, bind_argument]...]; 99 第二节 PL/SQL概述  7-6 本地动态SQL  动态批量绑定 批量绑定能减少PL/SQL和SQL引擎之间的切换,改善 性能。  批量EXECUTE IMMEDIATE  批量FETCH  批量FORALL 100 第二节 PL/SQL概述  7-6 本地动态SQL  批量EXECUTE IMMEDIATE 动态多行查询中,使用BULK COLLECT INTO子句来绑定变量。 返回多行结果的动态DML语句中,使用RETURNING BULK COLLECT INTO子句来批量绑定输出变量。  语法如下: EXECUTE IMMEDIATE dynamic_string [BULK COLLECT][INTO {define_variable[, define_variable]... | record}] [USING [IN | OUT | IN OUT] bind_argument [, [IN | OUT | IN OUT] bind_argument]...] [{RETURNING | RETURN} [BULK COLLECT ] INTO bind_argument[, bind_argument]...]; 101 第二节 PL/SQL概述  7-6 本地动态SQL  批量FETCH 从动态游标中取得数据。  语法如下: FETCH dynamic_cursor BULK COLLECT INTO define_variable[, define_variable ...]; 102 第二节 PL/SQL概述  7-6 本地动态SQL  批量FORALL 可以在FORALL内部使用EXECUTE IMMEDIATE语句。 动态字符串必须是一个INSERT、UPDATE或DELETE语句(不可以是 SELECT语句)。  语法如下: FORALL index IN lower bound..upper bound EXECUTE IMMEDIATE dynamic_string USING bind_argument | bind_argument(index) [, bind_argument | bind_argument(index)] ... [{RETURNING | RETURN} BULK COLLECT INTO bind_argument[, bind_argument ... ]]; 103 动态批量绑定实例  OPEN emp_cv FOR 'SELECT empno, ename FROM emp'; FETCH emp_cv BULK COLLECT INTO empnos, enames; CLOSE emp_cv;  EXECUTE IMMEDIATE 'SELECT sal FROM emp' BULK COLLECT INTO sals;  sql_stmt := 'UPDATE emp SET bonus = :1 RETURNING ename INTO :2'; EXECUTE IMMEDIATE sql_stmt USING bonus_amt RETURNING BULK COLLECT INTO enames; 104 第二节 PL/SQL概述  7-6 本地动态SQL  技巧与陷阱  改善性能  减少硬解析,重用游标 1.不同的emp_id值,会执行多次硬解析,打开不同的游标。  EXECUTE IMMEDIATE 'DELETE FROM emp WHERE empno = ' || T O_CHAR(emp_id); 2.不同的emp_id值,执行一次硬解析(只要语句还在共享 池),重用同一个游标。  EXECUTE IMMEDIATE 'DELETE FROM emp WHERE empno = :num ' USING emp_id; 3. 修改参数cursor_sharing(EXACT或similar) 105 提问:找找茬  下面的存储过程有问题吗? CREATE PROCEDURE drop_table(table_name IN VARCHAR2) AS BEGIN EXECUTE IMMEDIATE 'DROP TABLE :tab' USING table_name; END;  参数绑定不能传递模式对象,解决办法:  CREATE PROCEDURE drop_table(table_name IN VARCHAR2) AS BEGIN EXECUTE IMMEDIATE 'DROP TABLE ' || table_name; END; 106 提问:找找茬  下面的语句有问题吗? EXECUTE IMMEDIATE 'UPDATE emp SET comm = :x' USING NULL;  参数绑定不能传递NULL,解决办法:  DECLARE a_null CHAR(1); -- set to NULL automatically at run time BEGIN EXECUTE IMMEDIATE 'UPDATE emp SET comm = :x' USING a_null; END; 107 第二节 PL/SQL概述  7-6 本地动态SQL  技巧与陷阱  让过程对任意模式对象起作用  参数绑定不能传递模式对象,直接把参数嵌套到字符串 中  传递空值  USING语句中所传递的参数不能为NULL,但可以使用为空 的字符串。 108 第二节 PL/SQL概述  7-6 本地动态SQL  技巧与陷阱  避免死锁  有些情况下,执行SQL数据定义语句会导致死锁。比如, 用ALTER或DROP来操作正在使用的子程序或包。  CREATE PROCEDURE calc_bonus (emp_id NUMBER) AS BEGIN ... EXECUTE IMMEDIATE 'DROP PROCEDURE calc_bonus'; 109 第二节 PL/SQL概述  7-6 本地动态SQL  技巧与陷阱  使用重复占位符(一)  动态SQL语句中的占位符与USING子句中的绑定参数是位 臵关联的,而不是名称关联。  sql_stmt := 'INSERT INTO payroll VALUES (:x, :x, :y, :x)'; EXECUTE IMMEDIATE sql_stmt USING a, a, b, a; 110 第二节 PL/SQL概述  7-6 本地动态SQL  技巧与陷阱  使用重复占位符 (二)  动态PL/SQL块中只有唯一的占位符才与USING子句中的绑 定参数按位臵对应。  DECLARE a NUMBER := 4; b NUMBER := 7; BEGIN plsql_block := 'BEGIN calc_stats(:x, :x, :y, :x); END'; EXECUTE IMMEDIATE plsql_block USING a, b; END; 111 第二节 PL/SQL概述  7-7 构建可存储的PL/SQL程序  存储过程与函数  包  触发器 112 第二节 PL/SQL概述  7-7 构建可存储的PL/SQL程序  存储过程 语法: CREATE [OR REPLACE] PROCEDURE procedure_name (argument1 [mode1] datatype1, argument2 [mode2] datatype2, ...) IS|AS PL/SQL Block; 在SQL*Plus中使用EXECUTE命令调用存储过程 113 第二节 PL/SQL概述  7-7 构建可存储的PL/SQL程序  存储过程 参数类型:  IN Argument  OUT Argument  IN OUT Argument 114 第二节 PL/SQL概述  7-7 构建可存储的PL/SQL程序  函数 语法: CREATE [OR REPLACE] FUNCTION function_name (argument1 [mode1] datatype1, argument2 [mode2] datatype2, ...) IS|AS RETURN datatype PL/SQL Block; 115 第二节 PL/SQL概述  7-7 构建可存储的PL/SQL程序  包 创建包头语法: CREATE [OR REPLACE] PACKAGE package_name IS | AS public type and item declarations subprogram specifications END [package_name]; 116 第二节 PL/SQL概述  7-7 构建可存储的PL/SQL程序  包 创建包体语法: CREATE [OR REPLACE] PACKAGE BODY package_name IS | AS private type and item declarations subprogram bodies END package_name; 117 本节培训的“问题”和“方法”  旨在解决的问题  如何与ORACLE进行交互?  理解上述问题解决过程的根本方法:  使用sqlplus、sqlldr等工具与ORACLE进行交互。 118 第三节 SQL*PLUS工具  SQL*PLUS概述  SQL*PLUS 是Oracle提供的一个与oracle进行交互的客 户端工具,它不仅可以用于测试,运行SQL语句和 PL/SQL块,而且还可以用于管理Oracle数据库。  在sql*plus中,可以运行sql*plus命令与sql*plus语 句。  sql*plus语句执行完后,会保存在一个被称为sql buffer的内存区域中,并且只能保存一条最近执行的 sql语句。sql*plus命令不保存在sql buffer的内存区 域中。 119 第三节 SQL*PLUS工具  启动SQLPLUS  SQLPLUS的常用命令  SQLPLUS的格式化  SQLPLUS中使用帮助 120 第三节 SQL*PLUS工具  4-1 启动SQLPLUS  在命令行运行sql*plus。 语法如下: sqlplus [username]/[password][@server]  在windows环境中运行sql*plus。 具体方法: "开始->程序->Oracle-OraHome92->application development->sql*plus" 121 第三节 SQL*PLUS工具  4-2 SQLPLUS的常用命令  连接命令。  conn[ect]  disc[onnect]  passw[ord]  Exit,quit 默认情况下,当退出sql*plus时会自动提交事务。 122 第三节 SQL*PLUS工具  4-2 SQLPLUS的常用命令  编辑命令。  l[ist]  a[ppend]  c[hange]  Del  i[nput]  edi[t]  run和/ 123 第四节 数据的加载与卸载  SQL*Loader  EXP/IMP  平面文件卸载 124  一个场景:  2008年1月,建行ECIF对公项目上线新增了F2012(公 司补充信息)的日常导出。运维人员反映该导出导出 效率低,耗时很长,大大延长了ECIF批量处理窗口。 125 本节培训的“问题”和“方法”  旨在解决的问题  应用性能低下怎么办?  理解上述问题解决过程的根本方法:  性能优化。 126 第五节 开发中的性能优化  调优的目标  调优的方法  调优的工具 127 Q&A 128 THANKS 学习是一种态度,态度是一件小事,当同一个人抱有2个完全 不同的态度做同一件事情时,其效率/结果却往往会有天壤之别。
还剩127页未读

继续阅读

下载pdf到电脑,查找使用更方便

pdf的实际排版效果,会与网站的显示效果略有不同!!

需要 15 金币 [ 分享pdf获得金币 ] 0 人已下载

下载pdf

pdf贡献者

zhan_kevin

贡献于2011-06-22

下载需要 15 金币 [金币充值 ]
亲,您也可以通过 分享原创pdf 来获得金币奖励!
下载pdf