OraclePLSQLNote

—————————————————-
– see .henryrc to see how to set oracle environment

– START ORACLE FROM HENRY ACCOUNT
sqlplus / as SYSDBA
startup

– kill all users
shutdown immediate

– normal shutdown
-wait until all users finished
shutdown
—————————————————–
su – oracle (no directory change/no source of bashrc)
su oracle

$ORACLE_HOME/server/bin
– listener has to be started with oracle account
– listen to ports, allow others to connect
source oracle_env.sh
lsnrctl start

lsnrctl status
lsnrctl stop

$ORACLE_HOME/server/network/admin
– the listener on the server uses this to configure listening
listener.ora
– the client on client machine uses this:
tnsnames.ora

– sqlplus user creation
create user testuser identified by abc123;
grant create session to testuser;

-change password for user hr
alter user hr identified by abc123;

-unlock user hr
alter user hr account unlock;

———————————————————–

-view all of table
SELECT * FROM table_name;

-table creation
CREATE TABLE table_name1 (
column_name1 TYPE CONSTRAINT constraint_name1 PRIMARY KEY,
column_name2 TYPE NOT NULL,
column_name3 TYPE CONSTRAINT constraint_name2 REFERENCES table_name2(some_column),
);
CREATE TABLE table_name2 (
column_name1 TYPE NOT NULL,
column_name2 TYPE NOT NULL,
CONSTRAINT constraint_name1 PRIMARY KEY (column_name1, column_name2),
);
-types
CHAR
DATE
INTEGER
NUMBER(precision, scale)    // parameters optional
VARCHAR2(integer)
BINARY_FLOAT            // value e.g.:    39.5f
BINARY_DOUBLE            // value e.g.: 15.7d

– value insertion
INSERT INTO table_name (column1, column2, …)
VALUES (value1, ‘value2′, …);

-update
UPDATE table_name SET column=value WHERE column=value;

-delete
DELETE FROM table_name WHERE column=value;

-rollback last change
ROLLBACK;

-procedures
CREATE PROCEDURE procedure_name (
parameter1    IN table_name.column_name%TYPE,
parameter2    IN TYPE,
…
) AS return_value TYPE
BEGIN
logic_goes_here
END procedure_name;

SELECT column1, column2, … FROM table;

– row identifier, store physical location of row
SELECT ROWID, … FROM table;

– row number
SELECT ROWNUM, … from table;

– describe table
DESCRIBE table;

– arithmetic
– note dual is a dummy table
SELECT TO_DATE(’25-JUL-2007’) + 2 FROM dual;
– with columns:
SELECT column1 + 2 FROM table;

– column alias – alternate name for column
SELECT column1 + 2 column_alias FROM table;
– preserve case of alias:
SELECT column1 + 2 “column_alias” FROM products;
SELECT column1 + 2 AS “column_alias” FROM products;

– concatenation
SELECT column1 || ‘concatenation_string’ || column2 AS “column_alias” FROM table;

– to find null
WHERE column IS NULL

– return a string if column is null
NVL(column, ‘string’)
SELECT column1, column2, NVL(column3, ‘null value’) AS column3 FROM table;

– selecting distinct rows
SELECT DISTINCT column FROM table;

Operators
=
<> or !=
<
>
<=
>=
ANY        compares one value with any value in a list
SOME    same as ANY
ALL        compares one value with all values in a list

Sql operators
NOT            reverse operator
IN            in a list of values
SELECT … WHERE column IN (value, …);
BETWEEN        match a range of values
SELECT … WHERE column BETWEEN value AND value;
IS NULL
IS NAN
IS INFINITE
LIKE        match patterns in strings
_        match any one character
%        match any number of characters beginning at specified position
ESCAPE    tells database how to differentiate between characters to search for and wildcards
e.g. SELECT name FROM promotions WHERE name LIKE ‘%\%%’ ESCAPE ‘\’;        second % is an actual character to be searched for

Logical Operators
AND, OR, NOT
SELECT … WHERE condition logical_operator condition;
– if AND and OR are in the same expression, AND takes precedence

– sorting rows
– sorts from least to greatest (ascending)
SELECT column FROM table ORDER BY column;
SELECT column FROM table ORDER BY column ASC, column DESC, …;
SELECT column FROM table ORDER BY column_number;

– multiple tables
SELECT table1.column1, table2.column2
FROM table1, table2
WHERE table1.column1 = table2.column1;
– table alias
SELECT alias1.column, alias2.column
FROM table1 alias1, table2 alias2
WHERE alias1…..;

Cartesian product
– join all rows from one table to another
SELECT table1.column, table2.column FROM table1, table2;

– remember: Number of joins = number of tables used – 1

Types of join conditions
– equijoins    – use =
– non-equijoins – use other logical operators
Types of joins
– inner join – return row only when columns in the join contain values that satisfy join condiiton (at least one match in both tables)
– outer joins – return row even when column(s) in join condition contain null value in a required column
– self joins – return rows joined on the same table

Outer Join
– retrieves row even when one of the columns in the join contains null
– uses (+) which is an Oracle proprietary outer join operator
– left outer join:
SELECT table1.column1, … FROM table1, … WHERE table1.column = table2.column (+);

– right outer join:
SELECT table1.column1, … FROM table1, … WHERE table1.column (+) = table2.column;

– outer joins can only be placed on one side, cannot use an IN(…) operator and cannot be used with an OR operator

Self Joins
– join made on same table
– mainly uses the concatenation operations
SELECT table1.column1 ||’ ‘|| table1.column2 ||’ string ‘|| NVL(table1.column3, ‘nullstring’) … alias FROM table1 ….;

Inner Joins on two tables in SQL/92
– INNER JOIN and ON clauses
SELECT table1.column1, table2.column2 FROM table1 INNER JOIN table2 ON table1.column3 = table2.column3;

USING keyword joins in SQL/92
– USING clause
– query must use equijoin
– columns in the equijoin must have the same name
SELECT table1.column1, table2.column2 FROM table1 INNER JOIN table2 USING (column3);

