Oracle PL／SQL语言基础

PL/SQL简介

PL/SQL(ProceduralLanguage/SQL，过程语言/SQL)

它是结合Oracle过程语言和结构化查询语言的一种扩展语言

PL/SQL支持多种数据类型，可以使用条件语句和循环语句等控制结构

PL/SQL可用于创建存储过程、触发器和程序包，也可以用来处理业务规则、数据库事件或给SQL命令的执行添加程序逻辑

PL／SQL的优点

支持 SQL    支持面向对象编程 (OOP)    更好的性能    可移植性    与 SQL 集成       安全性

PL/SQL的基本结构-1

PL/SQL 语言是程序化程序设计语言。

块 (Block) 是 PL/SQL 程序中最基本的结构，所有 PL/SQL 程序都是由块组成。

块中包含过程化语句和 SQL 的 DML 语句。这些块可以按顺序出现，也可以相互嵌套（一个块在另一个块的内部）

块的分类

匿名块

匿名块是出现在应用程序中的没有名字且不存储到数据库中的块

匿名块出现在 SQL 语句可以出现的地方，它们可以调用其他程序，却不能被其他程序调用

命名块

命名块是一种带有标签的匿名块，标签为块指定了一个名称

子程序

子程序是存储在数据库中的过程（ procedure ）、函数（ function ），生成之后可以被多次执行

PL/SQL的基本结构-2

PL/SQL 的块由变量声明、程序代码和异常处理代码 3 部分组成：

DECLARE

标记声明部分

变量的声明，必须要在 begin 前面

声明一些变量、常量、用户定义的数据类型及游标

name varchar (30); -- 声明时不设置值

name varchar (30):=‘Jack’;-- 声明带有默认值

name preson.name%type ; -- 直接引用一个表的数据类型

BEGIN

标记主程序体部分开始

主程序体，在这里可以加入各种合法语句

EXCEPTION

标记异常处理部分开始

异常处理程序，当程序中出现错误时执行这一部分

END

标记主程序体结束部分

PL/SQL基本结构-3

declare

      说明部分    （变量说明，例外说明 〕

begin

      语句序列   （DML语句〕…

exception

      例外处理语句  

End;

字符集

PL/SQL 语言有效字符包括以下三类

所有大写和小写英文字母

0~9 的阿拉伯数字

操作符，包括 ( 、 ) 、 + 、 - 、*、 / 、 < 、 > 、 ! 、 = 、 @ 、 % 等

PL/SQL 标识符

标识符的最大长度为 30 个字符，不区分大小写，但建议在标识符中适当使用大小写，以增加程序的可读性。

PL/SQL常量和变量

在 PL/SQL 程序运行时，需要定义一些变量来存放一些数据。

常量和变量在使用前必须声明，可以使用 DECLARE 对变量进行声明，语法如下：

DECLARE

    <变量名> <变量类型>:=默认值;

  ….

在 DECLARE 块中可以同时声明多个常量和变量。声明普通常量或变量是需要说明以下信息：

常量或变量的名称

常量或变量的数据类型

变量说明

说明 变量  （char, varchar2, date, number, boolean, long)

varl             char(15);                  说明变量名、数据类型和长度后用分号结束说明语句。
married      boolean :=true;
psal            number(7,2);
my_name   emp.ename%type;                   引用型变量，即my_name的类型与emp表中ename列的类型一样
emp_rec     emp%rowtype;                    记录型变量集

记录变量分量的引用：

emp_rec.ename:='ADAMS';

声明常量

<常量名> constant <数据类型> := <值>;
:= 为赋值语句
关键字constant表示声明的是常量。常量一旦定义，在以后的使用中其值不再改变。
一些固定的大小为了防止有人改变，最好定义成常量。
例如
Pass_Score constant INTEGER := 60 ;

声明变量

声明变量的基本格式如下：
<变量名> <数据类型>[(宽度) := <初始值>];
变量声明是没有关键字，但要指定数据类型，宽度和初始值可以定义也可以不定义。
例如
Address VARCHAR2(30) := ‘地址未知’;
PL/SQL对一个未初始化的变量，将被默认赋值为NULL
例如
Address VARCHAR2(30);

案例-1

-- 打开输出

set severoutput on

--声明一个变量并输出

