Oracle---PL/SQL 基础知识

什么是PL/SQL

•PL/SQL是由Oracle开发，专用于Oracle的一种程序设计语言

•PL代表 ProceduralLanguage

•SQL代表StructuredQuery Language

•PL/SQL是Oracle数据库对SQL语句的扩展，增加了编程语言的特点

•PL/SQL 包含过程化语句和SQL语句

•数据操作和查询语句被包含在PL/SQL代码的程序单元中（PL/SQL块）,经过逻辑判断、循环等操作完成复杂的功能或者计算.

PL/SQL 块

•PL/SQL是块结构化语言(程序能被分成若干个逻辑块)

•PL/SQL块的组成

–PL/SQL语言以块为单位，块中可以嵌套子块。

–一个基本的PL/SQL块由3部分组成：

  定义部分（DECLARE）

  可执行部分（BEGIN）

  异常处理部分（EXCEPTION）

PL/SQL块的定义部分

  与其它语言一样，PL/SQL中使用的变量、常量、游标和异常处理的名字都必须先定义后使用。并且必须定义在以DECLARE关键字开头的定义部分。

PL/SQL块的可执行部分

  该部分是PL/SQL块的主体，包含该块的可执行语句。该部分定义了块的功能，是必须的。由关键字BEGIN开始，以END结束。

PL/SQL块的异常处理部分

  该部分包含块的异常处理程序（错误处理程序）。当该块程序体中的某个语句出现异常（检测到一个错误）时，oracle将程序控制转到异常部分的相应的异常处理程序中进行进一步的处理。该部分由关键字EXCEPTION开始，END关键字结束。

•DECLARE– 可选

–变量、常量、游标、用户自定义的特殊类型

•BEGIN –必须

–SQL 语句

–PL/SQL 语句

•EXCEPTION– 可选

–错误发生时的处理动作

•END; – 必须

DECLARE v_variable VARCHAR2(5); BEGIN SELECT column_name INTO v_variable FROM table_name; EXCEPTION WHEN exception_name THEN ... END;

PL/SQL的优点

•改善了性能

PL/SQL以整个语句块发送给服务器，这个过程在单次调用

中完成，降低了网络拥挤。而如果不使用PL/SQL，每条

SQL语句都有单独的传输交互，在网络环境下占用大量的

服务器时间，同时导致网络拥挤。

•可重用性

PL/SQL能运行在任何ORACLE环境中（不论它的操作系统

和平台），在其他ORACLE能够运行的操作系统上无需修改

代码。

•模块化

  每个PL/SQL单元可以包含一个或多个程序块，程序中的每一块都实现一个逻辑操作，从而把不同的任务进行分割，由不同的块来实现，块之间可以是独立的或是嵌套的。

PL/SQL块的类型

–匿名块：一般在要运行的应用中说明，运行时传递给PL/SQL引擎处理，只能执行一次，不能被存储在数据库中。

–过程，函数和包（Procedure，Function& Package）：是命名的PL/SQL块，被存储在数据库中，能够被多次执行，可以用外部程序来显示执行。

–触发器（Trigger）：是命名的PL/SQL块，被存储在数据库中，能够被多次执行，当相应的触发事件发生时自动被执行。

PL/SQL中变量

•PL/SQL中可使用标识符来声明变量、常量、游标、用户定义的异常等，并在SQL语句或过程化的语句中使用。

•标识符的命名和Oracle对数据库对象的命名原则相同。

–至多有30个字符

–不能是保留字

–必须以字母开头

–不允许与数据库中表的列名相同

–包含字母、$、_、数字符号

•对标识符的命名最好遵循相关命名规范

PL/SQL中变量

标量数据类型:

•容纳单个值

•内部没有分量

•分为四个类别：

–数字型

  number、binary_integer等

–字符型

  char、varchar2、long、RAW等

–日期型

  date、timestamp等

–布尔型

  true、false、null

