weinratgeber.info Business 11g Complete Reference Pdf


Monday, May 6, 2019

Oracle Database 11g: The Complete Reference. Kevin Loney. New York Chicago San Francisco. Lisbon London Madrid Mexico City Milan. New Delhi San. Read books online. Ebook viewer. Oracle Database Reference, 11g Release 2 (). E Copyright © , , Oracle and/or its affiliates. All rights reserved. Primary Author: Bert Rich.

11g Complete Reference Pdf

Language:English, Spanish, Arabic
Published (Last):
ePub File Size: MB
PDF File Size: MB
Distribution:Free* [*Regsitration Required]
Uploaded by: KISHA

Oracle database 11g: the complete reference. Home · Oracle Oracle Database 10g: The Complete Reference Oracle Database 11g, A Beginner's Guide. Read Oracle 11g Complete Reference Guide writer by weinratgeber.info Study. Why ? oracle database 11g the complete reference oracle press pdf. Oracle Database 11g The Complete Reference - [Free] Oracle Database 11g Reference [PDF] [EPUB] Provides a single source reference for.

Physical and logical structures[ edit ] An Oracle database system—identified by an alphanumeric system identifier or SID [74] —comprises at least one instance of the application, along with data storage. An instance—identified persistently by an instantiation number or activation id: SYS. Oracle documentation can refer to an active database instance as a "shared memory realm". In addition to storage, the database consists of online redo logs or logs , which hold transactional history.

Processes can in turn archive the online redo logs into archive logs offline redo logs , which provide the basis for data recovery and for the physical-standby forms of data replication using Oracle Data Guard. In version 10g, grid computing introduced shared resources where an instance can use CPU resources from another node in the grid. Information is shared between nodes through the interconnect—the virtual private network.

Storage[ edit ] The Oracle RDBMS stores data logically in the form of tablespaces and physically in the form of data files " datafiles ".

Segments in turn comprise one or more extents. Extents comprise groups of contiguous data blocks. Data blocks form the basic units of data storage. A DBA can impose maximum quotas on storage per user within each tablespace.

Specific partitions can then be added or dropped to help manage large data sets. Monitoring[ edit ] Oracle database management tracks its computer data storage with the help of information stored in the SYSTEM tablespace. A data dictionary consists of a special collection of tables that contains information about all user- objects in the database. Since version 8i, the Oracle RDBMS also supports "locally managed" tablespaces that store space management information in bitmaps in their own headers rather than in the SYSTEM tablespace as happens with the default "dictionary-managed" tablespaces.

Iterative Control: There are three loop types: Loops are mainly used to execute statements repeatedly until an exit condition is reached. It is mandatory to have an exit condition in a loop; otherwise, the loop is infinite. Looping constructs are the third type of control structures. Basic loop that performs repetitive actions without overall conditions FOR loops that perform iterative actions based on a count WHILE loops that perform iterative actions based on a condition Note: An EXIT statement can be used to terminate loops.

A basic loop must have an EXIT. A basic loop allows execution of its statements at least once, even if the EXIT condition is already met upon entering the loop. Without the EXIT statement, the loop would be infinite. The EXIT statement must be placed inside a loop. In the latter case, you can attach a WHEN clause to enable conditional termination of the loop.

If the condition yields TRUE, the loop ends and control passes to the next statement after the loop. Basic Loop: Example The basic loop example shown in the slide is defined as follows: If the condition is placed in the loop such that it is not checked until after the loop statements execute, the loop executes at least once.

However, if the exit condition is placed at the top of the loop before any of the other executable statements and if that condition is true, the loop exits and the statements never execute. Results To view the output, run the code example: The condition is evaluated at the start of each iteration. Thus, it is possible that none of the statements inside the loop are executed. If the condition yields NULL, the loop is bypassed and control passes to the next statement. Example In the example in the slide, three new location IDs for the CA country code and the city of Montreal are added.

