PL/SQL基础篇

PL/SQL(Procedural Language/SQL)是一种过程化语言，属于第三代语言，它与C、C++、Java等语言一样关注于处理细节，可以用来实现比较复杂的业务逻辑。它允许SQL的数据操纵语言和查询语句包含在块结构(block_structured)和代码过程语言中，使PL/SQL成为一个功能强大的事务处理语言。
PL/SQL程序都是以块（block）为基本单位，整个PL/SQL块分三部分：声明部分（用declare开头）、执行部分（以 begin开头）和异常处理部分（以exception开头）。其中执行部分是必须的，其他两个部分可选。无论PL/SQL程序段的代码量有多大，其基本结构就是由这三部分组成。

模板如下：
declare
　　/* 声明区(可选)：定义类型和变量、声明变量、声明函数、游标 */
　　begin
　　/* 执行区(必须的)：执行pl/sql语句或者sql语句 */
　　exception
　　/* 异常处理区(可选)：处理错误的 */
　　end;
如下所示为一段完整的PL/SQL块：
/*声明部分，以declare开头*/
declare v_id integer;
v_name varchar(20);
cursor c_emp is select * from employee where emp_id=3;
/*执行部分，以begin开头*/
begin 　open c_emp;　                        --打开游标
loop
fetch c_emp into v_id,v_name;　 　--从游标取数据
exit when c_emp%notfound ;
end loop ;
close c_emp;　 　                                --关闭游标
dbms_output.PUT_LINE(v_name);
/*异常处理部分，以exception开始*/
exception
when no_data_found then
dbms_output.PUT_LINE('没有数据');
end ;

1、常量与变量
定义常量与变量有如下规定：
--名称必须以字母开头
--名称长度不能超过30字符
--名称中不能包含减号（-）和空格
--不能是SQL保留字
SET SERVEROUTPUT ON;

DECLARE
v_empno   CONSTANT NUMBER(4):=7900;
v_ename   VARCHAR2(10);
v_job          VARCHAR2(9);
v_sal          NUMBER(7,2);
BEGIN
  SELECT  ename,job,sal  into v_ename,v_job,v_sal
  FROM emp WHERE empno=v_empno;
  DBMS_OUTPUT.PUT_LINE('雇员编号为：'||v_empno);
  DBMS_OUTPUT.PUT_LINE('雇员姓名为：'||v_ename);
  DBMS_OUTPUT.PUT_LINE('雇员职位为：'||v_job);
  DBMS_OUTPUT.PUT_LINE('雇员薪水为：'||v_sal);
END;

2、%TYPE和%ROWTYPE类型
--%type类型，例如emp.empno%type类型表示定义变量的类型与表emp中的empno字段的类型及长度相同
--%rowtype类型，例如emp%rowtype类型表示定义变量的类型与表emp中的一行所有字段的类型及长度相同
--%type类型只针对表中的某一列，%rowtype类型则针对表中的一行
DECLARE
v_empno   CONSTANT emp.empno%type:=7900;
v_emp       emp%rowtype;
BEGIN
  SELECT  *  into v_emp
  FROM emp WHERE empno=v_empno;
  DBMS_OUTPUT.PUT_LINE('雇员编号为：'||v_empno);
  DBMS_OUTPUT.PUT_LINE('雇员姓名为：'||v_emp.ename);
  DBMS_OUTPUT.PUT_LINE('雇员职位为：'||v_emp.job);
  DBMS_OUTPUT.PUT_LINE('雇员薪水为：'||v_emp.sal);
END;

3、PL/SQL记录类型和表类型
--记录类型与数据库中表的行结构非常相似，使用记录类型定义的变量可以存储由一个或多个字段组成的一行数据。
DECLARE
TYPE emp_type IS RECORD(
EMPNO NUMBER(4),                           
ENAME VARCHAR2(10),                       
JOB    VARCHAR2(9),                                              
SAL    NUMBER(7,2)     
);
v_emp  emp_type;
BEGIN
  SELECT empno,ename,job,sal  into v_emp                --empno,ename,job,sal的查询顺序与记录类型声明中定义的顺序一致
  FROM  emp  WHERE  empno=7900;
  DBMS_OUTPUT.PUT_LINE('雇员编号为：'||v_emp.empno);
  DBMS_OUTPUT.PUT_LINE('雇员姓名为：'||v_emp.ename);
  DBMS_OUTPUT.PUT_LINE('雇员职位为：'||v_emp.job);
  DBMS_OUTPUT.PUT_LINE('雇员薪水为：'||v_emp.sal);
END;

--使用记录类型变量只能保存一行数据，这限制了查询的返回行数。oracle提供了另外一种自定义类型，即表类型
--它是对记录类型的扩展，允许处理多行数据
DECLARE
  TYPE my_emp IS TABLE OF emp%ROWTYPE
  INDEX BY BINARY_INTEGER;
  t_emp my_emp;
BEGIN
  t_emp(1).empno:=6800;
  t_emp(1).ename:='TRACY';
  t_emp(1).job:='CLERK';
  t_emp(1).sal:=2500;
  t_emp(2).empno:=7200;
  t_emp(2).ename:='LILY';
  t_emp(2).job:='CLERK';
  t_emp(2).sal:=2800;
  DBMS_OUTPUT.PUT_LINE(t_emp(1).empno||'  '||t_emp(1).ename||'  '||t_emp(1).job||'  '||t_emp(1).sal);
  DBMS_OUTPUT.PUT_LINE(t_emp(2).empno||'  '||t_emp(2).ename||'  '||t_emp(2).job||'  '||t_emp(2).sal);
END;