VARCHAR2(最大长度）:变长字符的基本类型，最大的存储数据是32767字节。VARCHAR2型的变量和常量没有默认的长度。

CHAR[(最大长度)]:  固定长度字符数据的基本类型，最大有32767字节。如果没有指定的最大值，缺省值为1。

LONG  变长字符串的基本类型，最大长度是32k字节。

LONGRAW 变长字符串，最多可以存储32k字节的二进制数据。

NCHAR  使用由国家字符集指定的字符集

BINARY_INTEGER:介于-2147483647和2147483647之间整数的基本类型。

PLS_INTEGER:介于-2147483647和2147483647之间有符号的整数的基本类型。与NUMBER 和 BINARY_INTEGER相比，PLS_INTEGER占有较少的存储空间且运行效率高。

RAW存储定长的二进制数据，最大长度存储32k字节

LONG型和 VARCHAR2非常相似，不过LONG型的最大长度是32760字节，比VARCHAR2少了7个字节。

LONGRAW:二进制数据和字节字符串的基本类型，最大字节为32760。PL/SQL不能解释 LONGRAW 型的数据。

声明 PL/SQL变量

举例

Declare v_hiredate DATE; v_deptno NUMBER(2) NOT NULL := 10; v_location VARCHAR2(13) := ‘Shenyang'; c_tax_rate CONSTANT NUMBER(3,2) := 8.25; v_valid BOOLEAN NOT NULL := TRUE;

•变量的命名规则与SQL的规则基本相同，即每个标识符必须以字母开头，而且不分大小写。

•使用NOT NULL约束条件定义变量时，必须为变量赋予一个值

•在每行上声明一个变量，使代码更易于阅读和维护

•在常量声明中，关键字CONSTANT必须位于类型指定符之前，必须被初始化

•使用赋值运算符(:=)或DEFAULT保留字将变量初始化

•在PL/SQL中使用的变量、常量、游标和异常处理的名字都必须先声明后使用。

•声明部分是包括在关键字DECLARE和BEGIN之间的部分，每条语句之后用‘；’结束。

–不同块的变量可以同名

–数据库中列名应与变量名有截然不同的名字

DECLARE v_job emp.job%TYPE; empno emp.empno%TYPE:=7369; BEGIN SELECT job INTO v_job FROM emp WHERE empno = empno; END;
变量的赋值

语法

identifier := expr;

另外一种为变量赋值的方式是从数据库中选取值赋给变量。语法：

SELECT  column INTO  variable

FROM  table 

WHERE  condition;

SELECT 语句执行后，将出现下列情况之一：

• 只检索了一行

• 检索了多行

• 不检索任何行

只有当检索一行时，SELECT才成功操作。

%TYPE 的属性

•通过%TYPE属性声明一个变量，实际上就是参照变量或者表中字段的类型作为变量的类型，并且保持同步。变量将遵循下面的类型声明：

–已经声明过的变量类型 

–数据库中表的字段类型

•可以作为%TYPE前缀的可以是

–数据库表和列

–前面声明的变量名称

•PL/SQL在运行程序时确定变量的数据类型和大小

... v_ename emp.ename%TYPE; v_balance NUMBER(7,2); v_min_balance v_balance%TYPE := 10; ...

使用%TYPE 属性的好处：

–在编程时，可以不去查询数据库中字段的数据类型

–数据库中字段的数据类型可能被改变

–为了和前面的变量类型始终保持一致

•要在PL/SQL中引用绑定变量，需在该变量名前加上一个冒号 (:)。

•举例

VARIABLE g_salary NUMBER DECLARE v_sal emp.sal%TYPE; BEGIN SELECT sal INTO v_sal FROM emp WHERE empno = 7369; :g_salary := v_sal; END; /
DBMS_OUTPUT.PUT_LINE

•使用setserveroutput on 命令设置环境变量serveroutput为打开状态，从而使得PL/SQL程序能够在SQL*plus中输出结果。

DECLARE v_var varchar2(10); BEGIN SELECT ename into v_var FROM emp WHERE empno=7369; DBMS_OUTPUT.PUT_LINE('雇员名='||v_var); END;<span style="font-size: 36pt;"> </span>

•注释可以是

–/* 和*/之间的多行注释

–单行注释，以 －－ 开始

PL/SQL中的SQL语句

•使用SELECT命令从数据库中选取数据

•使用DML命令更新数据库中行

•使用COMMIT,ROLLBACK或SAVEPOINT 命令控制事务。

•使用隐式游标属性确定DML的输出

•关键字END标记着PL/SQL块的结束，不代表事务的结束，一个块可以跨越多个事务，一个事务能够跨越多个块

•PL/SQL不直接支持DDL

SELECT:

SELECT select_list INTO {variable_name[, variable_name]... | record_name} FROM table WHERE condition;

•必须使用INTO子句

•查询必须并且只能返回一行

INSERT

<span style="font-size:16px;">BEGIN INSERT INTO emp (empno,ename,job,mgr,hiredate,sal) VALUES( 1000,'ljs','manager',1100,sysdate,4000); END; /</span><span style="font-size: 36pt;"> </span>

UPDATE:

DECLARE v_sal emp.sal%type := 800; BEGIN UPDATE emp SET sal = sal + v_sal WHERE job = 'ANALYST'; END; /
DELETE:

DECLARE v_deptno emp.deptno%type := 10; BEGIN DELETE FROM emp WHERE deptno = v_deptno; END; /