Inner joins on more than two tables in SQL/92
SELECT table1.column1, table2.column2 AS alias1, table2.column1 AS alias2 FROM table1, table2 WHERE …;
SELECT table1.column1, table2.column2 AS alias1, table2.column1 AS alias2 FROM table1 INNER JOIN table2 USING (column3), INNER JOIN table3 USING (column4) …;        // note in this case, table2 and table3 become FROM table2 INNER JOIN table3…

Multiple columns in SQL/92
SELECT … FROM table1 INNER JOIN table2 ON table1.column1 = table2.column1 AND table1.column2 = table2.column2…;
SELECT … FROM table1 INNER JOIN table2 USING (column1, column2);

Outer Joins using SQL/92
FROM table1 {LEFT|RIGHT|FULL} OUTER JOIN table2
– LEFT – left outer join
– RIGHT – right outer join
– FULL – full outer join, use all rows in table1 and table2, including those with null values in BOTH columns
– when doing this, we no longer use (+) operator

SELF Joins in SQL/92
SELECT table1.column1 ||’ ‘|| table2.column1 FROM table1 INNER JOIN table2 ON table1.column3=table2.column2;

Cross Joins in SQL/92
SELECT * FROM table1 CROSS JOIN table2;

SQLPLUS editing SQL statements
A[PPEND] text
C[HANGE] /old/new        change text specified by old to new in the current line
CL[EAR] BUFF[ER]
DEL
DEL x        delete line number x
R[UN]        run statement stored in buffer, could also use / to run
x            makes line specified by x the current line

SAV[E] filename [{REPLACE|APPEND}]    usually save as a .sql file
GET filename                        get file contents into buffer
STA[RT] filename                    run file
@ filename                            same as above
ED[IT]    filename                    if no file specified, copies contents of buffer to a file called afiedt.buf and starts text editor
SPO[OL] filename                    copies output from SQLPLUS to the file
SPO[OL] OFF                            stops spooling and closes the file

DEFINE _EDITOR = ‘editor’            e.g. vi

Formatting columns
COL[UMN] {column|alias} [options]
– column is column number, alias is the column alias to be formatted
– options
FOR[MAT] format                        set format for the display of the column or alias to the format string
HEA[DING] heading                    set the heading of the column or alias to the heading string
JUS[TIFY] [{LEFT|CENTER|RIGHT}]        places the column output left, center or right
WRA[PPED]                            wraps the end of a string onto the next line of output
WOR[D_WRAPPED]                        individual words are not split across two lines
CLE[AR]                                clears any formatting of columns, default
e.g. COLUMN column FORMAT 999.999

SET PAGESIZE number_of_lines
SET LINESIZE number_of_characters

-clear formatting
COLUMN column CLEAR
CLEAR COLUMNS            // all

Substitution variables
– used in place of actual values in statements, even columns and tables
– use & character
– will ask to enter a value
SELECT * FROM table WHERE column=&variable;
– if rerun with /, will prompt for a new value and display a verify, SET VERIFY OFF to supress
– change variable definition character by SET DEFINE ‘character’;

Defined variables
– use prior to SQL run
DEFINE variable=value
DEFINE                    use on its own to list all session variables

– wait for user to enter a value for variable:
ACCEPT variable_name [type] [FORMAT format] [PROMPT prompt] [HIDE]
– prompt is the text which is displayed by SQLPLUS
– HIDE value as it is entered

UNDEFINE variable

Reports/Scripts
@ [path]script.sql [parameters]
– users can run them .sql
– important to use at beginning of file:
SET ECHO OFF        //suppress display of statements
SET VERIFY OFF        //suppress display of verification messages
SET LINESIZE [int]
SET PAGESIZE [int]
– remember to UNDEFINE variables at end of sql file
– passing variable to script
… WHERE column = &[number] …
To run the script: @ script.sql parameter1 parameter2…    parameters get passed in this order to respective number
– header
TTITLE ‘strings and parameters’
– footer
BTITLE ‘strings and parameters’
for both header and footer:
LEFT, RIGHT, CENTER        justification of text
_DATE                    display current date
SQL.USER                display user
SQL.PNO                    display current page (FORMAT used to format the number, add this FORMAT [integer] before use of SQL.PNO)
SKIP [integer]            skip [integer] line(s)
– add at the end of .sql file:
TTITLE OFF
BTITLE OFF
CLEAR COLUMNS

Computing subtotals
– break up output based on change in a column value
BREAK ON column2
– compute a value for a column
COMPUTE SUM OF column1 ON column2
– sum will be displayed on column1

Help
HELP
HELP INDEX
HELP EDIT

Oracle Built in Functions

– Single row functions    – operate on one row at a time, return one row of output for each row of input
– aggregate function    – operate on multiple rows at a time, return one row of output

Singel Row Functions
– character functions
e.g. SELECT ASCII(‘a’) FROM dual;
SELECT CONCAT(first_name, last_name) FROM customers;
– numeric functions
e.g. SELECT COS(*) FROM degrees_table;
– conversion functions
– converts from one data type to another
– date functions
– regular expression functions
– includes use of many special metacharacters
– search for pattern of characters in strings

Aggregate Functions
e.g. SELECT AVG(price) FROM products;
– Grouping rows
SELECT AVG(price) FROM products WHERE product_type_id = 1;
– Group by clause groups rows into blocks with the same column value
SELECT product_type_id, COUNT(ROWID) FROM products GROUP BY product_type_id ORDER BY product_type_id;
– aggregate functions must be grouped by whenever retrieving columns not placed within aggregate function
SELECT product_type_id, AVG(price) FROM products GROUP BY product_type_id;
– Having clause must always be used with GROUP BY (but not the other way around)
– filter groups of rows
– essentially a WHERE clause for aggregate functions
SELECT product_type_id, AVG(price) FROM products GROUP BY product_type_id HAVING AVG(price) > 20;
– Order of clauses
SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY …

