Oracle PL/SQL 中, 不使用 Cursor ,改用 SYS_CONNECT_BY_PATH, 將多筆資料組合成一筆資料

在 Oracle PL/SQL 中,

可以在 Select 使用 SYS_CONNECT_BY_PATH,

就可以將多筆資料組合成一筆資料,

而無需使用 Cursor 的方式組合.

 範例 1

select SYS_CONNECT_BY_PATH( object_name, ',' )
  from (select rownum r
             , a.object_name
          from (select object_name
                  from user_objects
                 where object_type = 'TABLE'
                   and object_name like 'XXF%'
                 order by object_name
               ) a             -- 先資料排序
       ) b                     -- 再取得排序後的 rownum
 start with b.r = 1            -- 跟 treeview 一樣, 指定開始位置
connect by prior b.r+1 = b.r;  -- 跟 treeview 一樣, 指定父子階層關係
 
結果 :
,XXFND_FND_USER
,XXFND_FND_USER,XXFND_HIERARCHY
,XXFND_FND_USER,XXFND_HIERARCHY,XXFND_SSO_MAP_USER

 範例 2

select max( ltrim( SYS_CONNECT_BY_PATH( object_name, ',' ), ',' ) )
  from (select rownum r
             , a.object_name
          from (select object_name
                  from user_objects
                 where object_type = 'TABLE'
                   and object_name like 'XXF%'
                 order by object_name
               ) a             -- 先資料排序
       ) b                     -- 再取得排序後的 rownum
 start with b.r = 1            -- 跟 treeview 一樣, 指定開始位置
connect by prior b.r+1 = b.r;  -- 跟 treeview 一樣, 指定父子階層關係
                               -- ltrim 是要去除最左邊的多餘的符號
                               -- max 是要取得最後組合的資料
 
結果 :
XXFND_FND_USER,XXFND_HIERARCHY,XXFND_SSO_MAP_USER

更進階的應用 : http://www.oracle.com/technology/oramag/code/tips2006/101606.html.

感謝 ECS Julia 小姐的技術指導.

Oracle PL/SQL: Treeview 樹狀查詢

Oracle Database 本身也有提供 Treeview 樹狀查詢,

 語法

select col1, col2, ...
  from table
[where condition ...]
 start with 要放在最上層的條件
connect by [prior] Child_Column = Parent_Column
[order [siblings] by sort ...]

p.s. 若 prior 有提供，則會查詢出本階與其所有下階資料。
p.s. 若 prior 無提供，則只查詢出本階資料。
p.s. 若 siblings 有提供，則會按父子階層排序，再按指定欄位排序。
p.s. 若 siblings 無提供，則會按指定欄位排序。
p.s. Child_Column = Parent_Column，視為 "Child 的父階是哪個"。
p.s. 查詢會先按 start with 展開 Treeview，再過濾 where 條件。

 範例

-- 建立 Temp Table
create table tom_tree(
  tree_id    number
, parent_id  number
, tree_name  varchar2(30)
);

-- 新增資料
insert into tom_tree values( 0, null, 'top' );
insert into tom_tree values( 1, 0, '1' );
insert into tom_tree values( 2, 0, '2' );
insert into tom_tree values( 3, 1, '1_1' );
insert into tom_tree values( 4, 1, '1_2' );
insert into tom_tree values( 5, 2, '2_1' );
insert into tom_tree values( 6, 2, '2_2' );
insert into tom_tree values( 7, 2, '2_3' );
commit;

-- Treeview 查詢
select level
     , tree_name
     , lpad( ' ', (Level - 1) * 3, ' ') || tree_name  -- 增加這個欄位的顯示, 會有層次感
  from tom_tree
 start with parent_id is null
 connect by prior tree_id = parent_id;

這種 Treeview 查詢, 應用於 BOM 表查詢, 是非常方便的, 希望對各位有所幫助.

昭佑.天翔

Oracle PL/SQL 取得某個月份的每一天日期 (某個區間的每一天日期)

若資料的日期只有 2010/12/01, 2010/12/05,

但在資料呈現上, 卻要顯示 2010/12/01, 2010/12/02, 2010/12/03, ... 2010/12/31,

