Oracle PL/SQL
Cheatsheet
Symbols
; Semicolon.Statement terminator
tpx% Percent sign Attribute indicator (cursor attributes like
%ISOPEN and indirect declaration attributes like %ROWTYPE). Also ud as multibyte wildcard symbol, as in SQL.
_ Single underscore Single-byte wildcard symbol, as in SQL
: Colon Host variable indicator, such as :block.item in Oracle Forms
** Double asterisk Exponentiation operator
< > and !=Not equals"
|| Double vertical bar Concatenation operator
<< and >>Label delimiters
:=Assignment operator
=>Association operator for positional notation
--Double dash: single-line comment indicator
/* and */Beginning and ending multiline comment block delimiters
Data Types.rockmelon
Databa types NUMBER
CHAR(N), VARCHAR2(N)
DATE
LONG LONG RAW
ROWID MLSLABEL Definition牛津小学英语2a
Ud to store any number
Ud for storing text
Oracle system date
Stores large blocks of text Stores large blocks of binary data Smaller binary data store
Uesd for row identifier
Security label
Non databa types.DEC, DECIMAL, REAL, DOUBLE-PRECISION, INTEGER, INT, SMALLINT, NATURAL, POSITIVE, NUMERIC, BINARY-INTEGER, CHARACTER, VARCHAR, BOOLEAN, TABLE, RECORD
PLSQL Module types
Procedure A non-formal function that can accept paremeters via value or reference. Similar in form to a function.
jgbFunction A classical function that returns one value. Usually contains declaration, execution and exception ctions.
Package A library, consisting of a specification with function/prototype signatures, and a body with actual code. eg
Trigger
划船的英文Code attached to a table that fires on certian conditions.
Module Sections or Blocks
Variable Declaration DECLARE
pid%TYPE, pi CONSTANT number := 3.14, ratio REAL,.. BEGIN..
Executable Section .. BEGIN lect * into my_employee id = 42; END; ..
Exception Handler.END; EXCEPTIONS .. END;
Package Syntax
Specification PACKAGE package_name
IS
[ declarations of variables and types ] [ specifications of cursors ]
[ specifications of modules ]
END [ package_name ];
Body PACKAGE BODY package_name
IS
[ declarations of variables and types ] [ specification and SELECT statement of cursors ]
cgnp
[ specification and body of modules ] [ BEGIN
executable statements ]
[ EXCEPTION
exception handlers ]
END [ package_name ];
高斯奥特曼片尾曲Filename Extensions
General SQL*Plus
master piece
disguiscript
.sql
Testing script.tst
Stored procedure.sp
Stored function.sf
Stored package body spb
Stored package
specification
.sps
Implict cursor attributes.
%NOTFOUND True if fetch did not return row.
%ROWCOUNT Number of rows procesd by this cursor %FOUND Opposite of %NOTFOUND
%ISOPEN If currently open for processing then true.
Transaction processing
Same Options as SQL COMMIT, ROLLBACK, SAVEPOINT Transaction begins at
execution of first
change of data.
Rollbacks go to last COMMIT or SAVE_POINT
solemnly
DBMS_TRANSACTION A package with functions for transaction control.
