set serveroutput on;
CREATE OR REPLACE PROCEDURE PROC1(invoicenr IN NUMBER, amnt OUT NUMBER)
AS BEGIN
SELECT AMOUNT INTO amnt FROM INVOICE WHERE INVOICE_NR = invoicenr;
END;
/*
And then call the function as it is:
DECLARE
amount NUMBER;
BEGIN
PROC1(1000001, amount);
dbms_output.put_line(amount);
END;
*/
sqlplus
SQL> ID@SERVICE_NAME/PASSWORD
SEQUENCE:CREATE OR REPLACE PROCEDURE PROC1(invoicenr IN NUMBER, amnt OUT NUMBER)
AS BEGIN
SELECT AMOUNT INTO amnt FROM INVOICE WHERE INVOICE_NR = invoicenr;
END;
/*
And then call the function as it is:
DECLARE
amount NUMBER;
BEGIN
PROC1(1000001, amount);
dbms_output.put_line(amount);
END;
*/
sqlplus
SQL> ID@SERVICE_NAME/PASSWORD
DROP SEQUENCE BTC.BTC_INVOICE_TEMP_S;
CREATE SEQUENCE BTC.BTC_INVOICE_TEMP_S
START WITH 69936
MAXVALUE 999999999999999999999999999
MINVALUE 1
NOCYCLE
CACHE 20
NOORDER;
select BTC_INVOICE_TEMP_S.nextval REPORT_ID from dual
------------------------------------------------------------------------------------------------------
PL SQL:
SELECT TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') FROM DUAL --2015/07/15 16:40:03
SELECT TO_DATE('2015/03/17 18:25:33','YYYY/MM/DD HH24:MI:SS') FROM DUAL --2015/3/17 下午 06:25:33
SELECT NVL(NULL,'0') FROM DUAL -- 0
SELECT DECODE('2','1','A','2','B','3','C') FROM DUAL --B
SELECT LENGTH('ABCCD') FROM DUAL -- 5
SELECT 'ABC' || 'DEF' FROM DUAL --ABCDEF
SELECT CONCAT('ABC','DEF') FROM DUAL --ABCDEF
SELECT INSTR('37381M8382','M') FROM DUAL --6 IndexOf
SELECT LOWER('cSdsaAOR') FROM DUAL --csdsaaor
SELECT UPPER('cSdsaAOR') FROM DUAL --CSDSAAOR
SELECT LPAD('78384',10,'0') FROM DUAL --0000078384
SELECT RPAD('78384',10,'0') FROM DUAL --7838400000
SELECT LTRIM(' SEDFRCMKD ') FROM DUAL --'SEDFRCMKD '
SELECT RTRIM(' SEDFRCMKD ') FROM DUAL --' SEDFRCMKD'
SELECT TRIM(' SEDFRCMKD ') FROM DUAL --'SEDFRCMKD'
SELECT REPLACE('SLICOS9V','ICO','X') FROM DUAL -- SLXS9V
SELECT SUBSTR('DCIOWVLK',1,3) FROM DUAL --DCI
SELECT TO_CHAR(12345,'0000000') FROM DUAL --0012345
SELECT TO_CHAR(12345,'$9999999') FROM DUAL -- $12345
SELECT TO_CHAR(12345,'S9999999') FROM DUAL -- +12345
SELECT TO_CHAR(12345.567,'999,999,999.99') FROM DUAL -- 12,345.57
SELECT ABS(-872389.42) FROM DUAL --872389.42
SELECT MOD(9 , 4 ) FROM DUAL --1
SELECT ROUND(9.567 , 2 ) FROM DUAL --9.57
SELECT GREATEST (89, 103, 68 ) FROM DUAL -- 103
SELECT LEAST(89, 103, 68 ) FROM DUAL -- 68
SELECT TO_NUMBER(COLUMN1) + 1 FROM TABLE1 --轉為數值
------------------------------------------------------------------------------------------------------
SELECT CASE:
(1)
SELECT CASE COLUMN1
WHEN A THEN AAA
WHEN B THEN BBB
ELSE CCC
END
AS NewColumn1
FROM TABLE1
(2)
SELECT CASE
WHEN COLUMN1 > 50 OR COLUMN1 < 70 THEN AAA
WHEN COLUMN1 < 25 AND COLUMN2 > 0 THEN BBB
ELSE CCC
END
AS NewColumn1
FROM TABLE1
------------------------------------------------------------------------------------------------------
tnsping:
tnsping Global Database Name
tnsping SERVICE_NAME
tnsping SID
------------------------------------------------------------------------------------------------------
系統指令:
DESC TABLENAME
SELECT * FROM V$VERSION
SELECT * FROM V$DATABASE;
SELECT * FROM ALL_TAB_COLUMNS WHERE COLUMN_NAME = 'XXX'
SELECT * FROM ALL_OBJECTS WHERE OBJECT_NAME = 'XXX'
------------------------------------------------------------------------------------------------------
--查詢被鎖住的帳號
SELECT * FROM DBA_USERS where ACCOUNT_STATUS = 'LOCKED'
SELECT * FROM DBA_USERS where USERNAME = 'BTC'
--解鎖
ALTER USER BTC ACCOUNT UNLOCK
------------------------------------------------------------------------------------------------------
顯示民國 年月日:
SELECT TO_CHAR(ADD_MONTHS(SYSDATE,-12*1911),'YYY') FROM DUAL 104
SELECT TO_CHAR(ADD_MONTHS(SYSDATE,-12*1911),'MM') FROM DUAL 09
SELECT TO_CHAR(ADD_MONTHS(SYSDATE,-12*1911),'DD') FROM DUAL 16
======================================================================================================
ROW_NUMBER() 找出11~20筆資料
select * from(
SELECT ROW_NUMBER() OVER(ORDER BY DELIVERY_ID) AS rn ,delivery_id,header_id,delivery_status,hmg_flag
FROM BTC_DELIVERY_ALL )a
where a.rn > 10 and a.rn <= 20
rn,delivery_id,header_id,delivery_status,hmg_flag
11 100023 33 3 N
12 100024 35 3 N
13 100024 36 3 N
------------------------------------------------------------------------------------------------------
ROWNUM 找出11~20筆資料
select * from (
select rownum rn,delivery_id,header_id,delivery_status,hmg_flag from BTC_DELIVERY_ALL
where rownum <= 20
) a
where a.rn > 10
rn,delivery_id,header_id,delivery_status,hmg_flag
11 100022 30 3 N
12 100023 32 3 N
13 100023 33 3 N
------------------------------------------------------------------------------------------------------
ROWNUM 資料排序後,找出11~20筆資料
select * from (
select rownum rn , delivery_id,header_id,delivery_status,hmg_flag from (
select delivery_id,header_id,delivery_status,hmg_flag from BTC_DELIVERY_ALL
order by header_id ) a
)b
where b.rn <= 20 and b.rn > 10
rn,delivery_id,header_id,delivery_status,hmg_flag
11 100023 33 3 N
12 100024 35 3 N
13 100024 36 3 N
------------------------------------------------------------------------------------------------------
row_number() partition 各分組自行排序,取每組第一名
select * from (
select row_number() over(partition by delivery_id order by delivery_id) rn , delivery_id,header_id,delivery_status,hmg_flag from (
select delivery_id,header_id,delivery_status,hmg_flag from BTC_DELIVERY_ALL
order by header_id ) a
)b
where b.rn = 1
rn,delivery_id,header_id,delivery_status,hmg_flag
1 100001 2 3 N
1 100021 22 3 N
1 100022 30 3 N
1 100023 32 3 N
1 100024 35 3 N
------------------------------------------------------------------------------------------------------
rank 對群組做排序,跳號
select * from (
select rank() over(order by delivery_id) rn , delivery_id,header_id,delivery_status,hmg_flag from (
select delivery_id,header_id,delivery_status,hmg_flag from BTC_DELIVERY_ALL
order by header_id ) a
)b
rn,delivery_id,header_id,delivery_status,hmg_flag
1 100001 2 3 N
2 100021 23 3 N
2 100021 24 3 N
2 100021 25 3 N
2 100021 26 4 N
2 100021 22 3 N
2 100021 27 4 N
2 100021 28 4 N
9 100022 30 3 N
10 100023 32 3 N
10 100023 33 3 N
12 100024 35 3 N
12 100024 36 3 N
------------------------------------------------------------------------------------------------------
dense_rank 對群組做排序,不跳號
select * from (
select dense_rank() over(order by delivery_id) rn , delivery_id,header_id,delivery_status,hmg_flag from (
select delivery_id,header_id,delivery_status,hmg_flag from BTC_DELIVERY_ALL
order by header_id ) a
)b
rn,delivery_id,header_id,delivery_status,hmg_flag
1 100001 2 3 N
2 100021 23 3 N
2 100021 24 3 N
2 100021 25 3 N
2 100021 26 4 N
2 100021 22 3 N
2 100021 27 4 N
2 100021 28 4 N
3 100022 30 3 N
4 100023 32 3 N
4 100023 33 3 N
5 100024 35 3 N
5 100024 36 3 N
======================================================================================================
刪除重複的資料
delete from
CPHBS_ORDER_HEADER
where rowid in
(select rowid from
(select
rowid,
row_number()
over
(partition by ORDER_HEADER_ID order by ORDER_HEADER_ID) dup
from CPHBS_ORDER_HEADER)
where dup > 1);
------------------------------------------------------------------------------------------------------
--FIND COLUMN COMMENT.
select tc.column_name
, tc.nullable
, tc.data_type || case when tc.data_type = 'NUMBER' and tc.data_precision is not null then '(' || tc.data_precision || ',' || tc.data_scale || ')'
when tc.data_type like '%CHAR%' then '(' || tc.data_length || ')'
else null
end type
, cc.comments
from user_col_comments cc
join user_tab_columns tc on cc.column_name = tc.column_name
and cc.table_name = tc.table_name
where cc.table_name = upper('CPH_INV_ITEMS_MASTER')
and CC.COLUMN_NAME = upper('EAN') ;
------------------------------------------------------------------------------------------------------
查不出資料或中文亂碼
設定Oracle Client 的 NLS_LANG, 必須符合 DB Server 的設定
select userenv('language') from dual
System.Environment.SetEnvironmentVariable("NLS_LANG", "AMERICAN_AMERICA.ZHT16BIG5")