Oracle 存储过程创建和删除表

CREATE OR REPLACE PROCEDURE CREATE_TABLE AS

BEGIN

EXECUTE IMMEDIATE 'DROP TABLE EMP';

EXECUTE IMMEDIATE 'DROP TABLE DEPT';

EXECUTE IMMEDIATE

'CREATE TABLE DEPT
 (DEPTNO NUMBER(2),
  DNAME VARCHAR2(14),
  LOC VARCHAR2(13)
 )';
EXECUTE IMMEDIATE

'CREATE TABLE EMP
 (EMPNO NUMBER(4) NOT NULL,
  ENAME VARCHAR2(10),
  JOB VARCHAR2(9),
  HIREDATE DATE,
  SAL NUMBER(7, 2),
  DEPTNO NUMBER(2)
 )';
EXECUTE IMMEDIATE

'ALTER TABLE EMP ADD CONSTRAINT EMP_PK PRIMARY KEY(EMPNO)';

 EXECUTE IMMEDIATE

 'ALTER TABLE DEPT ADD CONSTRAINT DEPT_PK PRIMARY KEY(DEPTNO)';

EXECUTE IMMEDIATE

 'ALTER TABLE EMP ADD CONSTRAINT EMP_FK_DEPT FOREIGN KEY(DEPTNO) REFERENCES DEPT';

END CREATE_TABLE;