Oracle9i SQL Reference
Release 1 (9.0.1)

Part Number A90125-01
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback

Go to previous page Go to beginning of chapter Go to next page

SQL Queries and Other SQL Statements, 3 of 3


Types of SQL Statements

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 definition language (DDL) statements enable you to perform these tasks:

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, see Oracle9i Database Concepts.

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

See Also:

Oracle9i Supplied PL/SQL Packages and Types Reference

Table 7-1 lists the DDL statements.

Table 7-1 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.

Table 7-2 Data Manipulation Language Statements
Statement 

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.

Table 7-3 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.

Table 7-4 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.

Table 7-5 System Control Statement
Statement 

ALTER SYSTEM 

Embedded SQL Statements

Embedded SQL statements place DDL, DML, and transaction control statements within a procedural language program. Embedded SQL is supported by the Oracle precompilers and is documented in the following books:


Go to previous page Go to beginning of chapter Go to next page
Oracle
Copyright © 1996-2001, Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback