Enhanced Guide to Enhanced Guide to Oracle 10gOracle 10g

Chapter 3:Using SQL Queries to Insert,Update, Delete, and View Data

Manipulating DataManipulating DataManipulating DataManipulating Data

ObjectivesObjectivesObjectivesObjectives

After completing this lesson, you After completing this lesson, you should be able to do the should be able to do the followingfollowing:: Describe each DML statementDescribe each DML statement Insert rows into a tableInsert rows into a table Update rows in a tableUpdate rows in a table Delete rows from a tableDelete rows from a table Control transactionsControl transactions

After completing this lesson, you After completing this lesson, you should be able to do the should be able to do the followingfollowing:: Describe each DML statementDescribe each DML statement Insert rows into a tableInsert rows into a table Update rows in a tableUpdate rows in a table Delete rows from a tableDelete rows from a table Control transactionsControl transactions

SQL ScriptsSQL Scripts

Script: text file that contains a Script: text file that contains a sequence of SQL commandssequence of SQL commands

Usually have .sql extensionUsually have .sql extension To run from SQL*Plus:To run from SQL*Plus:

Start full file path Start full file path SQL> START path_to_script_file;SQL> START path_to_script_file;

@ full file path @ full file path ((SQL>SQL> @ @ path_to_script_file;)path_to_script_file;)

Extension can be omitted if it is .sqlExtension can be omitted if it is .sql Path cannot contain any blank spacesPath cannot contain any blank spaces

Data Manipulation Data Manipulation LanguageLanguage

Data Manipulation Data Manipulation LanguageLanguage

A DML statement is executed A DML statement is executed when youwhen you:: Add new rows to a tableAdd new rows to a table Modify existing rows in a tableModify existing rows in a table Remove existing rows from a Remove existing rows from a

tabletable A A transactiontransaction consists of a consists of a

collection of DML statements collection of DML statements that form a logical unit of that form a logical unit of workwork..

A DML statement is executed A DML statement is executed when youwhen you:: Add new rows to a tableAdd new rows to a table Modify existing rows in a tableModify existing rows in a table Remove existing rows from a Remove existing rows from a

tabletable A A transactiontransaction consists of a consists of a

collection of DML statements collection of DML statements that form a logical unit of that form a logical unit of workwork..

TransactionsTransactions TransactionTransaction: series of action queries that : series of action queries that

represent a logical unit of workrepresent a logical unit of work consisting of one or more SQL DML commands consisting of one or more SQL DML commands

INSERT, UPDATE, DELETEINSERT, UPDATE, DELETE All transaction commands must succeed or none can All transaction commands must succeed or none can

succeedsucceed User can commit (save) changesUser can commit (save) changes User can roll back (discard) changesUser can roll back (discard) changes

Pending transactionPending transaction: a transaction waiting to : a transaction waiting to be committed or rolled backbe committed or rolled back Oracle DBMS locks records associated with pending Oracle DBMS locks records associated with pending

transactionstransactions Other users cannot view or modify locked recordsOther users cannot view or modify locked records

Adding a New Row to a Adding a New Row to a TableTable

Adding a New Row to a Adding a New Row to a TableTable

DEPT DEPT DEPTNO DNAME LOC ------ ---------- -------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON

New rowNew row

50 DEVELOPMENT DETROIT

DEPT DEPT DEPTNO DNAME LOC ------ ---------- -------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON

“…“…insert a new row insert a new row into DEPT table…”into DEPT table…”

50 DEVELOPMENT DETROIT

The INSERT StatementThe INSERT StatementThe INSERT StatementThe INSERT Statement

Add new rows to a table by using Add new rows to a table by using the INSERT statementthe INSERT statement..

Only one row is inserted at a time Only one row is inserted at a time with this syntaxwith this syntax..

Add new rows to a table by using Add new rows to a table by using the INSERT statementthe INSERT statement..

Only one row is inserted at a time Only one row is inserted at a time with this syntaxwith this syntax..

