Oracle中DBMS_RANDOM包的用法

我的myoracle.txt脚本内容如下：

drop procedure PRO_RECEIPTSTOCK_5D19A;

create or replace 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

--把票据类型为5D的缴款书在库存中更新成一般缴款书

UPDATE GFMIS_ALL.RECEIPTSTOCK T SET T.RECEIPTTYPE=4765 WHERE EXISTS (SELECT 1 FROM GFMIS_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_TMP INTO 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 WHEN C_RECEIPTSTOCK_TMP%NOTFOUND;

V_CURSQL := 'SELECT COUNT(1) FROM ' || V_DBUSER || '.RECEIPTSTOCK WHERE STOCKID = ' || v_STOCKID;

EXECUTE IMMEDIATE V_CURSQL INTO V_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 IMMEDIATE V_CURSQL;

UPDATE GFMIS_ALL.RECEIPTSTOCK_5D19A_TMP T SET T.ZTOF_STATUS = '0' WHERE T.STOCKID = v_STOCKID AND T.ZTOF_STATUS = '1';

DELETE FROM GFMIS.RECEIPTSTOCK WHERE STOCKID = v_STOCKID;

END IF;

END LOOP;

UPDATE GFMIS_ALL.RECEIPTSTOCK_5D19A_TMP T SET T.ZTOF_STATUS = '0' WHERE T.ZTOF_STATUS = '1' AND T.RECEIPTTYPE=11533;

COMMIT;

EXCEPTION

WHEN NO_DATA_FOUND THEN

NULL;

WHEN OTHERS THEN

CLOSE C_RECEIPTSTOCK_TMP;

dbms_output.put_line(SQLERRM);

IF C_RECEIPTSTOCK_TMP%ISOPEN THEN

CLOSE C_RECEIPTSTOCK_TMP;

END IF;

RAISE;

end PRO_RECEIPTSTOCK_5D19A;

DROP TRIGGER TRI_RECEIPTSTOCK_CHG_AFT;

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

--把票据类型为5DTB的缴款书在库存中更新成一般缴款书或者把指定的19个单位的库存移到分库里面

SELECT COUNT(1) INTO v_mycount FROM GFMIS_ALL.RECEIPTSTOCK_DISTRICT T WHERE T.UNITID = :NEW.UNITID;

IF (v_mycount > 0) THEN

SELECT T.DBUSER INTO v_dbuser FROM 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))) || ' AND RECEIPTTYPE = ' || :NEW.RECEIPTTYPE || ' AND STARTNO <= ''' || :NEW.STARTNO || ''' AND ENDNO >= ''' || :NEW.ENDNO || '';

EXECUTE IMMEDIATE curSQL INTO v_mycount;

if (v_mycount = 0) 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,v_dbuser,'1');

end if;

END IF;

IF (:NEW.RECEIPTTYPE = 11533) THEN

END IF;

select seq_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 FROM DUAL;

curTable := 'RECEIPTSTOCK';

if (deleting) then

v_distno:=substr(:OLD.STOCKID,2,2);

select dbuser into v_dbuser from GFMIS_ALL.DISTRICT where distno=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;

if (updating) then

v_distno:=substr(:OLD.STOCKID,2,2);

select dbuser into v_dbuser from GFMIS_ALL.DISTRICT where distno=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-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_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-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;

if (inserting) then

v_distno:=substr(:NEW.STOCKID,2,2);

select dbuser into v_dbuser from GFMIS_ALL.DISTRICT where distno=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-DD HH24: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

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;

EXCEPTION

WHEN OTHERS THEN

RAISE;

end TRI_RECEIPTSTOCK_CHG_AFT;

commit;

【注】：在每一个DROP语句后面不能加/，而在创建PROCEDURE和TRIGGER的之后必须加/符号，否则下面的脚本会

执行失败的。

执行txt和sql的脚本写法如下：

SQL> @D:\myoracle.txt;

Procedure dropped

Procedure created

Trigger dropped

Trigger created

Commit complete

SQL>

exec后面执行跟存储过程名或函数名[exec只能在命令行执行，call可以在任何环境下执行]。

借助别人的力量，我将走得更远。

发表于 2012-09-21 13:09 MR.CHEN 阅读(208) 评论(0) 编辑收藏

常用链接

留言簿

随笔档案

搜索

最新评论

阅读排行榜

评论排行榜