If the number of iterations is less than or equal to the number 3, the code within the loop is executed and a row is inserted into the locations table. Do not declare the counter; it is declared implicitly. The lower bound is still referenced first. Specifies the lower bound for the range of counter values Specifies the upper bound for the range of counter values.

FOR Loops continued Note: The sequence of statements is executed each time the counter is incremented, as determined by the two bounds. The lower bound and upper bound of the loop range can be literals, variables, or expressions, but they must evaluate to integers. The lower bound and upper bound are inclusive in the loop range. If the lower bound of the loop range evaluates to a larger integer than the upper bound, the sequence of statements is not executed.

For example, the following statement is executed only once: FOR i IN FOR Loops: The example in this slide shows how to achieve the same by using the FOR loop. Do not reference the counter as the target of an assignment. Neither loop bound should be NULL. The lower and upper bounds of a LOOP statement do not need to be numeric literals. They can be expressions that convert to numeric values. Suggested Use of Loops Use the basic loop when the statements inside the loop must execute at least once.

Use a FOR loop if the number of iterations is known. Suggested Use of Loops A basic loop allows the execution of its statement at least once, even if the condition is already met upon entering the loop.

Use a FOR loop if the number of iterations is predetermined. Nested Loops and Labels You can nest loops to multiple levels. Use labels to distinguish between blocks and loops.

Exit the outer loop with the EXIT statement that references the label. The termination of a nested loop does not terminate the enclosing loop unless an exception is raised. However, you can label loops and exit the outer loop with the EXIT statement. Label names follow the same rules as the other identifiers. A label is placed before a statement, either on the same line or on a separate line.

Nested Loops and Labels: Example In the example in the slide, there are two loops. The inner loop is nested within the outer loop. Many other programming languages have this functionality. In some cases, the workarounds are less efficient. It may be more efficient than the previous coding workarounds. The first assignment is executed for each of the 10 iterations of the loop.

You can also use the CONTINUE statement within an inner block of code, which does not contain a loop as long as the block is nested inside an appropriate outer loop. Basic loops that perform repetitive actions without overall conditions FOR loops that perform iterative actions based on a count WHILE loops that perform iterative actions based on a condition.

Summary In this lesson, you should have learned to change the logical flow of statements by using the following control structures: Summary A language can be called a programming language only if it provides control structures for the implementation of business logic.

These control structures are also used to control the flow of the program. A conditional control construct checks for the validity of a condition and performs an action accordingly. You use the IF construct to perform a conditional execution of statements. An iterative control construct executes a sequence of statements repeatedly, as long as a specified condition holds TRUE. You use the various loop constructs to perform iterative operations. Practice 5: Objectives You have already been introduced to composite data types.

In this lesson, you learn more about composite data types and their uses. Composite Data Types Can hold multiple values unlike scalar types Are of two types: Composite Data Types You learned that variables of the scalar data type can hold only one value, whereas a variable of the composite data type can hold multiple values of the scalar data type or the composite data type.

There are two types of composite data types: Records are used to treat related but dissimilar data as a logical unit. For example, you can define a record to hold employee details. By creating a record to store employee details, you create a logical collective unit. This makes data access and manipulation easier.

Collections are used to treat data as a single unit. Collections are of three types: You have all the related data as a single unit. You can easily access and modify data. Data is easier to manage, relate, and transport if it is composite. An analogy is having a single bag for all your laptop components rather than a separate bag for each component. Note that this data type can also be of the composite type such as records. You can define a collection to hold the first names of all employees.

You may have stored n names in the collection; however, name 1 is not related to name 2. The relation between these names is only that they are employee names.

Each record defined can have as many fields as necessary. Fields without initial values are initialized to NULL.

You can define RECORD types and declare user-defined records in the declarative part of any block, subprogram, or package.

You can declare and reference nested records. One record can be the component of another record. To use them, perform the following steps: The syntax for defining the record is shown in the slide. Declare and optionally initialize the internal components of this record type. Field1 data type Field2 data type Field3 data type. To reference or initialize an individual field, use the dot notation: You can then assign a value to the record field: In a block or subprogram, user-defined records are instantiated when you enter the block or subprogram.

