oracle学习笔记

oracle的逻辑结构包括表空间(tablespace)，段(segment),数据块(data block)以及模式对象(schema)。


表空间是Oracle数据库最大的逻辑结构


查询表空间可以用:
select * from dab_data_files;


查询表的空闲信息可以用:
select * from dba_free_space;


段:oracle段有四种类型:数据段，索引段，回滚段，临时段


数据段---->用来存储用户数据,每个表都有一个相对应回滚段，段名和表名相同


查询用户的数据段信息:
select * from user_extents;


索引段用来存储系统，用户的索引信息
select * from all_indexes; ---查询所有的索引段
select * from user_indexes; ---查询用户的索引信息


回滚段用来存储用户修改前得信息，一个事务只能对应一个回滚段，而一个回滚段能够对应多个事务，存储多个事务的回滚数据。
临时段用于order by语句的排序和一些汇总。




区是磁盘分配的最小单位,磁盘按照区来进行分配，每次最少分配一个区。区存储于段中，它按连续的数据块存储。


可以用字典dab_tablespaces查询表空间中区的信息，可以用字典user_tables查询段空间中区的信息，可以用字典user_extents查询区的分配状态。


select * from dab_tablespaces;


BLOCK_SIZE: 数据块的大小
INITIAL_EXTENT: 初始化时分配区的大小，一般区的大小为BLOCK_SIZE的整数倍。
NEXT_EXTENT: 当初始化分配的区不够时，下一次扩展的区的大小
MIN_EXTENT: 区大小的下限
MAX_EXTENT: 区大小的上限


我们可以通过以下SQL语句分别查询表空间，段，区中区的分配信息
select * from dba_tablespaces;
sleect table_name,tablespace_name,min_extents,max_extents from user_tables;
select * from user_extents;


数据块是数据库中的最小单位，也是数据文件磁盘存储空间单位,也是数据块I/O的最小单位,数据块大小由DB_BOLOCK_SIZE参数决定


模式对象(Schema Object)
Object模式对象包括表，索引，约束，过程，函数，触发器等应用结构

我们可以通过下面的SQL语句查询表空间和当前用户下的schema对象
select * from dba_source;
select * from user_source;




Oracle物理存储结构
数据文件，控制文件，重做日志文件，归档文件，初始化参数文件，跟踪文件，口令文件，警告文件，备份文件。
        数据文件: 用来存储数据库中所有数据
控制文件: 用来记录和描述数据库的物理存储信息
重做日志文件: 用于记录外部程序（或用户）对数据库的改变操作
归档文件: 用于保存已经写满的重做日志文件
初始化参数文件: 用于设置数据库启动时的参数初始值
跟踪文件：用来记录用户进程，数据库后台进程的运行情况
口令文件: 用来保存SYSDBA,SYSOPER权限的用户名和SYS口令
警告文件: 用于记录数据库的重要活动以及发生的错误
备份文件: 用来存放数据库备份所产生的文件


创建数据文件
        数据文件是依附于表空间而存在，创建数据文件就是向表空间中添加文件
创建数据文件时，应该根据文件数据量的大小来确定文件的大小和增长方式
语法:
alter tablespace ... add datafile;
alter tablespace ... add tempfile;


向oracle数据库的users表空间中添加一个大小为10mb的数据文件。
alter tablespace users add datafile 'D:\users02.dbf' size 10m;

向oracle数据库的temp表空间中添加一个大小为5mb的临时文件。
alter tablespace temp add tempfile 'D:\temptest.dbf' size 5m;




修改数据文件大小
方法: 设置数据文件为自动增长方式
手工改变数据文件的大小

设置数据文件为自动增长方式
创建时设置数据文件为自动增长
创建后修改数据文件为自动增长


 AUTOEXTEND ON NEXT ...MAXSIZE...|UNLIMITED

手工改变数据文件的大小
 
 alter database datafile...resize...


为oracle数据库的users中添加一个自动增长的数据文件
 
 alter tablespace users add datafile 'D:\user32.dbf' size 10m autoextend on next 512k maxsize 50m;



修改oracle数据库test表空间的数据文件SN1.txt为自动增长方式

 alter database datafile 'd:\SN1.txt' autoextend on next 512k maxsize unlimited;



取消oracle数据库test表空间的数据文件SN1.txt的自动增长方式

 alter database datafile 'd:\SN1.txt' autoextend off;



将oracle数据库test表空间的数据文件SN1.txt的大小设置为8m

 alter database datafile 'd:\SN1.txt' resize 8m;



改变数据文件的可用性
概念
   可以通过将数据文件联机或者脱机来改变数据文件的可用性。