INSERT INTO table [(column [, column...])]VALUES (value [, value...]);

INSERT INTO table [(column [, column...])]VALUES (value [, value...]);

Inserting New RowsInserting New RowsInserting New RowsInserting New Rows

Insert a new row containing values Insert a new row containing values for each columnfor each column..

List values in the default order of List values in the default order of the columns in the tablethe columns in the table. .

Optionally list the columns in the Optionally list the columns in the INSERT clauseINSERT clause..

Enclose character and date values Enclose character and date values within single quotation markswithin single quotation marks..

Insert a new row containing values Insert a new row containing values for each columnfor each column..

List values in the default order of List values in the default order of the columns in the tablethe columns in the table. .

Optionally list the columns in the Optionally list the columns in the INSERT clauseINSERT clause..

Enclose character and date values Enclose character and date values within single quotation markswithin single quotation marks..

SQL> INSERT INTO dept (deptno, dname, loc) 2 VALUES (50, 'DEVELOPMENT', 'DETROIT');1 row created1 row created..

Inserting Rows with Null Inserting Rows with Null ValuesValues

Inserting Rows with Null Inserting Rows with Null ValuesValues

Implicit methodImplicit method: : Omit the column Omit the column from the column listfrom the column list..

Implicit methodImplicit method: : Omit the column Omit the column from the column listfrom the column list..

SQL> INSERT INTO dept (deptno, dname ) 2 VALUES (60, 'MIS');1 row created1 row created..

• Explicit method: Specify the NULL keyword.

• Explicit method: Specify the NULL keyword.

SQL> INSERT INTO dept 2 VALUES (70, 'FINANCE', NULL);1 row created1 row created..

Inserting Special ValuesInserting Special ValuesInserting Special ValuesInserting Special Values

The SYSDATE function records The SYSDATE function records the current date and timethe current date and time..

The SYSDATE function records The SYSDATE function records the current date and timethe current date and time..

SQL> INSERT INTO emp (empno, ename, job, 2 mgr, hiredate, sal, comm, 3 deptno) 4 VALUES (7196, 'GREEN', 'SALESMAN', 5 7782, SYSDATE, 2000, NULL, 6 10);1 row created1 row created..

Format MasksFormat Masks

All data is stored in the database in a All data is stored in the database in a standard binary formatstandard binary format

Format masks are alphanumeric text Format masks are alphanumeric text strings that specify the format of strings that specify the format of input and output datainput and output data Table 3-1: Number format masksTable 3-1: Number format masks Table 3-2: Date format masksTable 3-2: Date format masks

Date values must be Date values must be converted from characters to converted from characters to dates using the TO_DATE dates using the TO_DATE function and a format mask function and a format mask

Example:Example:

Inserting Date ValuesInserting Date Values

Must be enclosed in single quotesMust be enclosed in single quotes Is case-sensitiveIs case-sensitive To insert a string with a single To insert a string with a single

quote, type the single quote twicequote, type the single quote twice Example:Example:

'Mike''s Motorcycle Shop''Mike''s Motorcycle Shop'

Inserting Text DataInserting Text Data

Year To Month Interval:Year To Month Interval:

TO_YMINTERVAL(‘years-months’)TO_YMINTERVAL(‘years-months’)

e.g. TO_YMINTERVAL(‘3-2’)e.g. TO_YMINTERVAL(‘3-2’)

Day To Second Interval:Day To Second Interval:

TO_DSINTERVAL(‘days HH:MI:SS.99’)TO_DSINTERVAL(‘days HH:MI:SS.99’)

e.g. TO_DSINTERVAL(‘-0 01:15:00’)e.g. TO_DSINTERVAL(‘-0 01:15:00’)

Inserting Interval Values Inserting Interval Values

Inserting LOB Column Inserting LOB Column LocatorsLocators

Oracle stores LOB data in separate Oracle stores LOB data in separate physical location from other types of physical location from other types of datadata

LOB locator LOB locator Structure containing information that Structure containing information that