They cease to exist when you exit the block or subprogram. Fields in the record take their names and data types from the columns of the table or view. The variable has the same data type and size as the table column. Also, if the variable is a number and is used in any calculations, you need not worry about its precision.

Oracle Database 11g The Complete Reference - WordPress.com

The fields in the record take their names and data types from the columns of the table or view. The record can also store an entire row of data fetched from a cursor or cursor variable. The slide shows the syntax for declaring a record. Is the name chosen for the record as a whole identifier reference Is the name of the table, view, cursor, or cursor variable on which the record is to be based The table or view must exist for this reference to be valid.

This is not code, but simply the structure of the composite variable. Make sure that the column names appear in the same order as the fields in your record. You can also assign one record to another if both have the same corresponding data types. Example The field declarations used in defining a record are like variable declarations. Each field has a unique name and a specific data type. Therefore, you must create the record type first, and then declare an identifier using that type.

When the program is recompiled, it automatically reflects the new table format. In the absence of this attribute, you would be forced to declare a variable for each of the columns retrieved by the SELECT statement. If an employee is retiring, information about that employee is added to a table that holds information about retired employees. The user supplies the employee number. The number of fields in the record must be equal to the number of field names in the INTO clause.

You can use this record to insert values into a table. This makes the code more readable. No employee retires on the hire date.

The inserted record is shown in the slide. You will see how to update this in the next slide. This slide shows you how to update a row by using a record. The ROW keyword is used to represent the entire row. The code shown in the slide updates the leavedate of the employee. The record is updated as shown in the slide. This data type can also be of the composite type such as records. Therefore, collections are used to treat data as a single unit. Of these three collections, the associative array is the focus of this lesson.

These two collections are covered in detail in the course Oracle Database 11g: Primary key of integer or string data type Column of scalar or record data type. It is a composite data type, and is user defined. Associative arrays are sets of key-value pairs. They can store data using a primary key value as the index, where the key values are not necessarily sequential.

Associative arrays have only two columns, neither of which can be named: The first column, of integer or string type, acts as the primary key.

Thousands of Programming Books

The second column, of scalar or record data type, holds values. Jones Smith Maduro Associative Array Structure As previously mentioned, associative arrays have two columns. The second column either holds one value per row, or multiple values. Unique Key Column: The data type of the key column can be: The value column can be either a scalar data type or a record data type.

A column with scalar data type can hold only one value per row, whereas a column with record data type can hold multiple values per row. Other Characteristics An associative array is not populated at the time of declaration.

It contains no keys or values, and you cannot initialize an associative array in its declaration. An explicit executable statement is required to populate the associative array. Like the size of a database table, the size of an associative array is unconstrained. That is, the number of rows can increase dynamically so that your associative array grows as new rows are added.

Note that the keys do not have to be sequential, and can be both positive and negative. Steps to Create an Associative Array Syntax: Steps to Create an Associative Array There are two steps involved in creating an associative array: Declare a variable of that data type. Is the name of the identifier that represents an entire associative array. Creating and Accessing Associative Arrays The key of each associative array is used to access an element in the array, by using the following syntax: Indexing does not need to start with 1.

The exists i method returns TRUE if a row with index i is returned. Use the exists method to prevent an error that is raised in reference to a nonexistent table element. INDEX BY Table of Records Option As previously discussed, an associative array that is declared as a table of scalar data type can store the details of only one column in a database table.

Oracle Database 11g, PL SQL Fundamentals, Student Guide

However, there is often a need to store all the columns retrieved by a query. Creating and Referencing a Table of Records As shown in the associative array example in the slide, you can: If the table structure changes, the record structure also picks up the change. Example 2 The example in the slide declares an associative array, using the INDEX BY table of records option, to temporarily store the details of employees whose employee IDs are between and Another loop is used to print the last names from the array.

