SQL> SET TERM ^ ;
SQL> CREATE PROCEDURE sp_get_user_orders (
CON> p_user_id INTEGER
CON> )
CON> RETURNS (
CON> order_id INTEGER,
CON> order_no VARCHAR(50),
CON> amount DECIMAL(12,2),
CON> status VARCHAR(20),
CON> created_at TIMESTAMP
CON> )
CON> AS
CON> BEGIN
CON> FOR SELECT order_id, order_no, amount, status, created_at
CON> FROM fgedu_orders
CON> WHERE user_id = :p_user_id
CON> INTO :order_id, :order_no, :amount, :status, :created_at
CON> DO
CON> BEGIN
CON> SUSPEND;
CON> END
CON> END^
SQL> SET TERM ; ^

SQL> SELECT * FROM sp_get_user_orders(1);

ORDER_ID ORDER_NO AMOUNT STATUS CREATED_AT
============ ========================================================= ============ ========================================================= ========================
1 ORD001 100.00 completed 2026-04-04 10:00:00.0000

SQL> COMMIT;
SQL> EXIT;

SQL> SET TERM ^ ;
SQL> CREATE TRIGGER trg_users_update FOR fgedu_users
CON> BEFORE UPDATE
CON> AS
CON> BEGIN
CON> NEW.updated_at = CURRENT_TIMESTAMP;
CON> END^
SQL> SET TERM ; ^

SQL> UPDATE fgedu_users SET email = ‘admin_new@fgedu.net.cn’ WHERE user_id = 1;

SQL> SELECT * FROM fgedu_users WHERE user_id = 1;

USER_ID USERNAME PASSWORD EMAIL CREATED_AT UPDATED_AT
============ ========================================================= ========================================================= ========================================================= ======================== ========================
1 admin password123 admin_new@fgedu.net.cn 2026-04-04 10:00:00.0000 2026-04-04 10:05:00.0000

SQL> COMMIT;
SQL> EXIT;

Database “/data/firebird/fgedudb.fdb”
Database header page information:
Flags 0
Checksum 12345
Generation 20
Page size 16384
ODS version 13.0
Oldest transaction 1
Oldest active 2
Oldest snapshot 2
Next transaction 10
Bumped transaction 1
Sequence number 0
Next attachment ID 5
Implementation ID 26
Shadow count 0
Page buffers 0
Next header page 0
Database dialect 3
Creation date Apr 4, 2026 10:00:00
Attributes force write

Analyzing database pages …

FGEDU_USERS (128)
Primary pointer page: 129, Index root page: 130
Data pages: 1, data page slots: 1, average fill: 50%
Fill distribution:
0 – 19% = 0
20 – 39% = 0
40 – 59% = 1
60 – 79% = 0
80 – 99% = 0

FGEDU_ORDERS (129)
Primary pointer page: 131, Index root page: 132
Data pages: 1, data page slots: 1, average fill: 0%
Fill distribution:
0 – 19% = 1
20 – 39% = 0
40 – 59% = 0
60 – 79% = 0
80 – 99% = 0

# 查看表统计信息
$ isql /data/firebird/fgedudb.fdb -user SYSDBA -password masterkey

SQL> SELECT
CON> RDB$RELATION_NAME AS TABLE_NAME,
CON> RDB$STATISTICS AS STATISTICS
CON> FROM RDB$INDICES
CON> WHERE RDB$RELATION_NAME = ‘FGEDU_USERS’;

TABLE_NAME STATISTICS
=================================================================================================== ===================
FGEDU_USERS 0.000000000
FGEDU_USERS 0.000000000

SQL> EXIT;

# 重建索引
$ isql /data/firebird/fgedudb.fdb -user SYSDBA -password masterkey

SQL> ALTER INDEX IDX_USERS_USERNAME INACTIVE;

SQL> ALTER INDEX IDX_USERS_USERNAME ACTIVE;

SQL> COMMIT;
SQL> EXIT;