create.sql

县级供电企业电费核算源码, 在客户处正常运行8年以上, Delphi 5开发,数据库为Interbase/Firebird, 深入使用Procedure和Trigger等, 对入门者具有很好的参考价值

/* Extract Database jym:D:\mis\data\smdljf.gdb */
CREATE DATABASE "jym:D:\mis\data\smdljf.gdb" PAGE_SIZE 1024 
;


/*  External Function declarations */
DECLARE EXTERNAL FUNCTION F_45 
DOUBLE PRECISION, INTEGER
RETURNS DOUBLE PRECISION BY VALUE 
ENTRY_POINT "fn_45" MODULE_NAME "udflib.dll";

DECLARE EXTERNAL FUNCTION F_ABS 
DOUBLE PRECISION
RETURNS DOUBLE PRECISION BY VALUE 
ENTRY_POINT "fn_abs" MODULE_NAME "udflib";

DECLARE EXTERNAL FUNCTION F_ADD2 
INTEGER, INTEGER
RETURNS INTEGER BY VALUE 
ENTRY_POINT "fn_add2" MODULE_NAME "udflib";

DECLARE EXTERNAL FUNCTION F_ASCII 
CSTRING(20) CHARACTER SET NONE
RETURNS INTEGER BY VALUE 
ENTRY_POINT "fn_ascii" MODULE_NAME "udflib";

DECLARE EXTERNAL FUNCTION F_BLOB_BYTECOUNT 
BLOB
RETURNS INTEGER BY VALUE 
ENTRY_POINT "fn_blob_bytecount" MODULE_NAME "udflib";

DECLARE EXTERNAL FUNCTION F_BLOB_LINECOUNT 
BLOB
RETURNS INTEGER BY VALUE 
ENTRY_POINT "fn_blob_linecount" MODULE_NAME "udflib";

DECLARE EXTERNAL FUNCTION F_BLOB_SUBSTR 
BLOB, INTEGER, INTEGER
RETURNS CSTRING(256) CHARACTER SET NONE  
ENTRY_POINT "fn_blob_substr" MODULE_NAME "udflib";

DECLARE EXTERNAL FUNCTION F_CHAR 
INTEGER
RETURNS CSTRING(2) CHARACTER SET NONE  
ENTRY_POINT "fn_char" MODULE_NAME "udflib";

DECLARE EXTERNAL FUNCTION F_DAY 
CSTRING(20) CHARACTER SET NONE
RETURNS INTEGER BY VALUE 
ENTRY_POINT "fn_day" MODULE_NAME "udflib";

DECLARE EXTERNAL FUNCTION F_DOW 

RETURNS CSTRING(12) CHARACTER SET NONE  
ENTRY_POINT "fn_dow" MODULE_NAME "udflib";

DECLARE EXTERNAL FUNCTION F_DOY 

RETURNS INTEGER BY VALUE 
ENTRY_POINT "fn_doy" MODULE_NAME "udflib";

DECLARE EXTERNAL FUNCTION F_FACT 
DOUBLE PRECISION
RETURNS DOUBLE PRECISION BY VALUE 
ENTRY_POINT "fn_fact" MODULE_NAME "udflib";

DECLARE EXTERNAL FUNCTION F_LOWER 
VARCHAR(256) CHARACTER SET NONE
RETURNS CSTRING(80) CHARACTER SET NONE  
ENTRY_POINT "fn_lower_c" MODULE_NAME "udflib";

DECLARE EXTERNAL FUNCTION F_MAXNUM 
DOUBLE PRECISION, DOUBLE PRECISION
RETURNS DOUBLE PRECISION BY VALUE 
ENTRY_POINT "fn_max" MODULE_NAME "udflib";

DECLARE EXTERNAL FUNCTION F_MONTH 
CSTRING(20) CHARACTER SET NONE
RETURNS INTEGER BY VALUE 
ENTRY_POINT "fn_month" MODULE_NAME "udflib";

DECLARE EXTERNAL FUNCTION F_MOY 

