# 玩家表
CREATE TABLE PLAYER (
PLAYER_ID VARCHAR(32) NOT NULL,
USER_ID VARCHAR(32) NOT NULL,
NICKNAME VARCHAR(100) NOT NULL,
LEVEL INTEGER DEFAULT 1,
EXP BIGINT DEFAULT 0,
VIP_LEVEL INTEGER DEFAULT 0,
GOLD BIGINT DEFAULT 0,
DIAMOND BIGINT DEFAULT 0,
AVATAR VARCHAR(500),
REGISTER_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
LAST_LOGIN_TIME TIMESTAMP,
LAST_LOGOUT_TIME TIMESTAMP,
TOTAL_LOGIN_TIME BIGINT DEFAULT 0,
TOTAL_ONLINE_TIME BIGINT DEFAULT 0,
PLAYER_STATUS VARCHAR(10) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_PLAYER PRIMARY KEY (PLAYER_ID)
) ORGANIZE BY ROW;

# 玩家角色表
CREATE TABLE PLAYER_CHARACTER (
CHARACTER_ID VARCHAR(32) NOT NULL,
PLAYER_ID VARCHAR(32) NOT NULL,
CHARACTER_NAME VARCHAR(100) NOT NULL,
CHARACTER_CLASS VARCHAR(50),
LEVEL INTEGER DEFAULT 1,
EXP BIGINT DEFAULT 0,
HP INTEGER DEFAULT 100,
MP INTEGER DEFAULT 100,
MAX_HP INTEGER DEFAULT 100,
MAX_MP INTEGER DEFAULT 100,
ATTACK INTEGER DEFAULT 10,
DEFENSE INTEGER DEFAULT 10,
SPEED INTEGER DEFAULT 10,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_PLAYER_CHARACTER PRIMARY KEY (CHARACTER_ID)
) ORGANIZE BY ROW;

# 创建索引
CREATE UNIQUE INDEX IDX_PLAYER_USER_ID ON PLAYER(USER_ID);
CREATE INDEX IDX_PLAYER_NICKNAME ON PLAYER(NICKNAME);
CREATE INDEX IDX_PLAYER_LEVEL ON PLAYER(LEVEL DESC, EXP DESC);
CREATE INDEX IDX_PLAYER_CHARACTER_PLAYER ON PLAYER_CHARACTER(PLAYER_ID);

# 道具表
CREATE TABLE ITEM (
ITEM_ID VARCHAR(32) NOT NULL,
ITEM_CODE VARCHAR(50) NOT NULL,
ITEM_NAME VARCHAR(200) NOT NULL,
ITEM_TYPE VARCHAR(20) NOT NULL,
ITEM_CATEGORY VARCHAR(50),
ITEM_QUALITY VARCHAR(20),
ITEM_DESC VARCHAR(500),
SELL_PRICE BIGINT,
BUY_PRICE BIGINT,
STACKABLE CHAR(1) DEFAULT ‘Y’,
MAX_STACK INTEGER DEFAULT 99,
BIND_TYPE VARCHAR(20),
ITEM_STATUS VARCHAR(10) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_ITEM PRIMARY KEY (ITEM_ID)
) ORGANIZE BY ROW;

# 玩家背包表
CREATE TABLE PLAYER_BAG (
BAG_ID VARCHAR(32) NOT NULL,
PLAYER_ID VARCHAR(32) NOT NULL,
BAG_TYPE VARCHAR(20) NOT NULL,
BAG_CAPACITY INTEGER DEFAULT 50,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_PLAYER_BAG PRIMARY KEY (BAG_ID)
) ORGANIZE BY ROW;

# 玩家道具表
CREATE TABLE PLAYER_ITEM (
RECORD_ID VARCHAR(32) NOT NULL,
PLAYER_ID VARCHAR(32) NOT NULL,
BAG_ID VARCHAR(32) NOT NULL,
ITEM_ID VARCHAR(32) NOT NULL,
QUANTITY INTEGER NOT NULL DEFAULT 1,
BAG_POSITION INTEGER,
BIND_STATUS VARCHAR(20),
EXPIRE_TIME TIMESTAMP,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_PLAYER_ITEM PRIMARY KEY (RECORD_ID)
) ORGANIZE BY ROW;

# 创建索引
CREATE UNIQUE INDEX IDX_ITEM_CODE ON ITEM(ITEM_CODE);
CREATE INDEX IDX_ITEM_TYPE ON ITEM(ITEM_TYPE, ITEM_STATUS);
CREATE INDEX IDX_PLAYER_BAG_PLAYER ON PLAYER_BAG(PLAYER_ID, BAG_TYPE);
CREATE INDEX IDX_PLAYER_ITEM_PLAYER ON PLAYER_ITEM(PLAYER_ID, BAG_ID);
CREATE INDEX IDX_PLAYER_ITEM_ITEM ON PLAYER_ITEM(ITEM_ID);

