Mastering PL/SQL Through Illustrations , livre ebook

Mastering PL/SQL Through Illustrations

From Learning Fundamentals to Developing Efficient PL/SQL Blocks

Dr. B. Chandra
www.bpbonline.com
FIRST EDITION 2021
Copyright © BPB Publications, India
ISBN: 978-93-89898-484
All Rights Reserved. No part of this publication may be reproduced or distributed in any form or by any means or stored in a database or retrieval system, without the prior written permission of the publisher with the exception to the program listings which may be entered, stored and executed in a computer system, but they can not be reproduced by the means of publication.
LIMITS OF LIABILITY AND DISCLAIMER OF WARRANTY
The information contained in this book is true to correct and the best of author’s & publisher’s knowledge. The author has made every effort to ensure the accuracy of these publications, but cannot be held responsible for any loss or damage arising from any information in this book.
All trademarks referred to in the book are acknowledged as properties of their respective owners but BPB Publications cannot guarantee the accuracy of this information.
Distributors:
BPB PUBLICATIONS
20, Ansari Road, Darya Ganj
New Delhi-110002
Ph: 23254990/23254991
MICRO MEDIA
Shop No. 5, Mahendra Chambers,
150 DN Rd. Next to Capital Cinema,
V.T. (C.S.T.) Station, MUMBAI-400 001
Ph: 22078296/22078297
DECCAN AGENCIES
4-3-329, Bank Street,
Hyderabad-500195
Ph: 24756967/24756400
BPB BOOK CENTRE
376 Old Lajpat Rai Market,
Delhi-110006
Ph: 23861747
Published by Manish Jain for BPB Publications, 20 Ansari Road, Darya Ganj, New Delhi-110002 and Printed by him at Repro India Ltd, Mumbai
www.bpbonline.com
Dedicated to
My Parents
About the Author
Dr. B. Chandra is an Adjunct Professor at the School of IT, Indian Institute of Technology, Delhi since Jan 2017. She served the computer Science Group of the Department of Maths, IIT Delhi as a senior professor till June 2016. During her tenure she was the Head of the Department for three years and was deeply involved in the curriculum of M.Tech program in Maths and Computing. She was the chief data scientist at Sprinklr Solns for more than a year from August 2016 and subsequent to this as a consultant at Netomi till recently. Her specialization areas include Databases, Distributed databases and Machine Learning. She has immense experience in teaching Database Management course at IIT Delhi apart from teaching other courses in computer science for more than 25 years for a large number of students. The topics covered in the database course included SQL, PL/SQL, Distributed databases apart and basics of databases. She has guided a number of PhD students in these areas at IIT Delhi. She has published more than 100 research papers in reputed International journals in the areas mentioned above. She has authored three books. She is a senior member of IEEE Computer society.
She has been a Visiting Professor for a year with the Graduate School of Business, University of Pittsburgh, Pittsburgh, USA and for a year at Penn State University, University Park, USA. She has also been a Visiting Scientist at NIST, Maryland and INRIA, France.
Your LinkedIn Profile: https://www.linkedin.com/in/dr-b-chandra-192523141/
About the Reviewer
Ram Gupta has over twenty-five years of experience in the software industry. He has served in leadership roles at Silicon Valley technology companies such as Oracle, Yahoo!, and several technology startup companies. His experience encompasses leading the development and management of numerous large-scale systems in the areas of cloud services, enterprise business, eCommerce, media management, social networking, search marketing, machine learning, and artificial intelligence. Ram is an alum of the Indian Institute of Technology, Delhi, where he obtained a Master of Technology degree in Computer Applications.
Acknowledgement
I would like to express my gratitude to Prof. P. K. Kalra, Head, Computer Services Centre, IIT Delhi and Prof. Preeti Ranjan Panda, Head, School of IT, IIT Delhi for their valuable support which enabled me to write this manuscript.
I express my gratitude to Mr. Ram Gupta, who has served ORACLE, Yahoo and other companies at Silicon Valley in leadership roles, for reviewing the manuscript in spite of his busy schedule. I wish to thank him for his highly valuable suggestions.
I thank the team BPB publications for bringing out this book in a short span of time and with dedicated effort.
I am thankful to my husband. Dr. Ashok Mittal and my daughter Dr. Priya Mittal for all the encouragement given to complete this manuscript.
Preface
PL/SQL stands for Procedural Language extensions to the Structured Query Language . PL/SQL can be easily integrated with SQL which is the most popular database language. PL/SQL adds procedural constructs to SQL for overcoming the limitations of SQL and is completely portable. PL/SQL is now a technology present in most Oracle servers and products.
The book extensively covers both the fundamentals and advanced topics in PL/SQL which makes it a unique read both for beginners and developers/ database administrators who want to master PL/SQL. The best part of the book is that it contains practical examples for every concept in PL/SQL. Illustrative PL/SQL blocks using database tables have also been provided in the book.
Chapters 1 covers the fundamentals of PL/SQL in detail. Chapters 2 and 3 give an in-depth description of control statements and different kinds of loops in PL/SQL with number of illustrations. Chapter 4 gives a vivid description of CURSORS in PL/SQL and CURSOR FOR loops. Processing of Explicit and Implicit cursors have been described. The role of cursor attributes has been illustrated through various PL/SQL blocks using database tables. Different types of REF cursors have also been illustrated.
In Chapter 5 it has been explained how to raise exceptions, reraise exceptions and raise exception through PL/SQL blocks using database tables. Predefined Exceptions have also been discussed in this chapter. RAISE_APPLICATION_ERROR for displaying user-defined error messages with error numbers and associating a name to Oracle defined error using PRAGMA EXCEPTION_INIT has been described in depth. Chapters 6 and 7 cover subprograms in depth. Creation of Functions, Procedures and the corresponding invoking blocks have been illustrated. In addition, different calling notations in Functions and Procedures have also been explained with examples.
Chapter 8 gives complete details of creation of Triggers before or after a DML operation INSERT, UPDATE or DELETE on database tables. Chapter 9 illustrates the creation of packages, creation of package body and execution using database tables. Collections in PL/SQL have been described in depth in chapter 10 with number of examples for creating INDEX-BY table, NESTED table and VARRAYS. Creation of INDEX -BY table of INDEX-BY table and INDEX-BY table of NESTED table has also been explained through PL/SQL blocks.
Appendix I gives the steps for requesting a workspace on Oracle APEX and for executing a PL/SQL block. Appendix II gives the description of seven databases and their corresponding tables. This appendix also gives the sample data for each of the database tables. Appendix III gives the commands for creation of database tables and insertion of sample records in each of the tables.
The book is an outcome of the author’s long experience in teaching courses at IIT Delhi and especially the course on Data base Management Systems. The author’s research contributions added value to this manuscript.
Downloading the code bundle and coloured images:
Please follow the link to download the Code Bundle and the Coloured Images of the book:
https://rebrand.ly/o28w58z
Errata
We take immense pride in our work at BPB Publications and follow best practices to ensure the accuracy of our content to provide with an indulging reading experience to our subscribers. Our readers are our mirrors, and we use their inputs to reflect and improve upon human errors if any, occurred during the publishing processes involved. To let us maintain the quality and help us reach out to any readers who might be having difficulties due to any unforeseen errors, please write to us at :
errata@bpbonline.com
Your support, suggestions and feedbacks are highly appreciated by the BPB Publications’ Family.

Did you know that BPB offers eBook versions of every book published, with PDF and ePub files available? You can upgrade to the eBook version at www.bpbonline.com and as a print book customer, you are entitled to a discount on the eBook copy. Get in touch with us at business@bpbonline.com for more details.
At www.bpbonline.com , you can also read a collection of free technical articles, sign up for a range of free newsletters, and receive exclusive discounts and offers on BPB books and eBooks.
BPB is searching for authors like you
If you're interested in becoming an author for BPB, please visit www.bpbonline.com and apply today. We have worked with thousands of developers and tech professionals, just like you, to help them share their insight with the global tech community. You can make a general application, apply for a specific hot topic that we are recruiting an author for, or submit your own idea.
The code bundle for the book is also hosted on GitHub at https://github.com/bpbpublications/Mastering-PL-SQL-Through-Illustrations . In case there's an update to the code, it will be updated on the existing GitHub repository.
We also have other code bundles from our rich catalog of books and videos available at https://github.com/bpbpublications . Check them out!
PIRACY
If you come across any illegal copies of our works in any form on the internet, we would be grateful if you would provide us with the location address or website name. Please contact us at business@bpbonline.com with a link to the material.
If you are interested in becoming an aut