Note the use of the first and last methods in the example. However, you can do the same more efficiently using cursors. Cursors are explained in the lesson titled Using Explicit Cursors. The results of the code example is as follows:. Nested Tables The functionality of nested tables is similar to that of associative arrays; however, there are differences in the nested table implementation.

The nested table is a valid data type in a schema-level table, but an associative array is not. Therefore, unlike associative arrays, nested tables can be stored in the database.

The size of a nested table can increase dynamically, although the maximum size is 2 GB. The key cannot be a negative value unlike in the associative array. Though reference is made to the first column as key, there is no key in a nested table.

There is a column with numbers. Elements can be deleted from anywhere in a nested table, leaving a sparse table with nonsequential keys. The rows of a nested table are not in any particular order. When you retrieve values from a nested table, the rows are given consecutive subscripts starting from 1. Nested Tables continued Example: If you do not initialize a nested table, it is automatically initialized to NULL. You can initialize the offices nested table by using a constructor: The complete code example and output is as follows: You have to specify the upper bound when you declare them.

This is similar to arrays in C language. Contrast that with nested tables, which are always stored out-of-line.

Summary of Collection Types Associative Arrays Associative arrays are sets of key-value pairs, where each key is unique and is used to locate a corresponding value in the array. The key can be either integer- or character-based. The array value may be of the scalar data type single value or the record data type multiple values.

Nested Tables A nested table holds a set of values. In other words, it is a table within a table. Nested tables are unbounded; that is, the size of the table can increase dynamically. Varrays Variable-size arrays, or varrays, are also collections of homogeneous elements that hold a fixed number of elements although you can change the number of elements at run time.

They use sequential numbers as subscripts. You can define equivalent SQL types, thereby allowing varrays to be stored in database tables.

When you are not sure about the structure of the underlying database table 2. When you want to retrieve an entire row from a table 3. When you want to declare a variable according to another previously declared variable or database column.

Summary In this lesson, you should have learned to: By using records, you can group the data into one structure, and then manipulate this structure as one entity or logical unit.

This helps reduce coding and keeps the code easy to maintain and understand. The size of an associative array is unconstrained. Nested tables. The key must also be in a sequence. Practice 6: In this lesson, you learn about explicit cursors.

You learn to differentiate between implicit and explicit cursors. You also learn to declare and control simple cursors, as well as cursors with parameters. Agenda What are explicit cursors? Using explicit cursors Using cursors with parameters Locking rows and referencing the current row.

Implicit cursors: You can use explicit cursors to name a private SQL area and to access its stored information.

For queries that return multiple rows, explicit cursors are declared and managed by the programmer, and manipulated through specific statements in the blocks executable actions. The Oracle Server implicitly opens a cursor to process each SQL statement that is not associated with an explicitly declared cursor. The set of rows returned by a multiple-row query is called the active set.

Its size is the number of rows that meet your search criteria. The diagram in the slide shows how an explicit cursor points to the current row in the active set. This enables your program to process the rows one at a time. Explicit cursor functions: Controlling Explicit Cursors Now that you have a conceptual understanding of cursors, review the steps to use them.

Open the cursor. The OPEN statement executes the query and binds any variables that are referenced. Rows identified by the query are called the active set and are now available for fetching. Fetch data from the cursor. In the flow diagram shown in the slide, after each fetch, you test the cursor for any existing row. If there are no more rows to process, you must close the cursor. Close the cursor. It is now possible to reopen the cursor to establish a fresh active set. The cursor marks the current position in the active set.

The OPEN statement executes the query associated with the cursor, identifies the active set, and positions the cursor at the first row. The FETCH statement retrieves the current row and advances the cursor to the next row until there are no more rows or a specified condition is met. Declaring the Cursor The syntax to declare a cursor is shown in the slide. That is because you are only defining a cursor in the declarative section and not retrieving any rows into the cursor.

