读书笔记 《精通Oracle PL/SQL》
来源：互联网 发布：大数据在医疗中的应用 编辑：程序博客网 时间：2024/04/29 11:08
Mastering Oracle PL/SQL Practical Solutions
五位作者：
Connnor MaDonald
Chaim Katz
Christopher Beck
Joel R. Kallman
David C. Knox
安装
一、高效能的PL/SQL
这里故意避开术语“性能”，因为效能不只是性能。
效能是指什么呢？假定你的代码符合功能需求，则一般认为，PL/SQL代码满足以下3个条件才能称为高效能（efficient）。
a. 性能：运行的时间在可接受的范围内。
b. 影响：它不会破坏（或者以某种不可接受的方式削弱）你系统中任何其他组件。
c. 可论证性：你可以证明（或表明），在所有合理的条件下，代码都不会破坏上述性能和影响条件。简言之，即使你想破坏性能也做不到。
要使程序达到高效能，运行消耗的时间必须在要求的时间内，且不会对系统的其他部分或资源造成严重破坏。我们还要能够论效能在大多数或所有可预期的条件下可以保持。
我们在此只围绕达到高效能集中讨论3点大的准则。
a. 当解析所要执行的SQL代码时，尽量减少数据库的工作量。从某种意义上说，这不是特指PL/SQL，因为不只是PL/SQL运行SQL。它适用于执行SQL的任何语言（Java, Visual Baisc等）。然而，它可能是导致Oracle应用程序不可扩展的首要原因，因此需要加以讨论。
b. 理解PL/SQL的特征和函数，并掌握如何正确地开发以避免无谓地重复。
c. 绝不使用PL/SQ来做SQL的工作。
文中列举了三个例子。
1. 显示日历
2. 计算中间数 用解析函数percentile_count。
3. 条件表的查找 用标量子查询功能。
二、全部打包
平时也用包，也就是将PL/SQL各部分组合在一起而已。
包的基本好处：
1. 包的重载
2. 包中的公有变量和私有变量
3. 初始化
4. 信息隐藏
信息隐藏的好处、永久的公有和私有变量以及初始化块，都不可避免地与包被分割成公有和私有部分联系在一起。该分割还提供了包对于独立过程的最有意义的优势：从所谓的“依赖危机（dependency crisis）”中独立出来。
三、令人困惑的游标
游标是十分简单的事物。你让数据库准备些结果，之后你得到了这些结果，然后你告诉数据库这些结果用过了。
1. 显式游标与隐式游标
隐式游标的原理：
a. 打开游标
b. 读取行
c. 再次读取（以检查 TOO_MANY_ROWS）
d. 关闭游标
由于PL/SQL开发者能完全控制显式游标，可以编写代码只读取一次，因此显式游标更高效。这确实是一个令人信服的论据，在PL/SQL的早期版本中，这个论据曾经是有价值的。然而，早在Oracle版本7.1时，就引入了欲取的概念。简而言之，当调用欲取操作用于从游标中读取一行时，下一行也在同一调用中获得。集合现代磁盘驱动器和文件系统中的预先读取（read-ahead）类似。数据库预测，你通常都会获取下一行。
有个“存在性检查”的例子：检查是否有员工是上个月聘用的。
差劲的SQL:
select count(*) from emp whre hiredate > trunc(sysdate, 'MM');
较好：
select count(*) from dual where exists (select null from emp where hiredate > trunc(sysdate, 'MM'));
其实隐式游标并没有比显式游标更强大的神奇属性。然而，这里所证明的是，隐式游标在代码上更加简洁，且在大多数情况下，性能等同于等价的显式游标，甚至比它更强。
另外，隐式游标更容易理解，不容易想显式游标那样被误用。
2. 跨架构的游标管理
a. 游标变量
游标变量提供了统一的方法，通过这种方法，查询结果集可以在应用程序组件之间传递。
游标变量在客户端定义，在服务器端打开，而从客户端读取数据。不需要在服务器端创建结果集并将整个结果集传递回客户端，客户端能直接访问该游标。无论何处，只要你拥有支持游标变量的客户端应用程序（Java, Proc*C等），客户端程序就可以访问游标结果集，而不需要获得整个结果集，然后保存，并传回。
四、高效数据处理
在PL/SQL中使用集合
使用集合最主要的动机是，它鼓励开发者多以集的方式思考，而不是行。虽然没有功能上的原因阻止开发者使用每次处理结果集中的一行，但从高效和性能的角度来讲，这样并不好。
批收集： bulk collect
批量绑定： forall。它被设计用来减少另一种开销---PL/SQL内的上下文切换（context switching）。在PL/SQL程序启动后，数据库中的PL/SQL运行时引擎开始执行代码。当在代码中遇到SQL语句后，PL/SQL运行时引擎将SQL传递给SQL执行引擎并等待响应。所以，一般的PL/SQL块在运行时会在两个引擎之间前后跳来跳去。
批量绑定的错误处理： save exceptions。现在当异常发生时，可以访问我们需要的信息来查明是什么导致了错误，更重要的是，可以取得集合中的行索引。sql%bulk_exceptions
用limit子句。
五、PL/SQL优化技巧
1. 尽量减少解析和内存消耗
定义者权限（默认）
调用者权限
使用管道函数
2. 数据类型：提示和技巧
关联数组，集合，使用基于记录的DML时的问题
3. 调用PL/SQL
六、触发器
七、DBA包
八、安全包
九、Web包
十、PL/SQL调试
自定义的DEBUG工具
开发DEBUG包背后的主要思想是让开发者更好地控制谁产生消息以及产生什么消息，而不需修改应用程序。
基于该主要思想，DEBUG工具的核心需求如下：
1. 对任何PL/SQL都可用
2. 生成的调试数量无限制
3. 生成的每行调试信息的长度无限制
4. 实时反馈
5. 自知（如，它知道自己是在哪个包中的哪一行）
6. 能轻易地开启和关闭
7. 能选择对包/过程/函数进行调试
8. 根据运行的用户，而采取不同的行为
9. 将消息保存于文件中
10. 容易使用
既然代码可以从http://www.apress.com免费下载，我就顺便把它贴出来吧。
三个文件： debug_ddl.sql，debug.pkh，debug.sql。
============ debug_ddl.sql
drop table debugTab
/
create table debugTab(
userid varchar2(30),
dir varchar2(32),
filename varchar2(1024),
modules varchar2(4000),
show_date varchar2(3),
date_format varchar2(255),
name_length number,
show_session_id varchar2(3),
--
-- Constraints
--
constraint debugtab_pk
primary key ( userid, filename ),
constraint debugtab_show_date_ck
check ( show_date in ( 'YES', 'NO' ) ),
constraint debugtab_show_session_id_ck
check ( show_session_id in ( 'YES', 'NO' ) )
)
/
create or replace
trigger biu_fer_debugtab
before insert or update on debugtab for each row
begin
:new.modules := upper( :new.modules );
:new.show_date := upper( :new.show_date );
:new.show_session_id := upper( :new.show_session_id );
:new.userid := upper( :new.userid );
declare
l_date varchar2(100);
begin
l_date := to_char( sysdate, :new.date_format );
exception
when others then
raise_application_error(
-20001,
'Invalid date format "' ||
:new.date_format || '"' );
end;
declare
l_handle utl_file.file_type;
--l_file varchar2(32767);
--l_location varchar2(23767);
begin
--l_file := substr( :new.filename,
--instr( replace( :new.filename, '\', '/' ),
--'/', -1 )+1 );
--l_location := substr( :new.filename,
--1,
--instr( replace( :new.filename, '\', '/' ),
--'/', -1 )-1 );
l_handle := utl_file.fopen(
location => :new.dir,
filename => :new.filename,
open_mode => 'a',
max_linesize => 32767 );
utl_file.fclose( l_handle );
exception
when others then
raise_application_error(
-20001,
'Cannot open debug dir/file ' ||
:new.dir || '/' ||
:new.filename );
end;
end;
/
============ debug.pkh
create or replace
package debug as
--
-- Type Definitions
--
type Argv is table of varchar2(4000);
emptyDebugArgv Argv;
--
-- Initializes the debuging for specified p_modules and will dump the
-- output to the p_dir directory on the server for the user p_user.
--
procedure init(
p_modules in varchar2 default 'ALL',
p_dir in varchar2 default 'TEMP',
p_file in varchar2 default user || '.dbg',
p_user in varchar2 default user,
p_show_date in varchar2 default 'YES',
p_date_format in varchar2 default 'MMDDYYYY HH24MISS',
p_name_len in number default 30,
p_show_sesid in varchar2 default 'NO' );
procedure status(
p_user in varchar2 default user,
p_dir in varchar2 default null,
p_file in varchar2 default null );
procedure f(
p_message in varchar2,
p_arg1 in varchar2 default null,
p_arg2 in varchar2 default null,
p_arg3 in varchar2 default null,
p_arg4 in varchar2 default null,
p_arg5 in varchar2 default null,
p_arg6 in varchar2 default null,
p_arg7 in varchar2 default null,
p_arg8 in varchar2 default null,
p_arg9 in varchar2 default null,
p_arg10 in varchar2 default null );
procedure fa(
p_message in varchar2,
p_args in Argv default emptyDebugArgv );
procedure clear(
p_user in varchar2 default user,
p_dir in varchar2 default null,
p_file in varchar2 default null );
end debug;
/
show error
============ debug.sql
create or replace
package body debug as
g_session_id varchar2(2000);
procedure who_called_me(
o_owner in out varchar2,
o_object out varchar2,
o_lineno out number ) is
--
l_call_stack long default dbms_utility.format_call_stack;
l_line varchar2(4000);
begin
/*
----- PL/SQL Call Stack -----
object line object
handle number name
86c60290 17 package body OPS$CLBECK.DEBUG
86c60290 212 package body OPS$CLBECK.DEBUG
86c60290 251 package body OPS$CLBECK.DEBUG
86aa28f0 1 procedure OPS$CLBECK.A
86a9e940 1 anonymous block
*/
-- skip three header lines and first levels in the stack
for i in 1 .. 6 loop
l_call_stack := substr( l_call_stack, instr( l_call_stack, chr(10) )+1 );
end loop;
-- set l_line to the current line
l_line := substr( l_call_stack, 1, instr( l_call_stack, chr(10) ) - 1 );
-- strip object handle
l_line := ltrim( substr( l_line, instr( l_line, ' ' )));
-- assign line number
o_lineno := to_number(substr( l_line, 1, instr( l_line, ' ' )));
l_line := ltrim( substr( l_line, instr( l_line, ' ' )));
-- strip out object type
l_line := ltrim( substr( l_line, instr( l_line, ' ' )));
-- if 'package body' or 'anonymous block', strip out second piece
if l_line like 'block%' or
l_line like 'body%' then
l_line := ltrim( substr( l_line, instr( l_line, ' ' )));
end if;
-- assign owner and object name
o_owner := ltrim(rtrim(substr( l_line, 1, instr( l_line, '.' )-1 )));
o_object := ltrim(rtrim(substr( l_line, instr( l_line, '.' )+1 )));
if o_owner is null then
o_owner := user;
o_object := 'ANONYMOUS BLOCK';
end if;
end who_called_me;
function parse_it(
p_message in varchar2,
p_argv in argv,
p_header_length in number ) return varchar2 is
--
l_message long := null;
l_str long := p_message;
l_idx number := 1;
l_ptr number := 1;
begin
if nvl( instr( p_message, '%' ), 0 ) = 0 and
nvl( instr( p_message, '\' ), 0 ) = 0 then
return p_message;
end if;
loop
l_ptr := instr( l_str, '%' );
exit when l_ptr = 0 or l_ptr is null;
l_message := l_message || substr( l_str, 1, l_ptr-1 );
l_str := substr( l_str, l_ptr+1 );
if substr( l_str, 1, 1 ) = 's' then
l_message := l_message || p_argv(l_idx);
l_idx := l_idx + 1;
l_str := substr( l_str, 2 );
elsif substr( l_str,1,1 ) = '%' then
l_message := l_message || '%';
l_str := substr( l_str, 2 );
else
l_message := l_message || '%';
end if;
end loop;
l_str := l_message || l_str;
l_message := null;
loop
l_ptr := instr( l_str, '\' );
exit when l_ptr = 0 or l_ptr is null;
l_message := l_message || substr( l_str, 1, l_ptr-1 );
l_str := substr( l_str, l_ptr+1 );
if substr( l_str, 1, 1 ) = 'n' then
l_message := l_message || chr(10) ||
rpad( ' ', p_header_length, ' ' );
l_str := substr( l_str, 2 );
elsif substr( l_str, 1, 1 ) = 't' then
l_message := l_message || chr(9);
l_str := substr( l_str, 2 );
elsif substr( l_str, 1, 1 ) = '\' then
l_message := l_message || '\';
l_str := substr( l_str, 2 );
else
l_message := l_message || '\';
end if;
end loop;
return l_message || l_str;
end parse_it;
function build_it(
p_debug_row in debugTab%rowtype,
p_owner in varchar2,
p_object in varchar2,
p_lineno number ) return varchar2 is
--
l_header long := null;
begin
if p_debug_row.show_session_id = 'YES' then
l_header := g_session_id || ' - ';
end if;
if p_debug_row.show_date = 'YES' then
l_header := l_header ||
to_char( sysdate,
nvl( p_debug_row.date_format,
'MMDDYYYY HH24MISS' ) );
end if;
l_header :=
l_header ||
'(' ||
lpad( substr( p_owner || '.' || p_object,
greatest( 1, length( p_owner || '.' || p_object ) -
least( p_debug_row.name_length, 61 ) + 1 ) ),
least( p_debug_row.name_length, 61 ) ) ||
lpad( p_lineno,5 ) ||
') ';
return l_header;
end build_it;
function file_it(
p_dir in debugtab.dir%type,
p_file in debugtab.filename%type,
p_message in varchar2 ) return boolean is
--
l_handle utl_file.file_type;
--l_file long;
--l_location long;
begin
--l_file := substr( p_file,
--instr( replace( p_file, '\', '/' ),
--'/', -1 )+1 );
--l_location := substr( p_file,
--1,
--instr( replace( p_file, '\', '/' ),
--'/', -1 )-1 );
l_handle := utl_file.fopen(
location => p_dir,
filename => p_file,
open_mode => 'a',
max_linesize => 32767 );
utl_file.put( l_handle, '' );
utl_file.put_line( l_handle, p_message );
utl_file.fclose( l_handle );
return true;
exception
when others then
if utl_file.is_open( l_handle ) then
utl_file.fclose( l_handle );
end if;
return false;
end file_it;
procedure debug_it(
p_message in varchar2,
p_argv in argv ) is
--
l_message long := null;
l_header long := null;
call_who_called_me boolean := true;
l_owner varchar2(255);
l_object varchar2(255);
l_lineno number;
l_dummy boolean;
begin
for c in ( select *
from debugtab
where userid = user )
loop
if call_who_called_me then
who_called_me( l_owner, l_object, l_lineno );
call_who_called_me := false;
end if;
if instr( ',' || c.modules || ',',
',' || l_object || ',' ) <> 0 or
c.modules = 'ALL'
then
l_header := build_it( c, l_owner, l_object, l_lineno );
l_message := parse_it( p_message, p_argv, length(l_header) );
l_dummy := file_it( c.dir, c.filename, l_header || l_message );
end if;
end loop;
end debug_it;
--
-- Public Procedures/Functions
--
procedure fa(
p_message in varchar2,
p_args in Argv default emptyDebugArgv ) is
begin
debug_it( p_message, p_args );
end fa;
procedure f(
p_message in varchar2,
p_arg1 in varchar2 default null,
p_arg2 in varchar2 default null,
p_arg3 in varchar2 default null,
p_arg4 in varchar2 default null,
p_arg5 in varchar2 default null,
p_arg6 in varchar2 default null,
p_arg7 in varchar2 default null,
p_arg8 in varchar2 default null,
p_arg9 in varchar2 default null,
p_arg10 in varchar2 default null ) is
begin
debug_it( p_message,
argv( substr( p_arg1, 1, 4000 ),
substr( p_arg2, 1, 4000 ),
substr( p_arg3, 1, 4000 ),
substr( p_arg4, 1, 4000 ),
substr( p_arg5, 1, 4000 ),
substr( p_arg6, 1, 4000 ),
substr( p_arg7, 1, 4000 ),
substr( p_arg8, 1, 4000 ),
substr( p_arg9, 1, 4000 ),
substr( p_arg10, 1, 4000 ) ) );
end f;
procedure status(
p_user in varchar2 default user,
p_dir in varchar2 default null,
p_file in varchar2 default null ) is
--
l_found boolean := false;
begin
dbms_output.put_line( chr(10) );
dbms_output.put_line( 'Debug info for ' ||
p_user );
for c in ( select *
from debugtab
where userid = p_user
and nvl( p_file, filename ) = filename
and nvl( p_dir, dir ) = dir )
loop
dbms_output.put_line( '---------------' ||
rpad( '-', length( p_user ), '-' ) );
l_found := true;
dbms_output.put_line( 'USER: ' ||
c.userid );
dbms_output.put_line( 'MODULES: ' ||
c.modules );
dbms_output.put_line( 'DIRECTORY: ' ||
c.dir );
dbms_output.put_line( 'FILENAME: ' ||
c.filename );
dbms_output.put_line( 'SHOW DATE: ' ||
c.show_date );
dbms_output.put_line( 'DATE FORMAT: ' ||
c.date_format );
dbms_output.put_line( 'NAME LENGTH: ' ||
c.name_length );
dbms_output.put_line( 'SHOW SESSION ID: ' ||
c.show_session_id );
dbms_output.put_line( ' ' );
end loop;
if not l_found then
dbms_output.put_line( 'No debug setup.' );
end if;
end status;
procedure clear( p_user in varchar2 default user,
p_dir in varchar2 default null,
p_file in varchar2 default null ) is
pragma autonomous_transaction;
begin
delete from debugTab
where userid = p_user
and filename = nvl( p_file, filename )
and dir = nvl( p_dir, dir );
commit;
end clear;
procedure init(
p_modules in varchar2 default 'ALL',
p_dir in varchar2 default 'TEMP',
p_file in varchar2 default user || '.dbg',
p_user in varchar2 default user,
p_show_date in varchar2 default 'YES',
p_date_format in varchar2 default 'MMDDYYYY HH24MISS',
p_name_len in number default 30,
p_show_sesid in varchar2 default 'NO' ) is
--
pragma autonomous_transaction;
debugTab_rec debugTab%rowtype;
l_message long;
begin
delete from debugTab
where userid = p_user
and filename = p_file;
insert into debugTab(
userid, modules, dir, filename, show_date,
date_format, name_length, show_session_id )
values (
p_user, p_modules, p_dir, p_file, p_show_date,
p_date_format, p_name_len, p_show_sesid )
returning
userid, modules, dir, filename, show_date,
date_format, name_length, show_session_id
into
debugTab_rec.userid, debugTab_rec.modules, debugTab_rec.dir,
debugTab_rec.filename, debugTab_rec.show_date,
debugTab_rec.date_format, debugTab_rec.name_length,
debugTab_rec.show_session_id;
l_message := chr(10) || 'Debug parameters initialized on ' ||
to_char( sysdate, 'dd-MON-yyyy hh24:mi:ss' ) || chr(10);
l_message := l_message || ' USER: ' ||
debugTab_rec.userid || chr(10);
l_message := l_message || ' MODULES: ' ||
debugTab_rec.modules || chr(10);
l_message := l_message || ' DIRECTORY: ' ||
debugTab_rec.dir || chr(10);
l_message := l_message || ' FILENAME: ' ||
debugTab_rec.filename || chr(10);
l_message := l_message || ' SHOW DATE: ' ||
debugTab_rec.show_date || chr(10);
l_message := l_message || ' DATE FORMAT: ' ||
debugTab_rec.date_format || chr(10);
l_message := l_message || ' NAME LENGTH: ' ||
debugTab_rec.name_length || chr(10);
l_message := l_message || 'SHOW SESSION ID: ' ||
debugTab_rec.show_session_id || chr(10);
if not file_it( debugTab_rec.dir, debugTab_rec.filename, l_message ) then
rollback;
raise_application_error(
-20001,
'Can not open directory - file: ' ||
debugTab_rec.dir || ' - ' || debugTab_rec.filename );
end if;
commit;
end init;
begin
g_session_id := userenv('SESSIONID');
end debug;
/
show error
很多地方值得仔细阅读。留着以后慢慢边看边记录。
- 读书笔记 《精通Oracle PL/SQL》
- oracle读书笔记-----PL/SQL编程
- Oracle读书笔记-PL/SQL基础
- 精通Oracle 10g Pl/SQL编程
- 精通Oracle PL SQL.pdf 免费下载
- 精通Oracle SQL（第二版）读书笔记
- 精通Oracle SQL（第二版）读书笔记
- 精通pl/sql编程
- oracle从入门到精通 之 四.PL/SQL学习
- 学习《精通oracle 10g pl/sql编程》的感受
- Oracle PL/SQL从入门到精通pdf
- PL/SQL读书笔记
- [DB]读书笔记：Oracle下的PL/SQL--1
- Oracle读书笔记-----PL/SQL编程（二）之程序流程
- Oracle读书笔记-----PL/SQL编程(三)之游标
- 《Oracle 11g SQL 和PL SQL从入门到精通》 学习笔记
- <<精通Oracle 10g PL/SQL编程》（水利水电）学习笔记
- 《精通Oracle10g PL/SQL编程》 是本oracle入门的好书
- WinCE6.0中应用程序如何直接访问物理空间
- Access SQL注入参考
- 由Spring管理的Struts2的Action的单实例问题
- rails代码重构之 视图层的渲染问题
- three20 拦路虎
- 读书笔记 《精通Oracle PL/SQL》
- struts2+spring整合
- Android 短信监听
- 模拟登录
- 用谷歌浏览器来当手机模拟器
- 批量插入数据的2种方法
- 如何在eclipse中添加browser调试
- js date函数|js date 格式化|js date 对象(转)
- Java获取操作系统信息
