ORACLE10演示测试程序源码.docVIP

ORACLE10演示测试程序源码 说明: 将基本的管理功能以代码实例展现出来 ************************************************************ /*建表*/ ************************************************************ create table t4 ( xh char(4) not null, xm char(6) not null, zym varchar(10), xb char(2), cssj date, zxf number(2) , bz varchar(20), constraint t4_1 primary key (xh), constraint t4_2 check (xb=男 or xb=女)) tablespace myts; ************************************************************ /*插数据*/ ************************************************************ insert into t4 values(1001,张1,为,to_dateyyyymmdd),50); /*ORA-02290: 违反检查约束条件 (SYSTEM.t4_2)*/ /*缺少记录*/ insert into t4 values(1001,张1,计算机,女,to_dateyyyymmdd),63,); insert into t4 values(1001,张1,计算机,女,to_dateyyyymmdd),63,); /*数据匹配*/ MERGE INTO t1 a USING t4 b ON(a.XH=b.XH) WHEN NOT MATCHED THEN INSERT (matched 相配的) Values(b.xh,b.xm,b.zym,b.xb,b.cssj,b.zxf,b.bz); /*删除数据*/ delete from t1 where zxf60; delete from t1;/*没有where 删除全部数据，慎用 同 TRUNCATE TABLE table_name*/ /*修改数据*/ /*修改条件数据*/ UPDATE t4 SET BZ=三好学生 WHERE XH=061101; /*修改全部数据*/ UPDATE t4 SET cj=cj+10; /*创建索引*/ CREATE INDEX kc_name_idx ON KC(KCM) TABLESPACE MYTS;(区分大小写!=myts) CREATE INDEX xs_xm_idx ON XS (XM) TABLESPACE MYTS INITRANS 2 MAXTRANS 255 NOSORT; /*修改索引*/ ALTER INDEX /*参数设定*/ INITRANS=2 MAXTRANS=128 /*删除索引*/ DROP INDEX XS_XM_IND; /*创建同义词*/ CREATE [PUBLIC] SYNONYM XS_KC FOR ADMIN.XS_KC； /*使用同义词*/ SELECT * FROM ADMIN.XS_KC; /*删除同义词*/ DROP PUBLIC SYNONYM XS_KC; /*创建数据库连接*/ CREATE PUBLIC DATABASE LINK MY_PLINK CONNECT TO A IDENTIFIED BY MANAGE USING XSCJ; /*查看数据库连接*/ select * from dba_db_links; /*删除数据库连接,PUBLIC属性依据连接属性*/ DROP [PUBLIC] DATABASE LINK MY_PLINK; /*域完整性*/ /*定义表时设置*/ CREATE TABLE books ( book_id number(10), book_name varchar2(50) not null, book_desc varc