序列：
所谓序列，在oracle中就是一个对象，这个对象用来提供一个有序的数据列，这个有序的数据列的值都不重复。
1.序列可以自动生成唯一值
2.是一个可以被共享的对象
3.典型的用来生成主键值的一个对象
4.可以替代应用程序代码
5.当sequence的值存放在缓存中时可以提高访问效率。
oracle创建序列语法：
CREATE SEQUENCE name
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}]
note:
1.increment by n:表明值每次增长n(步长),如果n是正数就递增,如果是负数就递减 默认是1
2.start with n: 从n开始
3.{MAXVALUE n | NOMAXVALUE}： 设置最大值
4.{MINVALUE n | NOMINVALUE}： 设置最小值，start with不能小于最小值。
5.CYCLE | NOCYCLE ： 是否循环，建议不使用
6.CACHE n | NOCACHE ： 是否启用缓存,每次缓存n个值
序列的属性(伪列)：
1.nextval : 返回下一个可用的序列值。
就算是被不同的用户调用，每次也返回一个唯一的值。
2.currval ：获取序列当前的值。
在currval调用之前，必须保证nextval已经获取过一次值。
例：create sequence testseq_id_seqt1
maxvalue 5
minvalue 2
CYCLE
NOCACHE;
//第一个值先nextval 后 currval
select testseq_id_seqt1.nextval from dual;
select testseq_id_seqt1.currval from dual;
使用sequence：
create table test_seq(
id number(3) primary key
);
1.向表中插入数据
insert into test_seq values(testseq_id_seq.nextval);
select * from test_seq;
2.查看序列的当前值
select testseq_id_seq.currval from dual;
3.获取序列的下一个值。
select testseq_id_seq.nextval from dual;
note:可以通过数据字典user_sequences查看当前用户所拥有的序列信息。
例如：
select sequence_name,min_value,max_value,last_number,INCREMENT_BY,cache_size
from user_sequences
where lower(sequence_name) = 'testseq_id_seq';
修改sequence：
ALTER SEQUENCE name
[INCREMENT BY n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}]
note：
1.必须是序列的拥有者，或者具有alter权限
2.修改后的序列，只对之后的值起作用。
3.不能修改start with，如果想改，只能删除，重新创建，启动。
alter sequence testseq_id_seqt1
nomaxvalue
minvalue 2
nocycle
NOCACHE;
删除sequence：
drop sequence seq_name;
例如：
drop sequence testseq_id_seq;
索引：
索引是关系数据库中用于存放每一条记录的一种对象，主要目的是加快数据的读取速度和完整性检查。
建立索引是一项技术性要求高的工作。一般在数据库设计阶段的与数据库结构一道考虑。应用系统的
性能直接与索引的合理直接有关
概念：
1. 类似书的目录结构
2、 Oracle 的“索引”对象，与表关联的可选对象，提高SQL查询语句的速度
3、 索引直接指向包含所查询值的行的位置，减少磁盘I/O
4、 与所索引的表是相互独立的物理结构
5、 Oracle 自动使用并维护索引，插入、删除、更新表后，自动更新索引
//索引由数据库自动使用
创建：
1.自动创建
当在表中指定了primary Key或者unique约束时会自动创建唯一值索引。
2.用户创建。
用户可以创建非唯一值所在以提高在访问行时的效率。
语法：
CREATE INDEX index_name
ON table_name (column[, column]...);
例如：
create index myIndex on s_emp (last_name);
创建成功后可以通过如下语句查看：
select index_name,index_type from user_indexes;
select * from user_ind_columns;
创建索引的原则：
1.列经常作为where子句的限定条件或者作为连接条件
2.列包含的数据量很大，并且很多非空的值。空值不能被索引，只有唯一索引才真正提高速度,
一般的索引只能提高30%左右
3.两个或者更多列频繁的组合在一起作为where的限定条件或者连接条件
4.列总是作为搜索条件
5.索引用于查出的数据量占2%~4%的情况
6.索引不是越多越好，不是索引越多越能加速查找。
7.要索引的表不经常进行修改操作
删除索引：
语法：DROP INDEX index_name;
例如：drop index myIndex;
权限控制
权限允许用户访问属于其它用户的对象或执行程序，
ORACLE系统提供权限：Object 对象级、System 系统级
查看权限的数据字典：
字典名 含义
ROLE_SYS_PRIVS System privileges granted to roles
ROLE_TAB_PRIVS Table privileges granted to roles
USER_ROLE_PRIVS Roles accessible by the user
USER_TAB_PRIVS_MADE Object privileges granted on the user's objects
USER_TAB_PRIVS_RECD Object privileges granted to the user
USER_COL_PRIVS_MADE Object privileges granted on the columns of the user's objects
USER_COL_PRIVS_RECD Object privileges granted to the user on specific columns
1.系统权限（系统权限是对用户而言）：
系统权限分类：
DBA: 拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构。
RESOURCE:拥有Resource权限的用户只可以创建实体，不可以创建数据库结构。
CONNECT:拥有Connect权限的用户只可以登录Oracle，不可以创建实体，不可以创建数据库结构。
对于普通用户：授予connect, resource权限。
对于DBA管理用户：授予connect，resource, dba权限。
DBA拥有最高的系统权限：
1，可以创建用户
语法：create user username identified by password;
当用户创建成功之后，此用户什么权限都没有，甚至不能登录数据库。
2. 赋予权限：
一个用户应该具有的基本权限包含：
CREATE SESSION
CREATE TABLE
CREATE SEQUENCE
CREATE VIEW
CREATE PROCEDURE
如果有多个用户他们都具有相同的权限(create session,create table,create sequence),赋予权限的动作过于麻烦，要给每个用户分别制定这些权限，因此oracle提出角色的概念，可以将权限赋值给角色，然后再将角色赋值给用户。
角色：一组权限的集合
例如：
grant resource,connect to test;
此时resource，connect就是角色。
查询resource,connect 具有哪些权限可以使用：
select privilege,role from role_sys_privs
where role = 'CONNECT' or role ='RESOURCE';
语法：
grant xxxx to user_name ;
例如：
grant create view to test;
3.回收权限
语法：revoke xxx from user_name;
4.修改密码：
语法：alter user xxx identified by xxxx;
5.删除用户：
语法：drop user username [cascade];
☆note: cascade:当用户下有表的时候，必须使用cascade级联删除。
例如： drop user test cascade;
//查询有哪些用户:select username from dba_users;//dba登录
2.对象权限(针对对象，类似表对象等)：
对象权限：select, update, insert, alter, index, delete, all //all包括所有权限
对象的 拥有者拥有所有的权限。
1.给用户赋予操作对象的权限：
GRANT object_priv [(columns)]
ON object
TO {user|role|PUBLIC}
[WITH GRANT OPTION]; //允许分配到权限的用户继续将权限分配给其它用户
例如：
创建test用户，创建表test22
grant select on test22 to test;
给test用户赋予在test22表上进行查询的权利。
grant update(id) on test22 to test;
给test赋予能够更新test22表上id列的权限。
/insert
2.回收权限：同系统权限。
语法：revoke xxx on obj from user;
例如：
revoke select , update on test22 from test;
视图：
所谓视图就是提取一张或者多张表的数据生成一个映射，管理视图可以同样达到操作原表的效果，方便数据的管理以及安全操作。(是基于一个表或多个表或视图的逻辑表，本身不包含数据，通过它可以对表里面的数据进行查询和修改。视图基于的表称为基表)视图其实就是一条查询sql语句，用于显示一个或多个表或其他视图中的相关数据。视图将一个查询的结果作为一个表来使用，因此视图可以被看作是存储查询结果的一个虚拟表。视图来源于表，所有对视图数据的修改最终都会被反映到视图的基表中，这些修改必须服从基表的完整性约束。
视图的存储：
与表不同，视图不会要求分配存储空间，视图中也不会包含实际的数据。视图只是定义了一个查询，视图中的数据是从基表中获取，这些数据在视图被引用时动态的生成。由于视图基于数据库中的其他对象，因此一个视图只需要占用数据字典中保存其定义的空间，而无需额外的存储空间。
视图的优势：
1.信息隐藏
比如s_emp表中有工资，可以创建视图，隐藏工资信息。（可以配合权限，让某个用户只能查看
视图，不能查看表。）通过视图可以设定允许用户访问的列和数据行。
2.使复杂查询变得简单,简化用户的SQL 语句,实际上就是SQL语句操作的结果作为视图的基表来使用。
3.数据独立。
4.相同数据的不同展示形式。
视图的分类：
1.简单视图
2.复杂视图
比较：
1、简单视图只从单表里获取数据，复杂视图从多表；
2、简单视图不包含函数和数据组，复杂视图包含；
3、简单视图可以实现DML操作，复杂视图不可以
简单视图 复杂视图
涉及到的表个数 1 1个或多个
包含函数 不包含 包含
包含组数据 不包含 包含
通过视图使用DML 可以 不可以
视图的创建：
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view_name
[(alias[, alias]...)]
AS
select 。。。。
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY]
note:
1.or replace:若所创建的试图已经存在，ORACLE自动重建该视图
2.force| noforce: 即使基表不存在也要建立该视图 | 基表不存在就不建立此视图，默认值。
3.alias: 为视图产生的列定义的别名(相当于给子查询的结果列起别名)
4.子查询中可以包含复杂的查询语法，这些细节都对用户隐藏。
5.子查询中不能包含order by子句。
6.WITH CHECK OPTION 插入或修改的数据行必须满足视图定义时的约束；换句话说，加上该关键词表示对
view进行dml操作的时候，只能操作select语句中where条件限制的内容
7.WITH READ ONLY ：该视图只读，不能在这个视图上进行任何DML操作。
//在没有WITH CHECK OPTION和 READ ONLY 的情况下，子查询中不能使用ORDER BY 子句
8.查看视图结构： desc view_name;
例如创建简单视图（可以通过DML语句修改）：
如果权限不足，sqlplus "/as sysdba"-->grant create view to briupz(表示用户名)
create or replace view myView
as
select id,last_name,start_date
from s_emp
where id <= 4;
此时可以使用：
1.查看视图中所有数据信息
select * from myView;
SELECT view_name,text from user_views;
2.执行插入：
insert into myView values(111,'haha','03-5月-16'); 插入成功！
3.再次查看，找不到刚插入的数据，因为这条数据不满足id<=4,但是查看原始表s_emp，有这条数据。
如果：
create or replace view myView
(id,name,s_date)
as
select id,last_name,start_date
from s_emp
where id <= 4
with check option;
此时可以使用：
1.查看视图中所有数据信息
select * from myView;
2.执行插入：
insert into myView values(121,'haha','03-5月-16'); 插入失败！，因为视图的约束时id<=4,现在插入的id值为121，所以失败！
with check option 插入或修改的数据行必须满足视图定义时的约束
视图上的DML操作应遵循的原则：
1.简单视图可以执行DML操作；
2.在视图包含GROUP 函数，GROUP BY子句，DISTINCT关键字时不能删除数据行；
3.在视图不出现下列情况时可通过视图修改基表数据或插入数据：
a.视图中包含GROUP 函数，GROUP BY子句，DISTINCT关键字；
b.使用表达式定义的列；
c.ROWNUM伪列。
d.基表中未在视图中选择的其他列定义为非空且无默认值。
创建复杂视图(不可以通过DML语句修改)：
复杂视图可能包含分组，组函数，多表连接等。
例如：
CREATE or replace VIEW myView
(name, minsal, maxsal, avgsal)
AS SELECT d.name, MIN(e.salary),
MAX(e.salary), AVG(e.salary)
FROM s_emp e, s_dept d
WHERE e.dept_id = d.id
GROUP BY d.name;
create or replace view myView2
(id,name,dept_name,s_date)
as
select se.id,last_name,name,start_date
from s_emp se,s_dept sd
where se.dept_id=sd.id and se.id <= 4
with check option;
insert into myView2 values(112,'aaa','Sales','01-1月-92');//error
查看视图信息
可以使用数据字典user_views;
删除视图对象：
DROP VIEW view_name;
存储过程：
存储过程(Procedure)是一组为了完成特定功能的SQL语句集合，经编译后存储在数据库中，用户通过指定存储过程的名称并给出参数来执行。
存储过程中可以包含逻辑控制语句和数据操纵语句，它可以接受参数、输出参数、返回单个或多个结果集以及返回值。
由于存储过程在创建时即在数据库服务器上进行了编译并存储在数据库中，所以存储过程运行要比单个的SQL语句块要快。同时由于在调用时只需用提供存储过程名和必要的参数信息，所以在一定程度上也可以减少网络流量、简单网络负担。
优点
1. 运行速度：对于很简单的sql，存储过程没有什么优势。对于复杂的业务逻辑，因为在存储过程创建的时候，数据库已经对其进行了一次解析和优化。存储过程一旦执行，在内存中就会保留一份这个存储过程，这样下次再执行同样的存储过程时，可以从内存中直接调用，所以执行速度会比普通sql快。
2. 减少网络传输：存储过程直接就在数据库服务器上跑，所有的数据访问都在数据库服务器内部进行，不需要传输数据到其它服务器，所以会减少一定的网络传输。但是在存储过程中没有多次数据交互，那么实际上网络传输量和直接sql是一样的。而且我们的应用服务器通常与数据库是在同一内网，大数据的访问的瓶颈会是硬盘的速度，而不是网速。
3. 可维护性：的存储过程有些时候比程序更容易维护，这是因为可以实时更新DB端的存储过程。有些bug，直接改存储过程里的业务逻辑，就搞定了。
4. 增强安全性：提高代码安全，防止 SQL注入。这一点sql语句也可以做到。
5. 可扩展性：应用程序和数据库操作分开，独立进行，而不是相互在一起。方便以后的扩展和DBA维护优化。
缺点
1. SQL本身是一种结构化查询语言，但不是面向对象的的，本质上还是过程化的语言，面对复杂的业务逻辑，过程化的处理会很吃力。同时SQL擅长的是数据查询而非业务逻辑的处理，如果如果把业务逻辑全放在存储过程里面，违背了这一原则。
2. 如果需要对输入存储过程的参数进行更改，或者要更改由其返回的数据，则您仍需要更新程序集中的代码以添加参数、更新调用，等等，这时候估计会比较繁琐了。
3. 开发调试复杂，由于IDE的问题，存储过程的开发调试要比一般程序困难。
4. 没办法应用缓存。虽然有全局临时表之类的方法可以做缓存，但同样加重了数据库的负担。如果缓存并发严重，经常要加锁，那效率实在堪忧。
5. 不支持群集，数据库服务器无法水平扩展，或者数据库的切割（水平或垂直切割）。数据库切割之后，存储过程并不清楚数据存储在哪个数据库中。
总结
1. 适当的使用存储过程，能够提高我们SQL查询的性能，
2. 存储过程不应该大规模使用，滥用。
系统存储过程:
系统存储过程是系统创建的存储过程，目的在于能够方便的从系统表中查询信息或完成与更新数据库表相关的管理任务或其他的系统管理任务。系统存储过程主要存储在master数据库中，以“sp”下划线开头的存储过程。尽管这些系统存储过程在master数据库中，但我们在其他数据库还是可以调用系统存储过程。有一些系统存储过程会在创建新的数据库的时候被自动创建在当前数据库中。
exec sp_databases; --查看数据库exec sp_tables; --查看表
exec sp_columns student;--查看列
exec sp_helpIndex student;--查看索引
exec sp_helpConstraint student;--约束
exec sp_stored_procedures;
exec sp_helptext 'sp_stored_procedures';--查看存储过程创建、定义语句
exec sp_rename student, stuInfo;--修改表、索引、列的名称
exec sp_renamedb myTempDB, myDB;--更改数据库名称
exec sp_defaultdb 'master', 'myDB';--更改登录名的默认数据库
exec sp_helpdb;--数据库帮助，查询数据库信息
exec sp_helpdb master;
示例：
--表重命名exec sp_rename 'stu', 'stud';select * from stud;--列重命名exec sp_rename 'stud.name', 'sName', 'column';exec sp_help 'stud';--重命名索引exec sp_rename N'student.idx_cid', N'idx_cidd', N'index';exec sp_help 'student'; --查询所有存储过程select * from sys.objects where type = 'P';select * from sys.objects where type_desc like '%pro%' and name like 'sp%';
用户自定义存储过程：
创建语法
create proc | procedure pro_name [{@参数数据类型} [=默认值] [output], {@参数数据类型} [=默认值] [output], .... ]as SQL_statements
创建不带参数存储过程
if (exists (select * from sys.objects where name = 'proc_get_student')) drop proc proc_get_studentgocreate proc proc_get_studentas select * from student;--调用、执行存储过程exec proc_get_student;修改存储过程alter proc proc_get_studentasselect * from student;带参存储过程
if (object_id('proc_find_stu', 'P') is not null) drop proc proc_find_stugocreate proc proc_find_stu(@startId int, @endId int)as select * from student where id between @startId and @endIdgoexec proc_find_stu 2, 4;带通配符参数存储过程
if (object_id('proc_findStudentByName', 'P') is not null) drop proc proc_findStudentByNamegocreate proc proc_findStudentByName(@name varchar(20) = '%j%', @nextName varchar(20) = '%')as select * from student where name like @name and name like @nextName;go exec proc_findStudentByName;exec proc_findStudentByName '%o%', 't%';带输出参数存储过程
if (object_id('proc_getStudentRecord', 'P') is not null) drop proc proc_getStudentRecordgocreate proc proc_getStudentRecord( @id int, --默认输入参数 @name varchar(20) out, --输出参数 @age varchar(20) output--输入输出参数)as select @name = name, @age = age from student where id = @id and sex = @age;go -- declare @id int, @name varchar(20), @temp varchar(20);set @id = 7; set @temp = 1;exec proc_getStudentRecord @id, @name out, @temp output;select @name, @temp;print @name + '#' + @temp;不缓存存储过程
if (object_id('proc_temp', 'P') is not null) drop proc proc_tempgocreate proc proc_tempwith recompileas select * from student;goexec proc_temp;加密存储过程
if (object_id('proc_temp_encryption', 'P') is not null) drop proc proc_temp_encryptiongocreate proc proc_temp_encryptionwith encryptionas select * from student;go exec proc_temp_encryption;exec sp_helptext 'proc_temp';exec sp_helptext 'proc_temp_encryption';带游标参数存储过程
if (object_id('proc_cursor', 'P') is not null) drop proc proc_cursorgocreate proc proc_cursor @cur cursor varying outputas set @cur = cursor forward_only static for select id, name, age from student; open @cur;go--调用declare @exec_cur cursor;declare @id int, @name varchar(20), @age int;exec proc_cursor @cur = @exec_cur output;--调用存储过程fetch next from @exec_cur into @id, @name, @age;while (@@fetch_status = 0)begin fetch next from @exec_cur into @id, @name, @age; print 'id: ' + convert(varchar, @id) + ', name: ' + @name + ', age: ' + convert(char, @age);endclose @exec_cur;deallocate @exec_cur;--删除游标分页存储过程
if (object_id('pro_page', 'P') is not null) drop proc proc_cursorgocreate proc pro_page @startIndex int, @endIndex intas select count(*) from product; select * from ( select row_number() over(order by pid) as rowId, * from product ) temp where temp.rowId between @startIndex and @endIndexgo--drop proc pro_pageexec pro_page 1, 4----分页存储过程if (object_id('pro_page', 'P') is not null) drop proc pro_stugocreate procedure pro_stu( @pageIndex int, @pageSize int)as declare @startRow int, @endRow int set @startRow = (@pageIndex - 1) * @pageSize +1 set @endRow = @startRow + @pageSize -1 select * from ( select *, row_number() over (order by id asc) as number from student ) t where t.number between @startRow and @endRow;exec pro_stu 2, 2;
存储过程语法摘抄自：http://www.cnblogs.com/hoojo/archive/2011/07/19/2110862.html
1182
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
