SQL与PL_SQL讲座3

单击此处编辑母版标题样式,*,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,第七章PL/SQL基础,1,一、概述,1、PL/SQL,PL/SQL是带有编程语言特性的SQL扩展,数据操纵及查询语句被包含在代码的过程单元中,2,2、PL/SQL的优点,模块化程序的开发,用过程化语言的控制结构编程,处理错误,可移植性,提高性能,3,3、PL/SQL块结构,DECLARE,变量、常量、游标、自定义异常,BEGIN,SQL语句,PL/SQL控制语句,EXCEPTION,错误发生时执行的动作,END；,4,4、程序类型,匿名块,应用程序触发器,数据库触发器,存储过程/函数,应用程序过程/函数,包过程,5,5、PL/SQL块的执行,PL/SQL块在PL/SQL引擎中执行：,SQL语句使用SQL语句执行器执行,非SQL语句使用过程语句执行器执行,6,二、子程序编程,1、PL/SQL程序单元,命名的PL/SQL块,主要有三类：,过程执行动作,函数计算值,包将逻辑上相关的过程和函数捆绑在一起,存储在数据库中，或作为应用程序的子程序,7,2、子程序组件,HEADER,子程序名、类型、参数,DECLARE,局部标识符,BEGIN,SQL语句,PL/SQL控制语句,EXCEPTION,错误发生时执行的动作,END；,8,3、PL/SQL块语法规则,语句可占多行,词汇单元可用空格分开,标识符：,可最多30个字符,不能使用保留字，除非将其括在双撇号中,必须以字母开头,不应与数据库表的列同名,命名规则,注释可为：,/*和*/之间的多行注释,-开头的单行注释,9,4、创建过程,语法：,PROCEDURE name(parameter,.),IS,pl/sql_block;,其中，参数语法为：,parameter_name IN|OUT|IN OUT datatype:=|DEFAULT expr,不能指定数据类型的约束,10,在SQL*Plus中创建过程时，使用CREATE OR REPLACE子句,用IS开始一个PL/SQL块,在IS和BEGIN之间输入局部变量声明,11,5、创建函数,创建PL/SQL函数可给调用环境返回一个值,在函数声明中加RETURN子句,在PL/SQL块中至少包含一个RETURN语句,12,语法：,FUNCTION name(parameter,.),RETURN datatype,IS,pl/sql_block;,13,函数不能包含DML语句,所有参数必须为IN,函数中只能用Oracle服务器数据类型，不能用PL/SQL数据类型,可充当单行函数,可在SQL中调用,调用需有EXECUTE权限,14,6、比较函数与过程,过程：作为PL/SQL语句执行；没有RETURN类型；可返回一个值,函数：作为表达式的一部分调用；必须有RETURN类型；必须返回一个值,15,7、在PL/SQL中处理变量,声明PL/SQL变量和常量，语法：,identifier CONSTANT datatype NOT NULL,:= | DEFAULT expr;,遵循命名规则,每行最多声明一个标识符,16,8、声明基本类型变量(Scalar),基本类型有：,BINARY_INTEGER,NUMBER(precision,scale),CHAR(maximum_length),LONG,LONG RAW,VARCHAR2(maximum_length),DATE,BOOLEAN,17,9、%TYPE属性,声明变量，可根据：,另一个已声明的变量,数据库列的定义,由PL/SQL确定变量的数据类型及其宽度,使用%TYPE属性的优点：,可以不知道底层数据库列的数据类型,可以在运行时改变底层数据库列的数据类型,18,10、复合数据类型：,Collection,Index-by Table,Nested Table,VARRY,RECORD,19,11、PL/SQL的RECORD,必须包含一个或多个字段，可由基本数据类型、RECORD、或PL/SQL的TABLE类型组成,与数据库表中的行不同,对从表中获取数据行的处理很方便,20,语法：,TYPE type_name IS RECORD,(field_name1 field_type NOT NULL :=|DEFAULT expr,.);,identifier type_name;,21,12、%ROWTYPE属性,根据数据库表或视图的一组列声明变量,%ROWTYPE的前缀为数据库表,RECORD中字段的名字和数据类型与表或视图的列一致,22,13、PL/SQL的TABLE,由两部分组成：,数据类型为BINARY_INTEGER的主键,基本数据类型列,可动态增加，因其无限制,23,语法：,TYPE type_name IS TABLE OF scalar_datatype NOT NULL,INDEX BY BINARY_INTEGER;,identifier type_name;,24,14、嵌套块和变量的作用范围,只要可执行语句允许，语句就可以嵌套,嵌套块作为一个语句,异常部分可包含嵌套块,对象的作用范围是引用该对象的程序区域,标识符在可引用区域中是可见的,25,15、变量赋值,identifier:=expr;,plsql_table_name(primary_key_value):=expr;,plsql_record_name.field_name:=expr;,26,16、PL/SQL运算符,逻辑运算符（同SQL）,算术运算符（同SQL）,连接运算符（同SQL）,指数运算符（*）,括号可控制操作顺序（同SQL）,27,17、PL/SQL函数,可用的：,单行数值函数（同SQL）,单行字符函数（同SQL）,数据类型转换函数（同SQL）,日期函数（同SQL）,其它函数,不可用的：,GREATEST,LEAST,组函数,28,18、引用非PL/SQL变量,引用的非PL/SQL变量为主机变量,引用的前缀为冒号（：）,29,三、流程控制,1、概述,控制PL/SQL的执行流程：,使用控制结构改变语句的逻辑流程,条件控制结构（IF语句）,循环控制结构：,基本循环,FOR循环,WHILE循环,EXIT语句,30,2、IF语句,语法：,IF condition THEN,statements;,ELSIF condition THEN,statements;,ELSE,statements;,END IF;,31,3、基本循环,语法：,LOOP,statement1;,statement2;,.,EXIT WHEN condition;,END LOOP;,没有EXIT语句，则是死循环,32,4、FOR循环,语法：,FOR index IN REVERSE lower_bound.upper_bound LOOP,statement1;,statement2;,.,END LOOP;,不需要声明index，它是隐式声明的,33,5、WHILE循环,WHILE condition LOOP,statement1;,statement2;,.,END LOOP;,34,6、嵌套循环与标号,循环可多级嵌套,可使用标号区分块和循环,可使用引用标号的EXIT语句退出外循环,35,第八章与数据库交互,36,一、概述,PL/SQL中的SQL命令：,使用SELECT命令可从数据库中获取一行数据,使用DML命令改变数据库的行,使用COMMIT或ROLLBACK命令控制事务,使用DBMS_SQL包发出DDL和DCL语句,37,二、检索数据,1、语法：,SELECT select_list,INTO variable_name|record_name,FROM table,WHERE condition;,38,需要使用INTO子句,数据库列和标识符应使用不同的名字,PL/SQL中的SELECT语句必须只检索一行,如果检索到零或多行，会产生异常,TOO_MANY_ROWS,NO_DATA_FOUND,39,三、操纵数据,使用DML命令改变数据库表：,INSERT,UPDATE,DELETE,40,四、控制事务,1、COMMIT和ROLLBACK命令,紧跟在COMMIT或ROLLBACK后面的第一条DML命令将初始化一个事务,使用SQL命令COMMIT或ROLLBACK可显式地结束一个事务,41,2、命令语法：,COMMIT WORK；,ROLLBACK WORK；,SAVEPOINT savepoint_name;,ROLLBACK WORK TO SAVEPOINT savepoint_name;,42,五、SQL游标,1、概述,游标是一个私有的SQL工作区，执行的每个SQL语句都有一个与其相关的游标,有两类游标：,隐式游标,显式游标,游标的属性：,允许测试SQL命令的输出,可在PL/SQL中象函数一样使用,43,2、隐式游标属性,SQL%ROWCOUNT,SQL%FOUND,SQL%NOTFOUND,SQL%ISOPEN,44,3、控制显式游标,DECLARE创建一个命名的SQL区域,OPEN标识活动集,FETCH将当前行装入变量,EMPTY?测试是否还有行；如果有行则返回到FETCH,CLOSE释放活动集,45,4、声明游标,定义参数，以允许替换游标查询中的值,DECLARE,CURSOR cursor_name IS select_statement;,在游标声明中不要包括INTO子句,46,5、打开游标,打开游标将执行查询，并标识一个活动集,OPEN cursor_name;,查询没有返回行，也不会产生异常,47,6、从游标获取数据,检索当前行的值，将其存入输出变量中,FETCH cursor_name INTO variable1,variable2,.;,需要测试游标是否包含行数据,48,7、关闭游标,对行的处理完成后，应关闭游标,CLOSE cursor_name;,49,8、控制多次获取,可使用循环处理显式游标中的多行,每次循环可获取一行数据,使用%NOTFOUND属性测试,50,9、游标和记录,将数据存入PL/SQL的RECORD可很方便地处理活动集中的行,51,10、带参数的游标,打开游标及执行查询时，会将参数值传给游标,CURSOR cursor_name(parameter_name datatype,.),IS select_statement;,52,11、游标FOR循环,简化显式游标的处理,隐式地打开、获取及关闭游标,FOR record_name IN cursor_name LOOP,statement1;,statement2;,.,END LOOP;,53,12、使用WHERE CURRENT OF子句,使用游标更新或删除当前行,通过在游标查询中包含FOR UPDATE子句，先锁定相关行,SELECT . FROM . FOR UPDATE OF column_reference NOWAIT,使用WHERE CURRENT OF子句引用显式游标中的当前行,54,第九章异常处理,55,一、概述,1、异常,异常的产生：,由Oracle错误产生,显式产生,处理异常,使用一个处理器处理,将其传送到调用环境,56,2、异常类型,预定义的Oracle服务器异常（隐式产生）,非预定义的Oracle服务器异常（隐式产生）,自定义异常（显式产生）,57,二、捕获异常,1、语法：,EXCEPTION,WHEN exception1 OR exception2 . THEN,statement1;,statement2;,.,WHEN OTHERS THEN,statement1;,statement2;,.,58,WHEN OTHERS是最后一个子句,EXCEPTION关键字是异常处理部分的开始,允许多个异常处理器,只有一个处理器被执行,59,2、捕获命名的Oracle服务器错误,在异常处理中引用标准的名字,预定义异常例：,NO_DATA_FOUND,TOO_MANY_ROWS,INVALID_CURSOR,ZERO_DIVIDE,DUP_VAL_ON_INDEX,60,3、捕获未命名的Oracle服务器错误,在声明部分，声明异常的名字；使用PRAGMA EXCEPTION_INIT关联,exception EXCEPTION;,PRAGMA EXCEPTION_INIT(exception,error_number);,在异常处理部分，处理所产生的异常,61,4、捕获自定义异常,在声明部分，声明异常的名字,exception EXCEPTION;,在执行部分，使用RAISE语句显式地产生异常,RAISE exception;,在异常处理部分，处理所产生的异常,62,三、错误处理函数,SQLCODE,返回错误代码的数字值,SQLERRM,返回与错误值相关的信息,63,四、传播异常,子块可以处理异常，也可以将异常传给外部块,64,