都道府県テーブル

JIS都道府県コード(を参考に)

SQL:CREATE TABLE
CREATE TABLE PREFECTURES
    (
    PREFECTURE_ID         NUMBER(2)     NOT NULL  -- 都道府県コード
  , PREFECTURE_NAME       VARCHAR2(12)  NOT NULL  -- 都道府県名
  , PREFECTURE_NAME_KANA  VARCHAR2(30)            -- 都道府県名カナ
  , CONSTRAINT PREFECTURE_PK PRIMARY KEY(PREFECTURE_ID) USING INDEX
 -- TABLESPACE は索引用表領域
    )
-- TABLESPACE はデータ用表領域
;

COMMENT ON COLUMN PREFECTURES.PREFECTURE_ID        IS '都道府県コード' ;
COMMENT ON COLUMN PREFECTURES.PREFECTURE_NAME      IS '都道府県名' ;
COMMENT ON COLUMN PREFECTURES.PREFECTURE_NAME_KANA IS '都道府県名カナ' ;
COMMENT ON TABLE  PREFECTURES IS '都道府県' ;

INSERT INTO PREFECTURES(PREFECTURE_ID, PREFECTURE_NAME, PREFECTURE_NAME_KANA) VALUES(01, '北海道'  , 'ホッカイドウ') ;
INSERT INTO PREFECTURES(PREFECTURE_ID, PREFECTURE_NAME, PREFECTURE_NAME_KANA) VALUES(02, '青森県'  , 'アオモリケン') ;
INSERT INTO PREFECTURES(PREFECTURE_ID, PREFECTURE_NAME, PREFECTURE_NAME_KANA) VALUES(03, '岩手県'  , 'イワテケン') ;
INSERT INTO PREFECTURES(PREFECTURE_ID, PREFECTURE_NAME, PREFECTURE_NAME_KANA) VALUES(04, '宮城県'  , 'ミヤギケン') ;
INSERT INTO PREFECTURES(PREFECTURE_ID, PREFECTURE_NAME, PREFECTURE_NAME_KANA) VALUES(05, '秋田県'  , 'アキタケン') ;
INSERT INTO PREFECTURES(PREFECTURE_ID, PREFECTURE_NAME, PREFECTURE_NAME_KANA) VALUES(06, '山形県'  , 'ヤマガタケン') ;
INSERT INTO PREFECTURES(PREFECTURE_ID, PREFECTURE_NAME, PREFECTURE_NAME_KANA) VALUES(07, '福島県'  , 'フクシマケン') ;
INSERT INTO PREFECTURES(PREFECTURE_ID, PREFECTURE_NAME, PREFECTURE_NAME_KANA) VALUES(08, '茨城県'  , 'イバラキケン') ;
INSERT INTO PREFECTURES(PREFECTURE_ID, PREFECTURE_NAME, PREFECTURE_NAME_KANA) VALUES(09, '栃木県'  , 'トチギケン') ;
INSERT INTO PREFECTURES(PREFECTURE_ID, PREFECTURE_NAME, PREFECTURE_NAME_KANA) VALUES(10, '群馬県'  , 'グンマケン') ;
INSERT INTO PREFECTURES(PREFECTURE_ID, PREFECTURE_NAME, PREFECTURE_NAME_KANA) VALUES(11, '埼玉県'  , 'サイタマケン') ;
INSERT INTO PREFECTURES(PREFECTURE_ID, PREFECTURE_NAME, PREFECTURE_NAME_KANA) VALUES(12, '千葉県'  , 'チバケン') ;
INSERT INTO PREFECTURES(PREFECTURE_ID, PREFECTURE_NAME, PREFECTURE_NAME_KANA) VALUES(13, '東京都'  , 'トウキョウト') ;
INSERT INTO PREFECTURES(PREFECTURE_ID, PREFECTURE_NAME, PREFECTURE_NAME_KANA) VALUES(14, '神奈川県', 'カナガワケン') ;
INSERT INTO PREFECTURES(PREFECTURE_ID, PREFECTURE_NAME, PREFECTURE_NAME_KANA) VALUES(15, '新潟県'  , 'ニイガタケン') ;
INSERT INTO PREFECTURES(PREFECTURE_ID, PREFECTURE_NAME, PREFECTURE_NAME_KANA) VALUES(16, '富山県'  , 'トヤマケン') ;
INSERT INTO PREFECTURES(PREFECTURE_ID, PREFECTURE_NAME, PREFECTURE_NAME_KANA) VALUES(17, '石川県'  , 'イシカワケン') ;
INSERT INTO PREFECTURES(PREFECTURE_ID, PREFECTURE_NAME, PREFECTURE_NAME_KANA) VALUES(18, '福井県'  , 'フクイケン',) ;
INSERT INTO PREFECTURES(PREFECTURE_ID, PREFECTURE_NAME, PREFECTURE_NAME_KANA) VALUES(19, '山梨県'  , 'ヤマナシケン') ;
INSERT INTO PREFECTURES(PREFECTURE_ID, PREFECTURE_NAME, PREFECTURE_NAME_KANA) VALUES(20, '長野県'  , 'ナガノケン') ;
INSERT INTO PREFECTURES(PREFECTURE_ID, PREFECTURE_NAME, PREFECTURE_NAME_KANA) VALUES(21, '岐阜県'  , 'ギフケン') ;
INSERT INTO PREFECTURES(PREFECTURE_ID, PREFECTURE_NAME, PREFECTURE_NAME_KANA) VALUES(22, '静岡県'  , 'シズオカケン') ;
INSERT INTO PREFECTURES(PREFECTURE_ID, PREFECTURE_NAME, PREFECTURE_NAME_KANA) VALUES(23, '愛知県'  , 'アイチケン') ;
INSERT INTO PREFECTURES(PREFECTURE_ID, PREFECTURE_NAME, PREFECTURE_NAME_KANA) VALUES(24, '三重県'  , 'ミエケン') ;
INSERT INTO PREFECTURES(PREFECTURE_ID, PREFECTURE_NAME, PREFECTURE_NAME_KANA) VALUES(25, '滋賀県'  , 'シガケン') ;
INSERT INTO PREFECTURES(PREFECTURE_ID, PREFECTURE_NAME, PREFECTURE_NAME_KANA) VALUES(26, '京都府'  , 'キョウトフ') ;
INSERT INTO PREFECTURES(PREFECTURE_ID, PREFECTURE_NAME, PREFECTURE_NAME_KANA) VALUES(27, '大阪府'  , 'オオサカフ') ;
INSERT INTO PREFECTURES(PREFECTURE_ID, PREFECTURE_NAME, PREFECTURE_NAME_KANA) VALUES(28, '兵庫県'  , 'ヒョウゴケン') ;
INSERT INTO PREFECTURES(PREFECTURE_ID, PREFECTURE_NAME, PREFECTURE_NAME_KANA) VALUES(29, '奈良県'  , 'ナラケン') ;
INSERT INTO PREFECTURES(PREFECTURE_ID, PREFECTURE_NAME, PREFECTURE_NAME_KANA) VALUES(30, '和歌山県', 'ワカヤマケン') ;
INSERT INTO PREFECTURES(PREFECTURE_ID, PREFECTURE_NAME, PREFECTURE_NAME_KANA) VALUES(31, '鳥取県'  , 'トットリケン') ;
INSERT INTO PREFECTURES(PREFECTURE_ID, PREFECTURE_NAME, PREFECTURE_NAME_KANA) VALUES(32, '島根県'  , 'シマネケン') ;
INSERT INTO PREFECTURES(PREFECTURE_ID, PREFECTURE_NAME, PREFECTURE_NAME_KANA) VALUES(33, '岡山県'  , 'オカヤマケン') ;
INSERT INTO PREFECTURES(PREFECTURE_ID, PREFECTURE_NAME, PREFECTURE_NAME_KANA) VALUES(34, '広島県'  , 'ヒロシマケン') ;
INSERT INTO PREFECTURES(PREFECTURE_ID, PREFECTURE_NAME, PREFECTURE_NAME_KANA) VALUES(35, '山口県'  , 'ヤマグチケン') ;
INSERT INTO PREFECTURES(PREFECTURE_ID, PREFECTURE_NAME, PREFECTURE_NAME_KANA) VALUES(36, '徳島県'  , 'トクシマケン') ;
INSERT INTO PREFECTURES(PREFECTURE_ID, PREFECTURE_NAME, PREFECTURE_NAME_KANA) VALUES(37, '香川県'  , 'カガワケン') ;
INSERT INTO PREFECTURES(PREFECTURE_ID, PREFECTURE_NAME, PREFECTURE_NAME_KANA) VALUES(38, '愛媛県'  , 'エヒメケン') ;
INSERT INTO PREFECTURES(PREFECTURE_ID, PREFECTURE_NAME, PREFECTURE_NAME_KANA) VALUES(39, '高知県'  , 'コウチケン') ;
INSERT INTO PREFECTURES(PREFECTURE_ID, PREFECTURE_NAME, PREFECTURE_NAME_KANA) VALUES(40, '福岡県'  , 'フクオカケン') ;
INSERT INTO PREFECTURES(PREFECTURE_ID, PREFECTURE_NAME, PREFECTURE_NAME_KANA) VALUES(41, '佐賀県'  , 'サガケン') ;
INSERT INTO PREFECTURES(PREFECTURE_ID, PREFECTURE_NAME, PREFECTURE_NAME_KANA) VALUES(42, '長崎県'  , 'ナガサキケン') ;
INSERT INTO PREFECTURES(PREFECTURE_ID, PREFECTURE_NAME, PREFECTURE_NAME_KANA) VALUES(43, '熊本県'  , 'クマモトケン') ;
INSERT INTO PREFECTURES(PREFECTURE_ID, PREFECTURE_NAME, PREFECTURE_NAME_KANA) VALUES(44, '大分県'  , 'オオイタケン') ;
INSERT INTO PREFECTURES(PREFECTURE_ID, PREFECTURE_NAME, PREFECTURE_NAME_KANA) VALUES(45, '宮崎県'  , 'ミヤザキケン') ;
INSERT INTO PREFECTURES(PREFECTURE_ID, PREFECTURE_NAME, PREFECTURE_NAME_KANA) VALUES(46, '鹿児島県', 'カゴシマケン') ;
INSERT INTO PREFECTURES(PREFECTURE_ID, PREFECTURE_NAME, PREFECTURE_NAME_KANA) VALUES(47, '沖縄県'  , 'オキナワケン') ;
-- 98 : 海外(JISにはない)
INSERT INTO PREFECTURES(PREFECTURE_ID, PREFECTURE_NAME, PREFECTURE_NAME_KANA) VALUES(98, '海外'    , 'カイガイ') ;
-- 99 : 住所不明(JISにはない)
INSERT INTO PREFECTURES(PREFECTURE_ID, PREFECTURE_NAME, PREFECTURE_NAME_KANA) VALUES(99, '住所不明', 'ジュウショフメイ') ;
COMMIT ;

