Oracle Database 11g Pl Sql Fundamentals Pdf

[Mauricette Atencio]

OraclePL/SQL celebrates its 22nd birthday in 2011. I know this because I am looking at the first Oracle PL/SQL user guide ever published; it is for PL/SQL Release 1.0, and its date of publication is September 1989. I was working for Oracle at that time, building the first sales automation tools ever used by the Oracle USA. sales force. I had already worked with PL/SQL inside SQL Forms 3.0, but with the release of Oracle 6 Database, PL/SQL was available as a free-standing application development language.

Best of all, there continues to be a steady influx of new PL/SQL developers. In fact, with the relatively recent emergence of India, China, and other nations as technology powerhouses, I have seen a whole new generation of developers discover and work to master PL/SQL.

To help newcomers to PL/SQL make the most of this language, Oracle Magazine has asked me to write a series of articles for PL/SQL beginners, of which this is the first. If you are an experienced PL/SQL developer, you may also find these articles a handy refresher on PL/SQL fundamentals.

I will assume for this series that although my readers are new to PL/SQL, they have had some programming experience and are familiar with SQL. My approach throughout, in addition, will be on getting developers productive in PL/SQL as quickly as possible.

To answer this question, it is important to remember that every Website you visit, every application you run is constructed from a stack of software technologies. At the top of the stack is the presentation layer, the screens or interactive devices with which the user directly interacts. (These days the most popular languages for implementing presentation layers are Java and .NET.) At the very bottom of the stack is the machine code that communicates with the hardware.

Somewhere in the middle of the technology stack you will find the database, software that enables us to store and manipulate large volumes of complex data. Relational database technology, built around SQL, is the dominant database technology in the world today.

SQL is a very powerful, set-oriented language whose sole purpose is to manipulate the contents of relational databases. If you write applications built on Oracle Database, you (or someone writing code at a lower level in the technology stack) must be executing SQL statements to retrieve data from or change data in that database. Yet SQL cannot be used to implement all business logic and end-user functionality needed in our applications. That brings us to PL/SQL.

PL/SQL stands for Procedural Language/Structured Query Language. PL/SQL offers a set of procedural commands (IF statements, loops, assignments), organized within blocks (explained below), that complement and extend the reach of SQL.

It is certainly possible to build applications on top of SQL and Oracle Database without using PL/SQL. Utilizing PL/SQL to perform database-specific operations, most notably SQL statement execution, offers several advantages, though, including tight integration with SQL, improved performance through reduced network traffic, and portability (PL/SQL programs can run on any Oracle Database instance). Thus, the front-end code of many applications executes both SQL statements and PL/SQL blocks, to maximize performance while improving the maintainability of those applications.

The first thing I do after connecting to the database through SQL*Plus is turn on server output, so that calls to DBMS_OUTPUT.PUT_LINE will result in the display of text on my screen. I then type in the code that constitutes my block. Finally I enter a slash (/) to tell SQL*Plus to execute this block.

Each tool offers slightly different windows and steps for creating, saving, and running PL/SQL blocks as well as enabling and disabling server output. In this article series, I will assume only that you have access to SQL*Plus and that you will run all my statements in a SQL*Plus command window.

I have now, in effect, extended PL/SQL. In addition to calling programs created by Oracle and installed in the database (such as DBMS_OUTPUT.PUT_LINE), I can call my own subprogram inside a PL/SQL block:

I have hidden all the details of how I say hello to the world inside the body, or implementation, of my procedure. I can now call this hello_world procedure and have it display the desired message without having to write the call to DBMS_OUTPUT .PUT_LINE or figure out the correct way to format the string. I can call this procedure from any location in my application. So if I ever need to change that string, I will do so in one place, the single point of definition of that string.

The hello_world procedure is very simple. Your procedures will have lots more code inside them, and they will almost always also have parameters. Parameters pass information into subprograms when they are called, and they enable you to create subprograms that are more flexible and generic. They can be used in many different contexts.

With the code needed to construct the message inside the hello_message function, I can use this message in multiple ways. I can, for example, call the function to retrieve the message and assign it to a variable:

