Tips for using Oracle (for beginner)

Useful SQL statements

get all the tables

SELECT * FROM TAB ORDER BY TNAME

get all the columns 

select * from col where tname = '<table name>' ORDER BY colno

Return Top n records (Select Top 1 * in MS SQL)

SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number;

Concatenation Operator 

• ||

IF THEN ELSE ELSIF END IF example

     IF (...) THEN
          ...;
     ELSIF (...) THEN
          ...;
     ELSE
          ...;
     END IF;

GetDate() (in MS SQL)

•     sysdate

Table DUAL

A dummy table with a single record used for selecting when you're not actually interested in the data, but instead want the results of some system function in a select statement:

•     select sysdate from dual;

Sequence

Open
to see the defination (Last number too)
Get next value
<sequence_name>.NEXTVAL;

INSERT INTO <table>
(<col1>, <col2>)
VALUES
(<seq_name>.NEXTVAL, 'xxx');

you can't use CURRVAL until you have used NEXTVAL at least once in your session.

Data Types

No boolean data type
varchar2 (2K, Oracle8 4K)
number(5,2) - 999.99

Terminology

Terminology	Oracle	SQL Server
	schema	database
	service name	database name
	System ID (SID)	database name
Storage	block	page
Extent	user-defined	fixed at 8 pages
Storage management pages (SMP)	dictionary or local	local only
Metadata	data dictionary	SYS database
Recursive SQL	connect by clause	HierarchyID data type
Language	PL/SQL	T-SQL

For Loop and with / as a Cursor

FOR i IN 1..10
LOOP
...
END LOOP;

--------------------------------
FOR iRec IN (SELECT * FROM <table> WHERE <field> = <value>)LOOP
...
END LOOP;

--------------------------------
CURSOR <cursor name> IS
    <sql statement>

BEGIN
    FOR <item> IN <cursor name>
        LOOP
            ...
        END LOOP;
END;

--------------------------------
can pass a parameter:

CURSOR <cursor name> (<parameter> VARCHAR2) IS
    <sql statement>
BEGIN
    FOR <item> IN <cursor name>(<parameter>)
        LOOP
            ...
        END LOOP;
END;