day13-oracle总结_数据库列和作为变量-优快云博客

Oracle

PL/SQL程序设计

PL/SQL简介

到目前为止，在数据库上一直使用单一的SQL语句进行数据操作，没有流程控制，无法开发复杂的应用。

OraclePL/SQL语言（ProceduralLanguage/SQL）是结合了结构化查询与Oracle自身过程控制为一体的强大语言，PL/SQL不但支持更多的数据类型，拥有自身的变量声明、赋值语句，而且还有条件、循环等流程控制语句。过程控制结构与SQL数据处理能力无缝的结合形成了强大的编程语言，可以创建过程和函数以及程序包。

PL/SQL是一种块结构的语言，它将一组语句放在一个块中，一次性发送给服务器，PL/SQL引擎分析收到PL/SQL语句块中的内容，把其中的过程控制语句由PL/SQL引擎自身去执行，把PL/SQL块中的SQL语句交给服务器的SQL语句执行器执行。如图所示：

PL/SQL体系结构

PL/SQL块发送给服务器后，先被编译然后执行，对于有名称的PL/SQL块（如子程序）可以单独编译，永久的存储在数据库中，随时准备执行。

PL/SQL的优点：

Ø 支持SQL

SQL是访问数据库的标准语言，通过SQL命令，用户可以操纵数据库中的数据。PL/SQL支持所有的SQL数据操纵命令、游标控制命令、事务控制命令、SQL函数、运算符和伪列。同时PL/SQL和SQL语言紧密集成，PL/SQL支持所有的SQL数据类型和NULL值。

Ø 支持面向对象编程

PL/SQL支持面向对象的编程，在PL/SQL中可以创建类型，可以对类型进行继承，可以在子程序中重载方法等。

Ø 更好的性能

SQL是非过程语言，只能一条一条执行，而PL/SQL把一个PL/SQL块统一进行编译后执行，同时还可以把编译好的PL/SQL块存储起来，以备重用，减少了应用程序和服务器之间的通信时间，PL/SQL是快速而高效的。

Ø 可移植性

使用PL/SQL编写的应用程序，可以移植到任何操作系统平台上的Oracle服务器，同时还可以编写可移植程序库，在不同环境中重用。

Ø 安全性

可以通过存储过程对客户机和服务器之间的应用程序逻辑进行分隔，这样可以限制对Oracle数据库的访问，数据库还可以授权和撤销其他用户访问的能力。

PL/SQL可用的SQL语句

PL/SQL是ORACLE系统的核心语言，现在ORACLE的许多部件都是由PL/SQL写成。在PL/SQL中可以使用的SQL语句有：

INSERT，UPDATE，DELETE，SELECT INTO，COMMIT，ROLLBACK，SAVEPOINT。

PL/SQL语法基础

PL/SQL块

PL/SQL是一种块结构的语言，一个PL/SQL程序包含了一个或者多个逻辑块，逻辑块中可以声明变量，变量在使用之前必须先声明。除了正常的执行程序外，PL/SQL还提供了专门的异常处理部分进行异常处理。每个逻辑块分为三个部分，语法是：

语法结构：PL/SQL块的语法

[DECLARE

--declaration statements] 1）声明部分

BEGIN

--executable statements 2）执行部分

[EXCEPTION

--exception statements] 3）异常处理部分

END;

语法解析：

1）声明部分：声明部分包含了变量和常量的定义。这个部分由关键字DECLARE开始，如果不声明变量或者常量，可以省略这部分。

2）执行部分：执行部分是 PL/SQL块的指令部分，由关键字BEGIN开始，关键字END结尾。所有的可执行PL/SQL语句都放在这一部分，该部分执行命令并操作变量。其他的PL/SQL块可以作为子块嵌套在该部分。PL/SQL块的执行部分是必选的。注意END关键字后面用分号结尾。