在下面几种情况下需要改变数据文件的可用性
要进行数据文件的脱机备份时，需要先将数据文件脱机。
要对数据文件进行重命名或者改变数据文件存储位置，需要先将数据文件脱机。
如果Oracle在写入某个数据文件时发生错误，会自动将该数据文件设置为脱机状态，并且记录在警告文件中。排除故障后，需要
以手动方式重新将该数据文件恢复为联机状态。
数据文件丢失或损坏，需要在启动数据库之前将数据文件脱机。



归档文件下数据可用性的改变
数据文件可用性的改变
alter database datafile ... online/offline;

临时文件可用性的改变
alter database tempfile ... online/offline;


在数据库处于归档模式下，将oracle数据库test表空间下的数据文件SN1.txt脱机。
alter database datafile 'd:\SN1.txt' offline;


将oracle数据库test表空间的数据文件SN1.txt联机。
alter database datafile 'd:\SN1.txt' online;



改变表空间所有数据文件的可用性
在归档模式下，将表空间脱机或联机
alter tablespace ...datafile offline/online;


在归档模式下，将临时表空间脱机或联机
alter tablespace ...tempfile offline/online;


在归档模式下，将test表空间中所有的数据文件脱机，但是test表空间不脱机。然后再将test表空间所有数据文件联机。
alter tablespace test datafile offline;
recover tablespace test;
alter tablespace test datafile online;




改变数据文件的名称或位置
改变同一表空间下的数据文件的名称或位置
alter tablespace...rename datafile...to


改变多个表空间中数据文件的名称或位置
alter database rename file...to


注意
   改变数据文件中的名称或位置时，Oracle只是改变记录在控制文件和数据字典中的数据文件信息，并没有改变操作系统中数据文件的名称和位置，并没有改变操作系统中数据文件的名称和位置，因此需要DBA手动更改操作系统中数据文件的名称和位置。




改变同一个表空间中的数据文件的名称和位置
将包含数据文件的表空间test置为脱机状态。
 alter tablespace test offline;

使用alter tablespace...rename datafile...to语句进行操作
将表空间test的数据文件SN1.txt重命名为SN2.txt
 alter tablespace test rename datafile 'D:\SN1.txt' to 'D:\SN2.txt';



改变多个表空间的数据文件的名称或位置

关闭数据库
shutdown


启动数据库到mount状态
startup mount



将表空间联机
 alter tablespace test online;


打开数据库
 alter database open;



执行alter databse rename file...to更新数据文件名称或位置。


在操作系统中，将users表空间中的users002.dbf文件复制到一个新的位置，如d:\oracle\product\10.2.0\oradata,修改tools表空间的
  数据文件tools01.dbf的名为tools001.dbf。
alter databse rename file 'd:\users002.dbf','d:\tools01.dbf'to 'd:\oracle\product\10.2.0\oradata','d:\tools001.dbf';





删除数据文件
删除某个表空间的某个空数据文件
   alter tablespace...drop datafile
删除某个临时表空间中的某个空的临时数据文件
   alter tablespace...drop tempfile
   alter database tempfile...drop

所谓的空数据文件或空临时数据文件是指为该文件分配的所有区都被回收。


删除数据文件或临时数据文件的同时，将删除控制文件和数据字典中与该数据文件或临时数据文件的相关信息，同时也将删除操作系统中对应的物理文件。


删除users表空间中数据文件users03.dbf和删除temp临时表空间中的临时数据文件temp03.dbf。
alter tablespace users drop datafile 'D:\oracle\product\10.2.0\oradata\orcl\users03.dbf';


删除临时数据文件temp03.dbf还可以表示为
alter database tempfile 'd:\oracle\product\10.2.0\oradata\orcl\temp02.dbf' drop including datafiles;


删除数据文件或临时数据文件时受到以下约束:
数据库运行在打开状态
数据文件或临时文件必须是空的
不能删除表空间的第一个或唯一的一个数据文件或临时数据文件
不能删除只读表空间中的数据文件
不能删除system表空间的数据文件
不能删除采用本地管理的处于脱机状态的数据文件。




查询数据文件信息
dba_data_files: 包含数据库中所有数据文件的信息，包括数据文件所属的表空间，数据文件编号等。
dba_temp_files: 包含数据库中所有临时文件的信息。
dba_extents: 包含所有表空间中已分配的区的描述信息。
user_extents: 包含当前用户所拥有的对象在所有表空间中已分配的区的描述信息。
dba_free_space: 包含表空间中空闲区的描述信息。
user_free_space: 包含当前用户可访问的表空间中空闲区的描述信息
V$DATAFILE: 包含从控制文件中获取的数据文件信息。
V$DATAFILE_HEADER: 包含从数据文件头部获取的信息。
V$TEMPFILE: 包含所有临时文件的基本信息



查询数据文件的详细信息
select tablespace_name,autoextensible,file_name from dba_data_files;


查询数据文件的增长方式
select tablespace_name,bytes,autoextensible,file_name from dba_data_files;


