http://people.inf.elte.hu/sila/DB1Pract/Oracle_PLSQL.pdf
Reference: https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_xmlgen.htm#i1012053
Get XML format
Example
SQL: SELECT ‘1’ AS ONE FROM DUAL;
Result:
ONE
—
1
SQL: SELECT DBMS_XMLGEN.GETXML(‘select “1″ as one from dual’) FROM DUAL;
Result:
DBMS_XMLGEN.GETXML(‘SELECT"1″ASONEFROMDUAL’)
————————————————————————–
<?xml version="1.0″?>
<ROWSET>
<ROW>
<ONE>1</ONE>
</ROW>
</ROWSET>
For a create table SQL statement, it would contain the information of:
Example
From: https://docs.oracle.com/cd/B28359_01/server.111/b28310/tables003.htm
CREATE TABLE hr.admin_emp (
empno NUMBER(5) PRIMARY KEY,
ename VARCHAR2(15) NOT NULL,
ssn NUMBER(9) ENCRYPT,
job VARCHAR2(10),
mgr NUMBER(5),
hiredate DATE DEFAULT (sysdate),
photo BLOB,
sal NUMBER(7,2),
hrly_rate NUMBER(7,2) GENERATED ALWAYS AS (sal/2080),
comm NUMBER(7,2),
deptno NUMBER(3) NOT NULL
CONSTRAINT admin_dept_fkey REFERENCES hr.departments
(department_id))
TABLESPACE admin_tbs
STORAGE ( INITIAL 50K);
COMMENT ON TABLE hr.admin_emp IS 'Enhanced employee table';
From:
https://ibatis.apache.org/docs/dotnet/datamapper/ch03s09.html
http://www.mybatis.org/mybatis-3/dynamic-sql.html
Oracle groups related type of information into logical structure that is called schema.
| Schema Object | Description |
| Table |
|
| Index | For performance of retrieval |
| View | For presentation |
| Sequence | |
| Synonym | For security and convenience |
| Stored Procedure |
|
| Trigger |
The Join in SQL is used to combine rows from two or more tables based on common fields. There are various type of join (inner, left, right …). The common fields are always specified in on clause statement. The “on" clause is often messed up with “where" clause. Also, the are various name of same join type which make us confused.
Syntax: Select * from table1 inner [optional] join table2 on table1.key = table2.key. In Oracle, Inner is optional for Inner Join.
Syntax: Select * from table1 left join table2 on table1.key = table2.key In Oracle, Outer is optional for Left/Right Join
| A LEFT JOIN B | A LEFT OUTER JOIN B |
| A RIGHT JOIN B | A RIGHT OUTER JOIN B |
| A FULL JOIN B | A FULL OUTER JOIN B |
| A INNER JOIN B | A JOIN B |
On Clause is used to filter join table row
Where Clause is used to filter result row
Select table1.id as Table1ID, table1.name as Table1Name, table2.id as Table2ID, table2.name as Table2Name from table1 join table2 on table1.id = table.id
| Table1ID | Table1Name | Table2ID | Table2Name |
|---|---|---|---|
| 1 | Table 1 Item 1 | 1 | Table 2 Item2 |
| 2 | Table 1 Item 2 | 2 | Table 2 Item 3 |
| 3 | Table 1 Item 3 | 3 | Table 2 Item 3 |
Select table1.id as Table1ID, table1.name as Table1Name, table2.id as Table2ID, table2.name as Table2Name from table1 left join table2 on table1.id = table.id
| Table1ID | Table1Name | Table2ID | Table2Name |
|---|---|---|---|
| 1 | Table 1 Item 1 | 1 | Table 2 Item 2 |
| 2 | Table 1 Item 2 | 2 | Table 2 Item 3 |
| 3 | Table 1 Item 3 | 3 | Table 2 Item 3 |
Select table1.id as Table1ID, table1.name as Table1Name, table2.id as Table2ID, table2.name as Table2Name from table1 left join table2 on table1.id = table.id and table1.id=1
| Table1ID | Table1Name | Table2ID | Table2Name |
|---|---|---|---|
| 1 | Table 1 Item 1 | 1 | Table 2 Item 2 |
| 2 | Table 1 Item 2 | (null) | (null) |
| 3 | Table 1 Item 3 | (null) | (null) |
Select table1.id as Table1ID, table1.name as Table1Name, table2.id as Table2ID, table2.name as Table2Name from table1 left join table2 on table1.id = table.id where table1.id=1
| Table1ID | Table1Name | Table2ID | Table2Name |
|---|---|---|---|
| 1 | Table1 Item 1 | 1 | Table 2 Item 2 |
In Short, The On Clause and Where Clause in Inner Join are equivalent. But for Left join, they are different.
Reference:
http://www.w3schools.com/sql/sql_join.asp
http://stackoverflow.com/questions/565620/difference-between-join-and-inner-join
http://stackoverflow.com/questions/406294/left-join-and-left-outer-join-in-sql-server
SQL can be different among databases such as oracle, mssql.
The difference could be found in: http://www.bristle.com/Tips/SQL.htm#differences_between_oracle_and_ms_sql_server
In this article, oracle and regular expression in Notepad++ could be used.
Insert
Syntax: INSERT INTO <TABLE> <OPTIONAL: COLUMN LIST> VALUES ( <VALUE LIST> )
Regular Expression: INSERT INTO (.*?)VALUES\((.*?)\)
Update
Syntax: UPDATE TABLE <TABLE> SET <UPDATE STATEMENT>
Create Table
Syntax: Create Table <Table> (<Column List>)
Drop Table
Syntax: Drop Table <Table>
Add Constraint
Syntax: Alter Table <Table> add constraint <Constraint> (<Column>) Reference <Table> (Column)
Drop Constraint
Syntax: Alter Table <Table> drop constraint <Constraint>
Reference:
Cursor can be viewed as a pointer to one row in a set of rows.
SET SERVEROUTPUT ON;
DECLARE
CURSOR USER_TABLES_CURSOR IS SELECT TABLE_NAME FORM USER_TABLES;
TABLE_NAME VARAHCR(100);
BEGIN
OPEN USER_TABLES_CURSOR;
LOOP
FETCH USER_TABLES_CURSOR INTO TABLE_NAME;
EXIT WHEN USER_TABLES_CURSOR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(TABLE_NAME);
END LOOP;
CLOSE USER_TABLES_CURSOR;
END;
/
SET SERVEROUTPUT ON;
DECLARE
TYPE USER_TABLES_CUR_TYP IS REF CURSOR;
USER_TABLES_CURSOR USER_TABLES_CUR_TYP;
TABLE_NAME VARCHAR2(100);
BEGIN
OPEN USER_TABLES_CURSOR FOR 'SELECT TABLE_NAME FROM USER_TABLES WHERE ROWNUM = 1';
LOOP
FETCH USER_TABLES_CURSOR INTO TABLE_NAME;
EXIT WHEN USER_TABLES_CURSOR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(TABLE_NAME);
END LOOP;
CLOSE USER_TABLES_CURSOR;
END;
/
SET SERVEROUTPUT ON;
DECLARE
CURSOR USER_TABLES_CURSOR IS
SELECT TABLE_NAME FORM TEST_TABLES FOR UPDATE OF TABLE_NAME;
TABLE_NAME VARAHCR(100);
BEGIN
OPEN TEST_TABLES_CURSOR;
LOOP
FETCH TEST_TABLES_CURSOR INTO TABLE_NAME;
EXIT WHEN USER_TABLES_CURSOR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(TABLE_NAME);
UPDATE TEST_TABLES
SET TABLE_NAME = 'TEST 1'
WHERE CURRENT OF TEST_TABLES_CURSOR;
END LOOP;
CLOSE TEST_TABLES_CURSOR;
END;
/
Tech Office 