oracle项目案例脚本

前言：这是我从其他地方找到的一个oracle的案例脚本，在自己使用数据库的时候方便使用。

  1 -- 01 创建表空间
  2 -- 注意表空间的路径 根据实际安装环境进行调整
  3 
  4 CREATE TABLESPACE ts_myscott 
  5     LOGGING 
  6     DATAFILE ‘/home/oracle_11/app/oradata/orcl/ts_myscott.dbf‘ SIZE 10M 
  7     EXTENT MANAGEMENT LOCAL;
  8     
  9 CREATE TABLESPACE ts_myscott2 
 10     LOGGING 
 11     DATAFILE ‘/home/oracle_11/app/oradata/orcl/ts_myscott2.dbf‘ SIZE 20M
 12     EXTENT MANAGEMENT LOCAL;
 13     
 14 ALTER DATABASE DATAFILE ‘/home/oracle_11/app/oradata/orcl/ts_myscott.dbf‘ AUTOEXTEND ON NEXT   10M MAXSIZE UNLIMITED;
 15 ALTER DATABASE DATAFILE ‘/home/oracle_11/app/oradata/orcl/ts_myscott2.dbf‘ AUTOEXTEND ON NEXT  20M MAXSIZE UNLIMITED; 
 16 
 17 commit;
 18 
 19 -- 02 创建方案 (创建用户)
 20 CREATE USER MYSCOTT  PROFILE DEFAULT 
 21     IDENTIFIED BY MYSCOTT DEFAULT TABLESPACE USERS 
 22     ACCOUNT UNLOCK;
 23 
 24 -- 资源和登录权限
 25 GRANT RESOURCE TO MYSCOTT;
 26 GRANT create session TO MYSCOTT;
 27 
 28 
 29 -- 03 创建表
 30 -- 创建部门表 并赋值
 31 CREATE TABLE MYSCOTT.DEPT(
 32     DEPTNO         NUMBER(2) PRIMARY KEY, 33      DNAME       VARCHAR2(14) NOT NULL, 34      LOC            VARCHAR2(13)
 35 )TABLESPACE ts_myscott;
 36 
 37 INSERT INTO MYSCOTT.dept VALUES(10,‘ACCOUNTING‘,‘NEW YORK‘);
 38 INSERT INTO MYSCOTT.dept VALUES(20,‘RESEARCH‘,‘DALLAS‘);
 39 INSERT INTO MYSCOTT.dept VALUES(30,‘SALES‘,‘CHICAGO‘);
 40 INSERT INTO MYSCOTT.dept VALUES(40,‘OPERATIONS‘,‘BOSTON‘);
 41 
 42 commit;
 43 
 44 -- 创建员工表 并赋值
 45 CREATE TABLE MYSCOTT.EMP(
 46     EMPNO           NUMBER(4) constraint emp_empno_pk PRIMARY KEY, 47     ENAME              VARCHAR2(10) constraint emp_ename_notnull NOT NULL, 48     JOB                VARCHAR2(9), 49     MGR                NUMBER(4), 50     HIREDATE           DATE, 51     SAL                NUMBER(7,2) constraint emp_sal_check check (SAL>0), 52     COMM               NUMBER(7,2), 53     DEPTNO            NUMBER(2) constraint emp_deptno_fk references MYSCOTT.dept(deptno) 
 54 )TABLESPACE ts_myscott;
 55 
 56 --创建索引 在新的表空间上
 57 CREATE  INDEX MYSCOTT.IX_CAtbAuditOperInfo_OT ON MYSCOTT.EMP(ENAME) TABLESPACE ts_myscott2;
 58 
 59 
 60 INSERT INTO MYSCOTT.EMP VALUES(7369,‘SMITH‘,‘CLERK‘,7902,‘17-12月-80‘,800,NULL,20);
 61 INSERT INTO MYSCOTT.EMP VALUES(7499,‘ALLEN‘,‘SALESMAN‘,7698,‘20-2月 -81‘,1600,300,30);
 62 INSERT INTO MYSCOTT.EMP VALUES(7521,‘WARD‘,‘22-2月 -81‘,1250,500,30);
 63 
 64 INSERT INTO MYSCOTT.EMP VALUES(7566,‘JONES‘,‘MANAGER‘,7839,‘02-4月 -81‘,2975,20);
 65 INSERT INTO MYSCOTT.EMP VALUES(7654,‘MARTIN‘,‘28-9月 -81‘,1400,30);
 66 INSERT INTO MYSCOTT.EMP VALUES(7698,‘BLAKE‘,‘01-5月 -81‘,2850,30);
 67 
 68 INSERT INTO MYSCOTT.EMP VALUES(7782,‘CLARK‘,‘09-6月 -81‘,2450,10);
 69 INSERT INTO MYSCOTT.EMP VALUES(7788,‘SCOTT‘,‘ANALYST‘,7566,‘19-4月 -87‘,3000,20);
 70 INSERT INTO MYSCOTT.EMP VALUES(7839,‘KING‘,‘PRESIDENT‘,‘17-11月-81‘,5000,10);
 71 
 72 INSERT INTO MYSCOTT.EMP VALUES(7844,‘TURNER‘,‘08-9月 -81‘,1500,0,30);
 73 INSERT INTO MYSCOTT.EMP VALUES(7876,‘ADAMS‘,7788,‘23-5月 -87‘,1100,20);
 74 INSERT INTO MYSCOTT.EMP VALUES(7900,‘JAMES‘,‘03-12月-81‘,950,30);
 75 
 76 INSERT INTO MYSCOTT.EMP VALUES(7902,‘FORD‘,20);
 77 INSERT INTO MYSCOTT.EMP VALUES(7934,‘MILLER‘,7782,‘23-1月 -82‘,1300,10);
 78 
 79 commit;
 80     
 81 -- 创建工资级别表 并赋值
 82 CREATE TABLE MYSCOTT.SALGRADE(
 83      GRADE            NUMBER, 84      LOSAL           NUMBER, 85      HISAL           NUMBER
 86 )TABLESPACE ts_myscott;
 87 
 88 INSERT INTO MYSCOTT.SALGRADE VALUES(1,700,1200);
 89 INSERT INTO MYSCOTT.SALGRADE VALUES(2,1201,1400);
 90 INSERT INTO MYSCOTT.SALGRADE VALUES(3,1401,2000);
 91 INSERT INTO MYSCOTT.SALGRADE VALUES(4,2001,3000);
 92 INSERT INTO MYSCOTT.SALGRADE VALUES(5,3001,9999);
 93 commit;
 94 
 95 --创建奖金表
 96 CREATE TABLE MYSCOTT.BONUS(
 97     ENAME      VARCHAR2(10), 98      JOB       VARCHAR2(9), 99      SAL          NUMBER,100      COMM      NUMBER
101 )TABLESPACE ts_myscott;
102 
103 ------停止-----
104 -- 04创建新用户方案     通过MYSCOTTUSER1来访问数据库,权限配置演示  
105 CREATE USER "MYSCOTTUSER1"  PROFILE "DEFAULT" IDENTIFIED BY "123456" DEFAULT TABLESPACE "USERS" ACCOUNT UNLOCK;
106 GRANT "CONNECT" TO "MYSCOTTUSER1";
107 GRANT SELECT ANY TABLE TO "MYSCOTTUSER1";
108 
109 GRANT DELETE ON MYSCOTT.DEPT TO "MYSCOTTUSER1";
110 GRANT INSERT ON MYSCOTT.DEPT TO "MYSCOTTUSER1";
111 GRANT UPDATE ON MYSCOTT.DEPT TO "MYSCOTTUSER1";
112 
113 GRANT DELETE ON MYSCOTT.EMP TO "MYSCOTTUSER1";
114 GRANT INSERT ON MYSCOTT.EMP TO "MYSCOTTUSER1";
115 GRANT UPDATE ON MYSCOTT.EMP TO "MYSCOTTUSER1";
116 
117 commit;
118 
119     

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!