《ORACLE相关：表空间序列索引分区游标存储过程分区等创建》由会员分享，可在线阅读，更多相关《ORACLE相关：表空间序列索引分区游标存储过程分区等创建（8页珍藏版）》请在金锄头文库上搜索。
1、-创建表空间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 tab
2、le 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 1nom
3、axvaluecache 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) := 汉阳区;
4、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(
5、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 whe
6、re 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 s
7、elect * from district;BEGIN FOR c_dis1 IN c_dis LOOP id1 := c_dis1.id; name1 := c_dis1.name; 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_diss.name); END LOOP; END;-调用过程BEGIN proc_showInfo(123);END;-删除过程DROP PROCEDURE proc_show
