Oracle Database PL/SQL User's Guide and Reference 10g Release 2 (). B .. 2 Fundamentals of the PL/SQL Language. Character Sets and. PL/SQL Feature for Oracle Database 11g Release 2 (). .. 2 PL/ SQL Language Fundamentals. Character Sets. PL/SQL is one of three key programming languages embedded in the Oracle Database, in this tutorial, please notify us at [email protected]
|Language:||English, Japanese, Arabic|
|Country:||United Arab Emirates|
|ePub File Size:||20.48 MB|
|PDF File Size:||10.80 MB|
|Distribution:||Free* [*Registration needed]|
Records 7 - 14 Oracle University and SQL Star International Limited use onlyฺ .. Oracle Database 10g: PL/SQL Fundamentals is an instructor-led course. x Oracle Database 10g PL/SQL Programming Chapter 3: PL/SQL Basics well-paced examples of the fundamentals required for effective PL/SQL coding. Oracle PL/SQL by example / Benjamin Rosenzweig, Elena Silvestrova Rakhimov . p. cm. LAB PL/SQL in SQL*Plus. Use Substitution Variables. Use the LAB PL/SQL Programming Fundamentals.
Your account ID is ora Each machine has its own complete environment, and is assigned the same account. All rights reserved. You can connect to any target Oracle database schema by using standard Oracle database authentication. You use SQL Developer in this course. SQL Developer Oracle SQL Developer is a free graphical tool designed to improve your productivity and simplify the development of everyday database tasks.
With just a few clicks, you can easily create and maintain stored procedures, test SQL statements, and view optimizer plans. When you are connected, you can perform operations on objects in the database. Alternatively, you can also view the introduction to SQL Developer demonstration.
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. 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. 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. 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.
Oracle PL SQL Interview Questions
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.
About This PL SQL Programming Tutorial
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. Loops are used to execute a single statement for 'N' number of times. There are 3 types of loops: 1. While loop 3. For loop or Numeric loop. Syntax: for loopcounter in lowerbound.. Cursor Types: 1. Note: implicit cursors always stores the attributes of the recently executed Ex1: begin delete from emp; sql statement.
Explicit cursors are always defined only on 'select' queries and the select query may refere any no. If the query retrieves 0 Explicit Cursors 1.
Controlled by user 2. Only on select queries 3.
Exception is not raised when the implicit cursors processed by the cursor so far ex: 1,2,3, Performance is better than 4. Poor Performance compared to 5. Your program attempts to assign values to the attributes of an uninitialized atomically null object. A cursor must be closed before it can be reopened. A cursor FOR loop automatically opens the cursor to which it refers.
So, your program cannot open that cursor inside the loop. The FETCH statement is expected to return no rows eventually, so when that happens, no exception is raised.Note that a variable is used while declaring the cursor.
Therefore, the control never reaches the THEN clause. An analogy is having a single bag for all your laptop components rather than a separate bag for each component. To create a folder for user-defined reports, right-click the User Defined Reports node r a or any folder name under that node and select Add Folder. Click Test to make sure that the connection has been set correctly.
You can use substitution variables in the SQL statements, but not bind variables. You do not need to customize them to each new environment.