Create Table:
You use the CREATE TABLE SQL command to create a new table. The general syntax for the CREATE TABLE command is:
CREATE TABLE tablename
(columnname1 data_type,
columnname2 data_type, ...);
VARCHAR2 Data Type
The VARCHAR2 data type stores variable-length character data up to a maximum of 4000 characters. You use the following syntax to declare a VARCHAR2 data column:
Columnname VARCHAR2(maximum_size)
s_last VARCHAR2(30);
CHAR Data Type
The CHAR data type stores fixed-length character data up to a maximum of 2000 characters.
Columnname CHAR[(maximum_size)]
s_class CHAR(2);
NVARCHAR2 and NCHAR Data Types
The NVARCHAR2 and NCHAR data types store variable-length and fixed-length data just as their VARCHAR2 and CHAR counterparts do, except that they use Unicode coding.
Columnname NVARCHAR2(maximum_size)
Columnname NCHAR[(maximum_size)]
Number Data Types
The NUMBER data type stores negative, positive, fixed, and floating-point numbers between 10-130 and 10125, with precision up to 38 decimal places.
Columnname NUMBER [([precision,] [scale])]
Integer Numbers
An integer is a whole number with no digits on the right side of the decimal point.
Columnname NUMBER(precision)
f_id NUMBER(5);
Fixed-point Numbers
A fixed-point number contains a specific number of decimal places, so the column declaration specifies both the precision and scale values
price NUMBER(5,2);
Floating-point Numbers
A floating-point number contains a variable number of decimal places. The decimal point can appear anywhere, from before the first digit to after the last digit, or can be omitted entirely.
Columnname NUMBER
s_gpa NUMBER;
DATE Data Type
The DATE data type stores dates from December 31, 4712 BC to December 31,AD 4712. The DATE data type stores the century, year, month, day, hour, minute, and second. The default date format is DD-MON-YY, which indicates the day of the month, a hyphen, the month (abbreviated using three capital letters), another hyphen, and the last two digits of the
year.The default time format is HH:MI:SS AM, which indicates the hours, minutes, and seconds using a 12-hour clock. If the user does not specify a time when he or she enters a DATE data value, the default time value is 12:00:00 AM. If the user does not specify the date when he or she enters a time value, the default date value is the first day of the current month.
To declare a DATE data column, use the following general syntax:
Columnname DATE
s_dob DATE;
TIMESTAMP Data Type
The TIMESTAMP data type stores date values similar to the DATE data type, except it also stores fractional seconds in addition to the century, year, month, day, hour, minute, and second. An example of a TIMESTAMP data value is 15-AUG-06 09.26.01.123975 AM. You use the TIMESTAMP data type when you need to store precise time values. The following general syntax declares a TIMESTAMP data column:
Columnname TIMESTAMP (fractional_seconds_precision)
For example, suppose you want to store a date value that includes the fractional seconds. You would declare the SL_DATE_RECEIVED columnas follows:
sl_date_received TIMESTAMP(2);
Large Object (LOB) Data Types
Sometimes databases store binary data, such as digitized sounds or images, or references to binary files from a word processor or spreadsheet. In these cases, you can use one of the Oracle10g large object (LOB) data types.Table 2-1 summarizes the four LOB data types. Previous versions of Oracle supported the RAW and LONG RAW data types for storing binary data. Oracle Corporation recommends storing binary data using the large object data types, so this book does not use the RAW and LONG RAW data types.
You declare an LOB data column using the following general syntax:
Columnname LOB_data_type
Large Object (LOB) Data Type Description
· BLOB Binary LOB, storing up to 4 GB of binary data in the database
· BFILE Binary file, storing a reference to a binary file located outside the database in a file maintained
· by the operating system
· CLOB Character LOB, storing up to 4 GB of character data in the database
· NCLOB Character LOB that supports 2-byte character codes, stored in the database—up to a maximum
· of 4 GB
You declare an LOB data column using the following general syntax:
Columnname LOB_data_type
In this syntax, LOB_data_type is the name of the LOB data type, and can have the value BLOB, CLOB, BFILE, or NCLOB.
f_image BLOB
Alternately, you could store a reference to the location of an external image file using the BFILE data type by making the following declaration:
f_image BFILE
CONSTRAINTS
Constraints are rules that restrict the data values that you can enter into a column in a database table.There are two types of constraints: integrity constraints, which define primary and foreign keys; and value constraints, which define specific data values or data ranges that must be inserted into columns and whether values must be unique or not NULL.There are two levels of constraints: table constraints and column constraints. A table constraint restricts the data value with respect to all other values in the table. An example of a table constraint is a primary key constraint, which specifies that a column value must be unique and cannot appear in this column in more than one table row. A column constraint limits the value that can be placed in a specific column, irrespective of values that exist in other table rows. Examples of column constraints are value constraints, which specify that a certain value or set of values must be used, and NOT NULL constraints, which specify that a value cannot be NULL.A value constraint might specify that the value of a GENDER column must be either M (for male) or F (for female).You might place a NOT NULL constraint on a student ADDRESS column, to ensure that users always enter address information when they create a new customer row.
You can place constraint definitions at the end of the CREATE TABLE command, after you declare all of the table columns. Or, you can place each constraint definition within the column definition, so it immediately follows the data column declaration for the column associated with the constraint. Every constraint in a user schema must have a unique constraint name.
Integrity Constraints
An integrity constraint defines primary key columns, and specifies foreign keys and their corresponding table and column references. The following paragraphs describe how to define primary keys, foreign keys, and composite primary keys.
Primary Keys
The general syntax for defining a primary key constraint within a column declaration is:
CONSTRAINT constraint_name PRIMARY KEY
The syntax for defining a primary key constraint at the end of the CREATE TABLE command, after the column declarations, is:
CONSTRAINT constraint_name PRIMARY KEY (columnname)
CREATE TABLE location
(loc_id NUMBER(6),ƒ
bldg_code VARCHAR2(10),
room VARCHAR2(6),
capacity NUMBER(5),
CONSTRAINT location_loc_id_pk PRIMARY KEY (loc_id));
Foreign Keys
A foreign key constraint is a column constraint that specifies that the value a user inserts in a column must exist as a primary key in a referenced table.
CONSTRAINT constraint_name
FOREIGN KEY (columnname)
REFERENCES primary_key_tablename (primary_key_columnname)
Composite Keys
Recall that a composite key is a primary key composed of two or more data columns.
CONSTRAINT constraint_name
PRIMARY KEY (columnname1, columnname2,…)
CREATE TABLE enrollment
(s_id NUMBER(5) CONSTRAINT enrollment_s_id_fk
REFERENCES student(s_id),
c_sec_idƒNUMBER(8) CONSTRAINT enrollment_c_sec_id_fk
REFERENCES course_section(c_sec_id),
CONSTRAINT enrollment_s_id_c_sec_id_pk
PRIMARY KEY (s_id, c_sec_id));
Value Constraints
Value constraints are column-level constraints that restrict the data values that users can enter into a given column. Commonly used value constraints include:
· _ CHECK conditions—This enables you to specify that a column value must be a specific value or fall within a range of values.
· _ NOT NULL constraint—Specifies whether a column value can be NULL _ DEFAULT constraint—Specifies that a column has a default value that the DBMS automatically inserts for every row, unless the user specifies an alternate value
· _ UNIQUE constraint—Specifies that a column must have a unique value for every table row
Check Conditions
Check conditions specify that a column value must be a specific value (such as M), from a set of allowable values (such as M or F), or fall within a specific range (such as greater than zero but less than 1000).You should create check condition constraints only when the number of allowable values is limited and not likely to change.
As an example of a check condition, consider the S_CLASS column in the Northwoods University STUDENT table, in which the values are restricted to FR, SO, JR, or SR (freshman, sophomore, junior, or senior). The syntax to define this check condition is:
CONSTRAINT student_s_class_cc CHECK
((s_class = ‘FR’) OR (s_class = ‘SO’)
OR (s_class = ‘JR’) OR (s_class = ’SR’))
You can also use a check condition to validate a range of allowable values. An example of a range check condition is in the CREDITS column in the Northwoods COURSE table, where the allowable values must be greater than 0 and less than 12.The constraint definition is:
CONSTRAINT course_credits_cc
CHECK((credits > 0) AND (credits <>
NOT NULL Constraints
The NOT NULL constraint specifies whether the user must enter a value for a specific row, or whether the value can be NULL (absent or unknown)
s_last VARCHAR2(30)
CONSTRAINT student_s_last_nn NOT NULL
Default Constraints
A default constraint specifies that a particular column has a default value that the DBMS automatically inserts for every table row.
s_state CHAR(2) DEFAULT ‘FL’
The DBMS will insert the default only if the user inserts a NULL value into the S_STATE column.
UNIQUE Constraints
A UNIQUE constraint is a table constraint that specifies that a column must have a unique value for every table row. It is basically the same as a primary key constraint, except NULL values are allowed in the column. NULL values are not allowed in a column that is referenced by a primary key constraint.
CONSTRAINT term_term_desc_uk UNIQUE (term_desc)
Creating SQL table using SQL*Plus
CREATE TABLE customer
(c_id NUMBER(5),
c_last VARCHAR2(30),
c_first VARCHAR2(30),
c_mi CHAR(1),
c_birthdate DATE,
c_address VARCHAR2(30),
c_city VARCHAR2(30),
c_state CHAR(2),
c_zip VARCHAR2(10),
c_dphone VARCHAR2(10),
c_ephone VARCHAR2(10),
c_userid VARCHAR2(50),
c_password VARCHAR2(15),
CONSTRAINT customer_c_id_pk PRIMARY KEY (c_id));
VIEWING INFORMATION ABOUT TABLES
To view the column names and data types of an individual table, you use the DESCRIBE command, which has the following syntax:
DESCRIBE tablename
The following command retrieves the names of all of a user’s database tables by retrieving the TABLE_NAME column from the USER_TABLES view:
SELECT table_name
FROM user_tables;
Similarly, the following command uses the ALL prefix and retrieves the names of all database tables that a user has either created or has been given object privileges to manipulate:
SELECT table_name
FROM all_tables;
Type SELECT table_name FROM user_tables; at the SQL prompt, and then press Enter to view information about the tables in your user schema.
Type SELECT table_name FROM all_tables; at the SQL prompt, and then press Enter to retrieve the names of all tables that you have privileges to manipulate. To retrieve a list of all of the constraints for a specific database table in your user schema, you use the following command:
SELECT constraint_name, constraint_type
FROMƒuser_constraints
WHERE table_name = ‘DATABASE_TABLENAME’;
Deleting and Renaming Existing Tables
To delete a table, you use the DROP TABLE command, which has the following syntax:
DROP TABLE tablename;
To drop a table that contains columns that other tables reference as foreign keys, you have two options. One option is to first drop all the tables that contain the foreign key references. For example, to delete the LOCATION table, you could first delete the FACULTY table, and then you could delete the LOCATION table.The second option is first to delete all of the foreign key constraints that reference the table to be deleted. To delete the foreign key constraints that reference a table, you use the CASCADE CONSTRAINTS option in the DROP TABLE command, which has the following syntax:
DROP TABLE tablename
CASCADE CONSTRAINTS;
When you execute the DROP TABLE command with the CASCADE CONSTRAINTS option, the system first drops all of the constraints associated with the table, and then drops the table.
To rename an existing table, you use the RENAME TO command, which has the following syntax:
RENAME old_tablename
TO new_tablename;
RENAME faculty TO nw_faculty;
Adding Columns to Existing Tables
The basic syntax of the command to add a new column to a table is:
ALTER TABLE tablename
ADD(columnname data_declaration constraints);
In this syntax, columnname is the name of the new data column. Data_declaration defines the new column’s data type and maximum size, and constraints defines any constraints you want to place on the new column, such as foreign key references, check conditions, or value constraints.
To add the START_DATE column to the FACULTY table type the following command at the SQL prompt:
ALTER TABLE faculty
ADD (start_date DATE);
Modifying Existing Column Data Definitions
The general syntax of the command to modify an existing column’s data declaration is:
ALTER tablename
MODIFY(columnname new_data_declaration);
ALTER TABLE faculty
MODIFY (f_rank CHAR(4));
Deleting a Column
The general command to delete an existing column is:
ALTER TABLE tablename
DROP COLUMN columnname;
Renaming a Column
ALTER TABLE tablename
RENAME COLUMN old_columnname TO new_columnname;
Adding and Deleting Constraints
To add a constraint to an existing table, you use the following command:
ALTER TABLE tablename
ADD CONSTRAINT constraint_name constraint_definition;
To add the UNIQUE constraint to the FACULTY table type the following command at the SQL prompt:
ALTER TABLE faculty
ADD CONSTRAINT faculty_f_pin_uk UNIQUE(f_pin);
To remove an existing constraint, you use the following command:
ALTER TABLE tablename
DROP CONSTRAINT constraint_name;
To drop the constraint type the following command at the SQL prompt:
ALTER TABLE faculty
DROP CONSTRAINT faculty_f_pin_uk;
Enabling and Disabling Constraints
You use the following command to disable an existing constraint:
ALTER TABLE tablename
DISABLE CONSTRAINT constraint_name;
Similarly, you use the following command to enable a constraint that you previously disabled:
ALTER TABLE tablename
ENABLE CONSTRAINT constraint_name;
USING SCRIPTS TO CREATE DATABASE TABLES
@ D:\206\Lecture03\Ch3Northwoods.sql
OR
START D:\206\Lecture03\Ch3Northwoods.sql
Using the INSERT Command
The basic syntax of the INSERT statement for inserting a value for each table column is:
INSERT INTO tablename
VALUES (column1_value, column2_value, ...);
INSERT INTO location
VALUES (1, 'CR', '101', NULL);
You can also use the INSERT command to insert values only in specific table columns. The basic syntax of the INSERT statement for inserting values into selected table columns is:
INSERT INTO tablename (columnname1, columnname2,...)
VALUES (column1_value, column2_value, ...);
INSERT INTO faculty (F_FIRST, F_LAST, F_ID)
VALUES ('Teresa', 'Marx', 1);
Inserting Values into DATE Columns
The general syntax of the TO_DATE function is:
TO_DATE('date_string', 'date_format_model')
In this syntax, date_string represents the date value as a text string, such as ‘08/24/2006’, and date_format_model is the format model that represents the date_string value’s format, such as MM/DD/YYYY.You convert the text string ‘08/24/2006’ to a DATE data type using the following command:
TO_DATE('08/24/2006', 'MM/DD/YYYY');
Similarly, the following command converts the character string ‘24-AUG-2005’ to a DATE data type:
TO_DATE('24-AUG-2005', 'DD-MON-YYYY')
To convert a 10:00 AM value to a DATE format for C_SEC_ID 1, you use the following command:
TO_DATE('10:00 AM', 'HH:MI AM')
CREATING SEARCH CONDITIONS IN SQL QUERIES
The general syntax of a SQL search condition is:
WHERE columnname comparison_operator search_expression
Defining Search Expressions
You must enclose text strings in single quotation marks. For example, you use the following search condition to match the S_CLASS value in the STUDENT table:
WHERE s_class = 'SR'
Search_expression values within single quotation marks are case sensitive. The search condition S_CLASS = 'sr' does not retrieve rows in which the S_CLASS value is ‘SR’.
When search_expression involves a DATE data value, you must use the TO_DATE function to convert the DATE character string representation to an internal DATE data format. The following search condition matches dates of January 1, 1980:
WHERE s_dob = TO_DATE('01/01/1980', 'MM/DD/YYYY')
Creating Complex Search Conditions
Logical operators AND, OR, and NOT can be used to combine search conditions. For example, the following complex search condition matches all rows in which BLDG_CODE is ‘CR’ and the capacity is greater than 50:
WHERE bldg_code = 'CR' AND capacity > 50
For example, the following search condition matches all course section rows that meet either on Tuesday and Thursday or on Monday, Wednesday, and Friday (at Northwoods University, R denotes courses that meet on Thursday):
WHERE day = 'TR' OR day = 'MWF'
You can use the NOT logical operator to match the logical opposite of a search expression, using this syntax:
WHERE NOT(search_expression)
For example, the following search condition finds all rows in the STUDENT table in which the S_CLASS column has any value other than ‘FR’:
WHERE NOT (s_class = 'FR')
Updating Table Rows
An UPDATE action query also usually contains a search condition to identify the row or rows to update. The general syntax of an UPDATE action query is:
UPDATE tablename
SET column1 = new_value1, column2 = new_value2, ...
WHERE searchƒcondition;
In this syntax, tablename specifies the table whose rows you wish to update. The SET clause lists the columns to update and their associated new values. For example, the following action query changes Teresa Marx’s F_RANK value to ‘ASSOCIATE’ and her F_PIN value to 1181:
UPDATE faculty
SETƒf_rank = 'ASSOCIATE', f_pin = 1181
WHERE f_id = 1;
Deleting Table Rows
The general syntax for a DELETE action query is:
DELETE FROM tablename
WHERE search condition;
Truncating Tables
When you need to delete all of the rows in a table quickly, you can truncate the table, which means you remove all of the table data without saving any rollback information.When you truncate a table, the table structure and constraints remain intact. To truncate a table, you use the TRUNCATE TABLE command, which has the following general syntax:
TRUNCATE TABLE tablename;
You cannot truncate a table that has foreign key constraints as long as the foreign key constraints are enabled. Therefore, you must disable a table’s foreign key constraints before you can truncate the table. Recall that you use the following command to disable an existing constraint:
ALTER TABLE tablename
DISABLE CONSTRAINT constraint_name;
RETRIEVING DATA FROM A SINGLE DATABASE TABLE
The basic syntax for a SQL query that retrieves data from a single database table is:
SELECT columnname1, columnname2,...
FROM ownername.tablename
[WHERE search_condition];
In the FROM clause, ownername specifies the table’s user schema, and tablename specifies the name of the database table. If you are retrieving data from a table in your own user schema, you can omit ownername (and the period) in the FROM clause. If you are retrieving data from a table in another user’s schema, you must include ownername in the FROM clause, and the table’s owner must have granted you the SELECT privilege on the table. For example, you use the following FROM clause to retrieve data values from the LOCATION table in user SCOTT’s user schema:
FROM scott.location
The WHERE clause optionally specifies a search condition that instructs the query to retrieve selected rows. To retrieve every row in a table, the data values do not need to satisfy a search condition, so you can omit the WHERE clause.
If you want to retrieve all of the columns in a table, you can use an asterisk ( * ) as awildcard character in the SELECT clause instead of typing every column name.
Type and execute the following query to select all rows and columns from the LOCATION table:
SELECT *FROM location;
Suppressing Duplicate Rows
Some of the faculty members have the same rank, so the query SELECT f_rank FROM faculty; retrieves duplicate values.The SQL DISTINCT qualifier examines query output before it appears on your screen and suppresses duplicate values.The DISTINCT qualifier has the following general syntax in the SELECT command:
SELECT DISTINCT columnname;
For example, to suppress duplicate faculty ranks, you use the command:
SELECT DISTINCT f_rank FROM faculty;
Using Search Conditions in SELECT Queries
To use an exact search condition in a SELECT query:
SELECT f_first, f_mi, f_last, f_rank
FROM faculty
WHERE f_rank = 'Associate';
Next you create an inexact search condition that retrieves the number of every room in the Business (‘BUS’) building at Northwoods University that has a capacity greater than or equal to 40 seats.You use the greater than or equal to ( >= ) comparison operator in the inexact search condition. To use an inexact search condition in a SELECT query type the following query to retrieve specific rows in the LOCATION table:
SELECT room
FROM location
WHERE bldg_code = 'BUS'
AND capacity >= 40;
Searching for NULL and NOT NULL Values
To search for NULL values, you use the following general syntax:
WHERE columnname IS NULL
Similarly, to retrieve rows in which the value of a particular column is not NULL, you use the following syntax:
WHERE columnname IS NOT NULL
Using the IN and NOT IN Comparison Operators
You can use the IN comparison operator to match data values that are members of a set of search values. For example, you can retrieve all enrollment rows in which the GRADE column value is a member of the set (‘A’,‘B’). Similarly, you can use the NOT IN comparison operator to match values that are not members of a set of search values.
Using the LIKE Comparison Operators
Sometimes, you need to perform searches by matching part of a character string. For example, you might want to retrieve rows for students whose last name begins with the letter M, or find all courses with the text string MIS in the COURSE_NO column. To do this, you use the LIKE operator. The general syntax of a search condition that uses the LIKE operator is:
WHERE columnname LIKE 'character_string'
Character_string represents the text string to be matched and is enclosed in single quotation marks. Character_string must contain either the percent sign ( % ) or underscore ( _ ) wildcard characters. Character_string represents the text string to be matched and is enclosed in single quotation marks. Character_string must contain either the percent sign ( % ) or underscore ( _ ) wildcard characters.
The percent sign ( % ) wildcard character represents multiple characters. If you place ( % ) on the left edge of the character string to be matched, the DBMS searches for an exact match on the far-right characters and allows an inexact match for the characters represented by ( % ). For example, the earch condition WHERE term_desc LIKE '%2006' retrieves all term rows in which the last four characters in the TERM_DESC column are 2006.
The underscore ( _ ) wildcard character represents a single character. For example, the search condition WHERE s_class LIKE '_R' retrieves all values for S_CLASS in which the first character can be any value, but the second character must be the letter R.
You can use the underscore ( _ ) and percent sign ( % ) wildcard characters together in a single search condition. For example, the search condition WHERE c_sec_day LIKE '_T%' retrieves all course sections that meet on Tuesday, provided exactly one character precedes T in the C_SEC_DAY column.The search condition ignores all of the characters that follow T in the column value, so the query retrieves values such as MT,MTW,and MTWRF.
Sorting Query Output
When you insert rows into an Oracle database, the DBMS does not store the rows in any particular order.When you retrieve rows using a SELECT query, the rows may appear in the same order in which you inserted them into the database, or they may appear in a different order, based on the database’s storage configuration.You can sort query output by using the ORDER BY clause and specifying the sort key, which is the column the DBMS uses as a basis for ordering the data.The syntax for a SELECT query that uses the ORDER BY clause is as follows:
SELECT columnname1, columnname2, ...
FROM ownername.tablename
WHERE search_condition
ORDER BY sort_key_column;
USING CALCULATIONS IN SQL QUERIES
· Multiplication, Division *, /
· Addition, Subtraction +, -
For example, you use the following SELECT clause to retrieve the product of the INV_PRICE times the INV_QOH columns in the INVENTORY table:
SELECT inv_price * inv_qoh
Date Calculations
To retrieve the current system date from the database server, you use the SYSDATE pseudocolumn. The following query retrieves the current system date:
SELECT SYSDATE
FROM DUAL;
For example, the following expression specifies a date that is 10 days after the order date (O_DATE) in the Clearwater Traders ORDERS table:
o_date + 10
The following expression returns the number of days between the current date and the order date column in the ORDERS table:
SYSDATE - o_date
To express the calculated ages in years instead of days, you must divide these values by the number of days in a year, which is approximately 365.25. To evaluate the subtraction operation before the division operation, you must place the subtraction operation in parentheses using the following expression:
(SYSDATE - S_DOB)/365.25
To determine the date on which a student enrolled in the university, you subtract the TIME_ENROLLED value from the current system date, as in the following expression:
SYSDATE - time_enrolled
You can also add or subtract intervals from one another to calculate the sum or difference of two intervals. For example, suppose you need to update the TIME_ENROLLED column every month by adding one month to the interval value.You use the following query to add an interval of one month to the current TIME_ENROLLED value:
SELECT s_id, time_enrolled + TO_YMINTERVAL('0-1')
FROM student;
Similarly, you use the following query to add an interval of 10 minutes to the C_SEC_DURATION column in the COURSE_SECTION table:
SELECT c_sec_id, c_sec_duration +
TO_DSINTERVAL('0 00:10:00')
FROM course_section;
Single-row Number Functions
Single-row Character Functions
Single-row Date Functions
ORACLE10g SQL GROUP FUNCTIONS
Using the COUNT Group Function
The COUNT group function returns an integer that represents the number of rows that a query returns. The COUNT(*) version of this function calculates the total number of rows in a table that satisfy a given search condition.
Using the GROUP BY Clause to Group Data
If a query retrieves multiple rows and the rows in one of the retrieved columns have duplicate values, you can group the output by the column with duplicate values and apply group functions to the grouped data. The GROUP BY clause has the following syntax:
GROUP BY group_columnname;
Using the HAVING Clause to Filter Grouped Data
You can use the HAVING clause to place a search condition on the results of queries that display group function calculations. The HAVING clause has the following syntax:
HAVING group_function comparison_operator value
For example, suppose you want to retrieve the total capacity of each building at Northwoods University, but you are not interested in the data for buildings that have a capacity of less than 100.You use the following HAVING clause to filter the output:
HAVING SUM(capacity) >= 100
FORMATTING OUTPUT IN SQL*PLUS
So far, you have accepted the default output formats in SQL*Plus—output column headings are the same as the database column names.
To specify alternate output heading text, you use the following syntax in the SELECT clause:
SELECT columnname1 "heading1_text",
columnname2 "heading2_text", ...
Aliases
An alias is an alternate name for a query column. After you create an alias, you can reference the alias in other parts of the query, such as in the GROUP BY or ORDER BY clause. The general syntax for creating an alias is:
SELECT columnname1 AS alias_name1...
JOINING MULTIPLE TABLES
SELECT column1, column2,...
FROM table1, table2
WHERE table1.joincolumn = table2.joincolumn
AND search_condition(s)
Inner Joins
The simplest type of join occurs when you join two tables based on values in one table being equal to values in another table.This type of join is called an inner join, equality join, equijoin, or natural join.
Query Design Diagram
Outer Joins
An inner join returns rows only if values exist in all tables that are joined. If no values exist for a row in one of the joined tables, the inner join does not retrieve the row. An outer join returns all rows from one table, which is called the inner table. An outer join also retrieves matching rows from a second table, which is called the outer table.
To create an outer join in Oracle10g SQL, you label the outer table in the join condition using the following syntax:
inner_table.join_column = outer_table.join_column(+)
The outer join operator ( + ) signals the DBMS to insert a NULL value for the columns in the outer table that do not have matching rows in the inner table.
Self-joins
To create a self-join, you must create a table alias and structure the query as if you are joining the table to a copy of itself. A table alias is an alternate name that you assign to the table in the query’s FROM clause.The syntax to create a table alias in the FROM clause is:
FROM table1 alias1, ...
Introduction to PL/SQL
©Bob Godfrey, 2008.
PL/SQL is delivered as part of SQL*Plus, and is used to extend the capabilities of SQL by adding procedural elements using traditional programming constructs.
| If you are new to programming, then the prospect of learning PL/SQL might seem a little daunting. In essence however, a program is nothing more than a set of instructions for achieving some purpose. The recipe for Australia's traditional Anzac Biscuits shown right incorporates both the elements and the structure of a simple program. First, the recipe is named (in this case Anzac Biscuits). Second, there are some declarations - in this case the list of ingredients. Third, following these declarations, there are a list of instructions which are to be obeyed in sequence. In a program, we would have a program (or subprogram) name, followed by some declarations of variables to be used, and finally, the instructions (called statements) themselves. These executable statements would be enclosed in a pair of "begin"..."end" brackets, just as in the recipe the horizontal bar was used to separate the ingredients from the cooking instructions. |
|
PL/SQL block structure
Unnamed block
declare
… … …
begin
… … …
end;
Stored procedure
create or replace procedure_name
as
… … … {declarations automatically follow the as statement}
… … …
begin
… … …
… … …
exception
… … …
end;
Nested blocks
create or replace procedure_name
as
… … …
… … …
begin
… … …
… … …
begin
… … …
… … …
exception
… … …
end;
… … …
… … …
exception
… … …
end;
A simple PL/SQL program
- First, start up Oracle, and at the SQL> prompt type in the command SET SERVEROUTPUT ON.
(This enables the display of output from procedures like DBMS_OUTPUT.PUT_LINE to appear.)
- Type the following text using say Windows Notepad. (While PL/SQL is not case sensitive, we here shall use the convention of typing reserved words in upper case, and user-assigned names in lower case. Whether or not you follow this convention is a matter of personal choice.)
DECLARE
-- variable to hold the current date
TodaysDate DATE;
BEGIN
TodaysDate := SYSDATE;
DBMS_OUTPUT.PUT_LINE(‘Today is ‘);
DBMS_OUTPUT.PUT_LINE(TodaysDate);
END;
[Note that PL/SQL is a strongly typed language, which means all variables must be declared prior to use]
- Save this text for later use in a file (say called example1.txt).
- Copy the text in Notepad, and having fired up Oracle, paste it into SQL*Plus.
- Press the Enter key to get to a new prompt line, and then type /, followed by Enter again, and your program should execute.
If you get a message like {Warning: Created with compilation errors} then try the command SHOW ERRORS to examine the details of the coding errors found.
Variables and Types
Database and other information is handled by PL/SQL through variables. Each variable must be declared to be of a defined type. The types available include all the types used in SQL for database table columns, plus generic types used in PL/SQL like the NUMBER type. For example
DECLARE
unit_price NUMBER;
description VARCHAR(15);
It is important that if a PL/SQL variable is going to be used to store or retrieve data in a database column, that the variable be of the same type as the column. One way to do this is to use the %TYPE operator, as in
DECLARE
required_code product.id%TYPE
which declares that the variable "required_code" will have the same type as the column "id" of the "product" table.
We can in addition declare a variable to be suitable to hold a complete row of a table (a record with several fields) by
DECLARE
product_tuple product%ROWTYPE;
All variables are given an initial value of NULL unless declared otherwise, as in
DECLARE
x NUMBER := 3;
Program Structure
| According to programming theory, all programs can be built up with combinations of three basic structures - sequence, selection, and iteration. The program described above used the simplest of these three structures - the sequence. The program example that follows uses the more complex selection structure. |
|
Date and Character String Manipulation
The file DateTime.sql contains the following code:
remark - Requires SET SERVEROUTPUT ON command for
remark - DBMS_OUTPUT.PUT_LINE results to be displayed.
DECLARE
TodaysDate DATE;
Today VARCHAR2 (9);
TimeNow CHAR (5);
Hour24 CHAR(2);
Meridian CHAR (4);
BEGIN
TodaysDate := SYSDATE;
Today := TO_CHAR(TodaysDate, 'DAY');
Today := RTRIM(Today);
Today := SUBSTR(Today,1,1) || LOWER(SUBSTR(Today,2,LENGTH(Today) - 1));
DBMS_OUTPUT.PUT_LINE('Today is ' || Today || ',' || TO_CHAR(TodaysDate));
TimeNow := TO_CHAR(TodaysDate, 'HH:MI');
Hour24 := TO_CHAR(TodaysDate, 'HH24');
IF Hour24 < '12:00' THEN
Meridian := 'A.M.';
ELSE
Meridian := 'P.M.';
END IF;
DBMS_OUTPUT.PUT_LINE('Your session started at ' || TimeNow || ' ' || Meridian);
END;
/
When run, this script produces the following typical output:
| Today is Monday,05-NOV-07 |
[If no output is produced, make sure that you have used the SET SERVEROUTPUT ON command at the SQL> prompt.]
If you are new to programming, or hesitant about jumping into PL/SQL at the deep end, there is a step-by-step analysis of this program that might help break the ice.
Working through the program code step-by-step, SYSDATE delivers today’s system date. [Note that like all Oracle dates, this also includes the time.]
The function TO_CHAR converts either a number or a a date to a character string. When used with dates, the function is expressed as TO_CHAR(
Common formats used with dates are:
| Format | Returns | Example |
| YYYY | Four digit year | 2008 |
| Y or YY or YYY | Last one, two or three digits of year | 8 or 08 or 008 for 2008 |
| BC or AD | B.C. or A.D. | 2008 A.D. |
| RR | Last two digits of the year, but modified to cross century boundaries. | 98 for 1998 |
| SYEAR or YEAR | Year spelled out; using an S places a minus sign before B.C. dates. | TWO THOUSAND EIGHT |
| Q | Quarter of the year | returns 2 for dates between April and June inclusive |
| MM | Month | Jan = 01..Dec=12 |
| RM | Roman numeral month | IV for April |
| MONTH | Name of month as a nine-character upper-case string | FEBRUARY |
| Month | Name of month as a nine-character mixed-case string | February |
| MON | Three-letter abbreviation for the name of the month | FEB |
| WW | Week of year | |
| W | Week of month | |
| DDD | Day of year | Jan 1st is 1, Feb 1st is 32 etc. |
| DD | Day of the month (01..31) | 28 |
| DDTH | Day of the month as an ordinal number | 28TH |
| D | Day of the week (1-7) | 4 for Wednesday |
| DAY | Day of the week, spelt out in upper-case | FRIDAY |
| Day | Day of the week, spelt out in mixed-case | Friday |
| DY | Abbreviated day name | SUN, MON etc. |
| AM, PM, A.M., P.M. | Meridian indicator (with and without periods) | AM |
| HH or HH12 | Hour of day (using 12-hour clock) | 11 |
| HH24 | Hour of day (using 24 hour clock) | 15 |
| MI | Minutes | (0..59) |
| SS | Seconds | (0..59) |
The RTRIM function removes trailing spaces from a character string.
Common string manipulation functions include:
| Function | Returns |
| SUBSTR( | Extracts length characters from the string, starting at character number start |
| INSTR( | the character position within string where there is a match for the text. |
| LENGTH( | the length of string. |
| RTRIM( | Removes trailing spaces from string |
| LOWER( | Converts the string to lower case |
String concatenation is achieved with the symbol ||.
IF/THEN/ELSE construct
The code described above used the selection structure IF… THEN… ELSE … This construct can be nested, as in the code fragment:
Today := RTRIM(TO_CHAR(SYSDATE,‘DAY’);
IF Today = ‘SUNDAY’ THEN
DBMS_OUTPUT.PUT_LINE(‘It is the weekend’);
ELSE
IF Today = ‘SATURDAY’ THEN
DBMS_OUTPUT.PUT_LINE(‘It is the weekend’);
ELSE
DBMS_OUTPUT.PUT_LINE(‘It is a weekday’);
END IF;
END IF;
Multiway branches can be coded as
IF THEN
ELSEIF THEN
ELSEIF THEN
ELSE
END IF;
for example
IF age<18 THEN salary:=1000 ;
ELSEIF age < 25 THEN salary:=1500;
ELSEIF age < 35 THEN salary:=2000;
ELSE salary:=2500;
END IF;
Using SQL commands within PL/SQL blocks
The command file PLSQLselect.sql contains the following code:
DECLARE
pname varchar(20);
price number;
stock number;
BEGIN
SELECT product_name,unit_price, on_hand
INTO pname, price, stock
FROM product_t
WHERE unit_price = (SELECT MAX(unit_price) FROM product_t);
DBMS_OUTPUT.PUT('There are '||TO_CHAR(stock,'99')||' units of the product '||
pname||' in stock,');
DBMS_OUTPUT.PUT_LINE(' each valued at '||TO_CHAR(price,'$999.99'));
END;
/
and uses PL/SQL to format the output of an SQL SELECT statement, in this case to identify the product with the highest unit price, producing
The command file includes a special form of the SELECT statement with the selected values being stored INTO the declared variables listed. This only works for retrieval of a single row (and so this routine will not work if, as might happen, there are two or more products with the same highest price). It is possible to use select where multiple rows will be retrieved, but this requires the use of cursors, which we will cover in a later session.
We can also use the INSERT command in a PL/SQL block, as in the command file InsertForLoop.sql
DROP TABLE seat;
CREATE TABLE seat (SeatRow CHAR(1), SeatColumn NUMBER);
DECLARE
RowCount BINARY_INTEGER;
ColCount BINARY_INTEGER;
Rows CHAR (3);
AlphaRow CHAR;
BEGIN
Rows := 'ABC';
FOR RowCount IN 1..3
LOOP
AlphaRow := SUBSTR(Rows,RowCount,1);
FOR ColCount IN 1..5
LOOP
INSERT INTO seat VALUES(AlphaRow, ColCount);
END LOOP;
END LOOP;
END;
/
which creates a seating file for an auditorium with 3 rows of seats (A, B, and C), and 5 seats per row (1..5). The SUBSTR(s, n, l) extracts l characters from the string s, starting at the n'th character.
The construct
FOR IN
END LOOP;
has been used to control the iterative process, and produce the following table contents:
Loop constructs in PL/SQL
As well as the construct already described
FOR IN
END LOOP;
there is also (for a decrementing index)
FOR IN REVERSE
END LOOP;
There is also a WHILE loop construct
WHILE
END LOOP;
where the LOOP is repeated as long as the stated condition holds TRUE. Clearly, for the loop to terminate, at least one statement in the loop body must be capable of making the condition take the value FALSE, for example
X := 2;
WHILE X < 10 LOOP
Y:=Y+X;
X := X+2
END LOOP;
which computes in Y the sum of all single-digit even numbers;
The basic form of iterative control is the simple LOOP, as in
LOOP
END LOOP;
The statements should include an exit statement, otherwise the loop will be executed infinitely. Thus the code fragment
X := 1;
LOOP
Y:=X*X;
IF Y >=50 THEN
EXIT;
END IF;
END LOOP;
will find the highest number whose square is less than or equal to 50.
The EXIT WHEN statement ends a loop conditionally, as in
X := 1;
LOOP
Y:=X*X;
EXIT WHEN Y >=50;
END LOOP;
The UPDATE command in PL/SQL
Consider the following procedure named debit_account, which debits a bank account: When invoked or called, this procedure accepts an account number and a debit amount. It uses the account number to select the account balance from the accts database table. Then, it uses the debit amount to compute a new balance. If the new balance is less than zero, an exception is raised; otherwise, the bank account is updated.
PROCEDURE debit_account (acct_id INTEGER, amount REAL) IS
old_balance REAL;
new_balance REAL;
overdrawn EXCEPTION;
BEGIN
SELECT bal INTO old_balance FROM accts
WHERE acct_no = acct_id;
new_balance := old_balance - amount;
IF new_balance < 0 THEN
RAISE overdrawn;
ELSE
UPDATE accts SET bal = new_balance
WHERE acct_no = acct_id;
END IF;
EXCEPTION
WHEN overdrawn THEN
...
END debit_account;
As an exercise
- Modify the PL/SQL program DateTime.sql to display the date in the form Monday, 5th Nov 2007.
- Write a PL/SQL program to print out a small (say 5x5) multiplication table.
- Construct a version of InsertForLoop.sql that will add a field price to each row, and will set the seat price to $20 if in the front (A) row, and $15 otherwise.
References
Many of the examples described above are drawn from chapter 4 of Morison, J. & Morison, M. A Guide to Oracle 9i, 2003, published by Course technology.
Posts
1 comment:
If anyone wants the PDF versions, I will be happy to upload them. Just Let me know cheers.
Post a Comment