数据库中的事务、视图、序列 、同义词、索引、数据字典、PL/SQL块 、异常处理、存储过程...

2019独角兽企业重金招聘Python工程师标准>>>

事务 定义：用户一次操作都成功，或是都失败。 特点：acid

一对多 – 主键对外键

      级联删除，设置为null

多对多 – 通过中间表实现的

Inner join , left,right,full

1：一对多的级联

create table person( id varchar(32) primary key, name varchar(30) ); create table car( id varchar(32) primary key, name varchar(30), pid varchar(32), constraint fk1 foreign key(pid) references person(id) on delete cascade ); --如果删除主表中的数据则连中子表中的数据一块删除

insert into person values('111','Jack'); insert into car values('C001','Buick','111');

select * from car;

--删除主表中的数据 delete from person where name='Jack';

设置为删除时设置为null： --创建这个约束设置为级联删除时为null alter table car add constraint fk2 foreign key(pid) references person(id) on delete set null;

2：一对一 一个人，有一个证件。

两种方式： 主键对唯一 – 》　primary to unique

create table man( id varchar(32) primary key, name varchar(30) ); create table card( id varchar(32) primary key, gov varchar(50), mid varchar(32) unique, constraint cardfk1 foreign key(mid) references man(id) )

insert into man values('M001','Jerry');

insert into card values('C001','济南公安','M001');

-- select m.name,c.gov from man m inner join card c on m.id=c.mid;

主键对主键：

CREATE TABLE man2( id VARCHAR(32) PRIMARY KEY, NAME VARCHAR(30) ); CREATE TABLE card2( id VARCHAR(32) PRIMARY KEY, gov VARCHAR(50), CONSTRAINT cardfk2 FOREIGN KEY(id) REFERENCES man2(id) ); select m.name,c.gov from man2 m inner join card2 c on m.id=c.id;

3：再谈创建表与事务 在系统中有一种表叫临时表： 临时表的最大好处是数据可以自动的清空。

可以通过 temporary创建临时表：

1:在提交时删除数据的临时表 – 默认值

、

在提交时保存数据：

何时删除数据呢？ 在用户退出删除数据。

4：view - > 视图[]

Use the CREATE VIEW statement to define a view, which is a logical table based on one or more tables or views. A view contains no data itself. The tables upon which a view is based are called base tables.

权限： To create a view in your own schema, you must have CREATE VIEW system privilege. To create a view in another user's schema, you must have CREATE ANY VIEW system privilege.

工作： 简化查询的。 1：快速的创建view

2：视图的分类 1：读写view Select * from someView; Update | delete | insert.. SomeView - 通过view保存到了基表中去。

默认的，如果view从一个表中来读写view

2：只读view 只能执行select

3：限制view

功能，简化查询的

5：序列 sequence 从某一个值不断的递增的过程。 Use the CREATE SEQUENCE statement to create a sequence, which is a database object from which multiple users may generate unique integers. You can use sequences to automatically generate primary key values.

create sequence s1;

--查询时可以通过s1.nextval - 下一值，初始化序列 获取开始的值或是下一个值 select s1.nextval from dual; --当执行了s1.nextval之后，就可以执行s1.currval获取当前值 select s1.currval from dual;

用法： desc stud; insert into stud values(s1.nextval,'Jack'); select * from stud;

以下是查询sequence的信息：

create sequence s2 INCREMENT by 2 start with 0 maxvalue 10 minvalue 0 cycle nocache;

select s2.currval from dual; select s2.nextval from dual;

drop sequence s2; create sequence s2 INCREMENT by -2 start with 10 maxvalue 10 minvalue 0 cycle nocache;

select s2.currval from dual; select s2.nextval from dual;

6：同义词 别名 Use the CREATE SYNONYM statement to create a synonym, which is an alternative name for a table, view, sequence, procedure, stored function, package, materialized view, Java class schema object, user-defined object type, or another synonym.

create SYNONYM o2 for s2;

select o2.nextval from dual;

7：索引 主键是索引 唯一索引

An index is a schema object that contains an entry for each value that appears in the indexed column(s) of the table or cluster and provides direct, fast access to rows. Oracle supports several types of index: Normal indexes (by default, Oracle creates B-tree indexes) create index idx3 on person(name);