Dates
– ‘DD-MON-YYYY’        //Mon first 3 letters
– ‘YYYY-MM-DD’        // MM in numbers 1-12
– set default date format
ALTER SESSION SET NLS_DATE_FORMAT = ‘format’;
– TIMESTAMP(seconds_precision)

Subqueries
– inner select statement within outer select/update/delete statement
– types
– single-row        return 0 or one row to outer SQL statement
– Multiple row         return one or more rows to outer SQL statement

– multiple column    return more than one column to outer SQL statement
– correlated        reference one or more columns in outer SQL statement
– nested             subqueries placed within another subquery

Single-row
– inside WHERE clause
SELECT column1, column2 FROM table1 WHERE column3 = (SELECT column3 FROM table1 WHERE column2 = ‘value’);
– inside HAVING clause
SELECT column1, AVG(column2) FROM table1 GROUP BY column1 HAVING AVG(column2) < (SELECT MAX(AVG(column2)) FROM table1)….
– inside FROM clause
SELECT column1 FROM(SELECT column1 FROM table1 WHERE column1 < 3);
– NOTE IN ALL CASES THE PARENTHESES QUERY MUST ONLY RETURN ONE ROW
– SUBQUERIES MAY NOT CONTAIN ORDER BY CLAUSES

Multiple-row
– inside IN or NOT IN clause
SELECT column1, column2 FROM table1 WHERE column1 IN (SELECT column1 FROM table1 WHERE column2 LIKE ‘%e%’);
– inside ANY clause
SELECT column1, column2 FROM table1 WHERE column3 < ANY (SELECT column4 FROM table2);
– inside ALL clause
SELECT column1, column2 FROM table1 WHERE column3 > ALL (SELECT column4 FROM table2);

Multiple Column Subqueries
SELECT column1, column2, column3 FROM table1 WHERE (column3, column4) IN (SELECT column3, MIN(column4) FROM table1 GROUP BY column3);

Correlated subqueries
SELECT column1, column2, column3 FROM table1 outer WHERE column3 > (SELECT AVG(column3) FROM table1 inner WHERE inner.column4 = outer.column4);
– using EXISTS and NOT EXISTS
SELECT column1, column2 FROM table1 outer WHERE EXISTS (SELECT column1 FROM table1 inner WHERE inner.column3=outer.column3);
– exists checks for existence of rows, whereas in checks for existence of values

Nested subqueries
– just subquery in a subquery, treating higher subquery as higher SQL statment
– ORDER BY can only be used by outermost one

Update
UPDATE table1 SET column1 = (SELECT AVG(column3) FROM table2) WHERE column2 = value;

Delete
DELETE FROM table1 WHERE column1 > (SELECT AVG(column2) FROM table2);

Advanced Queries

Set Operator
– combine rows returned by two or more queries
– number of columns and column types returned by queries must match, names could be differenct
UNION ALL        return all rows retrieved by queries, include duplicates
UNION            return all non-duplicate rows
INTERSECT        return rows that both queries retrieve in common
MINUS            return the remaining rows when row retrieved by second query is subtracted from those of the first

SELECT column1, column2, column3 FROM table1 UNION ALL SELECT column1, column2, column3 FROM table2 ORDER BY 1;

Combining set operators
– use parentheses
(SELECT column1, column2, column3 FROM table1 UNION SELECT column1, column2, column3 FROM table2) INTERSECT
SELECT column1, column2, column3 FROM table3;

CASE expression
– if then else logic
– types
– simple case expressions, use expressions to determine the returned value
– searched case expressions, use conditions to determine the returned value

– simple case expressions
SELECT column1, column2,
CASE search_expression
WHEN expression1 THEN result1
WHEN expression2 THEN result2
…
ELSE default_result
END
FROM tabl1;

Hierarchical Queries
– tree structure
– root node            top of tree
– parent node        has children
– child node
– leaf node            no children

CONNECT BY and START WITH Clauses
SELECT [LEVEL], column, expression, …
FROM table [WHERE where_clause] [[START WITH start_condition] [CONNECT BY PRIOR prior_condition]];
LEVEL                pseudo-colum that tells how far into tree you are: 1 for root node, 2 for child node,…
start_condition        where to start in hierarchical query, e.g. employee_id = 1
prior_condition        relationship between parent and child rows, e.g. employee_id = manager_id

Extended Group By Clauses
– ROLLUP        return a row containing subtotal for each group of rows, plus a row containing grand total
– CUBE            containing a subtotal for all combinations of rows, plus a grand total row

– passing a single column to ROLLUP
SELECT column1, SUM(column2) FROM table1 GROUP BY ROLLUP(column1) ORDER BY column1;
sum displayed for each column1, final sum of column2 displayed
– passing multiple columns to ROLLUP
SELECT column1, column2, AVG(column3) FROM table1 GROUP BY ROLLUP(column1,column2) ORDER BY column1, column3;
avg of column3 displayed for each column1, final avg of column3 displayed

– CUBE clause
SELECT column1, column2, SUM(column3) FROM table1 GROUP BY CUBE(column1, column2) ORDER BY column1, column2;
sum is displayed for each column1, sum displayed for each column2, total sum displayed

GROUPING() function
– accepts column, return 1 if column value is null, 0 if not null
– good to use with logical operations, e.g. CASE
SELECT GROUPING(column1), column1, SUM(column2) FROM table1 GROUP BY ROLLUP(column1) ORDER BY column1;

– GROUPING SETS
– get subtotal rows
– use this over CUBE whenever possible
SELECT column1, column2, SUM(column3) FROM table1 GROUP BY GROUPING SETS(column1, column2) ORDER BY column1, column2;

– GROUPING_ID
– filter rows using a having clause to exclude rows that don’t contain a subtotal or total
– accept one or more columns and returns the decimal equivalent of the GROUPING bit vector

column1    column2 Bit Vector    GROUPING_ID Return Value
!null    !null    00            0
!null    null    01            1
null    !null    10            2
null    null    11            3

