2.framework-sp_v001.sql

CRM源码This file describes some issues that should be implemented in future and how it should be imple

DELIMITER //

-- USE qwoss
-- //

DROP PROCEDURE IF EXISTS QX_NEXT_KEY
//


CREATE PROCEDURE QX_NEXT_KEY(TBL VARCHAR(240),
			       INCREMENT INT,
			       OUT RET_KEY INT)
    BEGIN
	DECLARE INCR INT;
	DECLARE DOCOMMIT TINYINT(1);
	DECLARE TBL_NAME VARCHAR(240) DEFAULT NULL;
	DECLARE N INT;

	IF INCREMENT <= 0 THEN 
		SET INCR = 1;
		SET DOCOMMIT = 0;
	ELSE 
		SET INCR = INCREMENT;
		SET DOCOMMIT = 1;
	END IF;
	
	SET AUTOCOMMIT=0;

	START TRANSACTION;
		
		SELECT COUNT(TABLENAME)
		FROM QX_KEYS
		WHERE QX_KEYS.TABLENAME = TBL
		INTO N;
		
		IF N = 0 THEN 
			-- Not found - add a new table/key pair.
			INSERT INTO QX_KEYS(TABLENAME, KEYVALUE) VALUES (TBL, INCR);
		ELSE
			-- Set a new key value.
			UPDATE QX_KEYS
			SET QX_KEYS.KEYVALUE = QX_KEYS.KEYVALUE + INCR 
			WHERE QX_KEYS.TABLENAME = TBL;
		END IF;
		
		-- Return the old key value + 1.
		-- It reserves the range [OLD_VALUE..NEW_VALUE] of values, if INCR > 1.
		SELECT QX_KEYS.KEYVALUE - INCR + 1 INTO RET_KEY
		FROM QX_KEYS
		WHERE QX_KEYS.TABLENAME = TBL;

	IF DOCOMMIT = 1 THEN
		COMMIT;
	ELSE 
		ROLLBACK;
	END IF;
	
	SET AUTOCOMMIT=1;

    END
//