查询临时数据文件信息
select tablespace_name,file_name,autoextensibale from dba_temp_files;






利用OEM管理数据文件
创建数据文件
编辑数据文件
查看数据文件信息
删除数据文件



控制文件
   控制文件是一个很小的二进制文件
   控制文件描述了整个数据库的结构
   在加载数据库时，实例必须首先通过初始化参数文件找到数据库的控制文件。
   在数据库运行期间，控制文件始终在不断更新，以便记录数据文件和重做日志文件的变化
   每个数据库至少拥有一个控制文件。一个数据库也可以同时拥有多个控制文件。
   分配在不同的物理硬盘中，以免数据库或硬盘损坏时，能够利用备份的控制文件启动数据库实例，可以提高数据库的可靠性。


控制文件的内容
   数据库名称和标识
   数据库创建的时间
   表空间名称
   数据文件和重做日志文件的名称和位置
   当前重做日志文件序列号
   数据库检查点的信息
   回退段的开始和结束
   重做日志的归档信息
   备份信息
   数据库恢复所需要的同步信息


控制文件中的最大化参数包括:
   MAXLOGFILES: 最大重做日志文件组数量
   MAXLOGMEMBERS: 重做日志文件组中最大成员数量
   MAXLOGHISTORY: 最大历史重做日志文件数量
   MAXDATAFILES: 最大数据文件数量
   MAXINSTANCES: 可同时访问的数据库最大实例个数


控制文件管理策略
   Oracle建议最少有两个控制文件，通过多路镜像技术，将多个控制文件分散到不同的磁盘中。在数据库运行过程中，
始终读取CONTROL_FILES参数指定的第一个控制文件，并同时写CONTROL_FILES参数指定的所有控制文件。如果其中一个
控制文件不可用，则必须关闭数据库并进行恢复。


每次对数据库结构进行修改后(添加，修改，删除数据库文件)，应该及时备份控制文件。



控制文件的管理
   创建控制文件
   实现多路镜像控制文件
   备份控制文件
   删除控制文件
   查看控制文件的信息
   利用OEM管理控制文件


创建控制文件
   创建控制文件的情形
控制文件全部丢失或损坏；
需要修改数据库名称；
在Oracle 10.2.0之前的版本中，需要修改某个最大化参数。

   创建控制文件的步骤
制作数据库中的所有数据文件和重做日志文件列表
select member from v$logfile;
select name from v$datafile;
select value from v$parameter where name='control_files';
如果数据库仍然处于运行状态，则关闭数据库
shutdown
在操作系统级别备份所有的数据文件和联机重做日志文件
启动实例到nomount状态
startup nomount

利用前面得到的文件列表，执行create controlfile创建一个新控制文件。
在操作系统级别对新建的控制文件进行备份
如果数据库重命名，则编辑db_name参数来指定新的数据库名称
如果数据库需要恢复，则进行恢复数据库操作
如果创建控制文件时指定了norestlogs,可以完全恢复数据库。
   recover database;
如果创建控制文件时指定了resetlogs,则必须在恢复时指定
using backup controlfile。
   recover database using backup controlfile;

打开数据库
   如果数据库不需要恢复或已经数据库进行了完全恢复，则可以正常打开数据库。
       alter datebase open;

   如果在创建控制文件时使用了resetlogs参数，则必须指定以resetlogs方式打开数据库。
alter database open resetlogs;

实现多路镜像控制文件
   编辑初始化参数control_files
alter system set control_files=...scope=spfile;
   关闭数据库
shutdown immediate;
   拷贝一个原有的控制文件到新的位置，并重新命名
   重新启动数据库
       startup


备份控制文件
   将控制文件备份为二进制文件
alter database backup controlfile to...

   将控制文件备份为文本文件
alter database backup controlfile to trace
将控制文件备份到<ORACLE_BASE>\admin\<SID>\udump目录下的跟踪文件



   将控制文件备份为二进制文件，存放到D盘下
alter database backup controlfile to 'D:\control.bkp';


   将控制文件备份为文本文件
alter database backup controlfile to trace;



删除控制文件
   编辑control_file初始化参数，使其不包含要删除的控制文件;
   关闭数据库;
   在操作系统中删除控制文件;
   重新启动数据库


查询控制文件的信息
   V$database: 从控制文件中获取的数据库信息;
   V$controlfile: 包含所有控制文件名称与状态信息;
   V$controlfile_record_section: 包含控制文件中各记录文档段信息;
   V$PARAMETER: 可以获取初始化参数control_files的值




利用OEM管理控制文件
   查看控制文件信息
   备份控制文件


重做日志文件的概念
   重做日志文件，包含了用户对数据库所做的更新操作。
   重做日志文件由重做记录构成的。
   用户对数据库的修改都是在数据库的数据高速缓冲区中进行，同时将产生的重做记录写入重做日志缓冲区。在一定