RETURNS SMALLINT  
ENTRY_POINT "fn_moy" MODULE_NAME "udflib";

DECLARE EXTERNAL FUNCTION F_MUL 
DOUBLE PRECISION, DOUBLE PRECISION
RETURNS DOUBLE PRECISION BY VALUE 
ENTRY_POINT "fn_mul" MODULE_NAME "udflib";

DECLARE EXTERNAL FUNCTION F_NEXTNUMBER 
VARCHAR(256) CHARACTER SET NONE
RETURNS CSTRING(80) CHARACTER SET NONE  
ENTRY_POINT "fn_nextnumber" MODULE_NAME "udflib";

DECLARE EXTERNAL FUNCTION F_REVERSE 
CSTRING(256) CHARACTER SET NONE
RETURNS CSTRING(80) CHARACTER SET NONE  
ENTRY_POINT "fn_reverse" MODULE_NAME "udflib";

DECLARE EXTERNAL FUNCTION F_SQRT 
DOUBLE PRECISION
RETURNS DOUBLE PRECISION  
ENTRY_POINT "fn_sqrt" MODULE_NAME "udflib";

DECLARE EXTERNAL FUNCTION F_STRCAT 
VARCHAR(255) CHARACTER SET NONE, VARCHAR(255) CHARACTER SET NONE
RETURNS CSTRING(80) CHARACTER SET NONE  
ENTRY_POINT "fn_strcat" MODULE_NAME "udflib";

DECLARE EXTERNAL FUNCTION F_STRLEN 
CSTRING(254) CHARACTER SET NONE
RETURNS INTEGER  
ENTRY_POINT "fn_strlen" MODULE_NAME "udflib.dll";

DECLARE EXTERNAL FUNCTION F_SUBSTR 
CSTRING(256) CHARACTER SET NONE, SMALLINT, SMALLINT
RETURNS CSTRING(80) CHARACTER SET NONE  
ENTRY_POINT "fn_substr" MODULE_NAME "udflib";

DECLARE EXTERNAL FUNCTION F_SYSDATE 

RETURNS CSTRING(12) CHARACTER SET NONE  
ENTRY_POINT "fn_sysdate" MODULE_NAME "udflib";

DECLARE EXTERNAL FUNCTION F_TRIM 
CSTRING(256) CHARACTER SET NONE
RETURNS CSTRING(80) CHARACTER SET NONE  
ENTRY_POINT "fn_trim" MODULE_NAME "udflib";

DECLARE EXTERNAL FUNCTION F_TRUNC 
CSTRING(256) CHARACTER SET NONE, SMALLINT
RETURNS VARCHAR(80) CHARACTER SET NONE  
ENTRY_POINT "fn_trunc" MODULE_NAME "udflib";

DECLARE EXTERNAL FUNCTION F_WARP 
VARCHAR(256) CHARACTER SET NONE, SMALLINT
RETURNS CSTRING(80) CHARACTER SET NONE  
ENTRY_POINT "fn_warp" MODULE_NAME "udflib";

DECLARE EXTERNAL FUNCTION F_YEAR 
CSTRING(20) CHARACTER SET NONE
RETURNS INTEGER BY VALUE 
ENTRY_POINT "fn_year" MODULE_NAME "udflib";

/* Domain definitions */
CREATE DOMAIN TP_BOOLEAN AS SMALLINT
         check((value=1) or (value =0) or (value is null));
CREATE DOMAIN TP_ID_S AS SMALLINT;
CREATE DOMAIN TP_ID_L AS INTEGER;
CREATE DOMAIN TP_COUNT AS INTEGER
         default 0
;
CREATE DOMAIN TP_COUNT_S AS SMALLINT
         default 0
;
CREATE DOMAIN TP_DEPT_NO AS CHAR(2);
CREATE DOMAIN TP_DEPT_NAME AS CHAR(20);
CREATE DOMAIN TP_PERSON_NO AS CHAR(6);
CREATE DOMAIN TP_PERSON_NAME AS CHAR(8)
         default ''
;
CREATE DOMAIN TP_PASSWORD AS CHAR(6)
         default ''
