51,59 €
Mehr erfahren.
- Herausgeber: Packt Publishing
- Kategorie: Wissenschaft und neue Technologien
- Sprache: Englisch
Master the advanced concepts of PL/SQL for professional-level certification and learn the new capabilities of Oracle Database 12c
About This Book
- Learn advanced application development features of Oracle Database 12c and prepare for the 1Z0-146 examination
- Build robust and secure applications in Oracle PL/SQL using the best practices
- Packed with feature demonstrations and illustrations that will help you learn and understand the enhanced capabilities of Oracle Database 12c
Who This Book Is For
This book is for Oracle developers responsible for database management. Readers are expected to have basic knowledge of Oracle Database and the fundamentals of PL/SQL programming. Certification aspirants can use this book to prepare for 1Z0-146 examination in order to be an Oracle Certified Professional in Advanced PL/SQL.
What You Will Learn
- Learn and understand the key SQL and PL/SQL features of Oracle Database 12c
- Understand the new Multitenant architecture and Database In-Memory option of Oracle Database 12c
- Know more about the advanced concepts of the Oracle PL/SQL language such as external procedures, securing data using Virtual Private Database (VPD), SecureFiles, and PL/SQL code tracing and profiling
- Implement Virtual Private Databases to prevent unauthorized data access
- Trace, analyze, profile, and debug PL/SQL code while developing database applications
- Integrate the new application development features of Oracle Database 12c with the current concepts
- Discover techniques to analyze and maintain PL/SQL code
- Get acquainted with the best practices of writing PL/SQL code and develop secure applications
In Detail
Oracle Database is one of the most popular databases and allows users to make efficient use of their resources and to enhance service levels while reducing the IT costs incurred. Oracle Database is sometimes compared with Microsoft SQL Server, however, Oracle Database clearly supersedes SQL server in terms of high availability and addressing planned and unplanned downtime. Oracle PL/SQL provides a rich platform for application developers to code and build scalable database applications and introduces multiple new features and enhancements to improve development experience.
Advanced Oracle PL/SQL Developer's Guide, Second Edition is a handy technical reference for seasoned professionals in the database development space. This book starts with a refresher of fundamental concepts of PL/SQL, such as anonymous block, subprograms, and exceptions, and prepares you for the upcoming advanced concepts. The next chapter introduces you to the new features of Oracle Database 12c, not limited to PL/SQL. In this chapter, you will understand some of the most talked about features such as Multitenant and Database In-Memory. Moving forward, each chapter introduces advanced concepts with the help of demonstrations, and provides you with the latest update from Oracle Database 12c context. This helps you to visualize the pre- and post-applications of a feature over the database releases. By the end of this book, you will have become an expert in PL/SQL programming and will be able to implement advanced concepts of PL/SQL for efficient management of Oracle Database.
Style and approach
The book follows the structure of the Oracle Certification examination but doesn't restrict itself to the exam objectives. Advanced concepts have been explained in an easy-to-understand style, supported with feature demonstrations and case illustrations.
Sie lesen das E-Book in den Legimi-Apps auf:
Seitenzahl: 458
Veröffentlichungsjahr: 2016
Ähnliche
Advanced Oracle PL/SQL Developer's Guide Second Edition
Advanced Oracle PL/SQL Developer's Guide Second Edition
Copyright © 2016 Packt Publishing
All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.
Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the author, nor Packt Publishing and its dealers and distributors will be held liable for any damages caused or alleged to be caused directly or indirectly by this book.
Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.
First published: May 2012
Second edition: February 2016
Production reference: 1080216
Published by Packt Publishing Ltd.
Livery Place
35 Livery Street
Birmingham B3 2PB, UK.
ISBN 978-1-78528-480-9
www.packtpub.com
Credits
Author
Saurabh K. Gupta
Reviewers
Kamran Aghayev A
Patrick Barel
Nassyam Basha
Ramakrishna Kandula
Wissem EL Khlifi
Sean Stacey
Davor Zelic
Commissioning Editor
Priya Singh
Acquisition Editor
Tushar Gupta
Content Development Editor
Arwa Manasawala
Technical Editor
Rohan Uttam Gosavi
Copy Editor
Stephen Copestake
Project Coordinator
Shweta H Birwatkar
Proofreader
Safis Editing
Indexer
Monica Ajmera Mehta
Graphics
Abhinash Sahu
Production Coordinator
Nilesh R. Mohite
Cover Work
Nilesh R. Mohite
About the Author
Saurabh K. Gupta is a seasoned database technologist with extensive experience in designing high performance and highly available database applications. His technology focus has been centered around Oracle Database architecture, Oracle Cloud platform, Database In-Memory, Database Consolidation, Multitenant, Exadata, Big Data, and Hadoop. He has authored the first edition of this book. He is an active speaker at technical conferences from Oracle Technology Network, IOUG Collaborate'15, AIOUG Sangam, and Tech Days. Connect with him on his twitter handle (or SAURABHKG) or through his technical blog www.sbhoracle.wordpress.com, with comments, suggestions, and feedback regarding this book.
About the Reviewers
Patrick Barel is a PL/SQL developer for AMIS Services (http://www.amis.nl/) in the Netherlands. Besides working with SQL and PL/SQL, he co-developed CodeGen together with Steven Feuerstein, and has written different plugins (http://bar-solutions.com/) for PL/SQL developer at (http://www.allroundautomations.com/). He publishes articles on AMIS Technology Blog (http://technology.amis.nl/blog) and on his own blog (http://blog.bar-solutions.com).
He has been a reviewer for several books including Oracle PL/SQL Programming by Steven Feuerstein. He has been an Oracle ACE since 2011.
Nassyam Basha is a database administrator and an Oracle ACE Director. He holds a master's degree in Computer Applications from the University of Madras. He is an Oracle 11g Certified Master and Exadata implementation specialist, and has good knowledge of Oracle technologies, such as Data Guard, RMAN, RAC, and Exadata. He actively participates in Oracle-related forums, such as OTN, where he has superhero status. He maintains an Oracle-technology-related blog (www.oracle-ckpt.com) and has coauthored Oracle Data Guard 11gR2 administration beginners guide, Packt Publishing. He actively writes many articles on OTN in various languages. He is a speaker at OTN, IOUG, and SANGAM, and he is the co-founder of Oraworld-team (www.oraworld-team.com). He is part of the AIOUG community on Twitter, where he occasionally expresses his views via the Twitter handle @AIOUG. He is currently working with Pythian as an Oracle database consultant.
Nassyam Basha has written Oracle Data Guard 11gR2 Beginner's Guide, Packt Publishing.
I want to thank the almighty Allah and my parents, Abdul Aleem and Rahimunnisa, for their support and blessings all the time—without them, nothing is possible. Special thanks to my wife and 9-month-old daughter Yashfeen Fathima, who've shared a lot of fun and crazy things with me while I worked on this book, and, as always, I would also like to thank my brother, Nawaz, and my cousins, for their great support. Finally, thanks to Saurabh Gupta for referring me as a technical reviewer, which was not an easy task for me, as this is my first assignment as a reviewer. He did a great job on this book.
Wissem El Khlifi is the first Oracle ACE in Spain and an Oracle Certified Professional DBA with over 12 years of IT experience.
He earned his Computer Science engineering degree from FST Tunisia, his master's degree in Computer Science from the UPC, Barcelona, and another master's degree in Big Data Science from the UPC, Barcelona.
His areas of interest are Linux System Administration, Oracle ERP and Databases (RAC and Dataguard), big data NoSQL database management, and big data analysis.
His career has included the roles of Oracle and Java analyst/programmer, Oracle DBA, architect, team leader, and big data scientist. He currently works as senior database and application engineer for Schneider Electric/APC.
He writes numerous articles on his website, http://www.oracle-class.com, and you can contact him via Twitter at @orawiss.
Davor Zelic is an IT professional with more than 15 years of experience in designing, developing, and implementing IT systems.
After getting his master's degree in Electrical Engineering, he began his professional career working with Oracle technology in the Croatian IT company TEB Informatika. For more than 10 years, Davor worked on IT projects related to road management, where he gained extensive experience working as an Oracle SQL, PLSQL, Forms, and Reports and Spatial developer. He has proved his knowledge by becoming an Oracle Certified Professional issued by the Oracle Corporation.
Apart from Oracle technology, Davor has gained expertise in design and development of geographic information systems for collection, storage, transformation, analysis, and visualization of geo-referenced data. He originally worked with Intergraph technology, but later his focus moved to open source GIS technologies, such as Geoserver and OpenLayers.
Davor currently works as a software architect at the IT department of Croatian Central Bank, designing software solutions for Croatian financial market data collection and analysis.
I want to thank my parents for the support that they gave me in choosing my educational path, which allowed me to find a job that is not just a routine, but also a source of satisfaction and constant challenge.
www.PacktPub.com
Support files, eBooks, discount offers, and more
For support files and downloads related to your book, please visit www.PacktPub.com.
Did you know that Packt offers eBook versions of every book published, with PDF and ePub files available? You can upgrade to the eBook version at www.PacktPub.com, and as a print book customer, you are entitled to a discount on the eBook copy. Get in touch with us at <[email protected]> for more details.
At www.PacktPub.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 Packt books and eBooks.
https://www2.packtpub.com/books/subscription/packtlib
Do you need instant solutions to your IT questions? PacktLib is Packt's online digital book library. Here, you can search, access, and read Packt's entire library of books.
Why subscribe?
Free access for Packt account holders
If you have an account with Packt at www.PacktPub.com, you can use this to access PacktLib today and view 9 entirely free books. Simply use your login credentials for immediate access.
Instant updates on new Packt books
Get notified! Find out when new books are published by following @PacktEnterprise on Twitter or the Packt Enterprise Facebook page.
Preface
How many of us would believe that PL/SQL was introduced as a scripting language for executing a bunch of SQL scripts? Well, that's true. With the growing need to build computational logic and condition-based constructs, and to manage exception rules within databases, Oracle Corporation first released PL/SQL along with Oracle Database Version 6.0 with a limited set of capabilities. Within its capacity, PL/SQL was capable of creating program units that could not be stored inside the database. Eventually, Oracle's release in the application line, SQL *Forms version V3.0, included the PL/SQL engine and allowed developers to implement the application logic through procedures. Back then, PL/SQL used to be part of the transaction processing option in Oracle 6 and the procedural option in Oracle 7. Since the time of its ingenuous beginning, PL/SQL has matured immensely as a standard feature of Oracle Database. It has been enthusiastically received by the developer community, and the credit goes to its support for advanced elements such as modular programming, encapsulation, support for objects and collections, program overloading, native and dynamic SQL, and exception handling.
PL/SQL is loosely derived from Ada (named after Ada Lovelace, an English mathematician who is regarded as the first computer programmer), a high-level programming language, which complies with the advanced programming elements. Building a database backend for an application demands the ability to design the database architecture, skills to code complex business logics, and expertise in administering and protecting the database environment. One of the principal reasons why PL/SQL is a key enabler in the development phase is its tight integration with Oracle's SQL language. In addition to this, it provides a rich platform for implementing the business logic in the Oracle Database layer and store them as procedures or functions for subsequent use. As a procedural language, PL/SQL provides a diverse range of datatypes, iterative and control constructs, conditional statements, and exception handlers.
In a standard software development space, an Oracle database developer is expected to get involved in schema design; code business logics on the server side by using functions, procedures, or packages; implement action rules by using triggers; and support client-side programs in setting up the application interface. While building the server-side code, developers should understand that their code contributes to the application's performance and scalability. Language basics are expected to be resilient, but while building robust and secure applications using PL/SQL, developers must take advantage of best practices and try to use advanced language features. This book focuses on the advanced features of PL/SQL validated up to the latest Oracle Database 12c.
Learning by example has always been a well-attested approach for diving deep into a concept. This book will enable you to master the latest enhancements and new features of Oracle Database 12c. For efficient reading, you just have to be familiar with the PL/SQL fundamentals so that you can relate to the evolution of an advanced feature from its ever-expanding roots.
This book closely follows the outline of the Oracle University certification; that is, the Oracle Certified Advanced PL/SQL Developer Professional (1Z0-146) exam. One of the most sought after certifications in the developer community, the 1Z0-146 certification's objectives are quite comprehensive, and touch upon the various progressive areas of PL/SQL. To name a few, PL/SQL code performance, maintenance, bulk processing techniques, PL/SQL collections, security implementation, and the handling of large objects. For certification aspirants, this book will serve as a one-stop exam guide. At many stages, this book goes beyond the certification objectives and attempts to build a deep understanding of the concepts. Therefore, mid-level database developers will find this book a handy language reference and would be keen to have it on their bookshelves.
My last work on the same subject will remain close to my heart, but this one is straight from my experience. I hope that this book will help you improve your PL/SQL development skills and gain confidence in using advanced features, along with meticulous familiarization of Oracle Database 12c.
"The only real security that a man can have in the world is a reserve of knowledge, experience and ability"
--Henry FordWhat this book covers
Chapter 1, Overview of PL/SQL Programming Concepts, provides an overview of PL/SQL fundamentals. It refreshes the basic concepts, such as PL/SQL language features, the anonymous block structure, exception handling, and stored subprograms.
Chapter 2, Oracle 12c SQL and PL/SQL New Features, talks about the new features of Oracle Database 12c. It starts with the idea of consolidation of databases on a cloud and how the Oracle 12c Multitenant architecture addresses the requirements. It consolidates the new features in Oracle 12c SQL and PL/SQL, and explains each of them with examples. It will help you to feel the essence of Oracle Database 12c and understand what the driving wheel of innovation is. A section on the Oracle Database 12c In-memory option will familiarize you with the breakthrough feature in the analytics and warehouse space.
Chapter 3, Designing PL/SQL Code, primarily focuses on the PL/SQL cursor's design and handling. You will get to learn the basics of cursor design, cursor types and cursor variables, handling cursors in PL/SQL, and design guidelines. This chapter will also include the enhancements made by Oracle Database 12c with respect to cursors.
Chapter 4, Using Collections, introduces you to the world of collections; namely, associative arrays, nested tables, and varrays. Taking you all the way from their creation in SQL and PL/SQL to design considerations, this chapter makes you wise enough to choose the right collection type in a given situation. A section on Oracle Database 12c enhancements to collections introduces a very handy feature that will allow you to join a table and collection.
Chapter 5, Using Advanced Interface Methods, focuses on a powerful feature of PL/SQL: how to execute external procedures in PL/SQL. You will learn and understand the specifics of executing a C or Java program in PL/SQL as an external procedure through step-by-step demonstration. This chapter also mentions the Oracle Database 12c enhancement which allows you to secure external procedures through an additional safety net.
Chapter 6, Virtual Private Database, provides a detailed overview of the Oracle Database Security Defense-in-depth architecture and focuses on one of the developer-centric features, known as the Virtual Private Database. Oracle Database 12c security enhancements and a demonstration of data redaction will make you understand Oracle's security offerings.
Chapter 7, Oracle SecureFiles, provides a thorough understanding of handling large objects in Oracle and focuses on storage optimizations made by SecureFiles. Introduced in Oracle 11g, SecureFiles is the new storage mechanism that scores high on its advanced features, such as compression, encryption, and deduplication. This chapter also helps you with the recommended migration methods from older LOBs to SecureFiles.
Chapter 8, Tuning the PL/SQL Code, introduces the best practices for tuning PL/SQL code. It starts with the PL/SQL optimizer and rolls through the benefits of native compilation, PL/SQL code writing skills, and code evaluation design. This chapter includes the changes in Oracle 12c with respect to large object handling.
Chapter 9, Result Cache, explains the result caching feature in Oracle Database. It is a powerful caching mechanism that enhances the performance of SQL queries and PL/SQL functions that are repeatedly executed on the server. This chapter also discusses the enhancements made to the feature in Oracle Database 12c.
Chapter 10, Analyzing, Profiling, and Tracing PL/SQL Code, details the techniques used to analyze, profile, and trace PL/SQL code. If you are troubleshooting PL/SQL code for performance, you must learn the profiling and tracing techniques. In an enterprise application environment, these practices are vital weapons in a developer's arsenal.
Chapter 11, Safeguarding PL/SQL Code against SQL injection, describes ways to protect your PL/SQL from being attacked. A vulnerable piece of code is prone to malicious attacks and runs the risk of giving away sensitive information. Efficient code writing and proofing the code from external attacks can help to minimizing the attack surface area. In this chapter, you will learn the practices for safeguarding your code against external threats.
Chapter 12, Working with Oracle SQL Developer, describes the benefits of the Oracle SQL Developer for developers, database administrators, and architects. This chapter not only helps you get started with SQL Developer, but also helps you gain a better understanding of the new features of SQL Developer 4.0 and 4.1.
What you need for this book
If you are good with PL/SQL development basics, I'm sure you will enjoy reading this book. You will learn new ways to program efficiently in PL/SQL.
Who this book is for
This book is for Oracle developers who are responsible for database management. Readers are expected to have basic knowledge of the Oracle Database and the fundamentals of PL/SQL programming. Certification aspirants can use this book to prepare for the 1Z0-146 examination in order to be come an Oracle Certified Professional in Advanced PL/SQL.
Conventions
In this book, you will find a number of styles of text that distinguish between different kinds of information. Here are some examples of these styles, and an explanation of their meaning.
Code words in text, database table names, folder names, filenames, file extensions, pathnames, dummy URLs, user input, and Twitter handles are shown as follows: "The modified SELECT query is then executed in the HR schema of the database."
A block of code is set as follows:
New terms and important words are shown in bold. Words that you see on the screen, in menus or dialog boxes for example, appear in the text like this: "This is how interpreted compilation works. In the case of native compilation,, a sharable dynamic linked library (DLL) is generated instead of a machine code."
Note
Warnings or important notes appear in a box like this.
Tip
Tips and tricks appear like this.
Reader feedback
Feedback from our readers is always welcome. Let us know what you think about this book—what you liked or may have disliked. Reader feedback is important for us to develop titles that you really get the most out of.
To send us general feedback, simply send an e-mail to <[email protected]>, and mention the book title via the subject of your message.
If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, see our author guide on www.packtpub.com/authors.
Customer support
Now that you are the proud owner of a Packt book, we have a number of things to help you to get the most from your purchase.
Downloading the example code
You can download the example code files for all Packt books you have purchased from your account at http://www.packtpub.com. If you purchased this book elsewhere, you can visit http://www.packtpub.com/support and register to have the files e-mailed directly to you.
Errata
Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you find a mistake in one of our books—maybe a mistake in the text or the code—we would be grateful if you would report this to us. By doing so, you can save other readers from frustration and help us improve subsequent versions of this book. If you find any errata, please report them by visiting http://www.packtpub.com/submit-errata, selecting your book, clicking on the erratasubmissionform link, and entering the details of your errata. Once your errata are verified, your submission will be accepted and the errata will be uploaded on our website, or added to any list of existing errata, under the Errata section of that title. Any existing errata can be viewed by selecting your title from http://www.packtpub.com/support.
Piracy
Piracy of copyright material on the Internet is an ongoing problem across all media. At Packt, we take the protection of our copyright and licenses very seriously. If you come across any illegal copies of our works, in any form, on the Internet, please provide us with the location address or website name immediately so that we can pursue a remedy.
Please contact us at <[email protected]> with a link to the suspected pirated material.
We appreciate your help in protecting our authors, and our ability to bring you valuable content.
Questions
You can contact us at <[email protected]> if you are having a problem with any aspect of the book, and we will do our best to address it.
Chapter 1. Overview of PL/SQL Programming Concepts
Structured Query Language (SQL) is a language that has been widely accepted and adopted for accessing relational databases. This language allows users to perform database operations such as reading, creating, modifying, and deleting the data. Since the summer of 1970, when Dr. E.F. Codd published the paper A Relational Model of Data for Large Shared Data Banks for the ACM journal, the language has matured comprehensively as an industry standard. With its broad range of features and easy adaptation to enterprise environments, the SQL language has been typically regarded as the most reliable language for interacting with relational databases.
PL/SQL was developed in 1991 by Oracle Corporation as a procedural language extension to SQL. Its ability to integrate seamlessly with SQL makes it a powerful language to construct the data access layer and the rich procedural extensions help in translating business logic within the Oracle Database. This first chapter introduces you to the PL/SQL language and refreshes some of the key programming concepts. The chapter is outlined as follows:
Introduction to PL/SQL
PL/SQL stands for Procedural Language-Structured Query Language(PL/SQL). It is part of the Oracle Database product, which means no separate installation is required. It is commonly used to translate business logic in the database and expose the program interface layer to the application. While SQL is purely a data access language that directly interacts with the database, PL/SQL is a programming language in which multiple SQLs and procedural statements can be grouped in a program unit. PL/SQL code is portable between Oracle Databases (subject to limitations imposed by versions). The built-in database optimizer refactors the code to improve the execution performance.
The advantages of PL/SQL as a language are as follows:
Although it is not used to build user interfaces, it provides the opportunity to build robust, secure, and portable interface layers, which can be exposed to a high-level programming language. Some of the key faculties of PL/SQL (PL/SQL accomplishments) are listed here:
PL/SQL program fundamentals
A well-written PL/SQL program should be able to answer the following fundamental questions:
Well, there are multiple tips and techniques to standardize PL/SQL coding practices. But before we drill down to the programming skills, let us familiarize ourselves with the structure of a PL/SQL program. A PL/SQL program can be broken down into four sections. Each section carries a specific objective and must exist in the same sequence in a program. Let us have a brief look at the sections:
The following block diagram shows the structure of a PL/SQL block:
A PL/SQL block is the elementary unit of a program that groups a set of procedural statements. Based on the sections included in a PL/SQL program unit, we can classify a program under following categories:
So, let's get started with our first anonymous PL/SQL block. The block declares a string and displays it on screen. Note that each line in the program ends with a semi-colon and the block ends with a slash (/) for code execution.
Note
The SERVEROUTPUT parameter is a SQL*Plus variable that enables the printing of DBMS_OUTPUT messages from a PL/SQL block.
Cursors – an overview
Writing SQL in PL/SQL is one of the critical parts of database programming. All SQL statements embedded within a PL/SQL block are executed as a cursor. A cursor is a private memory area, temporarily allocated in the session's User Global Area (UGA), that is used for processing SQL statements. The private memory stores the result set retrieved from the SQL execution and cursor attributes. Cursors can be classified as implicit and explicit cursors.
Oracle creates an implicit cursor for all the SQL statements included in the executable section of a PL/SQL block. In this case, the cursor lifecycle is maintained by the Oracle Database.
For explicit cursors, the execution cycle can be controlled by the user. Database developers can explicitly declare an implicit cursor under the DECLARE section along with a SELECT query.
The cursor execution cycle
A cursor moves through the following stages during execution. Note that, in the case of an implicit cursor, all the steps are carried out by the Oracle Database. Let's take a quick look at the execution stages OPEN, FETCH, and CLOSE.
Cursor attributes
Cursor attributes hold the information about the cursor processing at each stage of its execution:
Note
%ISOPEN is the only cursor attribute that is accessible outside the cursor execution cycle.
The following program uses the cursor attributes %ISOPEN, %NOTFOUND, and %ROWCOUNT to fetch the data from the EMP table and display it:
Cursor FOR loop
Looping through all the records of a cursor object can be facilitated with the use of the FOR loop. A FOR loop opening a cursor directly is known as a CURSORFOR loop. The usage of the CURSORFOR loop reduces the overhead of manually specifying the OPEN, FETCH, and CLOSE stages of a cursor.
The CURSORFOR loop will best compact the code when working with multi-row explicit cursors. The following PL/SQL block demonstrates the purpose:
Note that, with the CURSORFOR loop, you do not need to declare the block variables to capture the cursor columns. The CURSORFOR loop index implicitly acts as a record of the cursor type. Also, you do not need to explicitly open or close the cursor in the PL/SQL program.
Creating stored procedures
A procedure is a derivative of a PL/SQL block that has a name and is stored persistently within the database. It is the schema object that is primarily used to implement business logic on the server side. A procedure promotes a modular programming technique by breaking down complex logic into simple routines.
The key features of stored procedures are:
The following table differentiates between the IN, OUT, and INOUT parameters:
IN
OUT
IN OUT
Default parameter mode
Has to be explicitly defined
Has to be explicitly defined
Parameter's value is passed to the program from the calling environment
Parameter returns a value back to the calling environment
Parameter may pass a value from the calling environment to the program or return value to the calling environment
Parameters are passed by reference
Parameters are passed by value
Parameters are passed by value
May be a constant, literal, or initialized variable
Uninitialized variable
Initialized variable
Can hold default value
Default value cannot be assigned
Default value cannot be assigned
The syntax for a procedure is as follows:
The following standalone procedure converts the case of the input string from lower case to upper case:
Executing a procedure
A procedure can either be executed from SQL*Plus or a PL/SQL block. The P_TO_UPPER procedure can be executed from SQL*Plus.
The following code shows the execution of the procedure from SQL*Plus (note that the parameter is passed using bind variable):
The P_TO_UPPER procedure can be called as a procedural statement within an anonymous PL/SQL block:
Functions
Similar to a stored procedure, a function is a named derivative of a PL/SQL block that is physically stored within the Oracle database schema.
The key features of stored functions are as follows:
Note
Starting from Oracle Database 12c, PL/SQL—only data types can cross the PL/SQL to SQL interface. A PL/SQL anonymous block can invoke a PL/SQL subprogram with parameters of BOOLEAN or a packaged collection type.
The syntax for a function is as follows:
Let us create a standalone function, F_GET_DOUBLE, which accepts a numeric parameter and returns its double:
Functions – execution methods
Functions can either be called from a SQL*Plus environment or invoked from a PL/SQL program as a procedural statement.
The function F_GET_DOUBLE can be executed in the SQL* Plus command prompt as follows. As the function returns an output, you must declare a session variable and capture the function result in the variable.