条件下由DBWR进程将数据高速缓冲区中修改后的结果成批写回数据文件中，而重做日志缓冲区中的重做记录由LGWR进程周
期性的写入重做日志文件。
   利用重做日志文件恢复数据库是通过事务的重做(REDO)或回退(UNDO)实现的。



归档重做日志
   基本概念
Oracle数据库能够把已经写满的重做日志文件保存到指定的一个或多个位置，被保存的重做日志文件的集合称做归档
重做日志文件，这个过程称为归档。
根据是否进行重做日志文件归档，数据库运行可以分为归档模式或非归档模式。





游标
游标类似于编程语言中的指针，游标可以进行位置的移动，以循环访问结果集中每条记录。通过游标可以方便的访问当前记录
游标分为显式和隐式游标。显式游标可以被用户显式创建，打开，访问，关闭，即用户可以控制游标的整个生命周期。而隐式游标无需用户的全程控制，即可进行访问。




oracle函数中substr的作用
substr(string string,int a,int b)
参数1:string要处理的字符串
参数2: a截取字符串的开始位置(起始位置是0)
参数3: b截取字符串的长度(而不是字符串的结束位置)
例如:
substr('ABCDEFG',0); //返回: ABCDEFG,截取所有字符
substr('ABCDEFG',2); //返回: CDEFG,截取从C开始之后的所有字符
substr('ABCDEFG',1,3); //返回: ABC,截取从A开始3个字符，这里开始是1和3返回都是一个字符串
substr('ABCDEFG',1,100); //返回: ABCDEFG,100虽然超出预处理的字符串最大长度，但不会影响返回结果
substr('ABCDEFG',0,-3); //返回: EFG,注意参数-3，为负值时表示从尾部开始算起，字符串排列位置不变。


过程(Procedure)是一个存储在数据库中命名了的PL/SQL集合。它可以或不包含输入输出参数，是可执行的代码集合。
函数 也是存储在数据库中的PL/SQL集合。与过程的主要区别是函数必须有一个返回值。



PL/SQL支撑多种数据类型，如varchar2,number,date,boolean


Varchar2为可变长的字母数字数据类型。
在declare中，定义以;结束，所有varchar2便来定义类似于
 variable_name varchar2(max_length);
括号中的长度值为本变量的最大长度且必须是正整数，如:
vc_field varchar2(10);
在定义变量时，可以同时对其进行初始化，格式为:
vc_field varchar2(10) := 'STARTVALUE';


number用来表示所有的数值数据，说明格式为:
num_field number(precision,scale );
         precision可以有1~38个数字位,scale表示数字中小数点后的数字位数。
如: num_field number(12,2);表示num_field是一个整数部分最多10位，小数部分最多2位的变量。
 


date
此数据类型用于保存固定长度的日期值，date变量的说明为:
date_field date;
在缺省时，Oracle以DD-MON-YY格式显示日期。
(用户可以用to_char或to_date来改变缺省的日期格式)


boolean
这种数据类型只有两个值: true或false。使用boolean时，如果测试结果为true，则做某事，否则做另外的事。



pl/sql的组件
块结构
 pl/sql程序是由独立的变量声明，执行代码和异常处理等部分代码块写成的。pl/sql可以作为一个命名的子程序存放在数据库中，当在数据库中存储pl/sql时，子程序包括存储单元命名的头部分，程序类型的声明以及可选的in,out和in out参数的定义。只是可执行部分定义的begin和end语句是固定的，declare和exception部分是可选的。
下面是在一个无名块和一个存储过程的例子。
--无名块
declare
...
begin
...
end;



--存储过程
create or replace precodure_name
as
    --声明部分自动跟着as语句而不需要编码
    ...
    ...
begin
    ...
    ...
exception
    ...
    ...
end;


-- pl/sql块可以嵌套，在主begin/end块中可以产生许多begin/end块。
下面是一个嵌套快的例子:
create or replace procedure callculate_rebate
(pharmacy_id in number)
as
   ...
   ...
begin
   ...
   ...
  begin
    ...
    ...
  exception
    ...
    ...
  end;
 exception
 ...
 ...
end;




-- 一个匿名块中的自主事务声明只能由第一个声明部分组成，自主事务声明不允许任何嵌套。
下面的例子说明了怎样创建一个自主事务块:
declare
  prama autonomous_transaction;
begin
  ...
end;




声明部分
   此pl/sql块用于定义变量。
create or replace procedure samp(i_salary in number,i_city in number)
as
--这是declare段；因为我们正在进行命名存储对象的编码，declare是隐含的，不需要写出
accum1 number;
accum2 number;
h_date date := sysdate;变量能在此初始化
      status_flag varchar2(1);
      mess_text  varchar2(80);
      temp_buffer varchar2(1);
      --下面的游标
      cursor my_cursor
      is
      select employee_number,last_name,first_name
