SQL structure

В этой статье собраны и структурированы минимальные основы SQL Oracle из обширной документации базы данных Oracle 12c. Для тех, кто начинает изучение базы, эта информация поможет обрисовать общую картину. Для разработчиков и тестировщиков базы данных эти таблицы служат удобными помощниками в повседневной работе.
Расшифровки языковых аббревиатур в DB Oracle

SQL	Structured Query Language
DDL	Data Definition Language (DDL) statements manage schema objects in the database. These statements create new, drop old, and establish schema objects. They also control access to schema objects.
DML	Data Manipulation Language (DML) statements can change data in database tables. For example, DML statements insert new rows into a table, update column values in existing rows, delete rows from a table, lock a table in the database, and explain the execution plan for a SQL statement.
TCL	Transaction control language
PL	Procedural Language

Структуризация

SQL
Static PL/SQL			DDL		Dynamic PL/SQL		SQL*Plus
query	DML	TCL	manage	control	native	DBMS_SQL package
SELECT	DELETE	COMMIT	ALTER	ADMINISTER	CLOSE	DBMS_SQL.RETURN_RESULT Procedure	@
	EXPLAIN	ROLLBACK	CREATE	ANALYZE	EXECUTE IMMEDIATE	DBMS_SQL.GET_NEXT_RESULT Procedure	@@
	INSERT	SAVEPOINT	DROP	ASSOCIATE	FETCH	DBMS_SQL.TO_REFCURSOR Function	/
	LOCK TABLE	SET TRANSACTION	FLASHBACK	AUDIT	OPEN FOR	DBMS_SQL.TO_CURSOR_NUMBER Function	ACCEPT, APPEND, ARCHIVE, ATTRIBUTE
	MERGE		GRANT	CALL			BREAK, BTITLE
	UPDATE		PURGE	COMMENT			CHANGE, CLEAR, COLUMN, COMPUTE, CONNECT, COPY
			RENAME	DISASSOCIATE			DEFINE, DEL, DESCRIBE, DISCONNECT
			REVOKE	NOAUDIT			EDIT, EXECUTE, EXIT
			TRUNCATE	SET (constraint, role)			GET
							HELP, HOST
							INPUT
							LIST
							PASSWORD, PAUSE, PRINT, PROMPT
							QUIT
							RECOVER, REMARK, REPFOOTER, REPHEADER, RUN
							SAVE, SET, SHOW, SHUTDOWN, SPOOL, START, STARTUP, STORE
							TIMING, TTITLE
							UNDEFINE
							VARIABLE
							WHENEVER
							XQUERY

Нижеследующие списки касаются PL (Procedural Language)

Stored PL/SQL Units
Anonymous Block - часть хранимых подпрограмм, самостоятельный блок в скрипте или теле джоба, триггера
FUNCTION
LIBRARY
PACKAGE
PACKAGE BODY
PROCEDURE
TRIGGER
TYPE
TYPE BODY

PL/SQL Data Types
SQL	BOOLEAN	INTEGER	REF CURSOR	User-Defined Subtypes	Predefined
BINARY_FLOAT		SIMPLE_INTEGER		Unconstrained	BFILE
BINARY_DOUBLE		PLS_INTEGER		Constrained	BLOB
CHAR		BINARY_INTEGER		with Base Types	BOOLEAN
VARCHAR2					CHAR
LONG					CLOB
LONG RAW					DATE
ROWID					NUMBER
UROWID

PL/SQL Collections and Records						Collection Methods
Associative Arrays	Collection Types	Multidimensional Collections	Nested Tables	Varrays (Variable-Size Arrays)	Record	COUNT
					%ROWTYPE Attribute	DELETE
					Constants	EXISTS
					Types	EXTEND
					Variables	FIRST
						LAST
						LIMIT
						NEXT
						PRIOR
						TRIM

PL/SQL Language Elements
Statement	Pragma	Constuction	Declaration	Attributes	Others
Assignment	AUTONOMOUS_TRANSACTION	Block	Collection Variable	Implicit Cursor	Collection Method Invocation
Basic LOOP	EXCEPTION_INIT	Comment	Constant	Named Cursor	RETURNING INTO Clause
CASE	INLINE	Exception Handler	Cursor Variable	%ROWTYPE	SQLCODE Function
CLOSE	RESTRICT_REFERENCES	Expression	Exception	%TYPE	SQLERRM Function
CONTINUE	SERIALLY_REUSABLE		Explicit Cursor
Cursor FOR LOOP	UDF		Formal Parameter
DELETE			Function
EXECUTE IMMEDIATE			Procedure
EXIT			Record Variable
FETCH			Scalar Variable
FOR LOOP
FORALL
GOTO
IF
INSERT
NULL
OPEN
OPEN FOR
PIPE ROW
RAISE
RETURN
SELECT INTO
UPDATE
WHILE LOOP

PL/SQL Control Statements
Conditional Selection	LOOP	Sequential Control
IF THEN	LOOP	GOTO
IF THEN ELSE	EXIT	NULL
IF THEN ELSIF	EXIT WHEN
Simple CASE	CONTINUE
Searched CASE	CONTINUE WHEN
	FOR LOOP
	WHILE LOOP

SQL Statements for Stored PL/SQL Units
Изменение свойств*	Создание	Удаление
ALTER FUNCTION	CREATE FUNCTION	DROP FUNCTION
ALTER LIBRARY	CREATE LIBRARY	DROP LIBRARY
ALTER PACKAGE	CREATE PACKAGE	DROP PACKAGE
	CREATE PACKAGE BODY	DROP PACKAGE BODY**