在 Oracle PL/SQL 查詢語法中, 要如何實現,

範例, 如下 :

Oracle DB 透過 DB Link 連線其他資料庫

在 Oracle Database 中,

若要連接其他資料庫, 進行查詢, 維護, 執行等處理,

可以透過 DB Link,

相關的語法與注意事項, 參考如下 :

 建立方式 1: DB Server 需先設定好 Host_Name

create [public] database link <Gateway_name> connect to <User_name> identified by <Password> Using '<Host_name>';
 
-- 其中 Host Name 需設定在 Database Server 端的 tnsnames.ora 檔案中.

 建立方式 2: DB Server 無需設定好 Host_Name

create [public] database link <Gateway_name> connect to <User_name> identified by <Password> Using '(DESCRIPTION =
  (ADDRESS_LIST =
      (ADDRESS =
        (COMMUNITY = tcp.world)
        (PROTOCOL = TCP)
        (Host = <Remote_DB_IP_Address>)
        (Port = <Remote_DB_Port>)
      )
  )
  (CONNECT_DATA = (SID = <Remote_DB_SID>)
  )
)';

 執行 SQL

Select * from <Table_Name>@<Gateway_Name>;

 執行 Package

<Package_Name>.<Procedure_Name>@<Gateway_Name>( parameter1, parameter2, ... );

 注意事項

雖然用 DB Link, 開發人員不知道登入遠端 DB 的帳號密碼, 但是開發人員仍可處理給予權限的事情.
 
所以, 不要讓 DB Link 連上遠端 DB 重要的帳號.

昭佑.天翔

取得某個 USER 的所有 Supervisor

在 Oracle ERP 中, 如何取得某個 User, 他的 "所有 Employee Supervisor" 是誰, 這些 Supervisor 對應的 User Name 是什麼 ?

可以利用 Oracle Treeview 語法得知.

語法, 如下 :

 語法

SELECT FU.USER_NAME
  FROM FND_USER FU
     , (SELECT SUPERVISOR_ID
          FROM PER_ALL_ASSIGNMENTS_F
         WHERE SUPERVISOR_ID IS NOT NULL
         START WITH PERSON_ID = (SELECT EMPLOYEE_ID
                                   FROM FND_USER
                                  WHERE USER_NAME = UPPER('&USER_NAME')
                                )
        CONNECT BY PRIOR SUPERVISOR_ID = PERSON_ID
       ) EMP
 WHERE FU.EMPLOYEE_ID = EMP.SUPERVISOR_ID;

Oracle PL/SQL 取得某個區間的每一個月份 (Every Month)

若起訖月份為 201010 ~ 201103,

但在資料呈現上, 卻要顯示 2010/10, 2010/11, 2010/12, 2011/01, 2011/02, 2011/03,

在 Oracle PL/SQL 查詢語法中, 可以透過 ADD_MONTHS 與 MONTHS_BETWEEN 搭配 CONNECT BY 來實現,

參考範例, 如下 :

Oracle PL/SQL 取得某個區間的每一天日期 (Every Day)

若資料的日期只有 2010/12/01, 2010/12/05,

但在資料呈現上, 卻要顯示 2010/12/01, 2010/12/02, 2010/12/03, ... 2010/12/31,

在 Oracle PL/SQL 查詢語法中, 可以透過 TO_DATE 與 LAST_DAY 搭配 CONNECT BY 來實現,

參考範例, 如下 :

Oracle Database Create or Modify User (Owner, Schema)

在 Oracle Database 中建立一個新的 DB Schma,

可以參考下面的基本語法 :

 語法

-- 建立 User 
Create User <UserName> IDENTIFIED BY <Passsword>;

-- 修改 User 
Alter User <UserName> IDENTIFIED BY <Passsword>;

-- 讓 User 具有 "連接 DB" 的權限
Grant CONNECT to <UserName>;

-- 讓 User 具有 "對本身 Object 的 DML、DDL" 權限
Grant RESOURCE to <UserName>;

-- 讓 User 具有 "存取 Tablespace" 的權限
Grant UNLIMITED TABLESPACE to <UserName>;

注意: 在 Oracle 12c 資料庫中 create user 若有問題, 請參考這裡: 點選此處

昭佑.天翔