identifies LOB data type identifies LOB data type Points to alternate memory locationPoints to alternate memory location

Create blob locatorCreate blob locator EMPTY_BLOB()EMPTY_BLOB()

Inserting Specific Date Inserting Specific Date ValuesValues

Inserting Specific Date Inserting Specific Date ValuesValues

Add a new employeeAdd a new employee.. Add a new employeeAdd a new employee..SQL> INSERT INTO emp 2 VALUES (2296,'AROMANO','SALESMAN',7782, 3 TO_DATE('FEB 3, 1997', 'MON DD, YYYY'), 4 1300, NULL, 10);1 row created1 row created..

• Verify your addition.• Verify your addition.EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO----- ------- -------- ---- --------- ---- ---- ------ 2296 AROMANO SALESMAN 7782 03-FEB-97 1300 10

Changing Data in a TableChanging Data in a TableChanging Data in a TableChanging Data in a TableEMPEMP

“…“…update a row update a row in EMP table…”in EMP table…”

EMPEMP

EMPNO ENAME JOB ... DEPTNO

7839 KING PRESIDENT 10 7698 BLAKE MANAGER 30 7782 CLARK MANAGER 10 7566 JONES MANAGER 20 ...

20

EMPNO ENAME JOB ... DEPTNO

7839 KING PRESIDENT 10 7698 BLAKE MANAGER 30 7782 CLARK MANAGER 10 7566 JONES MANAGER 20 ...

The UPDATE StatementThe UPDATE StatementThe UPDATE StatementThe UPDATE Statement

Modify existing rows with the Modify existing rows with the UPDATE statementUPDATE statement..

Update more than one row at a Update more than one row at a time, if requiredtime, if required..

Modify existing rows with the Modify existing rows with the UPDATE statementUPDATE statement..

Update more than one row at a Update more than one row at a time, if requiredtime, if required..

UPDATE tableSET column = value [, column = value, ...][WHERE condition];

UPDATE tableSET column = value [, column = value, ...][WHERE condition];

Format:Format:WHERE WHERE fieldname operator expressionfieldname operator expression

OperatorsOperators Equal (=)Equal (=) Greater than, Less than (>, <)Greater than, Less than (>, <) Greater than or Equal to (>=)Greater than or Equal to (>=) Less than or Equal to (<=)Less than or Equal to (<=) Not equal (< >, !=, ^=)Not equal (< >, !=, ^=) LIKELIKE BETWEENBETWEEN ININ NOT INNOT IN

Search ConditionsSearch Conditions

WHERE s_name = ‘Sarah’WHERE s_name = ‘Sarah’

WHERE s_age > 18WHERE s_age > 18

WHERE s_class <> ‘SR’WHERE s_class <> ‘SR’

Text in single quotes is case sensitiveText in single quotes is case sensitive

Search Condition Search Condition ExamplesExamples

Updating Rows in a Updating Rows in a TableTable

Updating Rows in a Updating Rows in a TableTable Specific row or rows are modified Specific row or rows are modified

when you specify the WHERE when you specify the WHERE clauseclause..

All rows in the table are modified if All rows in the table are modified if you omit the WHERE clauseyou omit the WHERE clause..

Specific row or rows are modified Specific row or rows are modified when you specify the WHERE when you specify the WHERE clauseclause..

All rows in the table are modified if All rows in the table are modified if you omit the WHERE clauseyou omit the WHERE clause..

SQL> UPDATE emp 2 SET deptno = 20 3 WHERE empno = 7782;1 row updated1 row updated..

SQL> UPDATE employee 2 SET deptno = 20;14 rows updated14 rows updated..

SQL> UPDATE employee 2 SET deptno = 20;14 rows updated14 rows updated..

UPDATE emp *ERROR at line 1:ORA-02291: integrity constraint (USR.EMP_DEPTNO_FK) violated - parent key not found

