锐客网

  1. 首页 > 睿知 > it技术 > >

PL/SQL-尚硅谷

IT知识尚硅谷PL

【PL/SQL-尚硅谷】寸阳分阴须爱惜，休负春色与时光。这篇文章主要讲述PL/SQL-尚硅谷相关的知识，希望能为你提供帮助。


第一章 PL/SQL 程序设计简介

  • PL/SQL是一种高级数据库程序设计语言，该语言专门用于在各种环境下对ORACLE数据库进行访问。由于该语言集成于数据库服务器中，所以PL/SQL代码可以对数据进行快速高效的处理。除此之外，可以在ORACLE数据库的某些客户端工具中，使用PL/SQL语言也是该语言的一个特点。本章的主要内容是讨论引入PL/SQL语言的必要性和该语言的主要特点，以及了解PL/SQL语言的重要性和数据库版本问题。还要介绍一些贯穿全书的更详细的高级概念，并在本章的最后就我们在本书案例中使用的数据库表的若干约定做一说明。
本章主要重点：
  • PL/SQL概述
  • PL/SQL块结构
  • PL/SQL流程
  • 运算符和表达式
  • 游标
  • 异常处理
  • 数据库存储过程和函数
  • 包
  • 触发器
1.2 SQL与PL/SQL
a. 什么是PL/SQL?
  • PL/SQL是 Procedure Language & Structured Query Language 的缩写。ORACLE的SQL是支持ANSI(American national Standards Institute)和ISO92 (International Standards Organization)标准的产品。PL/SQL是对SQL语言存储过程语言的扩展。从ORACLE6以后，ORACLE的RDBMS附带了PL/SQL。它现在已经成为一种过程处理语言，简称PL/SQL。目前的PL/SQL包括两部分，一部分是数据库引擎部分；另一部分是可嵌入到许多产品（如C语言，java语言等）工具中的独立引擎。可以将这两部分称为：数据库PL/SQL和工具PL/SQL。两者的编程非常相似。都具有编程结构、语法和逻辑机制。工具PL/SQL另外还增加了用于支持工具（如ORACLE Forms）的句法，如：在窗体上设置按钮等。本章主要介绍数据库PL/SQL内容。
b. PL/SQL的好处(1) 有利于客户/服务器环境应用的运行
  • 对于客户/服务器环境来说，真正的瓶颈是网络上。无论网络多快，只要客户端与服务器进行大量的数据交换。应用运行的效率自然就回受到影响。如果使用PL/SQL进行编程，将这种具有大量数据处理的应用放在服务器端来执行。自然就省去了数据在网上的传输时间。
(2) 适合于客户环境
  • PL/SQL由于分为数据库PL/SQL部分和工具PL/SQL。对于客户端来说，PL/SQL可以嵌套到相应的工具中，客户端程序可以执行本地包含PL/SQL部分，也可以向服务发SQL命令或激活服务器端的PL/SQL程序运行。
c. PL/SQL 可用的SQL语句
  • PL/SQL是ORACLE系统的核心语言，现在ORACLE的许多部件都是由PL/SQL写成。
    在PL/SQL中可以使用的SQL语句有：
    INSERT，UPDATE，DELETE，SELECT … INTO，COMMIT，ROLLBACK，SAVEPOINT。
tips
提示：在 PL/SQL中只能用 SQL语句中的 DML 部分，不能用 DDL 部分，如果要在PL/SQL中使用DDL(如CREATEtable等)的话，只能以动态的方式来使用。
  • ORACLE 的 PL/SQL 组件在对 PL/SQL 程序进行解释时，同时对在其所使用的表名、列名及数据类型进行检查。
  • PL/SQL 可以在SQL*PLUS 中使用。
  • PL/SQL 可以在高级语言中使用。
  • PL/SQL可以 在ORACLE的 开发工具中使用。
  • 其它开发工具也可以调用PL/SQL编写的过程和函数，如Power Builder 等都可以调用服务器端的PL/SQL过程。
