Oracle 基本語法記錄

Posted by Adam on August 24, 2022
```sql -- Oracle SELECT 1 FROM DUAL; -- Test Database connect -- 取得 sequence 的下一個值 SELECT sometable_seq.NEXTVAL FROM DUAL; -- 取得 sequence 的當前值 SELECT sometable_seq.CURRVAL FROM DUAL; -- 取得時間內的資料 SELECT * FROM products WHERE SYSDATE BETWEEN start_time AND end_time; SELECT * FROM products WHERE SYSDATE BETWEEN to_date('2014/10/30 11:00:00', 'yyyy/mm/dd hh24:mi:ss') AND to_date('2014/10/30 15:00:00', 'yyyy/mm/dd hh24:mi:ss'); -- 取得當月第一日 SELECT trunc(SYSDATE, 'month') first_day FROM DUAL; -- 取得當月最後一日 SELECT last_day(SYSDATE) FROM DUAL; -- 取得指定日期的月份最後一日 SELECT last_day(date '2022-10-13') FROM DUAL; -- 修改欄位類型 ALTER TABLE table_name MODIFY column_name DECIMAL(10, 2); -- 刪除指定欄位 ALTER TABLE table_name DROP COLUMN column_name; -- 新增指定欄位 ALTER TABLE employees ADD department VARCHAR(50); -- TODO 新增一個 sequence ``` ### [Insert Condition](https://stackoverflow.com/questions/41849762/how-to-insert-conditionally-in-oracle) ```sql -- INSERT 條件判斷 INSERT INTO BAR (NAME, AGE) SELECT 'JOE', 50 FROM DUAL WHERE (SELECT COUNT(*) FROM FOO WHERE NAME = 'JOE') = 0 ``` ### [Insert sequence nextval](https://livesql.oracle.com/apex/livesql/file/content_GL1PAE2CYMKDK32A42NZIGJVR.html) ```sql INSERT INTO author VALUES (author_seq.nextval, 'Stephen', 'King', 'stephen@king.com', NULL) ``` ### [Oracle 分頁查詢](https://matthung0807.blogspot.com/2017/09/oracle.html) ```sql SELECT * FROM ( SELECT rownum rn, t.*, FROM ( SELECT * FROM table_name ORDER BY column_name -- 原本的SQL ) t WHERE rn < ((頁數 * 每頁筆數) + 1) -- 每頁的結束筆數 ) WHERE rn >= (((頁數 - 1) * 每頁筆數) + 1) -- 每頁的開始筆數 ------------------------------------------------------------------- SELECT * FROM ( SELECT rownum rn, t.*, FROM ( SELECT * FROM table_name ORDER BY column_name -- 原本的SQL ) t ) WHERE rn BETWEEN (((頁數 - 1) * 每頁筆數) + 1) AND (頁數 * 每頁筆數); ------------------------------------------------------------------- SELECT * FROM table_name ORDER BY column_name OFFSET (頁數 * 每頁筆數) ROWS FETCH NEXT 每頁筆數 ROWS ONLY; ``` ### [深入淺出 SQL 的子查詢 Subquery 各項常用技巧](https://chriskang028.medium.com/%E6%B7%B1%E5%85%A5%E6%B7%BA%E5%87%BA-sql-%E7%9A%84%E5%AD%90%E6%9F%A5%E8%A9%A2-subquery-%E5%90%84%E9%A0%85%E5%B8%B8%E7%94%A8%E6%8A%80%E5%B7%A7-8887731717f3) ```sql SELECT * FROM Orders o where o.CustomerID in (select CustomerID from Customers where city = 'London') order by o.CustomerID desc ----- SELECT o.OrderID, o.OrderDate, c.CustomerID, c.CustomerName, o.EmployeeID FROM Orders o INNER JOIN (SELECT * FROM Customers where city = 'London') c ON o.CustomerID=c.CustomerID order by c.CustomerID desc ``` ### 多對多中間表範例 ```sql CREATE TABLE table1_table2 ( table1_id NUMBER(10) NOT NULL, table2_id NUMBER(10) NOT NULL, CONSTRAINT table1_fk FOREIGN KEY (table1_id) REFERENCES table1 (id), CONSTRAINT table2_fk FOREIGN KEY (table2_id) REFERENCES table2 (id), CONSTRAINT table1_table2_pk PRIMARY KEY (table1_id, table2_id) ); ``` ### Output ```sql BEGIN -- 啟用 DBMS_OUTPUT DBMS_OUTPUT.ENABLE; -- 輸出訊息 DBMS_OUTPUT.PUT_LINE('Hello, world!'); DBMS_OUTPUT.PUT_LINE('This is a log message.'); -- 停用 DBMS_OUTPUT DBMS_OUTPUT.DISABLE; END; ``` ### 亂數 Random ```sql SELECT DBMS_RANDOM.VALUE AS random_number FROM DUAL; -- 0.59846546 SELECT FLOOR(DBMS_RANDOM.VALUE(1, 100)) AS random_number FROM DUAL; -- 58 INSERT INTO your_table (column1, column2, random_column) VALUES ('value1', 'value2', FLOOR(DBMS_RANDOM.VALUE(1, 100))); ``` ### Shuffle ```sql -- 對範圍為 1 到 1000 的數字進行洗牌 SELECT num FROM ( SELECT LEVEL AS num FROM DUAL CONNECT BY LEVEL <= 1000 ) t ORDER BY DBMS_RANDOM.VALUE; -- 給于每個亂數一個 id 值 SELECT ROWNUM AS id, shuffled_numbers.num FROM ( SELECT num FROM ( SELECT LEVEL AS num FROM DUAL CONNECT BY LEVEL <= 1000 ) ORDER BY DBMS_RANDOM.VALUE ) shuffled_numbers; ``` ### Loop ```sql DECLARE counter NUMBER := 1; -- 計數器初始值為1 BEGIN LOOP -- 在這裡放置你想要執行的程式碼 -- 檢查終止條件,當計數器達到特定值時終止迴圈 EXIT WHEN counter > 10; -- 執行其他操作,例如增加計數器值 counter := counter + 1; -- 在迴圈中休眠一段時間(可選) -- 使用 DBMS_LOCK.SLEEP 函數來進行休眠,單位為秒 DBMS_LOCK.SLEEP(1); END LOOP; END; ``` ### FOR ```sql BEGIN -- 在這裡放置你想要執行的程式碼 -- 使用 FOR 迴圈迭代 FOR i IN 1..10 LOOP -- 在迴圈內部執行的程式碼 -- 可以使用變數 i 來表示迭代的當前值 -- 執行其他操作,根據需求修改迴圈內的程式碼 -- 在迴圈中休眠一段時間(可選) -- 使用 DBMS_LOCK.SLEEP 函數進行休眠,單位為秒 DBMS_LOCK.SLEEP(1); END LOOP; END; BEGIN -- 遍歷表格中的數據 FOR row IN (SELECT * FROM your_table) LOOP -- 訪問每一行的列值 DBMS_OUTPUT.PUT_LINE('Column 1: ' || row.column1); DBMS_OUTPUT.PUT_LINE('Column 2: ' || row.column2); -- 可以繼續訪問其他列值 -- 做一些處理或操作 -- ... END LOOP; END; ``` ### DECLARE SELECT給值 ```sql DECLARE v_parent_id NUMBER; v_parent_name VARCHAR(255); BEGIN -- 選擇父節點 SELECT parent_id, parent_name INTO v_parent_id, v_parent_name FROM your_parent_table WHERE condition; -- 插入父節點到目標表 INSERT INTO your_target_table (parent_id, parent_name) VALUES (v_parent_id, v_parent_name); COMMIT; -- 提交事務 END; ``` ### JOIN ```sql SELECT c.customer_name, o.order_date FROM customers c JOIN orders o ON c.customer_id = o.customer_id; ``` 若 SQL 回覆有兩個外部的 key,需要透過兩個 JOIN 語句來將對應的內容顯示出來。下面是一個示例: ```sql SELECT A.key1, A.column1, B.key2, B.column2 FROM tableA A JOIN tableB B ON A.key1 = B.key1 JOIN tableC C ON A.key2 = C.key2; ``` 在這個例子中,我們有兩個外部 key 分別為 key1 和 key2,並且我們分別與 tableB 和 tableC 做 JOIN 以顯示對應的內容。透過這樣的方式,我們可以將兩個外部 key 對應的內容一起顯示出來。 ### Update JOIN ```sql UPDATE TableA SET TableA.column_name = TableB.column_name FROM TableA JOIN TableB ON TableA.join_column = TableB.join_column WHERE <condition>; ``` TableA 是要更新的表。 TableB 是要連接的表。 column_name 是要更新的目標欄位名稱。 join_column 是用於連接 TableA 和 TableB 的共同欄位。 <condition> 是選擇性的條件,用於限制更新的範圍。 ### UNISTR Oracle UNISTR函數用於將特殊Unicode字符轉換為對應的字符。它的語法如下: ``` SELECT UNISTR('unicode_string') FROM dual; ``` 其中,'unicode_string' 是包含Unicode轉換字符的字符串。例如,UNISTR('\0391\0392\0393') 將返回希臘字母"ΑΒΓ"。 這個函數可以在SELECT語句中使用,請確保在執行UNISTR函數之前已經連接到Oracle數據庫。 在Oracle SQL中,可以使用unistr函数來插入Unicode字元。以下是一個示例: 假設我們要插入一個包含特殊Unicode字元的字串到一個表中,可以使用以下語句: ```sql INSERT INTO table_name (column_name) VALUES (UNISTR('\0041\0042\0043\0044')); ``` 在這個例子中,UNISTR('\0041\0042\0043\0044')會插入字元A、B、C和D到指定的column中。這樣就可以在Oracle中插入包含Unicode字元的資料。 ### ROWNUM 當使用 Oracle 資料庫時,可以使用 ROWNUM 來獲取序號或排名,以下是一個使用 ROWNUM 的範例: ```sql SELECT ROWNUM, employee_id, first_name, last_name FROM employees WHERE ROWNUM <= 5; ``` 這個查詢將從 employees 表中選取前五條記錄,並且用 ROWNUM 來顯示序號。這樣就可以簡單地查詢出前五條記錄,在每條記錄的最前面添加序號。 ### CREATE TABLE 之後設定外鍵 可以在 create table 之後設定外鍵,使用 ALTER TABLE 指令來新增外鍵約束。下面是一個例子: ```sql -- 創建兩個表格 CREATE TABLE 表格1 ( id INT PRIMARY KEY, column1 VARCHAR(50) ); CREATE TABLE 表格2 ( id INT PRIMARY KEY, column2 INT, FOREIGN KEY (column2) REFERENCES 表格1(id) ); ``` 在這個例子中,我們在表格2中的 column2 欄位設置了外鍵約束,參考表格1中的 id 欄位。我们可以在 create table 之後使用 ALTER TABLE 指令來新增外鍵約束: ```sql -- 新增外鍵約束 ALTER TABLE 表格2 ADD CONSTRAINT fk_column2 FOREIGN KEY (column2) REFERENCES 表格1(id); ``` 透過這樣的方式,我們可以在 create table 之後設定外鍵約束。 ### CLOB 存入較大的字串方法 以下是一個宣告一個clob變數並將其存入資料庫的範例: ```sql DECLARE l_clob_var CLOB; BEGIN DBMS_LOB.CREATETEMPORARY(l_clob_var, TRUE); -- 在此將資料寫入CLOB變數,這邊只是一個範例 l_clob_var := '這是一個CLOB變數的範例內容'; -- 將CLOB變數寫入資料庫表中 INSERT INTO your_table (clob_column) VALUES (l_clob_var); -- 釋放臨時CLOB DBMS_LOB.FREETEMPORARY(l_clob_var); END; / ``` 請確保替換 your_table 和 clob_column 爲實際的表名和clob欄位名。在此範例中,我們使用了 PL/SQL 語句來宣告一個CLOB變數,使用DBMS_LOB.CREATETEMPORARY() 函數創建臨時CLOB,將數據存入CLOB變數後,將其插入到資料庫表中,最後使用 DBMS_LOB.FREETEMPORARY() 釋放臨時CLOB。 ### Sequence 你可以透過以下方式在Oracle中創建一個序列(sequence): 1. 使用 CREATE SEQUENCE 陳述式: ```sql CREATE SEQUENCE sequence_name START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 1000 CYCLE; ``` 2. 在上面的程式碼中,你可以替換 sequence_name 為你想要的序列名稱,設定起始值(START WITH)、遞增值(INCREMENT BY)、最小值(MINVALUE)、最大值(MAXVALUE)和循環設定(CYCLE)。 3. 你也可以使用 ALTER SEQUENCE 來修改一個現有的序列,或是使用 DROP SEQUENCE 來刪除一個序列。 透過以上的步驟,你就可以在Oracle中成功創建一個序列了。 --- 要刪除一個 sequence,您可以使用以下語句: ```sql DROP SEQUENCE sequence_name; ``` 請確保您有權限來刪除這個 sequence。當您執行這個語句之後,該 sequence 將被徹底刪除,並且無法恢復。 ### 查詢時使用正則表達式 在 Oracle SQL 中,可以使用正則表達式和 LIKE 來進行模糊查詢。要在 Oracle SQL 中使用正則表達式,首先需要使用 REGEXP_LIKE 函數,該函數接受三個參數:要比對的字符串、正則表達式模式和(可選的)標誌。 舉例來說,假設我們希望查詢所有以字母 'a' 開頭且長度為三個字元的字符串,可以使用以下的 SQL 語句: ```sql SELECT column_name FROM table_name WHERE REGEXP_LIKE(column_name, '^a.{2}$'); ``` 在上面的例子中,'^a.{2}$' 是正則表達式模式,意思是以 'a' 開頭且後面跟著任意兩個字元的字符串。這將返回所有符合條件的記錄。 注意:在 Oracle SQL 中,正則表達式的語法有特定的規則,請確保使用正確的語法和模式來執行查詢。