{"id":783,"date":"2023-10-05T12:29:12","date_gmt":"2023-10-05T16:29:12","guid":{"rendered":"https:\/\/ve2cuy.com\/?page_id=783"},"modified":"2024-09-18T10:08:23","modified_gmt":"2024-09-18T14:08:23","slug":"420-5d5-oracle-labo-example","status":"publish","type":"page","link":"https:\/\/ve2cuy.com\/index.php\/420-5d5-oracle-labo-example\/","title":{"rendered":"420-5D5 : Oracle – Labo.example"},"content":{"rendered":"\n

Contexte<\/h2>\n\n\n\n

Oracle.Tutorial fournit une base de donn\u00e9es ‘Exemple’ pour l’exp\u00e9rimentation avec le SGBD Oracle. <\/p>\n\n\n\n

Nous utiliserons cette DB pour les apprentissages sur les fonctions, proc\u00e9dures, d\u00e9clencheurs, …<\/p>\n\n\n\n

Les directives pour l’installation de la BD sont disponibles ici<\/a><\/p>\n\n\n\n

\n\n\n\n

Voici le sch\u00e9ma de la base de donn\u00e9es<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n
\n\n\n\n

Laboratoire partie A<\/h2>\n\n\n\n

Il faut cr\u00e9er, sur le SGBD \u00e0 192.168.138.58 (user:sys, pws: 4205D3) une PDB nomm\u00e9 ‘M’+ votre matricule, avec un utilisateur nomm\u00e9 ‘ot’.<\/p>\n\n\n\n

-- Par exemple,\nshow con_name;\n-- Si pas au contexte racine alors:\nALTER SESSION SET CONTAINER = CDB$ROOT\n\nCREATE PLUGGABLE DATABASE M12345678 ADMIN ot IDENTIFIED BY password create_file_dest='\/home\/oracle' ;\nshow pdbs;\nALTER SESSION SET CONTAINER = M12345678 ;\nALTER DATABASE OPEN;\nGRANT CONNECT, RESOURCE, DBA TO ot;\n\n-- Puis, ouvrir une nouvelle connexion avec l'utilisateur 'ot' \n<\/pre>\n\n\n\n
\n\n\n\n
Cr\u00e9er le sch\u00e9ma de la base de donn\u00e9es<\/h2>\n\n\n\n
-- regions\nCREATE TABLE regions\n  (\n    region_id NUMBER GENERATED BY DEFAULT AS IDENTITY\n    START WITH 5 PRIMARY KEY,\n    region_name VARCHAR2( 50 ) NOT NULL\n  );\n-- countries table\nCREATE TABLE countries\n  (\n    country_id   CHAR( 2 ) PRIMARY KEY  ,\n    country_name VARCHAR2( 40 ) NOT NULL,\n    region_id    NUMBER                 , -- fk\n    CONSTRAINT fk_countries_regions FOREIGN KEY( region_id )\n      REFERENCES regions( region_id ) \n      ON DELETE CASCADE\n  );\n\n-- location\nCREATE TABLE locations\n  (\n    location_id NUMBER GENERATED BY DEFAULT AS IDENTITY START WITH 24 \n                PRIMARY KEY       ,\n    address     VARCHAR2( 255 ) NOT NULL,\n    postal_code VARCHAR2( 20 )          ,\n    city        VARCHAR2( 50 )          ,\n    state       VARCHAR2( 50 )          ,\n    country_id  CHAR( 2 )               , -- fk\n    CONSTRAINT fk_locations_countries \n      FOREIGN KEY( country_id )\n      REFERENCES countries( country_id ) \n      ON DELETE CASCADE\n  );\n-- warehouses\nCREATE TABLE warehouses\n  (\n    warehouse_id NUMBER \n                 GENERATED BY DEFAULT AS IDENTITY START WITH 10 \n                 PRIMARY KEY,\n    warehouse_name VARCHAR( 255 ) ,\n    location_id    NUMBER( 12, 0 ), -- fk\n    CONSTRAINT fk_warehouses_locations \n      FOREIGN KEY( location_id )\n      REFERENCES locations( location_id ) \n      ON DELETE CASCADE\n  );\n-- employees\nCREATE TABLE employees\n  (\n    employee_id NUMBER \n                GENERATED BY DEFAULT AS IDENTITY START WITH 108 \n                PRIMARY KEY,\n    first_name VARCHAR( 255 ) NOT NULL,\n    last_name  VARCHAR( 255 ) NOT NULL,\n    email      VARCHAR( 255 ) NOT NULL,\n    phone      VARCHAR( 50 ) NOT NULL ,\n    hire_date  DATE NOT NULL          ,\n    manager_id NUMBER( 12, 0 )        , -- fk\n    job_title  VARCHAR( 255 ) NOT NULL,\n    CONSTRAINT fk_employees_manager \n        FOREIGN KEY( manager_id )\n        REFERENCES employees( employee_id )\n        ON DELETE CASCADE\n  );\n-- product category\nCREATE TABLE product_categories\n  (\n    category_id NUMBER \n                GENERATED BY DEFAULT AS IDENTITY START WITH 6 \n                PRIMARY KEY,\n    category_name VARCHAR2( 255 ) NOT NULL\n  );\n\n-- products table\nCREATE TABLE products\n  (\n    product_id NUMBER \n               GENERATED BY DEFAULT AS IDENTITY START WITH 289 \n               PRIMARY KEY,\n    product_name  VARCHAR2( 255 ) NOT NULL,\n    description   VARCHAR2( 2000 )        ,\n    standard_cost NUMBER( 9, 2 )          ,\n    list_price    NUMBER( 9, 2 )          ,\n    category_id   NUMBER NOT NULL         ,\n    CONSTRAINT fk_products_categories \n      FOREIGN KEY( category_id )\n      REFERENCES product_categories( category_id ) \n      ON DELETE CASCADE\n  );\n-- customers\nCREATE TABLE customers\n  (\n    customer_id NUMBER \n                GENERATED BY DEFAULT AS IDENTITY START WITH 320 \n                PRIMARY KEY,\n    name         VARCHAR2( 255 ) NOT NULL,\n    address      VARCHAR2( 255 )         ,\n    website      VARCHAR2( 255 )         ,\n    credit_limit NUMBER( 8, 2 )\n  );\n-- contacts\nCREATE TABLE contacts\n  (\n    contact_id NUMBER \n               GENERATED BY DEFAULT AS IDENTITY START WITH 320 \n               PRIMARY KEY,\n    first_name  VARCHAR2( 255 ) NOT NULL,\n    last_name   VARCHAR2( 255 ) NOT NULL,\n    email       VARCHAR2( 255 ) NOT NULL,\n    phone       VARCHAR2( 20 )          ,\n    customer_id NUMBER                  ,\n    CONSTRAINT fk_contacts_customers \n      FOREIGN KEY( customer_id )\n      REFERENCES customers( customer_id ) \n      ON DELETE CASCADE\n  );\n-- orders table\nCREATE TABLE orders\n  (\n    order_id NUMBER \n             GENERATED BY DEFAULT AS IDENTITY START WITH 106 \n             PRIMARY KEY,\n    customer_id NUMBER( 6, 0 ) NOT NULL, -- fk\n    status      VARCHAR( 20 ) NOT NULL ,\n    salesman_id NUMBER( 6, 0 )         , -- fk\n    order_date  DATE NOT NULL          ,\n    CONSTRAINT fk_orders_customers \n      FOREIGN KEY( customer_id )\n      REFERENCES customers( customer_id )\n      ON DELETE CASCADE,\n    CONSTRAINT fk_orders_employees \n      FOREIGN KEY( salesman_id )\n      REFERENCES employees( employee_id ) \n      ON DELETE SET NULL\n  );\n-- order items\nCREATE TABLE order_items\n  (\n    order_id   NUMBER( 12, 0 )                                , -- fk\n    item_id    NUMBER( 12, 0 )                                ,\n    product_id NUMBER( 12, 0 ) NOT NULL                       , -- fk\n    quantity   NUMBER( 8, 2 ) NOT NULL                        ,\n    unit_price NUMBER( 8, 2 ) NOT NULL                        ,\n    CONSTRAINT pk_order_items \n      PRIMARY KEY( order_id, item_id ),\n    CONSTRAINT fk_order_items_products \n      FOREIGN KEY( product_id )\n      REFERENCES products( product_id ) \n      ON DELETE CASCADE,\n    CONSTRAINT fk_order_items_orders \n      FOREIGN KEY( order_id )\n      REFERENCES orders( order_id ) \n      ON DELETE CASCADE\n  );\n-- inventories\nCREATE TABLE inventories\n  (\n    product_id   NUMBER( 12, 0 )        , -- fk\n    warehouse_id NUMBER( 12, 0 )        , -- fk\n    quantity     NUMBER( 8, 0 ) NOT NULL,\n    CONSTRAINT pk_inventories \n      PRIMARY KEY( product_id, warehouse_id ),\n    CONSTRAINT fk_inventories_products \n      FOREIGN KEY( product_id )\n      REFERENCES products( product_id ) \n      ON DELETE CASCADE,\n    CONSTRAINT fk_inventories_warehouses \n      FOREIGN KEY( warehouse_id )\n      REFERENCES warehouses( warehouse_id ) \n      ON DELETE CASCADE\n  );<\/pre>\n\n\n\n
\n\n\n\n
Ins\u00e9rer les enregistrements dans la BD<\/h2>\n\n\n\n
Il faut utiliser le contenu du fichier ot_data.sql<\/strong><\/p>\n\n\n\n
NOTE<\/mark><\/strong>: Vous allez rencontrer des probl\u00e8mes:<\/p>\n\n\n\n