– exclude rows that don’t contain a subtotal by simply checking if GROUPING_ID > 0
SELECT column1, column2, GROUPING_ID(column1,column2) AS grp_id, SUM(column3) FROM table1
GROUP BY CUBE(column1,column2) HAVING GROUPING_ID(column1,column2) > 0 ORDER BY column1,column2;

– GROUP_ID
– if n duplicated exist for a particular grouping, return numbers in the range 0 to n-1
– eliminate duplicate rows using a having clause that only allows for GROUP_ID = 0
SELECT column1, column2, GROUP_ID(), SUM(column3) FROM table1 GROUP BY column1, ROLLUP(column1,column2) HAVING GROUP_ID() = 0;

Analytic functions
– complex calculations, e.g. stats, google these

MODEL clause
– interrow calculations
SELECT column1, column2, column3, column4 FROM table1 WHERE column1 BETWEEN value1 AND value2
MODEL PARTITION BY (column1) DIMENSION BY (column2, column3) MEASURES (amount column4) (
column4[value_for_column2,value_for_column3] = ….
AVG(column4)[column2 BETWEEN value1 AND value2, value_for_column3] = …
);
PARTITION BY (x)                the results are partitioned by x
DIMENSION BY (x,y,…)            the dimensions of the array are x,y,…
MEASURES (amount z)                specifies each cell in the array contains an amount and that the array name is z

– positional notation: access cell by z[value_for_column2,value_for_column3,…]
– symbolic notation: access cell by z[column2=value_for_column2,column3=value_for_column3,…]
– can access all cells using the ANY (for positional) and IS ANY (for symbolic)
e.g. column4[ANY, column3 IS ANY]

– CURRENTV() gets current value of dimension
– FOR loop:
column4[FOR column2 FROM 1 TO 3 INCREMENT 1, value_for_column3]

– IS PRESENT    returns true if row specified by cell reference existed prior to the execution of MODEL clause
– PRESENT(cell, expr1, expr2)    return expr1 if row specified by cell existed prior to the execution of MODEL clause, expr2 if not
– PRESENTNNV(cell, expr1, expr2)     same as above except condition includes that cell value is not null
– IGNORE NAV        return 0, empty string, 01-JAN-2000 or null for null (put next to MODEL)
– KEEP NAV            return null for null or missing numeric values (put next to MODEL)

– RULES UPDATE(…)        if a cell doesn’t exist, a new row will not be created as by default but the code inside brackets executed

PIVOT and UNPIVOT
– pivot rotate rows into columns in the output of query, also allows run of aggregation function on data
e.g.
SELECT * FROM ( SELECT month, prd_type_id, amount FROM all_sales WHERE year = 2003 AND prd_type_id IN (1,2,3))
PIVOT(SUM(amount) FOR month IN (1 AS JAN, 2 AS FEB, 3 AS MAR, 4 AS APR)) ORDER BY prd_type_id;

