Oracle WebDB Tutorial Guide
Release 2.2

Part Number A77075-01

Library

Product

Contents

Index

Go to previous page Go to next page

2
Building and Browsing Database Objects

Estimated completion time: 20 minutes

This chapter shows you how to use Oracle WebDB to build and browse database objects. Database objects are structures used to store data in the database. You can create the following database objects in WebDB:

Icon  Database Object  Description 

 

Function 

A PL/SQL subprogram that performs a specified sequence of actions, and then returns a value. 

 

Index 

A structure associated with a table used to locate rows of the table quickly, and (optionally) to guarantee that every row is unique. 

 

Package 

A database object consisting of a specification and a body. The specification defines the procedures and functions that can be referenced by other program units. The body includes the actual implementation of the package. 

 

Procedure 

A PL/SQL subprogram that performs a specified sequence of actions. 

 

Sequence 

A database object used to automatically generate numbers for table rows. 

 

Synonym 

An alias assigned to a table, view, or other database object that can thereafter be used to refer to it. 

 

Table 

The basic storage structure in a relational database. Data is stored in columns and rows within a table. 

 

Trigger 

A stored procedure associated with a table and automatically executed before or after one or more specified events. 

 

View 

A virtual table whose rows do not actually exist in the database, but which is based on a table that is physically stored in the database. 

In this chapter, you'll learn how to build tables and procedures using Oracle WebDB's database object build wizards.


Note:

You can still build database objects using SQL CREATE commands if you prefer. WebDB simply provides an alternative tool for you to use. 


This chapter includes the following sections:

Section   

"Before You Begin" 

 

"Building a Table" 

 

"Building a Procedure" 

 

"Querying a Table" 

 

"Executing a Procedure" 

 

"What's Next?" 

 

2.1 Before You Begin

2.2 Building a Table

The Human Resources department has requested a table to store information about the company's employees.

  1. If you are not already on your WebDB home page, clickat the top right of the page.

  2. Click Build.


    Note:

    If you cannot see the Build link, you are probably not a WebDB Developer. Ask your DBA to add you to the WEBDB_DEVELOPER role. 


  3. Click Database Objects to display the Build Database Objects page.

    The page displayed contains the Create a Database Object panel.


    Tip:

    You can also clickin the toolbar at the bottom of any page to display the Build Database Objects page. 


  4. Clickto display the Create Table Wizard.

  5. In the Schema list, choose your own schema.


    Note:

    Your schema name is the same as your user name.

    If your own schema is not listed, you have probably not been granted Build In privileges on it. Ask your DBA to grant these privileges to you. 


  6. In the Table Name field, type TUTORIAL_TABLE.

  7. Clickto display the Columns page.


    WARNING:

    Clickif you need to go back to a previous page of the wizard. Do not click your browser's Back button. 


  8. Complete the column details using the information in the following table.

    Column Name  Datatype  Length  Precision  Null?  Primary Key? 

    EMPNO 

    NUMBER 

    10 

    <No value> 

    Uncheck 

    Check 

    ENAME 

    VARCHAR2 

    20 

    <No value> 

    Check 

    Uncheck 

    JOB 

    VARCHAR2 

    20 

    <No value> 

    Check 

    Uncheck 

    SAL 

    NUMBER 

    10 

    <No value> 

    Check 

    Uncheck 

    DEPTNO 

    NUMBER 

    10 

    <No value> 

    Check 

    Uncheck 

  9. Clickto display the Storage page.

  10. You can accept the default values on this page. Clickto display the Create Table page.

  11. Click OK to create the table and return to the Build Database Objects page.

2.3 Building a Procedure

The Human Resources department has requested a procedure to increase the salaries of the employees in a specified department by a specified percentage.

  1. Clickto display the Create Procedure Wizard.

  2. In the Schema list, choose your own schema.

  3. In the Procedure Name field, type TUTORIAL_PROC.

  4. Clickto display the Arguments page.

  5. Complete the argument details using the information in the following table.

    Argument Name  In/Out  Datatype  Default 

    P_DEPTNO 

    IN 

    NUMBER 

    <No value> 

    P_PCT_RAISE 

    IN 

    NUMBER 

    <No value> 

  6. Clickto display the Create Procedure page.

  7. In the Procedure Body field, change the text so it reads:

    BEGIN
        UPDATE TUTORIAL_TABLE
        SET SAL = ((P_PCT_RAISE/100)+1)*SAL
        WHERE DEPTNO = P_DEPTNO;
    END;
    
    
  8. Clickto display the Create Procedure page.

  9. Click OK to create the procedure and return to the Build Database Objects page.

2.4 Querying a Table

