Common Oracle DDLs

-- 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;