from employee a, department b
        where a.department_number = b.department_number
 and a.salary > i_salary
 and b.location = i_city;
       begin
         ...
         ...
       end;



      控制结构
控制结构是所有程序设计语言的核心。


      PL/SQL提供三种if逻辑结构供用户测试
      (1)if-then
      这个结构用于测试一个简单条件。如果该条件true，则执行一行或多行代码；如果为false，则程序控制转到测试后面的代码。
      下面的代码说明pl/sql这个逻辑的实现:
       if var1>10 then
var2 := var1+20;
       end if;
       上面的语句也可以用下列语句替换
       if not (var1<=10) then
 var2 := var1+20;
       end if;


       if-then允许嵌套
       if var1>10 then
if var2<var1 then
   var2 := var1+20;
end if;
       end if;


在pl/sql中实现if逻辑有两条规则:
 规则1
 每个if语句都有自己的then,以if开始的语句行不跟语句结束符(;)。
 规则2
 每个if语句块以相应的end if结束。


       (2)if-then-else
这种结构与if语句非常相似，唯一不同的是条件为flase时，执行跟在else后的一条或多条语句。
下面是例子:
if var1>10 then
 var2 := var1+20;
else
 var2 := var1*var1;
end if;



if-then-else语句也可以嵌套，如下所示:
if var1>10 then
  var2 := var1+20;
        else
  if var1 between 7 and 8 then
     var2 := 2*var1;
  else
     var2 := var1*var1;
  end if;
       end if;


下面是Pl/sql中if的另两条规则:
规则3
每个if语句有且只有一个else。
规则4
else语句行不跟语句结束符。


(3)if-then-elsif
这种结构用于替代嵌套if-then-else结构。前面清单中的嵌套if-then-else语句可以改写为:
if var1>10 then
  var2 := var1+20;
elsif var1 between 7 and 8 then
  var2 := 2*var1;
else
  var2 := var1 * var1;
end if;

Pl/sql中有关if逻辑的最后一条规则:
规则5

elsif无匹配的end if。
下面的代码段中，end if仿佛针对前面的elsif：
if var1>10 then
  var2 := var1+20;
elsif var1 between 7 and 8 then
  var2 := 2*var1;
end if;
(实际上，该end if属于本语句块开始的if,而不属于elsif关键字)



在任何if语句中可以有许多elsif语句。值得注意的是else语句是不需要的。下面例子说明一个有许多elsif语句的if语句；
if location = 'PHOENIX' then
  v_hockey_team_name := 'COYOTES';
elsif location = 'NEW YORK CITY' then
  v_hockey_team_name := 'RANGERS';
elsif locaton = 'OTTAWA' then
  v_hockey_team_name := 'SENATORS';
end if;




根据"the_act"的值，过程12a,12b,12g将被调用:
create or replace procedure license_transaction
(the_act int varchar2) as
begin
 if the_act='DLF' then --如the_act的值为DLF,那么
  12a;
 elsif the_act='DT' then --如果the_act的值为DT,那么
  12b;
 else
  12g;
 end if; --结束if
end;

循环
 循环提供了一遍又一遍直到完成执行同一个处理过程的能力。
 在编写循环语句时，要注意的是一定要确保在退出条件满足时有相应的退出代码。
 
PL/SQL中使用的几种循环形式
(1)LOOP-EXIT-END循环
此循环结构由三部分组成，其用法如下:
  cnt:=1; --在循环开始前，初始化循环计数器
  loop --第一部分：以循环关键字loop开始循环
     cnt:=cnt+1; --第二部分: 增加循环计数器的值
  if cnt>=100 then --测试cnt是否符合退出条件
     exit;         --满足退出条件，退出循环
  endif;
  ...
  ...
  end loop;




oracle中%type比直接定义的好处
当表类型改变的时候，不需要手动修改pl/sql块中调用的该类型


(2)loop-exit when-end循环
除退出条件检测有所区别外，此结构与前一个循环结构类似。
cut:=1; --在循环开始前，初始化循环计数器
loop --第一部分:以循环关键字loop开始循环
    cnt:=cnt+1; --第二部分:增加循环计数器的值
    exit when cnt>=100 --测试cnt是否符合退出条件
    ...
    ...
    ...
end loop; --第三部分: 关键字end loop结束循环
...
...


(3)while-loop-end循环
此结构在循环的while部分测试退出条件。
--在循环开始前，初始化循环计数器


cnt:=1;
while cnt<100 loop --第一部分:在每次执行循环前，while都要检查退出条件
...    --第二部分:循环体内部的可执行代码
cnt:=cnt+1;    --增加循环计数器的值以满足退出条件
...
end loop;    --第三部分:关键字end loop结束循环
...


