【原】備忘：Oracle 中建立預存程序及調用測試一例

 

The procedure :

CREATE OR REPLACE PROCEDURE createSequence
      (
      the_seq OUT NUMBER
      )
      IS
      vs_prevyear  CHAR(4);
      vs_curyear  CHAR(4);
      vs_curseq NUMBER;

      BEGIN
      -- get the year of the latest created changelog record
      select max(to_char(datecreated,'yyyy')) into vs_prevyear from rtchangelog;
      -- get the current year of the database
      SELECT TO_CHAR(SYSDATE,'YYYY') INTO vs_curyear FROM DUAL;
      -- get  the sequence count from the db
      SELECT COUNT(*) INTO vs_curseq  from seq where sequence_name='SQ_CHANGELOG';
     
      IF(vs_curseq =0) THEN
      execute immediate  'create sequence sq_changelog increment by 1 start with 1';
      ELSE
      IF(vs_prevyear<>vs_curyear) THEN
      execute immediate 'drop sequence sq_changelog';
      execute immediate 'create sequence sq_changelog increment by 1 start with 1';
      END IF;
      END IF;
        execute immediate  'select sq_changelog.nextval from dual' into the_seq;
        commit;
      END;

Test sql for toad:

declare
v_num number;
begin
createSequence (v_num) ;
DBMS_OUTPUT.PUT_LINE(v_num);
END;
/

作者：Tony zhao 歡迎訪問:找與淘網