ALTER PROCEDURE	CREATE PROCEDURE	DROP PROCEDURE
ALTER TRIGGER	CREATE TRIGGER	DROP TRIGGER
ALTER TYPE	CREATE TYPE	DROP TYPE
	CREATE TYPE BODY	DROP TYPE BODY

* - вместо внесения изменений через ALTER-команду можно пересоздать объект командой CREATE OR REPLACE. При этом объект сохранит статус, например, включенность триггера. ** - в документации Oracle пропущена команда, но удалить тело пакета с сохранением его спецификации позволительно

PL/SQL Reserved Words
ALL, ALTER, AND, ANY, AS, ASC, AT
BEGIN, BETWEEN, BY
CASE, CHECK, CLUSTERS, CLUSTER, COLAUTH, COLUMNS, COMPRESS, CONNECT, CRASH, CREATE, CURSOR
DECLARE, DEFAULT, DESC, DISTINCT, DROP
ELSE, END, EXCEPTION, EXCLUSIVE
FETCH, FOR, FROM, FUNCTION
GOTO, GRANT, GROUP
HAVING
IDENTIFIED, IF, IN, INDEX, INDEXES, INSERT, INTERSECT, INTO, IS
LIKE, LOCK
MINUS, MODE
NOCOMPRESS, NOT, NOWAIT, NULL
OF, ON, OPTION, OR, ORDER, OVERLAPS
PROCEDURE, PUBLIC
RESOURCE, REVOKE
SELECT, SHARE, SIZE, SQL, START, SUBTYPE
TABAUTH, TABLE, THEN, TO, TYPE
UNION, UNIQUE, UPDATE
VALUES, VIEW, VIEWS
WHEN, WHERE, WITH

PL/SQL Keywords
A, ADD, ACCESSIBLE, AGENT, AGGREGATE, ARRAY, ATTRIBUTE, AUTHID, AVG
BFILE_BASE, BINARY, BLOB_BASE, BLOCK, BODY, BOTH, BOUND, BULK, BYTE
C, CALL, CALLING, CASCADE, CHAR, CHAR_BASE, CHARACTER, CHARSET, CHARSETFORM, CHARSETID, CLOB_BASE, CLONE, CLOSE, COLLECT, COMMENT, COMMIT, COMMITTED, COMPILED, CONSTANT, CONSTRUCTOR, CONTEXT, CONTINUE, CONVERT, COUNT, CREDENTIAL, CURRENT, CUSTOMDATUM
DANGLING, DATA, DATE, DATE_BASE, DAY, DEFINE, DELETE, DETERMINISTIC, DIRECTORY, DOUBLE, DURATION
ELEMENT, ELSIF, EMPTY, ESCAPE, EXCEPT, EXCEPTIONS, EXECUTE, EXISTS, EXIT, EXTERNAL
FINAL, FIRST, FIXED, FLOAT, FORALL, FORCE
GENERAL
HASH, HEAP, HIDDEN, HOUR
IMMEDIATE, INCLUDING, INDICATOR, INDICES, INFINITE, INSTANTIABLE, INT, INTERFACE, INTERVAL, INVALIDATE, ISOLATION
JAVA
LANGUAGE, LARGE, LEADING, LENGTH, LEVEL, LIBRARY, LIKE2, LIKE4, LIKEC, LIMIT, LIMITED, LOCAL, LONG, LOOP
MAP, MAX, MAXLEN, MEMBER, MERGE, MIN, MINUTE, MOD, MODIFY, MONTH, MULTISET
NAME, NAN, NATIONAL, NATIVE, NCHAR, NEW, NOCOPY, NUMBER_BASE
OBJECT, OCICOLL, OCIDATE, OCIDATETIME, OCIDURATION, OCIINTERVAL, OCILOBLOCATOR, OCINUMBER, OCIRAW, OCIREF, OCIREFCURSOR, OCIROWID, OCISTRING, OCITYPE, OLD, ONLY, OPAQUE, OPEN, OPERATOR, ORACLE, ORADATA, ORGANIZATION, ORLANY, ORLVARY, OTHERS, OUT, OVERRIDING
PACKAGE, PARALLEL_ENABLE, PARAMETER, PARAMETERS, PARENT, PARTITION, PASCAL, PIPE, PIPELINED, PLUGGABLE, PRAGMA, PRECISION, PRIOR, PRIVATE
RAISE, RANGE, RAW, READ, RECORD, REF, REFERENCE, RELIES_ON, REM, REMAINDER, RENAME, RESULT, RESULT_CACHE, RETURN, RETURNING, REVERSE, ROLLBACK, ROW
SAMPLE, SAVE, SAVEPOINT, SB1, SB2, SB4, SECOND, SEGMENT, SELF, SEPARATE, SEQUENCE, SERIALIZABLE, SET, SHORT, SIZE_T, SOME, SPARSE, SQLCODE, SQLDATA, SQLNAME, SQLSTATE, STANDARD, STATIC, STDDEV, STORED, STRING, STRUCT, STYLE, SUBMULTISET, SUBPARTITION, SUBSTITUTABLE, SUM, SYNONYM
TDO, THE, TIME, TIMESTAMP, TIMEZONE_ABBR, TIMEZONE_HOUR, TIMEZONE_MINUTE, TIMEZONE_REGION, TRAILING, TRANSACTION, TRANSACTIONAL, TRUSTED
UB1, UB2, UB4, UNDER, UNPLUG, UNSIGNED, UNTRUSTED, USE, USING
VALIST, VALUE, VARIABLE, VARIANCE, VARRAY, VARYING, VOID
WHILE, WORK, WRAPPED, WRITE
YEAR
ZONE