Oracle Certification Notes: Part XIII

This is a continuation of my notes as I study for the Oracle Certified Associate crediential:

Data Manipulation Language (DML)

5 DML commands: SELECT, INSERT, UPDATE, DELETE, MERGE

May INSERT into 1 row using VALUES; May INSERT into multiple rows using SELECT

May INSERT into multiple tables at a time. As an example: INSERT ALL WHEN column1=1 INTO table1(column1) VALUES (retrieved.column1) WHEN column2=2 INTO table2(column1) VALUES (retrieved.column2) SELECT column1, column2 FROM table3;

TRUNCATE is technically considered a command of the Data Definition Language (DDL)

MERGE can be thought of as a way to perform an INSERT, UPDATE, or DELETE depending on the data circumstance. Instead of coding for all 3 scenarios, a MERGE does it all at once.

CONTROL Transactions

COMMIT, ROLLBACK, SAVEPOINT are control transactions

Relational database standards for transactions - ACID: atomicity, consistency, isolation, durability

Example: SAVEPOINT savepoint

Example: >ROLLBACK [TO SAVEPOINT savepoint]

Terminating transaction statements include COMMIT, ROLLBACK, and TRUNCATE. SAVEPOINT and ROLLBACK TO SAVEPOINT does not terminate a transaction.

Database Objects

An object (e.g., table, stored procedure) is owned by a database user; a set of objects owned by a database user is referred to as its schema.

Object names can be no more than 30 characters consisting of letters, numbers, underscore (_), dollar sign ($) and hash symbol (#)

Objects are defined by their namespace and schema_owner.object_name

Namespace is a group of object types. Stored Procedures, Tables, Views, Synonyms, and Sequences are considered to be in a namespace. Indexes are stored in a separate namespace.

The amount of limited space assigned to a table is known as an extent.

Posted via Woodland Blog (http://dramse01.blogspot.com)