PUSQL学习1——PUSQL 基础

• 带着问题学习
  了解PL/SQL块的基本结构以及PL/SQL 块的分类;
  学会在PL/SQL块中定义和使用变量;
  学会在PL/SQL块中编写可执行语句;
  了解编写PL/SQL代码的指导方针;
  了解Oracle 1Og的性特征—新数据类型BINARY_FLOAT和BINARY_DOUBLE,以及指定字符串文本的新方法.

PL/SQL块简介

块 (Block)是PL/SQL的基本程序单元,编写PL/SQL程序实际就是编写PL/SQL块。要 完成简单的应用功能。可能只需要写一个简单的PL/SQL块；如果要实现复杂的应用功能，就要在PL/SQL块中嵌套其他的PL/SQL块；编写PL/SQL应用模块，块的嵌套层次没有限制。

PL/SQL块结构

由三部分组成，声明部分(DECLARE/declare)，执行部分(BEGIN-END/begin-end)，异常部分(EXCEPTION/exception)。
- 声明部分：用于定义常量、变量、游标、例外(异常)、复杂数据类型等；
- 执行部分：用于实现应用模块功能，包含了要执行的SQL语句和PL/SQL语句
- 例外部分：用于对于执行部分出现的例外进行处理

格式

-- 声明部分
declare 
  -- 声明变量、常量、例外(异常)、游标、复杂数据类型
  -- 声明时，需要确定数据类型的长度
begin
  -- 执行SQL语句或PL/SQL语句
exception
  -- 处理执行过程中出现的例外(异常)
end;  -- 块结束标记

注意：
declare(声明部分)和exception(例外/异常部分)是可选的，begin(执行部分)是不需要有的，块最终以end结束，end后面要加“；”号

注意，当使用dbms_output包输出数据或准息时，必须将SQL*Plus的环境变量serveroutput设置为on。

PL/SQL块分类

根据需要实现的应用模块功能，可以将PL/SQL块划分为匿名块、命名块、子程序和触发器等四类。

• 匿名块：
  匿名块是指没有名称的PL/SQL块，匿名块既可以内嵌到应用程序中，也可以在交互式环境中直接使用

declare
  e_no number := &eno;
  e_name varchar2(20) ;
begin
  select ename into e_name from emp 
  where empno = e_no;
  dbms_output.put_line(e_name);
exception
  when no_data_found then
    dbms_output.put_line('此员工不存在');
end;

上述例子中，因为直接通过declare定义的块，所以是一个匿名块

• 命名块：
  命名块是指具有特定名称标识的PL/SQL块，命名块与匿名块非常类似，只不过在PL/SQL块前使用<<>>加以标记。当使用嵌套块时，为了区分多级嵌套层次关系，可以使用命名块加以区分。

-- 这里用的是PL/SQL Developer进行测试，所以需要在命名块外部添加一个匿名块，有网友说在SQLPlus中，命名块可以直接写，不需要再匿名块中，有待实验
begin
  <<oute>>
  declare
    e_name varchar2(20);
  begin
    <<inne>>
    begin
      select ename into e_name from emp where empno = &eno;
    end;
    dbms_output.put_line(e_name);
  end;
end;

• 子程序
  子程序包括过程、函数和包，当开发PL/SQL子程序时，既可以开发客户端的子程序，也可以开发服务器端的子程序，客户端子程序主要用在Developer中，而服务器端子程序可以用在任意应用程序中。

1.过程：过程用于执行特定操作，当建立过程时，既可以指定输入参数(in), 也可以制定输出参数(out)。通过在过程中使用输入参数，可以将应用环境的数据传递到执行部分，通过使用输出参数，可以将执行部分的数据传递到应用环境。可以使用create or replace procedure命令创建过程。

格式：
create or replace procedure 过程名(参数列表) is
begin
sql语句;
end;


-- 创建一个独立的过程
create or replace procedure update_sal(esal in emp.sal%type, eno in number) is
begin
  update emp set sal = esal where empno = eno;
  commit;
end;
-- 执行这个过程方式一
begin
  update_sal(800, 7369);
end;
-- 执行这个过程方式二
SQL> exec update_sal(800, 7369);
-- 执行这个过程方式三
SQL> call update_sal(800, 7369);


注意：
1.过程的形式参数不能定义数据类型的长度
2.过程的形式参数中可以给定in或out表示这个参数是输入参数，还是输出参数，输出参数表示语句执行结束后需要用同数据类型的值接收
3.如果不写in或out，默认为in，即输入参数

2.函数：
函数用于返回特定数据，当建立函数时，在函数头部必须包含return子句，而函数体内必须包含return语句返回数据，可通过create or replace function命令创建函数

格式：
create or replace function 方法名(参数) return 数据类型 is 参数名 数据类型;
begin
sql语句;
return 参数名;
end;


-- 创建一个独立的函数
create or replace function annual_income(e_no emp.empno%type)
  return number is sum_sal number;
begin
  select sal+nvl(comm, 0) into sum_sal from emp where empno = e_no;
  return sum_sal;
end;
-- 执行函数
declare
  a number;
begin
 a := annual_income(7369);
 dbms_output.put_line(a);
end;