1.3 运行PL/SQL程序
  • PL/SQL程序的运行是通过ORACLE中的一个引擎来进行的。这个引擎可能在ORACLE的服务器端，也可能在 ORACLE 应用开发的客户端。引擎执行PL/SQL中的过程性语句，然后将SQL语句发送给数据库服务器来执行。再将结果返回给执行端。
第二章PL/SQL块结构和组成元素2.1 PL/SQL块
  • PL/SQL程序由三个块组成，即声明部分、执行部分、异常处理部分
    PL/SQL块的结构如下：
DECLARE
/* 声明部分: 在此声明PL/SQL用到的变量,类型及游标，以及局部的存储过程和函数 */
BEGIN
/*执行部分:过程及SQL 语句, 即程序的主要部分*/
EXCEPTION
/* 执行异常部分: 错误处理*/
END;



tips
其中 执行部分是必须的。
PL/SQL块可以分为三类：
  2. 无名块：动态构造，只能执行一次。
  3. 子程序：存储在数据库中的存储过程、函数及包等。当在数据库上建立好后可以在其它程序中调用它们。
  4. 触发器：当数据库发生操作时，会触发一些事件，从而自动执行相应的程序。
2.2 PL/SQL结构
  • PL/SQL块中可以包含子块；
  • 子块可以位于 PL/SQL中的任何部分；
  • 子块也即PL/SQL中的一条命令；
2.3 标识符
PL/SQL程序设计中的标识符定义与SQL 的标识符定义的要求相同。要求和限制有：
  • 标识符名不能超过30字符；
  • 第一个字符必须为字母；
  • 不分大小写；
  • 不能用’-‘(减号);
  • 不能是SQL保留字。
tips
提示:一般不要把变量名声明与表中字段名完全一样,如果这样可能得到不正确的结果.
例如：下面的例子将会删除所有的纪录，而不是KING 的记录；
DECLARE
Ename varchar2(20) := ’KING’; -- Ename 进行赋值 【:= 赋值】
BEGIN
DELETE FROM emp WHERE ename=ename;
END;



  • 变量命名在PL/SQL中有特别的讲究，建议在系统的设计阶段就要求所有编程人员共同遵守一定的要求，使得整个系统的文档在规范上达到要求。下面是建议的命名方法：
标识符
命名规则
例子
程序变量
V_name
V_name
程序常量
C_Name
C_company_name
游标变量
Name_cursor
Emp_cursor
异常标识
E_name
E_too_many
表类型
Name_table_type
Emp_record_type
表
Name_table
Emp
记录类型
Name_record
Emp_record
SQL\\* Plus 替代变量
P_name
P_sal
绑定变量
G_name
G_year_sal
2.4 PL/SQL 变量类型
  • 在前面的介绍中，有系统的数据类型，也可以自定义数据类型。下表是ORACLE类型和PL/SQL中的变量类型的合法使用列表：
a. 变量类型在ORACLE8i中可以使用的变量类型有：
类型
子类
说 明
范 围
ORACLE限制
CHAR
Character,String,Rowid,Nchar
定长字符串,民族语言字符集
0到32767，可选,确省=1
2000
VARCHAR2
Varchar,String,NVARCHAR2
可变字符串,民族语言字符集
0到32767，4000
4000
BINARY_INTEGER
带符号整数,为整数计算优化性能






NUMBER(p,s)
Dec,Double,precision,Integer,Int,Numeric,Real,Small int
小数, NUMBER的子类型，高精度实数，整数, NUMBER 的子类型整数, NUMBER 的子类型; 与NUMBER等价,与NUMBER等价整数, 比 integer 小




LONG
变长字符串
0到2147483647
32,767字节


DATE
日期型
公元前4712年1月1日至公元后4712年12月31日




BOOLEAN
布尔型
TRUE, FALSE,NULL
不使用


ROWID
存放数据库行号






UROWID
通用行标识符，字符类型






b. 复合类型
  • ORACLE 在 PL/SQL 中除了提供象前面介绍的各种类型外,还提供一种称为复合类型的类型---记录和表.