Note that I call the hello_message function as part of a PL/SQL statement (in this case, an assignment of a string to a variable). The hello_message function returns a string, so it can be used in place of a string in any executable statement.

Now that you can see the importance of assigning names to logic, it is time to talk about the rules for names (or, to be more precise, identifiers) in both PL/SQL and, more generally, Oracle Database.

To offer you increased flexibility, Oracle Database lets you bypass the restrictions of the second and third rules by enclosing your identifier within double quotes. A quoted identifier can contain any sequence of printable characters excluding double quotes; differences in case will also be preserved. So all of the following strings are valid and distinct identifiers:

These same rules apply to the names of database objects such as tables, views, and procedures, with one additional rule: unless you put double quotation marks around the names of those database objects, Oracle Database will store them as uppercase.

Directly inside the DELETE statement, I reference the PL/SQL variable. When the block is executed, the variable name is replaced with the actual value, 10, and the DELETE is run by the SQL engine. SQL%ROWCOUNT is a special cursor attribute that returns the number of rows modified by the most recently executed DML statement in my session.

In this article, you learned about how PL/SQL fits into the wider world of Oracle Database. You also learned how to define blocks of code that will execute PL/SQL statements and to name those blocks so that your application code can be more easily used and maintained. Finally, you were introduced to the execution of SQL statements inside PL/SQL.

You can put BEGIN before any set of one or more executable statements and follow it with END, creating a nested block for those statements. There are two key advantages of doing this: (1) defer allocation of memory for variables needed only within that nested block, and (2) constrain the propagation of an exception raised by one of the statements in the nested block.

Next in this article series, I will show you how to control the flow of execution in your block: conditional logic with IF and CASE; iterative logic with FOR, WHILE, and simple loops; and raising and handling exceptions.

Steven Feuerstein is Oracle Corporation's Developer Advocate for PL/SQL, and an expert on the Oracle PL/SQL language, having written ten books on PL/SQL, including Oracle PL/SQL Programming and Oracle PL/SQL Best Practices (all published by O'Reilly Media). Steven has been developing software since 1980, spent five years with Oracle back in the "old days" (1987-1992), and was PL/SQL Evangelist for Quest Software (and then Dell) from January 2001 to February 2014 - at which point he returned joyfully to Oracle Corporation. He was one of the original Oracle ACE Directors and writes regularly for Oracle Magazine, which named him the PL/SQL Developer of the Year in both 2002 and 2006. He is also the first recipient of ODTUG's Lifetime Achievement Award (2009).

DISCLAIMER: We've captured these popular historical magazine articles for your reference. Links etc contained within these article possibly won't work. These articles are provided as-is with no guarantee that the information within them is the best advice for current versions of the Oracle Database.

Get coding quickly and effectively with Oracle PL/SQL, Oracle's database development language. This course is the first of a two part series, covering the essentials and fundamentals of Oracle PL/SQL.

In this Oracle PL/SQL training, you will learn about the fundamentals of Oracle PL/SQL programming language.

You will gain a solid understanding of PL/SQL datatypes and programming constructs like loops, conditional execution, cursors and exception handling, which will enable you to do effective debugging.

PL/SQL stands for "Procedural Language extensions to the Structured Query Language". It is Oracle's database development language. It is a procedural language and an extension of SQL which tells the compiler what to do and how to do it.

The main difference is that SQL executes a single query at a time, while PL/SQL executes an entire block of code at once. PL/SQL is procedural, defining how things need to be done, while SQL is declarative, defining what needs to be done.

This is a beginner course, so you don't need to know much to get started. Because PL/SQL is an extension to SQL, knowing SQL basics would help. Some general programming knowledge would also be helpful, but is not required. If you need a refresher on the Oracle database platform in general then check out this Oracle Database fundamentals course.

Pankaj Jain is an experienced technologist, with expertise in various aspects of software development lifecycle, architecting software solutions and softwaredevelopment. He has worked on an array of technologies, with focus on Oracle at the database layer and Java at the web tier. He is a certified Oracle 11g DBA.He lives in Seattle with his wife Simita and boys Ronit and Rohak. When he is not playing with technology, he likes spending time with his kids, travellingand doing photography.

3a8082e126