Oracle中几个需要用动态语句执行的情况
来源:互联网 发布:股票分时预警软件 编辑:程序博客网 时间:2024/06/10 20:17
Oracle中几个需要用动态语句执行的情况
1.一般的在存储过程或函数中
要执行一个SQL数据定义语句(例如:create table ..,alter table ,drop table);
或执行一个数据控制語句(例如:grant...);
或一个会话控制(例如:altersession);
时要使用动态语句;
2.得到更多的灵活性,例如可能要在select语句中的where子句创建不同的搜索条件等等。
--把票据类型为5D的缴款书在库存中更新成一般缴款书 UPDATE GFMIS_ALL.RECEIPTSTOCK T SETT.RECEIPTTYPE=4765 WHERE EXISTS (SELECT 1 FROMGFMIS_ALL.RECEIPTSTOCK_5D19A_TMP TT WHERE TT.STOCKID =substr(T.STOCKID, 4, length(TRIM(T.STOCKID))) AND TT.ZTOF_STATUS ='1' AND TT.RECEIPTTYPE=11533);
OPEN C_RECEIPTSTOCK_TMP; LOOP FETCH C_RECEIPTSTOCK_TMPINTO v_STOCKID, v_STARTNO, v_ENDNO, v_RECEIPTTYPE, v_STATUS, v_COUNT, v_UNITID, V_RELATEUSERNAME, V_GLIDENUM, V_FLAG, V_PROCTM, V_REGISTERTM, V_DBUSER;
EXIT WHENC_RECEIPTSTOCK_TMP%NOTFOUND;
V_CURSQL := 'SELECT COUNT(1) FROM ' || V_DBUSER ||'.RECEIPTSTOCK WHERE STOCKID = ' || v_STOCKID; EXECUTE IMMEDIATE V_CURSQL INTOV_MYCOUNT;
IF (V_MYCOUNT = 0)THEN V_SPROCTM:=TO_CHAR(V_PROCTM,'YYYY-MM-DD HH24:MI:SS'); V_SREGISTERTM:=TO_CHAR(V_REGISTERTM,'YYYY-MM-DD HH24:MI:SS'); IF (v_RECEIPTTYPE = 11533)THEN v_RECEIPTTYPE := 4765; END IF; V_CURSQL :='INSERT INTO ' ||V_DBUSER || '.RECEIPTSTOCK(STOCKID,STARTNO,ENDNO,RECEIPTTYPE,STATUS,COUNT,UNITID,RELATEUSERNAME,GLIDENUM,FLAG,PROCTM,REGISTERTM)VALUES (' || v_STOCKID || ',''' || v_STARTNO || ''',''' || v_ENDNO|| ''',' || v_RECEIPTTYPE || ',''' || v_STATUS || ''',' || v_COUNT|| ',' || 1 || ',''' || V_RELATEUSERNAME || ''',' ||NVL(V_GLIDENUM, 0) || ',''' || V_FLAG || ''', TO_DATE(''' ||V_SPROCTM || ''',''YYYY-MM-DD HH24:MI:SS''), TO_DATE(''' ||V_SREGISTERTM || ''',''YYYY-MM-DD HH24:MI:SS''))'; EXECUTE IMMEDIATEV_CURSQL;
UPDATEGFMIS_ALL.RECEIPTSTOCK_5D19A_TMP T SET T.ZTOF_STATUS = '0' WHERET.STOCKID = v_STOCKID AND T.ZTOF_STATUS = '1';
DELETE FROMGFMIS.RECEIPTSTOCK WHERE STOCKID = v_STOCKID; END IF;
END LOOP;
UPDATE GFMIS_ALL.RECEIPTSTOCK_5D19A_TMP TSET T.ZTOF_STATUS = '0' WHERE T.ZTOF_STATUS = '1' ANDT.RECEIPTTYPE=11533;
COMMIT;
EXCEPTION WHEN NO_DATA_FOUND THEN NULL; WHEN OTHERS THEN CLOSEC_RECEIPTSTOCK_TMP; dbms_output.put_line(SQLERRM); IFC_RECEIPTSTOCK_TMP%ISOPEN THEN CLOSE C_RECEIPTSTOCK_TMP; ENDIF; RAISE;
--把票据类型为5D的缴款书在库存中更新成一般缴款书 或者把指定的19个单位的库存移到分库里面 SELECT COUNT(1) INTO v_mycount FROM GFMIS_ALL.RECEIPTSTOCK_DISTRICTT WHERE T.UNITID = :NEW.UNITID; IF(v_mycount > 0) THEN SELECT T.DBUSER INTO v_dbuserFROM GFMIS_ALL.RECEIPTSTOCK_DISTRICT T WHERE T.UNITID =:NEW.UNITID; curSQL := 'SELECT COUNT(1)FROM ' || v_dbuser || '.RECEIPTSTOCK WHERE UNITID = ' ||substr(:NEW.UNITID, 4, length(TRIM(:NEW.UNITID))) || ' ANDRECEIPTTYPE = ' || :NEW.RECEIPTTYPE || ' AND ENDNO = ''' ||:NEW.ENDNO ||''''; --注意结尾的这个地方单引号个数 EXECUTE IMMEDIATE curSQL INTOv_mycount; if (v_mycount = 0) then INSERTINTO GFMIS_ALL.RECEIPTSTOCK_5D19A_TMP(STOCKID,STARTNO,ENDNO,RECEIPTTYPE,STATUS,COUNT,UNITID,RELATEUSERNAME,GLIDENUM,FLAG,PROCTM,REGISTERTM,DBUSER,ZTOF_STATUS)VALUES (substr(:NEW.STOCKID, 4,length(TRIM(:NEW.STOCKID))),:NEW.STARTNO,:NEW.ENDNO,:NEW.RECEIPTTYPE,:NEW.STATUS,:NEW.COUNT,substr(:NEW.UNITID,4,length(TRIM(:NEW.UNITID))),:NEW.RELATEUSERNAME,:NEW.GLIDENUM,:NEW.FLAG,:NEW.PROCTM,:NEW.REGISTERTM,v_dbuser,'1'); end if; ENDIF; IF(:NEW.RECEIPTTYPE = 11533) THEN INSERT INTO GFMIS_ALL.RECEIPTSTOCK_5D19A_TMP(STOCKID,STARTNO,ENDNO,RECEIPTTYPE,STATUS,COUNT,UNITID,RELATEUSERNAME,GLIDENUM,FLAG,PROCTM,REGISTERTM,DBUSER,ZTOF_STATUS)VALUES (substr(:NEW.STOCKID, 4,length(TRIM(:NEW.STOCKID))),:NEW.STARTNO,:NEW.ENDNO,:NEW.RECEIPTTYPE,:NEW.STATUS,:NEW.COUNT,substr(:NEW.UNITID,4,length(TRIM(:NEW.UNITID))),:NEW.RELATEUSERNAME,:NEW.GLIDENUM,:NEW.FLAG,:NEW.PROCTM,:NEW.REGISTERTM,NULL,'1'); ENDIF; END IF; selectseq_exchange_temp.nextval into id from dual; curUser :=lower(SYS_CONTEXT('userenv', 'session_user')); if (curUser !='all_exchange_user') then SELECT CURRENT_TIMESTAMP INTO curTime FROMDUAL; curTable := 'RECEIPTSTOCK'; if (deleting) then v_distno:=substr(:OLD.STOCKID,2,2); selectdbuser into v_dbuser from GFMIS_ALL.DISTRICT wheredistno=v_distno; curType :='3'; curID :=:OLD.stockid; if (curUser = 'gfmis_all') then curSQL :='SP_EX_RECEIPTSTOCK_DEL(''' || :OLD.STOCKID || ''')'; else curSQL :='SP_EX_RECEIPTSTOCK_DEL(''' || substr(:OLD.STOCKID,4) || ''')'; end if; end if; if (updating) then v_distno:=substr(:OLD.STOCKID,2,2); selectdbuser into v_dbuser from GFMIS_ALL.DISTRICT wheredistno=v_distno; curType :='2'; curID :=:OLD.stockid; if (curUser ='gfmis_all') then curSQL :='SP_EX_RECEIPTSTOCK_UPD(''' || :OLD.STOCKID || ''', ''' ||:NEW.COUNT || ''', ''' || FN_FMT_SQL(:NEW.ENDNO) || ''', ''' ||FN_FMT_SQL(:NEW.FLAG) || ''', ''' || :NEW.GLIDENUM || ''', ''' ||TO_CHAR(:NEW.PROCTM,'YYYY-MM-DD HH24:MI:SS') || ''', ''' ||:NEW.RECEIPTTYPE || ''', ''' || TO_CHAR(:NEW.REGISTERTM,'YYYY-MM-DDHH24:MI:SS') || ''', ''' || FN_FMT_SQL(:NEW.RELATEUSERNAME) || ''',''' || FN_FMT_SQL(:NEW.STARTNO) || ''', ''' ||FN_FMT_SQL(:NEW.STATUS) || ''', ''' || :NEW.STOCKID || ''', ''' ||:NEW.UNITID || ''')'; else curSQL :='SP_EX_RECEIPTSTOCK_UPD(''' || substr(:OLD.STOCKID,4) || ''', ''' || :NEW.COUNT|| ''', ''' || FN_FMT_SQL(:NEW.ENDNO) || ''', ''' ||FN_FMT_SQL(:NEW.FLAG) || ''', ''' || :NEW.GLIDENUM || ''', ''' ||TO_CHAR(:NEW.PROCTM,'YYYY-MM-DD HH24:MI:SS') || ''', ''' ||:NEW.RECEIPTTYPE || ''', ''' || TO_CHAR(:NEW.REGISTERTM,'YYYY-MM-DDHH24:MI:SS') || ''', ''' || FN_FMT_SQL(:NEW.RELATEUSERNAME) || ''',''' || FN_FMT_SQL(:NEW.STARTNO) || ''', ''' ||FN_FMT_SQL(:NEW.STATUS) || ''', ''' || substr(:NEW.STOCKID ,4) || ''', ''' || substr(:NEW.UNITID ,4) || ''')'; end if; end if; if (inserting) then v_distno:=substr(:NEW.STOCKID,2,2); selectdbuser into v_dbuser from GFMIS_ALL.DISTRICT wheredistno=v_distno; curType :='1'; curID := :NEW.stockid; if (curUser ='gfmis_all') then curSQL :='SP_EX_RECEIPTSTOCK_INS(''' || :NEW.COUNT || ''', ''' ||FN_FMT_SQL(:NEW.ENDNO) || ''', ''' || FN_FMT_SQL(:NEW.FLAG) || ''',''' || :NEW.GLIDENUM || ''', ''' || TO_CHAR(:NEW.PROCTM,'YYYY-MM-DDHH24:MI:SS') || ''', ''' || :NEW.RECEIPTTYPE || ''', ''' ||TO_CHAR(:NEW.REGISTERTM,'YYYY-MM-DD HH24:MI:SS') || ''', ''' ||FN_FMT_SQL(:NEW.RELATEUSERNAME) || ''', ''' ||FN_FMT_SQL(:NEW.STARTNO) || ''', ''' || FN_FMT_SQL(:NEW.STATUS) ||''', ''' || :NEW.STOCKID || ''', ''' || :NEW.UNITID || ''')'; else curSQL :='SP_EX_RECEIPTSTOCK_INS(''' || :NEW.COUNT || ''', ''' ||FN_FMT_SQL(:NEW.ENDNO) || ''', ''' || FN_FMT_SQL(:NEW.FLAG) || ''',''' || :NEW.GLIDENUM || ''', ''' || TO_CHAR(:NEW.PROCTM,'YYYY-MM-DDHH24:MI:SS') || ''', ''' || :NEW.RECEIPTTYPE || ''', ''' ||TO_CHAR(:NEW.REGISTERTM,'YYYY-MM-DD HH24:MI:SS') || ''', ''' ||FN_FMT_SQL(:NEW.RELATEUSERNAME) || ''', ''' ||FN_FMT_SQL(:NEW.STARTNO) || ''', ''' || FN_FMT_SQL(:NEW.STATUS) ||''', ''' || substr(:NEW.STOCKID ,4) || ''', '''|| substr(:NEW.UNITID ,4) || ''')'; end if; end if;
if (curUser ='gfmis_all') then INSERT INTO exchange_temp (ID,SEQID, TABLENAME,GENSQL, TYPE, DT, STATUS,ZKSTATUS,DBUSER) VALUES (id, curID,curTable, curSQL, curType,curTime, '2','1',v_dbuser ); else INSERT INTO exchange_temp (ID,SEQID, TABLENAME,GENSQL, TYPE, DT, STATUS,ZKSTATUS,DBUSER) VALUES (id, curID,curTable, curSQL, curType,curTime, '2','0',v_dbuser ); end if; end if;
EXCEPTION WHEN OTHERS THEN RAISE;
myrrsql in char rtnRctNO:=myrrsql; if myrrsql is null THEN return myrrsql; ELSE rtnRctNO :=REPLACE(rtnRctNO, '''', ''''''); return rtnRctNO; END IF;
要执行一个SQL数据定义语句(例如:create
或执行一个数据控制語句(例如:grant...);
或一个会话控制(例如:alter
时要使用动态语句;
2.得到更多的灵活性,例如可能要在select语句中的where子句创建不同的搜索条件等等。
execute immediate"sql语句";
也可以使用dbms_sql执行动态语句。
--------------------------------------------------------------------------------------------------
一个存储过程的例子:
create orreplace procedure PRO_RECEIPTSTOCK_5D19A IS
v_STOCKID NUMBER(13);
v_STARTNO VARCHAR2(10);
v_ENDNO VARCHAR2(10);
v_RECEIPTTYPE NUMBER(13);
v_STATUS CHAR(1);
v_COUNT NUMBER(13);
v_UNITID NUMBER(13);
V_RELATEUSERNAME VARCHAR2(150);
V_GLIDENUM NUMBER(20);
V_FLAG VARCHAR2(2);
V_PROCTM DATE;
V_SPROCTM VARCHAR2(30);
V_REGISTERTM DATE;
V_SREGISTERTM VARCHAR2(30);
V_DBUSER VARCHAR2(20);
V_CURSQL VARCHAR2(4000);
V_MYCOUNT NUMBER;
CURSOR C_RECEIPTSTOCK_TMP IS
SELECT
STOCKID,
STARTNO,
ENDNO,
RECEIPTTYPE,
STATUS,
COUNT,
UNITID,
RELATEUSERNAME,
GLIDENUM,
FLAG,
PROCTM,
REGISTERTM,
DBUSER
FROM GFMIS_ALL.RECEIPTSTOCK_5D19A_TMP
WHERE
ZTOF_STATUS = '1'
AND DBUSER IS NOT NULL;
begin
end PRO_RECEIPTSTOCK_5D19A;
--------------------------------------------------------------------------------------------------
一个触发器的例子:
CREATE OR REPLACE TRIGGER TRI_RECEIPTSTOCK_CHG_AFT
AFTER DELETE OR INSERT OR UPDATE
ON RECEIPTSTOCK
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
curTable varchar2(50);
curType char(1);
curTime TimeStamp;
id number(13);
curID number(13);
curSQL varchar2(4000);
curUser varchar2(30);
v_distno varchar2(30);
v_dbuser varchar2(30);
v_mycount number(13);
BEGIN
IF (INSERTING)THEN
end TRI_RECEIPTSTOCK_CHG_AFT;
--------------------------------------------------------------------------------------------------
把单引号替换成双引号的函数:
CREATE OR REPLACE FUNCTION "FN_FMT_SQL" (
)
return char
AS
rtnRctNO varchar2(4000);
BEGIN
end FN_FMT_SQL;
0 0
- Oracle中几个需要用动态语句执行的情况
- ORACLE 查询是否存在某些要处理的数据,然后再去动态执行下面需要执行的动态语句
- oracle关于查询空间使用情况的几个sql语句
- RuntimeException的特殊情况&&[C++] 有人会在程序中try catch吗?&&什么样的问题需要用try catch语句执行
- Oracle中一次执行多条SQL语句需要注意的地方
- ORACLE执行动态语句_hanCSDN_20130418
- ORACLE 动态执行SQL语句
- ORACLE 动态执行SQL语句
- oracle存储过程中如何执行动态SQL语句
- oracle存储过程中如何执行动态SQL语句
- Oracle 过程中执行动态 SQL 或 DDL 语句
- access中使用SQL语句需要注意的几个地方
- 【mysql】mysql中需要注意的几个sql语句
- oracle 中sql 语句的执行过程
- oracle执行select语句需要commit吗
- 使用Oracle的DBMS_SQL包执行动态SQL语句
- 使用Oracle的DBMS_SQL包执行动态SQL语句
- 使用Oracle的DBMS_SQL包执行动态SQL语句
- 调用Windows属性窗口
- DML、DDL、DCL区别 .
- 链表的基本操作C/c++
- Tornado模板系统实现方式
- 基于python实时监控cpu的小工具
- Oracle中几个需要用动态语句执行的情况
- linux shell中"2>&1"含义
- 对Android view/viewgroup事件分发的理解
- 压缩字节数组
- VC6程序如何跟踪进入DLL函数调试
- iOS webview清除缓存
- tornado模板引擎原理
- Qt5对于c++11的支持
- 前后端渲染页面的区别?