(4)for-in-loop-end循环
最后介绍的这种循环结构重复执行预定义次数的循环。该循环结构也由三部分组成:
.for in部分定义跟踪循环的变量
.执行循环体中的一条或多条语句，直至控制循环的变量满足退出条件为止。
end loop部分结束循环。
 
下面是一个例子:
 for cnt in 1..3 
 loop
      insert into tab1 values('Still in loop',cnt);
 end loop








异常
   异常(Exception)是PL/SQL处理错误情况的方法。

下面是常见的pl/sql异常
  异常 说明
no_data_found 如果一个select语句试图基于其条件检索数据，此异常表示不存在满足条件的数据行
too_many_rows 由于隐式游标每次只能检索一行数据，使用隐式游标时，这个异常检测到有多行数据存在
dup_val_on_index 如果某索引中已经有某键列值，若还要在该索引中创建该键码值的索引项时，出现此异常。
value_error 此异常表示指定目标域的长度小于待放入其中的数据的长度。
 
当遇到预先定义的错误时，错误被当前块的异常部分相应的when...then语句捕捉。跟在when句子后的then语句的代码被执行。then语句执行后，控制运行到了紧跟着当前块的end语句的行。
通常用一个when others错误处理柄。如果你在异常部分有其他的错误处理柄，确定when others是最后一个。如果你错误的先写when others子句，它将捕捉所有的错误，即便是那些预定义的错误。






下面的代码显示了一个嵌套块和一对异常部分:
create or replace procedure
          veteran_info(i_vin in number,
                       o_spouse_exists out boolean,
                       o_benefit_exists out boolean)
as
   v_benefit_amount    number(7,2);
   v_spouse_id         number(9);
begin
   --
   --为配偶查找退伍军人的身份证数字
   -- for the spouse
   --
   select spouse_vin
   into v_spouse_id
   from vin_xref
   where client_vin = i_vin;
   --
   --如果一行返回，设置输出配置的标志
   --
   o_spouse exists := true;
     --
     begin      -- 如果上面的选择语句准确返回一行则这将执行
     --
     -- 决定配偶是否收到补贴
     --
     select benefit_amout
     into   v_benefit_amout
     from   spouse_benefit
            where  spouse_vin=v_spouse_id;
            --
            -- 如果一行返回，检查总数和设置输出补贴标志
            --
            if v_benefit_amout>0 then
               o_benefit_exists := true;
            end if;
            --
      exception
        when no_data_found then
             --在外部块中配偶存在单声他们没有收到补贴，设置输出标志
              o_spouse_exists := true;
              o_benefit_exists := false; --没有收到补贴
       end;
       --
      exception   --这是外部块的异常部分
        when no_data_found then
           --'退伍军人没有配偶。第一个查询语句没有返回一行。内部块不执行。
           --控制转到被执行块得异常部分。因为配偶不存在，设置两个输出标志为false
             o_spouse_exists := false;
             o_benefit_exists := false;
      end;
    




      空操作与空值结构
有时候，用户结束测试一个条件。当测试条件为true,什么工作都不做；而当测试值为false时，则执行某些操作。
例子:
if cut>=90 then
  null;
else
  insert into tab1 values('Still less than 90',cut);
end if;
关键字null表示不执行操作。"null"结构常用于捕捉某个异常，但不做任何处理，然后继续执行下一块代码而不终止程序单元。


      


游标
 游标定义类似于其他PL/SQL变量。显式游标(Explicit Cursor)和隐式游标(Implicit Cursor)。显式游标要声明(Declare)，在使用前要打开(Open),使用完毕要关闭(Close)。隐式游标用户无需打开或关闭。
与循环结构结合的显式游标处理返回多于一行的Select语句。与循环结合的游标将允许你每次处理一行。当Select语句预计只返回一行时，隐式游标将做的更好。




        显式游标
           是作为declare一部分定义，所定义的sql语句必须只包含select语句，并且不能用insert,update或delete关键字。当select语句可能返回零或多于一行，必须使用显式游标
 


下面是一个游标定义实例
  declare
  1_first_name    varchar2(30);
  1_last_name     varchar2(30);
  1_ssn           number(9);
  -- 在person表中,first_name, last_name是varchar2(20)而ssn是number(9)
  -- varchar2(20) and ssn is number(9)
  cursor region_cur is --定义了一个游标region_cur
    select first_name,last_name,ssn
      from person
     where region_number = region_number_in;
  begin
    open region_cur; --打开游标region_cur
    fetch region_cur into 1_first_name,1_last_name,1_ssn;
   --.使用完游标要关闭



