Скрипт flookup.sql

-- *********************************************************************
-- Name: flookup.sql
-- Purpose:
-- Author: Vladimir Begun
-- $Id$
-- TODO: XXX

@@drop_stuff.sql

SET SCAN ON VERIFY OFF
SPOOL flookup.lst

-- Tablespace information
DEFINE ts_indx = indx

-- Categories
DEFINE category_limit = 1000000
DEFINE category_payment = 1
DEFINE category_currency = 2
DEFINE category_sex = 3

PROMPT Creation and populating the main lookup tables...

SET FEEDBACK OFF

PROMPT General sequence creation...
CREATE SEQUENCE seq$lookup
START WITH 1
INCREMENT BY 1
MAXVALUE &category_limit
/

PROMPT Table "category"...
CREATE TABLE category (
p NUMBER(2)
, name VARCHAR2(16)
, CONSTRAINT c$category$p PRIMARY KEY (p)
, CONSTRAINT c$category$name_u UNIQUE (name)
USING INDEX TABLESPACE &ts_indx STORAGE (MINEXTENTS 1 MAXEXTENTS 1 PCTINCREASE 0 INITIAL 16K NEXT 16K)
, CONSTRAINT c$category$name_nn CHECK (name IS NOT NULL)
)
ORGANIZATION INDEX TABLESPACE &ts_indx
STORAGE (MINEXTENTS 1 MAXEXTENTS 1 PCTINCREASE 0 INITIAL 16K NEXT 16K)
/

INSERT INTO category VALUES (&category_payment, 'PAYMENT');
INSERT INTO category VALUES (&category_currency, 'CURRENCY');
INSERT INTO category VALUES (&category_sex, 'SEX');

-- Languages
DEFINE l_russian = 1
DEFINE l_english = 2
DEFINE l_german = 3

PROMPT Table "lang"...
CREATE TABLE lang (
p NUMBER(1)
, name VARCHAR2(16)
, CONSTRAINT c$lang$p PRIMARY KEY (p)
, CONSTRAINT c$lang$name_u UNIQUE (name)
USING INDEX TABLESPACE &ts_indx STORAGE (MINEXTENTS 1 MAXEXTENTS 1 PCTINCREASE 0 INITIAL 16K NEXT 16K)
, CONSTRAINT c$lang$name_nn CHECK (name IS NOT NULL)
)
ORGANIZATION INDEX TABLESPACE &ts_indx
STORAGE (MINEXTENTS 1 MAXEXTENTS 1 PCTINCREASE 0 INITIAL 16K NEXT 16K)
/

INSERT INTO lang VALUES (&l_russian, 'Russian');
INSERT INTO lang VALUES (&l_english, 'English');
INSERT INTO lang VALUES (&l_german, 'German');

PROMPT Main lookup table for external references...
CREATE TABLE lookup (
p NUMBER
, CONSTRAINT c$lookup$p PRIMARY KEY (p)
)
ORGANIZATION INDEX TABLESPACE &ts_indx
STORAGE (MINEXTENTS 1 MAXEXTENTS 1 PCTINCREASE 0 INITIAL 16K NEXT 16K)
/

PROMPT Lookup values...
CREATE TABLE code_name (
fkey_category NUMBER(2) -- put your own data definition limits here
, fkey_language NUMBER(1)
, fkey_lookup NUMBER(10)
, name VARCHAR2(64)
, CONSTRAINT c$code_name$p PRIMARY KEY (fkey_category, fkey_lookup, fkey_language)
, CONSTRAINT c$code_name$fkey_category FOREIGN KEY (fkey_category) REFERENCES category(p)
, CONSTRAINT c$code_name$fkey_lookup FOREIGN KEY (fkey_lookup) REFERENCES lookup(p)
, CONSTRAINT c$code_name$fkey_language FOREIGN KEY (fkey_language) REFERENCES lang(p)
, CONSTRAINT c$code_name$proper_category CHECK (TRUNC(fkey_lookup / &category_limit) = fkey_category)
-- usually, lookup table has unique "value" for any category and language
, CONSTRAINT c$code_name$category_name_u UNIQUE (name, fkey_category, fkey_language)
USING INDEX TABLESPACE &ts_indx STORAGE (MINEXTENTS 1 MAXEXTENTS 1 PCTINCREASE 0 INITIAL 16K NEXT 16K)
)
ORGANIZATION INDEX TABLESPACE &ts_indx COMPRESS 2 -- PCTTHRESHOLD 2 OVERFLOW TABLESPACE &ts_indx
STORAGE (MINEXTENTS 1 MAXEXTENTS 1 PCTINCREASE 0 INITIAL 16K NEXT 16K)
/

