Oracle/PLSQL Topics: Loops and Conditional Statements
Oracle/PLSQL: Oracle System Tables
Below is an alphabetical listing of the Oracle system tables that are commonly used.
| System Table | Description |
| ALL_ARGUMENTS | Arguments in object accessible to the user |
| ALL_CATALOG | All tables, views, synonyms, sequences accessible to the user |
| ALL_COL_COMMENTS | Comments on columns of accessible tables and views |
| ALL_CONSTRAINTS | Constraint definitions on accessible tables |
| ALL_CONS_COLUMNS | Information about accessible columns in constraint definitions |
| ALL_DB_LINKS | Database links accessible to the user |
| ALL_ERRORS | Current errors on stored objects that user is allowed to create |
| ALL_INDEXES | Descriptions of indexes on tables accessible to the user |
| ALL_IND_COLUMNS | COLUMNs comprising INDEXes on accessible TABLES |
| ALL_LOBS | Description of LOBs contained in tables accessible to the user |
| ALL_OBJECTS | Objects accessible to the user |
| ALL_OBJECT_TABLES | Description of all object tables accessible to the user |
| ALL_SEQUENCES | Description of SEQUENCEs accessible to the user |
| ALL_SNAPSHOTS | Snapshots the user can access |
| ALL_SOURCE | Current source on stored objects that user is allowed to create |
| ALL_SYNONYMS | All synonyms accessible to the user |
| ALL_TABLES | Description of relational tables accessible to the user |
| ALL_TAB_COLUMNS | Columns of user's tables, views and clusters |
| ALL_TAB_COL_STATISTICS | Columns of user's tables, views and clusters |
| ALL_TAB_COMMENTS | Comments on tables and views accessible to the user |
| ALL_TRIGGERS | Triggers accessible to the current user |
| ALL_TRIGGER_COLS | Column usage in user's triggers or in triggers on user's tables |
| ALL_TYPES | Description of types accessible to the user |
| ALL_UPDATABLE_COLUMNS | Description of all updatable columns |
| ALL_USERS | Information about all users of the database |
| ALL_VIEWS | Description of views accessible to the user |
| DATABASE_COMPATIBLE_LEVEL | Database compatible parameter set via init.ora |
| DBA_DB_LINKS | All database links in the database |
| DBA_ERRORS | Current errors on all stored objects in the database |
| DBA_OBJECTS | All objects in the database |
| DBA_ROLES | All Roles which exist in the database |
| DBA_ROLE_PRIVS | Roles granted to users and roles |
| DBA_SOURCE | Source of all stored objects in the database |
| DBA_TABLESPACES | Description of all tablespaces |
| DBA_TAB_PRIVS | All grants on objects in the database |
| DBA_TRIGGERS | All triggers in the database |
| DBA_TS_QUOTAS | Tablespace quotas for all users |
| DBA_USERS | Information about all users of the database |
| DBA_VIEWS | Description of all views in the database |
| DICTIONARY | Description of data dictionary tables and views |
| DICT_COLUMNS | Description of columns in data dictionary tables and views |
| GLOBAL_NAME | global database name |
| NLS_DATABASE_PARAMETERS | Permanent NLS parameters of the database |
| NLS_INSTANCE_PARAMETERS | NLS parameters of the instance |
| NLS_SESSION_PARAMETERS | NLS parameters of the user session |
| PRODUCT_COMPONENT_VERSION | version and status information for component products |
| ROLE_TAB_PRIVS | Table privileges granted to roles |
| SESSION_PRIVS | Privileges which the user currently has set |
| SESSION_ROLES | Roles which the user currently has enabled. |
| SYSTEM_PRIVILEGE_MAP | Description table for privilege type codes. Maps privilege type numbers to type names |
| TABLE_PRIVILEGES | Grants on objects for which the user is the grantor, grantee, owner, or an enabled role or PUBLIC is the grantee |
| TABLE_PRIVILEGE_MAP | Description table for privilege (auditing option) type codes. Maps privilege (auditing option) type numbers to type names |
SQL: LIKE Condition
The LIKE condition allows you to use wildcards in the where clause of an SQL statement. This allows you to perform pattern matching. The LIKE condition can be used in any valid SQL statement - select, insert, update, or delete.
The patterns that you can choose from are:
% allows you to match any string of any length (including zero length)
_ allows you to match on a single character
Examples using % wildcard
The first example that we'll take a look at involves using % in the where clause of a select statement. We are going to try to find all of the suppliers whose name begins with 'Hew'.
SELECT * FROM suppliers
WHERE supplier_name like 'Hew%';
WHERE supplier_name like 'Hew%';
You can also using the wildcard multiple times within the same string. For example,
SELECT * FROM suppliers
WHERE supplier_name like '%bob%';
WHERE supplier_name like '%bob%';
In this example, we are looking for all suppliers whose name contains the characters 'bob'.
You could also use the LIKE condition to find suppliers whose name does not start with 'T'. For example,
SELECT * FROM suppliers
WHERE supplier_name not like 'T%';
WHERE supplier_name not like 'T%';
By placing the not keyword in front of the LIKE condition, you are able to retrieve all suppliers whose name does not start with 'T'.
Examples using _ wildcard
Next, let's explain how the _ wildcard works. Remember that the _ is looking for only one character.
For example,
SELECT * FROM suppliers
WHERE supplier_name like 'Sm_th';
WHERE supplier_name like 'Sm_th';
This SQL statement would return all suppliers whose name is 5 characters long, where the first two characters is 'Sm' and the last two characters is 'th'. For example, it could return suppliers whose name is 'Smith', 'Smyth', 'Smath', 'Smeth', etc.
Here is another example,
SELECT * FROM suppliers
WHERE account_number like '12317_';
WHERE account_number like '12317_';
You might find that you are looking for an account number, but you only have 5 of the 6 digits. The example above, would retrieve potentially 10 records back (where the missing value could equal anything from 0 to 9). For example, it could return suppliers whose account numbers are:
123170
123171
123172
123173
123174
123175
123176
123177
123178
123179.
123171
123172
123173
123174
123175
123176
123177
123178
123179.
Examples using Escape Characters
Next, in Oracle, let's say you wanted to search for a % or a _ character in a LIKE condition. You can do this using an Escape character.
Please note that you can define an escape character as a single character (length of 1) ONLY.
For example,
SELECT * FROM suppliers
WHERE supplier_name LIKE '!%' escape '!';
WHERE supplier_name LIKE '!%' escape '!';
This SQL statement identifies the ! character as an escape character. This statement will return all suppliers whose name is %.
Here is another more complicated example:
SELECT * FROM suppliers
WHERE supplier_name LIKE 'H%!%' escape '!';
WHERE supplier_name LIKE 'H%!%' escape '!';
This example returns all suppliers whose name starts with H and ends in %. For example, it would return a value such as 'Hello%'.
You can also use the Escape character with the _ character. For example,
SELECT * FROM suppliers
WHERE supplier_name LIKE 'H%!_' escape '!';
WHERE supplier_name LIKE 'H%!_' escape '!';
This example returns all suppliers whose name starts with H and ends in _. For example, it would return a value such as 'Hello_'.
Frequently Asked Questions
Question: How do you incorporate the Oracle upper function with the LIKE condition? I'm trying to query against a free text field for all records containing the word "test". The problem is that it can be entered in the following ways: TEST, Test, or test.
Answer: To answer this question, let's take a look at an example.
Let's say that we have a suppliers table with a field called supplier_name that contains the values TEST, Test, or test.
If we wanted to find all records containing the word "test", regardless of whether it was stored as TEST, Test, or test, we could run either of the following SQL statements:
select * from suppliers
where upper(supplier_name) like ('TEST%');
where upper(supplier_name) like ('TEST%');
or
select * from suppliers
where upper(supplier_name) like upper('test%')
where upper(supplier_name) like upper('test%')
These SQL statements use a combination of the upper function and the LIKE condition to return all of the records where the supplier_name field contains the word "test", regardless of whether it was stored as TEST, Test, or test.
Practice Exercise #1:
Based on the employees table populated with the following data, find all records whose employee_name ends with the letter "h".
| CREATE TABLE employees | |||
| ( | employee_number | number(10) | not null, |
| | employee_name | varchar2(50) | not null, |
| | salary | number(6), | |
| | CONSTRAINT employees_pk PRIMARY KEY (employee_number) | ||
| ); | |||
INSERT INTO employees (employee_number, employee_name, salary)
VALUES (1001, 'John Smith', 62000);
VALUES (1001, 'John Smith', 62000);
INSERT INTO employees (employee_number, employee_name, salary)
VALUES (1002, 'Jane Anderson', 57500);
VALUES (1002, 'Jane Anderson', 57500);
INSERT INTO employees (employee_number, employee_name, salary)
VALUES (1003, 'Brad Everest', 71000);
VALUES (1003, 'Brad Everest', 71000);
INSERT INTO employees (employee_number, employee_name, salary)
VALUES (1004, 'Jack Horvath', 42000);
VALUES (1004, 'Jack Horvath', 42000);
Solution:
The following SQL statement would return the records whose employee_name ends with the letter "h".
SELECT *
FROM employees
WHERE employee_name LIKE '%h';
FROM employees
WHERE employee_name LIKE '%h';
It would return the following result set:
| EMPLOYEE_NUMBER | EMPLOYEE_NAME | SALARY |
| 1001 | John Smith | 62000 |
| 1004 | Jack Horvath | 42000 |
Practice Exercise #2:
Based on the employees table populated with the following data, find all records whose employee_name contains the letter "s".
| CREATE TABLE employees | |||
| ( | employee_number | number(10) | not null, |
| | employee_name | varchar2(50) | not null, |
| | salary | number(6), | |
| | CONSTRAINT employees_pk PRIMARY KEY (employee_number) | ||
| ); | |||
INSERT INTO employees (employee_number, employee_name, salary)
VALUES (1001, 'John Smith', 62000);
VALUES (1001, 'John Smith', 62000);
INSERT INTO employees (employee_number, employee_name, salary)
VALUES (1002, 'Jane Anderson', 57500);
VALUES (1002, 'Jane Anderson', 57500);
INSERT INTO employees (employee_number, employee_name, salary)
VALUES (1003, 'Brad Everest', 71000);
VALUES (1003, 'Brad Everest', 71000);
INSERT INTO employees (employee_number, employee_name, salary)
VALUES (1004, 'Jack Horvath', 42000);
VALUES (1004, 'Jack Horvath', 42000);
Solution:
The following SQL statement would return the records whose employee_name contains the letter "s".
SELECT *
FROM employees
WHERE employee_name LIKE '%s%';
FROM employees
WHERE employee_name LIKE '%s%';
It would return the following result set:
| EMPLOYEE_NUMBER | EMPLOYEE_NAME | SALARY |
| 1002 | Jane Anderson | 57500 |
| 1003 | Brad Everest | 71000 |
Practice Exercise #3:
Based on the suppliers table populated with the following data, find all records whose supplier_id is 4 digits and starts with "500".
| CREATE TABLE suppliers | |||
| ( | supplier_id | varchar2(10) | not null, |
| | supplier_name | varchar2(50) | not null, |
| | city | varchar2(50), | |
| | CONSTRAINT suppliers_pk PRIMARY KEY (supplier_id) | ||
| ); | |||
INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES ('5008', 'Microsoft', 'New York');
VALUES ('5008', 'Microsoft', 'New York');
INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES ('5009', 'IBM', 'Chicago');
VALUES ('5009', 'IBM', 'Chicago');
INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES ('5010', 'Red Hat', 'Detroit');
VALUES ('5010', 'Red Hat', 'Detroit');
INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES ('5011', 'NVIDIA', 'New York');
VALUES ('5011', 'NVIDIA', 'New York');
Solution:
The following SQL statement would return the records whose supplier_id is 4 digits and starts with "500".
select *
FROM suppliers
WHERE supplier_id LIKE '500_';
FROM suppliers
WHERE supplier_id LIKE '500_';
It would return the following result set:
| SUPPLIER_ID | SUPPLIER_NAME | CITY |
| 5008 | Microsoft | New York |
| 5009 | IBM | Chicago |
SQL: "IN" Function
The IN function helps reduce the need to use multiple OR conditions.
The syntax for the IN function is:
SELECT columns
FROM tables
WHERE column1 in (value1, value2, .... value_n);
FROM tables
WHERE column1 in (value1, value2, .... value_n);
This SQL statement will return the records where column1 is value1, value2..., or value_n. The IN function can be used in any valid SQL statement - select, insert, update, or delete.
Example #1
The following is an SQL statement that uses the IN function:
SELECT *
FROM suppliers
WHERE supplier_name in ( 'IBM', 'Hewlett Packard', 'Microsoft');
FROM suppliers
WHERE supplier_name in ( 'IBM', 'Hewlett Packard', 'Microsoft');
This would return all rows where the supplier_name is either IBM, Hewlett Packard, or Microsoft. Because the * is used in the select, all fields from the suppliers table would appear in the result set.
It is equivalent to the following statement:
SELECT *
FROM suppliers
WHERE supplier_name = 'IBM'
OR supplier_name = 'Hewlett Packard'
OR supplier_name = 'Microsoft';
FROM suppliers
WHERE supplier_name = 'IBM'
OR supplier_name = 'Hewlett Packard'
OR supplier_name = 'Microsoft';
As you can see, using the IN function makes the statement easier to read and more efficient.
Example #2
You can also use the IN function with numeric values.
SELECT *
FROM orders
WHERE order_id in (10000, 10001, 10003, 10005);
FROM orders
WHERE order_id in (10000, 10001, 10003, 10005);
This SQL statement would return all orders where the order_id is either 10000, 10001, 10003, or 10005.
It is equivalent to the following statement:
SELECT *
FROM orders
WHERE order_id = 10000
OR order_id = 10001
OR order_id = 10003
OR order_id = 10005;
FROM orders
WHERE order_id = 10000
OR order_id = 10001
OR order_id = 10003
OR order_id = 10005;
Example #3 using "NOT IN"
The IN function can also be combined with the NOT operator.
For example,
SELECT *
FROM suppliers
WHERE supplier_name not in ( 'IBM', 'Hewlett Packard', 'Microsoft');
FROM suppliers
WHERE supplier_name not in ( 'IBM', 'Hewlett Packard', 'Microsoft');
This would return all rows where the supplier_name is neither IBM, Hewlett Packard, or Microsoft. Sometimes, it is more efficient to list the values that you do not want, as opposed to the values that you do want.
SQL: BETWEEN Condition
The BETWEEN condition allows you to retrieve values within a range.
The syntax for the BETWEEN condition is:
SELECT columns
FROM tables
WHERE column1 between value1 and value2;
FROM tables
WHERE column1 between value1 and value2;
This SQL statement will return the records where column1 is within the range of value1 and value2 (inclusive). The BETWEEN function can be used in any valid SQL statement - select, insert, update, or delete.
Example #1 - Numbers
The following is an SQL statement that uses the BETWEEN function:
SELECT *
FROM suppliers
WHERE supplier_id between 5000 AND 5010;
FROM suppliers
WHERE supplier_id between 5000 AND 5010;
This would return all rows where the supplier_id is between 5000 and 5010, inclusive. It is equivalent to the following SQL statement:
SELECT *
FROM suppliers
WHERE supplier_id >= 5000
AND supplier_id <= 5010;
FROM suppliers
WHERE supplier_id >= 5000
AND supplier_id <= 5010;
Example #2 - Dates
You can also use the BETWEEN function with dates.
SELECT *
FROM orders
WHERE order_date between to_date ('2003/01/01', 'yyyy/mm/dd')
AND to_date ('2003/12/31', 'yyyy/mm/dd');
FROM orders
WHERE order_date between to_date ('2003/01/01', 'yyyy/mm/dd')
AND to_date ('2003/12/31', 'yyyy/mm/dd');
This SQL statement would return all orders where the order_date is between Jan 1, 2003 and Dec 31, 2003 (inclusive).
It would be equivalent to the following SQL statement:
SELECT *
FROM orders
WHERE order_date >= to_date('2003/01/01', 'yyyy/mm/dd')
AND order_date <= to_date('2003/12/31','yyyy/mm/dd');
FROM orders
WHERE order_date >= to_date('2003/01/01', 'yyyy/mm/dd')
AND order_date <= to_date('2003/12/31','yyyy/mm/dd');
Example #3 - NOT BETWEEN
The BETWEEN function can also be combined with the NOT operator.
For example,
SELECT *
FROM suppliers
WHERE supplier_id not between 5000 and 5500;
FROM suppliers
WHERE supplier_id not between 5000 and 5500;
This would be equivalent to the following SQL:
SELECT *
FROM suppliers
WHERE supplier_id < 5000
OR supplier_id > 5500;
FROM suppliers
WHERE supplier_id < 5000
OR supplier_id > 5500;
In this example, the result set would exclude all supplier_id values between the range of 5000 and 5500 (inclusive
SQL: EXISTS Condition
The EXISTS condition is considered "to be met" if the subquery returns at least one row.
The syntax for the EXISTS condition is:
SELECT columns
FROM tables
WHERE EXISTS ( subquery );
FROM tables
WHERE EXISTS ( subquery );
The EXISTS condition can be used in any valid SQL statement - select, insert, update, or delete.
Example #1
Let's take a look at a simple example. The following is an SQL statement that uses the EXISTS condition:
SELECT *
FROM suppliers
WHERE EXISTS
(select *
from orders
where suppliers.supplier_id = orders.supplier_id);
FROM suppliers
WHERE EXISTS
(select *
from orders
where suppliers.supplier_id = orders.supplier_id);
This select statement will return all records from the suppliers table where there is at least one record in the orders table with the same supplier_id.
Example #2 - NOT EXISTS
The EXISTS condition can also be combined with the NOT operator.
For example,
SELECT *
FROM suppliers
WHERE not exists (select * from orders Where suppliers.supplier_id = orders.supplier_id);
FROM suppliers
WHERE not exists (select * from orders Where suppliers.supplier_id = orders.supplier_id);
This will return all records from the suppliers table where there are no records in the orders table for the given supplier_id.
Example #3 - DELETE Statement
The following is an example of a delete statement that utilizes the EXISTS condition:
DELETE FROM suppliers
WHERE EXISTS
(select *
from orders
where suppliers.supplier_id = orders.supplier_id);
WHERE EXISTS
(select *
from orders
where suppliers.supplier_id = orders.supplier_id);
Example #4 - UPDATE Statement
The following is an example of an update statement that utilizes the EXISTS condition:
| UPDATE suppliers | |
| SET supplier_name = | ( SELECT customers.name FROM customers WHERE customers.customer_id = suppliers.supplier_id) |
| WHERE EXISTS ( SELECT customers.name FROM customers WHERE customers.customer_id = suppliers.supplier_id); | |
Example #5 - INSERT Statement
The following is an example of an insert statement that utilizes the EXISTS condition:
INSERT INTO suppliers
(supplier_id, supplier_name)
SELECT account_no, name
FROM suppliers
WHERE exists (select * from orders Where suppliers.supplier_id = orders.supplier_id);
(supplier_id, supplier_name)
SELECT account_no, name
FROM suppliers
WHERE exists (select * from orders Where suppliers.supplier_id = orders.supplier_id);
No comments:
Post a Comment