下面的例子包括命名游标，打开游标，使用游标取数据，关闭游标
declare
     fname    varchar2(10);
     lname    varchar2(30);
     ssec_num varchar2(8);
     cursor region_cur is  --先定义游标
     select first_name,last_name,ssn
       from person
       where region_number = region_number_in;
   begin
     open region_cur;  --打开游标
     fetch region_cur into fname,lname,ssec_num;
     while region_cur%found
     loop  --开始循环
         if ssec_num is null then --如果ssec_num是空的
            insert into e_msg values(pin_in,'no ssnum');
         else
            insert into e_tab values(pin_in,sysdate);
         end if;
         fetch region_cur into fname,lname,ssec_num;
     end loop;
     close region_cur; --关闭游标
   end;




使用显式游标需要注意的是:用"%found"和"%notfound"检验游标(此处为"mycur")成功与否。如果游标按照其选择条件从数据库查询出一行数据，就返回成功。检验必须在游标关闭前执行。
if mycur%found then
  ...
end if;
if mycur%notfound then
  ...
  ...
end if;
...
...
fetch mycur into temp_buffer;
close mycur;
--因为游标已被关闭，下面的语句不被执行。
if mycur%found then
  ...
  ...
end if;



--循环执行游标取数操作时，检索出的总数据行数存放在系统变量"%rowcount"中。
while counter<100 loop
  fetch mycur into temp_buffer;  --所有的游标必须被取至一个或多个变量
  if mycur%rowcount <=50 then
  ...
  else
  ...
  end if;
  counter := counter+1;
end loop;



--所有的游标必须被取至一个或多个变量(取决于游标select列表中的列数)。
--游标的目标变量必须与游标select表中表列的数据类型一致:
---以下用法正确
--
declare
       cursor mycur is --声明一个游标mycur
                   select pin,            /* in是数字型*/
                   last_name              /*last_name是字符型*/
                   from person            --来自person表
                   where pin=pin_in;
       field1 varchar2(10);
       field2 number;
       begin
          open mycur;
          fetch mycur into field2,field1
       ...
   


如果试图打开一个已经打开的游标或关闭一个已经关闭的游标,将会出现错误。因此，用户在打开或关闭前，如果不清楚游标的状态，应该
用"%isopen"进行检查。根据其返回值为true或flase，采取相应的动作。
...
...
if mycur%isopen then
  null;
else
  open mycur;
end if;

--如果一个pl/sql块中使用了多个游标，那么每个游标的名字必须唯一。



游标的for循环
  ...
   ...
   declare
   cursor region_cur is  --定义一个游标region_cur
   select first_name,last_name,ssn
          from person
          where region number=region_number in;
    begin
     for region_rec in region_cur --用for循环遍历
     loop
         if region_rec.ssn is null then
         insert into e_msg values(pin_in,'no ssnum');
         else
                insert into e_tab values(pin_in,sysdate);
         end if;
     end loop;
    end;
    ...
    ...



     隐式游标
下述代码段使用了隐式游标。如果把select语句直接安排在行中，pl/sql会隐含地处理游标定义。在declare段中无隐式游标说明。
     ...
     begin
if counter >= 20 then
  select last_name
    into lname
    from person
    where pin = pin_in;
     ...
     else
     ...
       end if;
     end;
     使用隐式游标要注意以下几点:
     .每个隐式游标必须有一个into。
     if this_value >0 then
select count(*) into cnter from person;
     end if;
     .和显式游标一样，带有关键字"into"接收数据的变量时数据类型要与表列的一致。
     .隐式游标一次仅返回一行，使用时必须检查异常。最常见的异常为"no_data_found"和"too_many_rows"。


     
     用何种游标
推荐使用显式游标，因为它更有效，根据如下:
.通过检查PL/SQL的系统变量"%found"或"%notfound"确认成功或失败。使用显式游标的代码段简单地检测这些系统变量以确定使用
显式游标的select语句成功或失败。
.显式游标是在declare段中由人工定义的，这样pl/sql块的结构化程序更高(定义和使用分离)。
.游标的for循环减少了代码的数量，更容易按过程化处理。
.最好的程序员都使用显式游标。




PL/SQL的注释
 pl/sql是sql*plus和过程化代码共同构成的一个混合体。注释文本括在斜杠星号(/*注释*/)内，也可以由两个横杠(--)因此，如下所示:
 begin
         declare tfield varchar2(20);
          begin
          select desc_text
          into tfield
          from prod              /*prod是一个中心查询*/
         where pnum = 'FR4512'; /*表的拥有者为Planning*/
         end;
         end;


        begin
        declare tfield varchar2(20);
        begin
        select desc_text
        into tfield
        from prod                  --prod是一个中心查询
        where pnum = 'FR4512';     --表的拥有者为Planning
        end;
        end;




修改hr用户密码为tiger
首先，要以dba身份登录
然后
alter user hr identifield by tiger; /*这样，就将hr用户的密码修改为tiger了*/






--6月17日13:37分笔记
 %type用法，提取%type所在字段的类型


