PLSQL开发(精)PPT课件

.,1,第八章,PL/Sql开发,.,2,本章要点,PL/SQL基本概念PL/SQL的变量PL/SQL的基本语法Oracle各种程序单元使用游标取出多条数据异常处理,.,3,PL/SQL概述,什么是PL/SQLPL/SQL也是一种程序语言,被称作支持SQL的程序语言(ProgramLanguage),是Oracle数据库对SQL语句的扩展，在普通的SQL语言中增加了编程语言的特点数据操作和查询语句被包含在PL/SQL代码的过程性单元中，经过逻辑判断、循环等操作完成复杂的功能或者计算,.,4,PL/SQL的优点,使用PL/SQL可以编写具有很多高级功能的程序,虽然这些功能可以通过多个SQL语句来完成同样的功能,但是PL/SQL具有如下的优点:使一组语句功能形成模块化程序开发使用过程性语言控制程序结构可以对程序中的错误进行处理具有较好的可移植性集成在数据库中,调用更快减少了网络的交互,有助于提高程序性能,.,5,PL/SQL提供的新特性,PL/SQL提供了一些新的特性,可以进行复杂的信息处理软件包触发器存储过程函数异常处理PL/SQL可以使用所有的SQL数据操作,游标控制和事务控制命令,以及所有的SQL函数和运算符.PL/SQL完全支持SQL数据类型,.,6,SQL,SQL*PLUS,PL/SQL之间的关系,可以把oracle数据库看作餐馆的厨房,而SQL*PLUS是将菜单(脚本,命令或程序)送进厨房(即数据库)的服务员.在厨房中有两个厨师,SQL和PL/SQL.作为服务员的SQL*PLUS知道它可以处理那些命令,那些命令要交给厨师处理.在SQL*PLUS提示符下输入的执行命令或程序就象顾客点的菜.对于每个顾客点的菜,厨师都知道如何进行处理,就像在厨师心中的菜谱一样,PL/SQL也存储常用命令的食谱(这些元素称为触发器,存储函数,存储过程,软件包).有些大菜需要多个厨师共同处理,大多数的程序都是结合了SQL和PL/SQL,并在他们之间来回的传递信息,从而处理脚本或程序.顾客点的菜作好之后,再由作为服务员的SQL*PLUS将执行的结果显示给用户,.,7,PL/SQL提高了性能,Database,Database,SQL,SQL,SQL,SQL,SQLIF.THENSQLELSESQLENDIF;SQL,应用程序,应用程序,.,8,PL/SQL块的基本结构,PL/SQL中起作用的部分都是由基本块组成的.基本块有四个组成部分声明部分:DECLARE可选部分变量、常量、游标、用户定义异常声明执行体开始部分:BEGIN必要部分SQL语句PL/SQL语句异常处理部分:EXCEPTION可选部分程序出现异常时，捕捉异常并处理异常执行体结束:END;必要部分,.,9,PL/SQL块例子,DECLAREv_dept_idemployees.department_id%TYPE;BEGINSELECTdepartment_idINTOv_dept_idFROMemployeesWHEREemployee_id=100;DELETEdepartmentsWHEREdepartment_id=v_dept_id;COMMIT;EXCEPTIONWHENOTHERSTHENROLLBACK;INSERTINTOexception_table(message)VALUES(Someerroroccurredinthedatabase.);COMMIT;END;,.,10,PL/SQL环境,PL/SQLBlock,PL/SQL引擎,oracle数据库,PL/SQLBlock,ProceduralStatementExecutor,non-SQL,SQL,SQLStatementExecutor,SQL引擎,在数据库执行PL/SQL程序的时候,PL/SQL语句和SQL语句被分别解析和执行的。PL/SQL块被数据库内部的PL/SQL引擎提取，将SQL语句取出送给Oralce的SQL引擎。两种语句分别在两种引擎中分析处理，在数据库内部完成数据交互，处理的过程,.,11,在PL/SQL中处理变量,在声明部分声明和初始化变量在执行部分为变量赋新值，或在表达式中使用变量在异常处理部分也可以使用变量通过参数把值传递到PL/SQL块中通过输出变量或者参数将值传出PL/SQL块,.,12,声明变量和常量：语法,定义的标示符名称应遵循命名规则在声明常量和变量的时候可以为其设置初始化值,也可以设置NOTNULL可以使用赋值运算符(:=)或者DEFAULT保留字来初始化标识符在声明时，每行只能声明一个标识符,identifierCONSTANTdatatypeNOTNULL:=|DEFAULTexpr;例如:v_total_salNUMBER(9,2):=0;c_tax_rateCONSTANTNUMBER(3,2):=8.25;v_genderCHAR(1);v_validBOOLEANNOTNULL:=TRUE;,.,13,PL/Sql中的变量类型,简单变量复合（组合）变量外部变量,.,14,简单变量,简单变量不包括任何组件，只能保存一个值基本类型包括三大类：字符,数字,日期BINARY_INTEGER整形数字NUMBER(precision,scale)数字类型CHAR(maximum_length)定长字符类型VARCHAR2(maximum_length)变长字符类型DATE日期类型LONG长字符类型LONGRAW长二进制类型CLOB/BLOB/BFILE大对象类型(字符大对象,二进制大对象,操作系统文件大对象)BOOLEAN布尔类型,有效值为TRUE,FALSE,NULL,.,15,简单变量的声明,v_genderCHAR(1);v_countBINARY_INTEGER:=0;v_total_salNUMBER(9,2):=0;v_order_dateDATE:=SYSDATE+7;c_tax_rateCONSTANTNUMBER(3,2):=8.25;v_validBOOLEANNOTNULL:=TRUE;,.,16,复合数据类型,复合变量也叫做组合变量.在复合变量中包含多个内部的组件,每个组件都可以单独存放值.一个复合变量可以存放多个值与简单变量类型不同,复合变量类型不是数据库中已经存在的数据类型,所以复合变量在声明类型之前,首先要创建使用到的复合类型,然后将变量声明为复合变量复合数据类型：PL/SQLTABLES表类型PL/SQLRECORDS记录类型复合类型被创建后,可以被使用多次定义多个变量,.,17,复合数据类型-TABLE,表类型类似于其他编程语言中的数组类型由两个组件组成：数据类型为BINARY_INTEGER(整形数字)的主键数据类型为一个确定的简单类型的列Table类型没有长度限制，可以动态增长.表类型中的第二部分类似与数组中的值,这个部分必须是一个已经确定的简单类型,不能是其他的复合类型表类型的结构很像数组.第一部分使一个按1递增的整形数字,起到数字索引的作用,第二部分使一种确定的简单类型,用来存放每个索引号对应的具体的数值,.,18,PL/SQLTABLE结构,主键列.1Jones2Smith3Maduro.BINARY_INTEGER标量,.,19,声明一个PL/SQLTABLE,TYPEtype_nameISTABLEOFscalar_datatypeNOTNULLINDEXBYBINARY_INTEGER;identifiertype_name;,.TYPEname_table_typeISTABLEOFVARCHAR2(16)INDEXBYBINARY_INTEGER;v1_namename_table_type;v2_namename_table_type;.,语法：,例子：,.,20,PL/SQLRECORDS,复合类型中的RECODES类型是由多个组件组成的一种类型.包含一个或几个组件，每个组件称为一个域(FIELD)，域的数据类型可以是简单变量类型、另一个RECORD类型或PL/SQL的TABLE类型在使用RECORD变量时把多个域的集合作为一个逻辑单元使用,对记录类型变量赋值或引用,都需要使用“记录变量名.域名”的方式来实现主要用于从表中取出查询到的行数据,.,21,PL/SQLRECORD结构,记录类型可以包含一个或多个域,每个域相当于记录类型变量的一个属性.在使用记录变量类型时,实际上是对记录类型变量的属性进行操作.每个域都可以是不同的数据类型,存放不同类型的数据,Field1(数据类型)Field2(数据类型)Field3(数据类型),.,22,声明PL/SQLRECORD,语法：TYPEtype_nameISRECORD(field_name1field_typeNOTNULL:=|DEFAULTexpr,field_name2field_typeNOTNULL:=|DEFAULTexpr,.);identifiertype_name;例子：.TYPEemp_record_typeISRECORD(last_nameVARCHAR2(25),first_nameVARCHAR2(25),salNUMBER(8);emp_recordemp_record_type;.,.,23,%TYPE与%ROWTYPE,除了象前面那样直接为变量声明一个确定的简单类型或者已经创建好的复合类型外,PL/SQL也支持另外的两种声明变量类型的方法,通红%TYPE和%ROWTYPE属性来声明变量类型,.,24,%TYPE属性,通过%TYPE属性声明一个变量，变量将遵循下面的类型声明：一个已经声明过的变量类型一个数据库中的表的字段定义通过%TYPE类型声明新变量的类型,实际上就是将参照的变量或表中的字段类型作为新变量的类型,新变量的类型与它所参照的类型完全相同,并且保持同步可以作为%TYPE的前缀的可以是数据库表和列前面声明的变量名称PL/SQL在运行程序时确定变量的数据类型和大小,.,25,%TYPE属性：例子,使用%TYPE属性的好处：可能不知道数据库中字段的数据类型数据库中字段的数据类型可以在运行时已被改变和前面声明过的变量的类型保持一致,.v_last_names_emp.last_name%TYPE;v_first_names_emp.first_name%TYPE;v_balanceNUMBER(7,2);v_minimum_balancev_balance%TYPE:=10;.,.,26,%ROWTYPE属性,与%TYPE作用类似，用于定义不确定的类型变量类型将定义为由数据库的表的字段集合构成的RECORD类型%ROWTYPE的前缀是数据库的表名，或者另一个已经定义好的RECORD变量RECORD中的域，与表的字段的名称,个数,数据类型,以及长度完全相同,可以用来存放从表中取出的一条记录的所有的字段值(select*from表),.,27,%ROWTYPE属性：优点,数据库中表字段的数据类型和数目可能不知道数据库中表字段的个数和数据类型会在运行中改变,.dept_records_dept%ROWTYPE;emp_records_emp%ROWTYPE;.,.,28,PL/SQL块语法规则,语句可以写在多行,就象SQL语句一样各个关键字,字段名称等,通过空格分隔每条语句必须通过分号结束,包括PL/SQL结束部分的END关键字后面也需要加分号标识符的规定：最多可以包含30个字符不能包含保留字，若有使用双引号括起来必须以字母字符开始不能与数据库的表或者列名称相同,.,29,PL/SQL语法规则,在PL/SQL程序中出现的字符和日期必须用单引号括起来数字可以是简单值或科学计数法表示V_SALNUMBER(4):=2000;V_YEARNUMBER(4):=V_SAL*12;在PL/SQL中也要养成添加注释的习惯,注释可以是/*和*/之间的多行注释单行注释，以-开始,.,30,赋值语句,使用“:=”作为赋值符号，而不是“=”语法:identifier:=expr;plsql_table_name(primary_key_value):=expr;plsql_record_name.field_name:=expr;,.,31,变量赋值,设置保存薪水的变量v_sal:=5000;v_married:=true;在PL/SQLTABLE的第一个值中保存名字“Smith/*前面定义好的table型的变量TYPEname_table_typeISTABLEOFVARCHAR2(16)INDEXBYBINARY_INTEGER;v1_namename_table_type;v2_namename_table_type;*/v1_name(1):=Smith;把一个员工的基本信息保存到PL/SQLRECORD中/*前面定义好的RECORD型的变量TYPEemp_record_typeISRECORD(last_nameVARCHAR2(25),first_nameVARCHAR2(25),salNUMBER(8);emp_recordemp_record_type;*/emp_record.last_name:=Smith;emp_record.first_name:=zhang;emp_record.sal:=5000;,.,32,嵌套块和变量作用域,PL/SQL程序也叫做PL/SQL程序块,在程序块中可以嵌套另一个程序块,外部的程序块叫做“父块”或“外部块”,嵌套的块叫“子块”或“嵌套块”只要是允许执行语句的地方，就可以使用嵌套语句嵌套块也被当作一个语句异常部分也可以包含嵌套块，嵌套块也可以包括异常部分对象的作用域指的是可以应用对象的程序范围标识符可见的范围：子块可以向上查看父块的标识符父块不能向下查看到子块的标识符,.,33,嵌套块和变量作用域,DECLARExBINARY_INTEGER;BEGIN.DECLAREyNUMBER;BEGIN.END;.END;,.,34,嵌套块中的变量赋值,setserveroutputon;declarev_weightnumber(3):=100;begin-嵌套块-declarev_weightnumber(3):=1;beginv_weight:=v_weight+1;dbms_output.put_line(在嵌套块中v_weight的值是:|v_weight);end;-v_weight:=v_weight+1;dbms_output.put_line(在嵌套块外v_weight的值是:|v_weight);end;/,.,35,PL/SQL语句中的操作符,逻辑操作符数学操作符连接操作符指数操作符,.,36,PL/SQL中的操作符,设置Boolean标志的值v_equal:=(v_1=v_2);为循环增加计数器的值v_count:=v_count+1;,.,37,setserveroutputon;declarev_1number(3):=100;v_2number(3):=100;v_validboolean;beginv_valid:=(v_1=v_2);if(v_valid)thendbms_output.put_line(真);elsedbms_output.put_line(假);endif;end;/,.,38,PL/SQL中的函数,可用：数字函数字符函数类型转换函数日期函数不可用：组函数,.,39,PL/SQL中的函数,计算姓名字符数：setserveroutputon;declarev_namevarchar2(20):=aaa;v_addrvarchar2(20):=北京;v_countnumber(3);beginv_count:=length(v_addr);dbms_output.put_line(v_count);end;/,.,40,数据类型转换,把数据转换为可操作的数据类型转换函数：TO_CHARTO_DATETO_NUMBER,.,41,数据类型转换,setserveroutputon;declarev_1varchar2(20);begin-v_1:=USER|:|TO_CHAR(SYSDATE);v_1:=USER|:|SYSDATE;dbms_output.put_line(v_1);end;/,.,42,PL/SQL中的SQL命令,在PL/SQL程序块中可以使用各种SQL命令，但是使用的方法根据命令不同也各不相同使用SELECT命令，可以从数据库中取出单行数据,语法略有变化使用DML命令，修改数据库中的行,没有变化使用COMMIT或ROLLBACK命令控制事务,没有变化通过EXECUTEIMMEDIATE，执行DDL和DCL语句,.,43,查询数据,在PL/SQL中通过SELECT从数据库中检索数据：SELECTselect_listINTOariable_name|record_nameFROMtableWHEREcondition;必须使用INTO子句,用于将查询出的数据传递给变量查询必须并且只能返回一行可以使用完整的SELECT语法,.,44,查询数据,取出某个员工的雇佣时间和所属部门,droptableemployees;createtableemployees(empl_idnumber(5)primarykey,hire_datedate,department_idnumber(5);insertintoemployeesvalues(1000,1-2月-1999,2000);insertintoemployeesvalues(1001,2-2月-1999,2000);insertintoemployeesvalues(1002,3-2月-1999,2000);insertintoemployeesvalues(1003,4-2月-1999,2000);commit;setserveroutputon;declarev_hire_dateemployees.hire_date%type;v_department_idemployees.department_id%type;beginselecthire_date,department_idintov_hire_date,v_department_idfromemployeeswhereempl_id=1000;dbms_output.put_line(v_hire_date|-|v_department_id);end;/,.,45,查询数据,返回员工的平均薪水,droptableemployees;createtableemployees(empl_idnumber(5)primarykey,hire_datedate,salarynumber(8,2),department_idnumber(5);insertintoemployeesvalues(1000,1-2月-1999,2000,2000);insertintoemployeesvalues(1001,2-2月-1999,5000,2000);insertintoemployeesvalues(1002,3-2月-1999,8900,2000);insertintoemployeesvalues(1003,4-2月-1999,6700,2000);commit;setserveroutputon;declarev_avg_salaryemployees.salary%type;beginselectavg(salary)intov_avg_salaryfromemployees;dbms_output.put_line(平均工资是|:|v_avg_salary);end;/,.,46,查询异常,P