没有合适的资源?快使用搜索试试~ 我知道了~
首页Expert PL SQL Practices for Oracle Developers and DBAs 无水印pdf
Expert PL SQL Practices for Oracle Developers and DBAs 无水印pdf
需积分: 9 18 下载量 110 浏览量
更新于2023-03-16
评论
收藏 7.14MB PDF 举报
Expert PL SQL Practices for Oracle Developers and DBAs 英文无水印pdf pdf所有页面使用FoxitReader和PDF-XChangeViewer测试都可以打开 本资源转载自网络,如有侵权,请联系上传者或csdn删除 本资源转载自网络,如有侵权,请联系上传者或csdn删除
资源详情
资源评论
资源推荐
Rosenblum
Shelve in
Databases/Oracle
User level:
Beginning–Advanced
www.apress.com
SOURCE CODE ONLINE
BOOKS FOR PROFESSIONALS BY PROFESSIONALS
®
Expert PL/SQL Practices
In this book, the best and brightest in the field offer their collected wisdom on
PL/SQL programming. Each chapter deeply examines a specific problem, technol-
ogy, or feature set that you’ll face as a PL/SQL programmer. Together, they show
you how you can unleash true power by properly combining syntax and mechan-
ics with features and techniques. Armed with this knowledge, you’ll be able to do
more with less effort, write code that scales and performs well, and eliminate and
avoid defects.
Inside Expert PL/SQL Practices, you’ll discover how to:
• Know when it is best to use PL/SQL, and when to avoid it
• Move data efficiently using bulk SQL operations
• Write code that scales through pipelining, parallelism, and profiling
• Choose the right PL/SQL cursor type for any given application
• Reduce coding errors through sound development practices such as unit-testing
• Create and execute SQL and PL/SQL dynamically at runtime
The author team of Expert PL/SQL Practices are passionate about PL/SQL and the
power it places at your disposal. Each has chosen his or her topic out of the strong
belief that it can make a positive difference in the quality and scalability of the code
you write. They detail all that PL/SQL has to offer, guiding you, step-by-step, along
the path to mastery.
RELATED
For your convenience Apress has placed some of the front
matter material after the index. Please use the Bookmarks
and Contents at a Glance links to access them.
Download from Wow! eBook <www.wowebook.com>
iii
Contents at a Glance
About the Authors ................................................................................................... xvii
About the Technical Reviewers ................................................................................ xx
Introduction ............................................................................................................. xxi
■Chapter 1: Do Not Use .............................................................................................. 1
■Chapter 2: Dynamic SQL: Handling the Unknown .................................................. 19
■Chapter 3: PL/SQL and Parallel Processing ........................................................... 45
■Chapter 4: Warnings and Conditional Compilation ................................................ 71
■Chapter 5: PL/SQL Unit Testing .............................................................................. 97
■Chapter 6: Bulk SQL Operations ........................................................................... 121
■Chapter 7: Know Your Code ................................................................................. 171
■Chapter 8: Contract-Oriented Programming ........................................................ 213
■Chapter 9: PL/SQL from SQL ................................................................................ 235
■Chapter 10: Choosing the Right Cursor ............................................................... 291
■Chapter 11: PL/SQL Programming in the Large ................................................... 313
■Chapter 12: Evolutionary Data Modeling ............................................................. 367
■Chapter 13: Profiling for Performance ................................................................ 395
■Chapter 14: Coding Conventions and Error Handling .......................................... 425
■Chapter 15: Dependencies and Invalidations ...................................................... 445
Index .......................................................................................................................475
C H A P T E R 1
1
Do Not Use
By Riyaj Shamsudeen
Congratulations on buying this book. PL/SQL is a great tool to have in your toolbox; however, you
should understand that use of PL/SQL is not suitable for all scenarios. This chapter will teach you when
to code your application in PL/SQL, how to write scalable code, and, more importantly, when not to
code programs in PL/SQL. Abuse of some PL/SQL constructs leads to unscalable code. In this chapter, I
will review various cases in which the misuse of PL/SQL was unsuitable and lead to an unscalable
application.
PL/SQL AND SQL
SQL is a set processing language and SQL statements scale better if the statements are written with set
level thinking in mind. PL/SQL is a procedural language and SQL statements can be embedded in PL/SQL
code.
SQL statements are executed in the SQL executor (more commonly known as the SQL engine). PL/SQL
code is executed by the PL/SQL engine. The power of PL/SQL emanates from the ability to combine the
procedural abilities of PL/SQL with the set processing abilities of SQL.
Row-by-Row Processing
In a typical row-by-row processing program, code opens a cursor, loops through the rows retrieved from
the cursor, and processes those rows. This type of loop-based processing construct is highly discouraged
as it leads to unscalable code. Listing 1-1 shows an example of a program using the construct.
Listing 1-1. Row-by-Row Processing
DECLARE
CURSOR c1 IS
SELECT prod_id, cust_id, time_id, amount_sold
FROM sales
CHAPTER 1 ■ DO NOT USE
2
WHERE amount_sold > 100;
c1_rec c1%rowtype;
l_cust_first_name customers.cust_first_name%TYPE;
l_cust_lasT_name customers.cust_last_name%TYPE;
BEGIN
FOR c1_rec IN c1
LOOP
-- Query customer details
SELECT cust_first_name, cust_last_name
INTO l_cust_first_name, l_cust_last_name
FROM customers
WHERE cust_id=c1_rec.cust_id;
--
-- Insert in to target table
--
INSERT INTO top_sales_customers (
prod_id, cust_id, time_id, cust_first_name, cust_last_name,amount_sold
)
VALUES
(
c1_rec.prod_id,
c1_rec.cust_id,
c1_rec.time_id,
l_cust_first_name,
l_cust_last_name,
c1_rec.amount_sold
);
END LOOP;
COMMIT;
END;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:10.93
In Listing 1-1, the program declares a cursor c1, and opens the cursor implicitly using cursor–for-
loop syntax. For each row retrieved from the cursor c1, the program queries the customers table to
populate first_name and last_name to variables. A row is subsequently inserted in to the
top_sales_customers table.
There is a problem with the coding practice exemplified in Listing1-1. Even if the SQL statements
called in the loop are highly optimized, program execution can consume a huge amount of time.
Imagine that the SQL statement querying the customers table consumes an elapsed time of 0.1 seconds,
and that the INSERT statement consumes an elapsed time of 0.1 seconds, giving a total elapsed time of 0.2
seconds per loop execution. If cursor c1 retrieves 100,000 rows, then the total elapsed time for this
program will be 100,000 multiplied by 0.2 seconds: 20,000 seconds or 5.5 hours approximately.
Optimizing this program construct is not easy. Tom Kyte termed this type of processing as slow-by-slow
processing for obvious reasons.
剩余507页未读,继续阅读
yinkaisheng-nj
- 粉丝: 763
- 资源: 6953
上传资源 快速赚钱
- 我的内容管理 收起
- 我的资源 快来上传第一个资源
- 我的收益 登录查看自己的收益
- 我的积分 登录查看自己的积分
- 我的C币 登录后查看C币余额
- 我的收藏
- 我的下载
- 下载帮助
会员权益专享
最新资源
- 2023年中国辣条食品行业创新及消费需求洞察报告.pptx
- 2023年半导体行业20强品牌.pptx
- 2023年全球电力行业评论.pptx
- 2023年全球网络安全现状-劳动力资源和网络运营的全球发展新态势.pptx
- 毕业设计-基于单片机的液体密度检测系统设计.doc
- 家用清扫机器人设计.doc
- 基于VB+数据库SQL的教师信息管理系统设计与实现 计算机专业设计范文模板参考资料.pdf
- 官塘驿林场林防火(资源监管)“空天地人”四位一体监测系统方案.doc
- 基于专利语义表征的技术预见方法及其应用.docx
- 浅谈电子商务的现状及发展趋势学习总结.doc
- 基于单片机的智能仓库温湿度控制系统 (2).pdf
- 基于SSM框架知识产权管理系统 (2).pdf
- 9年终工作总结新年计划PPT模板.pptx
- Hytera海能达CH04L01 说明书.pdf
- 数据中心运维操作标准及流程.pdf
- 报告模板 -成本分析与报告培训之三.pptx
资源上传下载、课程学习等过程中有任何疑问或建议,欢迎提出宝贵意见哦~我们会及时处理!
点击此处反馈
安全验证
文档复制为VIP权益,开通VIP直接复制
信息提交成功
评论0