Pivoting on multiple columns
place columnes in the FOR part of the loop
… FOR (month, prd_type_id) IN (….

Multiple Aggregate Functions in Pivot
… PIVOT ( SUM(amount) AS sum_amount, AVG(amount) AS avg_amount FOR (month) IN (1 AS….)) …

UNPIVOT
– opposite of pivot
– same format

INSERT statement
INSERT INTO table1 (column1, column2,…) VALUES (integer_value, ‘string’, NULL, …);
INSERT INTO table1 VALUES (integer_value, ‘string’,…);

UPDATE
UPDATE table1 SET column1 = column_value WHERE column2 = column_value;

RETURNING
– return the value from an aggregate function
VARIABLE variable_name NUMBER
UPDATE table1 SET column1 = columnvalue RETURNING AVG(column1) INTO :variable_name
PRINT variable_name

DELETE
DELETE FROM table1 WHERE column1=columnvalue;

Database integrity
– changes made to rows by Insert, update, delete do not affect primary key and foreign key relationships for table

Default values
CREATE TABLE table1 (… column TYPE DEFAULT value);

Merging rows
– merge rows from one table into another
MERGE INTO table1 a USING table2 b ON( a.column1 = b.column1)
WHEN MATCHED THEN
UPDATE
SET ….
WHEN NOT MATCHED THEN
INSERT(a.column1, a.column2,….) VALUES(b.column1, b.column2,…);

Database transaction
– groups of sql statements that perform a logical unit of work
– to finalize
COMMIT;
– add change and make permanent
– undo
ROLLBACK;
-Savepoints
SAVEPOINT savepoint_name;
ROLLBACK TO SAVEPOINT savepoint_name;
– changes to table cannot be seen by other users until committed
– transaction locking
– one user alters table, locks table until he/she commits the change

– Phantom reads            – query read, new row inserted, query read, new row suddenly appears
– nonrepeatable reads    – reads row, row updated, row read again
– dirty reads            – row updated, not commit, read by user, rollback

Transaction isolation levels
READ UNCOMMITED        Phantom reads, nonrepeatable reads & dirty reads permitted
READ COMMITTED        Phantom reads, nonrepeatable reads
REPEATABLE READ        Phantom reads
SERIALIZABLE        all disallowed
– SET TRANSACTION ISOLATION LEVEL level;

Query flashbacks
– mistaken changes made, want to view original rows
– grant permission to do so:
GRANT EXECUTE ON SYS.DBMS_FLASHBACK TO user;

EXECUTE DBMS_FLASHBACK.ENABLE_AT_TIME(SYSDATE – 10/1440);        10 minutes in the past
EXECUTE DBMS_FLASHBACK.DISABLE();                                stop the flashbacks

System Change Number Flashbacks
– more precise, uses SCNs
VARIABLE variable1 NUMBER
EXECUTE :variable1 := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();    // gets current SCN
PRINT variable1

EXECUTE DMBS_CLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER(:current_scn);

EXECUTE DBMS_FLASHBACK.DISABLE();

Users
CREATE USER user_name IDENTIFIED BY password
[DEFAULT TABLESPACE default_tablespace]
[TEMPORARY TABLESPACE temporary_tablespace];

-view tablespace by
SELECT tablespace_name FROM dba_tablespaces;

GRANT CREATE SESSION, CREAT USER, CREATE TABLE,… TO user1, user2,…;
REVOKE CREATE SESSION,… FROM user1, user2,…;

-password change
ALTER USER user IDENTIFIED BY new_password;

CONNECT system/manager
DROP USER user;

– priveleges
query user_sys_prics table to check what priveleges user has
– object priveleges
-grant user permission to perform certain actions on database objects (e.g. update…)
GRANT SELECT, INSERT, UPDATE, EXECUTE, DELETE ON user1.table1 TO user;
GRANT UPDATE(column1, column2,…) ON …;
query user_tab_privs_made to see which object priveleges a user has made to other users
query user_tab_privs_recd to see received priveleges

– synonyms
– replaces having to type in user.table every time
GRANT CREATE SYNONYM TO user;
CREATE SYNONYM synonym FOR user1.table1;
– public synonym
– all users see the synonym
CREATE PUBLIC SYNONYM synonym FOR user1.table1;

REVOKE privelege ON table/synonym FROM user;

CREATE ROLE role [IDENTIFIED BY password];
– then grant priveleges to role
– check by selecting role_sys_privs or role_tabs_privs
– then grant role to user
– check by selecting user_role_privs

REVOKE role FROM user;

Auditing
– keep track of database operations
GRANT AUDIT SYSTEM/ANY TO user;
AUDIT command [BY user][ON table1];                        now audits any ‘command’ executed by user
SELECT * FROM user_audit_trail WHERE audit_option = ‘command’
user_audit_trail, user_audit_object, user_audit_session, user_audit_statement
– could also use the conditions: WHENEVER SUCCESSFUL, WHENEVER NOT SUCCESSFUL, BY SESSION, BY ACCESS

Creating tables
CREATE [GLOBAL TEMPORARY] TABLE table_name (
column_name TYPE [CONSTRAINT constraint_def DEFAULT default_exp],…
)
[ON COMMIT {DELETE|PRESERVE} ROWS]
TABLESPACE tab_space;

– GLOBAL TEMPORARY means table rows are temporary
– DESCRIBE
– SELECT FROM user_tab_columns

ALTER TABLE table1 [ADD new_column TYPE …][DROP COLUMN column]
ALTER TABLE table MODIFY column [NEW_TYPE][DEFAULT value][ADD CONSTRAINT name [CHECK condition]]

-foreign key
ALTER TABLE table1 ADD CONSTRAINT name column REFERENCES table(column) [ON DELETE [CASCADE|SET NULL]] [UNIQUE (column)] [DISABLE]
– ON DELETE CASCADE – if row in parent table deleted, matching rows in child table deleted
– UNIQUE – any existing rows must have a unique value in specified column

Dropping constraint
ALTER TABLE table DROP CONSTRAINT name;

Enable constraint
ALTER TABLE table ENABLE CONSTRAINT name;

Deferred constraint
– one enforced when transaction is committed
– add this after the constraint name
DEFERRABLE [INITIALLY DEFERRED|INITIALLY IMMEDIATE]
[checked when transaction committed|checked whenever row added, updated/deleted]

– get information on constraints for column
– query user_cons_columns where table_name = tablename

– comments
– to add to table/column
COMMENT ON {TABLE|COLUMN} table|table.column_name IS ‘comment’;
– to view:
SELECT * FROM {user_tab_comments|user_col_comments} WHERE table_name= table;
– truncate table
– remove all rows and reset storage area
TRUNCATE TABLE table;

DROP TABLE table;

Sequences
– generate a sequence of integers, usually to populate primary key column

CREATE SEQUENCE sequence_name
[START WITH start_num]
[INCREMENT BY increment]
[{MAXVALUE maximum_num|NOMAXVALUE}]
[{MINVALUE minimum_num|NOMINVALUE}]
[{CYCLE|NOCYCLE}]
[{CACHE cache_num|NOCACHE}]
[{ORDER|NOORDER}];

– select from user_sequences to view details
– currval column:    current value of sequence
– nextval column:    next value of sequence

-populating primary key wtih sequence
INSERT INTO table1 (column1, column2, …) VALUES (sequence1.nextval, value2,…);

ALTER SEQUENCE sequence INCREMENT BY x;        all new values will be incremented by x
DROP SEQUENCE sequence;

Indexes
– used to find specific rows in table
– rule of thumb: create index when a query retrieves <= 10 percent of total rows in table
– index should contain a wide range of values
– B-tree index
CREATE [UNIQUE} INDEX index_name ON table_name(column1 [,column2, …]) TABLESPACE tab_space;
note that functions can be applied to columns in creating index

– query user_indexes or all_indexes to see index information
ALTER INDEX
DROP INDEX

– bitmap index
– usually used for very large amounts of data
– bitmap, not pointer, used for each key value
CREATE BITMAP INDEX index_name ON table_name(….

Views
– predefined queries

CREATE [OR REPLACE] [{FORCE|NOFORCE}] VIEW view_name
[(alias_name[,alias_name,…])] AS subquery
[WITH (CHECK OPTION|READ ONLY) CONSTRAINT constraint_name];

– queries can be performed on a view
– inserts can be performed as well (only iwth simple views)
– insert, update and delete rows that arent visible by view can be on if WITH CHECK OPTION isn’t present
– if on, then any DML statements must satisfy view conditions
– READ ONLY constraint
– DESCRIBE works on views
– view constraints can be seen in the user_constraints view

Complex Views
– contain subqueries/group rows/functions

Modifying vies
– CREATE OR REPLACE VIEW
– ALTER VIEW view …
– DROP VIEW view;

Flashback Data archives
– store changes made to a table over a period of time
CREATE FLASHBACK ARCHIVE archive TABLESPACE tab_space QUOTA data_size RETENTION time;        e.g. 1 M (1 megabyte), 1 DAY
ALTER FLASHBACK ARCHIVE archive PURGE ALL;
ALTER FLASHBACK ARCHIVE archive PURGE BEFORE TIMESTAMP(timestamp);

PL/SQL programs
– divided up into blocks, each containing statements
[DECLARE declaration_statements]
BEGIN
executable_statements
[EXCEPTION
exception_handling_statements
]
END;
/
– PL/SQL block terminated with a /

e.g.

SET SERVEROUTPUT ON
DECLARE
v_width INTEGER;
v_height INTEGER := 2;
v_area INTEGER := 6;
BEGIN
— set width equal to the area divided by height
v_width := v_area/v_height;
DBMS_OUTPUT.PUT_LINE(‘v_width = ‘ || v_width);
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE(‘Division by zero’);
END;
/

Variables
– obvious.

Logic
IF condition1 THEN
statement1
ELSIF condition2 THEN
statement2
ELSE
statement3
END IF;

Loops
LOOP
statements
EXIT WHEN condition            — this line can occur anywhere in loop code
END LOOP;

WHILE condition LOOP
statement
END LOOP;

FOR loop_variable IN [REVERSE] lower_bound..upper_bound LOOP
statements
END LOOP;

Cursors
– fetch rows returned by a query
– steps
1) declare variables to store column values for a row
DECLARE
variable1 table1.column1%TYPE;
variable2 table1.column2%TYPE;
2) declare the cursor, which contains a query
CURSOR cursor1 IS
select_statement;
3) open the cursor
OPEN cursor1;
4) fetch the rows from the cursor one at a time and store column values in variable
FETCH cursor1 INTO variable1 [,variable2,…];
(note you usually use a LOOP)
5) close cursor
CLOSE cursor1;

