Example create emp dept custom
Aller à la navigation
Aller à la recherche
| Fiche express | |
|---|---|
| Type | Script SQL*Plus d'exemple |
| Domaine | Exemple DDL |
| Voir aussi | Example create emp dept original · Example create index organized table · Example create materialized view |
Example create emp dept custom est une variante simplifiée du schéma de démonstration
EMP/DEPT : seulement deux tables (dept et
emp, avec des noms de colonnes plus explicites que le schéma d'origine), mais
accompagnée d'un générateur de données aléatoires — un package PL/SQL et une procédure
fill_emp — permettant de peupler la table emp avec des milliers de
lignes de test. Utile pour des tests de volumétrie ou de performance, là où le schéma
original ne fournit qu'une quinzaine de lignes figées.
Script
Le script enchaîne quatre étapes : création de dept (avec 21 lignes de
référence), création de emp (vide), un générateur de nombres pseudo-aléatoires
(package random, algorithme congruentiel linéaire), et la procédure
fill_emp qui insère N employés en attribuant à chacun un
département, une date de naissance, un salaire et un poste tirés aléatoirement.
-- Schéma de démonstration simplifié + générateur de données aléatoires
PROMPT Connexion au compte de démonstration...
CONNECT demo_user
/* ------------------------- CREATE TABLE dept ------------------------- */
DROP TABLE dept CASCADE CONSTRAINTS
/
CREATE TABLE dept (
dept_id NUMBER
, name VARCHAR2(100)
, location VARCHAR2(100)
)
/
ALTER TABLE dept
ADD CONSTRAINT dept_pk PRIMARY KEY (dept_id)
/
ALTER TABLE dept
MODIFY ( name CONSTRAINT dept_nn1 NOT NULL
, location CONSTRAINT dept_nn2 NOT NULL
)
/
/* -------------------------- CREATE TABLE emp -------------------------- */
DROP TABLE emp CASCADE CONSTRAINTS
/
CREATE TABLE emp (
emp_id NUMBER
, dept_id NUMBER
, name VARCHAR2(30)
, date_of_birth DATE
, date_of_hire DATE
, monthly_salary NUMBER(15,2)
, position VARCHAR2(100)
, extension NUMBER
, office_location VARCHAR2(100)
)
/
ALTER TABLE emp
ADD CONSTRAINT emp_pk PRIMARY KEY (emp_id)
/
ALTER TABLE emp
MODIFY ( name CONSTRAINT emp_nn1 NOT NULL
, date_of_birth CONSTRAINT emp_nn2 NOT NULL
, date_of_hire CONSTRAINT emp_nn3 NOT NULL
, monthly_salary CONSTRAINT emp_nn4 NOT NULL
, position CONSTRAINT emp_nn5 NOT NULL
)
/
ALTER TABLE emp
ADD CONSTRAINT emp_fk1 FOREIGN KEY (dept_id) REFERENCES dept(dept_id)
/
/* ------------------------- INSERT INTO dept ---------------------------- */
INSERT INTO dept VALUES (100, 'ACCOUNTING', 'BUTLER, PA');
INSERT INTO dept VALUES (101, 'RESEARCH', 'DALLAS, TX');
INSERT INTO dept VALUES (102, 'SALES', 'CHICAGO, IL');
INSERT INTO dept VALUES (103, 'OPERATIONS', 'BOSTON, MA');
INSERT INTO dept VALUES (104, 'IT', 'PITTSBURGH, PA');
INSERT INTO dept VALUES (105, 'ENGINEERING', 'WEXFORD, PA');
INSERT INTO dept VALUES (106, 'QA', 'WEXFORD, PA');
INSERT INTO dept VALUES (107, 'PROCESSING', 'NEW YORK, NY');
INSERT INTO dept VALUES (108, 'CUSTOMER SUPPORT', 'TRANSFER, PA');
INSERT INTO dept VALUES (109, 'HQ', 'WEXFORD, PA');
INSERT INTO dept VALUES (110, 'PRODUCTION SUPPORT', 'MONTEREY, CA');
INSERT INTO dept VALUES (111, 'DOCUMENTATION', 'WEXFORD, PA');
INSERT INTO dept VALUES (112, 'HELP DESK', 'GREENVILLE, PA');
INSERT INTO dept VALUES (113, 'AFTER HOURS SUPPORT', 'SAN JOSE, CA');
INSERT INTO dept VALUES (114, 'APPLICATION SUPPORT', 'WEXFORD, PA');
INSERT INTO dept VALUES (115, 'MARKETING', 'SEASIDE, CA');
INSERT INTO dept VALUES (116, 'NETWORKING', 'WEXFORD, PA');
INSERT INTO dept VALUES (117, 'DIRECTORS OFFICE', 'WEXFORD, PA');
INSERT INTO dept VALUES (118, 'ASSISTANTS', 'WEXFORD, PA');
INSERT INTO dept VALUES (119, 'COMMUNICATIONS', 'SEATTLE, WA');
INSERT INTO dept VALUES (120, 'REGIONAL SUPPORT', 'PORTLAND, OR');
COMMIT;
/* --------------------- CREATE PACKAGE random --------------------------- */
CREATE OR REPLACE PACKAGE random IS
-- Entier aléatoire dans [0, r-1]
FUNCTION rndint(r IN NUMBER) RETURN NUMBER;
-- Réel aléatoire dans [0, 1]
FUNCTION rndflt RETURN NUMBER;
END;
/
CREATE OR REPLACE PACKAGE BODY random IS
m CONSTANT NUMBER := 100000000; -- conditions initiales
m1 CONSTANT NUMBER := 10000; -- (pour un meilleur résultat)
b CONSTANT NUMBER := 31415821;
a NUMBER; -- graine
the_date DATE;
days NUMBER; -- pour générer la graine initiale
secs NUMBER;
FUNCTION mult(p IN NUMBER, q IN NUMBER) RETURN NUMBER IS
p1 NUMBER; p0 NUMBER; q1 NUMBER; q0 NUMBER;
BEGIN
p1 := TRUNC(p / m1);
p0 := MOD(p, m1);
q1 := TRUNC(q / m1);
q0 := MOD(q, m1);
RETURN (MOD((MOD(p0*q1 + p1*q0, m1)*m1 + p0*q0), m));
END;
FUNCTION rndint(r IN NUMBER) RETURN NUMBER IS
BEGIN
a := MOD(mult(a, b) + 1, m);
RETURN (TRUNC((TRUNC(a / m1) * r) / m1));
END;
FUNCTION rndflt RETURN NUMBER IS
BEGIN
a := MOD(mult(a, b) + 1, m);
RETURN (a / m);
END;
BEGIN
-- Graine initiale basée sur la date système
the_date := SYSDATE;
days := TO_NUMBER(TO_CHAR(the_date, 'J'));
secs := TO_NUMBER(TO_CHAR(the_date, 'SSSSS'));
a := days * 86400 + secs;
END;
/
/* ------------------ CREATE PROCEDURE fill_emp --------------------------- */
CREATE OR REPLACE PROCEDURE fill_emp (num_records IN NUMBER) IS
rand NUMBER;
randf NUMBER;
randfe NUMBER;
rand_dept_id NUMBER;
rand_date NUMBER;
rand_salary NUMBER;
record_count_success NUMBER;
record_count_fail_ic NUMBER;
record_count_fail_other NUMBER;
max_emp_id NUMBER;
CURSOR max_emp_csr IS
SELECT MAX(emp_id) FROM emp;
BEGIN
DBMS_OUTPUT.ENABLE;
OPEN max_emp_csr;
FETCH max_emp_csr INTO max_emp_id;
CLOSE max_emp_csr;
max_emp_id := NVL(max_emp_id, 0) + 1;
record_count_success := 0;
record_count_fail_ic := 0;
record_count_fail_other := 0;
FOR loop_index IN max_emp_id .. (max_emp_id + (num_records - 1)) LOOP
rand := random.rndint(20);
randf := random.rndflt;
randfe := TRUNC(random.rndflt * 10000);
IF (randfe < 1000) THEN
randfe := randfe * 10;
END IF;
rand_dept_id := rand + 100;
rand_date := rand * 10;
rand_salary := randf * 10000;
IF (rand_salary < 1000) THEN
rand_salary := rand_salary * 10;
END IF;
DECLARE
integrity_constraint_e EXCEPTION;
PRAGMA EXCEPTION_INIT (integrity_constraint_e, -02291);
BEGIN
INSERT INTO emp VALUES (
loop_index
, rand_dept_id
, 'Name at : ' || (rand_dept_id * 17)
, SYSDATE - (rand_date * 90)
, SYSDATE + rand_date
, rand_salary
, 'Position at : ' || (rand_dept_id * 13)
, randfe
, 'Office Location at : ' || (rand_dept_id * 15)
);
IF (MOD(loop_index, 1000) = 0) THEN
COMMIT;
END IF;
record_count_success := record_count_success + 1;
EXCEPTION
WHEN integrity_constraint_e THEN
record_count_fail_ic := record_count_fail_ic + 1;
WHEN OTHERS THEN
record_count_fail_other := record_count_fail_other + 1;
END;
END LOOP;
COMMIT;
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.PUT_LINE('Procédure terminée : insertion dans emp.');
DBMS_OUTPUT.PUT_LINE('----------------------------------------------');
DBMS_OUTPUT.PUT_LINE('Enregistrements demandés : ' || num_records);
DBMS_OUTPUT.PUT_LINE('Enregistrements insérés avec succès : ' || record_count_success);
DBMS_OUTPUT.PUT_LINE('Échecs (contrainte d''intégrité) : ' || record_count_fail_ic);
DBMS_OUTPUT.PUT_LINE('Échecs (autre cause) : ' || record_count_fail_other);
END;
/
-- Exemple d'utilisation : générer 5000 employés de test
-- EXEC fill_emp(5000);
Un appel d'exemple (EXEC fill_emp(5000), commenté à la fin) a été ajouté : le
script d'origine créait la procédure sans jamais illustrer son appel.
Voir aussi
- Example create emp dept original — schéma de démonstration Oracle classique (EMP/DEPT/BONUS/SALGRADE)
- Example create index organized table — variante IOT construite à partir de ce schéma emp
- Example create materialized view — vue matérialisée agrégeant emp/dept