# 交易表
CREATE TABLE TRANSACTION (
TRANSACTION_ID VARCHAR(32) NOT NULL,
TRANSACTION_TYPE VARCHAR(20) NOT NULL,
PLAYER_ID_FROM VARCHAR(32),
PLAYER_ID_TO VARCHAR(32),
ITEM_ID VARCHAR(32),
QUANTITY INTEGER,
GOLD_AMOUNT BIGINT,
DIAMOND_AMOUNT BIGINT,
TRANSACTION_STATUS VARCHAR(20) NOT NULL,
TRANSACTION_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_TRANSACTION PRIMARY KEY (TRANSACTION_ID)
) ORGANIZE BY ROW;

# 玩家货币变更记录表
CREATE TABLE CURRENCY_LOG (
LOG_ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
PLAYER_ID VARCHAR(32) NOT NULL,
CURRENCY_TYPE VARCHAR(20) NOT NULL,
CHANGE_TYPE VARCHAR(20) NOT NULL,
AMOUNT_BEFORE BIGINT NOT NULL,
AMOUNT_CHANGE BIGINT NOT NULL,
AMOUNT_AFTER BIGINT NOT NULL,
RELATED_ID VARCHAR(32),
RELATED_TYPE VARCHAR(20),
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_CURRENCY_LOG PRIMARY KEY (LOG_ID)
) PARTITION BY RANGE (CREATE_TIME)
(
STARTING ‘2026-01-01-00.00.00.000000’
ENDING ‘2026-12-31-23.59.59.999999’
EVERY 1 DAY
);

# 创建索引
CREATE INDEX IDX_TRANSACTION_PLAYER_FROM ON TRANSACTION(PLAYER_ID_FROM, TRANSACTION_TIME DESC);
CREATE INDEX IDX_TRANSACTION_PLAYER_TO ON TRANSACTION(PLAYER_ID_TO, TRANSACTION_TIME DESC);
CREATE INDEX IDX_CURRENCY_LOG_PLAYER ON CURRENCY_LOG(PLAYER_ID, CREATE_TIME DESC) PARTITIONED;

# 道具交易存储过程
CREATE OR REPLACE PROCEDURE SP_ITEM_TRANSACTION(
IN p_player_id_from VARCHAR(32),
IN p_player_id_to VARCHAR(32),
IN p_item_id VARCHAR(32),
IN p_quantity INTEGER,
IN p_gold_amount BIGINT,
OUT p_result_code VARCHAR(10),
OUT p_result_msg VARCHAR(200)
)
LANGUAGE SQL
BEGIN
DECLARE v_gold_from BIGINT;
DECLARE v_item_count INTEGER;
DECLARE v_transaction_id VARCHAR(32);

SET p_result_code = ‘SUCCESS’;
SET p_result_msg = ‘交易成功’;
SET v_transaction_id = ‘TXN’ || TO_CHAR(CURRENT TIMESTAMP, ‘YYYYMMDDHH24MISSFF6’);

START TRANSACTION;

SELECT GOLD INTO v_gold_from
FROM PLAYER
WHERE PLAYER_ID = p_player_id_from
WITH RS USE AND KEEP UPDATE LOCKS;

IF v_gold_from < p_gold_amount THEN SET p_result_code = 'FAIL'; SET p_result_msg = '金币不足'; ROLLBACK; RETURN; END IF; SELECT COUNT(*) INTO v_item_count FROM PLAYER_ITEM WHERE PLAYER_ID = p_player_id_to AND ITEM_ID = p_item_id; IF v_item_count < p_quantity THEN SET p_result_code = 'FAIL'; SET p_result_msg = '道具不足'; ROLLBACK; RETURN; END IF; UPDATE PLAYER SET GOLD = GOLD - p_gold_amount, UPDATE_TIME = CURRENT TIMESTAMP WHERE PLAYER_ID = p_player_id_from; INSERT INTO CURRENCY_LOG ( PLAYER_ID, CURRENCY_TYPE, CHANGE_TYPE, AMOUNT_BEFORE, AMOUNT_CHANGE, AMOUNT_AFTER, RELATED_ID, RELATED_TYPE ) VALUES ( p_player_id_from, 'GOLD', 'SPEND', v_gold_from, -p_gold_amount, v_gold_from - p_gold_amount, v_transaction_id, 'TRANSACTION' ); UPDATE PLAYER SET GOLD = GOLD + p_gold_amount, UPDATE_TIME = CURRENT TIMESTAMP WHERE PLAYER_ID = p_player_id_to; SELECT GOLD INTO v_gold_from FROM PLAYER WHERE PLAYER_ID = p_player_id_to; INSERT INTO CURRENCY_LOG ( PLAYER_ID, CURRENCY_TYPE, CHANGE_TYPE, AMOUNT_BEFORE, AMOUNT_CHANGE, AMOUNT_AFTER, RELATED_ID, RELATED_TYPE ) VALUES ( p_player_id_to, 'GOLD', 'EARN', v_gold_from - p_gold_amount, p_gold_amount, v_gold_from, v_transaction_id, 'TRANSACTION' ); UPDATE PLAYER_ITEM SET PLAYER_ID = p_player_id_from, UPDATE_TIME = CURRENT TIMESTAMP WHERE PLAYER_ID = p_player_id_to AND ITEM_ID = p_item_id FETCH FIRST p_quantity ROWS ONLY; INSERT INTO TRANSACTION ( TRANSACTION_ID, TRANSACTION_TYPE, PLAYER_ID_FROM, PLAYER_ID_TO, ITEM_ID, QUANTITY, GOLD_AMOUNT, TRANSACTION_STATUS ) VALUES ( v_transaction_id, 'ITEM_TRADE', p_player_id_from, p_player_id_to, p_item_id, p_quantity, p_gold_amount, 'SUCCESS' ); COMMIT; END;