UPDATE emp *ERROR at line 1:ORA-02291: integrity constraint (USR.EMP_DEPTNO_FK) violated - parent key not found

SQL> UPDATE emp 2 SET deptno = 55 3 WHERE deptno = 10;

SQL> UPDATE emp 2 SET deptno = 55 3 WHERE deptno = 10;

Updating RowsUpdating Rows: : Integrity Constraint Integrity Constraint

ErrorError

Updating RowsUpdating Rows: : Integrity Constraint Integrity Constraint

ErrorError

Department n

umber 55 does

Department n

umber 55 does

not exis

t

not exis

t

Department n

umber 55 does

Department n

umber 55 does

not exis

t

not exis

t

“…“…delete a row delete a row from DEPT table…”from DEPT table…”

Removing a Row from a Removing a Row from a Table Table

Removing a Row from a Removing a Row from a Table Table DEPT DEPT

DEPTNO DNAME LOC ------ ---------- -------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 50 DEVELOPMENT DETROIT 60 MIS ...

DEPT DEPT DEPTNO DNAME LOC ------ ---------- -------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 60 MIS ...

The DELETE StatementThe DELETE StatementThe DELETE StatementThe DELETE Statement

You can remove existing rows from a You can remove existing rows from a table by using the DELETE table by using the DELETE statementstatement..

You can remove existing rows from a You can remove existing rows from a table by using the DELETE table by using the DELETE statementstatement..DELETE [FROM] table[WHERE condition];

DELETE [FROM] table[WHERE condition];

Specific rows are deleted when you Specific rows are deleted when you specify the WHERE clausespecify the WHERE clause..

All rows in the table are deleted if All rows in the table are deleted if you omit the WHERE clauseyou omit the WHERE clause..

Specific rows are deleted when you Specific rows are deleted when you specify the WHERE clausespecify the WHERE clause..

All rows in the table are deleted if All rows in the table are deleted if you omit the WHERE clauseyou omit the WHERE clause..

Deleting Rows from a Deleting Rows from a TableTable

Deleting Rows from a Deleting Rows from a TableTable

SQL> DELETE FROM department 2 WHERE dname = 'DEVELOPMENT'; 1 row deleted1 row deleted..

SQL> DELETE FROM department 2 WHERE dname = 'DEVELOPMENT'; 1 row deleted1 row deleted..

SQL> DELETE FROM department;4 rows deleted4 rows deleted..

SQL> DELETE FROM department;4 rows deleted4 rows deleted..

Deleting RowsDeleting Rows: : Integrity Constraint Integrity Constraint

ErrorError

Deleting RowsDeleting Rows: : Integrity Constraint Integrity Constraint

ErrorErrorSQL> DELETE FROM dept 2 WHERE deptno = 10;

SQL> DELETE FROM dept 2 WHERE deptno = 10;

DELETE FROM dept *ERROR at line 1:ORA-02292: integrity constraint (USR.EMP_DEPTNO_FK) violated - child record found

DELETE FROM dept *ERROR at line 1:ORA-02292: integrity constraint (USR.EMP_DEPTNO_FK) violated - child record found

You cannot delete a row

You cannot delete a row

that contains a primary

that contains a primary

key key

that is used as a foreign

that is used as a foreign

key key

in another table

in another table..

You cannot delete a row

You cannot delete a row

that contains a primary

that contains a primary

key key

that is used as a foreign

that is used as a foreign

key key

in another table

in another table..

Database TransactionsDatabase TransactionsDatabase TransactionsDatabase Transactions

Begin when the first executable Begin when the first executable SQL statement is executedSQL statement is executed

End with one of the following End with one of the following eventsevents:: COMMIT or ROLLBACK is issuedCOMMIT or ROLLBACK is issued DDL or DCL statement executes DDL or DCL statement executes

((automatic commitautomatic commit)) User exitsUser exits System crashesSystem crashes

Begin when the first executable Begin when the first executable SQL statement is executedSQL statement is executed

