Oracle 常用plsql总结

删除表： 

drop table 表名; 

drop table 表名 cascade constraints; 级联删除 

trucate table 表名; 
(表结构还在，数据全部删除，释放表所占的空间，不支持回退,常用删除大表) 

更改表名 
rename 旧表名 to 新表名; 


索引（index） 

创建索引：Creating indexes(概念很重要对系统的性能影响非常大） 

建索引的目的就是为了加快查询速度。 

索引就相于一本的书的目录。索引点系统空间，属于表的附属物。删除一个表时，相对应的索引也会删除。索引是会进行排序。 

truncate 表时索引结构在，但是数据不存在。 

FTS  --- full table scan 全表扫描 
用索引就是为了快速定位数据：(理解时就以字典的目录为例) 
创建索引就是创建key和记录的物理位置（rowid）组成的键值对。索引是有独立的存储空间,但是和表是逻辑关联的,索引和表的关系是依附关系,表被删除了,索引也没有存在的意义也就被删除了 

在建表时会根据表中的PK或UK自动的建立唯一性索引。 

查看表的rowid： 
select rowid,first_name from s_emp; 
rowid 定义的信息有：object block table 

每条记录都有自己的rowid 

索引由谁创建：用户,建索引后会使DML操作效率慢，但是对用户查询会提高效率,这就是我们建索引的最终目的。 

创建一个索引： 
create index 索引名 on 表名 (字段名); 
create index testindex on test(c1, c2); 

索引分为唯一性索引，联合索引。索引中是不会维护空值的。 

哪些字段应该建索引：创建索引就是为了减少物理读，索引会减少扫描的时间。 
经常要用where的子句的地方，所以要用索引.用不用索引，关键要看所查询的数据与所有数据的百分比，表越大，查询的记录越少，索引的效率就越高. 

替换变量：用&符号来定义替换变量支持交互性提示,对于字符性的数字，一定要写在单引号之间 
set    verify on 
set    verify off; 
相当于开关变量，用于控制是否显示新旧的sql语句 
select id,last_name,salary from s_emp where title='&job_title'; 
更改交互的提示信息： 
accept  p_dname prompt ' 提示信息'; 
定义变量： 
define p_dname='abc'; 


关于oralce中产生序列(sequence) 可以自动产生唯一值 
create sequence  序列名; 

(不带参数时默认为从1 开始每次递增 1，oracle中为了提高产生序列的效率一般一次性产生20个序列放入当前会话的序列池中备用以加快效率，序列会出现不连续的动作回退操作不会影响序列取值) 

sequence 的参数： 

increment by n   递增量 
start with n     起始值 
maxvalue n       最大值  nomaxvalue  定义的最大值 
minvalue n       最小值 
cycle|no cycle   轮回  
cache n          缓存(第一次取时会一次取多少个id存起来) 

查看sequence 示图： 
desc    user_sequences ; 
select   sequence_name , cache_size , last_number  from  user_sequences   where   sequence_name  like 's_'; 
select  序列名.currval  from   dual    查看当前的序列数 
select  序列名.nextval  from   dual    查看下一个序列数，它会自动给当前的序列加１ 
为列：nextval          currval 
(开另一个session时取当前值不成功时，应该先取下一个值，再取当前值) 

清空当前会话的内存： 
alter system  flush   shared_pool;（执行此命令要有DBA权限，一般用户执行出错） 

修改序列：(此命令不常用，只需了解就行不必深究) 
alter  sequence  序列名  修改项； 
删除序列sequence 
drop sequence 序列名; 


视图 

创建视图： 
creating  views 视图名; 

视图就相当于一条select 语句,定义了一个视图就是定义了一个sql语句,视图不占空间,使用view 不会提高性能，但是能简单化sql语句 
（扩展知识： oracle  8i 以后的新示图） 
MV  物化视图(占存储空间，把select 结果存在一个空间，会提高查询视图,增强实时性，但是存在刷新问题，物化示图中的数据存在延迟问题，主要应用在数据仓库中用要用于聚合表) 

使用视图的好处：控制数据访问权限。 

如何创建一个视图的例子： 
create or replace view test_vi as select * from test1 where c1=1; 

or replace的意义，如果view存在就覆盖，不存在才创建。 
force|no force ，基表存在是使用，不存在是则创建该表。 

此时往表test1（base table 基表）中插入数据时：表中没能变化，视图中的数据发生改变 
从示图中插数据时相对应的表会发生改变: 
往示图中插数据时，会直接插进基表中，查看视图中的数据时，相当于就是执行创建时的select语句。 

限制对数据库的访问，简化查询。 
简单视图：来自于单表，且select语句中不能包括函数，能进行DML操作。 
复杂视图：来源于多张表，不能执行DML操作。 

视图的约束 
with read only 视图只读约束(O) 
with check option 不允许插入与where条件不符的记录，类似于check约束的功能(V) 

在select from 后也可以使用子查寻，这个写法也叫做内嵌视图 
例： 
select first_name,salary,avgsal from s_emp e,(select dept_id,avg(salary) avgsal from s_emp group by dept_id) s where e.dept_id=s.dept_id and e.salary>s.avgsal; 

删除视图 drop views 示图名; 

行号（rownum） 
关于rownum： 
rownum  有个特点要么等于1 要么小于某个值， 不能直接等于某个值, 不能大于某个值。 
rownum常用于分页显示。 
rownum只用于读入内存的数据。 


找出工资前三名的员工 
select first_name,salary 
from (select first_name,salary from s_emp order by salary desc) 
where rownum<=3; 

列出每一个表的外键的定义，主表表名，主表字段名，子表表名，子表字段名 （画出E-R图） 
   
select c.table_name,cc.column_name, 
       p.table_name,pc.column_name, 
       p.constraint_type 
from user_constraints c,user_cons_columns cc,user_constraints p,user_cons_columns pc 
where c.constraint_name=cc.constraint_name 
and p.constraint_name=pc.constraint_name 
and c.r_constraint_name=p.constraint_name 
and c.table_name='S_EMP' 
and c.constraint_type='R'; 

动态生成脚本 

set head off 
set feed off 
set echo off 
spool seletab.sql 
select 'select * from '||table_name||';' 
from user_tables; 
spool off