learn-oracle





SQL - Types of SQL Statements


Previous Chapter | Next Chapter

The tables in the following sections provide a functional summary of SQL statements and are divided into these categories:

* Data Definition Language (DDL) Statements

* Data Manipulation Language (DML) Statements

* Transaction Control Statements

* Session Control Statements

* System Control Statements

Data Definition Language (DDL) Statements
Data definition language (DDL) statements enable you to perform these tasks:

* Create, alter, and drop schema objects

* Grant and revoke privileges and roles

* Analyze information on a table, index, or cluster

* Establish auditing options

* Add comments to the data dictionary

The CREATE, ALTER, and DROP commands require exclusive access to the specified object. For example, an ALTER TABLE statement fails if another user has an open transaction on the specified table.

The GRANT, REVOKE, ANALYZE, AUDIT, and COMMENT commands do not require exclusive access to the specified object. For example, you can analyze a table while other users are updating the table.

Oracle implicitly commits the current transaction before and after every DDL statement.

Many DDL statements may cause Oracle to recompile or reauthorize schema objects. For information on how Oracle recompiles and reauthorizes schema objects and the circumstances under which a DDL statement would cause this

DDL statements are supported by PL/SQL with the use of the DBMS_SQL package.

Data Definition Language Statements
ALTER CLUSTER
ALTER DATABASE
ALTER DIMENSION
ALTER FUNCTION
ALTER INDEX
ALTER MATERIALIZED VIEW
ALTER MATERIALIZED VIEW
ALTER PACKAGE
ALTER PROCEDURE
ALTER PROFILE
ALTER RESOURCE COST
ALTER ROLE
ALTER ROLLBACK SEGMENT
ALTER SEQUENCE
ALTER TABLE
ALTER TABLESPACE
ALTER TRIGGER
ALTER TYPE
ALTER USER
ALTER VIEW
ANALYZE
ASSOCIATE STATISTICS
AUDIT
COMMENT
CREATE CLUSTER
CREATE CONTEXT
CREATE CONTROLFILE
CREATE DATABASE
CREATE DATABASE LINK
CREATE DIMENSION
CREATE DIRECTORY
CREATE FUNCTION
CREATE INDEX
CREATE INDEXTYPE
CREATE LIBRARY
CREATE MATERIALIZED VIEW
CREATE MATERIALIZED VIEW
CREATE OPERATOR
CREATE PACKAGE
CREATE PACKAGE BODY
CREATE PFILE
CREATE PROCEDURE
CREATE PROFILE
CREATE ROLE
CREATE ROLLBACK SEGMENT
CREATE SCHEMA
CREATE SEQUENCE
CREATE SYNONYM
CREATE SPFILE
CREATE TABLE
CREATE TABLESPACE
CREATE TEMPORARY TABLESPACE
CREATE TRIGGER
CREATE TYPE
CREATE USER
CREATE VIEW
DISASSOCIATE STATISTICS
DROP CLUSTER
DROP CONTEXT
DROP DATABASE LINK
DROP DIMENSION
DROP DIRECTORY
DROP FUNCTION
DROP INDEX
DROP INDEXTYPE
DROP LIBRARY
DROP MATERIALIZED VIEW
DROP MATERIALIZED VIEW
DROP OPERATOR
DROP PACKAGE
DROP PROCEDURE
DROP PROFILE
DROP ROLE
DROP ROLLBACK SEGMENT
DROP SEQUENCE
DROP SYNONYM
DROP TABLE
DROP TABLESPACE
DROP TRIGGER
DROP TYPE
DROP USER
DROP VIEW
GRANT
NOAUDIT
RENAME
REVOKE
TRUNCATE
Data Manipulation Language (DML) Statements
Data manipulation language (DML) statements query and manipulate data in existing schema objects. These statements do not implicitly commit the current transaction.

CALL
DELETE
EXPLAIN PLAN
INSERT
LOCK TABLE
MERGE
SELECT
UPDATE
The CALL and EXPLAIN PLAN statements are supported in PL/SQL only when executed dynamically. All other DML statements are fully supported in PL/SQL.

Transaction Control Statements

Transaction control statements manage changes made by DML statements.

Transaction Control Statements
Statement

COMMIT
ROLLBACK
SAVEPOINT
SET TRANSACTION
All transaction control statements except certain forms of the COMMIT and ROLLBACK commands are supported in PL/SQL. For information on the restrictions, see COMMIT and ROLLBACK.

Session Control Statements
Session control statements dynamically manage the properties of a user session. These statements do not implicitly commit the current transaction.

PL/SQL does not support session control statements.

Session Control Statements

Statement

ALTER SESSION

SET ROLE

System Control Statement
The single system control statement dynamically manages the properties of an Oracle instance. This statement does not implicitly commit the current transaction.

ALTER SYSTEM is not supported in PL/SQL.

Previous Chapter | Next Chapter

Discuss Savepoint to Update | More Tutorials on Oracle dba | Source : Oracle Documentation




Liked it ? Want to share it ? Social Bookmarking

Add to: Mr. Wong Add to: BoniTrust Add to: Newsider Add to: Digg Add to: Del.icio.us Add to: Reddit Add to: Jumptags Add to: StumbleUpon Add to: Slashdot Add to: Netscape Add to: Furl Add to: Yahoo Add to: Spurl Add to: Google Add to: Blinklist Add to: Technorati Add to: Newsvine Information




Want to share or request more about types of sql Statements in SQL Tutorial to become a Oracle DBA. Direct your requests to webmaster@oracleonline.info