Note that a variable is used while declaring the cursor. These variables are considered bind variables, which must be visible when you are declaring the cursor. These variables are examined only once at the time the cursor opens. Opening the Cursor The OPEN statement executes the query associated with the cursor, identifies the active set, and positions the cursor pointer at the first row. OPEN is an executable statement that performs the following operations: Dynamically allocates memory for a context area 2.

Binds the input variables sets the values for the input variables by obtaining their memory addresses 4. Identifies the active set the set of rows that satisfy the search criteria.

Rows in the active set are not retrieved into variables when the OPEN statement is executed. Positions the pointer to the first row in the active set Note: After each fetch, the cursor advances to the next row in the active set. Consider the example shown in the slide.

Two variables, empno and lname, are declared to hold the fetched values from the cursor. You have successfully fetched the values from the cursor to the variables. However, there are six employees in department 30, but only one row was fetched.

To fetch all rows, you must use loops. In the next slide, you see how a loop is used to fetch all the rows. Advances the pointer to the next row in the active set. Match each variable to correspond to the columns positionally. Finally, test to see whether the cursor contains rows. If a fetch acquires no values, there are no rows left to process in the active set and no error is recorded.

Closing the Cursor You can reopen the cursor if required. A cursor can be reopened only if it is closed. Cursors and Records You have already seen that you can define records that have the structure of columns in a table. You can also define a record based on the selected list of columns in an explicit cursor.

This is convenient for processing the rows of the active set, because you can simply fetch into the record. Therefore, the values of the rows are loaded directly into the corresponding fields of the record.

The cursor FOR loop is a shortcut to process explicit cursors. Implicit open, fetch, exit, and close occur. The record is implicitly declared.

You now learn to use a cursor FOR loop, which processes rows in an explicit cursor. It is a shortcut because the cursor is opened, a row is fetched once for each iteration in the loop, the loop exits when the last row is processed, and the cursor is closed automatically. The loop itself is terminated automatically at the end of the iteration where the last row is fetched.

Test the cursor attributes during the loop if required. Supply the parameters for a cursor, if required, in parentheses following the cursor name in the FOR statement. Cursor FOR Loops continued The example that was used to demonstrate the usage of a simple loop to fetch data from cursors is rewritten to use the cursor FOR loop.

You can access the fetched data with this implicit record as shown in the slide. Observe that no variables are declared to hold the fetched data using the INTO clause. Explicit Cursor Attributes Use explicit cursor attributes to obtain status information about a cursor. Explicit Cursor Attributes As with implicit cursors, there are four attributes for obtaining the status information of a cursor.

When appended to the cursor variable name, these attributes return useful information about the execution of a cursor manipulation statement. You cannot reference cursor attributes directly in a SQL statement. Fetch rows in a loop. Use cursor attributes to determine when to exit the loop. Example The example in the slide retrieves the first 10 employees one by one. If you are writing cursor FOR loops using subqueries, you need not declare the cursor in the declarative section.

The example that was used to illustrate a cursor FOR loop is rewritten to illustrate a cursor FOR loop using subqueries. You cannot reference explicit cursor attributes if you use a subquery in a cursor FOR loop because you cannot give the cursor an explicit name. Cursors with Parameters Syntax: Pass parameter values to a cursor when the cursor is opened and the query is executed.

Open an explicit cursor several times with a different active set each time. Cursors with Parameters You can pass parameters to a cursor.

This means that you can open and close an explicit cursor several times in a block, returning a different active set on each occasion. For each execution, the previous cursor is closed and reopened with a new set of parameters. Each formal parameter in the cursor declaration must have a corresponding actual parameter in the OPEN statement.

Parameter data types are the same as those for scalar variables, but you do not give them sizes. The parameter names are for reference in the query expression of the cursor. The parameter notation does not offer greater functionality; it simply allows you to specify input values easily and clearly.

This is particularly useful when the same cursor is referenced repeatedly. Cursors with Parameters continued Parameter data types are the same as those for scalar variables, but you do not give them sizes.

