Oracle PL/SQL practice

Oracle PL/SQL practice
Basic Information
Author: (English) bilington (A.) and so on, Lu Tao [Translator's introduction]
Press: People's post and telecommunications Press
ISBN: 9787115294852
Mounting time:
Published on: February 1, November 2012
Start: 16
Page number: 166
Version: 1-1
Category: Computer> database> Oracle

For more information, Oracle PL/SQL practices
Introduction
Books
Computer books
This book is jointly created by 15 well-known technical experts. Each author uses a chapter to introduce their best PL/SQL topics, covering all aspects of PL/SQL development. The author of this book is either a backbone member in the Oracle community or a member of the famous oak table, and is often active in the first line of Oracle technical training. He has a thorough understanding of PL/SQL, it has a simple and unique way to explain complex problems. What are you waiting?
This book focuses on the latest and most practical features of PL/SQL, and how to make it more efficient, better effect, etc. Three sections fully explain the various topics related to PL/SQL. In addition, each chapter is equipped with very appropriate sample code, tracking graphs, and output results, supplemented by in-depth explanations, which makes you feel overwhelmed. Each chapter covers the best practices in PL/SQL development, reflecting the author's accumulated experience and lessons over the years, and its value is extraordinary.
This book is suitable for readers with certain PL/SQL experience.
Directory
Chapter 1 avoiding misuse 1
1.1 row-by-row processing 1
1.2 nested row-by-row processing 3
1.3 lookup query 5
1.4 excessive dual access 8
1.4.1 arithmetic operation of date 8
1.4.2 access sequence 9
1.4.3 fill master-slave 9
Over 1.5 function calls 10
1.5.1 unnecessary function calls 10
1.5.2 expensive function call 12
1.6 Database Link call 14
1.7 over-use trigger 15
1.8 over-Submission 15
1.9 excessive resolution 16
1.10 Summary 16
Chapter 2 dynamic SQL: handling unknown 18
2.1 three methods of dynamic SQL 19
2.1.1 local dynamic SQL 19
2.1.2 Dynamic Cursor 21
2.1.3 dbms_ SQL 25
2.2 example of dynamic thinking 26
2.3 Security Question 30
2.4 performance and resource utilization 33
2.4.1 reverse mode 34
2.4.2 comparison of dynamic SQL implementation 35
2.5 object dependency 37
2.5.1 negative impact 37
2.5.2 positive impact 37
2.6 summary 38
Chapter 3 PL/SQL and parallel processing 39
3.1 why parallel processing 39
3.2 law affecting parallel processing 40
3.3 rise of big data 40
3.4 parallel and distributed processing 41
3.5 parallel hardware architecture 41
3.6 define objective 42
3.6.1 acceleration 42
3.6.2 proportional expansion 43
3.6.3 concurrency 43
3.7 candidate workloads for Parallel Processing 43
3.7.1 parallel and OLTP 43
3.7.2 parallel and non-OLTP workloads 44
3.8 mapreduce programming model 44
3.9 45 before PL/SQL
3.10 processes that can be used for parallel activities 45
3.11 parallel execution server using mapreduce 46
3.11.1 pipeline table function 46
3.11.2 Guide 60
3.11.3 parallel pipeline table function summary 61
3.12 summary 61
Chapter 2 warning and Conditional compilation 62
4.1 PL/SQL warning 62
4.1.1 basic 62
4.1.2 use warning 63
4.1.3 update warning to error 67
4.1.4 ignore warning 68
4.1.5 compilation and warning 69
4.1.6 Conclusion 72
4.2 Conditional compilation 72
4.2.1 base 72
4.2.2 which part of the code is running 75
4.2.3 benefits of preprocessing Code 76
4.2.4 validity verification 78
4.2.5 control compilation 80
4.2.6 query variable 81
4.2.7 concluding remarks on Conditional compilation 82
4.3 Summary 84
Chapter 4 PL/SQL unit test 85
5.1 why test code 85
5.2 what is unit test 86
5.2.1 debug or test 86
5.2.2 test establishment time 86
5.3 unit test build tool 87
5.3.1 utplsql: Use the command line code 87
5.3.2 quest code Tester for Oracle 87
5.3.3 Oracle SQL developer 88
5.4 prepare and maintain the unit test environment 88
5.4.1 create a unit test archive 89
5.4.2 maintain the unit test data warehouse 90
5.4.3 import Test 91
5.5 building unit test 91
5.5.1 Unit Test Wizard 91
5.5.2 create the first test implementation 92
5.5.3 add start and remove processes 93
5.5.4 collect code coverage statistics 93
5.5.5 specify parameter 93
5.5.6 add process verification 94
5.5.7 save test 95
5.5.8 debug and run test 95
5.6 extended testing scope 95
5.6.1 create a search value 96
5.6.2 Implant Test implementation 97
5.6.3 create a dynamic query 98
5.7 support unit test function 99
5.7.1 run report 99
5.7.2 create a library 100
5.7.3 export, import, and synchronize test 103
5.7.4 build kit 105
5.8 run test 105 from the command line
5.9 Conclusion 106
Chapter 2 batch SQL operations 6th
6.1 hardware store 107
6.2 set the example in this Chapter 108
6.3 Perform Batch operations in PL/SQL 108
6.3.1 getting started in batch 110
6.3.2 Data Types of three collection styles 112
6.3.3 why bother yourself? 114
6.3.4 116 overhead of monitoring batch collection
6.3.5 refactor code to use batch collection 119
6.4 batch binding 127
6.4.1 getting started with batch binding 127
6.4.2 measure the batch binding performance of 128
6.4.3 monitor memory usage 131
6.4.4 11g improvement 133
6.5 batch binding error handling 134
6.5.1 save limits and batch operations 137
6.5.2 log errors clause 138
6.5.3 robust batch binding 139
6.6 legitimate reasons for large-scale collection 143
6.7 real benefits: client batch processing 145
6.8 conclusion 149
Chapter 4 understanding your code 7th
7.1 selection of content in this Chapter 152
7.2 Automatic Code Analysis 153
7.2.1 static analysis 154
7.2.2 dynamic analysis 154
7.3 timing of analysis 154
7.4 perform static analysis 156
7.4.1 data dictionary 156
7.4.2 PL/scope 163
7.5 perform dynamic analysis 175
7.5.1 dbms_profiler and dbms_trace 175
7.5.2 dbms_hprof 184
7.6 Conclusion 189
Chapter 2 Contract-Oriented Programming 8th
8.1 contractual design 190
8.1.1 software contract 190
8.1.2 elements of the basic contract 191
8.1.3 asserted 192
8.1.4 reference 192
8.2 PL/SQL contract 192
8.2.1 basic assert program 192
8.2.2 standard package local asserted 194
8.2.3 use assert to execute contract 196
8.2.4 Other improvement 198
8.2.5 contract-oriented function prototype 199
8.3 example: test the odd and even numbers of 200
8.4 useful Contract Model 202
8.4.1 use not null input and output not null 202
8.4.2 The function returns not null 203
8.4.3 Boolean functions return not null 203
8.4.4 check function: returns true or assertfail 204
8.5 principle of error-free code 205
8.5.1 strictly assert prerequisite 205
8.5.2 meticulous modularization 206
8.5.3 function-based interface 206
8.5.4 crash at assertfail 207
8.5.5 perform regression tests on post conditions 207
8.5.6 avoid 207 conflict between correctness and performance.
8.5.7 use ORACLE 11g for optimized compilation 208
8.6 conclusion 209
Chapter 2 calling PL/SQL 9th from SQL
9.1 cost of using PL/SQL functions in SQL 210
9.1.1 context switch 211
9.1.2 run 216
9.1.3 less ideal data access 218
9.1.4 difficulties of optimizer 222
9.1.5 trap of read consistency 226
9.1.6 other questions 228
9.2 reduce the cost of PL/SQL functions by 228
9.2.1 overall situation view 229
9.2.2 use SQL alternatives 230
9.2.3 execution reduced by 236
9.2.4 assist CBO 244
9.2.5 optimizing PL/SQL 255
9.3 conclusion 257
Chapter 4 select the correct cursor 10th
10.1 explicit cursor 258
10.1.1 anatomical explicit cursor 260
10.1.2 explicit cursor and batch processing 261
10.1.3 ref cursor Introduction 262
10.2 implicit cursor 263
10.2.1 anatomy implicit cursor 264
10.2.2 implicit cursor and additional theory 265
10.3 static ref cursor 267
10.3.1 detailed list of limits on cursor variables 269
10.3.2 client and ref cursors 270
10.3.3 resolution topic 271
10.4 dynamic ref cursor 273
10.4.1 examples and best practices 273
10.4.2 SQL Injection threat 275
10.4.3 description of column 276 in Ref cursor
10.5 conclusion 277
Chapter 2 large-scale PL/SQL programming 11th
11.1 use the database as an application server based on PL/SQL 279
11.1.1 Case Study: avaloq banking 279
11.1.2 advantages of using PL/SQL in the database for business logic 281
11.1.3 restrictions on using databases as PL/SQL-based application servers 283
11.1.4 software factor 284
11.2 requirements for large-scale programming 284
11.3 consistency achieved through specifications 285
11.3.1 acronym 286
11.3.2 prefix and suffix 289 for PL/SQL identifiers
11.4 code and data modularization 291
11.4.1 package and related table as module 293
11.4.2 modules containing multiple packages or sub-modules 297
11.4.3 mode as module 299
11.4.4 modular 303 in Mode
11.4.5 comparison between modular mode and modular mode 306
11.5 use PL/SQL Object-Oriented Programming 306
11.5.1 use User-Defined Object-Oriented Programming 307
11.5.2 use PL/SQL to record Object-Oriented Programming 310
11.5.3 evaluation 316
11.6 memory management 317
11.6.1 memory usage 317
11.6.2 set 322
11.7 Conclusion 325
Chapter 2 progressive Data Modeling 12th
12.1 experience gained from 20 years of system development 327
12.2 database and agile development 328
12.3 progressive Data Modeling 329
12.4 rebuilding a database 331
12.5 create an access layer through PL/SQL 335
12.6 agile declaration 347
12.7 incremental data modeling with PL/SQL 349
12.7.1 define interface 349
12.7.2 think about scalability 349
12.7.3 test-driven development 350
12.7.4 use mode and user 350 wisely
12.8 conclusion 351
Chapter 2 Performance Analysis 13th
13.1 what is performance 353
13.1.1 functional requirements: 353
13.1.2 response time 353
13.1.3 throughput 354
13.1.4 354 Resource Utilization
13.1.5 performance is a 355 feature.
13.2 what is profiling 356
13.2.1 sequence 356
13.2.2 magic 357 of profiles
13.2.3 benefits of Performance Analysis 357
13.3 performance measurement 358
13.3.1 why is the program 358 slow?
13.3.4 Conditional compilation 364
13.3.5 built-in analyzer 365
13.3.6 extended SQL tracking data (event 10046) 365
13.3.7 Oracle measurement tool Library (ILO) 366
13.4 problem diagnosis 368
13.4.1 R approach 369
13.4.2 ILO example 371
13.4.3 sample analysis 373
13.5 conclusion 376
Chapter 4 coding specifications and error handling 14th
14.1 why coding specifications 378
14.2 format 379
14.2.1 Case 379
14.2.2 note 380
14.2.3 comparison 380
14.2.4 indent 380
14.3 dynamic code 383
14.4 pack 384
14.5 Stored Procedure 385
14.5.1 name 385
14.5.2 parameter 386
14.5.3 call 386
14.5.4 local variable 386
14.5.5 constant 386
14.5.6 type 387
14.5.7 global variable 387
14.5.8 local stored procedures and functions 387
14.5.9 Stored Procedure metadata 388
14.6 function 388
14.7 error handling 389
14.7.1 error capture 389
14.7.2 Error Report 390
14.7.3 error 391 recovery
14.7.4 first test and then display 392
14.8 conclusion 392
Chapter 2 dependency and failure 15th
15.1 dependency chain 395
15.2 shorten the dependency chain by 401
15.3 data type reference 406
15.4 views used for table modification 407
15.5 add components to package 410
15.6 synonyms in the dependency chain 413
15.7 Resource lock 414
15.8 use a trigger to forcibly execute dependency 415
15.9 create trigger 418 that was originally disabled
15.10 conclusion 420

Source of this book: China Interactive publishing network