Oracle Database 11g Express Edition – PL/SQL and Database Administration Concepts
Slide 1:
Hello and welcome to this online, self-paced course titled Oracle Database 11g Express Edition – PL/SQL and Database Administration Concepts. My name is Anupama Mandya, and I am part of Oracle Corporation’s Curriculum Development team. This course is part of Oracle Database 11g Express Edition New Features Self-Study series.
In this course, using demonstrations, I will be discussing some basic PL/SQL and Database Administration Concepts using Oracle SQL Command Line and Oracle SQL Developer tools. This course is presented in two parts.
Slide 2: Using the Player
Using The Player Script
Before we begin, take a look at some of the features of this Flash-based course player. If you’ve attended similar Oracle eStudy courses in the past then, feel free to skip this slide.
To your left, you will find a hierarchical course outline. This course enables and even encourages you to go at your own pace, which means you are free to skip over topics you already feel confident on, or jump right to a feature that really interests you, or go back and review topics that were already covered. Simply click on a course section to expand its contents and then select an individual slide. However, note that by default we will automatically walk you through the entire course without requiring you to use the outline.
To the right of the course outline tab is the transcripts tab, which contains any additional reference notes for the current slide. Feel free to read these reference notes at the conclusion of the course. Or if you prefer you can read them as we go along.
The third tab in the left panel is where you can search for content in this course.
Standard Flash player controls are also found at the bottom of the player, including pause, previous, and next buttons. There is also an interactive progress bar to fast forward or rewind the current slide. Interactive slides may have additional controls and buttons along with instructions on how to use them.
To the right of the standard Flash player controls is the Full Screen control. Click on it once to hide the course ouline panel, and click it again to maximize the course display. This may be useful when viewing videos and demos embedded in the course.
The course will now pause, so feel free to take some time and explore the interface. Then when you’re ready to continue, click the next button below or alternatively click the next slide in the course outline on the left.
Slide 3: About This Course
So, you know the title of the course, but you may be asking yourself “Am I in the right place?” To help you answer these questions you can answer information here regarding the course objectives, target audience and the prerequisites. When finished, click the “Next Topic” button.
Slide 4: Road Map
The first topic in this course is Getting familiar with the commonly used terms in PL/SQL
Slide5: FAQ
Introduction:
In this topic, we discuss what PL/SQL is and the commonly used terms in PL/SQL. Click on each of the tabs to uncover the answers to some of the commonly asked questions.
What is PL/SQL?
Procedural Language/SQL (PL/SQL) (pronounced as P L sequel) is a native Oracle Database Express Edition extension to SQL. It bridges the gap between declarative and imperative program control by adding procedural elements, such as conditional control and loops.
In PL/SQL, you can declare constants and variables, procedures and functions, types and variables of those types, and triggers. You can handle exceptions (run-time errors). You can create PL/SQL units like-procedures, functions, packages, types, and triggers-that are stored in the database for reuse by applications that use any of the Oracle Database XE programmatic interfaces.
The language offers a robust programming environment that enables you to program procedurally and/or with object-oriented programming techniques such as encapsulation, information hiding, and function overloading.
With PL/SQL, you can use the state-of-the-art programming for the Oracle Database server and toolset.
Why use PL/SQL?
The following are the major benefits of using PL/SQL :
Integration of procedural constructs with SQL
PL/SQL integrates control statements and conditional statements with SQL. This gives you a better control of SQL statements and their execution.
Reduction in network congestion
PL/SQL allows you to logically combine SQL statements as one unit. The application can send the entire block to the database instead of sending the SQL statements one at a time. This reduces the network traffic.
Modularized program development
PL/SQL allows you to group logically related statements within blocks. You can nest blocks inside larger blocks to build powerful program. Also, you can break your application into smaller modules.
Integration with tools
The PL/SQL engine is integrated in Oracle tools such as HTML Database, Oracle Database XE, Oracle Forms, Oracle Reports and so on. When you use these tools, the locally available PL/SQL engine processes the procedural statements and only the SQL statements are passed to the database.
What is a Sub Program?
A subprogram is a PL/SQL unit that consists of SQL and PL/SQL statements that solve a specific problem or perform a set of related tasks.
A subprogram can have parameters, whose values are supplied by the invoker. A subprogram can be either a procedure or a function.
Typically, you use a procedure to perform an action and a function to compute and return a value.
A subprogram follows PL/SQL block structure; that is, it has:
■ Declarative part (optional)
The declarative part contains declarations of types, constants, variables, exceptions, explicit cursors, and nested subprograms. These items are local to the subprogram and cease to exist when the subprogram completes execution.
■ Executable part (required)
The executable part contains statements that assign values, control execution, and manipulate data.
■ Exception-handling part (optional)
The exception-handling part contains code that handles exceptions (run-time errors).
What are the different Block Types?
There are three types of blocks that make up a PL/SQL program:
Anonymous blocks: These are the unnamed PL/SQL blocks that are embedded within an application or are issued interactively.
Procedures: These are the named PL/SQL blocks. These blocks accept inbound parameters but won't explicitly return any value.
Functions: These are the named PL/SQL blocks. These blocks accept inbound parameters and will always return a value. The structure of a function is like that of a procedure, except that it includes a RETURN clause and at least one RETURN statement.
Typically, you use a procedure to perform an action and a function to compute and return a value.
What is a Stored SubProgram?
A stored subprogram is a subprogram that is stored in the database. Because they are stored in the database, stored programs can be used as building blocks for many different database applications. (A subprogram that is declared within another subprogram, or within an anonymous block, is called a nested subprogram or local subprogram. It cannot be invoked from outside the subprogram or block in which it is declared. An anonymous block is a block that is not stored in the database.)
There are two kinds of stored subprograms:
Standalone stored subprogram, which is created at schema level
Package subprogram, which is created inside a package
Standalone stored subprograms are useful for testing pieces of program logic, but when you are sure that they work as intended, Oracle recommends that you put them into packages.
What are Packages?
A package is a PL/SQL unit that consists of related subprograms and the explicit cursors and variables that they use.
Oracle recommends that you put your subprograms into packages. Some of the reasons are:
Packages allow you to hide implementation details from client programs.
Package subprograms must be qualified with package names when invoked, which ensures that their names will always work.
Package subprograms can send and receive records and collections.
What is a Collection?
A collection is a PL/SQL composite variable that stores elements of the same type in a specified order, similar to a one-dimensional array. The internal components of a collection are called elements. Each element has a unique subscript that identifies its position in the collection.
To access a collection element, you use subscript notation:
collection_name(element_subscript).
You can treat collection elements like scalar variables. You can also pass entire collections as subprogram parameters (if neither the sending nor receiving subprogram is a standalone stored subprogram).
A collection method is a built-in PL/SQL subprogram that either returns information about a collection or operates on a collection. To invoke a collection method, you use dot notation: collection_name.method_name.
For example, collection_name.COUNT returns the number of elements in the collection.
PL/SQL has three types of collections:
■ Associative arrays (formerly called "PL/SQL tables" or "index-by tables")
■ Nested tables
■ Variable arrays (varrays)
What is a Trigger?
While working with a database application, you may want to add programmatic functionality that executes when specific operations occur in the database. For example, there may be typical scenarios such as the following:
Example 1: You may want to insert data into a table. You find that the new data is inconsistent with the existing data in the table. You may want the system to raise an error that will cause the transaction to be rolled back.
Another example is: You may want the system to record information such as the timing and the details of the user modifying data in a table.
In such scenarios, Triggers help the user deal with these and other complex situations. A trigger is a PL/SQL unit that is stored in the database and (if it is in the enabled state) automatically executes ("fires") in response to a specified event.
What are the uses of a Trigger?
One use of triggers is to enforce business rules that apply to all client applications. For example, suppose that data added to the EMPLOYEES table must have a certain format, and that many client applications can add data to this table. A trigger on the table can ensure the proper format of all data added to it. Because the trigger executes whenever any client adds data to the table, no client can circumvent the rules, and the code that enforces the rules can be stored and maintained only in the trigger, rather than in every client application.
You can use triggers for:
• Improving data security i.e. Triggers provide enhanced and complex security checks and auditing.
• Improving data integrity where Triggers enforce dynamic data integrity and complex referential integrity constraints.
Triggers ensure that related operations are performed together implicitly
Slide 6: Moving on, the next topic in this course is about Creating Standalone Stored Sub Programs and Packages.
Slide 7: Overview- Standalone Stored Sub Programs and Packages
Introduction: A PL/SQL program comprises one or more blocks. These blocks can be entirely separate or nested within another.
In this topic, we discuss what Standalone SubPrograms and Packages are.
Overview: A subprogram is a PL/SQL unit that consists of SQL and PL/SQL statements that solve a specific problem or perform a set of related tasks. A stored subprogram is a subprogram that is stored in the database. Because they are stored in the database, stored programs can be used as building blocks for many different database applications.
Anonymous Blocks: are the unnamed PL/SQL blocks that are embedded within an application or are issued interactively.
Procedures: The structure of a Procedure follows a PL/SQL block structure and these blocks accept inbound parameters but do not explicitly return any value.
Functions: Functions are a kind of Stored Subprogram whose structure is like that of a procedure, except that it includes a RETURN clause and at least one RETURN statement.
Packages: A Package is an encapsulated collection of related program objects which are stored together in the database.
Program objects include procedures, functions, variables, constants, cursors, and exceptions.
Slide 8: Anonymous Blocks
Features of an Anonymous Block :
Let us look at the features of Anonymous Blocks. As we know, Anonymous blocks are unnamed blocks. They are declared inline at the point in an application where they are to be executed and are compiled each time the application is executed.
Anonymous blocks are not stored in the database, and they are passed to the PL/SQL engine for execution at run time.
You will not be able to invoke or call the block that you wrote earlier because blocks are anonymous and do not exist after they are executed.
Anonymous blocks can serve as nested blocks inside procedures, functions, and other anonymous blocks.
Structure of an Anonymous Block:
The structure of an Anonymous Block is as shown. Here, it has three basic parts: a declarative part as(DECLARE), an executable part (from BEGIN through END), and an exception-handling (EXCEPTION) part that handles error conditions. Only the executable part is required. The optional declarative part is written first, where you define types, variables, and similar items. These items are manipulated in the executable part. Exceptions raised during execution can be dealt with in the exception-handling part.
Slide 9: Demo Setup
Following is a demonstration about Creating an Anonymous Block
Slide 11: About Procedures
Structure of a Procedure
In this topic, we discuss about Procedures. First, let us take a look at the Structure of a Procedure.
The structure of a Procedure is as shown
The code that begins with PROCEDURE and ends before IS or AS is the subprogram signature. The declarative, executable, and exception-handling parts comprise the subprogram body.
Creating and Managing Procedures:
You create new procedures with the CREATE PROCEDURE statement, which may declare a list of parameters and must define the actions to be performed by the standard PL/SQL block. The CREATE clause enables you to create stand-alone procedures that are stored in an Oracle database.
To create a standalone stored procedure, use either the SQL Developer tool Create PL/SQL Procedure or the DDL statement CREATE PROCEDURE.
To change a standalone stored Procedure, use either the SQL Developer tool Edit or the DDL statement ALTER PROCEDURE