;
CREATE DOMAIN TP_MENU AS CHAR(20)
         default ''
;
CREATE DOMAIN TP_MONEY AS NUMERIC(15, 2)
         default 0
;
CREATE DOMAIN TP_PRICE AS NUMERIC(15, 2)
         default 0
;
CREATE DOMAIN TP_TELEPHONE AS CHAR(12)
         default ''
;
CREATE DOMAIN TP_ZIP AS CHAR(6)
         default ''
;
CREATE DOMAIN TP_ELECT_NO AS CHAR(2);
CREATE DOMAIN TP_ELECT_NAME AS CHAR(20)
         default ''
;
CREATE DOMAIN TP_TRADE_NO AS CHAR(2);
CREATE DOMAIN TP_TRADE_NAME AS CHAR(20);
CREATE DOMAIN TP_VOLTAGE_NO AS CHAR(2);
CREATE DOMAIN TP_VOLTAGE_NAME AS CHAR(20)
         default ''
;
CREATE DOMAIN TP_BASE_NO AS CHAR(2);
CREATE DOMAIN TP_BASE_NAME AS CHAR(20)
         default ''
;
CREATE DOMAIN TP_OTHER_NO AS CHAR(2);
CREATE DOMAIN TP_OTHER_NAME AS CHAR(20)
         default ''
;
CREATE DOMAIN TP_VOLUME_NO AS CHAR(1);
CREATE DOMAIN TP_VOLUME_NAME AS CHAR(20);
CREATE DOMAIN TP_STATION_NO AS CHAR(1);
CREATE DOMAIN TP_STATION_NAME AS CHAR(16)
         default ''
;
CREATE DOMAIN TP_LINE_NO AS CHAR(3);
CREATE DOMAIN TP_LINE_NAME AS CHAR(20)
         default ''
;
CREATE DOMAIN TP_TRANSFER_NO AS CHAR(6);
CREATE DOMAIN TP_TRANSFER_NAME AS CHAR(20)
         default ''
;
CREATE DOMAIN TP_HOUSE_NO AS CHAR(12);
CREATE DOMAIN TP_HOUSE_NAME AS CHAR(20);
CREATE DOMAIN TP_TOWN_NO AS CHAR(3);
CREATE DOMAIN TP_TOWN_NAME AS CHAR(20)
         default ''
;
CREATE DOMAIN TP_VILLAGE_NO AS CHAR(5);
CREATE DOMAIN TP_VILLAGE_NAME AS CHAR(20)
         default ''
;
CREATE DOMAIN TP_BOX_NO AS CHAR(6);
CREATE DOMAIN TP_AMMETER_MODEL AS CHAR(10);
CREATE DOMAIN TP_AMMETER_COUNT AS NUMERIC(15, 2);
CREATE DOMAIN TP_AMMETER_NO AS CHAR(10);
CREATE DOMAIN TP_AMMETER_POSTURE AS CHAR(6);
CREATE DOMAIN TP_AMMETER_AMPERE AS CHAR(10);
CREATE DOMAIN TP_FACTORY AS CHAR(10);
CREATE DOMAIN TP_RIGHT AS CHAR(10);
CREATE DOMAIN TP_CT_MODEL AS CHAR(10);
CREATE DOMAIN TP_CT_NO AS CHAR(10);
CREATE DOMAIN TP_RATE AS NUMERIC(15, 2);
CREATE DOMAIN TP_NOTE AS CHAR(50)
         default ''
;

