CREATE TABLE table
(column_1 data_type_1,
column_2 data_type_2,
...
PRIMARY KEY (columns)
);
CREATE TABLE product (
p_code VARCHAR2(6),
p_name VARCHAR2(15),
price NUMBER(4, 2),
launch_dt DATE,
PRIMARY KEY ( p_code )
);
When creating a table, you should always add a primary key, even though a primary key is optional. A primary key is a column or a set of columns that uniquely identify every row in the table.
INSERT INTO table
(column_1,
column_2,
... )
VALUES (value_1,
value_2,
... )
);
INSERT INTO product (
p_code,
p_name,
price,
launch_dt
) VALUES (
1,
'Nail',
10.0,
'31-MAR-2013'
);
You can only add one row in an INSERT statement. Issue a COMMIT command to persist (confirm the storage of) the rows
UPDATE table_name
SET column_1 = new_value_1 [,
column_2 = new_value_2,
... ]
[WHERE condition];
UPDATE product
SET
price = price - ( price * 0.05 );
You specify which rows to update in the WHERE clause. Without a WHERE clause, all rows will be updated. With a WHERE clause, only rows that meet the condition will be updated. If no row meets the condition in the WHERE clause, nothing will be updated.
UPDATE product
SET
price = price - ( price * 0.05 )
WHERE
p_code = 9;
DELETE FROM table
[WHERE condition];
DELETE FROM product
WHERE
p_name = 'Nut';
Without the WHERE condition, all rows will be deleted and the product table will be empty. You cannot delete some of the columns in a row; the DELETE statement deletes the whole row. If you need to change the content of a specific column, use the UPDATE statement.
SELECT column_names FROM table_name [WHERE condition];
SELECT * FROM product;
SELECT p_name, price FROM product;
SELECT column_names FROM table_name [WHERE condition];
SELECT p_name, price FROM product WHERE price = 15;
SELECT p_name, price FROM product WHERE p_name != 'Nut';
Using the AND and OR logical operator you can combine predicates to form a compound condition. Only rows that satisfy the compound condition will be returned by the query.
SELECT
*
FROM
product
WHERE
( launch_dt >= '30-MAR-13'
OR price > 15 )
AND ( p_name != 'Nail' );
If a compound condition contains both the OR condition and the AND condition, the AND condition will be evaluated first because AND has a higher precedence than OR. However, anything in parentheses will have an even higher precedence than AND. For example, the SELECT statement has an OR and an AND, but the OR condition is in parentheses so the OR condition is evaluated first. If you remove the parentheses in the SELECT statement, the query will return a different result.
SELECT
*
FROM
product
WHERE
launch_dt >= '30-MAR-13'
OR price > 15
AND p_name != 'Nail';
Without the parentheses, the compound condition price > 15 AND p_name != 'Nail' will be evaluated first, resulting in the Screw and Super_Nut. The result is then OR-ed with the launch_dt >= 30-MAR-13'
You can use NOT to negate a condition and return rows that do not satisfy the condition.
SELECT
*
FROM
product
WHERE
NOT ( launch_dt >= '30-MAR-13'
OR price > 15
AND p_name != 'Nail' );
SELECT
*
FROM
product
WHERE
( launch_dt >= '30-MAR-13'
OR price > 15 )
AND NOT ( p_name != 'Nail' );
The BETWEEN operator evaluates equality to any value within a range. The range is specified by a boundary, which specifies the lowest and the highest values
SELECT columns FROM table
WHERE column BETWEEN(lowest_value, highest_value);
The boundary values are inclusive, meaning lowest_value and highest_value will be included in the equality evaluation.
SELECT
*
FROM
product
WHERE
price BETWEEN 15 AND 25;
The IN operator compares a column with a list of values
SELECT columns FROM table
WHERE column IN(value1, value2, ...);
SELECT * FROM product WHERE price IN (10, 25, 50);
The LIKE operator allows you to specify an imprecise equality condition
SELECT columns FROM table
WHERE column LIKE ' ... wildcard_character ... ';
SELECT * FROM product WHERE p_name LIKE 'N__' OR p_name LIKE 'Sc%';
If the string you specify in the LIKE operator contains an underscore or a percentage sign, SQL will regard it as a wild character
SELECT * FROM product WHERE p_name LIKE '%\_%' ESCAPE '\';
SELECT * FROM product WHERE price NOT BETWEEN 15 AND 25;
SELECT * FROM product WHERE price IS NULL;
Invalid usage of the equal operator on NULL
SELECT * FROM product WHERE price = NULL;
Executing the query produces no output.
SELECT column_1 AS alias1, column_2 AS alias2, ...
FROM table;
SELECT
p_code,
p_name AS "PRODUCT NAME"
FROM
product;
An output column can also be an expression. An expression in the SELECT clause can include columns, literal values, arithmetic or string operators, and functions
SELECT
p_code,
'p_name in Uppercase: ' || upper(p_name) AS "PRODUCT NAME",
( price * 100 ) AS "NORMALIZED_PRICE",
to_char(launch_dt, 'DD/MM/YYYY') AS "LAUNCH_DATE"
FROM
product;
You can limit the number of output row by using the ROWNUM pseudo column
SELECT columns FROM table(s)
WHERE conditions AND ROWNUM < count;
SELECT * FROM product WHERE price > 10 AND ROWNUM < 4;
A query may return duplicate rows. Two rows are duplicates if each of their columns contains exactly the same data. If you don’t want to see duplicate output rows, use DISTINCT in your SELECT clause. You can use DISTINCT on one column or multiple columns.
SELECT DISTINCT price FROM product ORDER BY price;
SELECT DISTINCT price, launch_dt FROM product ORDER BY price;
You can manipulate your query output further by using aggregate functions.
MAX(column): The maximum column value
MIN(column): The minimum column value
SUM(column): The sum of column values
AVG(column): The average column value
COUNT(column): The count of rows
COUNT(*): The count of all rows including NULL
SELECT
MAX(price),
MIN(price),
SUM(price),
AVG(price),
COUNT(price),
COUNT(*)
FROM
product;
expressionCASE allows you to have dynamic query output in which a column value may vary depending on the value of the column. CASE comes in two flavors: Simple and Searched
SELECT columns,
CASE column
WHEN equal_value1
THEN output_value1
WHEN equal_value2
THEN output_value2
WHEN ...
[ELSE else_value]
END AS output_column
FROM table
WHERE ... ;
In the Simple CASE, column_name is compared to equal_values in the WHEN clause, starting from the first WHEN and down to the last WHEN. If column_name matches a WHEN value, the value right after the THEN clause is returned and the CASE process stops. If column_name matches none of the WHEN values, else_value is returned if there exists an ELSE clause. If column_name matches none of the WHEN values but no ELSE clause exists, NULL will be returned.
SELECT
p_code,
p_name,
CASE price
WHEN 10 THEN
'Cheap'
WHEN 15 THEN
'Medium'
WHEN 25 THEN
'Expensive'
ELSE
'Others'
END AS price_cat
FROM
product;
The case in the Simple CASE compares a column with various values. On the hand, the case in the Searched CASE can be any condition
SELECT columns,
CASE
WHEN condition1
THEN output_value1
WHEN condition2
THEN output_value2
WHEN ...
ELSE else_value
END AS output_column
FROM table
WHERE ... ;
SELECT
p_code,
p_name,
CASE
WHEN ( price <= 10
AND p_name NOT LIKE 'Nut%' ) THEN
'Cheap'
WHEN price BETWEEN 11 AND 25 THEN
'Medium'
WHEN price > 25
AND to_char(launch_dt, 'YYYYMMDD') > '20130329' THEN
'Expensive'
WHEN price IS NULL THEN
'Not valid'
ELSE
'Others'
END AS product_cat
FROM
product;
To order the output, use the ORDER BY clause. The ORDER BY clause must appear last in a SELECT statement.
SELECT columns FROM
table
WHERE condition ORDER BY column(s)
You can order output rows in one of the following methods.
- by one or more columns
- in ascending or descending direction
- by using the GROUP BY clause
- by using UNION and other set operators
SELECT * FROM product ORDER BY p_name;
The default direction is ascending
SELECT * FROM product ORDER BY p_name DESC;
To order by more than one column, list the columns in the ORDER BY clause. The sequence of columns listed is significant. The order will be conducted by the first column in the list, followed by the second column, and so on. For example, if the ORDER BY clause has two columns, the query output will first be ordered by the first column. Any rows with identical values in the first column will be further ordered by the second column.
SELECT * FROM product ORDER BY launch_dt, price;
You can apply different order directions on ordered columns too
SELECT * FROM product ORDER BY launch_dt, price DESC;
If your SELECT statement has both the WHERE clause and the ORDER BY clause, ORDER BY must appear after the WHERE clause.
SELECT
*
FROM
product
WHERE
p_name = 'Nut'
ORDER BY
p_name,
p_code DESC;
You can store a query output into a new or existing table.
CREATE TABLE new_table AS SELECT ... ;
CREATE TABLE nut_product
AS
SELECT
*
FROM
product
WHERE
p_name LIKE '%Nut%';
INSERT INTO existing_table AS SELECT ... ;
INSERT INTO non_nut
SELECT
*
FROM
product
WHERE
p_name NOT LIKE '%Nut%';
In a query the GROUP BY clause appears after the WHERE clause and before the ORDER clause, if any.
SELECT columns,
aggregate_function(group_columns)
FROM table(s)
WHERE condition
GROUP BY group_columns
ORDER BY column(s);
SELECT
launch_dt,
MAX(price) max,
MIN(price) min,
SUM(price) sum,
AVG(price) avg,
COUNT(price) count,
COUNT(*) AS "COUNT(*)"
FROM
product
GROUP BY
launch_dt
ORDER BY
launch_dt;
You can group by more than one column. If you do that, rows having the same value on all the columns will form a group
SELECT
price,
launch_dt,
MAX(price) max,
MIN(price) min,
SUM(price) sum,
AVG(price) avg,
COUNT(price) count,
COUNT(*) "COUNT(*)"
FROM
product
GROUP BY
price,
launch_dt
ORDER BY
price,
launch_dt;
SELECT columns,
aggregate_function(group_columns)
FROM table(s)
WHERE condition
GROUP BY group_columns
HAVING aggregate_condition
ORDER BY columns;
The WHERE condition can be used to select individual rows. On the other hand, the HAVING condition is used for selecting individual groups. Only groups that satisfy the condition in the HAVING clause will be returned by the query. In other words, the HAVING condition is on the aggregate, not on a column. If present, the HAVING clause must appear after the GROUP BY
SELECT
price,
launch_dt,
MAX(price) max,
MIN(price) min,
SUM(price) sum,
AVG(price) avg,
COUNT(price) count,
COUNT(*) "COUNT(*)"
FROM
product
GROUP BY
price,
launch_dt
HAVING
COUNT(price) > 1
ORDER BY
price,
launch_dt;
A primary key is a column, or a set of columns, which uniquely identifies every row in a table. A foreign key is a column, or a set of columns, which is used to relate to the primary key of another table. The process of using the foreign key/primary key to relate rows from two tables is called joining.
While a primary key must be unique, a foreign key does not have to be unique. You can have a foreign key in more than one row
Even though the use of primary and foreign keys is not an absolute requirement for joining tables, their absence may cause you to incorrectly join tables.
To query data from multiple tables, use the JOIN keyword to specify the related columns from two tables. The JOIN clause of a SELECT statement joins related rows from two or more tables, based on their primary key/foreign key relationship.
SELECT columns FROM table_1, table_2, ... table_n
WHERE table_1.primary_key = table_2.foreign_key
AND table_2.primary_key = table_n.foregin_key;
SELECT
c_name,
p_code,
c_order.qty,
c_order.order_dt
FROM
c_order
JOIN customer ON c_order.c_no = customer.c_no;
In a join query, different tables can have columns with identical names. To make sure you refer to the correct column of a table, you need to qualify it with its table
SELECT
c_name,
p_code,
o.qty,
o.order_dt
FROM
c_order o
JOIN customer c ON o.c_no = c.c_no;
SELECT
c_name,
p_name,
o.qty,
o.order_dt
FROM
c_order o
JOIN customer c ON o.c_no = c.c_no
JOIN product p ON o.p_code = p.p_code;
SELECT
c_name,
p_name,
o.qty,
o.order_dt
FROM
c_order o
JOIN customer c ON o.c_no = c.c_no
JOIN product p ON o.p_code = p.p_code
WHERE
p_name NOT LIKE 'Super%';
The preceding joins were on one column. Tables can also be joined on more than one column.
SELECT columns FROM table_1, table_2
WHERE table_1.column_1 = table_2.column_1
AND table_1.column_2 = table_2.column_2
...
AND table_1.column_n = table_2.column_n;
SELECT
o.c_no,
o.p_code,
o.order_dt,
ship_qty,
ship_dt,
qty
FROM
shipment s
JOIN c_order o ON s.c_no = o.c_no
AND s.p_code = o.p_code
AND s.order_dt = o.order_dt;
While an inner join query produces only related rows from the joined tables, an outer join query produces all rows from one table even when some of the rows do not have matching rows from the other table There are three subtypes of outer joins, LEFT, RIGHT, and FULL
All rows from the table on the left of the left outer join will be in the output whether or not there are matching rows from the table on its right.
SELECT columns FROM table_1 RIGHT OUTER JOIN table_2 ON
table_1.column = table_2.column ... ;
SELECT
o.*,
ship_dt
FROM
c_order o
LEFT OUTER JOIN shipment s ON o.p_code = s.p_code
AND o.c_no = s.c_no;
The full outer join returns all rows from both tables whether or not there are matching rows from the opposite table.
SELECT columns
FROM table_1 FULL OUTER JOIN table_2
ON table_1.column = table_2.column … ;
SELECT
o.*,
s.*
FROM
c_order o
FULL OUTER JOIN shipment s ON o.p_code = s.p_code
AND o.c_no = s.c_no;
SELECT columns
FROM table alias_1
JOIN table alias_2
ON alias_1.column_x = alias_2.column_y;
SELECT
prod.p_code,
prod.p_name,
subst.p_code subst_p_code,
subst.p_name subst_name
FROM
product prod
LEFT OUTER JOIN product subst ON prod.s_code = subst.p_code
ORDER BY
prod.p_code;
If two tables have columns that share a name, you can naturally join the two tables on these columns. In a natural join, you do not need to specify the columns that the join should use
SELECT columns FROM table_1 NATURAL JOIN table_2 ... ;
SELECT * FROM c_order NATURAL JOIN customer;
SELECT
*
FROM
c_order o
NATURAL RIGHT JOIN product p
WHERE
o.order_dt = p.launch_dt;
A natural join will use all columns with the same names from the joined tables. If you want your query to join only on some of these identically named columns, instead of using the NATURAL keyword, use the USING keyword.
SELECT columns
FROM table_1
JOIN table_2 USING (column);
SELECT
p_code,
SUM(s.ship_qty)
FROM
c_order o
JOIN shipment s USING ( p_code )
GROUP BY
p_code;
A subquery is a query nested within another query. The containing query is called an outer query. A subquery in turn can have a nested query, making it a multiple nested query
A single-row subquery is a subquery that returns a single value. A singlerow subquery can be placed in the WHERE clause of an outer query. The return value of the subquery is compared with a column of the outer query using one of the comparison operators.
SELECT
*
FROM
product
WHERE
price = (
SELECT
MAX(price)
FROM
product p
INNER JOIN c_order o ON p.p_code = o.p_code
);
The column and subquery result do not have to be the same column, but they must have compatible data types
A subquery that returns more than one value is called a multiple-row subquery. This type of subquery also occurs in the WHERE clause of an outer query, however instead of using a comparison operator, you use IN or NOT IN in the WHERE clause.
SELECT
*
FROM
product
WHERE
price IN (
SELECT
MAX(price)
FROM
product p
INNER JOIN c_order s ON p.p_code = s.p_code
GROUP BY
p.launch_dt
);
In addition to IN and NOT IN, you can also use the ALL and ANY operators in a multiple-row subquery. With ALL or ANY you use a comparison operator.
SELECT
*
FROM
product
WHERE
price >= ALL (
SELECT
MAX(price)
FROM
product p
INNER JOIN c_order o ON o.p_code = o.p_code
GROUP BY
p.launch_dt
)
ORDER BY
p_code;
SELECT
*
FROM
product
WHERE
price = ANY (
SELECT
MAX(price)
FROM
product p
INNER JOIN c_order o ON p.p_code = o.p_code
GROUP BY
p.launch_dt
)
ORDER BY
p_code;
A subquery can contain another query, making it a query with multiple nested subqueries.
SELECT
customer.*
FROM
customer
WHERE
c_no IN (
SELECT
c_no
FROM
c_order
WHERE
p_code IN (
SELECT
p_code
FROM
product
WHERE
p_name NOT LIKE '%Nut%'
)
);
All the preceding subqueries are independent of their outer queries. A subquery can also be related to its outer query, where one or more column from the outer query table is (are) related to the column(s) of the subquery table in the WHERE clause of the subquery. This type of subquery is called the correlated subquery.
SELECT
customer.*
FROM
customer
WHERE
c_no IN (
SELECT
c_no
FROM
c_order o
JOIN product p ON o.p_code = p.p_code
WHERE
p_name NOT LIKE '%Nut%'
AND customer.c_no = o.c_no
);
You can combine the results of two or more SELECT statements using the UNION ALL, UNION, INTERSECT, or MINUS operators. The number of output columns from every statement must be the same and the corresponding columns must have identical or compatible data types.
When you combine two or more queries with the UNION ALL operator, the overall output will be the total rows from all the queries
SELECT
p_code,
p_name,
'FIRST QUERY' query
FROM
product p
WHERE
p_name LIKE '%Nut%'
UNION ALL
SELECT
p.p_code,
p_name,
'SECOND_QUERY' query
FROM
c_order o
INNER JOIN product p ON o.p_code = p.p_code;
SELECT
p_code,
p_name,
'FIRST QUERY' query
FROM
product p
WHERE
p_name LIKE '%Nut%'
UNION ALL
SELECT
p.p_code,
p_name,
'SECOND_QUERY' query
FROM
c_order o
INNER JOIN product p ON o.p_code = p.p_code
ORDER BY
p_code;
UNION is similar to UNION ALL. However, with UNION duplicate rows will be returned once only
SELECT
p_code,
p_name
FROM
product p
WHERE
p_name LIKE '%Nut%'
UNION
SELECT
p.p_code,
p_name
FROM
c_order o
INNER JOIN product p ON o.p_code = p.p_code
ORDER BY
p_code;
UNION is similar to UNION ALL. However, with UNION duplicate rows will be returned once only
SELECT
p_code,
p_name
FROM
product p
WHERE
p_name LIKE '%Nut%'
UNION
SELECT
p.p_code,
p_name
FROM
c_order o
INNER JOIN product p ON o.p_code = p.p_code
ORDER BY
p_code;
When you combine two or more queries with the INTERSECT operator, the output will consist of rows common to all the participating SELECT statements. In other words, only if a row is returned by all the SELECT statements will the row be included in the final result
SELECT
p_code,
p_name
FROM
product p
WHERE
p_name LIKE '%Nut%'
INTERSECT
SELECT
p.p_code,
p_name
FROM
c_order o
INNER JOIN product p ON o.p_code = p.p_code
ORDER BY
p_code;
When you combine two SELECT statements using the MINUS operator, the final output will be rows from the first query that are not returned by the second query
SELECT
p_code,
p_name
FROM
product p
WHERE
p_name LIKE '%Nut%'
MINUS
SELECT
p.p_code,
p_name
FROM
c_order o
INNER JOIN product p ON o.p_code = p.p_code
ORDER BY
p_code;
A view is effectively a predefined query. You create and use views most frequently for the following purposes:
- Hiding table columns (for security protection)
- Presenting pre-computed columns (in lieu of table columns)
- Hiding queries (so that the query outputs are available without running the queries)
You create a view using the CREATE VIEW statement.
The SELECT statement at the end of the CREATE VIEW statement is the predefined query. When you use a view its predefined query is executed. Since a query result is a table that is not persisted (stored) in the database, a view is also known as a virtual table. The table in the SELECT statement of a view is known as a base table. One of the reasons you use a view is when you have a table you need to share with other people. If you don’t want some of the table columns viewable by others, you can use a view to hide those columns. You would then share the view and restrict access to the base table.
CREATE VIEW product_v (
p_code,
p_name
) AS
SELECT
p_code,
p_name
FROM
product;
The product_v view can now be used just as you would any database table
SELECT * FROM product_v WHERE p_name NOT LIKE '%Nut%';
Note that within a database a view name must be unique among all the views and tables in the database. Another use of the view is to derive computed columns not available in the base table(s)
CREATE VIEW product_sell_v
(p_no , p_name, sell_price
) AS
SELECT p_code, p_name, (price + margin) FROM product;
CREATE VIEW prod_subs_v AS
SELECT
prod.p_code,
prod.p_name,
subst.p_code subst_p_code,
subst.p_name subst_name
FROM
product prod
LEFT OUTER JOIN product subst ON prod.s_code = subst.p_code
ORDER BY
prod.p_code;
A view can be based on another view. Such a view is called a nested view.
CREATE VIEW ps_noname_v (
p_no,
sell_price
) AS
SELECT
p_no,
sell_price
FROM
product_sell_v;
You can easily manage your views in Oracle. To see all views in the current database, execute the following statement.
SELECT VIEW_NAME FROM USER_VIEWS;
To delete a view, use the DROP VIEW statement. The syntax for the DROP VIEW statement is as follows
DROP VIEW view_name;
The Oracle database provides functions that you can use in your queries. These built-in functions can be grouped into numeric functions, character functions, datetime functions, and functions for handling null values
ABS(n) returns the absolute value of n
SELECT p_code, price, (price – 20), ABS(price – 20.00) FROM product;
ROUND(n, d) returns a number rounded to a certain number of decimal places. The argument n is the number to be rounded and d the number of decimal places.
SELECT p_code, price, ROUND (price, 1) FROM product;
SIGN(n) returns a value indicating the sign of n. This function returns -1 for n < 0, 0 for n = 0, and 1 for n > 0.
SELECT p_code, price, SIGN(price – 15) FROM product;
TRUNC(n, d) returns a number truncated to a certain number of decimal places. The argument n is the number to truncate and d the number of decimal places
SELECT p_code, price, TRUNC(price, 1) FROM product;
CONCAT(string1, string2) concatenates string1 and string2 and returns the result. If you pass a number as an argument, the number will first be converted to a string.
SELECT p_code, CONCAT(CONCAT(p_name, ' -- ') , price) FROM product;
You can also use the || operator to concatenate strings
LOWER(str) converts str to lowercase and UPPER(str) converts str to uppercase
SELECT p_name, LOWER(p_name), UPPER(p_name) FROM product;
LENGTH(str) returns the length of string str. The length of a string is the number of characters in it.
SELECT p_name, LENGTH(p_name) FROM product;
SUBSTR(str, start_position, [length]) returns a substring of str starting from the position indicated by start_position. If length is not specified, the function returns a substring from start_position to the last character in str. If length is present, the function returns a substring which is length characters long starting from start_position. If length is less than 1, the function returns an empty string.
SELECT SUBSTR(phone, 3) FROM customer;
CURRENT_DATE() returns the current date (the current date of the Oracle server at the time you run the query).
SELECT p_code, launch_dt, CURRENT_DATE FROM product;
TO_CHAR(dt, fmt_specifier) converts a date (dt) to a string in the format specified by fmt_specifier. In the following example, the launch_dt column is formatted with a format specifier that has three components:
- DD - the day of the month
- MONTH - the long name of the month in uppercase
- YYYY - the year
SELECT p_code, TO_CHAR(launch_dt, 'DD MONTH YYYY') reformatted_dt
FROM product;
COALESCE(expr-1, expr-2, ..., expr-n) returns the first expression from the list that is not NULL
SELECT p_name, price, min_price,
COALESCE((price * 0.9), min_price, 5.0) sale_price
FROM product;
NULLIF (expr1, expr2) compares expr1 and expr2. If they are equal, the function returns null. If they are not equal, the function returns expr1
SELECT
p_code,
p_name,
nullif(p.price, op.price) current_price
FROM
product p
JOIN old_product op USING ( p_code );
NVL (expr1, expr2) returns exprs1 if expr1 is not NULL; otherwise, it returns expr2.
SELECT
p_code,
p.p_name,
p.price current_price,
nvl(op.price, p.price) old_price
FROM
product p
LEFT OUTER JOIN old_product op USING ( p_code );
PL/SQL, short for Procedural Language extension to SQL, complements SQL with a procedural programming language. PL/SQL is a feature of the Oracle database. The objective of this is to introduce some of the most commonly used PL/SQL features such as
- row-by-row processing of query output
- if-then-else decision logic
- exception handling
- user-defined functions
You can write a PL/SQL program to process query output row-by-row sequentially. The structure of the PL/SQL program for row-by-row processing is as follows.
BEGIN
FOR output_row_variable IN (
SELECT ...)
LOOP
row processing statements;
END LOOP;
END;
BEGIN
FOR invc IN (
SELECT
c_no,
c_name,
p_name,
qty,
price unit_prc,
( qty * price ) total_prc,
sysdate invoice_dt,
launch_dt
FROM
c_order
NATURAL JOIN product
NATURAL JOIN customer
) LOOP
INSERT INTO invoice VALUES (
invc.c_no,
invc.c_name,
invc.p_name,
invc.qty,
invc.unit_prc,
invc.total_prc,
invc.invoice_dt
);
END LOOP;
END;
Note that to execute a PL/SQL program you need to enter the forward slash / after the last END;.
You can use an if statement to branch in a program
BEGIN
FOR invc IN (
SELECT
c_no,
c_name,
p_name,
qty,
price unit_prc,
( qty * price ) total_prc,
sysdate invoice_dt,
launch_dt
FROM
c_order
NATURAL JOIN product
NATURAL JOIN customer
) LOOP
IF invc.launch_dt IS NOT NULL THEN
INSERT INTO invoice VALUES (
invc.c_no,
invc.c_name,
invc.p_name,
invc.qty,
invc.unit_prc,
invc.total_prc,
invc.invoice_dt
);
ELSE
NULL;
END IF;
END LOOP;
END;
HandlingPL/SQL allows you to handle errors (or exceptions) in your program using the EXCEPTION statement. Its syntax is as follows.
EXCEPTION
WHEN exception_name
THEN exception_handling_statement;
DECLARE
invc invoice%rowtype;
BEGIN
SELECT
*
INTO invc
FROM
invoice
WHERE
c_no = '&c_no_prompt'
AND p_name = '&p_name_prompt'
AND to_char(invoice_dt, 'DD-MON-YY') = '&invoice_dt_prompt';
dbms_output.put_line(invc.c_name
|| ' - '
|| invc.p_name
|| ' - '
|| invc.total_prc);
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('Error: The invoice does not exist!');
END;
To see the exception-handler’s message, issue a set serveroutput on command before you execute the program.
Using PL/SQL you can write your own functions. The syntax for a userdefined function is as follows.
CREATE FUNCTION FUNCTION name(parameters)
RETURN data_type
IS
Variable_declarations
BEGIN
Processing_statements
EXCEPTION
Exception_handling statements
END;
CREATE FUNCTION calc_new_price (
exist_price NUMBER,
change_percentage NUMBER
) RETURN NUMBER IS
BEGIN
RETURN exist_price + ( exist_price * change_percentage );
END;
Using the function
UPDATE product SET price = calc_new_price(price, 0.1) ;
The data dictionary of a database contains data about the data in the database. This data about data is also known as metadata. The data in the data dictionary is stored as tables. As such, you can use your SQL skills gained so far to query the data dictionary of your database. The tables and their data are maintained by the Oracle database system. You should not access the dictionary tables directly. Instead, use the Oracle-supplied views of the dictionary
The dictionary view is one of the views in the data dictionary. It returns all the views available in your dictionary. The view has two columns and can have over 650 rows
SELECT * FROM dictionary;
####The User_Catalog View The user_catalog view stores the names of your tables and views
SELECT * FROM user_catalog;
You can get table and view names from the User_Catalog view. To see the details of each table and view, can you query the user_tab_cols view.
SELECT
column_name,
data_type,
data_length,
data_precision,
data_scale
FROM
user_tab_cols
WHERE
table_name = 'PRODUCT';
The user_procedures view contains information about your stored programs, including functions, procedures and triggers.
SELECT object_name, object_type FROM user_procedures;
This view contains the source code of your stored functions
SELECT line, text FROM user_source WHERE name = 'CALC_NEW_PRICE';
An index entry of the Index section of a book points to the location of the word/phrase indicated by the entry. The index entry helps you find the pages containing the word/phrase. Similarly, a column index of a table can speed up your finding data in a database. If your query has a condition on a column (or columns) that is (are) not indexed, the table will be fully scanned and the query will be slower than if an index was available. The topics covered are as follows.
- Creating an index
- Multi-column indexes
- Bit map and join bit map indexes
- Function-based indexes
- Deleting an index
To create an index on a column or columns of a table, use this statement.
CREATE INDEX index ON table (columns);
CREATE INDEX p_name_ix ON product (p_name);
You cannot have duplicate index names. In addition, you cannot have the same column(s) indexed more than once.
An index can be based on multiple columns. A multi-column index is useful when you need to search on rows having the same value on an indexed column. For instance, if your query has to search on the p_name column of the product table and there can be more than one row with the same p_name but with different launch dates, it would help if you create an index on both p_name and launch_dt.
CREATE INDEX p_name_launch_ix ON product (p_name, launch_dt);
The indexes created in Listings C.1 and C.2 are ordinary indexes, which are technically called B-tree indexes. Another type of index, the bitmap index, can be a better choice for tables whose rows will not be changed concurrently, such as in a low-volume data entry environment. A product table is an example of such low data maintenance and therefore is suitable for bitmap indexes. By contrast, the customer order table gets rows inserted more frequently and is not a good candidate for bitmap indexes.
CREATE BITMAP INDEX order_bix ON c_order (p_code, c_no);
The bitmap join index is a variant of the bitmap index. The bitmap join index is specifically created to expedite column retrieval from a joined table in a join query.
CREATE BITMAP INDEX c_name_bji ON
c_order ( c.c_name ) FROM c_order o, customer c WHERE
o.c_no = c.c_no;
There are often cases where capitalization is used inconsistently when entering data into a table. Sometimes, a column value is entered in all capitals, sometimes in lowercase, and sometimes in mixed cases.
SELECT * FROM customer WHERE UPPER(c_name) LIKE '%STORE';
For this query to run faster, you should also create an index that is based on the UPPER-ed version of the c_name column
CREATE INDEX func_name_ix ON
customer ( upper(c_name) );
To delete an index of any type, use the DROP INDEX statement
DROP INDEX s_name_bji;