PROMPT register_code_name procedure creation...
CREATE OR REPLACE FUNCTION register_code_name (
as_code_name_i code_name.name%TYPE
, an_language_i lang.p%TYPE
, an_category_i category.p%TYPE
, an_lookup_pkey_i lookup.p%TYPE DEFAULT NULL
)
RETURN NUMBER
IS
ln_lookup_pkey lookup.p%TYPE;
BEGIN
IF (an_lookup_pkey_i IS NULL)
THEN
INSERT INTO lookup (p)
VALUES (an_category_i * &category_limit + seq$lookup.NEXTVAL)
RETURNING p INTO ln_lookup_pkey
;
ELSE
ln_lookup_pkey := an_lookup_pkey_i;
END IF;
INSERT INTO code_name(fkey_category, fkey_language, fkey_lookup, name)
VALUES (an_category_i, an_language_i, ln_lookup_pkey, as_code_name_i)
;
RETURN ln_lookup_pkey;
END register_code_name;
/
SHOW ERRORS

PROMPT Populating of the main lookup table by various values...
VAR var_lookup_pkey NUMBER
EXEC :var_lookup_pkey := register_code_name('чИПДСЭЙК РМБФЕЦ', &l_russian, &category_payment);
EXEC :var_lookup_pkey := register_code_name('Inpayment', &l_english, &category_payment, :var_lookup_pkey);
EXEC :var_lookup_pkey := register_code_name('Eizahlung', &l_german, &category_payment, :var_lookup_pkey);
EXEC :var_lookup_pkey := register_code_name('йУИПДСЭЙК РМБФЕЦ', &l_russian, &category_payment);
EXEC :var_lookup_pkey := register_code_name('Outpayment', &l_english, &category_payment, :var_lookup_pkey);
EXEC :var_lookup_pkey := register_code_name('Auszahlung', &l_german, &category_payment, :var_lookup_pkey);
EXEC :var_lookup_pkey := register_code_name('пВТБФОЩК РМБФЕЦ', &l_russian, &category_payment);
EXEC :var_lookup_pkey := register_code_name('Backpayment', &l_english, &category_payment, :var_lookup_pkey);
EXEC :var_lookup_pkey := register_code_name('Rueckzahlung', &l_german, &category_payment, :var_lookup_pkey);

-- currency
EXEC :var_lookup_pkey := register_code_name('дПММБТ уыб', &l_russian, &category_currency);
EXEC :var_lookup_pkey := register_code_name('USD', &l_english, &category_currency, :var_lookup_pkey);
EXEC :var_lookup_pkey := register_code_name('ыЧЕКГБТУЛЙК ЖТБОЛ', &l_russian, &category_currency);
EXEC :var_lookup_pkey := register_code_name('CHF', &l_english, &category_currency, :var_lookup_pkey);
EXEC :var_lookup_pkey := register_code_name('еЧТП', &l_russian, &category_currency);
EXEC :var_lookup_pkey := register_code_name('EUR', &l_english, &category_currency, :var_lookup_pkey);
EXEC :var_lookup_pkey := register_code_name('хЛТБЙОУЛБС ЗТЙЧОС', &l_russian, &category_currency);
EXEC :var_lookup_pkey := register_code_name('UAH', &l_english, &category_currency, :var_lookup_pkey);
EXEC :var_lookup_pkey := register_code_name('тПУУЙКУЛЙК ТХВМШ', &l_russian, &category_currency);
EXEC :var_lookup_pkey := register_code_name('RUB', &l_english, &category_currency, :var_lookup_pkey);

-- sex
EXEC :var_lookup_pkey := register_code_name('н', &l_russian, &category_sex);
EXEC :var_lookup_pkey := register_code_name('M', &l_english, &category_sex, :var_lookup_pkey);
EXEC :var_lookup_pkey := register_code_name('M', &l_german, &category_sex, :var_lookup_pkey);
EXEC :var_lookup_pkey := register_code_name('ц', &l_russian, &category_sex);
EXEC :var_lookup_pkey := register_code_name('F', &l_english, &category_sex, :var_lookup_pkey);
EXEC :var_lookup_pkey := register_code_name('W', &l_german, &category_sex, :var_lookup_pkey);
COMMIT;

PROMPT CHECK: This one will fail due to unique values constraint...
EXEC :var_lookup_pkey := register_code_name('W', &l_german, &category_sex);
SET FEEDBACK ON

-- views
@@cr_v.sql
-- trigger
@@cr_trg.sql
-- how it looks
@@show_views.sql
-- main tables
@@main_tables.sql
-- check-up
@@check_up.sql
SPOOL OFF