Record 6 - 14 For more information about LOBs, see the PL/SQL User's Guide and Reference. Oracle Database 11g: PL/SQL Fundamentals 2 - 9 Types of. Product 6 - 33 Volume I • Student Guide Oracle Database 11g Documentation I .. Oracle Database 11g: SQL Fundamentals I is an instructor-led course featuring lectures . major application development environments such as PL/SQL, Java/JDBC,. . generated in multiple formats such as PDF, HTML, Excel. Oracle Database PL/SQL Language Reference, 11g Release 1 (). B 05 . New PL/SQL Features for 11g Release 1 ().
|Language:||English, Spanish, Portuguese|
|Distribution:||Free* [*Register to download]|
The information contained in this document is subject to change without notice. If you find any problems in the documentation, please report them to us in writing. Oracle Database PL/SQL Language Reference, 11g Release 2 (). E 13 . PL/SQL Features for Oracle Database 11g Release 2. Oracle PL/SQL by example / Benjamin Rosenzweig, Elena Silvestrova Rakhimov . p. cm. .. Formatting Guide. APPENDIX B Student Database Schema section of the PL/SQL Language Reference manual offered as part of Oracle .. Starting with Oracle 11g, the PL/SQL compiler can perform subprogram inlining.
This variable can be accessed only within the inner block. Variable Scope and Visibility The output of the block shown in the slide is as follows: Patrick Date of Birth: Mike Date of Birth: Examine the date of birth that is printed for father and child.
The output does not provide the correct information, because the scope and visibility of the variables are not applied correctly. These variables have the scope of the block in which they are declared. 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, as shown in this example, you can declare variables with the same name in two different blocks nested blocks. The two items represented by identifiers are distinct; changes in one do not affect the other.
Using a Qualifier with Nested Blocks A qualifier is a label given to a block. You can use a qualifier to access the variables that have scope but are not visible. Determine each of the following values according to the rules of scoping: Value of outer. Determining Variable Scope Answers to the questions of scope are as follows: CLERK not eligible for commission 2. The following table shows the default order of operations from high priority to low priority: Code Conventions The following table provides guidelines for writing code in uppercase or lowercase characters to help distinguish keywords from named objects.
Programming Guidelines Make code maintenance easier by: ELSE max: END IF.
To show structure. Compare the following IF statements for readability: Indenting Code For clarity. Quiz You can use most SQL single-row functions such as number.
False 3. The result is always TRUE. The relational operators enable you to compare arbitrarily complex expressions. Avoid using the same identifiers in the parent and child blocks. A block can have any number of nested blocks defined within its executable part. 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. Conversion functions convert a value from one data type to another. Because the exception section is also a part of the executable section.
Comparison operators compare one expression with another. Blocks defined within a block are called subblocks. Practice 3: Overview Exercises 1 and 2 are paper based.
The lesson also presents the various SQL cursor attributes that can be used with implicit cursors. Objectives In this lesson. You learn the need for cursors and differentiate between the two types of cursors. Make changes to rows in the database by using DML commands. You can use dynamic SQL to execute them. DDL statements cannot be directly executed.
Just as a block can span multiple transactions. Dynamic SQL statements are built as character strings at run time and can contain placeholders for parameters. You can use DML commands to modify the data in a database table. These statements are dynamic SQL statements. Be sure that they correspond positionally and that their data types are compatible.
Queries must return only one row. You must specify one variable for each item selected. A query that returns more than one row or no row generates an error. If your requirement is to retrieve multiple rows and operate on the data. Observe how the INTO clause. The variables in the INTO clause must thus be in the same order.
This example raises an unhandled runtime exception because. The example shown in the slide is defined as follows: The names of local variables and formal parameters take precedence over the names of database tables.
The names of database table columns take precedence over the names of local variables. Avoid using database column names as identifiers. MERGE is a deterministic statement. That is. The decision whether to update or insert into the target table is based on a condition in the ON clause. Inserting Data: The data in the employees table needs to remain unchanged. Inserting Data In the example in the slide.
Even though the employees table is not read-only. If no rows are modified. Updating Data: Example Increase the salary of all employees who are stock clerks. Deleting Data: Example Delete rows that belong to department 10 from the employees table. If a match is found. If the row is not found. Each row is inserted or updated in the target table depending on an equijoin condition.
You do not have access to it. The Oracle Server allocates a private memory area called the context area for processing SQL statements. Types of Cursors There are two types of cursors: An implicit cursor is created and managed by the Oracle Server. When the executable block issues a SQL statement. A cursor is a pointer to the context area. You have no control over this area because it is internally managed by the Oracle Server. There are two types of cursors: The SQL statement is parsed and processed in this area.
The information required for processing and the information retrieved after processing are all stored in this area. In such cases. As a programmer. A cursor that is declared by programmers is called an explicit cursor. Observe that the attributes are prefixed with SQL. This attribute may be used as the exit condition in a loop.
Print the number of rows deleted. False 4. You learn about exception handling later in the course. Explicit cursors are declared by programmers. There are two types of cursors. A cursor is a pointer to the memory area. Practice 4: You have also learned to include expressions and SQL statements in the executable block.
CASE expressions. LOOP control structures. IF Statement Syntax: They may include additional IF statements containing several nested IF. IF Statement continued In the syntax: The tests are executed in sequence so that a later predicate that might be true is preempted by an earlier predicate that is true. There is no limitation on the number of conditional expressions. For example: The condition has not changed and.
In this case. The output of the block is shown below the code. I am young '. I am in my thirties'. I am always young '. The example illustrates the following characteristics of these clauses: I am in my twenties'. In such a case. To return the result. To select the result. The value of the selector determines which result is returned. The value returned by these expressions is used to select one of several alternatives.. The selector is followed by one or more WHEN clauses that are checked sequentially.
If the value of the selector equals the value of a WHEN clause expression. This value is accepted from the user by using a substitution variable. CASE Expressions: Based on the value entered by the user. Example In the example in the slide. The WHEN clause compared a value against this test expression. The same example is rewritten in this slide to show searched CASE statements.
In searched CASE statements. The output of the slide code example is as follows: Handling Nulls Consider the following example: In the second example. But nulls are indeterminate. Whether or not x is equal to y is unknown..
You may expect the sequence of statements to execute because x and y seem unequal. Handling Nulls When you are working with nulls. You can build a complex Boolean condition by combining simple Boolean conditions with the logical operators AND.
Logic Tables You can build a simple Boolean condition by combining number. Logic Tables Build a simple Boolean condition with a comparison operator. In the logic tables shown in the slide: Answers 1. NULL 4. Boolean Expressions or Logical Expression? The AND logic table can help you to evaluate the possibilities for the Boolean condition in the slide. What is the value of flag in each case? TRUE 2. FALSE 3. Iterative Control: An EXIT statement can be used to terminate loops.
A basic loop must have an EXIT. Looping constructs are the third type of control structures. It is mandatory to have an exit condition in a loop. There are three loop types: Loops are mainly used to execute statements repeatedly until an exit condition is reached. A basic loop allows execution of its statements at least once.
Basic Loops Syntax: LOOP statement1. If the condition yields TRUE. A basic loop can contain multiple EXIT statements. Without the EXIT statement. In the latter case. When the EXIT statement is encountered. The EXIT statement must be placed inside a loop. Basic Loop: Example The basic loop example shown in the slide is defined as follows: Results To view the output.
The condition is evaluated at the start of each iteration. If the condition yields NULL. It is declared implicitly as an integer. The lower bound is still referenced first. Do not declare the counter. In addition. FOR i IN 3. The bounds are rounded to integers. The lower bound and upper bound of the loop range can be literals.
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. FOR Loops continued Note: The sequence of statements is executed each time the counter is incremented. FOR Loops: The example in this slide shows how to achieve the same by using the FOR loop. FOR i IN 1. The lower and upper bounds of a LOOP statement do not need to be numeric literals. Neither loop bound should be NULL..
They can be expressions that convert to numeric values. Do not reference the counter as the target of an assignment.. 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. Use a FOR loop if the number of iterations is predetermined. If the loop is labeled. The termination of a nested loop does not terminate the enclosing loop unless an exception is raised. Label names follow the same rules as the other identifiers.
Use labels to distinguish between blocks and loops. Exit the outer loop with the EXIT statement that references the label. A label is placed before a statement. Leave inner loop only.. The inner loop is nested within the outer loop.. Nested Loops and Labels: Before the Oracle Database 11g release. In some cases. With the Oracle Database 11g release. Many other programming languages have this functionality. 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. 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. You have all the related data as a single unit. Data is easier to manage. There are two types of composite data types: Records are used to treat related but dissimilar data as a logical unit. An analogy is having a single bag for all your laptop components rather than a separate bag for each component. Collections are of three types: You can easily access and modify data.
Collections are used to treat data as a single unit. By creating a record to store employee details. Associative array. Composite Data Types You learned that variables of the scalar data type can hold only one value. Nested table. This makes data access and manipulation easier. You may have stored n names in the collection. These collections are similar to arrays in programming languages such as C. The relation between these names is only that they are employee names.
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. One record can be the component of another record.
The syntax for defining the record is shown in the slide. To use them. Declare and optionally initialize the internal components of this record type. To reference or initialize an individual field.
They cease to exist when you exit the block or subprogram. In a block or subprogram. Field1 data type Field2 data type Field3 data type Example: The record can also store an entire row of data fetched from a cursor or cursor variable. The variable has the same data type and size as the table column. Is the name chosen for the record as a whole identifier reference Is the name of the table.
In the following example. The fields in the record take their names and data types from the columns of the table or view.
The slide shows the syntax for declaring a record.. This is not code. You can also assign one record to another if both have the same corresponding data types. Make sure that the column names appear in the same order as the fields in your record. Each field has a unique name and a specific data type.
In the example in the slide. Example The field declarations used in defining a record are like variable declarations. In the absence of this attribute. If a DDL statement changes the columns in a table.
When the program is recompiled. The user supplies the employee number. If an employee is retiring. No employee retires on the hire date. This makes the code more readable. To see the output shown in the slide. You can use this record to insert values into a table. You will see how to update this in the next slide. The number of fields in the record must be equal to the number of field names in the INTO clause.
The inserted record is shown in the slide. This slide shows you how to update a row by using a record. These two collections are covered in detail in the course Oracle Database 11g: This data type can also be of the composite type such as records. Agenda As stated previously. Of these three collections. It is a composite data type.
They can store data using a primary key value as the index. Associative arrays are sets of key-value pairs. Associative arrays have only two columns. Associative Array Structure As previously mentioned. Values column The value column can be either a scalar data type or a record data type.. Note that the keys do not have to be sequential. It contains no keys or values.. A column with scalar data type can hold only one value per row Unique Key Column: The data type of the key column can be: Jones Smith Maduro.
The second column either holds one value per row. Associative Array Structure 1 Unique key column Steps to Create an Associative Array Syntax: Example In the example.
Steps to Create an Associative Array There are two steps involved in creating an associative array: Is the name of the identifier that represents an entire associative array identifier Note: Declare a variable of that data type.. Use the exists method to prevent an error that is raised in reference to a nonexistent table element. Creating and Accessing Associative Arrays The example in the slide creates two associative arrays.
Indexing does not need to start with 1. Creating and Accessing Associative Arrays. The key of each associative array is used to access an element in the array. All rights reserved.. Creating and Referencing a Table of Records As shown in the associative array example in the slide. If the table structure changes. Another loop is used to print the last names from the array.
Note the use of the first and last methods in the example.. Using a loop. Example 2 The example in the slide declares an associative array. There is a column with numbers. Though reference is made to the first column as key. Nested Tables The functionality of nested tables is similar to that of associative arrays.
Nested Tables 1 2 3 4. Bombay Sydney Oxford London. The complete code example and output is as follows: If you do not initialize a nested table. FOR i in 1. Nested Tables continued Example: BEGIN offices: You can initialize the offices nested table by using a constructor: Tokyo 6. You have to specify the upper bound when you declare them. This is similar to arrays in C language. Contrast that with nested tables.. The key can be either integer. They use sequential numbers as subscripts.
Nested Tables A nested table holds a set of values. Nested tables are unbounded. In other words. The array value may be of the scalar data type single value or the record data type multiple values.
You can define equivalent SQL types. Because associative arrays are intended for storing temporary data. Varrays Variable-size arrays. When you want to retrieve an entire row from a table 3. When you are not sure about the structure of the underlying database table 2.
When you want to declare a variable according to another previously declared variable or database column 6. By using records. The size of an associative array is unconstrained. This helps reduce coding and keeps the code easy to maintain and understand. 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. However, it is good programming practice to initialize all variables. Two objects can have the same name only if they are defined in different blocks. Where they coexist, you can qualify them with labels and use them.
Avoid using column names as identifiers. 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, floating point, character, Boolean, date, collection, and LOB types. A scalar data type holds a single value and has no internal components.
Scalar data types can be classified into four categories: number, character, date, and Boolean. Character and number data types have subtypes that associate a base type to a constraint. If you do not specify a maximum length, the default length is set to 1. Base type for variable-length character data up to 32, bytes.
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. Note that the keys can be both positive and negative. Declare a variable of that data type. That is, the c grows as new rows are added. The column can belong to any scalar or record data type. The primary key is either a number or a string. It contains no keys or values. 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 c a method to prevent an error that is raised in reference to a nonexistent table element. There is often a need to store all the columns retrieved by a query.
I n table index. If the table structure changes, the record structure also picks up the change. The slide demonstrates one way to work with INDEX BY table of records; however, you n a e can do the same more efficiently using cursors.
There is a column with numbers in sequence that is considered as the key column. Elements can be deleted from anywhere in a nested table, leaving a sparse table with a e nonsequential keys. The rows of a nested table are not in any particular order. When you retrieve n t e r s values from a nested table, the rows are given consecutive subscripts starting from 1. You can check whether an element exists in a nested table and also whether a nested table is a subset of another.
You can initialize the offices nested table by using a constructor: You have to specify the upper bound O ly when you declare them. This is similar to arrays in the C language. Contrast that with nested tables, which are n Example: 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. In the absence of this attribute, you would be forced to declare a variable for each of the c l e columns retrieved by the SELECT statement. By using r a 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 key column can be an integer or a string; the column that holds the value can be of any data type. The key must also be in a sequence. In the executable section, get all the information from the countries table by using countryid.
Display selected information about the country. Sample output is as follows. Assign 10 to c a c. Increase deptno by 10 for every iteration of the loop. The output is as follows: Modify the block that you created in question 2 to retrieve all information about each department from the departments table and display the information.
Administration Manager Id: Marketing Manager Id: downloading Manager Id: Human Resources Manager Id: Shipping Manager Id: IT Manager Id: Public Relations Manager Id: Sales Manager Id: Executive Manager Id: Finance Manager Id: In this lesson, you learn about explicit cursors.
You learn to differentiate between implicit and explicit cursors. You also learn to declare and control O ly simple cursors as well as cursors with parameters.
The set of rows returned by a multiple-row query is called the active set. Its size is the number of O ly rows that meet your search criteria.
The diagram in the slide shows how an explicit cursor at a time. This enables your program to process the rows one Explicit cursor functions: SQL area. Open the cursor. Rows identified by the query are called the active set and are now available for fetching. Fetch data from the cursor. If there are no more rows to process, you must close the cursor.
Close the cursor. It is now possible to reopen the l e cursor to establish a fresh active set. Cursor pointer 2 Fetch a row. Cursor pointer Cursor 3 Close the cursor. The cursor marks the current position in the active set.
The OPEN statement executes the query associated with the cursor, identifies the active set, O ly 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. 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. Dynamically allocates memory for a context area n a e 3. Binds the input variables sets the values for the input variables by obtaining their memory addresses t e r U s 4. Identifies the active set the set of rows that satisfy the search criteria.
Positions the pointer to the first row in the active set c r a Note: After each fetch, the c r a cursor advances to the next row in the active set.
O ly Consider the example shown in the slide. However, there are six employees in department 30, but only one row was fetched. To fetch all rows, you must use e r s 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 c r a O Oracle Database 11g: 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. You can reopen the cursor if required. A cursor can be reopened only if it is closed. This is convenient for processing the rows of the active set, because you can simply fetch into the O ly record.
Therefore, the values of the row are loaded directly into the corresponding fields of the record. You now learn to use a r 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 O ly processed, and the cursor is closed automatically. The loop itself is terminated automatically at In the syntax: You can access the fetched data with O ly this implicit record as shown in the slide.
You cannot reference cursor attributes directly in a SQL statement. Use cursor attributes to determine when to exit the loop. Example e A c l The example in the slide retrieves the first ten employees one by one. The difference between the cursor r a FOR loops using subqueries and the cursor FOR loop lies in the cursor declaration.
If you are writing cursor FOR loops using subqueries, you need not declare the cursor in the declarative O ly section. 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. This means that you can open and close an explicit cursor r a 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. Parameter data types are the same as those for scalar variables, but you do not give them sizes. The parameter names are for references in the query expression of the cursor. This is particularly useful when the same cursor is referenced repeatedly. 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 see the updated data only when you reopen the cursor.
Therefore, it is better to have locks on the rows before you update or O ly delete rows. 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. This enables you to apply updates and deletes to the row currently being addressed, without the need to explicitly reference the row ID. When evaluated, the subquery provides a value or set of values to the outer query. A table alias, t2, a e refers to this temporary data source in the FROM clause.
When this cursor is opened, the active n t e r U s set contains the department number, department name, and total number of employees working for those departments that have three or more employees.
The Oracle server implicitly opens a cursor to process each SQL statement that is not associated with an explicitly declared cursor. There are two kinds of cursors: For individually. 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 I n statements but not in SQL statements. If you are using simple loops, you have to open, fetch, and close the cursor; however, cursor FOR r a loops do this implicitly.
This ensures that the data you are using is not updated by another session after O you open the cursor. Overview e A c l In this practice, you apply your knowledge of cursors to process a number of rows from a table parameters. In the executable section, use the cursor FOR loop to operate on the data retrieved. Declare variables to hold the values retrieved from each cursor.
Display the department number and department name. You may want to print a line after you have displayed the details of each department. Use appropriate attributes for the exit condition. Also, determine whether a cursor is already open before opening the cursor.
Close all the loops and cursors, and then end the executable section. Execute the script. The sample output is as follows: For example, to view the top five salaries, enter 5.
Declare another variable sal of type employees. You can use a simple loop to operate on the data. Make sure you add an exit condition to avoid having an infinite loop.
The output shown represents the five highest salaries in the employees table. So far it has been assumed that the code works satisfactorily if you take care of compile-time O ly errors. However, the code may cause some unanticipated errors at run time. There are no syntax errors in the code, which means r a you must be able to successfully execute the anonymous block. You see the following output when you execute the code: Such errors that occur at run time are called exceptions. Consider using a cursor.
For exception handling, you include another optional section called the exception O ly section. You need not pay attention to the syntax and statements; you learn about them later in the lesson. The output of t e r U s the code is shown in the slide above. When the exception is raised, I n the control shifts to the exception section and all the statements in the exception section are c l e executed.
These errors are t e r U s converted into predefined exceptions. The raised exception may be either user-defined or predefined. These errors are any standard Oracle c r a errors that are not predefined. You can explicitly declare exceptions and associate them with the non-predefined Oracle errors.
No abruptly. Yes Exception Execute statements Propagate the is raised. Terminate gracefully. If the exception is raised in the executable section of the block, processing then branches to the corresponding O ly exception handler in the exception section of the block. You need to declare these within the Oracle Server error t e r s Oracle Server error U declarative section; the Oracle server will raise the error implicitly, and I n you can catch for the error in the c l e User-defined error A condition that the exception handler.
Declare in the declarative section r a developer determines is abnormal and raise explicitly. O Note: Each handler consists of a WHEN clause, which specifies an exception name, followed by a sequence of statements to be executed when that exception is O ly raised. However, you cannot have multiple handlers for a single exception. This traps any exception not yet handled. Some Oracle tools have their own predefined exceptions that you can raise to cause events in the application.
They are standard Oracle errors. O ly Such exceptions are called non-predefined exceptions. The declared exception is raised implicitly. That enables you to refer to any internal exception t e r U s by name and to write a specific handler for it. Declare the name of the exception in the declarative section. Reference the declared exception within the corresponding exception-handling routine. Note the following line in the example: Instead, you must assign their values to local variables and then use the variables in the SQL statement, as shown in the following example: For example, you may prompt the user to enter a department number.
Define an exception to deal with error conditions in the input data. Check whether the O ly department number exists. If it does not, then you may have to raise the user-defined exception. Declare the name of the user-defined exception within the declarative section. The user supplies the r a department number and the new name. If the supplied department number does not exist, no rows are updated in the departments table.
Raise an exception and print a O message for the user that an invalid department number was entered. Use the RAISE statement by itself within an exception handler to raise the same exception again and propagate it back to the calling environment.
If results. O ly In the syntax: The returned error is consistent with how the Oracle server produces a predefined, non-predefined, or user-defined error. The error number O ly and message are displayed to the user.
Exception section: Predefined exceptions are error conditions that are defined by the Oracle server. Non-predefined exceptions can be any standard Oracle O ly server errors. User-defined exceptions are exceptions specific to your application. Internal exceptions are raised implicitly automatically by the run-time system; however, user-defined exceptions must be raised a explicitly.
To handle raised exceptions, you write separate routines called exception handlers. Overview e A c l In this practice, you create exception handlers for specific situations. The purpose of this practice is to show the usage of predefined exceptions. Delete all records in the messages table. In the declarative section, declare two variables: Initialize the latter to Do not use explicit cursors. The purpose of this practice is to show how to declare exceptions with a standard Oracle violated — child record found.
This lesson introduces you to named blocks, which r a are also called subprograms. In the lesson, you learn to differentiate between anonymous blocks and subprograms.
Because they are unnamed, they can be neither reused nor stored for later use. They are also known as subprograms. The block structure of the subprograms is similar to the structure of anonymous blocks. A subprogram contains the following sections: Subprograms can have an optional declarative section. The optional declarative section follows the IS or AS keyword in the subprogram r a Executable section: This is the mandatory section of the subprogram, which contains the implementation of the business logic.
Looking at the code in this section, you can easily O determine the business functionality of the subprogram. This is an optional section that is included to handle exceptions. Anonymous blocks are not persistent database objects. They are compiled and executed O ly only once. They are not stored in the database for reuse.
Procedures and functions are compiled and stored in the database in a compiled form. They are n a e recompiled only when they are modified. 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 calling application can pass parameters to the procedures if the procedure is designed to accept I n parameters. Similarly, a calling application can retrieve a value if it invokes a function or a procedure.
Syntax e A c l The slide shows the syntax for creating procedures. You can have any number of arguments separated by commas. You learn about procedures in detail in O the course titled Oracle Database 11g: Example e A l Examine the code in the slide. You can check the source for the procedure by issuing the following command: You have to include the r a call to the procedure in the executable section of the anonymous block.
Similarly, you can invoke the procedure from any application, such as a Forms application, a Java application, and O ly so on. Syntax e A c l The slide shows the syntax for creating a function. You can have any number or arguments separated by a comma. You pass the argument when a e you invoke the function. The difference between a procedure O and a function is that a function must return a value to the calling program.
The function is hard- n a e coded to check for this employee ID only. If you want to check for any other employees, you t e r U s must modify the function itself.
You can solve this problem by declaring the function so that it accepts an argument. You can then pass the employee ID as parameter. Thus the call to the function is included as the conditional expression for the IF block. The code shown in the slide removes that constraint because it is rewritten to accept the employee number as a parameter.
O ly Program Units. The output of the code is as follows: Are compiled only once 3. Are stored in the database 4. Do not have to return values if they are functions 5. However, the major r a constraint with anonymous blocks is that they are not stored and, therefore, cannot be reused.
The structure of a procedure or a function is similar to the structure of an anonymous block. These subprograms are stored in the database and are, therefore, reusable. Modify the script to convert the anonymous block to a procedure called greet. Click the Clear button to clear the workspace.
Create and execute an anonymous block to invoke the greet procedure. Drop the greet procedure by issuing the following command: Execute the script to create the procedure. Open a new worksheet. Click the SQL Developer icon on your desktop. Use the preceding information provided c l Note: To display the properties of the newly created connection, right-click the r a connection name, and then select Properties from the shortcut menu.
Substitute the username, password, host name, and service name with the appropriate information O ly as provided by your instructor. Review the results of both methods of c l Note: Take a few minutes to familiarize yourself with the data, or consult Appendix B, this course.
The Select Connection window is displayed. I c l e r a O Oracle Database 11g: This folder contains the when done to accept the new setting. Ask your instructor for help in locating the labs folder for this course. Start SQL Developer. The instructor will provide the necessary information. Enter the following code in the workspace.
Click the Run Script button. You should see the following output in the Script Output tab: Click the Save button. Select the folder in which you want to save the file. Identify valid and invalid identifiers: The declaration in c is invalid because string literals should be enclosed within single quotation marks.
O ly The block produces an error because the fname variable is used without initializing. Click the Open button. Your workspace will now have the code in the. In the declarative section, declare the following variables: Variable today of type DATE. Variable tomorrow of type today. Follow the instructions in m y d e step 2 f of practice 1 to save the file. Sample output is as d e follows: Product is in stock.
Declare variables: Calculate the contribution of the employee towards provident fund PF. A variables for the calculation. Try to use only one expression to calculate the PF. Interacting with the Oracle Server 1.
Sample output is as m y follows: You have already retrieved the current maximum department number from the c departments table. Writing Control Structures 1. Insert the numbers 1 through 10, excluding 6 and 8. This script creates an emp table that is a replica of the I n employees table. Create a variable sal of type Oracle Database 11g: Commit m y before the end of the block. Working with Composite Data Types 1.
Modify the block that you created in exercise 2 to retrieve all information about each I table of records. Using Explicit Cursors 1. Also, check whether a cursor is already open before opening the cursor. Close all the loops and cursors, and end the executable section.
For r a example, to view the top five salaries, enter 5. Remember that the salaries should not be duplicated. Handling Exceptions 1.
Delete all the records in the messages table. The purpose of this practice is to show how to declare exceptions with a standard Oracle Server error. Use the Oracle server error ORA integrity constraint violated — child record found. There are r a employees in this department child records exist. Creating Stored Procedures and Functions 1. Call the argument n a e today DATE: Create and execute an anonymous block to invoke the greet procedure with a parameter.
You learn how r a to use SQL Developer for your database development tasks. With just a few clicks, you can easily create and debug stored procedures, test SQL statements, and view optimizer plans.
The SQL Developer 1. Therefore, it is a cross-platform tool. SQL Developer does not require an installer t e r U s and you need to simply unzip the downloaded file.