ORACLE相关：表空间、序列、索引、分区、游标、存储过程、分区等创建.doc

-创建表空间create tablespace textdatafile F:/db/ordata/hibernate/text.dbfsize 10mAUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITEDLOGGINGEXTENT MANAGEMENT LOCALSEGMENT SPACE MANAGEMENT AUTO;-创建用户create user textidentified by textdefault tablespace text-为用户分配权限grant connect,resource,dba to text;-用户登录表的结构create table street( idNUMBER(5) primary key not null, p_id NUMBER(5), nameVARCHAR2(18) not null, constraint s_fk_p_id foreign key (p_id) references district(id);-创建表create table district( idNUMBER(5) primary key not null, nameVARCHAR2(18) not null);-创建序列create sequence seq_streetstart with 1increment by 1nomaxvaluecache 10;create sequence seq_districtstart with 1increment by 1nomaxvaluecache 10; drop table street;drop table district;delete district;delete street;select * from street;select * from district;delete district where id != 6;-PL/SQL-插入数据DECLARE v_id NUMBER(5) := 7; v_name VARCHAR2(18) := 汉阳区;BEGIN insert into district values(v_id,v_name);EXCEPTION When others then DBMS_OUTPUT.PUT_LINE(插入数据失败);END;-IF-THEN语句DECLARE num1 number := 5; num2 number := 1; num3 number := 3; result varchar2(20);BEGIN IF num1 num2 THEN result := num1 is big; ELSIF num1 10 THEN EXIT; END IF; DBMS_OUTPUT.PUT_LINE(num4); END LOOP; END;-WHILE LOOP循环DECLARE num5 number := 1;BEGIN WHILE num5 = 1 THEN RAISE temp_ex; END IF; DBMS_OUTPUT.PUT_LINE(用户不存在！); EXCEPTION WHEN temp_ex THEN DBMS_OUTPUT.PUT_LINE(用户已存在！);END;-游标DECLARE id1 number(5); name1 varchar2(18); -声明一个游标 CURSOR c_district is select * from district where id=6;BEGIN -打开游标 OPEN c_district; -判断游标是否返回记录 IF c_district %NOTFOUND THEN DBMS_OUTPUT.PUT_LINE(没有找到相应的数据！); ELSE -从游标中读取数据 FETCH c_district into id1,name1; DBMS_OUTPUT.PUT_LINE(id1| |name1); END IF; -关闭游标 CLOSE c_district;END;-FOR循环操作游标DECLARE id1 number(5); name1 varchar2(18); CURSOR c_dis is select * from district;BEGIN FOR c_dis1 IN c_dis LOOP id1 := c_dis1.id; name1 := c_; DBMS_OUTPUT.PUT_LINE(id1|name1); END LOOP;END;-存储过程-创建过程CREATE OR REPLACE PROCEDURE proc_showInfo -声明一个输入参数 (name1 IN varchar2) as -声明一个游标，在查询语句中使用输入参数作为查询条件 CURSOR c_dist IS select id,name from district where name = name1; BEGIN FOR c_diss IN c_dist LOOP DBMS_OUTPUT.PUT_LINE(c_diss.id|c_); END LOOP; END;-调用过程BEGIN proc_showInfo(123);END;-删除过程DROP PROCEDURE proc_showInfo;-创建函数CREATE OR REPLACE FUNCTION getCount -声明输入参数 (id1 IN number) -声明返回类型 return number AS f_count number; BEGIN -使用INTO语句将结果赋值给变量 select count(*) into f_count from district where id = id1; -使用RETURN语句返回 return f_count; END;-调用函数DECLARE -声明变量接收函数的返回值 v_count number;BEGIN v_count := getCount(6); DBMS_OUTPUT.PUT_LINE(v_count);END;-删除函数DROP FUNCTION GETCOUNT;-创建包头CREATE OR REPLACE PACKAGE emp_package AS -声明存储过程，用于插入一条记录 PROCEDURE my_proc( id1 number, name1 varchar2 );END emp_package;-创建包体CREATE OR REPLACE PACKAGE BODY emp_package AS -存储过程的实现 PROCEDURE my_proc( id1 number, name1 varchar2) AS BEGIN INSERT INTO district VALUES(id1,name1); END my_proc;END emp_package;-包的调用BEGIN emp_package.my_proc(9,123);END;-创建视图CREATE OR REPLACE VIEW v_district AS select * from district;-查询视图里的数据SELECT * FROM v_district;-删除视图DROP VIEW v_district;-创建数据库链CREATE DATABASE LINK link_goods connect to text identified by text USING (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 1)(PORT = 1521) ) (CONNECT_DATA = (SERVICE_NAME = PRD32) ) );-访问数据链SELECT * FROM goodslink_goods;-提高数据库性能-查看库高速缓存在执行调用阶段的不命中数select sum(pins) 请求数,sum(reloads) 不命中数 from v$librarycache;-查看库缓存的使用率select (sum(pins-reloads)/sum(pins) library cache from v$librarycache;-查看数据字典高速缓存不命中数select sum(gets) 请求数 , sum(getmisses) 不命中数 from v$rowcache;-查看数据字典高速缓存的使用率select (sum(gets-getmisses-usage-fixed)/sum(gets) 数据字典使用率 from v$rowcache;-查看数据库缓存的命中率select (1-(select value from v$sysstat where name = physical reads)/( (select value from v$sysstat where name = consistent gets)+ (select value from v$sysstat where name = db block gets)from (select name , value from v$sysstat where name in(db block gets , consistent gets , physical reads);-创建索引create unique index d_name on street (name);-修改索引alter index d_name rebuild storage (initial 1m next 512k);-删除索引drop index d_name;-创建范围分区create table rang_active( id number, name varchar2(20)partition by range(id)( partition p_1 values less than (6) tablespace space1, partition p_2 values less than (7) tablespace space2, partition p_3 values less than (maxvalue) tablespace space3);-创建列表分区create table list_active( id number, address varchar2(20)partition by list(address)( partition l_1 values(北京) tablespace space1, partition l_1 values(广州) tablespace space1, partition l_1 values(上海) tablespace space1,)-创建散列分区create table hash_active( id number, name varchar2(20)partition by hash(id)( partition h_1 tablespace space1, partition h_2 tablespace space2);-本地索引与分区表的创建create table dept( id number, name varchar2(20)partition by range(id)( partition d_p1 values less than (10) tablespace dp1, partition d_p2 values less than (20) tablespace dp2, partition d_p3 value less than(maxvalue) tablespace dp3);-根据表分区创建本地索引分区create index d_index on dept(id) local( partition d_p1