Open-for statement
– flexible: can assign cursor to a different query
OPEN cursor1 FOR select_statement;

Unconstrained Cursors
– return type for a constrained cursor must match the columns in the query that is run by the cursor
– no return type – can run any query
TYPE cursor1 IS RED CURSOR;                RED CURSOR type has no return type
cursor2 cursor1;                        declare a cursor1 object called cursor2
use the cursor2 type from now on

Exceptions
– exception block catches exceptions
– place right before END;
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE(‘DIVISION BY ZERO’);

Procedures
– centralize business logic
CREATE [OR REPLACE] PROCEDURE procedure1
[(parameter_name [IN|OUT|IN OUT] type [,…])]
{IS|AS}
BEGIN
procedure_body
END procedure1;

– IN|OUT|IN OUT
– mode of the parameter
IN: default mode, an IN parameter must be set to a value when the procedure is run, cannot be changed in procedure body
OUT: parameter set to a value in the body
IN OUT: combination of both
– call procedure
CALL procedure(parameter1, parameter2,…);
DROP PROCEDURE procedure;

– if procedure creation has errors, type SHOW ERRORS to show

Function
– similar to procedure
– function must return a value
CREATE [OR REPLACE] FUNCTION function1
[(parameter_name [IN|OUT|IN OUT] type [,…])]
RETURN type
{IS|AS}
BEGIN
function_body
END function1

– calling function
SELECT function(parameters…) FROM dual;

Packages
– group functions and procedures
CREATE [OR REPLACE] PACKAGE package_name
{IS|AS}
package_specification
END package_name;

– package_specification:
TYPE t_ref_cursor IS RED CURSOR;
FUNCTION get_products_ref_curor RETURN t_ref_cursor;
PROCEDURE update_product_price(p_product_id IN products.product_id%TYPE, p_factor IN NUMBER);

– package body
CREATE [OR REPLACE] PACKAGE BODY package_name
{IS|AS}
package_body
END package_name;

CREATE PACKAGE BODY product_package AS
FUNCTION get_products_ref-cursor
RETURN t_red-cursor IS
v_products_ref_cursor t_red-cursor;
BEGIN
OPEN v_products_ref_cursor FOR
SELECT product_id, name, price
FROM products;
RETURN v_products_ref_cursor;
END get_products_red_cursor;

PROCEDURE update_product_price(p_product_id IN products.product_id%TYPE, p_factor IN NUMBER) AS v_product_count INTEGER;
BEGIN
SELECT COUNT(*)
INTO v_product_count
FROM products
WHERE product_id = p_product_id;

IF v_product_count = 1 THEN
UPDATE products
SET price = price * p_factor
WHERE product_id = p_product_id;
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END update_product_price;
END product_package;

– calling functions and procedures in a package
SELECT product_package.get_products_red_cursor FROM dual;
-info on func and proc in package
SELECT object_name, procedure_name FROM user_procedures;
– FROP PACKAGE package;

Triggers
– procedure run automatically by database when specified DML statement run against certain database table
– may fire before or after DML
– can be run once for every row affected by DML (row level trigger), also has access to old and new column values when trigger fires
– or once for all rows (statement level trigger)

CREATE [OR REPLACE] TRIGGER trigger
{BEFORE|AFTER|INSTEAD OF|FOR} trigger_event
ON table
[FOR EACH ROW]
[{FORWARD|REVERSE} CROSSEDITION]
[{FOLLOWS|PRECEDES} schema.other_trigger]
[{ENABLE|DISABLE}]
[WHEN trigger_condition]
BEGIN
trigger_body
END trigger;

– trigger event causes trigger to fire
– FORWARD|REVERSE CROSSEDITION    forward intended to fire when a FML makes change while an online application accessing database is being patched, reverse intended to fire and handle DML after patch
– FOLLOWS|PRECEDES schema.other_trigger specifies whether this trigger goes before/after another

– firing trigger
SET SERVEROUTPUT ON
then do something that satisfies condition
– ALTER TRIGGER trigger…
DROP TRIGGER trigger;

Objects
CREATE TYPE object AS OBJECT (
variable1    TYPE,
…
MEMBER FUNCTION function RETURN type
);
/

CREATE TYPE BODY object AS
MEMBER FUNCTION function RETURN type IS variable type;
BEGIN
…
RETURN variable;
END;
END;
/

DESCRIBE object

