Oracle Database PL/SQL Language Reference, 11g Release 1 (). B 05 . New PL/SQL Features for 11g Release 1 (). Oracle Database PL/SQL Language Reference, 11g Release 2 (). E 13 . PL/SQL Features for Oracle Database 11g Release 2. Record 6 - 14 O ly l & On n a e t e r U s I n c l e r a O Oracle Database 11g: PL/SQL Fundamentals I - 2 Course Objectives After completing this course, you.
|Language:||English, Spanish, Hindi|
|Genre:||Academic & Education|
|Distribution:||Free* [*Sign up for free]|
Development, Oracle Database 10g PL/SQL Programming, and Expert. Oracle PL/SQL. About the Technical Editor. A. Scott Mikolaitis is an Applications Architect. Oracle 11g has introduced a number of new features and improvements for PL/ SQL. This intro- duction briefly describes features not covered in this book and. Covers Versions Through. Oracle Database 11g Release 2. 5th Edition. Oracle *. PL/SQL. Programming. O'REILLYⓇ. Steven Feuerstein with Bill Pribyl.
Enter the host name for your PC. ORCL 3. Test the new connection. If the Status is Success, connect to the database using this new connection.
If the status is Success, click the Connect button. Expand the MyDBConnection connection by clicking the plus sign next to it. Expand the Tables icon by clicking the plus sign next to it. Take a few minutes to familiarize yourself with the data, or consult Appendix B, a e which provides the description and data for all tables in the HR schema that you will use in n this course. The Preferences window is displayed. Click the Worksheet Parameters option under the Database option.
This folder c r acontains the solutions scripts, code examples scripts, and any labs or demos used in this course. Familiarize yourself with the labs folder on the D: The Open window is displayed. Ensure that the path that you set in a previous step is the default path that is displayed in the Open window. How many subfolders do you see in the labs folder? Navigate through the folders, and open a script file without executing the code. Clear the displayed code in the SQL Worksheet area.
There are only a few SQL commands, so you can easily learn and use them.
However, if you want to alter any data that is retrieved in a conditional manner, you soon encounter the limitations of SQL. For every employee retrieved, check the n t e r U s department ID and the salary. I n Looking at the problem, you know that you have to execute the preceding SQL statement, c l e collect the data, and apply logic to the data.
One solution is to write a SQL statement for each department to give bonuses to the employees in that department. Remember that you also have r a to check the salary component before deciding the bonus amount. This makes it a little complicated. You now feel that it would be much easier if you had conditional statements. It provides a programming extension to the already-existing SQL. Maintenance of code is made easier with such a well- defined structure. Maintaining and debugging the code is made unit.
It brings state-of-the-art programming to the Oracle server and toolset. It passes the procedural statements to the individually.
Therefore, all procedural statements are executed locally and only the I n SQL statements are executed in the database. SQL is a nonprocedural language. When you issue a SQL command, your command tells the database server what to do. However, you O ly cannot specify how to do it. Earlier in this lesson, you saw an example of the need for such integration.
If you have designed an application containing forms, you may have many different forms with fields in each form. When a form submits the data, you may have to I n execute a number of SQL statements.
SQL statements are sent to the database one at a time. The application can send the entire block to the database instead of sending the SQL statements one O at a time. This significantly reduces the number of database calls.
Blocks has the following advantages: You do not need to customize them to each new environment. You can write portable program packages and create libraries that can be reused in different environments. Exception handling: You can define separate blocks for dealing with exceptions. This section essentially needs to have at least one statement. Observe that END is terminated with a semicolon. The exception section is nested within the executable section.
U I n c l e r a O Oracle Database 11g: These blocks can be entirely separate or They are: Anonymous blocks are unnamed blocks. They are declared inline at the t e r U s point in an application where they are to be executed and are compiled each time the application is executed. These blocks are not stored in the database. Triggers in Oracle Developer components consist of such blocks.
These anonymous blocks get executed at run time because they are inline. If you want to execute l e the same block again, you have to rewrite the block. You are unable to invoke or call the block c a that you wrote earlier because blocks are anonymous and do not exist after they are executed. Subprograms are complementary to anonymous blocks.
Because they are named and stored, you can invoke them whenever you want depending on your application. You can declare them either as procedures or as functions.
You typically use a procedure to perform an action and a function to compute and return a value. You can store subprograms at the server or application level. Using Oracle Developer components Forms, Reports , you can declare procedures and functions as part of the application a form or report and call them from other procedures, functions, and triggers within the same application whenever necessary.
A function is similar to a procedure, except that a function must return a value. They are fired components for example, Oracle automatically when triggered by a DML or a Forms Developer system event respectively. The block has the declarative section and the executable section.
You need not pay attention to the syntax of statements in the block; you learn the syntax later in the O ly course. Note that the is executed. You now learn how to print the value. Therefore, you need to use O ly predefined Oracle packages for input and output. To generate output, you must: Pass I n the value that has to be printed as argument to this procedure as shown in the slide. The procedure then outputs the argument. True 2. The optional exception section is nested within the executable section.
Overview This practice covers the following topics: You can save your scripts in the labs folder. The solutions for all practices are in the soln folder. In this lesson, you learn r a about valid and invalid identifiers. The lesson describes the various data types. Variables are used to temporarily store the value. You can use the value stored in these variables for processing and manipulating n a e the data. After the variables are declared, you can I n use them repeatedly in an application by referring to them multiple times in various statements.
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.
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.
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. 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.
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: This error is called mutating error, and this trigger is called mutating trigger, and table is called mutating table.
Mutating errors are not occurred in statement level trigger because through these statement level trigger when we are performing DML operations automatically data committed into the database, whereas in row level trigger when we are performing transaction data is not committed and also again we are reading this data from the same table then only mutating errors is occurred.
These are inserting, updating, deleting clauses. These clauses are used in statement, row-level trigger. These triggers are also called as trigger predicate clauses.
Syntax: If inserting then stmts; else if updating then stmts; else if deleting then stmts; end if; Q What is Discard File? Ans: This file extension is. Discard file also stores reflected record based on when clause condition within control file.
The output of the code in the slide shows the correct information:. 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: Comparisons involving nulls always yield NULL. In conditional control statements, if the condition yields NULL, its associated sequence of statements is not executed.
Programming Guidelines Make code maintenance easier by: Documenting code with comments Developing a case convention for the code Developing naming conventions for identifiers and other objects Enhancing readability by indenting. Code Conventions The following table provides guidelines for writing code in uppercase or lowercase characters to help distinguish keywords from named objects. Indenting Code For clarity, indent each level of code.
Indenting Code For clarity and enhanced readability, indent each level of code. To show structure, you can divide lines by using carriage returns and you can indent lines by using spaces and tabs. Compare the following IF statements for readability: True 2. A block can have any number of nested blocks defined within its executable part. Blocks defined within a block are called subblocks. You can nest blocks only in the executable part of a block.
Because the exception section is also a part of the executable section, it can also contain nested blocks. Ensure correct scope and visibility of the variables when you have nested blocks.
Avoid using the same identifiers in the parent and child blocks. Conversion functions convert a value from one data type to another. Comparison operators compare one expression with another. The relational operators enable you to compare arbitrarily complex expressions.
Practice 3: You learn the need for cursors and differentiate between the two types of cursors. The lesson also presents the various SQL cursor attributes that can be used with implicit cursors. Make changes to rows in the database by using DML commands.
You can use DML commands to modify the data in a database table. Just as a block can span multiple transactions, a transaction can span multiple blocks. DDL statements cannot be directly executed. These statements are dynamic SQL statements. Dynamic SQL statements are built as character strings at run time and can contain placeholders for parameters.
You can use dynamic SQL to execute them. Every value retrieved must be stored in a variable by using the INTO clause. 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, and the order of the variables must correspond with the items selected. A query that returns more than one row or no row generates an error. You learn about exception handling in the lesson titled Handling Exceptions. If your requirement is to retrieve multiple rows and operate on the data, you can make use of explicit cursors.
You are introduced to cursors later in this lesson and learn about explicit cursors in the lesson titled Using Explicit Cursors. The variables in the INTO clause must thus be in the same order. For instance, you cannot use group functions using the following syntax: Naming Ambiguities In potentially ambiguous SQL statements, the names of database columns take precedence over the names of local variables.
The example shown in the slide is defined as follows: Avoid using database column names as identifiers.
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. Database columns and identifiers should have distinct names.
The decision whether to update or insert into the target table is based on a condition in the ON clause. MERGE is a deterministic statement. That is, you cannot update the same row of the target table multiple times in the same MERGE statement. The data in the employees table needs to remain unchanged.
Updating Data: Example Increase the salary of all employees who are stock clerks. Recall that if column names and identifier names are identical in the WHERE clause, the Oracle Server looks to the database first for the name.
Deleting Data: Example Delete rows that belong to department 10 from the employees table. If the WHERE clause is not used, all the rows in a table can be removed if there are no integrity constraints.
Each row is inserted or updated in the target table depending on an equijoin condition. If a match is found, the row is updated to match the row in the employees table. There are two types of cursors: Declared explicitly by the programmer. The Oracle Server allocates a private memory area called the context area for processing SQL statements. 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. You have no control over this area because it is internally managed by the Oracle Server. A cursor is a pointer to the context area. However, this cursor is an implicit cursor and is automatically managed by the Oracle Server.
Types of Cursors There are two types of cursors: An implicit cursor is created and managed by the Oracle Server. You do not have access to it. 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. Observe that the attributes are prefixed with SQL.
Therefore, you use SQL instead of the cursor name. This attribute may be used as the exit condition in a loop. You learn about explicit cursor attributes in the lesson titled Using Explicit Cursors.
Print the number of rows deleted. You learn about exception handling later in the course. However, the DDL commands cannot be used directly. A cursor is a pointer to the memory area.
There are two types of cursors. Explicit cursors are declared by programmers. Practice 4: Selecting data from a table Inserting data into a table Updating data in a table Deleting a record from a table. You have also learned to include expressions and SQL statements in the executable block.
IF Statement Syntax: 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. Marks the end of an IF statement.
Therefore, the control never reaches the THEN clause. For example: There is no limitation on the number of conditional expressions. However, these statements must be related with appropriate logical operators. The output of the block is shown below the code. I am a child ' ; I am young ' ; I am in my twenties' ; I am in my thirties' ; I am always young ' ;. The example illustrates the following characteristics of these clauses: Conditions are evaluated one by one from the top. The final ELSE clause is optional.
In the example, the output of the block is shown below the code. In such a case, the control goes to the ELSE statement. Guidelines You can perform actions selectively based on conditions that are being met. When you write code, remember the spelling of the keywords: ELSIF is one word. END IF is two words.
Indent the conditionally executed statements for clarity. To select the result, the CASE expression uses expressions. The value returned by these expressions is used to select one of several alternatives. To return the result, the CASE expression uses a selector, which is an expression whose value is used to return one of several alternatives.
The selector is followed by one or more WHEN clauses that are checked sequentially. The value of the selector determines which result is returned. A searched CASE expression has no selector. CASE Expressions: This value is accepted from the user by using a substitution variable. The WHEN clause compared a value against this test expression. In searched CASE statements, you do not have a test expression. The same example is rewritten in this slide to show searched CASE statements.
Handling Nulls When you are working with nulls, you can avoid some common mistakes by keeping in mind the following rules: Simple comparisons involving nulls always yield NULL. If the condition yields NULL in conditional control statements, its associated sequence of statements is not executed. Handling Nulls Consider the following example: You may expect the sequence of statements to execute because x and y seem unequal.
But nulls are indeterminate. Whether or not x is equal to y is unknown. In the second example, you may expect the sequence of statements to execute because a and b seem equal. Logic Tables Build a simple Boolean condition with a comparison operator. Logic Tables You can build a simple Boolean condition by combining number, character, and date expressions with comparison operators. In the logic tables shown in the slide: Boolean Expressions or Logical Expression?
What is the value of flag in each case? The AND logic table can help you to evaluate the possibilities for the Boolean condition in the slide.
Answers 1. TRUE 2. FALSE 3. NULL 4. Iterative Control: There are three loop types: Loops are mainly used to execute statements repeatedly until an exit condition is reached. It is mandatory to have an exit condition in a loop; otherwise, the loop is infinite. Looping constructs are the third type of control structures. Basic loop that performs repetitive actions without overall conditions FOR loops that perform iterative actions based on a count WHILE loops that perform iterative actions based on a condition Note: An EXIT statement can be used to terminate loops.
A basic loop must have an EXIT. A basic loop allows execution of its statements at least once, even if the EXIT condition is already met upon entering the loop. Without the EXIT statement, the loop would be infinite.
The EXIT statement must be placed inside a loop. In the latter case, you can attach a WHEN clause to enable conditional termination of the loop. If the condition yields TRUE, the loop ends and control passes to the next statement after the loop. Basic Loop: Example The basic loop example shown in the slide is defined as follows: If the condition is placed in the loop such that it is not checked until after the loop statements execute, the loop executes at least once. However, if the exit condition is placed at the top of the loop before any of the other executable statements and if that condition is true, the loop exits and the statements never execute.
Results To view the output, run the code example: The condition is evaluated at the start of each iteration. Thus, it is possible that none of the statements inside the loop are executed.
If the condition yields NULL, the loop is bypassed and control passes to the next statement. Example In the example in the slide, three new location IDs for the CA country code and the city of Montreal are added.
If the number of iterations is less than or equal to the number 3, the code within the loop is executed and a row is inserted into the locations table.
Do not declare the counter; it is declared implicitly. The lower bound is still referenced first. Specifies the lower bound for the range of counter values Specifies the upper bound for the range of counter values. FOR Loops continued Note: The sequence of statements is executed each time the counter is incremented, as determined by the two bounds. The lower bound and upper bound of the loop range can be literals, variables, or expressions, but they must evaluate to integers.
The lower bound and upper bound are inclusive in the loop range. If the lower bound of the loop range evaluates to a larger integer than the upper bound, the sequence of statements is not executed. For example, the following statement is executed only once: FOR i IN FOR Loops: The example in this slide shows how to achieve the same by using the FOR loop.
Do not reference the counter as the target of an assignment. Neither loop bound should be NULL. The lower and upper bounds of a LOOP statement do not need to be numeric literals. They can be expressions that convert to numeric values. Suggested Use of Loops Use the basic loop when the statements inside the loop must execute at least once. Use a FOR loop if the number of iterations is known.
Suggested Use of Loops A basic loop allows the execution of its statement at least once, even if the condition is already met upon entering the loop. Use a FOR loop if the number of iterations is predetermined. Nested Loops and Labels You can nest loops to multiple levels. Use labels to distinguish between blocks and loops. Exit the outer loop with the EXIT statement that references the label. The termination of a nested loop does not terminate the enclosing loop unless an exception is raised.
However, you can label loops and exit the outer loop with the EXIT statement. Label names follow the same rules as the other identifiers. A label is placed before a statement, either on the same line or on a separate line. Nested Loops and Labels: Example In the example in the slide, there are two loops. The inner loop is nested within the outer loop.
Many other programming languages have this functionality. In some cases, the workarounds are less efficient. It may be more efficient than the previous coding workarounds.
The first assignment is executed for each of the 10 iterations of the loop. You can also use the CONTINUE statement within an inner block of code, which does not contain a loop as long as the block is nested inside an appropriate outer loop.
Basic loops that perform repetitive actions without overall conditions FOR loops that perform iterative actions based on a count WHILE loops that perform iterative actions based on a condition. Summary In this lesson, you should have learned to change the logical flow of statements by using the following control structures: Summary A language can be called a programming language only if it provides control structures for the implementation of business logic.
These control structures are also used to control the flow of the program. A conditional control construct checks for the validity of a condition and performs an action accordingly. You use the IF construct to perform a conditional execution of statements. An iterative control construct executes a sequence of statements repeatedly, as long as a specified condition holds TRUE.
You use the various loop constructs to perform iterative operations. Practice 5: Objectives You have already been introduced to composite data types.
In this lesson, you learn more about composite data types and their uses. Composite Data Types Can hold multiple values unlike scalar types Are of two types: Composite Data Types You learned that variables of the scalar data type can hold only one value, whereas a variable of the composite data type can hold multiple values of the scalar data type or the composite data type.
There are two types of composite data types: Records are used to treat related but dissimilar data as a logical unit. For example, you can define a record to hold employee details. By creating a record to store employee details, you create a logical collective unit. This makes data access and manipulation easier. Collections are used to treat data as a single unit. Collections are of three types: You have all the related data as a single unit. You can easily access and modify data.
Data is easier to manage, relate, and transport if it is composite. An analogy is having a single bag for all your laptop components rather than a separate bag for each component. Note that this data type can also be of the composite type such as records. You can define a collection to hold the first names of all employees. You may have stored n names in the collection; however, name 1 is not related to name 2. The relation between these names is only that they are employee names.
Each record defined can have as many fields as necessary. Fields without initial values are initialized to NULL. You can define RECORD types and declare user-defined records in the declarative part of any block, subprogram, or package. You can declare and reference nested records. One record can be the component of another record. To use them, perform the following steps: The syntax for defining the record is shown in the slide.
Declare and optionally initialize the internal components of this record type. Field1 data type Field2 data type Field3 data type.
To reference or initialize an individual field, use the dot notation: You can then assign a value to the record field: In a block or subprogram, user-defined records are instantiated when you enter the block or subprogram.
They cease to exist when you exit the block or subprogram. Fields in the record take their names and data types from the columns of the table or view. The variable has the same data type and size as the table column.
Also, if the variable is a number and is used in any calculations, you need not worry about its precision. The fields in the record take their names and data types from the columns of the table or view.
The record can also store an entire row of data fetched from a cursor or cursor variable. The slide shows the syntax for declaring a record. Is the name chosen for the record as a whole identifier reference Is the name of the table, view, cursor, or cursor variable on which the record is to be based The table or view must exist for this reference to be valid. This is not code, but simply the structure of the composite variable. Make sure that the column names appear in the same order as the fields in your record.
You can also assign one record to another if both have the same corresponding data types. Example The field declarations used in defining a record are like variable declarations. Each field has a unique name and a specific data type. Therefore, you must create the record type first, and then declare an identifier using that type. When the program is recompiled, it automatically reflects the new table format. In the absence of this attribute, you would be forced to declare a variable for each of the columns retrieved by the SELECT statement.
If an employee is retiring, information about that employee is added to a table that holds information about retired employees. The user supplies the employee number. The number of fields in the record must be equal to the number of field names in the INTO clause. You can use this record to insert values into a table. This makes the code more readable. No employee retires on the hire date.
The inserted record is shown in the slide. You will see how to update this in the next slide. This slide shows you how to update a row by using a record. The ROW keyword is used to represent the entire row. The code shown in the slide updates the leavedate of the employee. The record is updated as shown in the slide. This data type can also be of the composite type such as records. Therefore, collections are used to treat data as a single unit.
Of these three collections, the associative array is the focus of this lesson. These two collections are covered in detail in the course Oracle Database 11g: Primary key of integer or string data type Column of scalar or record data type.
It is a composite data type, and is user defined. Associative arrays are sets of key-value pairs. They can store data using a primary key value as the index, where the key values are not necessarily sequential. Associative arrays have only two columns, neither of which can be named: The first column, of integer or string type, acts as the primary key. The second column, of scalar or record data type, holds values.
Jones Smith Maduro Associative Array Structure As previously mentioned, associative arrays have two columns. The second column either holds one value per row, or multiple values. Unique Key Column: The data type of the key column can be: The value column can be either a scalar data type or a record data type.
A column with scalar data type can hold only one value per row, whereas a column with record data type can hold multiple values per row. Other Characteristics An associative array is not populated at the time of declaration. It contains no keys or values, and you cannot initialize an associative array in its declaration.
An explicit executable statement is required to populate the associative array. Like the size of a database table, the size of an associative array is unconstrained. That is, the number of rows can increase dynamically so that your associative array grows as new rows are added. Note that the keys do not have to be sequential, and can be both positive and negative. Steps to Create an Associative Array Syntax: Steps to Create an Associative Array There are two steps involved in creating an associative array: Declare a variable of that data type.
Is the name of the identifier that represents an entire associative array. Creating and Accessing Associative Arrays The key of each associative array is used to access an element in the array, by using the following syntax: Indexing does not need to start with 1. The exists i method returns TRUE if a row with index i is returned. Use the exists method to prevent an error that is raised in reference to a nonexistent table element.
INDEX BY Table of Records Option As previously discussed, an associative array that is declared as a table of scalar data type can store the details of only one column in a database table. However, there is often a need to store all the columns retrieved by a query. Creating and Referencing a Table of Records As shown in the associative array example in the slide, you can: If the table structure changes, the record structure also picks up the change.
Example 2 The example in the slide declares an associative array, using the INDEX BY table of records option, to temporarily store the details of employees whose employee IDs are between and Another loop is used to print the last names from the array.
Note the use of the first and last methods in the example. However, you can do the same more efficiently using cursors. Cursors are explained in the lesson titled Using Explicit Cursors.
The results of the code example is as follows:. Nested Tables The functionality of nested tables is similar to that of associative arrays; however, there are differences in the nested table implementation.
The nested table is a valid data type in a schema-level table, but an associative array is not. Therefore, unlike associative arrays, nested tables can be stored in the database. The size of a nested table can increase dynamically, although the maximum size is 2 GB. The key cannot be a negative value unlike in the associative array.
Though reference is made to the first column as key, there is no key in a nested table. There is a column with numbers. Elements can be deleted from anywhere in a nested table, leaving a sparse table with nonsequential keys. The rows of a nested table are not in any particular order. When you retrieve values from a nested table, the rows are given consecutive subscripts starting from 1. Nested Tables continued Example: If you do not initialize a nested table, it is automatically initialized to NULL.
You can initialize the offices nested table by using a constructor: The complete code example and output is as follows: You have to specify the upper bound when you declare them. This is similar to arrays in C language. Contrast that with nested tables, which are always stored out-of-line.
Summary of Collection Types Associative Arrays Associative arrays are sets of key-value pairs, where each key is unique and is used to locate a corresponding value in the array. The key can be either integer- or character-based. The array value may be of the scalar data type single value or the record data type multiple values.
Nested Tables A nested table holds a set of values. In other words, it is a table within a table. Nested tables are unbounded; that is, the size of the table can increase dynamically. Varrays Variable-size arrays, or varrays, are also collections of homogeneous elements that hold a fixed number of elements although you can change the number of elements at run time. They use sequential numbers as subscripts.