(1) 记录类型
  • 记录类型是把逻辑相关的数据作为一个单元存储起来，称作PL/SQL RECORD 的域(FIELD)，其作用是存放互不相同但逻辑相关的信息。
  • 定义记录类型语法如下TYPE record_type IS RECORD( --把它当作一个对象，需要声明变量，变量多个用逗号隔开,最后一个分号 Field1 type1[NOT NULL][:= exp1 ], Field2 type2[NOT NULL][:= exp2 ], . . .. . . Fieldn typen[NOT NULL][:= expn ] ) ;
    PL/SQL-尚硅谷

    文章图片

    或
    PL/SQL-尚硅谷

    文章图片
tips
提示:
  2. DBMS_OUTPUT.PUT_LINE 过程的功能类似于 Java 中的 System.out.println() 直接将输出结果送到标准输出中.
  3. 在使用上述过程之前必须将 SQL * PLUS 的环境参数 SERVEROUTPUT 设置为 ON, 否则将看不到输出结果:
    set serveroutput on
可以用 SELECT语句对记录变量进行赋值,只要保证记录字段与查询结果列表中的字段相配即可。
(2) 使用%TYPE
  • 定义一个变量，其数据类型与已经定义的某个数据变量的类型相同，或者与数据库表的某个列的数据类型相同，这时可以使用%TYPE。
  • 使用%TYPE特性的优点在于：
  • 所引用的数据库列的数据类型可以不必知道；
  • 所引用的数据库列的数据类型可以实时改变。
    PL/SQL-尚硅谷

    文章图片
c. 使用%ROWTYPE
  • PL/SQL 提供%ROWTYPE操作符, 返回一个记录类型, 其数据类型和数据库表的数据结构相一致。
  • 使用%ROWTYPE特性的优点在于：
  • 所引用的数据库中列的个数和数据类型可以不必知道；
  • 所引用的数据库中列的个数和数据类型可以实时改变。
    PL/SQL-尚硅谷

    文章图片
d. PL/SQL 表(嵌套表)PL/SQL 程序可使用嵌套表类型创建具有一个或多个列和无限行的变量, 这很像数据库中的表. 声明嵌套表类型的一般语法如下:
TYPE type_name IS TABLE OF
{datatype | {variable | table.column} % type | table%rowtype};



方法
描述
EXISTS(n)
Return TRUE if the nth element in a PL/SQL table exists;
COUNT
Returns the number of elements that a PL/SQL table currently contains;
FIRST LAST
Return the first and last (smallest and lastest) index numbers in a PL/SQL table. Returns NULL if the PL/SQL table is empty.
PRIOR(n)
Returns the index number that precedes index n in a PL/SQL table;
NEXT(N)
Returns the index number that succeeds index n in a PL/SQL table;
TRIM
TRIM removes one element from the end of a PL/SQL table.
TRIM(n) removes n element from the end of a pl/sql table.
DELETE
DELETE removes all elements from a PL/SQL table.
DELETE(n) removes the nth elements from a PL/SQL table.
DELETE(m, n) removes all elements in the range m to n from a PL/SQL table.
PL/SQL-尚硅谷

文章图片

tips
说明:
  2. 在使用嵌套表之前必须先使用该集合的构造器初始化它. PL/SQL 自动提供一个带有相同名字的构造器作为集合类型.
  3. 嵌套表可以有任意数量的行. 表的大小在必要时可动态地增加或减少: extend(x) 方法添加 x 个空元素到集合末尾; trim(x) 方法为去掉集合末尾的 x 个元素.
2.5 运算符和表达式(数据定义)
a. 关系运算符
运算符
意义
=
等于
< > , != , ~= , ^=
不等于
<
小于
>
大于
< =
小于或等于
> =
大于或等于
b. 一般运算符
运算符
意义
+
加号
-
减号
*
乘号
/
除号
:=
赋值号
=>
关系号
..
范围运算符
||
字符连接符
c. 逻辑运算符
运算符
意义
IS NULL
是空值
BETWEENAND
介于两者之间
IN
在一列值中间
AND
逻辑与
OR
逻辑或
NOT
取返,如IS NOT NULL, NOT IN
2.6 变量赋值
  • 在PL/SQL编程中，变量赋值是一个值得注意的地方，它的语法如下：variable:= expression ; -- variable 是一个PL/SQL变量, expression 是一个PL/SQL 表达式