End with one of the following End with one of the following eventsevents:: COMMIT or ROLLBACK is issuedCOMMIT or ROLLBACK is issued DDL or DCL statement executes DDL or DCL statement executes

((automatic commitautomatic commit)) User exitsUser exits System crashesSystem crashes

Advantages of COMMIT Advantages of COMMIT and ROLLBACK and ROLLBACK

StatementsStatements

Advantages of COMMIT Advantages of COMMIT and ROLLBACK and ROLLBACK

StatementsStatements Ensure data consistencyEnsure data consistency Preview data changes before Preview data changes before

making changes permanentmaking changes permanent Group logically related operationsGroup logically related operations

Ensure data consistencyEnsure data consistency Preview data changes before Preview data changes before

making changes permanentmaking changes permanent Group logically related operationsGroup logically related operations

DELETEDELETE

Controlling TransactionsControlling TransactionsControlling TransactionsControlling Transactions TransactiTransacti

onon

TransactiTransactionon

Savepoint ASavepoint A

ROLLBACK to Savepoint BROLLBACK to Savepoint B

DELETEDELETE

Savepoint BSavepoint BCOMMITCOMMIT

INSERTINSERTUPDATEUPDATE

ROLLBACK to Savepoint AROLLBACK to Savepoint A

INSERTINSERTUPDATEUPDATEINSERTINSERT

ROLLBACKROLLBACK

INSERTINSERT

An automatic commit occurs under An automatic commit occurs under the following circumstancesthe following circumstances:: DDL statement is issuedDDL statement is issued DCL statement is issuedDCL statement is issued Normal exit from SQLNormal exit from SQL**Plus, without Plus, without

explicitly issuing COMMIT or explicitly issuing COMMIT or ROLLBACKROLLBACK

An automatic rollback occurs under An automatic rollback occurs under an abnormal termination of an abnormal termination of SQLSQL**Plus or a system failurePlus or a system failure..

An automatic commit occurs under An automatic commit occurs under the following circumstancesthe following circumstances:: DDL statement is issuedDDL statement is issued DCL statement is issuedDCL statement is issued Normal exit from SQLNormal exit from SQL**Plus, without Plus, without

explicitly issuing COMMIT or explicitly issuing COMMIT or ROLLBACKROLLBACK

An automatic rollback occurs under An automatic rollback occurs under an abnormal termination of an abnormal termination of SQLSQL**Plus or a system failurePlus or a system failure..

Implicit Transaction Implicit Transaction ProcessingProcessing

Implicit Transaction Implicit Transaction ProcessingProcessing

State of the Data Before State of the Data Before COMMIT or ROLLBACKCOMMIT or ROLLBACK

State of the Data Before State of the Data Before COMMIT or ROLLBACKCOMMIT or ROLLBACK

The previous state of the data can be The previous state of the data can be recoveredrecovered..

The current user can review the results The current user can review the results of the DML operations by using the of the DML operations by using the SELECT statementSELECT statement..

Other users Other users cannotcannot view the results of view the results of the DML statements by the current the DML statements by the current useruser..

The affected rows are The affected rows are lockedlocked; other ; other users cannot change the data within the users cannot change the data within the affected rowsaffected rows..

The previous state of the data can be The previous state of the data can be recoveredrecovered..

The current user can review the results The current user can review the results of the DML operations by using the of the DML operations by using the SELECT statementSELECT statement..

Other users Other users cannotcannot view the results of view the results of the DML statements by the current the DML statements by the current useruser..

The affected rows are The affected rows are lockedlocked; other ; other users cannot change the data within the users cannot change the data within the affected rowsaffected rows..

State of the Data After State of the Data After COMMITCOMMIT

State of the Data After State of the Data After COMMITCOMMIT

Data changes are made permanent in Data changes are made permanent in the databasethe database..

The previous state of the data is The previous state of the data is permanently lostpermanently lost..

All users can view the resultsAll users can view the results.. Locks on the affected rows are Locks on the affected rows are