# 排行榜表
CREATE TABLE RANKING (
RANKING_ID VARCHAR(32) NOT NULL,
RANKING_TYPE VARCHAR(20) NOT NULL,
RANKING_DATE DATE NOT NULL,
PLAYER_ID VARCHAR(32) NOT NULL,
RANK_VALUE BIGINT NOT NULL,
RANKING_POSITION INTEGER NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_RANKING PRIMARY KEY (RANKING_ID)
) ORGANIZE BY ROW;

# 创建索引
CREATE UNIQUE INDEX IDX_RANKING_TYPE_DATE ON RANKING(RANKING_TYPE, RANKING_DATE, PLAYER_ID);
CREATE INDEX IDX_RANKING_TYPE_RANK ON RANKING(RANKING_TYPE, RANKING_DATE, RANKING_POSITION);

# 等级排行榜查询
SELECT
p.PLAYER_ID,
p.NICKNAME,
p.LEVEL,
p.EXP,
RANK() OVER (ORDER BY p.LEVEL DESC, p.EXP DESC) AS RANKING
FROM PLAYER p
WHERE p.PLAYER_STATUS = ‘ACTIVE’
ORDER BY p.LEVEL DESC, p.EXP DESC
FETCH FIRST 100 ROWS ONLY;

# 金币排行榜
SELECT
p.PLAYER_ID,
p.NICKNAME,
p.GOLD,
RANK() OVER (ORDER BY p.GOLD DESC) AS RANKING
FROM PLAYER p
WHERE p.PLAYER_STATUS = ‘ACTIVE’
ORDER BY p.GOLD DESC
FETCH FIRST 100 ROWS ONLY;

# 更新排行榜存储过程
CREATE OR REPLACE PROCEDURE SP_UPDATE_RANKING(
IN p_ranking_type VARCHAR(20),
IN p_ranking_date DATE
)
LANGUAGE SQL
BEGIN
DECLARE v_rank INTEGER DEFAULT 0;

DELETE FROM RANKING
WHERE RANKING_TYPE = p_ranking_type AND RANKING_DATE = p_ranking_date;

IF p_ranking_type = ‘LEVEL’ THEN
INSERT INTO RANKING (
RANKING_ID, RANKING_TYPE, RANKING_DATE,
PLAYER_ID, RANK_VALUE, RANKING_POSITION
)
SELECT
‘RANK’ || TO_CHAR(CURRENT TIMESTAMP, ‘YYYYMMDDHH24MISSFF6’) || CHAR(ROW_NUMBER() OVER ()),
p_ranking_type,
p_ranking_date,
PLAYER_ID,
LEVEL * 1000000000 + EXP,
ROW_NUMBER() OVER (ORDER BY LEVEL DESC, EXP DESC)
FROM PLAYER
WHERE PLAYER_STATUS = ‘ACTIVE’
ORDER BY LEVEL DESC, EXP DESC
FETCH FIRST 1000 ROWS ONLY;
END IF;

IF p_ranking_type = ‘GOLD’ THEN
INSERT INTO RANKING (
RANKING_ID, RANKING_TYPE, RANKING_DATE,
PLAYER_ID, RANK_VALUE, RANKING_POSITION
)
SELECT
‘RANK’ || TO_CHAR(CURRENT TIMESTAMP, ‘YYYYMMDDHH24MISSFF6’) || CHAR(ROW_NUMBER() OVER ()),
p_ranking_type,
p_ranking_date,
PLAYER_ID,
GOLD,
ROW_NUMBER() OVER (ORDER BY GOLD DESC)
FROM PLAYER
WHERE PLAYER_STATUS = ‘ACTIVE’
ORDER BY GOLD DESC
FETCH FIRST 1000 ROWS ONLY;
END IF;

END;