第9章 plSQL

PL/SQL编程第9章本章内容9.1PL/SQL简介9.2条件选择语句9.3循环语句9.4游标9.5异常处理一、PL/SQL特点1.PL/SQL的优点（1）PL/SQL是一种高性能的基于事务处理的语言，能运行在任何Oracle环境中，支持所有数据处理命令。通过使用PL/SQL程序单元处理SQL的数据定义和数据控制元素。（2）PL/SQL支持所有SQL数据类型和所有SQL函数，同时支持所有Oracle对象类型。9.1PL/SQL简介（3）PL/SQL块可以被命名和存储在Oracle服务器中，同时也能被其他的PL/SQL程序或SQL命令调用，任何客户/服务器工具都能访问PL/SQL程序，具有很好的可重用性。（4）PL/SQL是以整个语句块发给服务器，这样可以降低网络拥挤。二、开发及运行环境服务器端PL/SQL不需要显式的安装或许可。它是Oracle数据库的隐式部分，此处有相应的文档。PL/SQL编译器和解释器也嵌入到OracleDeveloper中，为开发者在客户端和服务器端提供一致的、可利用的开发模型。PL/SQL的体系结构•PL/SQL引擎驻留在Oracle服务器中•该引擎接受PL/SQL块并对其进行编译执行将PL/SQL块发送给Oracle服务器用户执行过程语句引擎将SQL语句发送给SQL语句执行器Oracle服务器PL/SQL引擎SQL语句执行器过程语句执行器执行SQL语句将结果发送给用户PL/SQL的结构•PL/SQL是一种块结构的语言，组成PL/SQL程序的单元是逻辑块，一个PL/SQL程序包含了一个或多个逻辑块。每个块都可以划分为三个部分。①声明部分（Declarationsection）•声明部分包含了变量和常量的数据类型和初始值。这个部分是由关键字DECLARE开始，如果不需要声明变量或常量，那么可以忽略这一部分。②执行部分（Executablesection）•执行部分是PL/SQL块中的指令部分，由关键字BEGIN开始，所有的可执行语句都放在这一部分，其他的PL/SQL块也可以放在这一部分。③异常处理部分（Exceptionsection）•这一部分是可选的，在这一部分中处理异常或错误，对异常处理的详细讨论在后面进行。•PL/SQL块语法结构如下：[DECLARE]声明部分BEGIN执行部分[EXCEPTION异常处理部分]END;说明：•PL/SQL语句以分号（；）结束。•PL/SQL程序块的注释方式：•单行注释：--注释内容•多行注释（块注释）：/*注释内容*/•本章介绍的是PL/SQL无名程序块，只能在SQL*Plus和iSQL*Plus环境下执行一次。第10章介绍的存储过程、函数和触发器等命名的PL/SQL程序块，可以单独编译、多次执行。[DECLARE]声明部分BEGIN……[DECLARE]声明部分BEGIN执行部分[EXCEPTION异常处理部分]END;[EXCEPTION异常处理部分]END;子块主块块的嵌套结构PL/SQL代码块结构Declarev_numnumber:=0;Beginv_num:=2/v_num;Exceptionwhenothersthendbms_output.put_line(‘error’);End;最简单的PL/SQL块•Begin•dbms_output.put_line(‘HelloWorld!’);•End;设置服务器端的输出状态Setserveroutputon块的执行SQL*PLUS中匿名的PL/SQL块的执行是在PL/SQL块后输入/来执行。命名的程序与匿名程序的执行不同，执行命名的程序块必须使用execute关键字:。如果在另一个命名程序块或匿名程序块中执行这个程序，那么就不需要EXECUTE关键字。注意:二、常量与变量的声明Declarev_namevarchar2(20);Beginv_name:=‘myname’;dbms_output.put_line(v_name);End;变量声明的规则1.变量名不能使用保留字。如from，select2.第一个字符必须是字母3.变量名最多包含30个字符4.不要与数据库的表或者列同名5.每一行只能声明一个变量常用的变量类型1.binary_integer：整数，主要用来计数而不是用来表示字段类型。2.number：数字类型3.char：定长字符串4.varchar2：变长字符串5.date：日期6.long：长字符串，最长2G7.Boolean：布尔类型，可以取true，false，null1、变量（1）声明变量声明变量的语句格式如下：Variable_name[CONSTANT]datatype[NOTNULL][:=DEFAULTexpression]可以在声明变量的同时给变量强制性的加上NOTNULL约束条件，此时变量在初始化时必须赋值。注意:（2）给变量赋值给变量赋值有两种方式：①直接给变量赋值:=②通过SQLSELECTINTO或FETCHINTO给变量赋值2、常量常量与变量相似，但常量的值在程序内部不能改变，常量的值在定义时赋予，声明方式与变量相似，但必须包括关键字CONSTANT。常量和变量都可被定义为SQL和用户定义的数据类型。--变量声明declarev_tempnumber(1);v_countbinary_integer:=0;v_salnumber(7,2):=4000.00;v_datedate:=sysdate;v_piconstantnumber(3,2):=3.14;v_validboolean:=false;v_namevarchar2(20)notnull:='myname';begindbms_output.put_line('v_tempvalue:'||v_temp);end;注：没有赋初值的变量取NULL3、参考类型•为了保证变量的类型与某个字段的数据类型一致，可以将数据表的某字段的数据类型指定给所声明的变量，这样，当字段的数据类型改变时，变量的数据类型也会随之变化，从而使得PL/SQL程序具有相对稳定性。•下面介绍2种常见的参考类型：①%TYPE②%ROWTYPE%TYPE•%TYPE:一个变量的类型定义参考另一个已经定义的变量类型，或参考数据表中某个字段的数据类型。•例如：v1CHAR(6);V2V1%TYPE;snameemp.ename%TYPE;%TYPE•declare•v_empnonumber(4);•v_empno2emp2.empno%type:=1234;•v_empno3v_empno2%type;•begin•dbms_output.put_line(v_empno2);•end;%ROWTYPE•%ROWTYPE:一个变量的类型定义参考某个表或视图的结构，此类型变量内的分量的名字、数据类型与表或视图结构中的字段名字、数据类型完全一致。•例如：定义：stadept%ROWTYPE;dept表中有deptno,dname,loc等字段，则sta变量的分量表示为：sta.deptno,sta.dname,sta.loc。%ROWTYPEdeclarev_tempdept2%rowtype;beginv_temp.deptno:=50;v_temp.dname:='aaaa';v_temp.loc:='bj';dbms_output.put_line(v_temp.dname||‘'||v_temp.loc);end;注：引用部分的名字不能改，必须和被参考表的字段名一样复合变量--记录类型一种复合数据类型，与数据库中表的行结构非常相似，使用记录类型定义的变量可以存储由一个或多个字段组成的一行数据。创建记录类型需要使用TYPE语句，其语法如下：TYPErecord_nameISRECORD(field_namedata_type[[NOTNULL]{:=|DEFAULT}value][,…]);语法说明如下：•record_name创建的记录类型名称。•ISRECORD表示创建的是记录类型（区别于后面的表类型）。•field_name记录类型中的字段名。--record变量类型declaretypetype_record_deptisrecord(dnamedept.dname%type,locdept.loc%type);v_temptype_record_dept;beginv_temp.deptno:=50;v_temp.dname:='aaaa';v_temp.loc:='bj';dbms_output.put_line(v_temp.dname||''||v_temp.loc);end;替代变量提供用户输入的界面，在用户输入数据后，先替换PL/SQL脚本，再编译执行。其语法如下：&替换内容--根据用户输入的年龄打印输出。declarev_agenumber(3);beginv_age:=&年龄;dbms_output.put_line(|’您的年龄是:’|v_age);end;替代变量并不是一个真正的变量,它不能保存任何内容,只是提供一个替换功能.练一练根据用户输入的姓名,输出姓名值.替代的如果是字符串,则该变量必须加上单引号!复合变量—表类型思考:根据用户的输入来输出性别的值,如果输入10,输出男,输入20,输出女.语法：TYPE类型名ISTABLEOF值类型INDEXBY索引类型说明：定义一个表格结构,这个表格用于保存”索引键值对”,这样可以方便通过索引找到值。值类型：实际保存的值的类型。如：男，女索引类型：Binary_integerPls_integerdeclare--1.定义表结构typemyTabTypeistableofchar(2)indexbypls_integer;--2.创建表类型变量sexTabmyTabType;v_sex_codenumber(2);v_sex_namechar(2);begin--3.插入数据：sexTab(10):='男';sexTab(20):='女';--用户输入v_sex_code:=&性别编号;--4.查询数据(如：sexTab(10)表示“查询索引为10的值，得到男”)v_sex_name:=sexTab(v_sex_code);dbms_output.put_line('您的性别是='||v_sex_name);end;sexTab在插入数据以后，在内存结构如下：index值10男20女说明：PL/SQL的下标键值可以不连续，也可以是负值，甚至可以是字符串。根据用户的输入来输出性别的值,如果输入nan,输出男,输入nv,输出女.练一练小结：·保存一个值的变量，可以用具体的数据或者type类型·保存多个值的变量，可以用rowtype，record。·保存多行2列的变量，可以用table类型。Select语句在PL/SQL中的应用select要和into一起用并且保证有且只有一条记录的返回值。declarev_enameemp.ename%type;v_salemp.sal%type;beginselectename,salintov_ename,v_salfromempwhereempno=7369;dbms_output.put_line(v_ename||''||v_sal);end;Select的简单用法declarev_empemp%rowtype;beginselect*intov_empfromemp2whereempno=7369;dbms_output.put_line(v_emp.ename);end;DML语句的用法declarev_deptnodept.deptno%type:=50;v_dnamedept.dname%type:=‘aaaa’;v_locdept.loc%type:=‘bj’;begininsertintodeptvalues(v_deptno,v_dname,v_loc);commit;end;给10部门的员