oracle练习6

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as scott
 
SQL> create table  scoot.student;
 
create table  scoot.student
 
ORA-00906: 缺失左括号
 
SQL> create table scott.student();
 
create table scott.student()
 
ORA-00904: : 标识符无效
 
SQL> create table scott.student(stuname nvarchar(8));
 
create table scott.student(stuname nvarchar(8))
 
ORA-00907: 缺失右括号
 
SQL> create  table  scott.student(stuname varchar(8));
 
Table created
 
SQL> select * from student;
 
STUNAME
--------
 
SQL> insert into student select ename from emp;
 
14 rows inserted
 
SQL> create index in_student_stuname;
 
create index in_student_stuname
 
ORA-00969: 缺失 ON 关键字
 
SQL> create index in_student_stuname on student(stuname);
 
Index created
 
SQL>  select * from user_indexes;
 
INDEX_NAME                     INDEX_TYPE                  TABLE_OWNER                    TABLE_NAME                     TABLE_TYPE  UNIQUENESS COMPRESSION PREFIX_LENGTH TABLESPACE_NAME                 INI_TRANS  MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE PCT_THRESHOLD INCLUDE_COLUMN  FREELISTS FREELIST_GROUPS   PCT_FREE LOGGING     BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR STATUS     NUM_ROWS SAMPLE_SIZE LAST_ANALYZED DEGREE                                   INSTANCES                                PARTITIONED TEMPORARY GENERATED SECONDARY BUFFER_POOL USER_STATS DURATION        PCT_DIRECT_ACCESS ITYP_OWNER                     ITYP_NAME                      PARAMETERS                                                                       GLOBAL_STATS DOMIDX_STATUS DOMIDX_OPSTATUS FUNCIDX_STATUS JOIN_INDEX IOT_REDUNDANT_PKEY_ELIM DROPPED
------------------------------ --------------------------- ------------------------------ ------------------------------ ----------- ---------- ----------- ------------- ------------------------------ ---------- ---------- -------------- ----------- ----------- ----------- ------------ ------------- -------------- ---------- --------------- ---------- ------- ---------- ----------- ------------- ----------------------- ----------------------- ----------------- -------- ---------- ----------- ------------- ---------------------------------------- ---------------------------------------- ----------- --------- --------- --------- ----------- ---------- --------------- ----------------- ------------------------------ ------------------------------ -------------------------------------------------------------------------------- ------------ ------------- --------------- -------------- ---------- ----------------------- -------
PK_DEPT                        NORMAL                      SCOTT                          DEPT                           TABLE       UNIQUE     DISABLED                  USERS                                   2        255          65536                       1  2147483645                                                                              10 YES                                                                                                            VALID                                         1                                        1                                        NO          N         N         N         DEFAULT     NO                                                                                                                                                                                          NO                                                        NO         NO                      NO
PK_EMP                         NORMAL                      SCOTT                          EMP                            TABLE       UNIQUE     DISABLED                  USERS                                   2        255          65536                       1  2147483645                                                                              10 YES                                                                                                            VALID                                         1                                        1                                        NO          N         N         N         DEFAULT     NO                                                                                                                                                                                          NO                                                        NO         NO                      NO
IN_STUDENT_STUNAME             NORMAL                      SCOTT                          STUDENT                        TABLE       NONUNIQUE  DISABLED                  USERS                                   2        255          65536                       1  2147483645                                                                              10 YES              0           1            14                       1                       1                 1 VALID            14          14 2013-6-2 上午 1 1                                        1                                        NO          N         N         N         DEFAULT     NO                                                                                                                                                                                          NO                                                        NO         NO                      NO
 
SQL> drop index in_student_stuname;
 
Index dropped
 
SQL> select * from user_ind_columns;
 
INDEX_NAME                     TABLE_NAME                     COLUMN_NAME                                                                      COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESCEND
------------------------------ ------------------------------ -------------------------------------------------------------------------------- --------------- ------------- ----------- -------
PK_DEPT                        DEPT                           DEPTNO                                                                                         1            22           0 ASC
PK_EMP                         EMP                            EMPNO                                                                                          1            22           0 ASC
 
SQL> drop tabale student;
 
drop tabale student
 
ORA-00950: 无效 DROP 选项
 
SQL> drop table student;
 
Table dropped
 
SQL> create table student(id int,stuname varchar(8));
 
Table created
 
SQL>  create sequence s_student_id
  2   increment by 1;
 
Sequence created
 
SQL> insert into student(s_student_id,'longxiang');
 
insert into student(s_student_id,'longxiang')
 
ORA-01747: user.table.column, table.column 或列说明无效
 
SQL> insert into student(s_student_id,'longxiang');
 
insert into student(s_student_id,'longxiang')
 
ORA-01747: user.table.column, table.column 或列说明无效
 
