Oracle Database Advanced Application Developer's Guide, 11g Release information about SQL processing in Oracle Database Concepts. Home / Oracle Database Online Documentation Library, 12c Release 1 () .. This chapter introduces the advanced PL/SQL features and refers to other. Oracle Advanced PL/SQL Professional Guide focuses on advanced features of Oracle. 11g PL/SQL. PL/SQL concepts (validated up to Oracle 11g Release 1).

Advanced Pl Sql Concepts Pdf

Language:English, Dutch, French
Published (Last):11.11.2015
ePub File Size:19.41 MB
PDF File Size:17.22 MB
Distribution:Free* [*Sign up for free]
Uploaded by: YANG

Oracle 10g Advanced PL/SQL Programming See the Oracle Concepts guide, "Data Concurrency and Consistency," for more information on. Oracle PL/SQL by example / Benjamin Rosenzweig, Elena Silvestrova CHAPTER 1 PL/SQL Concepts. 1 . CHAPTER 10 Exceptions: Advanced Concepts. about optimization of SQL queries and PL/SQL scripts, however, you have to understand the basics of SQL. This manual is written for advanced optimization techniques. Because this manual is an .. Note: You can automatically generate documentation (HTML, PDF, CHM,) from the metadata in the.

Forward references are not allowed. You must declare a variable a e before referencing it in other statements, including other declarative statements. In the executable section, the existing value of the variable can be replaced with the new value.

Subprograms can take parameters. You can pass variables as parameters to subprograms. Variables can be used to hold c the value that is returned by a function.

You have the option of assigning an initial value to a variable as shown in the slide. You do not need to assign a value to a variable in order to declare it. If you refer to O ly other variables in a declaration, be sure that they are already declared separately in a previous statement.

In the syntax: In addition to variables, you can also declare cursors and exceptions in the declarative section. This variable can be accessed in the executable section of the same block. A value John is assigned to O ly the variable in the executable section. String literals must be enclosed in single quotation marks. The value of the variable is concatenated with the string 'My name is: The e r s output of this anonymous block is: This value is manipulated in O the executable section of the block.

The output of this anonymous block is: Father's day! You can specify any character that is not present in the string as delimiter.

