《ORACLE相关：表空间、序列、索引、分区、游标、存储过程、分区等创建.doc》由会员分享，可在线阅读，更多相关《ORACLE相关：表空间、序列、索引、分区、游标、存储过程、分区等创建.doc（9页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、 . . . . -创建表空间createtablespace textdatafileF:/db/ordata/hibernate/text.dbfsize10mAUTOEXTENDONNEXT10M MAXSIZEUNLIMITEDLOGGINGEXTENTMANAGEMENTLOCALSEGMENTSPACEMANAGEMENTAUTO;-创建用户createuser textidentifiedby textdefaulttablespace text-为用户分配权限grantconnect,resource,dbato text;-用户登录表的结构createtable street
2、(idNUMBER(5) primarykeynotnull, p_id NUMBER(5),nameVARCHAR2(18) notnull,constraint s_fk_p_id foreignkey (p_id) references district(id);-创建表createtable district(idNUMBER(5) primarykeynotnull, nameVARCHAR2(18) notnull);-创建序列createsequence seq_streetstartwith1incrementby1nomaxvaluecache10;createsequenc
3、e seq_districtstartwith1incrementby1nomaxvaluecache10;droptable street;droptable district;delete district;delete street;select * from street;select * from district;delete district whereid != 6;-PL/SQL-插入数据DECLARE v_id NUMBER(5) := 7; v_name VARCHAR2(18) := 汉阳区;BEGINinsertinto district values(v_id,v_
4、name);EXCEPTIONWhenothersthen DBMS_OUTPUT.PUT_LINE(插入数据失败);END;-IF-THEN语句DECLARE num1 number := 5; num2 number := 1; num3 number := 3;resultvarchar2(20);BEGINIF num1 num2 THENresult := num1 is big;ELSIF num1 10THENEXIT;ENDIF; DBMS_OUTPUT.PUT_LINE(num4);ENDLOOP; END;-WHILE LOOP循环DECLARE num5 number :
5、= 1;BEGINWHILE num5 = 1THENRAISE temp_ex;ENDIF; DBMS_OUTPUT.PUT_LINE(用户不存在！);EXCEPTIONWHEN temp_ex THEN DBMS_OUTPUT.PUT_LINE(用户已存在！);END;-游标DECLARE id1 number(5); name1 varchar2(18);-声明一个游标CURSOR c_district isselect * from district whereid=6;BEGIN-打开游标OPEN c_district;-判断游标是否返回记录IF c_district %NOTFOU
6、NDTHEN DBMS_OUTPUT.PUT_LINE(没有找到相应的数据！);ELSE-从游标中读取数据FETCH c_district into id1,name1; DBMS_OUTPUT.PUT_LINE(id1| |name1);ENDIF;-关闭游标CLOSE c_district;END;-FOR循环操作游标DECLARE id1 number(5); name1 varchar2(18);CURSOR c_dis isselect * from district;BEGINFOR c_dis1 IN c_dis LOOP id1 := c_dis1.id; name1 := c
7、_dis1.name; DBMS_OUTPUT.PUT_LINE(id1|name1);ENDLOOP;END;-存储过程-创建过程CREATEORREPLACEPROCEDURE proc_showInfo-声明一个输入参数 (name1 INvarchar2)as-声明一个游标，在查询语句中使用输入参数作为查询条件CURSOR c_dist ISselectid,namefrom district wherename = name1; BEGINFOR c_diss IN c_dist LOOP DBMS_OUTPUT.PUT_LINE(c_diss.id|c_diss.name);END
8、LOOP;END;-调用过程BEGIN proc_showInfo(123);END;-删除过程DROPPROCEDURE proc_showInfo;-创建函数CREATEORREPLACEFUNCTION getCount-声明输入参数 (id1 INnumber)-声明返回类型returnnumberAS f_count number;BEGIN-使用INTO语句将结果赋值给变量selectcount(*) into f_count from district whereid = id1;-使用RETURN语句返回return f_count;END;-调用函数DECLARE-声明变量接
9、收函数的返回值 v_count number;BEGIN v_count := getCount(6); DBMS_OUTPUT.PUT_LINE(v_count);END;-删除函数DROPFUNCTION GETCOUNT;-创建CREATEORREPLACEPACKAGE emp_package AS-声明存储过程，用于插入一条记录PROCEDURE my_proc( id1 number, name1 varchar2 );END emp_package;-创建包体CREATEORREPLACEPACKAGEBODY emp_package AS-存储过程的实现PROCEDURE my
10、_proc( id1 number, name1 varchar2)ASBEGININSERTINTO district VALUES(id1,name1);END my_proc;END emp_package;-包的调用BEGIN emp_package.my_proc(9,123);END;-创建视图CREATEORREPLACEVIEW v_district ASselect * from district;-查询视图里的数据SELECT * FROM v_district;-删除视图DROPVIEW v_district;-创建数据库链CREATEDATABASELINK link_
11、goodsconnectto text identifiedby textUSING(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.11)(PORT = 1521) ) (CONNECT_DATA = (SERVICE_NAME = PRD32) ) );-访问数据链SELECT * FROM goodslink_goods;-提高数据库性能-查看库高速缓存在执行调用阶段的不命中数selectsum(pins) 请求数,sum(reloads) 不命中数 from v$librarycache;-
12、查看库缓存的使用率select (sum(pins-reloads)/sum(pins) library cache from v$librarycache;-查看数据字典高速缓存不命中数selectsum(gets) 请求数 , sum(getmisses) 不命中数 from v$rowcache;-查看数据字典高速缓存的使用率select (sum(gets-getmisses-usage-fixed)/sum(gets) 数据字典使用率 from v$rowcache;-查看数据库缓存的命中率select(1-(selectvaluefrom v$sysstat wherename =
13、 physical reads)/( (selectvaluefrom v$sysstat wherename = consistent gets)+ (selectvaluefrom v$sysstat wherename = db block gets)from(selectname , valuefrom v$sysstat wherenamein(db block gets , consistent gets , physical reads);-创建索引createuniqueindex d_name on street (name);-修改索引alterindex d_name r
14、ebuildstorage (initial1m next512k);-删除索引dropindex d_name;-创建围分区createtable rang_active(idnumber,namevarchar2(20)partitionbyrange(id)(partition p_1 valueslessthan (6) tablespace space1,partition p_2 valueslessthan (7) tablespace space2,partition p_3 valueslessthan (maxvalue) tablespace space3);-创建列表分
15、区createtable list_active(idnumber, address varchar2(20)partitionbylist(address)(partition l_1 values() tablespace space1,partition l_1 values() tablespace space1,partition l_1 values() tablespace space1,)-创建散列分区createtable hash_active(idnumber,namevarchar2(20)partitionbyhash(id)(partition h_1 tables
16、pace space1,partition h_2 tablespace space2);-本地索引与分区表的创建createtable dept(idnumber,namevarchar2(20)partitionbyrange(id)(partition d_p1 valueslessthan (10) tablespace dp1,partition d_p2 valueslessthan (20) tablespace dp2,partition d_p3 valuelessthan(maxvalue) tablespace dp3);-根据表分区创建本地索引分区createindex d_index on dept(id) local(partition d_p1 tablespace dp1,partition d_p2 tablespace dp2,partition d_p3 tablespace dp3);-全局索引分区的创建createindex g_index on dept(id) globalpartitionbyrange(id)(partition g1 valueslessthan (100),partition g2 valueslessthan (200),partition g3 valueslessthan (maxvalue);9 / 9