declare
  myid dept.id%type
  myname dept.name%type
begin
    select id,name into myid,myname from dept;
    dbms_output.put_line(myid);
    dbms_output.put_line(myname);
end;




 %rowtype用法，提取%rowtype所在的字段的类型
declare
   type type_dept is table of dept %rowtype
   index by binary_integer;
   tb type_dept;
    begin
     tb(1).id:='001';
     tb(2).id:='001';
     dbms_output.put_line(tb.COUNT);
    end;





type用法，相当于结构体
declare
 lv_order_date date:=sysdate;
 lv_order_txt varchar2(5) default '001';
 lv_last varchar2(10) not null:='us';
TYPE type_test is record
(
  myid dept.id%type,
  myname dept.name%type
);
  rec type_test;
begin
  lv_order_date:=sysdate;
  dbms_output.put_line(lv_last);
  select id,name into rec from dept;


  dbms_output.put_line(rec.myid);
  dbms_output.put_line(rec.myname);
end;


 


for循环
     declare
     i number(5); --声明一个数值型的变量i
     begin
        for i in 1..5 --从1循环到5,循环条件
         loop      --开始循环
          dbms_output.put_line(i); --输出数据      
         end loop;
     end;
     



         loop循环
       declare
      v number:=1;  --声明一个数值型的变量v
      begin         
        loop       --开始循环
          dbms_output.put_line(v);  --输出变量v的值
          exit when v>=5; --当变量v大于等于5的时候，退出循环
          v:=v+1; --变量递增1
        end loop;
      end;





while循环
         declare
      v number:=1; --声明一个数值型的变量v
      begin
        while v<10  --当变量v小于10时
        loop
            dbms_output.put_line(v); --打印出变量v的值
              v:=v+1; --v递增1
           end loop;
      end;


      
        
          error的设定


            declare
         t number:=1; --定义一个数值型的变量t
       begin
         if t>10 then --如果t>10那么
          dbms_output.put_line('t>10'); --打印出t>10
          elsif t>20 then --如果t>20
          dbms_output.put_line('t>20'); --打印出t>20
          else
          goto err; --跳转到err
          <<err>> 
          dbms_output.put_line('data_not found!'); 
          end if;
       end;







 exception的用法
         declare
          ex Exception; --定义一个名为ex的异常
        begin
          Update dept set name='Edison' --更新名字为edison,id为100的用户的数据
              where id='100'
          if sql%notfound then --如果没有找到数据,那么
              raise ex;  --抛出异常ex
           end if;
           exception
             when ex then --当异常ex发生时，那么
                 dbms_output.put_line('update failed.'); --打印出更新失败
        end;






--第2个例子
declare
   type rc_dept is record
   (
      myid dept.id%type,
      myname dept.name%type
      myaddr dept.addr%type
   );
   tb rc_dept;
  begin
     select id,name,addr into tb from dept where id=:gb_id;
     dbms_output.put_line('id:'||tb.myid);
     dbms_output.put_line('name:'||tb.myname);
     dbms_output.put_line('addr:'||tb.myaddr);
  exception
     when no_data_found then
          dbms_output.put_line('no record is found');
     when too_many_rows then
          dbms_output.put_line('too many rows are selected');
     when others then
          dbms_output.put_line('undefine error');
          dbms_output.put_line('error coede:'||sqlcode);
          dbms_output.put_line('error message:'||SQLERRM);
  end;
  





--if/else的用法
declare
    v1 number:=90;  --定义一个变量v1赋值为90
    begin
     if v1=10 then  --如果v1的值是10，那么
       dbms_output.put_line('v1 is 10'); --打印出v1的值是10
     elsif v1=20 then --如果v1的值是20，那么
       dbms_output.put_line('v2 is 20'); --打印出来的值就是20
     else
       dbms_output.put_line('v2 is others'); --打印出来的值就是v2 is others
     end if;
    end;




--case的用法
        declare
         v number :=10;
         begin
         case v       --特别要注意，case后面不能跟:，否则会发生ORA-01008: 并非所有变量都已绑定的错误
         when 10 then dbms_output.put_line('v is 10');
         when 20 then dbms_output.put_line('v is 20');
         else dbms_output.put_line('v is not 10 and 20');
         end case;
         end;




        
--procedure的建立和调用
create or replace procedure test_sp
      (test in number,outtest out number)
        is 
begin
            if test>10 then
                  printsomething('test is over 10!');
   else
         begin
                      outtest:=test;
     printsomething(outtest);
                  end;
end;
/


create or replace procedure printsomething
       (print in number)
is
begin
            dbms_output.put_line(print);
end;
/

create or replace procedure printsomething
(print in number)
is 
begin
   dbms_output.put_line(print);
end;
/


create or replace procedure printsomething
(print in char)
is 
begin
dbms_output.put_line(print);
end;
/