课程第44次：2016-07-06星期三

botang · 发表于 2016-7-7 10:06:18

本帖最后由 botang 于 2016-7-7 10:09 编辑

【上完1Z0-051的第10章】DDL和表以及约束
【开始1Z0-051的第11章】其他Oracle Schema对象
【1Z0-051】：共11章（0 1 2 3 4 5 6 7 8 9 10 11）这本书快上完了

【1Z0-052】：共13章（0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18）
【1Z0-053】：共15章（0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20）
表示已经上过的，表示还没上的。
完全按照上课顺序：
https://www.botangdb.com/mytrain/201601/00000028.html

create table t05110_a ( a number default 999 , b varchar2(20) default 'AAA' ) ;
select * from user_Tab_columns t where t.TABLE_NAME='T05110_A';
alter table t05110_a modify ( b default 'BBB') ;
insert into t05110_a values (default, default);
select * from t05110_a;
----
create table t05110_clob( a number , b clob ) ;
insert into t05110_clob values (1,'AAAAAA') ;
select * from t05110_clob ;
select * from user_objects o where o.OBJECT_NAME='T05110_CLOB';
80630
select * from user_objects o where o.OBJECT_NAME like '%80630%';
select * from user_lobs;
create table t05110_blob ( a varchar2(20) , b blob ) ;
CREATE OR REPLACE PROCEDURE procblob
( p_dir VARCHAR2, p_file VARCHAR2)
IS
v_f BFILE;
v_b blob;
BEGIN
INSERT INTO t05110_blob values( p_file, EMPTY_BLOB ()) RETURN b into v_b;
v_f := BFILENAME (p_dir, p_file);
DBMS_LOB.FILEOPEN (v_f, DBMS_LOB.FILE_READONLY);
DBMS_LOB.LOADFROMFILE (v_b, v_f, DBMS_LOB.GETLENGTH (v_f));
DBMS_LOB.FILECLOSE (v_f);
commit;
end;
select * from user_errors;
begin
procblob ('PHOTODIR','computer.jpg');
end;
select * from t05110_blob;
----
create table t05110_time1( a INTERVAL YEAR(3) TO MONTH );
insert into t05110_time1 values ( INTERVAL '123-4' YEAR(3) TO MONTH );
select * from t05110_time1 ;
select sysdate+a from t05110_time1 ;
create table t05110_time2 ( a INTERVAL DAY(3) to second(3) );
insert into t05110_time2 values ( INTERVAL '4 13:50:6.333' DAY(3) to second(3) ) ;
select * from t05110_time2;
select sysdate+a from t05110_time2 ;
-----
create table t05110_nn( a number not null );
insert into t05110_nn values (1 );
commit;
select * from user_constraints c where c.TABLE_NAME='T05110_NN';
create table t05110_nn2( a number constraint c_t05110_nn not null );
insert into t05110_nn2 values (1);
commit;
select * from user_constraints c where c.TABLE_NAME='T05110_NN2';
create table t1 as select * from t05110_nn;
create table t2 as select * from t05110_nn2;
select * from user_constraints c where c.TABLE_NAME in ('T1','T2');
create table t05110_nn3( a number , constraint c_t05110_nn3 check( a is not null) );
insert into t05110_nn3 values (1);
select * from user_constraints c where c.TABLE_NAME='T05110_NN3';
create table t3 as select * from t05110_nn3;
select * from t3;
select * from t05110_nn4;
select * from user_constraints c where c.TABLE_NAME in ('T1','T2','T3');
create table t05110_nn4 ( a number ) ;
select * from user_constraints c where c.TABLE_NAME='T05110_NN4';
insert into t05110_nn4 values (null);
select * from t05110_nn4;
alter table t05110_nn4 add constraint c_t05110_nn4 check ( a is not null) enable novalidate;
select * from user_constraints c where c.TABLE_NAME='T05110_NN4';
create table t05110_nn5( a number ,
constraint c_t05110_nn5 check ( a is not null ) disable validate ) ;
insert into t05110_nn5 values (1) ;
---
select * from exceptions;
alter table t05110_nn4 modify constraint c_t05110_nn4
enable validate exceptions into exceptions;
select * from exceptions;
select * from t05110_nn4 where rowid='AAATsJAAEAAAAPMAAA';
update t05110_nn4 set a=1 where rowid='AAATsJAAEAAAAPMAAA';
----
create table t05110_unique1 ( a number
constraint c_t05110_unique1 unique );
select * from user_indexes i where i.TABLE_NAME='T05110_UNIQUE1';
select * from user_constraints c where c.TABLE_NAME='T05110_UNIQUE1';
insert into t05110_unique1 values (1) ;
select * from t05110_unique1 ;
insert into t05110_unique1 values (null);
insert into t05110_unique1 values (null);
create table t05110_unique2 (a number
constraint c_t05110_unique2 unique deferrable initially deferred ) ;
select * from user_indexes i where i.TABLE_NAME='T05110_UNIQUE2';
select * from user_constraints c where c.TABLE_NAME='T05110_UNIQUE2';
insert into t05110_unique2 values ( 1 ) ;
insert into t05110_unique2 values ( 1 ) ;
insert into t05110_unique2 values ( 1 ) ;
select * from t05110_unique2 ;
commit;
-----
create table t05110_unique3 ( a number ) ;
create unique index i05110_unique3 on t05110_unique3 ( a);
alter table t05110_unique3 add constraint c_t05110_unique3
unique( a ) not deferrable initially immediate ;
---
create table t05110_parent ( a number constraint c_t05110_parent primary key ) ;
insert into t05110_parent values (1) ;
create table
t05110_son ( b number constraint c_t05110_son referencing t05110_parent
on delete set null ) ;
insert into t05110_son values (1) ;
select * from t05110_parent;
select * from t05110_son ;
delete from t05110_parent where a=1;
commit;
----
create table t05110_check ( a number constraint c_t05110_check
check ( a >7 )) ;
insert into t05110_check values (8) ;
update t05110_check set a=9 where a=88;
---
create or replace trigger trgvalue
after update or insert on t05110_check
referencing old as old new as new
for each row
begin
if :old.a >= :new.a
then
raise_application_error(-20999,'Must biger');
end if;
end ;
select * from t05110_check;
select * from user_triggers;
update t05110_check set a=9 ;
insert into t05110_check values ( 8 ) ;
select * from t05110_check;
update t05110_check set a=9 where a=8;
---
create or replace view v05110_a
as ( select employee_id , department_id
from employees where department_id=10 ) ;
select * from v05110_a;
update v05110_a set department_id=20 where employee_id=200;
create or replace view v05110_a
as ( select employee_id , department_id
from employees where department_id=20 ) with check option ;
select * from v05110_a;
update v05110_a set department_id=10 where employee_id=200;
---
create table t05311_a ( a number ) ;
insert into t05311_a values (1) ;
create view v05311_a as ( select a*2 b from
t05311_a ) ;
select * from v05311_a;
update v05311_a set b=4 where b=2;
---
delete from v05311_a;
select * from t05311_a;
---
create sequence seq1 ;
select * from user_sequences where sequence_name='SEQ1';
select seq1.nextval from dual;

复制代码