SQL> insert into student(s_student_id,'lx');
 
insert into student(s_student_id,'lx')
 
ORA-01747: user.table.column, table.column 或列说明无效
 
SQL> insert into student(s_studentid,"lx");
 
insert into student(s_studentid,"lx")
 
ORA-00926: 缺失 VALUES 关键字
 
SQL> insert into student values(s_student_id,"lx");
 
insert into student values(s_student_id,"lx")
 
ORA-00984: 列在此处不允许
 
SQL> insert into student  values(s_student_id,'lx');
 
insert into student  values(s_student_id,'lx')
 
ORA-00984: 列在此处不允许
 
SQL> insert into  student values(s_student_id,'lx');
 
insert into  student values(s_student_id,'lx')
 
ORA-00984: 列在此处不允许
 
SQL> insert into student values(0,'lx');
 
1 row inserted
 
SQL> insert into student values(sequence s_student_id,'lx');
 
insert into student values(sequence s_student_id,'lx')
 
ORA-00917: 缺失逗号
 
SQL> insert into  student values(s_student_in.nextval,'lx');
 
insert into  student values(s_student_in.nextval,'lx')
 
ORA-02289: 序列不存在
 
SQL> insert into student values(s_student_id.nextval,'lx');
 
1 row inserted
 
SQL> insert into student values(s_student_id.nextval,'dxm');
 
1 row inserted
 
SQL> create table course (id int,cname varchar(20));
 
Table created
 
SQL> insert into course values(s_student_id.nextval,'c#');
 
1 row inserted
 
SQL> insert into course values(s_student_id.nextval,'java');
 
1 row inserted
 
SQL> select * from  course;
 
                                     ID CNAME
--------------------------------------- --------------------
                                      3 c#
                                      4 java
 
SQL> select * from student;
 
                                     ID STUNAME
--------------------------------------- --------
                                      0 lx
                                      1 lx
                                      2 dxm
 
SQL> insert into student values(s_student_id.nextval,'dd');
 
1 row inserted
 
SQL> select * from student;
 
                                     ID STUNAME
--------------------------------------- --------
                                      0 lx
                                      1 lx
                                      2 dxm
                                      5 dd
 
SQL> create or replace view emp_10
  2  as
  3   select * from emp where deptno=10;
 
create or replace view emp_10
as
 select * from emp where deptno=10
 
ORA-01031: 权限不足
 
SQL>  create or replace view emp_10
  2   as
  3     select * from emp where deptno=10;
 
View created
 
SQL> delete from emp_10;
 
3 rows deleted
 
SQL> select * from emp;
 
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980-12-17     800.00               20
 7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30
 7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30
 7566 JONES      MANAGER    7839 1981-4-2      2975.00               20
 7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30
 7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30
 7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20
 7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30
 7876 ADAMS      CLERK      7788 1987-5-23     1100.00               20
 7900 JAMES      CLERK      7698 1981-12-3      950.00               30
 7902 FORD       ANALYST    7566 1981-12-3     3000.00               20
 
11 rows selected
 
SQL> rollback;
 
Rollback complete
 
SQL> select * from emp;
 
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980-12-17     800.00               20
 7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30
 7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30
 7566 JONES      MANAGER    7839 1981-4-2      2975.00               20
 7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30
 7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30
 7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10
 7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20
 7839 KING       PRESIDENT       1981-11-17    5000.00               10
 7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30
 7876 ADAMS      CLERK      7788 1987-5-23     1100.00               20
 7900 JAMES      CLERK      7698 1981-12-3      950.00               30
 7902 FORD       ANALYST    7566 1981-12-3     3000.00               20
 7934 MILLER     CLERK      7782 1982-1-23     1300.00               10
 
14 rows selected
 
SQL> create or replace trigger emp_t
  2  before update or delete or insert
  3  on emp
  4  begin
  5  if to_char(sysdate,'dy')='星期天' then
  6    raise_application_error(-2000,'you would not update the emp on sunday');
  7  end if;
  8  end ;
  9 
 10 
 10 
 10 
 10 
 10   delete from emp where  deptno=10;
 10 
 10 
 10  create or replace trigger emp_t
 11    before update  or delete or insert
 12    on emp
 13    begin
 14       if  to_char(sysdate,‘dy’)=‘星期三' then
 15          raise_application_error(-20000, 'you would not
 16                            update the emp table  on Wednesday') ;
 17       end if ;
 18    end ;
 19 
 20 
 21 
 22 
 23 
 23 
 23 
 23  delete  from emp where deptno=10;
 24  ;
 25 
 26  select * from emp;
 27  rollback;
 28 
 29  ;
 30 
 31  ro
 32 
 32 
 32  ;
 33  sql> select * from emp;
 34 
 34 
 35