Oracle database 11g pl/sql programming free pdf

Updates reflect the latest Oracle 11g release with the most recent figures and examples using the user-friendly SQL Developer tool. Important Notice: Media content referenced within the product description or the product text may not be available in the ebook version.

The book teaches the basics of the Oracle database from a beginner s perspective to the advanced concepts using a hands-on approach. Each and every concept has been elaborated with suitable practical examples along with code for clear and precise understanding of the topic. In the process, it discusses the various data types and built-in functions of Oracle, as well as the sorting of records and the table operations. 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.

Commit m y before the end of the block. FOR i IN Modify the block that you created in exercise 2 to retrieve all information about each I table of records. 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.

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.

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. 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.

To install SQL Developer, you need an unzip tool. Unzip the downloaded SQL Developer kit into the folder created in step 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. You can customize many aspects of the appearance and behavior of SQL Developer by setting preferences. 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.

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: n - t e r U s Basic: 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. TNS: 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. 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.

You can use bind variables in the SQL statements, but not substitution a e 2. Commit: Writes any changes to the database and ends the transaction I n 4. Rollback: Discards any changes to the database, without writing them to the database, and ends the transaction l e 5. Cancel: Stops the execution of any statements currently being executed c 6.

Execute Explain Plan: 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.

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. 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. 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. 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: r a 1. 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: n a e 1. From the Tools menu, select External Tools. In the External Tools dialog box, select New to add new tools. Therefore, users will no longer be able to connect to an Oracle 8. Create a folder.

Note: As with version 1. 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: r a O ly 1.

Enter the username, password, and database name. Log on to your machine. Enter the sqlplus command shown in the slide. In the syntax: I n username Your database username c l e password Your database password Your password is visible if you enter it here. O Instead, enter only your username. Enter your password at the password prompt.

The result c must contain data. In the result: r a Null? 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. Create a deployment profile. Deploy the objects. 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: a debugger and a debuggee, which may reside on a different platform.

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. Expand the Schema node. Expand the appropriate node depending on what you are debugging: procedure, function, or package body. Optional for debugging only Select the function, procedure, or package that you want to debug and double-click to open it in the Code Editor. Note: The breakpoint must be set on an executable line of code.

If the debugger does not stop, the breakpoint may have not been set on an executable line of code verify that the breakpoint was set correctly. Make sure that either the Code Editor or the procedure in the Navigator is currently selected. Click the Debug toolbar button, or, if you want to run without debugging, click the Run toolbar button. In some cases, you may need to write additional code to initialize values to be passed as arguments.

Click OK to execute or debug the target. Analyze the output information displayed in the Log window. U displayed. I n In the case of functions, the return value is displayed. It is a trigger in r a a program that, when reached, pauses program execution allowing you to examine the values of some or all of the program variables. By setting breakpoints in potential problem areas of your O ly source code, you can run your program until its execution reaches a location you want to debug.

You can then use the debugger to a e view the state of your program. Breakpoints are flexible in that they can be set before you begin n t e r U s a program execution or at any time while you are debugging. To set a breakpoint in the Code Editor, click the left margin next to a line of executable code.

I n Breakpoints set on comment lines, blank lines, declaration, and any other non-executable lines of code are not verified by the debugger and are treated as invalid. The debugger pauses r a the program execution at the point where the breakpoint is set. At this point, you can check the values of the variables. You can continue with the program execution by clicking the Resume O ly icon.

The debugger will then move on to the next breakpoint. After executing all the Debugging — Log area. You can modify program data values during a debugging session as a way to test hypothetical bug fixes during a program run. You use the Data window to display information about variables in your program.

The Data n a e window displays the arguments, local variables, and static fields for the current context, which is t e r U s controlled by the selection in the Stack window. If you move to a new context, the Data window is updated to show the data for the new context. If the current program was compiled without I n debug information, you will not be able to see the local variables.

After you enter a watch expression, the Watch window displays the current value of the expression. As your program runs, the value of the watch changes as your program updates the O ly values of the variables in the watch expression. It displays the call stack r a for the current thread. When you select a line in the Stack window, the Data window, Watch window, and all other windows are updated to show data for the selected class.

Thus, declaring a cursor variable creates a pointer, not an item. However, cursors differ from cursor variables the way constants differ from variables. A cursor n a e is static, but a cursor variable is dynamic because it is not tied to a specific query. You can open t e r U s a cursor variable for any type-compatible query. This gives you more flexibility. You can pass cursor variables back and forth between an application and server through remote procedure calls O RPCs.

Therefore, you can pass the value of a cursor variable freely from one scope to another. For example, you can declare a cursor variable on the client side, open and fetch c l e from it on the server side, then continue to fetch from it back on the client side. A cursor variable holds a reference to the cursor work area in the PGA instead of addressing it O with a static name.

Because you address this area by a reference, you gain the flexibility of a variable. Unlike packaged variables, cursor variables do not have persistent states. Remember, declaring a cursor variable creates a pointer, not an item.

Cursor variables cannot be saved in the database; they follow the usual scoping and instantiation rules. END; Any bind arguments in the query are evaluated only when the cursor variable is opened. Thus, to fetch rows from the cursor using different bind values, you must reopen the cursor variable with the bind arguments set to their new values.

You can use a different l e INTO clause on separate fetches with the same cursor variable. Each fetch retrieves another row c from the same result set. After that, the associated result set is undefined. In these practices, you apply the concepts that you learned in the course. These additional practices provide supplemental practice in declaring variables, writing executable statements, interacting with the Oracle server, writing control structures, and working with composite data types, cursors, and handle exceptions.

Evaluate each of the following declarations. Determine which of them are not legal and explain why. In each of the following assignments, determine the data type of the resulting expression.

For the exercises below, you must create a temporary table to store the results. Store a department number in a substitution variable. In the executable part of the program, do the following: a. Store an employee name in a substitution variable. Update the department ID of the employee with the new department number, and e c l update the salary with the new salary.

Use the emp table for the updates. Process each row from the cursor, and if the salary is greater than 15, and the hire date is later than FEB, display the employee name, salary, and hire date in the window in the format shown in the sample output below: Pass a parameter of the DATE data type to the cursor and print the details of all the employees who have joined after that date.

Use the EMP table for this practice. Verify the results by querying the emp table. You must have JavaScript enabled in your browser to utilize the functionality of this website. A Promo Code is an alpha-numeric code that is attached to select promotions or advertisements that you may receive because you are a McGraw-Hill Professional customer or e-mail alert subscriber.

Printed books. Find out more about our formats. Responding To A Promotion? View Promotion. More Views. Check out the new look and enjoy easier access to your favorite features. Try it now. No thanks. Get print book. Shop for Books on Google Play Browse the world's largest eBookstore and start reading today on the web, tablet, phone, or ereader.

Go to Google Play Now ». Joan Casteel.