/* Table: TB_AMMETER, Owner: SYSDBA */
CREATE TABLE TB_AMMETER (F_HOUSE_NO TP_HOUSE_NO NOT NULL,
        F_AMMETER_ORDER TP_COUNT  default 1
 NOT NULL,
        F_AMMETER_NO TP_AMMETER_NO,
        F_AMMETER_DATE DATE,
        F_AMMETER_COUNT TP_AMMETER_COUNT  default 0
 NOT NULL,
        F_AMMETER_LAST_COUNT TP_AMMETER_COUNT,
        F_THIS_COUNT TP_AMMETER_COUNT,
        F_AMMETER_MODEL TP_AMMETER_MODEL,
        F_AMMETER_RIGHT TP_RIGHT,
        F_CT_NO TP_CT_NO  default ''
,
        F_CT_MODEL TP_CT_MODEL,
        F_CT_RIGHT TP_RIGHT,
        F_COUNT01 TP_AMMETER_COUNT,
        F_COUNT02 TP_AMMETER_COUNT,
        F_COUNT03 TP_AMMETER_COUNT,
        F_COUNT04 TP_AMMETER_COUNT,
        F_COUNT05 TP_AMMETER_COUNT,
        F_COUNT06 TP_AMMETER_COUNT,
        F_COUNT07 TP_AMMETER_COUNT,
        F_COUNT08 TP_AMMETER_COUNT,
        F_COUNT09 TP_AMMETER_COUNT,
        F_COUNT10 TP_AMMETER_COUNT,
        F_COUNT11 TP_AMMETER_COUNT,
        F_COUNT12 TP_AMMETER_COUNT,
        F_LNO CHAR(4),
        F_COUNT NUMERIC(15, 2),
        F_LCONSTANT SMALLINT default 1
 NOT NULL,
        F_PLRATE SMALLINT default 1
 NOT NULL,
        F_TRANSFER_NO CHAR(6),
        F_OK SMALLINT default 0
 NOT NULL,
        F_COPYTIME CHAR(19),
        F_MAXCOUNT NUMERIC(15, 2) Default 0
 NOT NULL,
        F_MINCOUNT NUMERIC(15, 2) Default 9999
 NOT NULL,
PRIMARY KEY (F_HOUSE_NO, F_AMMETER_ORDER));

/* Table: TB_AMMETER_DETAIL, Owner: SYSDBA */
CREATE TABLE TB_AMMETER_DETAIL (F_DATE DATE NOT NULL,
        F_HOUSE_NO TP_HOUSE_NO NOT NULL,
        F_AMMETER_ORDER TP_COUNT NOT NULL,
        F_AMMETER_COUNT TP_AMMETER_COUNT,
        F_ADJUST_COUNT TP_AMMETER_COUNT  default 0
,
        F_NOTE VARCHAR(50),
PRIMARY KEY (F_DATE, F_HOUSE_NO, F_AMMETER_ORDER));

/* Table: TB_AMMETER_MODEL, Owner: SYSDBA */
CREATE TABLE TB_AMMETER_MODEL (F_AMMETER_MODEL TP_AMMETER_MODEL NOT NULL,
        F_AMMETER_POSTURE TP_AMMETER_POSTURE,
        F_AMMETER_AMPERE TP_AMMETER_AMPERE,
        F_AMMETER_FACTORY TP_FACTORY,
        F_AMMETER_TURN TP_AMMETER_COUNT  default 10000
,
PRIMARY KEY (F_AMMETER_MODEL));

/* Table: TB_CT_MODEL, Owner: SYSDBA */
CREATE TABLE TB_CT_MODEL (F_CT_MODEL TP_CT_MODEL NOT NULL,
        F_CT_FACTORY TP_FACTORY,
PRIMARY KEY (F_CT_MODEL));

/* Table: TB_CURRENT, Owner: SYSDBA */
CREATE TABLE TB_CURRENT (F_CLASS CHAR(10),
        F_VALUE CHAR(20));

/* Table: TB_CWCMETER, Owner: SYSDBA */
CREATE TABLE TB_CWCMETER (F_CENTERNO INTEGER NOT NULL,
        F_LNO CHAR(4) NOT NULL,
        F_BATCH INTEGER NOT NULL,
        F_COUNT INTEGER,
        F_FACTCOUNT NUMERIC(15, 2),
        F_COPYTIME CHAR(19),
        F_OK SMALLINT,
PRIMARY KEY (F_CENTERNO, F_LNO, F_BATCH));