released; those rows are available for released; those rows are available for other users to manipulateother users to manipulate..

All savepoints are erasedAll savepoints are erased..

Data changes are made permanent in Data changes are made permanent in the databasethe database..

The previous state of the data is The previous state of the data is permanently lostpermanently lost..

All users can view the resultsAll users can view the results.. Locks on the affected rows are Locks on the affected rows are

released; those rows are available for released; those rows are available for other users to manipulateother users to manipulate..

All savepoints are erasedAll savepoints are erased..

Committing DataCommitting DataCommitting DataCommitting Data

SQL> UPDATE emp 2 SET deptno = 10 3 WHERE empno = 7782;1 row updated1 row updated..

SQL> UPDATE emp 2 SET deptno = 10 3 WHERE empno = 7782;1 row updated1 row updated..

Make the changesMake the changes.. Make the changesMake the changes..

• Commit the changes.• Commit the changes.SQL> COMMIT;Commit completeCommit complete..

State of the Data After State of the Data After ROLLBACKROLLBACK

State of the Data After State of the Data After ROLLBACKROLLBACK Discard all pending changes by Discard all pending changes by

using the ROLLBACK statementusing the ROLLBACK statement.. Data changes are undoneData changes are undone.. Previous state of the data is Previous state of the data is

restoredrestored.. Locks on the affected rows are Locks on the affected rows are

releasedreleased..

Discard all pending changes by Discard all pending changes by using the ROLLBACK statementusing the ROLLBACK statement.. Data changes are undoneData changes are undone.. Previous state of the data is Previous state of the data is

restoredrestored.. Locks on the affected rows are Locks on the affected rows are

releasedreleased..SQL> DELETE FROM employee;14 rows deleted14 rows deleted..SQL> ROLLBACK;Rollback completeRollback complete..

Used to mark Used to mark individual individual sections of a sections of a transactiontransaction

You can roll You can roll back a back a transaction to transaction to a savepointa savepoint

SavepointsSavepoints

Rolling Back Changes Rolling Back Changes to a Markerto a Marker

Rolling Back Changes Rolling Back Changes to a Markerto a Marker

Create a marker in a current Create a marker in a current transaction by using the SAVEPOINT transaction by using the SAVEPOINT statementstatement..

Roll back to that marker by using the Roll back to that marker by using the ROLLBACK TO SAVEPOINT statementROLLBACK TO SAVEPOINT statement..

Create a marker in a current Create a marker in a current transaction by using the SAVEPOINT transaction by using the SAVEPOINT statementstatement..

Roll back to that marker by using the Roll back to that marker by using the ROLLBACK TO SAVEPOINT statementROLLBACK TO SAVEPOINT statement..SQL> UPDATE...

SQL> SAVEPOINT update_done;Savepoint createdSavepoint created..SQL> INSERT...SQL> ROLLBACK TO update_done;Rollback completeRollback complete..

Truncating TablesTruncating TablesTruncating TablesTruncating Tables

Removes all table data without Removes all table data without saving any rollback informationsaving any rollback information Advantage: fast way to delete table Advantage: fast way to delete table

datadata Disadvantage: can’t be undoneDisadvantage: can’t be undone

Syntax:Syntax:TRUNCATE TABLE TRUNCATE TABLE tablenametablename;;

Removes all table data without Removes all table data without saving any rollback informationsaving any rollback information Advantage: fast way to delete table Advantage: fast way to delete table

datadata Disadvantage: can’t be undoneDisadvantage: can’t be undone

Syntax:Syntax:TRUNCATE TABLE TRUNCATE TABLE tablenametablename;;

SummarySummarySummarySummary

Description

Adds a new row to the table

Modifies existing rows in the table

Removes existing rows from the table

Makes all pending changes permanent

Allows a rollback to the savepoint marker

Discards all pending data changes

Statement

INSERT

UPDATE

DELETE

COMMIT

SAVEPOINT

ROLLBACK

Sequential list of numbers that Sequential list of numbers that is automatically generated by is automatically generated by the databasethe database

