PL/SQL Programming Concepts: Review

PL/SQL Run-Time Architecture

Image 699

Block Type

  • Procedure
  • Function
  • Anonymous

PL/SQL Block Structure

  • Declare
    • Variables, Cursors, User-defined exceptions
  • Begin
    • SQL Statement
    • PL/SQL Statement
  • Exception
    • Action to perform when exception occurs
  • End;

Type of Variables

  • PL/SQL Variables
    • Scalar
    • Reference
    • Large object (LOB)
    • Composite (Record, Collection)
  • Non-PL/SQL Variables
    • Bind variables

 

Reference:

http://people.inf.elte.hu/sila/DB1Pract/Oracle_PLSQL.pdf

 

 

 

 

Advanced PL/SQL

  • Oracle11g: Advanced PL/SQL
  • PL/SQL Programming Concepts: Review
  • Designing PL/SQL Code
  • Working with Collections
  • Using Advanced Interface Methods
  • Implementing Fine-Grained Access Control for VPD
  • Manipulating Large Objects
  • Administering SecureFile LOBs
  • Performance and Tuning
  • Improving Performance with Caching
  • Analyzing PL/SQL Code
  • Profiling and Tracing PL/SQL Code
  • Safeguarding Your Code Against SQL Injection Attacks

Oracle function – DBMS_XMLGEN.GETXML

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>

Create Table SQL Statement Information

For a create table SQL statement, it would contain the information of:

  1. Table Name
  2. Column Name
  3. Column Data Type
  4. Column Data Length
  5. Column Data Length Unit
  6. Not Null
  7. Encrypted
  8. Primary Key Constraint
  9. Default Value
  10. Generated Always
  11. Table Space
  12. Storage
  13. Comment

 

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

Create Table Statement Info

 

 

 

 

 

 

Oracle Database Overview

 

Relational Database Management System (RDBMS)

Schema

Oracle groups related type of information into logical structure that is called schema.

Schema Object Description
Table
  • Row: record
  • Column: field
Index For performance of retrieval
View For presentation
Sequence
Synonym For security and convenience
Stored Procedure
  • Procedure
  • Function
  • Package
Trigger

Reference

 

SQL – Join

Overview

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.

 

Inner Join

Syntax: Select * from table1 inner [optional] join table2 on table1.key = table2.key. 
In Oracle, Inner is optional for Inner Join.

Left Join

Syntax: Select * from table1 left join table2 on table1.key = table2.key
In Oracle, Outer is optional for Left/Right Join

Equivalent Syntax

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

 

The Difference Between Where Clause and On Clause

On Clause is used to filter join table row

Where Clause is used to filter result row

Example

Inner Join

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

Left Join

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

Left Join On <Con1> And <Con2>

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)

Left Join On <Con1> Where <Con2>

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

Conclusion

In Short, The On Clause and Where Clause in Inner Join are equivalent. But for Left join, they are different.

 

Visual Representation of Various Join

From: http://stackoverflow.com/questions/15891863/what-is-the-difference-between-join-keyword-and-inner-join-keyword-in-oracle-sql

enter image description here

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

 

Oracle SQL and Regular Expression

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:

http://www.w3schools.com/sql/default.asp