So far you have created a table (TUTORIAL_TABLE) and a procedure (TUTORIAL_PROC). Now you need to test these objects to make sure they meet the requirements of the Human Resources department. First, you need to find the table in the database.

  1. In the toolbar at the bottom of the page, clickto display the Browse Database page.

    The page displayed contains the Find Database Objects panel.


  2. In the Schema field, type the name of your own schema (the schema where you created the table).


    Tip:

    Clickto display a list of schemas if you don't know the name of the schema you want to browse. 


  3. In the Type list, choose Tables.

  4. Click Browse to list all the tables in your own schema.


    Note:

    To browse database objects in a schema other than your own, you must have been granted Browse In privileges to do so by the DBA. If you have Build In privileges on a schema, you automatically have Browse In privileges on that schema. 


  5. Click Tutorial_Table to display the Query and Update Table page.

    The form on the Query and Update page is divided into three sections. The first section lists the columns in the table. You can type values in the fields to specify query criteria or to add new data. The second and third sections of the form enable you to specify the way the query results are displayed. For example, you can order the results by a specified column, or download the results and display them in Microsoft Excel.


    TUTORIAL_TABLE does not currently contain any data. Let's insert a row so that you have some test data to work with.

  6. Use the information in the following table to complete the first section of the Query and Update Table form.

    Column  Value 

    EMPNO 

    1 

    ENAME 

    KING 

    JOB 

    PRESIDENT 

    SAL 

    5000 

    DEPTNO 

    10 

  7. Click Insert New Row to add the data to your table.


  8. Click Browse Table in the link history to return to the Query and Update Table page.

  9. Click Query to take a look at the data you just added.


  10. At the top right of the page, click Return to Table to return to the Query and Update Table page.

  11. Let's add some more data. Use the information in the following table to add more rows to TUTORIAL_TABLE. After completing the form for each row, click Insert New Row, and then Browse Table to return to the form to add the next row.

    EMPNO  ENAME  JOB  SAL  DEPTNO 

    BLAKE 

    MANAGER 

    3000 

    30 

    JONES 

    MANAGER 

    3500 

    20 

    ALLEN 

    SALESMAN 

    1500 

    30 

    CLARK 

    MANAGER 

    2500 

    10 

    MILLER 

    CLERK 

    1000 

    10 

    SCOTT 

    ANALYST 

    2000 

    20 

  12. After you have finished, in the Query and Update Table page, click Query again to review all the data.


    You can specify values to restrict the results of the query. For example, you might only want to display employee details for those employees who earn more than 3000.

  13. Click Return to Table.

  14. In the field for the SAL column, type >3000.

  15. Click Query to display all those employees with a salary greater than 3000.


    You can also restrict which columns to query and control how the query results are displayed. For example, you might only want to see the name and salary of employees in Department 10.

  16. Click Return to Table.

  17. Uncheck EMPNO, JOB, and DEPTNO.

    ENAME and SAL should be checked.

  18. In the field for the DEPTNO column, type 10.

  19. In the Order By list, choose SAL.

  20. In the Query Options list, choose Display Results in Table with Borders.

  21. Click Query to display all those employees in Department 10. Make a note of this information, it will be useful to refer to later in this chapter.


2.5 Executing a Procedure

Now that you have added some data to TUTORIAL_TABLE, let's test TUTORIAL_PROC.

  1. In the toolbar at the bottom of the page, clickto display the Browse Database page.

  2. In the Schema field, type the name of your own schema (the schema where you created the procedure).

  3. In the Type list, choose Procedures.

  4. Click Browse to list all the procedures in your own schema.

  5. Click Tutorial_Proc to display the Execute Procedure page. This is where you provide values for the arguments defined in the procedure.


  6. In the field for the P_DEPTNO argument, type 10.

  7. In the field for the P_PCT_RAISE argument, type 5.

  8. Click Execute.

  9. Theimage indicates that the procedure executed successfully.

    Now let's take another look at TUTORIAL_TABLE to check the results of the procedure.

  10. Click your browser's Back button to return to the Execute Procedure page.

  11. In the toolbar at the bottom of the page, clickto display the Browse Database page.

  12. In the Schema field, type the name of your own schema.

  13. In the Object name field, type TUTORIAL%.

  14. Click Browse to list all the objects in your own schema that start with "tutorial".

  15. Click Tutorial_Table to display the Query and Update Table page.

  16. Click Query to view all the data in the table.


  17. Compare the salaries of the employees in Department 10 (KING, CLARK, and MILLER) with those that you noted down at the end of the previous exercise. Executing TUTORIAL_PROC has increased them by 5%.

2.6 What's Next?

In this chapter, you learned how to:

In Chapter 3, "Building Components", you will build components based on the TUTORIAL_TABLE table you created in this chapter.


Go to previous page Go to next page
Oracle
Copyright © 1996-2000, Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index