In this chapter, you will create and use the types of database objects that were discussed in 'Querying and Manipulating Data'.
Self-organizing and mostly autonomous: Providing AI-backed data mining and analysis capabilities, PL SQL DEVELOPER KEY GENERATOR for Mac identifies relevant data and presents trends and projections with very minimal user intervention. In Oracle Sql developer 11g, how do I generate a random integer and assign it to a variable? This is what I've tried so far: STB:= SELECT dbmsrandom.value(1,10) num FROM dual; With this code I.
Note that the statements CREATE TABLE,ALTER TABLE, DROP TABLE, and so on, use an implicit commit, and cannot be rolled back.
This chapter contains the following sections:
Data types associate a set of properties with values so you can use these values in the database. Depending on the data type, Oracle Database can perform different kinds of operations on the information in the database. For example, it is possible to calculate a sum of numeric values but not characters.
Oracle Database supports many kinds of data types, including the most common VARCHAR2(length), NUMBER(precision, scale), DATE, and also CHAR(length), CLOB, TIMESTAMP, and others. As you create a table, you must specify data types for each of its columns and (optionally) indicate the longest value that can be placed in the column.
Some of the data types and their properties you will use here include the following:
The VARCHAR2 stores variable-length character literals, and is the most efficient option for storing character data. When creating a VARCHAR2 column in a table, you must specify the maximum number of characters in a column, which is a length between 1 and 4,000. In the employeestable, the first_name column has a VARCHAR(20) data type and the LAST_NAME column has a VARCHAR2(25) data type.
An option to the VARCHAR2 data type, NVARCHAR2 stores Unicode variable-length character literals.
The CHAR data type stores fixed-length character literals; it uses blanks to pad the value to the specified string length, which is between 1 and 2,000.
An option to the CHAR2 data type, NCHAR stores Unicode fixed-length character literals.
The CLOB data type is a character large object data type that contains single-byte or multibyte characters. The maximum size of a CLOB is (4 gigabytes - 1) x (database block size).
The NUMBER data type stores zero, and integers and real numbers as positive and negative fixed numbers with absolute values between 1.0 x 10-130 and 1.0 x 10126 using a fixed-point or floating-point format, with decimal-point precision. Oracle guarantees that NUMBER data types are portable between different operating systems, and recommends it for most cases where you need to store numeric data.
You can use the precision option to set the maximum number of digits in the number, and the scale option to define how many of the digits are to the right of the decimal separator. In the employeestable, the salary column is defined as NUMBER(8,2), providing 6 digits for the primary unit of currency (dollars, pounds, marks, and so on) and 2 digits for the secondary unit of currency (cents, pennies, pfennigs, and so on).
For floating-point numbers, Oracle Database provides the numeric BINARY_FLOAT and BINARY_DOUBLE data types as enhancements to the basic NUMBER data type. BINARY_FLOAT (32-bit IEEE 754 format) ranges in absolute value between 1.17549 x e-38F and 3.40282 x e38Fand BINARY_DOUBLE (64-bit IEEE 754 format) ranges in absolute value between 2.22507485850720 x e-308 and 1.79769313486231 x e308. Both use binary precision that enables faster arithmetic calculations and often reduces storage requirements.
The DATE data type stores point-in-time values, dates and times; this includes the century, year, month, day, hours, minutes, and seconds. The valid date range is from January 1, 4712 BC to December 31, 9999 AD. Oracle Database supports many different formats for displaying date and time values. In the employeestable, the hire_date column is defined as a DATE.
The TIMESTAMP data type stores values that are precise to fractional seconds, and is therefore useful in applications that must track event order.
The TIMESTAMP WITH TIME ZONE data type stores time zone information, and can therefore record date information that must be coordinated across several geographic regions.
Tables are the basic unit of data storage in an Oracle database, and hold all user-accessible data. Tables are two-dimensional objects made up of vertical columns that represent the fields of the table and horizontal rows that represent the values for each record in the table.
In this section, you will create all the necessary tables and other schema objects to implement an employee performance evaluation process for the existing hr schema.
To implement the employee evaluation process, you will need to establish three tables, performance_parts, evaluations, and scores.
The performance_partstable lists the categories of performance measurements, and the relative weight for each item.
The evaluationstable will contain the employee's information, evaluation date, and the job, manager and department at the time of evaluation. You must preserve this information in this table because at any point in the future, the employee may change job designation, manager, or department.
The scorestable contains the scores assigned to each performance category for each evaluation.
To create a table using SQL Developer interface:
You will create the performance_partstable using the SQL Developer graphical interface.
In the Connections navigation hierarchy, click the plus sign (+) next to hr_conn to expand the list of schema objects.
Right-click Tables.
Select New Table.
In the Create Table window, enter the following information:
For Schema, select HR.
For Name, enter PERFORMANCE_PARTS.
Click the default column that was created with the table.
Enter the information for the first column in the table as follows:
For Column Name, enter PERFORMANCE_ID.
For Type, enter VARCHAR2.
For Size, enter 2.
Leave the value of Not Null and Primary Key properties. You will come back to this later, in 'Ensuring Data Integrity'.
Enter information for the second column as follows:
Key generator for adobe creative cloud. Check your internet connection and try to access the Adobe activation servers again.You need a secure connection when signing in to an app. Then try to sign in to your Creative Cloud app again.
Click Add Column.
For Column Name, enter NAME.
For Type, enter VARCHAR2.
For Size, enter 80.
Enter information for the third column as follows:
Click Add Column.
For Column Name, enter WEIGHT.
For Type, enter NUMBER.
Click OK.
SQL Developer generates the new table, performance_parts.
In the Connections navigation hierarchy, click the plus sign (+) next to Tables to expand the list of tables.
performance_parts is a new table in the hr schema, listed between locations and regions.
You just created a new table, performance_parts. If you click the table, the table will appear on the right side of the SQL Developer window, showing its new columns. If you click the SQL tab, you will see the script that created this table.
In Example 3-1, you will create the evaluations table by entering the information directly in the SQL Worksheet pane.
Example 3-1 Creating a Table in SQL Script
The results of the script follow.
You created a new table, evaluations. If you click the table, the table will appear on the right side of the SQL Developer window, showing its new columns. If you click the SQL tab, you will see the script that created this table. You may need to click the Refresh icon.
In Example 3-2, you will create another table, scores, by entering the information in the SQL Worksheet pane.
Example 3-2 Creating the SCORES Table
The results of the statement follow.
You created a new table, scores. If you click the table, the table will appear on the right side of the SQL Developer window, showing its new columns. If you click the SQL tab, you will see the script that created this table. You may need to click the Refresh icon.
See Also:
Oracle Database SQL Language Reference for information on the CREATE TABLE statement
The data in the table must satisfy the business rules that are modeled in the application. Many of these rules can be implemented throughintegrityconstraints that use the SQL language to explicitly state what type of data values are valid for each column.
When an integrity constraint applies to a table, all data in the table must conform to the corresponding rule, so when your application includes a SQL statement that inserts or modifies data in the table, Oracle Database automatically ensures that the constraint is satisfied. If you attempt to insert, update, or remove a row that violates a constraint, the system generates an error, and the statement is rolled back. If you attempt to apply a new constraint to a populated table, the system may generate an error if any existing row violates the new constraint.
Because Oracle Database checks that all the data in a table obeys an integrity constraint much faster than an application can, you can enforce the business rules defined by integrity constraints more reliably than by including this type of checking in your application logic.
See Also:
Oracle Database SQL Language Reference for information about integrity constraints
There are five basic types of integrity constraints:
A NOT NULL constraint ensures that the column contains data (it is not null).
Aunique constraint ensures that multiple rows do not have the same value in the same column. This type of constraint can also be used on combination of columns, as a composite unique constraint. This constraint ignores null values.
Aprimary keyconstraint combines NOT NULL and UNIQUE constraints in a single declaration; it prevents multiple rows from having the same value in the same column or combination of columns, and prevents null values.
A foreign key constraint requires that for each value in the column on which the constraint is defined, there must be a matching value in a specified other table and column.
A checkconstraint ensures that a value satisfies a specified condition. Use check constraints when you need to enforce integrity rules based on logical expressions, such as comparisons. Oracle recommends that you never use check constraints when other types of constraints can provide the necessary checking.
You will now add different types of constraints to the tables you created in 'Creating a Table'.
To Add a NOT NULL Constraint Using the SQL Developer Interface:
You will add a NOT NULL constraint to the table using the SQL Developer graphical interface.
In the Connections navigation hierarchy, click the plus sign (+) next to Tables to expand the list of tables.
Right-click the performance_parts table.
Select Edit.
In the Edit Table window, follow these steps:
In the Edit Table window, click Columns.
In the Columns list, select NAME.
In the Column Properties section, check Cannot be NULL.
Click OK.
In the Confirmation window, click OK.
You have now created a NOT NULL constraint for the name column of the performance_partstable.
The definition of the name column in the performance_parts table is changed to the following; note that the constraint is automatically enabled.
Example 3-3 shows how you can add another NOT NULL constraint to the performance_partstable by entering the required information directly in the SQL Statement window.
Example 3-3 Adding a NOT NULL Constraint in SQL Script
The results of the script follow.
You just created a NOT NULL constraint for column weight of the performance_parts table. If you click the SQL tab, you will see that the definition of the weight column changed. You may need to click the Refresh icon.
To add a unique constraint using the SQL Developer interface:
You will add a unique constraint to the scorestable using the SQL Developer graphical interface. You could also use the Edit Table window, as in the NOT NULL constraint, to accomplish this task.
In the Connections navigation hierarchy, click the plus sign (+) next to Tables to expand the list of tables.
Right-click the scorestable.
Select Constraint, and then select Add Unique.
In the Add Unique window, enter the following information:
Set the constraint name to SCORES_EVAL_PERF_UNIQUE.
Set Column 1 to EVALUATION_ID.
Set Column 2 to PERFORMANCE _ID.
Click Apply.
In the Confirmation window, click OK.
You have now created a unique constraint for the scorestable.
The following SQL statement was added to your table definition:
To add a primary key constraint using the SQL Developer interface:
You will add a primary key constraint to the performance_partstable using the SQL Developer graphical interface. You could also use the Edit Table window, as in the NOT NULL constraint, to accomplish this task.
In the Connections navigation hierarchy, click the plus sign (+) next to Tables to expand the list of tables.
Right-click the performance_parts table.
Select Constraint, and then select Add Primary Key.
In the Add Primary Key window, enter the following information:
Set the primary key name to PERF_PERF_ID_PK.
Set Column 1 to PERFORMANCE_ID.
Click Apply.
In the Confirmation window, click OK.
You have now created a primary key constraint for the performance_partstable.
The following SQL statement was added to your table definition:
In Example 3-4, you will create a primary key constraint on the evaluationstable by entering the required information directly in the SQL Statement window.
Example 3-4 Adding a Primary Key Constraint in SQL Script
The results of the script follow.
You just created a primary key eval_eval_id_pk on the evaluationstable. If you click the SQL tab, you will see the following SQL statement was added to your table definition. You may need to click the Refresh icon.
To add a foreign key constraint using the SQL Developer interface:
You will add two foreign key constraints to the scorestable using the SQL Developer graphical interface. You could also use the Edit Table window, as in the NOT NULL constraint, to accomplish this task.
In the Connections navigation hierarchy, the plus sign (+) next to Tables to expand the list of tables.
Right-click the scorestable.
Select Constraint, and then select Add Foreign Key.
In the Add Foreign Key window, enter the following information:
Set the foreign key name to SCORES_EVAL_FK.
Set Column Name to EVALUATION_ID.
Set Reference Table Name to EVALUATIONS.
Set Referencing Column to EVALUATION_ID.
Click Apply.
In the Confirmation window, click OK.
You have now created a foreign key constraint on the evalution_id column from the evaluationstable.
Add another foreign key constraint by repeating steps 2 through 5, with the following parameters:
Set the foreign key name to SCORES_PERF_FK.
Set Column Name to PERFORMANCE_ID.
Set Reference Table Name to PERFORMANCE_PARTS.
Set Referencing Column to PERFORMANCE_ID.
Click Apply.
The following SQL statements were added to your table definition:
In Example 3-5, you will create a foreign key constraint on the evaluationstable by entering the required information directly in the SQL Statement window.
Example 3-5 Adding a Foreign Key Constraint in SQL Script
The results of the script follow.
You have now created a foreign key constraint on the employee_id column from the employeestable. If you click the SQL tab, you will see the following SQL statement was added to your table definition. You may need to click the Refresh icon.
To add a check constraint using the SQL Developer interface:
You will add a check constraint to the scorestable using the SQL Developer graphical interface. You could also use the Edit Table window, as in the NOT NULL constraint, to accomplish this task.
In the Connections navigation hierarchy, the plus sign (+) next to Tables to expand the list of tables.
Right-click the scorestable.
Select Constraint, and then select Add Check.
In the Add Check window, enter the following information:
Set the Constraint Name to SCORE_VALID.
Set Check Condition to score >=0 and score <=9.
Set Status to ENABLE.
Click Apply.
In the Confirmation window, click OK.
You have now created a check constraint on the score column of the scorestable.
The following SQL statement was added to your table definition:
You can use SQL Developer to enter data into tables, to edit, and to delete existing data. The following tasks will show these processes for the performance_parts table.
To add data to a table using the SQL Developer interface:
Follow these steps to add rows of data to the performance_partstable
In the Connections navigation hierarchy, double-click the performance_parts table.
Click the Data tab in the performance_parts table display.
In the Data pane, click the New Record icon.
In the new row, add the following information; you can click directly into the column, or tab between columns:
Set PERFORMANCE_ID to 'WM'
Set NAME to 'Workload Management'
Set WEIGHT to 0.2
Press the Enter key.
Add a second row with the following information: set PERFORMANCE_ID to 'BR, set NAME to 'Building Relationships, and set WEIGHT to 0.2.
Press the Enter key.
Add a third row with the following information: set PERFORMANCE_ID to 'CF', set NAME to 'Customer Focus', and set WEIGHT to 0.2.
Press the Enter key.
Add a fourth row with the following information: set PERFORMANCE_ID to 'CM', set NAME to 'Communication', and set WEIGHT to 0.2.
Press the Enter key.
Add a fifth row with the following information: set PERFORMANCE_ID to 'TW', set NAME to 'Teamwork', and set WEIGHT to 0.2.
Press the Enter key.
Add a sixth row with the following information: set PERFORMANCE_ID to 'RD', set NAME to 'Results Orientation', and set WEIGHT to 0.2.
Press the Enter key.
Click the Commit Changes icon.
Review and close the Data Editor Log window.
Review the new data in the table performance_parts.
You have added 6 rows to the performance_parts table.
To modify table data using the SQL Developer interface:
Follow these steps to change data to the performance_partstable.
In the Connections navigation hierarchy, double-click the performance_parts table.
Click the Data tab in the performance_partstable display.
In the Data pane, in the 'Workload Management' row, click the weight value, and enter a new value for '0.3'.
In the 'Building Relationships' row, click the weight value, and enter a new value for '0.15'.
In the 'Customer Focus' row, click the weight value, and enter a new value for '0.15'.
Press the Enter key.
Click the Commit Changes icon.
Review and close the Data Editor Log window.
You have now changed values in three rows of the performance_partstable.
To delete table data using the SQL Developer interface:
Imagine that in the company modeled by the hr schema, management decided that the categories Workload Management and Results Orientation had too much overlap. You will now remove the row 'Results Orientation' from the performance_partstable.
In the Connections navigation hierarchy, double-click the performance_parts table.
Click the Data tab in the performance_partstable display.
In the Data pane, click the 'Results Orientation' row.
Click the Delete Selected Row(s) icon.
Click the Commit Changes icon.
Review and close the Data Editor Log window.
You have now removed a row from the performance_partstable.
See Also:
When you define a primary key on a table, Oracle Database implicitly creates an index on the column that contains the primary key. For example, you can confirm that an index was created for the evaluationstable on its primary key, by looking at its Indexes pane.
In this section, you will learn how to add different types of indexes to the tables you created earlier.
To create an index using the SQL Developer interface:
Follow these steps to create a new index for the evaluationstable.
In the Connections navigation hierarchy, right-click the evaluations table.
Select Index and then select Create Index.
Alternatively, in the Connections navigation hierarchy, you can right-click Indexes and select New Index.
In the Create Index window, enter the following parameters:
Ensure that the Schema is set to HR.
Set the Name to EVAL_JOB_IX.
Click the Add Column Expression icon, which looks like a 'plus' sign.
Set the Column Name or Expression to JOB_ID.
Set the Order to ASC.
Click OK.
You have now created a new index EVAL_JOB_IX on the column JOB_ID in the evaluationstable. You can see this index by finding it in the list of Indexes in the Connections navigation hierarchy, or by opening the evaluations table and browsing to the Indexes tab. The following script is the equivalent SQL statement for creating this index.
To modify an index using SQL Developer interface:
Follow these steps to reverse the sort order of the EVAL_JOB_IXindex.
In the Connections navigation hierarchy, the plus sign (+) to expand Indexes.
Right-click EVAL_JOB_IX, and select Edit.
In the Edit Index window, change Order to DESC.
Click OK.
You changed the index. The following script is the equivalent SQL statement for creating this index:
To delete an index using SQL Developer interface:
Following steps to delete the EVAL_JOB_IXindex.
In the Connections navigation hierarchy, the plus sign (+) to expand Indexes.
Right-click EVAL_JOB_IX, and select Drop.
In the Drop window, click Apply.
In the Confirmation window, click OK.
You deleted the index EVAL_JOB_IX. The following script is the equivalent SQL statement for dropping this index.
See Also:
Oracle Database SQL Language Reference for information on the CREATE INDEX statement
Oracle Database SQL Language Reference for information on the ALTER INDEX statement
Oracle Database SQL Language Reference for information on the DROP INDEX statement
Sometimes it becomes necessary to delete a table and all its contents from your schema. To accomplish this, you must use the SQL statement DROP TABLE. You will use the tables that you already created to learn other concepts, so create a simple table that you can subsequently delete by running the following script in the SQL Statement window:
To delete a table using the SQL Developer interface:
Follow these steps to delete TEMP_TABLE from the hr schema.
In the Connections navigation hierarchy, right-click TEMP_TABLE.
Select Table, and then select Drop.
In the Drop window, click Apply.
In the Confirmation window, click OK.
You deleted the table TEMP_TABLE. The following script is the equivalent SQL statement for dropping this table.
See Also:
Oracle Database SQL Language Reference for information on the DROP TABLE statement
Views are logical tables based on one or more tables or views. Views are particularly useful if your business needs include frequent access to information that is stored in several different tables.
The standard syntax for creating a view follows:
To create a view using the SQL Developer interface:
Follow these steps to delete create a new view from the hr schema.
In the Connections navigation hierarchy, right-click Views.
Select New View.
In the Create View window, enter the following parameters:
Ensure that Schema is set toHR.
Set Name to SALESFORCE.
Set the SQL Query to the following:
In SQL Parse Results, click Test Syntax.
Click OK.
You created a new view. The equivalent SQL statement for creating this view follows:
In Example 3-6, you will create a view of all employees in the company and their work location, similar to the query you used in 'Using Character Functions'.
Example 3-6 Creating a View in SQL Script
The results of the script follow.
You have now created new view that relies on information in 4 separate tables, or a 4-wayJOIN. In the Connections navigation hierarchy, if you click the 'plus' sign next to Views, you will see emp_locations.
To change the properties of a view in SQL Developer interface:
You will change the salesforce view by adding to it the employees in the Marketing department, and then rename the view to sales_marketing.
In the Connections navigation hierarchy, right-click the salesforce view.
Select Edit.
In the Edit View window, change the SQL Query by adding the following to the last line: 'OR department_id = 20'.
Click Test Syntax.
Click OK.
To rename the view, right-click salesforce and select Rename.
In the Rename window, set New View Name to sales_marketing.
Click Apply.
In the Confirmation window, click OK.
You changed the view. The equivalent SQL statements for changing and renaming the view are:
To drop a view using the SQL Developer interface:
You will use the DROP VIEW statement to delete thesales_marketing view.
In the Connections navigation hierarchy, right-click the sales_marketing view.
Select Drop.
In the Drop window, click Apply.
In the Confirmation window, click OK.
You deleted the view. The equivalent SQL statement for dropping the view is:
See Also:
Oracle Database SQL Language Reference for information on the CREATE VIEW statement
Oracle Database SQL Language Reference for information on the DROP VIEW statement
Sequences are database objects that generate unique sequential values, which are very useful when you need unique primary keys. The hr schema already has three such sequences: departments_seq, employees_seq, and locations_seq.
The sequences are used through these pseudocolumns:
The CURRVAL pseudocolumn returns the current value of a sequence. CURRVAL can only be used after an initial call to NEXTVAL initializes the sequence.
The NEXTVAL pseudocolumn increments the sequence and returns the next value. The first time that NEXTVAL is used, it returns the initial value of the sequence. Subsequent references to NEXTVAL increment the sequence value by the defined increment, and return the new value.
Note that a sequence is not connected to any other object, except for conventions of use. When you plan to use a sequence to populate the primary key of a table, Oracle recommends that you use a naming convention to link the sequence to that table. Throughout this discussion, the naming convention for such sequences is table_name_seq.
You can create a sequence in the SQL Developer Interface, or using the SQL Statement window.
To create a sequence using the SQL Developer interface:
The following steps will create a sequence, evaluations_seq, that you can use for the primary key of the evaluationstable.
In the Connections navigation hierarchy, right-click Sequences.
Select New Sequence.
In the New Sequence window, enter the following parameters:
Ensure that Schema is set to HR.
Set Name to EVALUATIONS_SEQ.
In the Properties tab:
Set Increment to 1.
Set Start with to 1.
Check Order.
Click OK.
You have now created a sequence that can be used for the primary key of the evaluationstable. If you click '+' to expand the Sequence tree, you can see new sequence. The equivalent SQL statement is:
In Example 3-7, you will create another sequence by entering the required information directly in the SQL Statement window.
Example 3-7 Creating a Sequence Using SQL Script
The results of the script follow.
See Also:
Oracle Database SQL Language Reference for information on the CREATE SEQUENCE statement
To delete a sequence, you must use the SQL statement DROP SEQUENCE. To see how a sequence can be deleted in SQL Developer, you can use the test_seq sequence you created earlier. If the new sequence does not appear in the Connections hierarchy navigator, click the refresh icon.
To drop a sequence:
Follow these steps to drop a sequence.
In the Connections navigator, right-click the test_seq sequence.
In the Drop window, click Apply.
In the Confirmation window, click OK.
You have now deleted the sequence test_seq. The equivalent SQL statement follows:
See Also:
Oracle Database SQL Language Reference for information on the DROP SEQUENCE statement
A synonym is an alias for any schema object and can be used to simplify SQL statements or even obscure the names of actual database objects for security purposes. Additionally, if a table is renamed in the database (departments to divisions), you could create a departments synonym and continue using your application code as before.
To create a synonym using the SQL Developer interface:
The following steps will create a synonym, positions, that you can use in place of the jobs schema object.
In the Connections navigation hierarchy, right-click Synonyms.
Select New Synonym.
In the New Synonym window, set the following parameters:
Ensure that Schema is set to HR.
Set Name to POSITIONS.
In the Properties tab:
Select Object Based. This means that the synonym refers to a specific schema object, such as a table, a view, a sequence, and so on.
Set Object Based to JOBS.
Click OK.
You created a synonympositions for the jobstable. The equivalent SQL statement follows:
In Example 3-8, you use the new positions synonym in place of the jobstable name.
Example 3-8 Using a Synonym
The results of the query appear.
To drop a synonym:
Follow these steps to drop the positions synonym.
In the Connections navigator, right-click the positions synonym.
Select Drop.
In the Drop window, click Apply.
In the Confirmation window, click OK.
You deleted synonym positions. The equivalent SQL statement follows:
See Also:
Oracle Database SQL Language Reference for information on the CREATE SYNONYM statement
Oracle Database SQL Language Reference for information on the DROP SYNONYM statement