Oracle笔记2

建立序列

create sequence my_seq
start with 1001  //开始
increment by 1   //每次增长1
nomaxvalue   //  无最大值
minvalue 1001   //  最小值
cycle /nocycle   // 循环/不循环
cache 100 /nocache   // 缓存/不缓存

select my_seq.currval from dual ;--查询序列当前值，序列建立后不能直接用该方法

建立索引

创建B—树索引的语法：

create index 索引名 on 表名(列名）[tablespace  表空间];

创建位图索引语法：

create bitmap index 索引名 on 表名(列名）;

删除索引：drop index 索引名
分类：
按照数据存储方式，可以分为B树、反向索引、位图索引;
B-树索引建立在重复值很少的列上，而位图索引则建立在重复值很多、不同值相对固定的列上。
对于包含低基数列以及在SQL语句的WHERE子句中使用许多AND或OR运算符的数据仓库环境，非常适合使用位图索引
按照索引列的个数分类，可以分为单列索引、复合索引；
按照索引列值的唯一性，可以分为唯一索引和非唯一索引.
此外还有函数索引，全局索引，分区索引…

PL/SQL块

declare
      说明部分    （变量说明，例外说明 〕
begin
      语句序列   （DML语句〕… 
exception
      例外处理语句   
end;

数据类型

1、%type

v_ename emp.ename%type;  --定义和emp表的ename相同的类型

2、%rowtype

v_dept_row dept%rowtype;  --定义和dept相同的行类型

3、%record

type emp_record_type is record (  --定义一个记录类型，包含员工信息
  ename emp.ename%type,
  sal emp.sal%type,
  comm emp.comm%type,
  total_sal sal%type);
v_emp_record emp_record_type;--声明记录类型变量

4、table类型

type dept_table_type is table of dept%rowtype
index by binary_integer;  --定义一个记录类型
v_dept_table  dept_table_type;  --声明记录类型变量

流程控制语句

一、if 选择

if  条件表达式  then
     语句段
 end if;

if 条件表达式 then
	语句段1
else
	语句段2
end if;

if 条件表达式1 then
语句段1
elsif 条件表达式2 then
语句段2
elsif 条件表达式3  then
语句段3
......
elsif 条件表达式n
语句段n
end if;

二、case when选择

case
when 条件表达式1 then
语句段1;
when 条件表达式2 then
语句段2;
......
else
语句段n;
end case;

三、loop循环

loop
   <程序块 1>
   if <条件表达式> then
       exit
   end if
   <程序块 2>
end loop;

loop
   <程序块 1>
   exit  when<条件表达式>
   <程序块 2>
end loop;

while <条件表达式>  loop
     <程序块>
end loop;

四、for循环

for <循环变量>  in  <初始值>..<终止值> loop
     <程序块>
end loop;

存储过程

什么是存储过程：存储过程一般用于执行一个指定的操作，可以将常用的特定操作封装成过程。
创建存储过程：

create or replace procedure pro_do（参数名  in/out  类型...）
 as   声明部分
begin
   执行部分
  dbms_output.put_line('这是一个存储过程');
exception
  异常处理部分
end;

in 为输入类型参数，out 为输出类型参数

调用过程：
1、在pl/sql 块中调用：过程名（）；
2、call 过程名（）
3、exec 过程名（）

函数

函数用于返回特定数据，如果在应用程序中经常需要通过执行SQL语句来返回特定数据，则可以基于这些操作创建特定的函数。
函数和过程的结构类似，但必须有一个RETURN子句，用于返回函数值。函数说明要指定函数名、结果值的类型，以及参数类型。
语法：

create or replace function 函数名(参数类型)
 return 返回值类型
as 声明部分
begin
   执行部分
  return
exception
  异常处理部分
end;

函数调用：
1，在pl/sql 块中调用：函数名（）；
2，select 函数名() from dual;

过程与函数比较

1、过程与函数有许多相同的功能及特性
都使用IN模式的参数传入数据、OUT模式的参数返回数据；
输入参数都可以接收默认值，都可以传值；
调用时的实参都可以使用位置表示法或名称表示法；
都有声明部分、执行部分和异常处理部分；
2、不同：
一般而言，如果需要返回多个值或不返回值，就使用过程；
如果只需要返回一个值，就使用函数；
虽然函数带OUT模式的参数也能返回多个值，但是一般都认为这种方法属于不好的编程习惯或风格，过程一般用于执行一个指定的动作，函数一般用于计算和返回一个值。

视图

创建或修改视图：
create or replace view 视图名 as select... [with read only];
删除视图：drop view 视图名；

同义词

语法：

create synonym 同义词 for 对象；
--删除
drop synonym 同义词；

游标

游标分为显式游标和隐含游标两种，隐含游标用于处理SELECT INTO和DML语句 ；显式游标则用于处理SELECT语句返回的多行数据。
一、显示游标

--定义游标 
 cursor cursor_name IS
select_statement;
--打开游标
OPEN cursor_name;
--提取数据 
 fetch cursor_name into variable1,variable2,...;
--将游标中的数据一次性地提取到表结构中
fetch cursor_name bulk collect into collect1…;
--关闭游标 
 close cursor_name;

游标属性使用格式为：游标名 + 属性名
%ISOPEN
用于确定游标是否已经打开。如果游标已经打开，则返回值为TRUE；否则返回FALSE

%FOUND
检查是否从结果集中提取到数据。如果提取到数据，则返回值为TRUE；否则返回FALSE

%NOTFOUND
与%FOUND属性恰好相反，如果提取到数据，则返回值为FALSE；否则返回TRUE

%ROWCOUNT
返回到当前行为止已经提取到的实际行数
二、参数游标
定义：
cursor cursor_name(参数，类型) is select_statement;
参数只能指定类型，而不能指定长度。
把参数作为查询条件，打开游标时传参。

触发器

1、DML触发器：对数据库表进行DML操作时触发。
定义语法

create or replace trigger 触发器名字
before|after
update|insert|delete on table_name
for each row
begin
    ...
end;

2、instead of 触发器：为了在不能执行DML操作的复杂视图上执行DML操作，必须基于视图创建instead of 触发器。
语法：

create or replace trigger 触发器名字
instead of 
delete/update/insert on view_name 
for each row
  begin
        ...;
  end;

3、系统触发器：指基于oracle系统事件所创建的触发器
事件属性函数及功能：
ora_client_ip_address ： 返回客户端的IP地址
ora_database_name ：返回当前数据库名
ora_dict_obj_name ：返回DDL操作所对应的数据库对象名
ora_dict_obj_owner ：返回DDL操作所对应的数据库对象的所有者名
ora_dict_obj_type ：返回DDL操作所对应的数据库对象的类型
ora_instance_num ：返回历程号
ora_is_alter_column(column_name in varchar2) ：检测特定列是否被修改
ora_is_drop_column(column_name in varchar2) ：检测特定列是否被删除
ora_login_user ：返回登录用户名
ora_sysevent ：返回触发触发器的系统事件名

删除触发器：drop trigger 触发器名字;
禁用触发器：alter trigger 触发器名字 disable;
启用触发器：alter trigger 触发器名字 enable;
启用emp表上所有触发器：alter table emp enable all trigger;

oracle优化

一、优化
1、合理的数据库设计
2、建立优化索引
3、避免在索引列上使用计算
4、注意组合索引的顺序
5、where子句的连接顺序
6、减少对表的查询，减少访问数据库的次数
7、select子句中避免使用 *
8、用where子句替代having
9、尽量多使用commit
10、使用表的别名
二、删除重复行
删除emp表中的重复行

delete from emp e1
 where e1.rowid >
       (select min(e2.rowid) from emp e2 where e1.empno = e2.empno);