Oracle Database PL/SQL User's Guide and Reference 10g Release 2 () Oracle, JD Edwards, PeopleSoft, and Retek are registered trademarks of Oracle. Oracle Database PL/SQL Language Reference, 11g Release 2 () . PL/SQL Feature for Oracle Database 11g Release 2 (). Oracle Database PL/SQL Language Reference, 11g Release 1 () Oracle Corporation and its affiliates disclaim any liability for any.
|Language:||English, Japanese, Hindi|
|ePub File Size:||18.32 MB|
|PDF File Size:||10.73 MB|
|Distribution:||Free* [*Registration needed]|
Oracle Database SQL Language Reference, 11g Release 2 () . Oracle Database 11g Release 2 () New Features in the SQL. Product 6 - 33 Oracle University and BUSINESS SUPPORT LTDA use only. THESE eKIT .. major application development environments such as PL/SQL, Java/JDBC,.NET and . generated in multiple formats such as PDF, HTML, Excel. Record 6 - 14 I-9 Coding PL/SQL in SQL*Plus I Coding PL/SQL in Oracle JDeveloper I Starting SQL Developer and Creating a Database Connection I
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. Number having precision p and scale s. The precision p can range from 1 through The following example shows how to use [ as a delimiter: 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.
Composite data types: You learn about host variables later in this lesson. Types of Variables continued The slide illustrates the following data types: TRUE represents a Boolean value. The image represents a BLOB. Use meaningful identifiers for variables. Initialize variables with the assignment operator: 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.
Getting Started With PL/SQL
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: 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. Number having precision p and scale s.
The precision p can range from 1 through The scale s can range from 84 through Base type for integers between 2,,, and 2,,, Base type that stores one of the three possible values used for logical calculations: It requires 5 bytes to store the value. Represents floating-point number in IEEE format.
It requires 9 bytes to store the value. DATE values include the time of day in seconds since midnight. The range for dates is between B. To specify the precision, you must use an integer in the range 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 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 symbolic constant or variable to specify the precision; you must use an integer literal in the range The defaults are 2 and 6, respectively. Declaring Scalar Variables Examples: Declaring Scalar Variables The examples of variable declaration shown in the slide are defined as follows: Flag to indicate whether a piece of data is valid or invalid; initialized to TRUE.
A database column definition Another declared variable. If you have to design large subprograms, this can be time consuming and error prone. If you refer to a previously declared variable, prefix the variable name of the previously declared variable to the variable being declared.
You can avoid hard coding the data type of a variable. You need not change the variable declaration if the column definition changes. This ensures that such a variable is always compatible with the column that is used to populate it.
Oracle Database 11g, PL SQL Fundamentals, Student Guide
Declare variables to store the balance of a bank account, as well as the minimum balance, which is 1, Arithmetic, character, and date expressions can be used to return a Boolean value. These 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 a table that are affected by the statement.
In a procedural statement, Boolean expressions are the basis for conditional control. NULL stands for a missing, inapplicable, or unknown value. Declare and initialize a Boolean variable: A database column can be of 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 wave forms of up to terabytes depending on the database block size. LOB data types allow efficient, random, piecewise access to data and can be attributes of an object type.
The character large object CLOB data type is used to store large blocks of character data in the database. The binary large object BLOB data type is used to store large unstructured or structured binary objects in the database. When you insert or retrieve such data into or from the database, the database does not interpret the data. External applications that use this data must interpret the data.
They could be operating system files. Composite Data Types: Records and Collections As mentioned previously, a scalar data type holds a single value and has no internal components. You access each field with this syntax: A record variable can hold a table row, or some columns from a table row. Each record field corresponds to a table column. You access each element by its unique subscript.
Lists and arrays are classic examples of collections. Working with Composite Data Types. Bind Variables Bind variables are: Bind Variables Bind variables are variables that you create in a host environment. For this reason, they are sometimes called host variables. Therefore, bind variables are accessible even after the block is executed. When created, bind variables can be used and manipulated by multiple subprograms.
A bind variable is an environment variable, but is not a global variable. 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 user has to specify the size so that the required number of bytes can be allocated. Referencing Bind Variables Example: Then, the output of the SQL statement follows: Example In the code example: When a valid employee number is enteredin this case the output of the bind variable is automatically printed.
The bind variable contains the salary for the employee number that is provided by the user. 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 to the definition of another declared variable 4. Is prefixed with the database table and column name or the name of the declared variable. Also, if the variable is used in any calculations, you need not worry about its precision. You learn about this attribute in the lesson titled Working with Composite Data Types.
In this lesson, you learned how to declare variables in the declarative section. 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 executable section and also how to use them and manipulate the values of variables.
Practice 2: 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 learned to identify valid and invalid identifiers. Recall that keywords cannot be used as identifiers.
1Z Oracle PLSQL Developer Certified Associate Exam Dumps
Quoted identifiers: All subsequent usage of these variables should have double quotation marks. However, use of quoted identifiers is not recommended. Delimiters are symbols that have special meaning. Therefore, ; is an 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, 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 achieve.
Therefore, there should be a way in which you can indicate that these instructions need not be compiled. Comments are mainly used for this purpose. Any instruction that is commented is not interpreted by the compiler. Numbers can be simple values or in scientific notation. Numeric literals can be represented either by a simple value for example, Right-click the active SQL Worksheet and, in the shortcut menu that appears, select the Format option as shown in example 2.
Commenting Code Prefix single-line comments with two hyphens Commenting Code You should comment code to document each phase and to assist debugging. For multiline comments, you can either precede each comment line with two hyphens, or use the block comment format.
Comments are strictly informational and do not enforce any conditions or behavior on the logic or data. Well-placed comments are extremely valuable for code readability and future code maintenance. Single-row functions. The following functions are not available in procedural statements: The functions mentioned here are only a subset of the complete list. Examples You can use SQL functions to manipulate data.
These functions are grouped into the following categories: Number Character Conversion Date Miscellaneous. Before 11g: 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 method created a usability problem. In Oracle Database 11g, the limitation of forcing you to write a SQL statement to retrieve a sequence value is eliminated. With the sequence enhancement feature: Sequence usability is improved The developer has to type less The resulting code is clearer.
Data Type Conversion Converts data to comparable data types Is of two types: Implicit conversion Explicit conversion. Data Type Conversion In any programming language, converting one data type to another is a common requirement. Data type conversions can be of two types: Implicit conversions: Implicit conversions can be between: Characters and numbers Characters and dates Oracle Database 11g: Data Type Conversion continued Explicit conversions: To convert values from one data type to another, use built-in functions.
An exception section can contain nested blocks. You can nest blocks wherever an 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 requirements, you can divide the executable section into smaller blocks. The exception section can also contain nested blocks.
Nested Blocks: Nested Blocks continued The example shown in the slide has an outer parent block and a nested child 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. 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. The scope of a variable is the portion of the program in which the variable is declared and is accessible.
The visibility of a variable is the portion of the program where the variable can be accessed without using a qualifier. 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 can print the fathers name, the childs name, and the date of birth. Only the childs date of birth can be printed here because the fathers date of birth is not visible. The fathers date of birth is visible in the outer block and, therefore, can be printed. 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. Therefore, the fathers date of birth and the childs date of birth can both be printed from within the inner block.
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. 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.
with the full PL/SQL support and MS Word template engine
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.
I n SQL Developer can be downloaded with the following packaging options: To install SQL Developer, you need an unzip tool. Download the SQL Developer kit from http: Unzip the downloaded SQL Developer kit into the folder created in step 1. SQL Developer 1. The installation kit for l e SQL Developer 1. You may use either version of SQL c Developer in this course. Instructions for installing SQL Developer version 1. By using this tab, you can run predefined reports or create and add your own reports.
You can customize many aspects of the appearance and behavior of SQL Developer by setting preferences. The following menus contain standard t e r U s entries, plus entries for features specific to SQL Developer: To use SQL Developer, you must have at least one database connection, which may be existing, created, or imported. O ly You can create and test connections for multiple databases and for multiple schemas. On Windows, if the tnsnames. You can export connections to an XML file so that you can reuse it later.
Enter the O ly username and password of the schema that you want to connect to. You can select the connection type as: In this type, enter hostname and SID for the database you want to connect to. Port is already set to Or you can also choose to enter the Service - I n name directly if you use a remote database connection.
You can select any one of the database aliases imported from the c l - e tnsnames. Click Test to ensure that the connection has been set correctly. So, after you close the SQL Developer connection and open it again, you are not prompted for the password.
The connection gets added in the Connections Navigator. You can expand the connection to view the database objects and view object definitions, for example, dependencies, details, statistics, and so on. However, these connections are read-only connections that enable you to browse objects and data in that data source.
You can customize many aspects of the appearance of SQL Developer by setting preferences. For example, if you select a table in the Navigator, the details about columns, t U constraints, grants, statistics, triggers, and so on are displayed on an easy-to-read tabbed page. Expand the Connections node in the Connections Navigator. Expand Tables. By default, the Columns tab is selected. It shows the column O description of the table.
Using the Data tab, you can view the table data and also enter new rows, update data, and commit these changes to the database. Alternatively, you can create objects using the context menus.
When created, you can edit the objects using an edit dialog or one of the many context-sensitive menus. O ly As new objects are created or existing objects are edited, the DDL for those adjustments is l one or more objects in the schema.
To open a dialog box for creating e r s a new table, right-click Tables and select New Table. The dialog boxes to create and edit t U database objects have multiple tabs, each reflecting a logical grouping of properties for that type of object.
Example e A c l In the Create Table dialog box, if you do not select the Advanced check box, you can create a r a table quickly by specifying columns and some frequently used features. If you select the Advanced check box, the Create Table dialog box changes to one with multiple O ly options, in which you can specify an extended set of features while you create the table.
In the Connections Navigator, right-click Tables. In the Create Table dialog box, select Advanced. Specify column information. Click OK. Sometimes, you may want to edit the table that you have created; to do so, right-click the table in the Connections Navigator and select Edit. Executes the statement where the cursor is located in the Enter SQL variables.
You can use bind variables in the SQL statements, but not substitution a e 2.
Writes any changes to the database and ends the transaction I n 4. Discards any changes to the database, without writing them to the database, and ends the transaction l e 5. Stops the execution of any statements currently being executed c 6. Displays a dialog box with information about SQL statements that you have r a executed O 7.
Generates the execution plan, which you can see by clicking the Explain tab 8. Enter SQL statements. Results are shown here. Commands not n a e supported by the SQL Worksheet are ignored and are not sent to the Oracle database.
You can display a SQL Worksheet by using any of the following two options: Alternatively, e r s In the example in the slide, because there are multiple SQL statements, the first statement is t U I n terminated with a semicolon. The cursor is in the first statement, and therefore, when the statement is executed, results corresponding to the first statement are displayed in the Results box. You can have multiple files open at the same time.
Each file displays as a Script Pathing I n l e You can select a default path to look for scripts and to save scripts. The Open O ly 2. Method 2 Use the command followed by the location and name of the file you want to execute, and click the Run Script icon.
Enter SQL Statement window. Use the command, followed by the location, and name of the file you want to run, in the 2. After formatting, the SQL code is beautified with the l a e keywords capitalized and the statement properly indented. When you place your cursor here, it shows the Snippets window. From the drop-down list, you can select the functions category you want.
SQL Developer has the feature called Snippets. You can drag snippets into the Editor window. You can use the drop-down list to select a group. A Snippets button is placed in the right window margin, so that you can display the e r s Snippets window if it becomes hidden. Then you can edit the syntax so that the SQL function is valid in the current context.
To see a brief description O ly of a SQL function in a tool tip, place the cursor over the function name. Provide the location of the sqlplus. Using the Debug menu r a options, you can perform the following debugging tasks: You cannot resume execution from this point; instead, to start running or debugging from the beginning of the function or procedure, l e click the Run or Debug icon in the Source tab toolbar. O These options are also available as icons in the debugging toolbar.
These reports can be grouped into the following categories: Individual reports are r a displayed in tabbed panes at the right side of the window; and for each report, you can select O using a drop-down list the database connection for which to display the report.
For reports about objects, the objects shown are only those visible to the database user associated with the selected database connection, and the rows are usually ordered by Owner. You can also create your own user-defined reports. Organize reports in folders. To create a user-defined report, perform the following steps: Then, click Apply. You can also include an optional tool tip to be displayed I n when the cursor stays briefly over the report name in the Reports navigator display.
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. Information about user-defined reports, including any folders for these reports, is stored in a file named UserReports. Also, you have shortcut icons to some of the frequently used tools such as Notepad, Microsoft Word, and Dreamweaver, available to you.
To do so, perform the following: From the Tools menu, select External Tools. In the External Tools dialog box, select New to add new tools.
O ly The preferences are grouped into the following categories: Therefore, users will no longer be able to connect to an Oracle 8. Create a folder. For example: By using this tab, you can browse database objects and users to which you have access.
Identified by the Reports icon, this tab enables you to run predefined reports s or create and add your own reports.
As with version 1. Contains the Run File and Execution Profile options that are relevant when a function or procedure is selected, and also debugging options. Provides integrated support for the following versioning and source control systems: The Run menu also contains options that are relevant when a function or procedure is selected for debugging.
These are the same options that are found in the Debug menu in version 1. Using SQL r a Developer, you can browse, create, and edit database objects. SQL Developer enables you to create and save your own special set of reports for repeated use. Version 1. Oracle SQL contains many extensions. It contains its own command language. To log in from a Windows environment: Enter the username, password, and database name.
Log on to your machine. Enter the sqlplus command shown in the slide. I n username Your database username c l e password Your database password Your password is visible if you enter it here. To ensure the integrity of your password, do not enter it at the operating system prompt. O Instead, enter only your username. Enter your password at the password prompt.
The result c must contain data. In the result: The SQL prompt then appears. The new current line is displayed. The newly edited line is a e displayed.
Verify the new contents of the buffer by using the LIST command. In this case, replace the employees table with the departments table. The default extension is. GET filename [. The default extension for the file name is. OFF closes the spool file. OUT closes the spool file and sends the file results to the printer. In this way, you can store frequently used scripts for use in the future.
You can also, alternatively, use the symbol to run a script. This opens an editor with the script file in it. The delimiter must be the first character of a l a e new line immediately following the statement. You can now append to, or replace an existing file, where previously you could only use SPOOL to create and replace a file. O ly To spool output generated by commands in a script without displaying the output on the screen, run interactively.
It supports every stage of the software development life cycle SDLC from modeling to deploying. This innovative approach makes J2EE development simple and efficient. A connection serves several O ly purposes from browsing the database and building applications, all the way through to deployment.
The Applications - Navigator provides an infrastructure that the different extensions can plug into and use to organize their data and menus in a consistent, abstract manner.
The raw files that make a e up these abstract nodes appear in the Structure window. O ly In the Structure window, you can view the document data in a variety of ways. When the current selection is a node in the navigator, the default editor is assumed. The tabs at the top of the editor window are the document tabs. Selecting a document tab gives O ly that file focus, bringing it to the foreground of the window in the current editor. Selecting an l a e editor tab opens the file in that editor.
Create a database connection.
Oracle Database 11g, PL SQL Fundamentals, Student Guide
Create a deployment profile. Deploy the objects. Deploying to the database uses the information provided in the Deployment Profile Wizard and two Oracle Database utilities: Expand Database and select a database connection. In the connection, expand a schema.
Right-click a folder corresponding to the object type Procedures, Packages, Functions. Enter a valid name for the function, package, or procedure and click OK. A skeleton definition is created and opened in the Code Editor. You can then edit the I n subprogram to suit your need. You may need to change the NULL values with reasonable values that are passed into the program unit. After you change the values, click OK. The output is displayed in the Message-Log window.
The Drop Confirmation dialog a box appears; click Yes. The object is dropped from the database. A local debugging session is started by r a setting breakpoints in source files, and then starting the debugger. Remote debugging requires two JDeveloper processes: This option can be enabled using various ways. For example, you can specify parameters being passed or t e r details about what was tested. U s return values from a function giving you more control over what is run and providing you output I n Note: The procedures or functions in the Oracle database can be either stand-alone or within a package.
Create a database connection using the Database Wizard. In the Navigator, expand the Database node to display the specific database username and schema name.However, you can do the same more efficiently using cursors. These reports can be grouped into the following categories: Time-out occurred while the Oracle server was waiting for a resource. You can pass parameters to the cursor that is used in a cursor FOR loop: Syntax e A c l The slide shows the syntax for creating a function.
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.
Practices are intended to cover most of the topics that are presented in the corresponding lesson. If you do not initialize a nested table, it is automatically initialized to NULL.