3）异常处理部分：该部分是可选的，该部分用EXCEPTION关键字把可执行部分分成两个小部分，之前的程序是正常运行的程序，一旦出现异常就跳转到异常部分执行。

Ø plsql说明：

1）PL/SQL是一种编程语言，与Java和C#一样，除了有自身独有的数据类型、变量声明和赋值以及流程控制语句外，PL/SQL还有自身的语言特性；

2）PL/SQL对大小写不敏感

3）PL/SQL块中的每一条语句都必须以分号结束，SQL语句可以是多行的，但分号表示该语句结束

PL/SQL中的特殊符号说明：

类型	符号	说明
赋值运算符	:=	Java和C#中都是等号，PL/SQL的赋值是：=
特殊字符	\|\|	字符串连接操作符。
	--	PL/SQL中的单行注释。
	*/,/*	PL/SQL中的多行注释，多行注释不能嵌套。
	<<,>>	标签分隔符。只为了标识程序特殊位置。
	..	范围操作符，比如：1..5 标识从１到５
算术运算符	*+，-，，/**	基本算术运算符。
算术运算符	**	求幂操作，比如：32=9**
关系运算符	>，<,>=,<=,=	基本关系运算符，=表示相等关系，不是赋值。
关系运算符	<>,!=	不等关系。
逻辑运算符	AND,OR,NOT	逻辑运算符。

PL/SQL块分类

1. 无名块

动态构造，只能执行一次。

2. 子程序

存储在数据库中的存储过程、函数及包等。当在数据库上建立好后可以在其它程序中调用它们。

3. 触发器

当数据库发生操作时，会触发一些事件，从而自动执行相应的程序。

声明

变量声明

PL/SQL支持SQL中的数据类型，PL/SQL中正常支持NUMBER,VARCHAR2,DATE等OracleSQL数据类型。声明变量必须指明变量的数据类型，也可以声明变量时对变量初始化，变量声明必须在声明部分。声明变量的语法是：

语法格式：

变量名数据类型[ :=初始值]

语法解析：数据类型如果需要长度，可以用括号指明长度，比如：varchar2(20)。

变量的命名规范：

（1）变量名首字母必须是英文字母，其后可以是字母、数字或者特殊字符$、#和_

（2）变量名字的长度不能超过30

（3）变量名中不能有空格

标识符号和命名规范：

（1）当定义变量时，建议使用v_作为前缀，如v_sal；

（2）当定义常量时，建议使用c_作为前缀，如c_rate；

（3）当定义游标时，建议使用_cursor作为后缀，如emp_cursor；

（4）当定义异常时，建议使用e_作为前缀，例如e_error；

声明变量并赋值

DECLARE

sname VARCHAR2(20) :='jerry'; ①

[sname varchar2(20) default 'jerry';]

BEGIN

sname:=sname||' and tom'; ②

dbms_output.put_line(sname); ③

END;

说明：

① 声明一个变量sname，初始化值是“jerry”。字符串用单引号，如果字符串中出现单引号可以使用两个单引号（’’）来表示，即单引号同时也具有转义的作用。初始化赋值可以’:=’，也可以使用default关键词。

② 对变量sname重新赋值，赋值运算符是“:=”。

③ dbms_output.put_line是输出语句，可以把一个变量的值输出，在SQL*Plus中输出数据时，如果没有结果显示，可以使用命令：set serveroutput on设置显示输出到SQL*Plus控制台上的内容。

从数据库中查询出结果赋给变量

对变量赋值还可以使用SELECT…INTO 语句从数据库中查询数据对变量进行赋值。但是查询的结果只能是一行记录，不能是零行或者多行记录。

DECLARE

sname VARCHAR2(20) DEFAULT'jerry'; --（1）

BEGIN

SELECT ename INTO sname FROM emp WHEREempno=7934; --（2）

dbms_output.put_line(sname);

END;

说明：

（1）变量初始化时，可以使用DEFAULT关键字对变量进行初始化。

（2）使用select…into语句对变量sname赋值，要求查询的结果必须是一行，不能是多行或者没有记录。

从控制台输入变量值

在SQL*Plus也可以像Java一样从控制台输入变量的值。

格式：&变量名

示例：

declare --定义变量

v_enamevarchar2(10);

v_empnonumber;

begin--执行部分

v_empno := &no;

select ename into v_ename from emp whereempno=v_empno;

dbms_output.put_line('用户名'||v_ename);

exception

when no_data_found then

dbms_output.put_line('未发现该编号');

end;

说明：

（1）使用&给empno赋值。

（2）no_data_found ：PL/SQL的预定义异常。

声明常量

常量在声明时赋予初值，并且在运行时不允许重新赋值。使用CONSTANT关键字声明常量，常量初值可以使用赋值运算符（:=）赋值，也可以使用DEFAULT关键字赋值。

例子：计算圆的面积。

DECLARE

pi CONSTANT number :=3.14; --圆周率长值

r number DEFAULT 3; --圆的半径默认值3

area number; --面积。

BEGIN

area:=pi*r*r; --计算面积

dbms_output.put_line(area); --输出圆的面积

END;

PL/SQL数据类型

标量数据类型

标量数据类型的变量只有一个值，且内部没有分量。标量数据类型包括数字型，字符型，日期型和布尔型。这些类型有的是Oracle SQL中定义的数据类型，有的是PL/SQL自身附加的数据类型。字符型和数字型又有子类型，子类型只与限定的范围有关，比如NUMBER类型可以表示整数，也可以表示小数，而其子类型POSITIVE只表示正整数。

类型	说明
VARCHAR2(长度)	可变长度字符串，Oracle SQL定义的数据类型，在PL/SQL中使用时最常32767字节。在PL/SQL中使用没有默认长度，因此必须指定。
NUMBER(精度，小数)	Oracle SQL定义的数据类型，见第二章。
DATE	Oracle SQL定义的日期类型，见第二章。
TIMESTAMP	Oracle SQL定义的日期类型，见第二章。
CHAR(长度)	Oracle SQL定义的日期类型，固定长度字符，最长32767字节，默认长度是1，如果内容不够用空格代替。
LONG	Oracle SQL定义的数据类型，变长字符串基本类型，最长32760字节。在Oracle SQL中最长2147483647字节。
BOOLEAN	PL/SQL附加的数据类型，逻辑值为TRUE、FALSE、NULL
BINARY_INTEGER	PL/SQL附加的数据类型，介于-2³¹和2³¹之间的整数。
PLS_INTEGER	PL/SQL附加的数据类型，介于-2³¹和2³¹之间的整数。类似于BINARY_INTEGER，只是PLS_INTEGER值上的运行速度更快。
NATURAL	PL/SQL附加的数据类型，BINARY_INTEGER子类型，表示从0开始的自然数。
NATURALN	与NATURAL一样，只是要求NATURALN类型变量值不能为NULL。
POSITIVE	PL/SQL附加的数据类型，BINARY_INTEGER子类型，正整数。
POSITIVEN	与POSITIVE一样，只是要求POSITIVE的变量值不能为NULL。
REAL	Oracle SQL定义的数据类型，18位精度的浮点数
INT,INTEGER,SMALLINT	Oracle SQL定义的数据类型，NUMBERDE的子类型，38位精度整数。
SIGNTYPE	PL/SQL附加的数据类型，BINARY_INTEGER子类型。值有：1、-1、0。
STRING	与VARCHAR2相同。

属性数据类型

当声明一个变量的值是数据库中的一行或者是数据库中某列时，可以直接使用属性类型来声明。Oracle中存在两种属性类型：%TYPE和%ROWTYPE。

Ø % ROWTYPE

引用数据库表中的一行作为数据类型，即RECORD类型（记录类型），是PL/SQL附加的数据类型。表示一条记录，就相当于Java中的一个对象。可以使用“.”来访问记录中的属性。

代码演示：

DECLARE

myemp EMP%ROWTYPE; ①

BEGIN

SELECT * INTO myemp FROM emp WHERE empno=7934; ②

dbms_output.put_line(myemp.ename); ③

END;

代码解析：

① 声明一个myemp对象，该对象表示EMP表中的一行。

②从EMP表中查询一条记录放入myemp对象中。

③访问该对象的属性可以使用“.”。

Ø %TYPE

引用某个变量或者数据库的列的类型作为某变量的数据类型。

代码演示：%TYPE应用

DECLARE

v_sal emp.sal%TYPE; ①

v_mysal number(4):=3000;

v_totalsal mysal%TYPE; ②

BEGIN

SELECT SAL INTO v_sal FROM emp WHERE empno=7934;

v_totalsal:=v_sal+v_mysal;

dbms_output.put_line(v_totalsal);

END;

代码解析：

①定义变量v_sal为emp表中sal列的类型。

②定义v_totalsal是变量v_mysal的类型。

%TYPE可以引用表中的某列作的类型为变量的数据类型，也可以引用某变量的类型作为新变量的数据类型。

异常处理概念

异常情况处理(EXCEPTION)是用来处理正常执行过程中未预料的事件,程序块的异常处理预定义的错误和自定义错误,由于PL/SQL程序块一旦产生异常而没有指出如何处理时,程序就会自动终止整个程序运行.

有三种类型的异常错误：

1．预定义 ( Predefined )错误

ORACLE预定义的异常情况大约有24个。对这种异常情况的处理，无需在程序中定义，由ORACLE自动将其引发。

2．非预定义 ( Predefined )错误

即其他标准的ORACLE错误。对这种异常情况的处理，需要用户在程序中定义，然后由ORACLE自动将其引发。

3．用户定义(User_define) 错误

程序执行过程中，出现编程人员认为的非正常情况。对这种异常情况的处理，需要用户在程序中定义，然后显式地在程序中将其引发。

异常处理部分一般放在 PL/SQL 程序体的后半部,结构为:

EXCEPTION

WHEN first_exception THEN <code to handle first exception >

WHEN second_exception THEN <code to handle second exception >

WHEN OTHERS THEN <codeto handle others exception >

END;

异常处理可以按任意次序排列,但 OTHERS 必须放在最后。

常见预定义异常

错误号	异常错误信息名称	说明
ORA-0001	Dup_val_on_index	试图破坏一个唯一性限制
ORA-0051	Timeout-on-resource	在等待资源时发生超时
ORA-0061	Transaction-backed-out	由于发生死锁事务被撤消
ORA-1001	Invalid-CURSOR	试图使用一个无效的游标
ORA-1012	Not-logged-on	没有连接到ORACLE
ORA-1017	Login-denied	无效的用户名/口令
ORA-1403	No_data_found	SELECT INTO没有找到数据
ORA-1422	Too_many_rows	SELECT INTO 返回多行
ORA-1476	Zero-divide	试图被零除
ORA-1722	Invalid-NUMBER	转换一个数字失败
ORA-6500	Storage-error	内存不够引发的内部错误
ORA-6501	Program-error	内部错误
ORA-6502	Value-error	转换或截断错误
ORA-6504	Rowtype-mismatch	缩主游标变量与 PL/SQL变量有不兼容行类型
ORA-6511	CURSOR-already-OPEN	试图打开一个已存在的游标
ORA-6530	Access-INTO-null	试图为null 对象的属性赋值
ORA-6531	Collection-is-null	试图将Exists 以外的集合( collection)方法应用于一个null pl/sql 表上或varray上
ORA-6532	Subscript-outside-limit	对嵌套或varray索引得引用超出声明范围以外
ORA-6533	Subscript-beyond-count	对嵌套或varray 索引得引用大于集合中元素的个数.