PL/SQL Control Statements 控制語法

From: Polin Wei

PL/SQL 在程式語法中對於狀態的判別有兩個語法可作判別：IF 與 CASE 語法 (statements)：

IF statements 語法使用方法有下列幾個方式：

IF-THEN Statement

IF condition THEN
sequence_of_statements
END IF;

IF-THEN-ELSE Statement

IF condition THEN
sequence_of_statements1
ELSE
sequence_of_statements2
END IF;

IF-THEN-ELSIF Statement

IF condition1 THEN
sequence_of_statements1
ELSIF condition2 THEN
sequence_of_statements2
ELSE
sequence_of_statements3
END IF;

IF-THEN-ELSE 的例子

繼續閱讀...

PL/SQL Data Type

From: Polin Wei

PL/SQL 的資料型態 ( Data Type ) 可以分成兩大類

• Predefined Datatypes 預設資料型態
• User-Defined Subtypes 使用者自定資料型態
• 進階資料型態宣告
  －　%TYPE
  －　%ROWTYPE
  

Predefined Datatypes 預設資料型態的種類，大致整理成下圖：

繼續閱讀...

Oracle To_Date Function 由文字格式轉成日期格式

From: Polin Wei

to_date function ：由文字格式轉換成日期格式

在使用此函式時，可以下列 SQL 來查詢目前的日期格式為何：

SELECT parameter, value FROM v$nls_parameters
where parameter='NLS_TIMESTAMP_TZ_FORMAT';

select to_char(sysdate,'DD-MON-RR') from dual;

select to_date('06-9月-10') from dual;

to_date function 語法如下：

to_date( string1, [ format_mask ], [ nls_language ] )

* string1 ：文字格式的日期
* format_mask ：選用，將 string1 轉換成此格式的日期
* nls_language is optional： This is the nls language used to convert string1 to a date.

繼續詳細閱讀

[教學]SQL 語法 JOIN

　　今天寫程式時，為了查詢 RDB 資料庫中三個 Table 裡相關的值，本來是用一般 SQL 語法，但為了求所有部門內的人員，所以只好重新溫習一下 SQL 語法中的 Left Join。 為了讓自己不要忘記，一方面也可以讓大家瞭解一下；以下例的實例來說明，應該比較容易瞭解。Table 範例如下：

table : Users

userId	named	companyNo	deptNo
0001	Scott	c01	d01
0002	Tiger	c01	d02
0003	Jacky	c01	d02
0004	Polin	c01	d03

SQL:
create table users ( userId char(4), name varchar2(16), companyNo char(3), DeptNo char(3) );
insert into users values( '0001' , 'Scott', 'c01' , 'd01' );
insert into users values( '0002' , 'Tiger', 'c01' , 'd02' );
insert into users values( '0003' , 'Jacky' , 'c01' , 'd02' );
insert into users values( '0004' , 'Polin', 'c01' , 'd03' );

---

table : Companys

companyNo	name
c01	ABC Corp.
c02	XYZ Corp.

SQL:
create table Company ( companyNo char(3), name varchar2(16) );
insert into Company values( 'c01' , 'ABC Corp.' );
insert into Company values( 'c02' , 'XYZ Corp.' );

---

table : Depts

companyNo	DeptNo	name
c01	d01	Account
c01	d02	Sales
c01	d03	MIS
c01	d04	HR

SQL:
create table Depts ( companyNo char(3), deptNo char(3), name varchar2(16) );
insert into Depts values( 'c01' ,'d01' , 'Account' );
insert into Depts values( 'c01' ,'d02' , 'Sales' );
insert into Depts values( 'c01' ,'d03' , 'MIS' );
insert into Depts values( 'c01' ,'d04' , 'HR' );

---

需求一： User 屬於那一個公司，那一個部門的 SQL:

SELECT u.*, d.name as deptName, c.name as companyName
FROM users u , depts d, company c
WHERE 1=1
AND u.deptNo = d.deptNo
AND u.companyNo = c.companyNo

結果：

USERID	NAME	COMPANYNO	DEPTNO	DEPTNAME	COMPANYNAME
0001	Scott	c01	d01	Account	ABC Corp.
0003	Jacky	c01	d02	Sales	ABC Corp.
0002	Tiger	c01	d02	Sales	ABC Corp.
0004	Polin	c01	d03	MIS	ABC Corp.

---

需求二：列出所有部門的人員，部門若沒有人員也要列出。

SELECT d.*, u.name as userName , c.name as companyName
FROM depts d
LEFT JOIN users u ON d.deptNo = u.deptNo
INNER JOIN company c ON d.companyNO = c.companyNo

結果：

COMPANYNO	DEPTNO	NAME	USERNAME	COMPANYNAME
c01	d01	Account	Scott	ABC Corp.
c01	d02	Sales	Jacky	ABC Corp.
c01	d02	Sales	Tiger	ABC Corp.
c01	d03	MIS	Polin	ABC Corp.
c01	d04	HR	(null)	ABC Corp.

PS: 若仍用需求一的方法，則無法顯示 d04 HR 部門

SQL:
SELECT d.*, u.name as userName , c.name as companyName
FROM depts d, users u, company c
WHERE 1=1
AND d.deptno = u.deptno
AND d.companyno = c.companyno
結果:

COMPANYNO	DEPTNO	NAME	USERNAME	COMPANYNAME
c01	d01	Account	Scott	ABC Corp.
c01	d02	Sales	Jacky	ABC Corp.
c01	d02	Sales	Tiger	ABC Corp.
c01	d03	MIS	Polin	ABC Corp.

---

整理一下這三個 JOIN 的特色如下：

INNER JOIN - 兩邊的資料表擁有相同的優先權, 要兩邊都有的資料才會被包含在新的資料表.

RIGHT JOIN - 右邊的資料表擁有優先權, 右邊所有的資料都會被包含, 而左邊只有符合的資料才會被包含.

LEFT JOIN - 左邊的資料表擁有優先權, 左邊所有的資料都會被包含, 而右邊只有符合的資料才會被包含.