3.包：
包用于逻辑组合相关的过程和函数， 它由包规范(包头)和包体两部分组成，相当于Java中的借口和实现类。包规范用于定义公用的常量、变量、过程和函数，可以使用create or replace package创建包

-- 包头：
create or replace package 包名 is
  function find(参数列表) return 返回值类型;
  procedure 过程名(参数列表) ;
end;

create or replace package test is
  function find(a number) return number;
  procedure find2(b in number, c out number);
end;

包头(包规范)只包含了过程和函数的说明，而没有过程和函数的实现代码。包体用于实现包头(包规范)中的过程和函数，使用create or replace package body创建

-- 包体
create or replace package body 包体名 is 
  function 函数名(参数列表) return 返回值类型 is 返回值列表 ;
    begin 
      函数的方法体;
      return 返回值;
    end;
  procedure 过程名(参数列表) is 
    begin 
      过程方法体;
    end;
end;

create or replace package body test is
  function find(a number) return number is e_sal number ;
    begin
      select sal into e_sal from emp where empno = a;
      return e_sal;
    end;
  procedure find2(b in number, c out number) is 
    begin
      select sal into c from emp where empno = b ;
    end;
end;

当调用包的过程和函数时，在过程和函数名之前必须带上包名座位前缀(包名.子程序名)，如果访问其他方案的包，还必须加上方案名作为前缀(方案名.包名.子程序名)

declare 
  num number;
begin 
  num := test.find(7369);
end;

定义并使用变量

编写PL/SQL程序时，若临时存储数值，必须要定义变量和常量；若在应用环境和子程序之间传递数据，必须要为子程序指定参数。在PL/SQL程序中定义变量、常量和参数时，必须指定数据类型。PL/SQL中有四种数据类型：标量类型(Scalar)、复合类型(Composite)、参照类型(Reference)和LOB(Large Object)类型。

标量变量

指只能存放单个数值的变量。

• 常用标量类型
  1.varchar2(n)
  用于定义可变长度的字符串，其中n指定字符串的最大长度，其最大值为32767,当时用此数据类型时，必须指定长度。
  2.char(n)
  存储固定长度的字符串，n指定字符串的最大长度，最大值为32767字节，如果没有指定n，默认为1
  3.number(p,s)
  存储固定长度的证书和浮点数，p表示精度，指定数字的总位数，s表示标度，指定小数点后的数字位数
  4.date
  存储时间和日期数据，长度为固定的7个字节，当给date变量赋值时，数据需与日期格式和日期语言匹配
  5.timestamp
  是Oracle9i新增的数据类型，存储日期和时间数据，与date使用方法相同，在显示日期时，还会显示时间和上下午标记
  6.long和long raw
  long用于定义变长字符串，最大长度为32760字节；long raw用于定义变长的二进制数据，最大长度32760字节
  7.boolean
  存储布尔变量，其值为true、false、null；为PL/SQL数据类型，表列不能采用boolean数据类型
  8.binary_integer
  定义整数，数值范围-2147483647~2147483647之间，在Oracle9i之前，当在PL/SQL块中定义PL/SQL表时，必须使用该数据类型作为下标的数据类型，为PL/SQL数据类型，表列不能采用boolean数据类型
  9.binary_float和binary double
  是Oracle 10g 新增的数据类型，主要用于高速的科学计算。
• 定义标量变量
  如果要使用变量，需要在声明中定义变量
  1.语法

变量名 [constant] 数据类型 [not null] [:= | default 初始值]
constant可选，如果定义常量，则必须有这个标识
not null可选，规定数据是否可为null
:= 赋值符号、
defalut 定义数据的初始值，如果为常量，则必须定义

2.%type属性
定义变量时，可以使用%type属性确定变量的数据类型

sal emp.sal%type; -- 这就表示定义了一个名为sal，数据类型为emp表中的sal字段的数据类型的变量

这样定义变量的好处在于不容易出现数据类型错误
- 复合变量
存放多个值得变量，必须使用PL/SQL的复合数据类型定义；包括PL/SQL记录、PL/SQL表、嵌套表以及VARRAY等四种复合数据类型。
1.PL/SQL记录
需要在定义部分定义记录数和记录变量，当使用记录成员时，必须要加记录变量作为前缀(记录变量.记录成员)

-- 格式：
declare 
  type 复合类型名称 is record(
    参数列表
  );
begin
  执行体;
end;

-- 例子
declare 
  type emp_no_name is record(
       eno emp.empno%type,
       enam emp.ename%type);
  emp_no_name_test emp_no_name;
begin
  select empno, ename into emp_no_name_test from emp where empno = 7369;
  dbms_output.put_line(emp_no_name_test.eno || ' ' || emp_no_name_test.enam);
end;

2.PL/SQL表
类似于Java中的数组，但PL/SQL表的下标可以为负值，元素个数没有限制，需要先声明。

-- 格式：
declare
  type 表名 is table of 数据类型
    index by binary_integer;
  变量名 数据类型;
begin
  执行语句;
end;
-- 例子：
declare
  type emp_table is table of emp.sal%type
    index by binary_integer;
  emp_table_test emp_table;
begin
  select sal into emp_table_test(-1) from emp where empno = 7369;
  dbms_output.put_line(emp_table_test(-1));
end;