ORACLE相关：表空间、序列、索引、分区、游标、存储过程、分区等创建
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
--创建表空间
create tablespace text
datafile'F:/db/ordata/hibernate/text.dbf'
size10m
AUTOEXTEND ON NEXT10M MAXSIZE UNLIMITED
LOGGING
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
--创建用户
create user text
identified by text
default tablespace text
--为用户分配权限
grant connect,resource,dba to text;
--用户登录表的结构
create table street(
id NUMBER(5) primary key not null,
p_id NUMBER(5),
name VARCHAR2(18) not null,
constraint s_fk_p_id foreign key (p_id) references district(id) );
--创建表
create table district(
id NUMBER(5) primary key not null,
name VARCHAR2(18) not null
);
--创建序列
create sequence seq_street
start with1
increment by1
nomaxvalue
cache10;
create sequence seq_district
start with1
increment by1
nomaxvalue
cache10;
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 < num2 THEN
result := 'num3 is big';
ELSE
result := 'num2';
END IF;
DBMS_OUTPUT.PUT_LINE(result);
END;
--CASE 语句
DECLARE
chap char := 'c';
result varchar2(20);
BEGIN
CASE chap
WHEN'A'THEN result := 'is A';
WHEN'B'THEN result := 'is B';
ELSE result := 'Ok!';
END CASE;
DBMS_OUTPUT.put_line(RESULT);
END;
--LOOP循环
DECLARE
num4 number := 0;
BEGIN
LOOP
num4 := num4+1;
IF num4 > 10THEN
EXIT;
END IF;
DBMS_OUTPUT.PUT_LINE(num4);
END LOOP;
END;
--WHILE LOOP循环
DECLARE
num5 number := 1;
BEGIN
WHILE num5 < 5LOOP
num5 := num5 + 1;
DBMS_OUTPUT.PUT_LINE(num5);
END LOOP;
END;
--FOR LOOP循环
DECLARE
num6 number := 1;
BEGIN
FOR i IN1..10 LOOP
num6 := num6+i;
END LOOP;
DBMS_OUTPUT.PUT_LINE(num6);
END;
--动态SQL，执行DDL
BEGIN
EXECUTE IMMEDIATE'create table temp_table'
|| '(id integer,name varchar2(20))';
END;
SELECT * FROM temp_table;
DROP TABLE temp_table;
--执行PL/SQL语句块,以下代码中如果district表中有多行数据将会出错DECLARE
plsql varchar2(200);
BEGIN
plsql := 'DECLARE name varchar2(20);'
||'BEGIN
select ''桥口区'' into name from district ;
DBMS_OUTPUT.PUT_LINE(''当前日期是：''||name);
END;';
EXECUTE IMMEDIATE plsql;
END;
--绑定变量
DECLARE
id2 tab_t.id1%type;
name2 tab_1%type;
plsql varchar2(200);
BEGIN
plsql := 'insert into tab_t values(1)';
EXECUTE IMMEDIATE plsql returning id1 into id2 ; DBMS_OUTPUT.PUT_LINE(id2);
END;
create table tab_t
(