The parameter names are for reference in the cursors query. In the following example, a cursor is declared and is defined with one parameter: The following statements open the cursor and return different active sets: You can pass parameters to the cursor that is used in a cursor FOR loop: Use explicit locking to deny access to other sessions for the duration of a transaction.

Lock the rows before the update or delete. You see the updated data only when you reopen the cursor. Therefore, it is better to have locks on the rows before you update or delete rows.

Returns an Oracle Server error if the rows are locked by another session. This is useful when you want to base an update on the existing values in a row. In that case, you must make sure that the row is not changed by another session before the update. Control is immediately returned to your program so that it can do other work before trying again to acquire the lock.

You can try to open the cursor later. If the rows are still locked after n seconds, an error is returned. Use cursors to update or delete the current row.

You can use the combination for updating and deleting the current row from the corresponding database table. This enables you to apply updates and deletes to the row currently being addressed, without the need to explicitly reference the row ID.

Distinguish cursor types: Implicit cursors are used for all DML statements and singlerow queries. Explicit cursors are used for queries of zero, one, or more rows. There are two kinds of cursors: For queries that return multiple rows, you must explicitly declare a cursor to process the rows individually. Every explicit cursor and cursor variable has four attributes: When appended to the cursor variable name, these attributes return useful information about the execution of a SQL statement.

You can use cursor attributes in procedural statements but not in SQL statements. Use simple loops or cursor FOR loops to operate on the multiple rows fetched by the cursor.

If you are using simple loops, you have to open, fetch, and close the cursor; however, cursor FOR loops do this implicitly. This ensures that the data you are using is not updated by another session after you open the cursor. Practice 7: Overview In this practice, you apply your knowledge of cursors to process a number of rows from a table and populate another table with the results using a cursor FOR loop.

You also write a cursor with parameters. So far it has been assumed that the code works satisfactorily if you take care of compile-time errors. However, the code may cause some unanticipated errors at run time. What Is an Exception? There are no syntax errors in the code, which means that you must be able to successfully execute the anonymous block. However, you see the following error report when you execute the code:.

The code does not work as expected. Such errors that occur at run time are called exceptions. Handling the Exception: If your string has a quotation mark as in Todays Date, the string would be 'Today''s Date'. The value of the variable is concatenated with the string 'My name is:'. This value is manipulated in the executable section of the block. Father's day! This makes your string complicated, especially if you have SQL statements as strings.

You can specify any character that is not present in the string as a delimiter. The slide shows how to use the q' notation to specify the delimiter. The example uses! You start the string with q' if you want to use a delimiter. The character following the notation is the delimiter used. Enter your string after specifying the delimiter, close the delimiter, and close the notation with a single quotation mark.

Scalar data types: Scalar data types hold a single value. The value depends on the data type of the variable. Reference data types: Reference data types hold values, called pointers, which point to a storage location.

LOB data types: LOB data types hold values, called locators, which specify the location of large objects such as graphic images that are stored outside the table. You learn about host variables later in this lesson. Atlanta The image represents a BLOB.

Use meaningful identifiers for variables. The key is to apply your naming convention consistently for easier identification.

Use meaningful and appropriate identifiers for variables. A constant must be initialized in its declaration; otherwise, you get a compilation error. After initializing a constant, you cannot change its value. If you do not assign an initial value, the new variable contains NULL by default until you assign a value.Non-predefined exceptions can be any standard Oracle Server errors.

When you start SQL Developer for the first time, you need to provide the path to the java.

When the exception is raised, the control shifts to the exception section and all the statements in the exception section are executed. Subprograms can have an optional declarative section. A subprogram contains the following sections: The syntax for defining the O ly 2.

O ly Program Units. Lesson Objectives After completing this lesson, you should be able to do the following:

LANNIE from Brownsville
I fancy studying docunments absentmindedly . Look over my other articles. I am highly influenced by model building.