--查询索引的信息 select * from user_indexes;

8：数据字典 系统表 Dba_xxxx 管理员可以查询的系统表 Dba_users保存了所有用户信息 Dba_data_files 保存了所有系统的表空间信息 Dba_tablespaces User_xxx 每一个用户都可以查询表： User_tables 保存了所有某个用户的表 Tab只是一个view它数据来自于user_table User_constaints User_cons_colus User_indexes User_views V$ 性能控制表 管理员可以查询 V$session 所有字典表的字典表：

select * from v$session where username='HR';

alter system kill session '113,711';

select * from DICTIONARY order by table_name desc;

9：PL/SQL块 Procedure Language(面向过程) / 基于SQL[]

语法： Declare //声明变量的位置 name varchar(30); age int : = 90; mail stuid.email%type; - > 声明一个变量叫mail它的类型，与stud表的eimal类型一样 stud stud%rowType ; - > 行集类型 begin 代码块的开始 ｛ //代码块执行 Dbms_output.put_line(‘Hello’);

end; }

declare str varchar(30); begin str:='&nm'; dbms_output.put_line(str); end;

9.1:给变量赋值的方式 1：声明时赋值,代码块 := 2：查询赋值 declare nm varchar(30); begin select name into nm from person where id='P003'; dbms_output.put_line('姓名是:'||nm); end;

2.1：行集类型的赋值 declare pp person%rowType; begin select id,name into pp from person where id='P003'; dbms_output.put_line('姓名是:'||pp.name||','||pp.id); end;

2.2 赋多个值 declare pid varchar(30); pname varchar(30); begin select name,id into pname,pid from person where id='P003'; dbms_output.put_line('姓名是:'||pname||','||pid); end;

9.2、控制语句 If If 条件 then

End if;

If 条件 then .. Elsif 条件 then

End if;

Loop 转 Loop Exit when 条件; if … then exit; end if; End loop;

declare vsum int:=0; vi int:=1; begin loop vsum:=vsum+vi;

  exit when vi>=100;
  vi:=vi+1;
end loop;

     dbms_output.put_line('summ is:'||vsum);

end;

while .. ..loop end loop

declare vsum int:=0; vi int:=1; begin while vi<=100 loop vsum:=vsum+vi; vi:=vi+1; end loop;

     dbms_output.put_line('000summ is:'||vsum);

end;

for i in 1..100 loop … end loop

declare vsum int:=0; begin for vi in 1..100 loop vsum:=vsum+vi; end loop;

     dbms_output.put_line('000summ is:'||vsum);

end;

9.3、异常处理 将异常的处理放到所有代码的最后：

declare vsum int; begin vsum:='Jack';

dbms_output.put_line('sum is:'||vsum); --异常处理必须要所有代码的最后 Exception When value_error then ….. when others then dbms_output.put_line('有错'); end;

9.4、异常的编号和异常的信息 declare vsum int; begin vsum:='Jack';

dbms_output.put_line('sum is:'||vsum); --异常处理必须要所有代码的最后 exception when others then dbms_output.put_line('有错：'||SQLERRM||' =='||SQLCODE);
end;

9.5、抛出异常 declare vsum int:=99; --自定义异常 myexp exception; begin raise myexp;--自已经抛出异常[]

dbms_output.put_line('sum is:'||vsum); --异常处理必须要所有代码的最后 exception when others then dbms_output.put_line('有错：'||SQLERRM||' =='||SQLCODE);
end;

declare vsum int:=99;

begin --里面的值：-20000~ -20999 raise_application_error(-20900,'这是息愿意抛出的民宅');

dbms_output.put_line('sum is:'||vsum); --异常处理必须要所有代码的最后 exception when others then dbms_output.put_line('有错：'||SQLERRM||' =='||SQLCODE);
end;

10：存储过程 Procedure

create or replace procedure p1 as aa int; begin for i in 1..10 loop insert into person values(sys_guid(),'JJerry'||i); end loop; end;

select * from person;

exec p1;

转载于:https://my.oschina.net/dtz/blog/639702