実行権をPUBLICにし、インスタンスの全スキーマからSELECT可能に

GRANT SELECT ON PREFECTURES TO PUBLIC ;

PUBLIC SYNONYM に

CREATE OR REPLACE PUBLIC SYNONYM PREFECTURES FOR PREFECTURES ;

-- 一覧
SELECT PR.*
FROM PREFECTURES PR
ORDER BY
    PR.PREFECTURE_ID ;

-- 使用例
CREATE TABLE USERS
    (
    USER_ID        NUMBER(10)     NOT NULL  -- ユーザーID
  , USER_NAME      VARCHAR2(128)  NOT NULL  -- ユーザー名
  , PREFECTURE_ID  NUMBER(2)                -- 都道府県コード(後で設定する可能性がある、として)
  , CONSTRAINT USERS_PK PRIMARY KEY(USER_ID) USING INDEX
 -- TABLESPACE は索引用表領域
    )
-- TABLESPACE はデータ用表領域
;
-- INSERT は省略

SELECT
    US.USER_ID
  , US.USER_NAME
  , PR.PREFECTURE_NAME
FROM USERS US
LEFT OUTER JOIN PREFECTURES PR
ON  US.PREFECTURE_ID = PR.PREFECTURE_ID
ORDER BY
    US.USER_ID ;