– when adding row to table, must use a constructor to supply attribute values:
INSERT INTO products (product, quantity_in_stock) VALUES ( object(1,’pasta’,’20 oz bag of pasta’,3.95,10),50);

– selecting object from table:
SELECT p.object
FROM products p
WHERE p.object.id = 1;

SELECT p.product.column1, p.product.function(), …. FROM….

Object table
– object type used to define table
CREATE TABLE table OF object;

– note we can have an object inside another object

REF(table) retrieves object identifier (OID) , unique for each object in table
CREATE TABLE table (
id INTEGER PRIMARY KEY,
customer_red REF t_person SCOPE IS object_customers,…
);
– REF defines an object reference, SCOPE IS restricts an object reference to point to objects in a specific table

TYPE inheritance
– useful for nested objects

CREATE TYPE object1 AS OBJECT (
parameter1    TYPE,
parameter2    object2,
MEMBER FUNCTION function RETURN VARCHAR2
) NOT FINAL;
/
– not final clause means that attributes from object1 can be inherited from
to inherit:
CREATE TYPE object3 UNDER object1(
parameter1    TYPE,…
);
/

Using a subtype object in place of a supertype object
– if a table is of a object1 type and object2 is a subtype of object1, object2 can be stored in table
– add NOT SUBSTITUTABLE AT ALL LEVELS clause to end of CREATE TABLE statement in order to prevent such behaviour
– IS OF () checks if an object is of a particular type/subtype
– TREAT() does a run-time check to see if an objects type may be treated as a supertype
– SYS_TYPEID() returns ID of object’s type

NOT INSTATNTIABLE object types
– prevent objects of said type from being created
CREATE TYPE object1 AS OBJECT(….) NOT FINAL NOT INSTANTIABLE;/
– use object1 as an abstract supertype and never create any objects of that type, e.g. vehicle obj, only create subtypes car and boat obj

User-Defined Constructors
– inside create type parentheses:
CONSTRUCTOR FUNCTION object_name (
parameter1 INTEGER
parameter2 ….
) RETURN SELF AS RESULT
– can have multiple constructors: method overloading

Overriding Methods
– if supertype object has method, can override in the subtype object by:
CREATE TYPE object2 UNDER object1(
…
OVERRIDING MEMEBER FUNCTION function RETURN INTEGER
);
/
CREATE TYPE BODY object2 AS
OVERRIDING MEMBER FUNCTION function RETURN INTEGER IS
BEGIN
…
RETURN integer;
END;
END;
/

Generalized Invocation
– call a method in a supertype from a subtype
(SELF AS super_object).function(parameters)

Collections
– database types that stores sets of elements
– Varrays
– stores an ordered set of elements, each element has an index that records its position in the array
– can only be modified as a whole, not individually
– Nested tables
– table in another table
– can modify individual elements in a nested table
– Associative arrays
– something like a hash table, only in PL/SQL
– set of key and value pairs

script: collection_schema.sql
user: collection_user
password: collection_password

Varray
CREATE TYPE varray1 AS VARRAY(integer_size) OF type;/
– to change size limit:
ALTER TYPE varray MODIFY LIMIT integer_size CASCADE;
– incorporating Varray into tables: simply use the varray as a TYPE
– populating a varray with elements
INSERT INTO table VALUES(column1, …, varray(element, element,…));
UPDATE table SET columnX = varray(…) WHERE …;

Nested Table
CREATE TYPE nested_table AS TABLE OF table;/
– using a nested table to define a column in a table
CREATE TABLE table (
column1 INTEGER PRIMARY KEY,
…
columnX nested_table
)
NESTED TABLE
columnX
STORE AS
nested_table_name [TABLESPACE tablespace];
– populating nested table with elements
INSERT INTO table VALUES(column1,…., nested_table(…));
UPDATE TABLE(SELECT columnX FROM table WHERE …) VALUES ( nested_table(…));
UPDATE TABLE (SELECT columnX FROM table WHERE …) alias column SET VALUE(alias) = nested_table(…) WHERE VALUE(alias) = nested_table(…);
DELETE FROM TABLE (SELECT columnX FROM table WHERE …) alias column WHERE VALUE(alias) = nested_table(…);

Table()
– retrieval of varray and nested tables as strictly rows
SELECT a.* FROM table c, TABLE(c.varray) a;
SELECT a.* FROM table c, TABLE(c.nested_table) a;

Map method
– compare contents of nested tables
– equal only if
– same type
– same no. of rows
– all elements contain same values
CREATE TYPE object AS OBJECT(
…
MAP MEMBER FUNCTION function RETURN type
);/
CREATE TYPE BODY object AS
MAP MEMBER FUNCTION function RETURN type IS
BEGIN
… RETURN type
END function;
END;
– comparison of objects will automatically use the MAP function

CAST()
– convert collection of one type to another
CAST(____AS____)

Multilevel Collections
– collections of collections

Associative Arrays
– set of key and value pair
TYPE associative_array IS TABLE OF NUMBER INDEX BY VARCHAR2(15);

-changing size of an element type
ALTER TYPE varray MODIFY ELEMENT TYPE CARCHAR2(60) CASCADE;
-changing no. of elements in varray
ALTER TYPE varray MODIFY LIMIT 5 CASCADE;

Large Objects (LOB)
– CLOB    character data LOB type
– NCLOB    store multiple byte character data (e.g. Chinese)
– BLOB    binary data
– BFILE    binary FILE type, store a pointer to a file

– store up to 128 terabytes, a LONG and LONG RAW column can only 2 gigs
– table can have multiple LOB columns, but only one LONG or LONG RAW
– can be accessed in random order

LOB
– two parts
– LOB locator    pointer specifying location of LOB data
– LOB data        actual data stored in LOB

Type declaration
CLOB
BLOB
BFILE
NCLOB

Value insertion
TO_CLOB(‘text’)
TO_BLOB(‘11110001010101’)
EMPTY_CLOB() … same for all others

