-- Table
CREATE TABLE <table name>
( <field name> <data type> NOT NULL,
-- ... ...
CONSTRAINT <primary key name> PRIMARY KEY (field name)
);
COMMENT ON COLUMN <table name.field name> IS 'comments...';
ALTER TABLE <table name> ADD <field name> <data type>
-- Index
CREATE INDEX <index name> ON <table name> (<field name>);
DROP INDEX <index name>;
-- View
CREATE OR REPLACE FORCE VIEW <view name> (<field name>,...)
SELECT <field name>,...
FROM <table/view name>;
-- Sequence
CREATE SEQUENCE <sequence name> MINVALUE 1 MAXVALUE 1000000000 INCREMENT BY 1 START WITH 1;
-- Stored Function
CREATE OR REPLACE FUNCTION <function name> (<parameter name> IN <data type>, ...)
RETURN <data type>
IS
<local variable name> <data type>;
BEGIN
IF <condition> THEN
<local variable name> := <value>;
ELSE
-- ... ...
END IF;
RETURN <local variable name>;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END <function name>;
-- Stored procedure
CREATE OR REPLACE PROCEDURE <procedure name> (<parameter name> IN <data type, ...)
IS
<local variable> <data type>;
CURSOR <cursor name> IS
<select query statement>;
BEGIN
OPEN <cursor name>;
LOOP
FETCH <cursor name> INTO <local variable>, ...;
EXIT WHEN <cursor name>%NOTFOUND;
-- iterator cursor
END LOOP;
CLOSE <cursor name>;
EXCEPTION
WHEN OTHERS THEN
<local vairable> := <value>;
DBMS_OUTPUT.PUT_LINE('SQL Error: ...');
END <procedure name>;
/
-- Package
-- Specification
CREATE OR REPLACE <package name>
AS
FUNCTION <function name> (<parameter name> IN <data type>, ...) RETURN <data type>;
PROCEDURE <procedure name> (<parameter name> IN <data type>, ...);
-- ...
END <package name>;
/
-- Body
CREATE OR REPLACE BODY <package name>
AS
FUNCTION <function name> (<parameter name> IN <data type>, ...)
IS
<local variable> <data type>;
BEGIN
END <function name>;
-- ... ...
RETURN <data type>
END <package name>
-- Trigger
CREATE OR REPLACE <trigger name>
BEFORE INSERT ON <table name> REFERENCING NEW AS NEW OLD AS OLD
-- or AFTER INSERT OR UPDATE ON <table name> ...s
FOR EACH ROW
DECLARE
<local variable> <data type>;
BEGIN
<local variable> := <value>;
IF :NEW.id IS NULL THEN
SELECT <sequence name>.NEXTVAL INTO <local variable> FROM DUAL;
:NEW.id := <local variable>;
END IF;
END;
-- Scheduled jobs
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => '<job name>',
job_type => 'PLSQL_BLOCK';
job_action => 'BEGIN <stored procedure name(parameters...)> END;',
start_date => TRUNC(SYSDATE, 'DD') + 23/24, -- starting at 23:00
repeat_interval => 'FREQ=MONTHLY;BYMONTHDAY=22;'
-- on 22nd every month; or FREQ=DAILY;BYHOUR=23, or FREQ=WEEKLY;BYDAY=FRI...s
);
END;
/
-- Grant permission
GRANT SELECT,UPDATE,DELETE,INSERT,EXECUTE ON <table name> TO <user name>;
REVOKE SELECT,UPDATE,DELETE,INSERT,EXECUTE ON <table name> FROM <user name>;
-- Get environment information using system functions
SELECT SYS_CONTEXT('USERENV','OS_USER'), SYS_CONTEXT('USERENV','MODULE'), SYS_CONTEXT('USERENV','HOST') FROM DUAL;