declare
   	name varchar(10):=‘HelloWorld';
  begin
     dbms_output.put_line(name);
  end;

案例 -2

例如：

 SET SERVEROUTPUT ON;
 DECLARE
 Pass_Score constant INTEGER:=60;
 Address VARCHAR2(30):='北京海淀区';
 BEGIN
	DBMS_OUTPUT.PUT_LINE(Pass_Score);
	DBMS_OUTPUT.PUT_LINE(Address);
 END;

使用 SET SERVEROUTPUT ON 命令设置环境变量 SERVEROUTPUT 为打开状态，从而使 PL/SQL 程序能够在 SQL*Plus 中输出结果

使用函数 DBMS_OUTPUT.PUT_LINE() 可以输出参数的值

案例-3

匿名块示例：

创建一个匿名程序块，该程序块用于接收用户输入的员工编号，查询并输出该员工的姓名，还用于处理用户输入的员工编号不存在的异常 

set serveroutput on
set verify off
DECLARE
 v_ename varchar2(50);
BEGIN
	select ename into v_ename from emp where empno=&eno;	
	dbms_output.put_line('您要查找的姓名是：'|| v_ename);       
EXCEPTION
	WHEN NO_DATA_FOUND THEN
	dbms_output.put_line('输入的员工编号不存在！');
END;

数据类型：

%TYPE 数据类型

当定义 PL/SQL 变量存放值时，必须确保变量使用合适的数据类型和长度，否则可能会在运行过程中出现 PL/SQL 运行错误 为了避免这种不必要的错误，可以使用 %TYPE 属性来定义变量

当使用 %TYPE 属性定义变量时， Oracle 会自动地按照数据库表列或其他变量来确定新变量的类型和长度 %ROWTYPE 数据类型

如果一张表中包含较多的列，则可以使用 %ROWTYPE 来定义一个表示表中一行记录的变量

示例：

根据输入的员工编号查找该员工信息，及其所在部门的信息。

declare
v_ename emp.ename%type;
v_deptno dept.deptno%type;
v_dept_row dept%rowtype;
begin
  select ename ,deptno into v_ename,v_deptno
			 from emp where empno=&eno;
  select * into v_dept_row from dept where deptno=v_deptno;
  dbms_output.put_line('您要查找的员工是：'||v_ename
			||'，所在部门信息为：');
  dbms_output.put_line('部门编号：'||v_deptno||
	'  部门名称：'||v_dept_row.dname||'   所在地：'||v_dept_row.loc);
end; 

%record数据类型

类似高级语言中的结构

首先需要定义记录类型和记录变量

当引用记录成员时，必须将记录变量作为前缀

示例：

根据输入的员工编号输出该员工的姓名、基本工资、奖金及实发工资。

Type  record_name is record
(
V1  data_type1  [not null] [:=default_value],
V2  data_type2  [not null] [:=default_value],
Vn  data_typen  [not null] [:=default_value]
);

declare 
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;--声明记录类型变量
begin
  select ename,sal,nvl(comm,0),sal+nvl(comm,0) into v_emp_record
  from emp where empno=7369;
  dbms_output.put_line('员工姓名：'|| v_emp_record.ename);
  dbms_output.put_line('基本工资：'|| v_emp_record.sal);
  dbms_output.put_line('奖金：'|| v_emp_record.comm);
  dbms_output.put_line('实发工资：'|| v_emp_record.total_sal);
end;

TABLE 数据类型

TABLE( 索引表 ) 相当于一个键值集合，键是唯一的，用于查找对应的值。键可以是整数或字符串

示例：

将部门编号是10和20的部门信息存储到table类型中，然后输出其信息。

declare
type dept_table_type is table of dept%rowtype
index by binary_integer;
v_dept_table  dept_table_type;
begin
  select * into v_dept_table(0) from dept where deptno=10;
  select * into v_dept_table(1) from dept where deptno=20;
  dbms_output.PUT_LINE('编号：'||v_dept_table(0).deptno||'   名称：'
		||v_dept_table(0).dname||'   所在地：'||v_dept_table(0).loc);
  dbms_output.PUT_LINE('编号：'||v_dept_table(1).deptno||'   名称：'
		||v_dept_table(1).dname||'   所在地：'||v_dept_table(1).loc);
end;