Oracle 資料庫安排

資料表格：

CREATE TABLE "DEPT" ( "DEPTNO" NUMBER(2,0), "DNAME" VARCHAR2(14), "LOC" VARCHAR2(13), PRIMARY KEY ("DEPTNO") ENABLE ) ; CREATE TABLE "EMP" ( "EMPNO" NUMBER(4,0) NOT NULL ENABLE, "ENAME" VARCHAR2(10), "JOB" VARCHAR2(9), "MGR" NUMBER(4,0), "HIREDATE" DATE, "SAL" NUMBER(7,2), "COMM" NUMBER(7,2), "DEPTNO" NUMBER(2,0), PRIMARY KEY ("EMPNO") ENABLE ) ; ALTER TABLE "EMP" ADD FOREIGN KEY ("MGR") REFERENCES "EMP" ("EMPNO") ENABLE;ALTER TABLE "EMP" ADD FOREIGN KEY ("DEPTNO") REFERENCES "DEPT" ("DEPTNO") ENABLE;

預儲程式 (STRESSTEST1) ：

CREATE OR REPLACE PROCEDURE "STRESSTEST1" (p_DEPTNO IN NUMBER) IS v_EMPNO NUMBER(4,0); v_ENAME VARCHAR2(10); v_JOB VARCHAR2(9); v_MGR NUMBER(4,0); v_HIREDATE DATE; v_SAL NUMBER(7,2); v_COMM NUMBER(7,2); v_DEPTNO NUMBER(2,0); BEGIN v_DEPTNO := p_DEPTNO; DELETE FROM EMP WHERE DEPTNO=v_DEPTNO; v_ENAME := 'ST1'; v_JOB := 'ST'; v_MGR := NULL; v_HIREDATE := CURRENT_DATE; v_SAL := 9999; v_COMM := 9999; FOR I IN 1..99 LOOP v_EMPNO := p_DEPTNO * 100 + I; INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (v_EMPNO, v_ENAME, v_JOB, v_MGR, v_HIREDATE, v_SAL, v_COMM, v_DEPTNO); END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN FOR I IN 1..99 LOOP v_EMPNO := p_DEPTNO * 100 + I; INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (v_EMPNO, v_ENAME, v_JOB, v_MGR, v_HIREDATE, v_SAL, v_COMM, v_DEPTNO); END LOOP; END;

預儲程式 (STRESSTEST0) ：

CREATE OR REPLACE PROCEDURE "STRESSTEST0" (p_DEPTNO IN NUMBER) IS BEGIN DELETE FROM EMP WHERE DEPTNO=p_DEPTNO; DELETE FROM DEPT WHERE DEPTNO=p_DEPTNO; INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES (p_DEPTNO, TO_CHAR(CURRENT_TIMESTAMP, 'MMDDHHMISS'), TO_CHAR(CURRENT_TIMESTAMP, 'MMDDHHMISS')); STRESSTEST1(p_DEPTNO); EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES (p_DEPTNO, TO_CHAR(CURRENT_TIMESTAMP, 'MMDDHHMISS'), TO_CHAR(CURRENT_TIMESTAMP, 'MMDDHHMISS')); STRESSTEST1(p_DEPTNO); END;