The slide shows how to use the q' notation to specify the a e delimiter. The example uses! Consider the following example: You start the string with q' if I n 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 l e quotation mark. The following example shows how to use [ as a delimiter: Scalar data types hold a single value. Composite data types contain internal elements that are either scalar or composite.

Reference data types hold values, called pointers, that point to a storage location. LOB data types hold values, called locators, that specify the location of c large objects such as graphic images that are stored outside the table. You learn about host variables later in this lesson.

I n c l e r a O Oracle Database 11g: 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. It is good programming practice to initialize all variables. Where a e they coexist, you can qualify them with labels and use them.

Although the code example in the slide works, code that is written using the same name for a database table and a variable is not easy to read or maintain. For instance, you can choose from integer, r a floating point, character, Boolean, date, collection, and LOB types. O ly A scalar data type holds a single value and has no internal components.

About This PL SQL Programming Tutorial

Character and number data types have subtypes that associate a base type to a constraint. Base type for variable-length character data up to 32, bytes. The scale s can range from —84 through Base type for integers between —2,,, and 2,,, I n c l e r a O Oracle Database 11g: It requires 5 bytes to store the value. It requires 9 bytes to store the value. DATE values include the time of day in seconds O ly since midnight. The range for dates is between B. To specify the precision, you must use an integer in the range 0—9.

The default is 6. You cannot use a symbolic constant or variable to specify the precision; you must use an integer literal in the range 0—9. When you retrieve the value, the Oracle server returns the value in your local session time zone. The default is 2. In both cases, you cannot use a A symbolic constant or variable to specify the precision; you must use an e l integer literal in the range 0—9. The defaults are 2 and 6, respectively. If you have O ly to design large subprograms, this can be time consuming and error prone.

If you refer to a previously declared variable, I n prefix the variable name of the previously declared variable to the variable being declared. This ensures that such a variable is always compatible with the column that is used to populate it. These r a comparisons, called Boolean expressions, consist of simple or complex expressions separated by relational operators.

30 Most Important PL/SQL Interview Questions and Answers

In a SQL statement, you can use Boolean expressions to specify the rows in O ly a table that are affected by the statement. NULL stands for a missing, inapplicable, or unknown value. For this reason, they are sometimes called host variables. However, bind variables are accessible even after the block is executed.

Therefore, when created, bind variables can be e r s used and manipulated by multiple subprograms. However, you can specify the size for character strings. The Oracle server uses the same number of bytes to store 7, 70, and. It is not practical to calculate the size of the Oracle number representation from the number format, so the code always allocates the bytes needed. With character strings, the size is required from the user so that the required number of bytes can be allocated.

Note the SQL statement that uses the bind variable. The output of the SQL t U statement is: A database column can be of r a the LOB category. With the LOB category of data types BLOB, CLOB, and so on , you can store blocks of unstructured data such as text, graphic images, video clips, and sound O ly wave forms of up to terabytes depending on database block size. When you insert or retrieve such data into or from the I n database, the database does not interpret the data.

External applications that use this data must interpret the data. BFILEs are stored outside the database. They r a could be operating system files. Composite data types also known as collections are: Is used to declare a variable according to a database column definition 2. Is used to declare a variable according to a collection of columns in a database table or view 3. Is used to declare a variable according the definition of another declared variable 4. If you refer to a previously declared variable, c l e prefix the variable name of the previously declared variable to the variable being declared.

Also, if r a the variable is used in any calculations, you need not worry about its precision. You saw some of the guidelines for declaring variables. You learned how to initialize variables when you declare them. You learned how to initialize variables in the I n executable section and also how to use them and manipulate the values of variables.

Overview e A Exercises 1, 2, and 3 are paper based. Identify valid and invalid identifier names: Identify valid and invalid variable declaration and initialization: Examine the following anonymous block and choose the appropriate statement.

The block returns an error because the fname variable is used without initializing. Create an anonymous block. In the declarative section, declare the e r following variables: Sample output is as follows: Add code to create two bind variables. You learn to write nested blocks. You also learn about the scope and visibility of variables in nested blocks and about qualifying variables with labels.

John, , True — Comments: You have learned to identify valid and invalid identifiers. Recall that keywords cannot be used as identifiers.

Quoted identifiers: However, use c of quoted identifiers is not recommended. Delimiters are symbols that have special meaning. Therefore, ; is an O example of a delimiter. This is only a subset and not a complete list of delimiters. Any value that is assigned to a variable is a literal. Any character, numeral, O ly Boolean, or date value that is not an identifier is a literal.

Literals are classified as: A numeric literal represents an integer or real value for example, and 1.

Create a PDF file from Oracle PL/SQL

Values that are assigned to Boolean variables are Boolean literals. It is good programming practice to explain what a piece of code is trying to l e achieve.

There should be a way in which you can indicate that these r ainstructions need not be compiled. Comments are mainly used for this purpose. Any O instruction that is commented is not interpreted by the compiler. Well-placed comments are extremely valuable for code readability and future code maintenance.

O ly The following functions are not available in procedural statements: The functions mentioned here are only a subset of the I n c l e r a O Oracle Database 11g: These functions are grouped into the following categories: Although the old style of using a SELECT statement to query a sequence is still valid, it is recommended that you do not use it. This n t e r method created a usability problem. With the sequence enhancement feature: Data type conversions can be of O ly Implicit conversions: Implicit conversions can be between: To convert values from one data type to another, use built-in functions.

You can nest blocks wherever an r a executable statement is allowed, thus making the nested block a statement.

If your executable section has code for many logically related functionalities to support multiple business O ly requirements, you can divide the executable section into smaller blocks. The exception section can also contain nested blocks.

The r variable is declared in the inner block.

You can access this variable in the inner block as shown in the slide. This variable can be accessed only within the inner block. Patrick r a Date of Birth: These variables have the scope of the block in which they are declared and are O accessible. Therefore, the scope of these variables is limited to the outer block. These variables are accessible only within the nested block and are not accessible in the outer block.

However, this variable is not visible in the inner block because the inner block has a local variable with the same name. You cannot have variables with the same name in a block. However, you can declare variables with the same name in two different blocks nested blocks. The two items represented by the identifiers are distinct; changes in one do not affect the other.

You can use a qualifier to access the variables that have r a scope but are not visible. Examine the code: The outer block is labeled outer. Determine each of the following values according to the rules of scoping: Value of x at position 1 r a O ly 2. Value of outer.

Determining Variable Scope 1. CLERK not eligible for commission 2. To show structure, you can r a divide lines by using carriage returns and you can indent lines by using spaces and tabs. Compare the following IF statements for readability: The functions mentioned here are only a subset of the complete list.

Blocks defined O ly within a block are called subblocks. You can nest blocks only in the executable part of a block. Ensure correct scope and visibility of the variables when you have nested blocks. Avoid n a e using the same identifiers in the parent and child blocks. Conversion t U functions convert a value from one data type to another. Comparison operators compare one I n expression to another. The relational operators enable you to compare arbitrarily complex a expressions.

Overview e A Exercises 1 and 2 are paper based. Use single-line comment syntax to comment the lines that create the bind variables. Use multiple-line comments in the executable section to comment the lines that assign values to the bind variables.

Also, declare two variables: Include the following SQL statement in the executable section: You can comment the lines that display the dates and print the bind variables, if you want to.

Calculate the contribution of the employee toward provident fund PF. Use the local variables for the calculation. Try and use only one expression to calculate the PF. You learn the need for cursors and differentiate O ly between the two types of cursors.

The lesson also presents the various SQL cursor attributes that can be used with implicit cursors. You can use DML commands to modify the data in a database table. Just as a a e block can span multiple transactions, a transaction can span multiple blocks. DDL statements cannot be directly executed.

These statements are dynamic SQL l e statements. Dynamic SQL statements are built as character strings at run time and can c contain placeholders for parameters. You can use dynamic SQL to execute them. Be sure that they correspond positionally and that their data types are compatible. Queries must return only one row. A query that returns more than one row or no row generates an error. You O learn about exception handling later in the course. If your requirement is to retrieve multiple rows and operate on the data, you can make use of explicit cursors.

You cannot use them as follows: The decision whether to update or insert into the target table is based on a condition in the ON clause.

I n Note: MERGE is a deterministic statement. That is, you cannot update the same row of the target l e table multiple times in the same MERGE statement. The data in the employees table needs to remain unchanged. Even though the n t e r U s employees table is not read only, inserting, updating, and deleting are not allowed on this table to ensure consistency of output.

Example Increase the salary of all employees who are stock clerks. Recall that if column names and O ly identifier names are identical in the WHERE clause, the Oracle server looks to the database first for the name. Example Delete rows that belong to department 10 from the employees table. If the WHERE clause is not used, c a all the rows in a table can be removed if there are no integrity constraints.

Implicit and explicit. The SQL statement is parsed and processed in this area. Information required for processing and information retrieved after processing are all stored in this area. You have no e r s control over this area because it is internally managed by the Oracle server. However, this cursor is an implicit cursor and is automatically managed by the Oracle server. An implicit cursor is created and managed by the Oracle server. You do not have access to it. The Oracle server creates such a cursor when it has to execute a SQL statement.

As a programmer, you may want to retrieve multiple rows from a database table, have a pointer to each row that is retrieved, and work on the rows one at a time. In such cases, you can declare cursors explicitly depending on your business requirements. A cursor that is declared by programmers is called an explicit cursor.

I n Therefore, you use SQL instead of the cursor name. This attribute may be used as the exit condition in a loop. O You learn about explicit cursor attributes later in the course. Print the number of rows deleted. However, the DDL commands cannot be used directly. There are two types of cursors.

Implicit cursors are l n a e created and managed internally by the Oracle server to execute SQL statements. Display the maximum department ID. Declare two variables: You have already retrieved the current maximum department ID from the departments table. O ly follows: Skip step a if you have not started a new session for this practice. End the executable block with the END keyword. You r a have also learned to include expressions and SQL statements in the executable block.

The tests are executed in sequence so that a later predicate that might be true is preempted by an earlier predicate that is true. Therefore, the control never reaches the THEN clause. Here is an example: There is no limitation on the number of conditional expressions. However, c l e these statements must be related with appropriate logical operators.

The output of the block is shown in the slide. Conditions are evaluated one by one from the top. The final ELSE clause is optional. The c control goes to the ELSE statement. The value returned by these expressions is used to select one of several alternatives. To return the result, the c r a CASE expression uses a selector, which is an expression whose value is used to return one of several alternatives. The value of the selector determines which result is returned.

If the value of the result is returned.

Furthermore, its WHEN clauses contain search conditions that yield a Boolean value rather than expressions that can yield a value of any type. This value is accepted from the user by using a substitution variable. In searched CASE statements, you do not have a test expression. Similarly, you can include statements in the CASE statement. But nulls s are indeterminate. Whether or not x is equal to y is unknown. In the logic tables shown in the slide: TRUE O ly 2. NULL 4. 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. O ly types of loops: An EXIT statement can be used to terminate loops. A basic loop must have an EXIT. LOOP statement1;. A basic loop allows execution of its statements at least once, even if the EXIT infinite. The EXIT statement must be placed inside a loop. In the c l e latter case, you can attach a WHEN clause to enable conditional termination of the loop.

If the r a condition yields TRUE, the loop ends and control passes to the next statement after the loop. 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.

What is PL/SQL?

The condition is evaluated at the start of each iteration. Thus, it is possible that none of the statements inside the loop are executed. In addition, they have a control c In the syntax: The lower bound is still referenced first.

It is declared implicitly as an integer. 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 This slide shows you how to achieve 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. Without the EXIT statement, the loop would be infinite. The loop terminates iterations are performed.

Use a FOR loop if the number of iterations is predetermined. The termination of a nested loop c r a does not terminate the enclosing loop unless an exception was raised. However, you can label loops and exit the outer loop with the EXIT statement.

O ly Label names follow the same rules as other identifiers. The inner O ly loop is nested within the outer loop. Many other programming languages have this functionality. Before the Oracle Database O ly 11g release, you could code a workaround using Boolean variables and conditional statements to efficient. It may be n t e r U s more efficient than previous coding workarounds.

After the innermost loop is terminated by the BeforeTopLoop in this example.

These control structures are also used to control the flow of the program. You use the IF construct to perform a conditional execution of statements.

You use the various loop constructs to perform iterative I n c l e r a O Oracle Database 11g: Insert the numbers 1 to 10, excluding 6 and 8.

Commit before the end of the block. You should see the following output: This script creates an emp table that is a replica of c a the employees table.

Create a variable sal of type emp. Update the stars column for the employee with the string of asterisks. Commit I n before the end of the block.

Oracle PL SQL Interview Questions

The following should be the output: In this lesson, you learn more about composite data types and their uses. 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 n easier.

This makes data access and manipulation three types: Collections are used to treat data as a single unit. You have all the related data as a single unit. You can easily access and modify the 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 e r s first names of all employees.

You may have stored n names in the collection; however, name 1 t U is not related to name 2. The relation between these names is only that they are employee names. One record can be the component of another I n c l e r a O Oracle Database 11g: The variable has c r a the same data type and size as the table column. Also, if the variable is used in any calculations, you need not worry about its precision.

The fields in the record take their names and data types from the columns of the table or n a e view. The record can also store an entire row of data fetched from a cursor or cursor variable. 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 e A record. You can also assign one record to another if both have the same corresponding data types. When the program is recompiled, it will automatically reflect the new table format.

In the absence of this attribute, you would be forced to declare a variable for each of the I n columns retrieved by the SELECT statement. To use them: The syntax for defining the O ly 2.

Declare and optionally initialize the internal components of this record type. Example Declare variables to store the name, job, and salary of a new employee. Each field has a r a unique name and a specific data type. Therefore, you must create the record type first and then declare an identifier using that type. To reference or initialize an individual field, use dot notation: They cease to exist when you exit the block or subprogram.

If an employee is retiring, c r a 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 O ly into a table. This makes the code more readable. No employee retires on the n a e 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 r a 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. However, the size depends on the values that the key data type can hold. INDEX BY tables can c r a store data using a primary key value as the index, where the key values are not necessarily sequential.

They are used to represent n t e r U s mathematical integers in a compact form and to implement arithmetic operations by using machine arithmetic. If the column is of scalar type, it can c r a hold only one value. If the column is of record type, it can hold multiple values.

This is an user defined type so we are creating it in 2 steps process i. What are The Types of Ref Cursors? Ans: In all databases having 2 ref cursors. Strong ref cursor Weak ref cursor Strong ref cursor is a ref cursor which have return type, whereas weak ref cursor has no return type.

What is Difference Between trim, delete collection method? What is Overloading Procedures? Overloading procedure having same name with different type or different number of parameters. What is Global Variables? What is Forward Declaration? Each constant, value and parameter has a data type that tells us the type of data used and their associated operations in the program.

They are further classified into four categories- CHAR— these data types hold the fixed-length character strings. Its value ranges from 1 to 32, characters. DATE— it is a data type for storing date and time information. LONG— it is an Oracle data type for storing character data of variable length. Composite data type- it is a combination of other data types and internal components that can be easily altered and manipulated.

These include record, table and array. Reference data type- it is a data type that holds pointers to other data items.You have already retrieved the current maximum department ID from the departments table.

You might also like: SQL SERVER 2008 R2 BLACK BOOK

Because they are stored in the database, any t e r U s application can make use of these subprograms based on appropriate permissions. The optional exception section is nested within the executable section. Declare the name of the user-defined exception within the declarative section.

Enter the host name for your PC. View the results on the Script Output tabbed page. It shows the column O description of the table. Rows identified by the query are called the active set and are now available for fetching. In some cases, you may need to write additional code to initialize values to be passed as arguments.

MARTHA from Berkeley
Please check my other posts. I have a variety of hobbies, like canoeing. I do enjoy sharing PDF docs instantly .