2.framework-sp_v001.sql

来自「CRM源码This file describes some issues tha」· SQL 代码 · 共 120 行

-- -- --
-- SP --
-- -- --


--
-- NEXT KEY
--

/*
 * Key allocation stored procedure (needed for custom records locking).
 * ====================================================================
 *
 * Call format: QX_NEXT_KEY(TABLE, INCREMENT) ==> NEXT_PRIMARY_KEY
 * NOTE. 
 * Procedure behavior depends on INCREMENT parameter value:
 *   INCREMENT = 1 - returns the next key value, 
 *   INCREMENT < 1 - returns the "fake" value (is NOT saved in table),
 *   INCREMENT > 1 - returns the value and "reserves" the range [OLD_VALUE..NEW_VALUE] of values.
 */
CREATE OR REPLACE  PROCEDURE QX_NEXT_KEY  (
a_input_table	VARCHAR2,
a_increment		INTEGER,
a_returnkey		OUT INTEGER
)
AS
    tablekey INTEGER;
    returnkey INTEGER;
    increment INTEGER;

    CURSOR cursor1 IS
    SELECT  keyvalue
    FROM    qx_keys
    WHERE   tablename = a_input_table
    FOR     UPDATE;

BEGIN
 IF ( a_increment <= 0 ) THEN
  increment := 1;
 ELSE
  increment := a_increment;
 END IF;

 OPEN cursor1;

 LOOP
  FETCH cursor1 INTO tablekey;
  IF ( cursor1%NOTFOUND ) THEN
    a_returnkey := increment;
    INSERT INTO qx_keys(tablename, keyvalue) VALUES ( a_input_table, a_returnkey );
  ELSE
    a_returnkey := tablekey + increment;
  UPDATE qx_keys SET keyvalue = a_returnkey WHERE CURRENT OF cursor1;
    END IF;
   EXIT;
  END LOOP;

  CLOSE cursor1;

END;
/

/*******************************
 CREATE TEMPORARY BLOB PROCEDURE
********************************/
CREATE OR REPLACE PROCEDURE GET_EMPTY_BLOB
(
	V_DATA          OUT NOCOPY BLOB
)
IS
BEGIN
  DBMS_LOB.CREATETEMPORARY( V_DATA, TRUE, DBMS_LOB.SESSION );
END;
/

/*******************************
 CREATE TEMPORARY CLOB PROCEDURE
********************************/
CREATE OR REPLACE PROCEDURE GET_EMPTY_CLOB
(
	V_DATA          OUT NOCOPY CLOB
)
IS
BEGIN
  DBMS_LOB.CREATETEMPORARY( V_DATA, TRUE, DBMS_LOB.SESSION );
END;
/

/********************************
 DESTROY TEMPORARY BLOB PROCEDURE
*********************************/
CREATE OR REPLACE PROCEDURE FREE_TMP_BLOB
(
	V_DATA          IN OUT NOCOPY BLOB
)
IS
BEGIN
  IF DBMS_LOB.ISTEMPORARY(V_DATA) = 1
  THEN
    DBMS_LOB.FREETEMPORARY(V_DATA);
  END IF;
END;
/

/********************************
 DESTROY TEMPORARY CLOB PROCEDURE
*********************************/
CREATE OR REPLACE PROCEDURE FREE_TMP_CLOB
(
	V_DATA          IN OUT NOCOPY CLOB
)
IS
BEGIN
  IF DBMS_LOB.ISTEMPORARY(V_DATA) = 1
  THEN
    DBMS_LOB.FREETEMPORARY(V_DATA);
  END IF;
END;
/