BFILE
– must first create a directory object in database, store the dictory in the file system where files located
CREAT DIRECTORY directory AS ‘path’;
INSERT INTO bfile_table (id, bfile_column) VALUES (1, BFILENAME(‘directory_object’,’filename’));

LONG and LONG RAW types
– LONG    store up to 2gig character data
LONG RAW    store up to 2gig binary data
RAW    store up to 4kn of binary data

Encryption of LOB data
– need to create a wallet directory in $ORACLE_BASE\admin\$ORACLE_SID
ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY “password”;

CREATE TABLE ( …, clob_column CLOB ENCRYPT USING ‘encrytiontype’)
LOB(clob_column) STORE AS SECUTEFILE([COMPRESS][DEDUPLICATE LOB]{CACHE|CACHE READS|NOCACHE});

ALTER SYSTEM SET WALLET CLOSE;

————————–
Running SQL with Java

Environment Variables
$ORACLE_HOME            /usr/lib/oracle/xe/app/oracle/product/10.2.0/server
$JAVA_HOME                /usr/lib/jvm/java-6-openjdk
$PATH                    $JAVA_HOME/bin:$ORALCE_HOME/bin
$CLASSPATH                $ORACLE_HOME/jdbc/lib/ojdbc6.jar:$ORACLE_HOME/jlib/orail8n.jar
$LD_LIBRARY_PATH        $ORACLE_HOME/jdbc/lib

Oracle JDBC Drivers
– Thin Driver
– smallest footprint
– OCI Driver
– middle tier
Server-Side Internal Driver
– provides direct access to database, used by Oracle JVM to  communicate with that database
Server-Side Thin Driver
– same as above, except with a Thin Driver aspect

To use JDBC in java
1)    import java.sql.*;
2)    register the Oracle JDBC Drivers
Class.forName(“oracle.jdbc.OracleDriver”);            // forName() method
//or
DriverManager.registeredDriver(new oracle.jdbc.OracleDriver());        // registerDriver() method
3)     Opening a database connection
Using getConnection():
Connection myConncetion = DriverManager.getConnection(URL, username, password);        // getConncetion() method: URL, username, password
// about the database URL
format:        driver_name:@driver_information
driver_name    is one of the following:
jdbc:oracle:thin
jdbc:oracle:oci
driver_information is the format:
host_name:port:database_SID
host_name        name of computer
port            port number on which database listener waits (1521 default)
database_SID    Oracle SID of database instance
or driver information can also be:
(description=(address=(host= host_name )(protocol= tcp)(port= port))(connect_data=(sid= database_SID)))
// or
Using an Oracle Data Source:
1) Create Oracle data source object of the oracle.jdbc.pool.OracleDataSource class
OracleDataSource myDataSource = new OracleDataSource();
2) Set the oracle data source object attributes using the set methods
– set___() and get____() methods
myDataSource.setServerName(“localhost”);
myDataSource.setDatabaseName(“ORCL”);
myDataSource.setDriverType(“oci”);
myDataSource.setNetworkProtocol(“tcp”);
myDataSource.setPortNumber(1521);
myDataSource.setUser(“scott”);
myDataSource.setPassword(“tiger”);
3) connect to the database via the object using the getConnection() method
Connection myConnection = myDataSource.getConnection();
// Note you could also pass a username and password as parameters of the method
4)    Creating a JDBC statement Object
Statement myStatement = myConncetion.createStatement();
– execute() method will generally perform any SQL statement
– executeQuery() if want to perform query
– executeUpdate() if want to perform INSERT UPDATE DELETE
5) Closing (should be in a finally block)
myStatement.close();
myConnection.close();

Retrieving Rows from Database
1) Create ResultSet object, populate it with results from query
ResultSet customerResultSet = myStatement.executeQuery(
“SELECT customer_id, first_name, last_name, dob, phone ” +
“FROM customers” +
“WHERE customer_id = 1”
);
2) read column values from the ResultSet using get() methods
while(customerResultSet.next()){
customerId = customerResultSet.getInt(“customer_id”);
firstName = customerResultSet.getString(“first_name”);
dob = customerResultSet.getDate(“dob”);
}
3) close ResultSet object
customerResultSet.close();

Adding Rows to the Database
– use executeUpdate() method
myStatement.executeUpdate(“INSERT INTO customers”+…);

Modifying Rows
myStatement.executeUpdate(“UPDATE customers SET first_name = ‘” + firstName + “‘ WHERE …);

Deleting Rows
myStatement.executeUpdate(“DELETE FROM …);

Handling Nulls
– wasNull() will return true if NULL…
– cast to object
productTypeId = (java.lang.Integer) productResultSet.getObject(“product_type_id”);

Controlling Database Transactions
myConncetion.setAutoCommit(false);
myConnection.commit();
myConnection.rollback();

Performing Data Definition Language Statements
– CREATE, ALTER, DROP, TRUNCATE, RENAME
myStatement.execute(“CREATE TABLE …”);

Handling exceptions
try{
…
} catch (SQLException e){
…
}

Prepared SQL statements
– alternative to memory intensive for loop to insert multiple objects
– question marks used to indicate variable positions
PreparedStatement myPreparedStatement = myConncetion.prepareStatement(
“INSERT INTO products ” +
“(product_id, product_type_id, name, description, price) VALUES (” +
“?, ?, ?, ?, ?”
“)”
);

for (int a = 0; a < productArray.length; a++){
myPrepStatement.setInt(1,productArray[a].productId);
myPrepStatement.setInt(2,productArray[a].product_type_id);
myPrepStatement.setString(3,productArray[a].name);
myPrepStatement.setNull(4,java.sql.Types.VARCHAR);
…
myPrepStatement.execute();
}

Oracle JDBC extensions
import oracle.sql.*;
import oracle.jdbc.*;

SQL Tuning
– use WHERE clause to limit row retrieval
– use table joins rather than multiple queries
– fully qualified column references: using table alias in queries and use alias for each column in query
– use CASE expression rather than multiple queries

XML
XMLELEMENT(“column_tag_name”, column1);
generate XML data from relational data