oracle動態sql儲存過程示例
create or replace procedure p_deal_order_complete_info(
V_ORDER_ID IN DEAL_ORDER.ORDER_ID%TYPE, --訂單ID
V_ORDER_TYPE IN DEAL_ORDER.ORDER_TYPE%TYPE, --訂單型別
V_prov_code IN DEAL_ORDER.Prov_Code%TYPE, --省編碼
V_city_code IN DEAL_ORDER.city_code%TYPE, --市編碼
V_order_status IN DEAL_ORDER.order_status%TYPE, --訂單狀態
V_channel_id IN DEAL_ORDER.channel_id%TYPE, --渠道
V_begin_date IN DEAL_ORDER.Create_Date%TYPE, --建立時間From （DATE）
V_end_date IN DEAL_ORDER.Create_Date%TYPE, --建立時間To （DATE）
v_syscursor out sys_refcursor
)
is
/* 過程、函式簡要描述資訊
**********************************************************
* 儲存過程名 : p_deal_order_complete_info
* 建立日期 : 2012-10-26
* 作者 : abc
* 模組 : 交易管理
* 功能描述 : 匯出訂單資訊
* 輸入引數 ： 訂單ID
訂單型別
地區
訂單狀態
渠道
建立時間From （DATE）
建立時間To （DATE）
*
*
* 輸出引數 ： 訂單型別
地區
訂單狀態
金額
渠道
建立時間
備註
客戶名稱 -- 使用者管理表,透過訂單表的使用者id，與此表關聯，可以得到客戶名稱和身份證號碼
身份證號碼
賬戶名稱
支付方式 －－付款資訊,透過訂單表的付款id與此表關聯，可以得到此
收貨人
聯絡號碼
手機號碼
物流公司 --不知源於哪個表
物流編號 －－源於哪個表啊，訂單表的
送貨人 －－不知是哪個表
送貨人聯絡電話 －－同上
訂單明細ID －－訂單明細表
銷售品ID
銷售品名稱
銷售品型別
價格
數量
選擇項ID －－訂單使用者選擇記錄表 ，透過訂明明細id與訂明明細表關聯
選擇項型別
選擇項名稱>集合
* 源表： DEAL_ORDER 訂單表
* 目標表：
* 備註:
①字元型別入參為空時，不做為查詢條件。
數值型別入參為-1時，不做為查詢條件。
②訂單ID、地區提供模糊查詢功能。
*------------------------------------------------------------
* 修改歷史
* 序號 日期 修改人 修改原因
* 1 2012-10-26 abc 建立
*------------------------------------------------------------
*/
v_sql varchar2(5000);
V_PAR_DATE VARCHAR2(30); -- 操作日期
V_PRO_NAME VARCHAR2(40); -- 儲存過程名
V_ERR_CODE VARCHAR2(50); -- 錯誤程式碼
V_ERR_MSG VARCHAR2(3000); -- 錯誤資訊
begin
V_ERR_MSG := '遊標初始化失敗';
V_PRO_NAME := 'p_deal_order_complete_info';
V_PAR_DATE := TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:MM:SS');
PLOG.INFO(V_PRO_NAME, '開始執行，日期: ' || V_PAR_DATE);
--主體邏輯
--若無任何引數
if V_ORDER_ID is null and V_ORDER_TYPE is null and
V_prov_code is null and V_city_code is null and V_order_status is null and
V_channel_id is null and V_begin_date is null and V_end_date is null then
open v_syscursor for 'select d.order_id,d.order_type,
d.Prov_Code,d.city_code,
d.order_status,d.ORDER_PRICE,
d.channel_id,d.CREATE_DATE,
d.ORDER_REMARK,uinfo.REALNAME,
uinfo.CARDNUM,uinfo.username,
dpay.PAY_TYPE,dpay.SEND_CUSNAME,
dpay.SEND_CUSTELNUM,dpay.SEND_CUSMOBILE,
de.ORDER_DETAIL_ID,
de.OFFER_ID,de.OFFER_TITLE,
de.OFFER_TYPE,de.PRICE,
de.QTY,dc.CHOOSE_ID,
dc.CHOOSE_TYPE,dc.CHOOSE_NAME
from DEAL_ORDER d,
DEAL_ORDER_DETAIL de,
DEAL_ORDER_USER_CHOOSE dc
DEAL_PAY_INFO dpay,
USER_INFO uinfo
where d.order_id=de.order_id and
de.ORDER_DETAIL_ID=dc.ORDER_DETAILID and
d.PAY_ID=dpay.PAY_ID and
d.USER_ID=uinfo.USERID';
else
--如下的sql剛開始一直編譯不透過，後來把此sql單拿出為到ue,然後再複製回來即可，我想可能是什麼特殊字元導致不能編譯透過，還有一法，就是把如下的sql慢慢接著步步測試，直到完成測試成功
v_sql :='select d.order_id,d.order_type,
d.Prov_Code,d.city_code,
d.order_status,d.ORDER_PRICE,
d.channel_id,d.CREATE_DATE,
d.ORDER_REMARK,uinfo.REALNAME,
uinfo.CARDNUM,uinfo.username,
dpay.PAY_TYPE,dpay.SEND_CUSNAME,
dpay.SEND_CUSTELNUM,dpay.SEND_CUSMOBILE,
de.ORDER_DETAIL_ID,
de.OFFER_ID,de.OFFER_TITLE,
de.OFFER_TYPE,de.PRICE,
de.QTY,dc.CHOOSE_ID,
dc.CHOOSE_TYPE,dc.CHOOSE_NAME
from DEAL_ORDER d,
DEAL_ORDER_DETAIL de,
DEAL_ORDER_USER_CHOOSE dc
DEAL_PAY_INFO dpay,
USER_INFO uinfo
where d.order_id=de.order_id and
de.ORDER_DETAIL_ID=dc.ORDER_DETAILID and
d.PAY_ID=dpay.PAY_ID and
d.USER_ID=uinfo.USERID and ';
--判斷是否輸入店鋪ID
--判斷是否輸入訂單ID
if V_ORDER_ID is not null then
v_sql := v_sql || ' d.ORDER_ID like '||chr(39)||'%'|| V_ORDER_ID ||'%'||chr(39)||' and ';
end if;
--判斷是否輸入訂單型別
if V_ORDER_TYPE is not null then
v_sql := v_sql || 'd.ORDER_TYPE=' || V_ORDER_TYPE || ' and ';
end if;
--判斷是否輸入省編碼
if V_prov_code is not null then
v_sql := v_sql || 'd.Prov_Code=' ||chr(39)||V_prov_code ||chr(39)|| ' and ';
end if;
--判斷是否輸入市編碼
if V_city_code is not null then
v_sql := v_sql || 'd.city_code=' || chr(39)||V_city_code ||chr(39)|| ' and ';
end if;
--判斷是否輸入訂單狀態
if V_order_status is not null then
v_sql := v_sql || 'd.order_status=' ||chr(39)|| V_order_status||chr(39)||' and ';
end if;
--判斷是否輸入渠道
if V_channel_id is not null then
v_sql := v_sql || 'd.channel_id=' ||chr(39)||V_channel_id||chr(39)|| ' and ';
end if;
--判斷是否輸入建立時間From
if V_begin_date is not null then
v_sql := v_sql || 'd.Create_Date >= ' || V_begin_date || ' and ';
end if;
--判斷是否輸入建立時間To
if V_end_date is not null then
v_sql := v_sql || 'd.Create_Date <= ' || V_end_date || ' and ';
end if;
V_ORDER_ID IN DEAL_ORDER.ORDER_ID%TYPE, --訂單ID
V_ORDER_TYPE IN DEAL_ORDER.ORDER_TYPE%TYPE, --訂單型別
V_prov_code IN DEAL_ORDER.Prov_Code%TYPE, --省編碼
V_city_code IN DEAL_ORDER.city_code%TYPE, --市編碼
V_order_status IN DEAL_ORDER.order_status%TYPE, --訂單狀態
V_channel_id IN DEAL_ORDER.channel_id%TYPE, --渠道
V_begin_date IN DEAL_ORDER.Create_Date%TYPE, --建立時間From （DATE）
V_end_date IN DEAL_ORDER.Create_Date%TYPE, --建立時間To （DATE）
v_syscursor out sys_refcursor
)
is
/* 過程、函式簡要描述資訊
**********************************************************
* 儲存過程名 : p_deal_order_complete_info
* 建立日期 : 2012-10-26
* 作者 : abc
* 模組 : 交易管理
* 功能描述 : 匯出訂單資訊
* 輸入引數 ： 訂單ID
訂單型別
地區
訂單狀態
渠道
建立時間From （DATE）
建立時間To （DATE）
*
*
* 輸出引數 ： 訂單型別
地區
訂單狀態
金額
渠道
建立時間
備註
客戶名稱 -- 使用者管理表,透過訂單表的使用者id，與此表關聯，可以得到客戶名稱和身份證號碼
身份證號碼
賬戶名稱
支付方式 －－付款資訊,透過訂單表的付款id與此表關聯，可以得到此
收貨人
聯絡號碼
手機號碼
物流公司 --不知源於哪個表
物流編號 －－源於哪個表啊，訂單表的
送貨人 －－不知是哪個表
送貨人聯絡電話 －－同上
訂單明細ID －－訂單明細表
銷售品ID
銷售品名稱
銷售品型別
價格
數量
選擇項ID －－訂單使用者選擇記錄表 ，透過訂明明細id與訂明明細表關聯
選擇項型別
選擇項名稱>集合
* 源表： DEAL_ORDER 訂單表
* 目標表：
* 備註:
①字元型別入參為空時，不做為查詢條件。
數值型別入參為-1時，不做為查詢條件。
②訂單ID、地區提供模糊查詢功能。
*------------------------------------------------------------
* 修改歷史
* 序號 日期 修改人 修改原因
* 1 2012-10-26 abc 建立
*------------------------------------------------------------
*/
v_sql varchar2(5000);
V_PAR_DATE VARCHAR2(30); -- 操作日期
V_PRO_NAME VARCHAR2(40); -- 儲存過程名
V_ERR_CODE VARCHAR2(50); -- 錯誤程式碼
V_ERR_MSG VARCHAR2(3000); -- 錯誤資訊
begin
V_ERR_MSG := '遊標初始化失敗';
V_PRO_NAME := 'p_deal_order_complete_info';
V_PAR_DATE := TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:MM:SS');
PLOG.INFO(V_PRO_NAME, '開始執行，日期: ' || V_PAR_DATE);
--主體邏輯
--若無任何引數
if V_ORDER_ID is null and V_ORDER_TYPE is null and
V_prov_code is null and V_city_code is null and V_order_status is null and
V_channel_id is null and V_begin_date is null and V_end_date is null then
open v_syscursor for 'select d.order_id,d.order_type,
d.Prov_Code,d.city_code,
d.order_status,d.ORDER_PRICE,
d.channel_id,d.CREATE_DATE,
d.ORDER_REMARK,uinfo.REALNAME,
uinfo.CARDNUM,uinfo.username,
dpay.PAY_TYPE,dpay.SEND_CUSNAME,
dpay.SEND_CUSTELNUM,dpay.SEND_CUSMOBILE,
de.ORDER_DETAIL_ID,
de.OFFER_ID,de.OFFER_TITLE,
de.OFFER_TYPE,de.PRICE,
de.QTY,dc.CHOOSE_ID,
dc.CHOOSE_TYPE,dc.CHOOSE_NAME
from DEAL_ORDER d,
DEAL_ORDER_DETAIL de,
DEAL_ORDER_USER_CHOOSE dc
DEAL_PAY_INFO dpay,
USER_INFO uinfo
where d.order_id=de.order_id and
de.ORDER_DETAIL_ID=dc.ORDER_DETAILID and
d.PAY_ID=dpay.PAY_ID and
d.USER_ID=uinfo.USERID';
else
--如下的sql剛開始一直編譯不透過，後來把此sql單拿出為到ue,然後再複製回來即可，我想可能是什麼特殊字元導致不能編譯透過，還有一法，就是把如下的sql慢慢接著步步測試，直到完成測試成功
v_sql :='select d.order_id,d.order_type,
d.Prov_Code,d.city_code,
d.order_status,d.ORDER_PRICE,
d.channel_id,d.CREATE_DATE,
d.ORDER_REMARK,uinfo.REALNAME,
uinfo.CARDNUM,uinfo.username,
dpay.PAY_TYPE,dpay.SEND_CUSNAME,
dpay.SEND_CUSTELNUM,dpay.SEND_CUSMOBILE,
de.ORDER_DETAIL_ID,
de.OFFER_ID,de.OFFER_TITLE,
de.OFFER_TYPE,de.PRICE,
de.QTY,dc.CHOOSE_ID,
dc.CHOOSE_TYPE,dc.CHOOSE_NAME
from DEAL_ORDER d,
DEAL_ORDER_DETAIL de,
DEAL_ORDER_USER_CHOOSE dc
DEAL_PAY_INFO dpay,
USER_INFO uinfo
where d.order_id=de.order_id and
de.ORDER_DETAIL_ID=dc.ORDER_DETAILID and
d.PAY_ID=dpay.PAY_ID and
d.USER_ID=uinfo.USERID and ';
--判斷是否輸入店鋪ID
--判斷是否輸入訂單ID
if V_ORDER_ID is not null then
v_sql := v_sql || ' d.ORDER_ID like '||chr(39)||'%'|| V_ORDER_ID ||'%'||chr(39)||' and ';
end if;
--判斷是否輸入訂單型別
if V_ORDER_TYPE is not null then
v_sql := v_sql || 'd.ORDER_TYPE=' || V_ORDER_TYPE || ' and ';
end if;
--判斷是否輸入省編碼
if V_prov_code is not null then
v_sql := v_sql || 'd.Prov_Code=' ||chr(39)||V_prov_code ||chr(39)|| ' and ';
end if;
--判斷是否輸入市編碼
if V_city_code is not null then
v_sql := v_sql || 'd.city_code=' || chr(39)||V_city_code ||chr(39)|| ' and ';
end if;
--判斷是否輸入訂單狀態
if V_order_status is not null then
v_sql := v_sql || 'd.order_status=' ||chr(39)|| V_order_status||chr(39)||' and ';
end if;
--判斷是否輸入渠道
if V_channel_id is not null then
v_sql := v_sql || 'd.channel_id=' ||chr(39)||V_channel_id||chr(39)|| ' and ';
end if;
--判斷是否輸入建立時間From
if V_begin_date is not null then
v_sql := v_sql || 'd.Create_Date >= ' || V_begin_date || ' and ';
end if;
--判斷是否輸入建立時間To
if V_end_date is not null then
v_sql := v_sql || 'd.Create_Date <= ' || V_end_date || ' and ';
end if;
--and 1=1非常好，我之前是沒有想到，我哪個招還要把最後的and用substr截掉，這個就不用了，很好，以為備記
v_sql := v_sql || 'and 1=1';
open v_syscursor for v_sql;
end if;
V_ERR_MSG := V_PRO_NAME || '遊標開啟失敗 ';
PLOG.INFO(V_PRO_NAME, '過程結束,結束日期: ' || V_PAR_DATE);
/*資料異常記錄資訊後回滾*/
v_sql := v_sql || 'and 1=1';
open v_syscursor for v_sql;
end if;
V_ERR_MSG := V_PRO_NAME || '遊標開啟失敗 ';
PLOG.INFO(V_PRO_NAME, '過程結束,結束日期: ' || V_PAR_DATE);
/*資料異常記錄資訊後回滾*/
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
V_ERR_CODE := SQLCODE;
PLOG.ERROR(V_PRO_NAME, V_ERR_CODE, V_ERR_MSG || ' ' || SQLERRM);
end p_deal_order_complete_info;
WHEN OTHERS THEN
ROLLBACK;
V_ERR_CODE := SQLCODE;
PLOG.ERROR(V_PRO_NAME, V_ERR_CODE, V_ERR_MSG || ' ' || SQLERRM);
end p_deal_order_complete_info;
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9240380/viewspace-747282/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- SQL儲存過程示例SQL儲存過程
- ORACLE 儲存過程示例Oracle儲存過程
- 動態儲存過程儲存過程
- oracle儲存過程(procedure)中執行動態SQL小記Oracle儲存過程SQL
- PL/SQL中動態掉用儲存過程SQL儲存過程
- MySQL儲存過程裡動態SQL的使用UXMySql儲存過程UX
- Oracle Pl/SQL 之 儲存過程OracleSQL儲存過程
- 動態呼叫儲存過程 sample:儲存過程
- 儲存過程vs.動態SQL：如何選用？PV儲存過程SQL
- MyBatis 示例之儲存過程MyBatis儲存過程
- Oracle SQL Developer Debug儲存過程OracleSQLDeveloper儲存過程
- 【實戰】oracle job + 儲存過程 的使用示例Oracle儲存過程
- SQL Server系統儲存過程和引數示例SQLServer儲存過程
- /*動態執行儲存過程DEMO*/儲存過程
- 用儲存過程動態建立表儲存過程
- Oracle儲存過程Oracle儲存過程
- SQL Server 儲存過程SQLServer儲存過程
- 【SQL Server】--儲存過程SQLServer儲存過程
- tcbs_批量儲存過程_sql_case when_示例儲存過程SQL
- SQL 儲存過程裡呼叫另一個儲存過程SQL儲存過程
- oracle procedure 儲存過程輸入及輸出in out示例Oracle儲存過程
- JDBC 呼叫儲存過程程式碼示例JDBC儲存過程
- Oracle儲存過程乾貨（一）：儲存過程基礎Oracle儲存過程
- oracle儲存過程將引數字串分割sqlOracle儲存過程字串SQL
- SQL 分頁儲存過程SQL儲存過程
- MS SQL Server儲存過程SQLServer儲存過程
- Sql Server系列：儲存過程SQLServer儲存過程
- SQL 建立儲存過程PROCEDURESQL儲存過程
- 解密SQL SERVER儲存過程解密SQLServer儲存過程
- SQL儲存過程迴圈SQL儲存過程
- sql儲存過程分頁SQL儲存過程
- Oracle儲存過程-1Oracle儲存過程
- oracle的儲存過程Oracle儲存過程
- Oracle儲存過程例子Oracle儲存過程
- Oracle建立儲存過程Oracle儲存過程
- oracle plsql儲存過程OracleSQL儲存過程
- 【Oracle】儲存過程中將動態SQL的多行結果進行迴圈遍歷Oracle儲存過程SQL
- 動態呼叫帶引數的儲存過程儲存過程