Oracle11g基础学习---------（4）PLSQL语言基础

 学习Oracle从哪里入手呢，在这里，我将带你走进Oracle的大门，一天一章，7天入门不是梦

 

1.PL/SQL语言简介

PL/SQL是Oracle在标准SQL语言上的过程性扩展 允许嵌入SQL语句，定义变量和常量，允许过程语言结构（条件分支语句和循环语句），允许使用异常来处理Oracle错误 PL/SQL可以用于创建存储过程、触发器和程序包等，也可以用于处理业务 规则、数据库事件或给SQL命令的执行添加程序逻辑 1.2 PL/SQL优点提高应用程序的运行性能提供模块化的程序设计功能允许定义标识符具有过程语言控制结构具有良好的兼容性处理运行错误1.3 PL/SQL块所有的PL/SQL程序都以块作为基本单位块中包含过程化语句和SQL的DML语句。这些块可以按顺序出现，也可以相互嵌套（一个块在另一个块的内部）块的分类匿名块匿名块是出现在应用程序中的没有名字且不存储到数据库中的块匿名块出现在SQL语句可以出现的地方，它们可以调用其他程序，却不能被其他程序调用命名块命名块是一种带有标签的匿名块，标签为块指定了一个名称子程序子程序是存储在数据库中的过程（procedure）、函数（function），生成之后可以被多次执行程序包程序包是存储在数据库中的一组子程序、变量定义程序包中的子程序可以被其他程序包或者子程序调用触发器触发器是一种存储在数据库中的命名块，生成之后可以被多次执行在相应的触发器事件发生之前或之后就会被执行一次或多次2. 程序结构组成部分PL/SQL是Oracle在标准SQL语言上的过程性扩展 允许嵌入SQL语句，定义变量和常量，允许过程语言结构（条件分支语句和循环语句），允许使用异常来处理Oracle错误 PL/SQL可以用于创建存储过程、触发器和程序包等，也可以用于处理业务 规则、数据库事件或给SQL命令的执行添加程序逻辑 2.2 PL/SQL注释单行注释单行注释可以在一行的任何地方由两个短横线（--）开始，并且直到该行的最后多行注释多行注释由“/*”开始、“*/”结束，可以跨越多行，但不允许嵌套2.3 常量与变量在声明部分中可以声明需要使用的常量、变量、函数、游标、异常处理名称 声明identifier_name [CONSTANT] data_type [NOT NULL][:= value_expression] | [DEFAULT value_expression]参数 说明identifier_name 指定需要声明的常量、变量的名称 data_type 指定数据类型 := 赋值运算符DEFAULT 给变量赋予默认值value_expression 赋值表达式CONSTANT 表明声明的是一个常量 NOT NULL 表明声明的变量不能为空除了可以使用常量来给变量赋值之外，还可通过SELECT INTO语句将从数据库表中查询的结果赋予变量 2.4 数据类型CHAR CHAR表示固定长度字符串，长度不够的使用空格来补充，最多可以存储2000字节 VARCHAR2 VARCHAR2表示可变长度字符串，最多可以存储4000字节 NUMBER NUMBER类型可以存储正数、负数、零、定点数和精度为38位的浮点数 DATE类型DATE数据类型用于存储表中的日期和时间数据 DATE类型的长度是7，7个字节分别表示世纪、年、月、日、时、分和秒 TIMESTAMP类型 TIMESTAMP数据类型用于存储日期的年、月、日、小时、分和秒值。其中，秒值精确到小数点6位，该数据类型同时包含时区信息 CLOB大字符串对象类型CLOB数据类型用于存储可变长度的字符数据，最多可存储4GB 该数据类型用于存储VARCHAR2类型不能存储的长文本信息 BLOB大二进制类型 BLOB数据类型用于存储较大的二进制对象，如图形、视频剪辑和声音剪辑等，该类型最多可以存储4GB数据%TYPE数据类型当定义PL/SQL变量存放值时，必须确保变量使用合适的数据类型和长度，否则可能会在运行过程中出现PL/SQL运行错误 为了避免这种不必要的错误，可以使用%TYPE属性来定义变量 当使用%TYPE属性定义变量时，Oracle会自动地按照数据库列或其他变量来确定新变量的类型和长度%ROWTYPE数据类型如果一张表中包含较多的列，则可以使用%ROWTYPE来定义一个表示表中一行记录的变量 为了使一个变量的数据类型与一张表中记录的各个列的数据类型对应一致，Oracle提供了%ROWTYPE定义方式RECORD数据类型PL/SQL记录类似于高级语言中的结构，每个PL/SQL记录一般都包含多个成员 当使用PL/SQL记录时，首先需要在定义部分定义记录类型和记录变量，然后在执行部分引用该记录变量 当引用记录成员时，必须将记录变量作为前缀，格式为：记录变量.记录成员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]);TABLE数据类型TABLE(索引表)相当于一个键值集合，键是唯一的，用于查找对应的值。键可以是整数或字符串TYPE table_name IS TABLE OF element_type [NOT NULL]INDEX BY [BINARY_INTEGER | PLS_INTEGER | VARCHAR2];VARRAY数据类型VARRAY(数组)是具有相同数据类型的一组成员的集合。每个成员都有一个唯一的下标，它取决于成员在数组中的位置 语法TYPE varray_name IS VARRAY(size) OF element_type [NOT NULL]; varray_name是VARRAY数据类型的名称 size指定可以容纳的成员的最大数量，每个成员的数据类型是element_type对于VARRAY数据类型而言，首先的3个步骤是定义、声明、初始化。这3个步骤缺一不可，否则就会在后面的使用过程中出错。在引用VARRAY数组中的成员时，需要在一对括号中使用顺序下标，下标从1开始3. 流程控制IF 条件表达式 THEN 语句段END IF;IF 条件表达式 THEN 语句段1ELSE 语句段2END IF;IF 条件表达式1 THEN语句段1ELSIF 条件表达式2 THEN语句段2ELSIF 条件表达式3 THEN语句段3......ELSIF 条件表达式n语句段nEND IF;3.1 条件控制CASE 表达式WHEN 条件表达式结果1 THEN语句段1;WHEN 条件表达式结果2 THEN语句段2;......ELSE语句段n;END CASE;CASEWHEN 条件表达式结果1 THEN语句段1;WHEN 条件表达式结果2 THEN语句段2;......ELSE语句段n;END CASE;3.2 循环控制基本循环：LOOP语句段;EXIT [WHEN 条件表达式]END LOOP;EXIT语句必须放在循环体内，且只能退出循环体，不能退出PL/SQL块。当需要退出PL/SQL块时，应该使用RETURN语句WHILE循环WHILE 条件表达式 LOOP语句段;END LOOP;FOR循环 当使用基本循环或WHILE循环时，需要定义循环控制变量 当使用FOR循环时，Oracle会隐含定义循环变量FOR 循环变量 in [REVERSE] 初值表达式..终值表达式 LOOP 语句段;END LOOP;3.3 顺序控制GOTO语句GOTO语句用于跳转到特定标号处去执行语句使用GOTO语句会增加程序的复杂性，并使应用程序可读性变差，所以在开发应用时建议不要使用GOTO语句NULL语句NULL语句不会执行任何操作，并直接将控制传递到下一个条语句使用NULL语句的优点在于可以提高PL/SQL程序的可读性4. 异常处理PL/SQL异常是指通过了编译的PL/SQL程序在运行时产生的错误导致异常的原因有很多，如内存用尽、硬件故障、违反表的完整性约束、设计上的缺陷PL/SQL提供了一种异常错误处理机制，可以帮助实现对错误的捕获和处理当异常发生时，PL/SQL会自动地捕获异常并自动地将程序控制流程转移到异常处理部分的程序EXCEPTIONWHEN 异常错误名称1 [OR 异常错误名称2......] THEN语句段1;WHEN异常错误名称3 [OR 异常错误名称4......] THEN语句段2;......WHEN OTHERS THEN语句段3;4.1 预定义异常针对一些常见的错误，PL/SQL预定义了一些异常错误 当PL/SQL程序违反了Oracle的规定或超出了系统规定的限制时，就会隐含地引发一个预定义的错误 PL/SQL在程序包STANDARD中包含了这些预定义异常异常 ORACLE错误 描述 LOGIN_DENIED ORA-1017 无效的用户名或者口令 NO_DATA_FOUND ORA-1403 查询未找到数据 NOT_LOGGED_ON ORA-1012 还未连接就试图数据库操作 PROGRAM_ERROR ORA-6501 内部错误 ROWTYPE_MISMATCH ORA-6504 主变量和光标的类型不兼容 STORAGE_ERROR ORA-6500 内部错误 ACCESS_INTO_NULL ORA-6530 试图访问一个未初始化的对象时出现CASE_NOT_FOUND ORA-6592 CASE语句中的选项与用户输入数据不匹配时出现CURSOR_ALREADY_OPEN ORA-6511 试图打开一个已打开的光标DUP_VAL_ON_INDEX ORA-0001 试图破坏一个唯一性限制INVALID_CURSOR ORA-1001 试图使用一个无效的光标INVALID_NUMBER ORA-1722 试图对非数字值进行数字操作TIMEOUT_ON_RESOURCE ORA-0051 发生超时TOO_MANY_ROWS ORA-1422 SELECT INTO命令返回的多行TRANSACTION_BACKED_OUT ORA-006 由于死锁提交被退回VALUE_ERROR ORA-6502 转换或者裁剪错误ZERO_DIVIDE ORA-1476 试图被零除4.2 自定义异常预定义异常都是由Oracle判断的异常错误在实际的PL/SQL程序开发过程中，为了具体的业务规则、编程和调试的需要，程序员可以自定义一些异常自定义异常必须要声明，并且必须使用RAISE语句显式地引发在更新表中的数据时，如果没有符合条件的记录，则不会更新数据。因为这不是错误，所以不会有任何的错误提示，但可以使用自定义错误异常的方法来提示5. 开发动态SQL在PL/SQL程序设计中，可以直接使用DML和事务控制的语句，但DDL语句及系统控制语句却不能在PL/SQL中直接使用需要在PL/SQL中使用DDL语句及系统控制语句时，可以通过使用动态SQL来实现，例如需要动态建表或某个不确定的操作需要动态执行在PL/SQL块中使用的SQL语句在编译时是明确的，且执行的是确定对象，则属于静态SQL在PL/SQL块编译时SQL语句是不确定的，如根据用户输入的参数的不同而执行不同的操作，则属于动态SQL编译程序对动态语句部分不进行处理，只是在程序运行时动态地创建语句，对语句进行语法分析并执行该语句5.2 处理非查询语句EXECUTE IMMEDIATE dynamic_string [INTO {define_variable[,define_variable]...| record}][USING [IN |OFF |IN OUT] bind_argument [,[ IN | OUT |IN OUT] bind_argument] ...][{RETURNING | RETURN } INTO bind_argument [, bind_argument] ...];参数 说明dynamic_string 指定存放SQL语句或PL/SQL块的字符串变量 define_variable 指定存放单行查询结果的变量 bind_argument(IN) 指定存放被传递给动态SQL值的变量 bind_argument(OUT) 指定存放动态SQL返回值的变量使用EXECUTE IMMEDIATE处理DDL和DCL操作使用EXECUTE IMMEDIATE处理DML操作处理包含RETURNING子句的DML语句当使用EXECUTE IMMEDIATE处理包含RETURNING子句的DML语句时，必须使用RETURNING INTO子句接收返回的数据当直接使用EXECUTE IMMEDIATE处理包含RETURNING子句的DML语句时，只能处理作用在单行上的DML语句使用EXECUTE IMMEDIATE处理单行查询使用EXECUTE IMMEDIATE不仅可以处理DDL、DCL和DML语句，而且还可以用于处理单行查询语句但在使用EXECUTE IMMEDIATE语句处理单行查询语句时，需要使用INTO子句接收返回的数据使用BULK子句处理多行查询通过在EXECUTE IMMEDIATE语句中引入BULK子句，不仅可以处理单行查询，而且还可以处理多行查询语句总结PL/SQL是Oracle在标准SQL语言上的过程性扩展，它不仅允许嵌入SQL语句，而且允许定义变量和常量，允许过程语言结构PL/SQL程序块可以分为匿名块、命名块、子程序、程序包和触发器PL/SQL程序块由3个部分组成：声明部分、执行部分和异常处理部分在PL/SQL程序块中，可以使用SELECT INTO语句将查询的结果赋给变量当使用%TYPE属性定义变量时，Oracle会自动地按照数据库列或其他变量来确定新变量的类型和长度可以使用%ROWTYPE来定义一个表示表中一行记录的变量TABLE索引表相当于一个键值集合，键是唯一的，用于查找对应的值。键可以是整数或字符串VARRAY数组是具有相同数据类型的一组成员的集合。每个成员都有一个唯一的下标，它取决于成员在数组中的位置自定义异常必须要声明，并且必须使用RAISE语句显式地引发需要在PL/SQL中使用DDL语句及系统控制语句时，可以通过使用动态SQL来实现