資料庫查詢

1.1 版本

1.2 功能介紹

資料庫查詢是指從定義好的資料庫連結中，也就是資料源中使用 SQL 語句來選擇需要的欄位，一般是直接對資料源中的資料庫表直接進行操作。

資料庫查詢方式可分為：表查詢、檢視表查詢、模糊查詢三種。

2.1 表查詢

2.1.1 查詢步驟

1）點選「範本>範本資料集」，彈出範本資料集對話框，點選範本資料集視窗的左上角的按鈕，選擇「資料庫查詢」，然後給資料庫查詢命名，選擇所需的資料連結，如下圖所示：

2）選中查詢編輯器，在其中輸入 SQL 語句SELECT * FROM ORDERS where ORDERID=${ID}。

這是一個帶參數的 SQL 查詢，${}為參數的形式，中間的 ID 即參數的名稱，點選預覽，跳出提示框，輸入參數ID=10004，結果可以在預覽組中看，實際就是執行SELECT * FROM ORDERS where ORDERID=10004的 SQL 查詢。如下圖所示：

注：一般的 SQL 語句通用，只是在設定參數時需要參照以上的形式。

注：若參數名稱是字母和數字組合（例如：A1）則會被系統認為是儲存格，如果不設定預設值會觸發範本計算，進而導致執行一次 SQL。所以使用者需要避免使用字母和數字組合的參數名稱，若要用則必須給它設定預設值。

2.1.2 資料集介面的基本屬性

下面介紹整個資料集介面的一些基本屬性，如下表所示：

2.2 檢視表查詢

檢視表查詢是指對定義好的資料源中的檢視表進行操作。

2.2.1 資料庫查詢

1）點選選單「範本>範本資料集」，彈出範本資料集對話框，點選範本資料集視窗的左上角的按鈕，選擇「資料庫查詢」，然後給資料庫查詢命名，即可新增檢視表查詢。如下圖所示：

2.2.2 使用檢視表查詢

選擇資料源使用 SQL 對檢視表查詢進行操作。下文以 SQL Server 檢視表資料庫為例：

在查詢編輯器中輸入 SQL 語句：SELECT * FROM [sys].[types] 其中 [sys].[types] 是某個檢視表查詢的名稱，然後點選「預覽」即可看到檢視表查詢引用成功。如下圖所示：

注：可以將資料連結中的表、檢視表直接拖動到查詢編輯器中。

2.3 模糊查詢

在表下方有一塊空白搜尋框，它們用作模糊查詢。例如想要搜尋顧客相關的表，在搜尋框中填入customer，則會進行自動搜尋進行模糊匹配，如下圖所示：

注1：FineReport 資料庫查詢只支援一條自然 SQL 語句的查詢，不支援多個自然 SQL 語句，比如 SELECT * FROM A SELECT * FROM B

注2：create 、drop 等建立表、刪除表不在官方支援範圍內，官方只支援 select 。

注3：在SQL資料集中執行儲存程式時，當前系統只支援以下兩種開頭的語句格式：{CALL 和 {?=CALL，不支援以 EXEC 開頭的語句格式。

在之前版本中，若 SQL 中含有參數，就不方便直接從查詢介面將 SQL 語句複製到其他地方。

10.0.17 及之後的版本，新增「查看執行 SQL」功能，方便使用者貼補其他資料庫軟體進行排查測試。

3.1 範例

點選查詢頁面上方的「查看執行 SQL」按鈕，由於 SQL 語句中包含參數，會自動彈出參數輸入介面。如下圖所示：

執行的 SQL 語句自動複製到剪貼簿，使用者可將其貼補到其他資料庫軟體進行排查測試。如下圖所示：

3.2 注意事項

1）若SQL中呼叫了自訂函式，則無法正常使用「查看執行SQL」功能。複製的SQL語句在其他資料庫軟體無法正常運作。
2）某些使用者為了 SQL 防注入 ，開啟了轉義功能。當參數與分離字元匹配時，會影響查詢結果。在這種情況下會提示：轉義部分參數防止SQL注入，查詢結果與預期可能不符，請檢查並修改參數或交握管理者 。   
滑鼠觸碰右上角提示圖示，可以看到已轉義的參數。在本範例中，輸入參數值 1002 是被轉義的參數。

11.0.16 及之後版本，支援在資料庫日誌中的 SQL 語句前新增註釋，幫助排查資料庫效能問題。

4.1 SQL註釋內容

SQL 查詢場景及對應的註釋內容如下表所示：
注1：註釋中的「範本名稱」/「資料集名稱」/「任務名稱」為 base64 加密後的密文，使用者可自行使用解碼工具進行解碼查看。

注2：儲存程式不支援顯示 SQL 註釋。

注3：不支持mycat，如需使用请联系技术支持。

SQL查詢場景	日誌格式	效果範例
設計器中預覽資料集	-- DesignerDatasetPreview_[資料集名稱]_[正在編輯的範本名] SQL語句
數據決策系統中預覽伺服器資料集	-- DatasetPreview_[資料集名稱] SQL語句
預覽範本	-- TemplatePreviewFetch_[範本名稱] SQL語句
抽數快取	-- ESDFetch_[範本名稱] SQL語句
排程管理	-- ScheduleFetch_[範本名稱]_[任務名稱] SQL語句

4.2 資料庫日誌查詢

4.2.1 MySQL

MySQL 伺服器執行的查詢語句會記錄在通用查詢日誌(General Query Log)中，預設情況下，通用查詢日誌功能是關閉的。
使用以下命令查看通用查詢日誌是否開啟，其中，「general_log_file」變數指定了日誌檔案所在的位置。如下圖所示：

mysql>SHOW VARIABLES LIKE '%general%';

若通用查詢日誌功能為關閉狀態，可以透過修改 MySQL 的 my.cnf 配置檔案來開啟通用查詢日誌功能。

general_log_file = /var/log/mysql/mysql.log    #general_log_file的路徑為日誌的匯出路徑
general_log = 1    #將general_log設定為1，表示開啟查詢日誌

修改完成後，儲存配置檔案，並重啟 MySQL 即可。

4.2.2 Oracle

v$sql 記錄共享池快取中使用者執行過的 SQL 資訊，可以透過 SQL 語句查詢出具體 SQL 以及操作的時間，如下圖所示：
注：若查詢不到已執行的 SQL，可能是執行的 SQL 已經被置換出共享 SQL 區。

SELECT SQL_TEXT，FIRST_LOAD_TIME
FROM v$sql

4.2.3 SQL SERVER

sys.dm_exec_query_stats 記錄 SQL Server 快取中的查詢語句，可以透過 SQL 語句查詢出具體 SQL 以及操作的時間，如下圖所示：
注：sys.dm_exec_query_stats 僅記錄已完成的查詢資料。

SELECT TOP 1000
--建立時間
QS.creation_time,
--查詢語句
SUBSTRING(ST.text,(QS.statement_start_offset/2)+1,
((CASE QS.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
ELSE QS.statement_end_offset END - QS.statement_start_offset)/2) + 1
) AS statement_text,
--執行正文
ST.text,
--執行計劃
QS.total_worker_time,
QS.last_worker_time,
QS.max_worker_time,
QS.min_worker_time
FROM
sys.dm_exec_query_stats QS
--關鍵字
CROSS APPLY
sys.dm_exec_sql_text(QS.sql_handle) ST
WHERE
QS.creation_time BETWEEN '2011-10-20 16:00:00' AND '2023-10-20 17:00:00'
AND ST.text LIKE '%%'
ORDER BY
QS.creation_time DESC
显示代码