a. 字符及数字运算特点
  • 空值加数字仍是空值：NULL + < 数字> = NULL
  • 空值加（连接）字符，结果为字符：NULL || < 字符串> = < 字符串>
b. BOOLEAN 赋值
  • 布尔值只有TRUE, FALSE及 NULL 三个值。
c. 数据库赋值
  • 数据库赋值是通过 SELECT语句来完成的，每次执行 SELECT语句就赋值一次，一般要求被赋值的变量与SELECT中的列名要一一对应。如：DECLARE emp_idemp.empno%TYPE :=7788; -- 使用数据库列的类型 emp_name emp.ename%TYPE; wagesemp.sal%TYPE; BEGIN -- 使用 select 查询的字段 into 定义的变量 from 表 [where 标间] SELECT ename, NVL(sal,0) + NVL(comm,0) INTO emp_name, wages FROM emp WHERE empno = emp_id; -- 打印输出 DBMS_OUTPUT.PUT_LINE(emp_name||’----‘||to_char(wages)); END;
tips
提示：不能将SELECT语句中的列赋值给布尔变量。
d. 可转换的类型赋值
  • CHAR 转换为 NUMBER：
    使用 TO_NUMBER 函数来完成字符到数字的转换，如：
    ??v_total:= TO_NUMBER(‘100.0’) + sal; ??
  • NUMBER 转换为CHAR：
    使用 TO_CHAR函数可以实现数字到字符的转换，如：
    ??v_comm := TO_CHAR(‘123.45’) || ’元’ ; ??
  • 字符转换为日期：
    使用 TO_DATE函数可以实现字符到日期的转换，如：
    ??v_date := TO_DATE(2001.07.03,yyyy.mm.dd); ??
  • 日期转换为字符
    使用 TO_CHAR函数可以实现日期到字符的转换，如：
    ??v_to_day := TO_CHAR(SYSDATE, yyyy.mm.dd hh24:mi:ss) ; ??
2.7 变量作用范围及可见性
在PL/SQL编程中，如果在变量的定义上没有做到统一的话，可能会隐藏一些危险的错误，这样的原因主要是变量的作用范围所致。与其它高级语言类似，PL/SQL的变量作用范围特点是：
  • 变量的作用范围是在你所引用的程序单元（块、子程序、包）内。即从声明变量开始到该块的结束。
  • 一个变量（标识）只能在你所引用的块内是可见的。
  • 当一个变量超出了作用范围，PL/SQL引擎就释放用来存放该变量的空间（因为它可能不用了）。
  • 在子块中重新定义该变量后，它的作用仅在该块内。
2.8 注释
  • 在PL/SQL里，可以使用两种符号来写注释，即：
  2. 使用双 ‘-‘ ( 减号) 加注释
    PL/SQL允许用 – 来写注释，它的作用范围是只能在一行有效。如：
V_SalNUMBER(12,2); -- 工资变量。

  1. 使用 /**/来加一行或多行注释，如：
/***********************************************/
/* 文件名： department_salary.sql*/
/***********************************************/

tips
提示：被解释存放在数据库中的 PL/SQL 程序，一般系统自动将程序头部的注释去掉。只有在 PROCEDURE 之后的注释才被保留；另外程序中的空行也自动被去掉。
2.9 简单例子
a. 简单数据插入例子
/* 本例子仅是一个简单的插入，不是实际应用。*/
DECLARE
v_enameVARCHAR2(20) := ‘Bill’; --声明变量
v_salNUMBER(7,2) :=1234.56;
v_deptnoNUMBER(2) := 10;
v_empnoNUMBER(4) := 8888;
BEGIN
INSERT INTO emp ( empno, ename, JOB, sal, deptno , hiredate )
VALUES ( v_empno, v_ename, ‘Manager’, v_sal, v_deptno,
TO_DATE(’1954.06.09’,’yyyy.mm.dd’) );
COMMIT;
END;



b. 简单数据删除例子
/* 本例子仅是一个简单的删除例子，不是实际应用。*/
DECLARE
v_empnonumber(4) := 8888

    推荐阅读


    上一篇：36条极简人生建议

    下一篇：最近学习freemarker