Used to generate values for Used to generate values for surrogate keyssurrogate keys

SequencesSequences

Creating New SequencesCreating New Sequences

CREATE SEQUENCE commandCREATE SEQUENCE command DDL commandDDL command No need to issue COMMIT commandNo need to issue COMMIT command

General Syntax Used to General Syntax Used to Create a New SequenceCreate a New Sequence

Syntax:Syntax:CREATE SEQUENCE CREATE SEQUENCE sequence_namesequence_name

[optional parameters];[optional parameters];

Example:Example:CREATE SEQUENCE f_id_sequenceCREATE SEQUENCE f_id_sequence

START WITH 200;START WITH 200;

Creating SequencesCreating Sequences

Viewing Sequence Viewing Sequence InformationInformation

Query the SEQUENCE Data Query the SEQUENCE Data Dictionary View:Dictionary View:

PseudocolumnsPseudocolumns

Acts like a column in a database Acts like a column in a database queryquery

Actually a command that returns a Actually a command that returns a specific valuesspecific values

Used to retrieve:Used to retrieve: Current system date Current system date Name of the current database userName of the current database user Next value in a sequenceNext value in a sequence

Pseudocolumn ExamplesPseudocolumn Examples

PseudocolumPseudocolumnn

NameName

OutputOutput

CURRVALCURRVALMost recently retrieved Most recently retrieved sequence valuesequence value

NEXTVALNEXTVALNext value in a sequenceNext value in a sequence

SYSDATESYSDATECurrent system date Current system date from database serverfrom database server

USERUSERUsername of current Username of current useruser

Retrieving the current system dateRetrieving the current system date::SELECT SYSDATESELECT SYSDATEFROM DUAL;FROM DUAL; Retrieving the name of the current Retrieving the name of the current

user:user:SELECT USERSELECT USERFROM DUAL;FROM DUAL;

DUAL is a system table that is used DUAL is a system table that is used with pseudocolumnswith pseudocolumns

Using PseudocolumnsUsing Pseudocolumns

Accessing the next value in a Accessing the next value in a sequence:sequence:

sequence_namesequence_name.NEXTVAL.NEXTVAL

Inserting a new record using a Inserting a new record using a sequence:sequence:

INSERT INTO my_faculty VALUESINSERT INTO my_faculty VALUES

(f_id_sequence.nextval, ‘Professor Jones’);(f_id_sequence.nextval, ‘Professor Jones’);

Using PseudocolumnsUsing PseudocolumnsWith SequencesWith Sequences

Permissions that you can grant to other Permissions that you can grant to other users to allow them to access or modify users to allow them to access or modify your database objectsyour database objects

Granting object privileges:Granting object privileges:GRANT privilege1, privilege2, …GRANT privilege1, privilege2, …ON object_nameON object_nameTO user1, user 2, …;TO user1, user 2, …;

Revoking object privileges:Revoking object privileges:REVOKE privilege1, privilege2, …REVOKE privilege1, privilege2, …ON object_nameON object_nameFROM user1, user 2, …;FROM user1, user 2, …;

Object PrivilegesObject Privileges

Examples of Object Examples of Object PrivilegesPrivileges

Object TypeObject TypePrivilegePrivilegeDescriptionDescription

Table, Table, SequenceSequence

ALTERALTERAllows user to change object’s Allows user to change object’s structure using the ALTER commandstructure using the ALTER command

Table, Table, SequenceSequence

DROPDROPAllows user to drop objectAllows user to drop object

Table, Table, SequenceSequence

SELECTSELECTAllows user to view objectAllows user to view object

TableTableINSERT, INSERT, UPDATE, UPDATE, DELETEDELETE

Allows user to insert, update, delete Allows user to insert, update, delete table datatable data

Any database Any database objectobject

ALLALLAllows user to perform any operation Allows user to perform any operation on objecton object

Granting and Revoking Granting and Revoking Object PrivilegesObject Privileges