/* Table: TB_ELECT, Owner: SYSDBA */
CREATE TABLE TB_ELECT (F_ELECT_NO TP_ELECT_NO NOT NULL,
        F_ELECT_NAME TP_ELECT_NAME,
        F_TRADE_NO TP_TRADE_NO,
        F_COUNTRY_PRICE TP_PRICE,
        F_SERVICE_PRICE TP_PRICE,
        F_PRICE TP_PRICE,
PRIMARY KEY (F_ELECT_NO));

/* Table: TB_FEE, Owner: SYSDBA */
CREATE TABLE TB_FEE (F_HOUSE_NO TP_HOUSE_NO NOT NULL,
        F_HOUSE_NAME TP_HOUSE_NAME,
        F_TRANSFER_NO TP_TRANSFER_NO,
        F_COUNT1 TP_AMMETER_COUNT  default 0
,
        F_COUNTRYAMOUNT1 TP_MONEY  default 0
,
        F_SERVICEAMOUNT1 TP_MONEY  default 0
,
        F_AMOUNT1 TP_MONEY  default 0
,
        F_COUNT2 TP_AMMETER_COUNT  default 0
,
        F_COUNTRYAMOUNT2 TP_MONEY  default 0
,
        F_SERVICEAMOUNT2 TP_MONEY  default 0
,
        F_AMOUNT2 TP_MONEY  default 0
,
        F_COUNT3 TP_AMMETER_COUNT  default 0
,
        F_COUNTRYAMOUNT3 TP_MONEY  default 0
,
        F_SERVICEAMOUNT3 TP_MONEY  default 0
,
        F_AMOUNT3 TP_MONEY  default 0
,
        F_COUNT4 TP_AMMETER_COUNT  default 0
,
        F_COUNTRYAMOUNT4 TP_MONEY  default 0
,
        F_SERVICEAMOUNT4 TP_MONEY  default 0
,
        F_AMOUNT4 TP_MONEY  default 0
,
        F_COUNT5 TP_AMMETER_COUNT  default 0
,
        F_COUNTRYAMOUNT5 TP_MONEY  default 0
,
        F_SERVICEAMOUNT5 TP_MONEY  default 0
,
        F_AMOUNT5 TP_MONEY  default 0
,
        F_COUNT TP_AMMETER_COUNT  default 0
,
        F_COUNTRYAMOUNT TP_MONEY  default 0
,
        F_SERVICEAMOUNT TP_MONEY  default 0
,
        F_AMOUNT TP_MONEY  default 0
,
        F_ARREARAGE TP_MONEY,
        F_LATEFEE TP_MONEY,
PRIMARY KEY (F_HOUSE_NO));

/* Table: TB_HELP, Owner: SYSDBA */
CREATE TABLE TB_HELP (F_CLASS CHAR(16) NOT NULL,
        F_TOPIC CHAR(30) NOT NULL,
        F_TEXT BLOB SUB_TYPE 0 SEGMENT SIZE 80,
PRIMARY KEY (F_CLASS, F_TOPIC));

/* Table: TB_HOUSE, Owner: SYSDBA */
CREATE TABLE TB_HOUSE (F_HOUSE_NO TP_HOUSE_NO NOT NULL,
        F_TRANSFER_NO TP_TRANSFER_NO,
        F_VOLUME_NO TP_VOLUME_NO,
        F_VILLAGE_NO TP_VILLAGE_NO,
        F_HOUSE_NAME TP_HOUSE_NAME,
        F_POSITION CHAR(10),
        F_TELEPHONE TP_TELEPHONE,
        F_VOLTAGE_NO TP_VOLTAGE_NO NOT NULL,
        F_MASTER_AMMETER_NO TP_AMMETER_NO  default ''
,
        F_BOX_NO TP_BOX_NO,
        F_DATE DATE,
        F_ADJUST_COUNT TP_AMMETER_COUNT  default 0
,
        F_SON_COUNT TP_AMMETER_COUNT  default 0
,
        F_SUM_COUNT TP_AMMETER_COUNT,
        F_BASE_COUNT TP_AMMETER_COUNT  default 0