PLSQL编程基础课件

第7章PL/SQL编程基础前面所学习的SQL是操作关系性数据库的一种通用语言，但是SQL本身是一种非过程化的语言，即不用指明执行的具体方法和途径，而是简单地调用相应语句直接取得结果即可。所以SQL本身并不适合在复杂的业务流程下使用，为了解决这一问题，Oracle提供了PL/SQL编程，这是一种过程化编程语言，与JAVA语言一样关注于处理细节，可以实现较为复杂的业务逻辑。PLSQL编程基础7.1PL/SQL简介PL/SQL语法结构DECLARE--声明部分，例如：定义变量、常量、游标BEGIN--程序编写、SQL语句EXECEPTION--处理异常END;/声明部分（DECLARE）：包含变量定义、用户定义的PL/SQL类型、游标、引用的函数或过程。执行部分（BEGIN）：包含变量赋值、对象初始化、条件结构、迭代结构、嵌套的PL/SQL块，或是对局部或者存储PL/SQL命名块的调用。异常部分（exception）：包含错误处理语句，该语句可以像执行部分一样使用所有项。结束部分（END）:程序执行到END表示结束，分号用户结束块，正斜杠（/）执行块程序PLSQL编程基础既没有变量，也没有流程控制（分支、循环）。PL/SQL与SQL区别：SQLPL/SQLPL/SQL是结构化和过程化的结合体。每条SQL语句逐一发送给数据库。一次性将多条SQL语言一起发送给数据库7.1PL/SQL简介PLSQL编程基础最简单的PL/SQL块可以不做任何事情，但是在PL/SQL编程过程中要求，执行块至少要有一条语句，即使这条语句只是编写一个NULL也行。范例：编写不做任何工作的PL/SQL块。BEGINNULL;END;/范例：编写一个简单的PL/SQL程序DECLAREv_numnumber;BEGINv_num:=30;DBMS_output.put_line(‘v_num变量的内容是：’||v_num);END;7.1PL/SQL简介PLSQL编程基础范例：编写PL/SQL块，输入一个雇员编号，而后取得指定的雇员姓名。Declarev_enonumber;v_enamevarchar2(10);BeginV_eno:=&empno;--由键盘输入雇员编号Selectenameintov_enamefromempwhereempno=v_eno;DBMS_OUTPUT.put_line('编号为:'||v_eno||'雇员的名字为：'||v_ename);End;7.1PL/SQL简介PLSQL编程基础7.2

变量的声明与赋值变量声明并使用变量：变量名称可以有字母、数字、_、$、#等组成。所有变量名称要求以字母开头，不能是Oracle中的保留字变量的长度最多只能为30个字符变量不区分大小写PLSQL编程基础7.2

变量的声明与赋值声明变量语法：变量名称[CONSTANT]类型[notNULL][:=value]使用%TYPE声明变量类型：在编写PL/SQL程序时，如果希望某一个变量与指定数据表中某一列的类型一样，则可以采用“变量定义”表名称.表名称%TYPE的格式PLSQL编程基础7.2

变量的声明与赋值Declarev_enoemp.empno%type;v_enameemp.ename%type;BeginDBMS_output.put_line('请输入雇员编号：');v_eno:=&empno;selectenameintov_enamefromempwhereempno=v_eno;DBMS_output.put_line('编号为：'||v_eno||'雇员的名字为：'||v_ename);end;PLSQL编程基础7.2

变量的声明与赋值使用%ROWTYPE声明变量类型PL/SQL提供了一种%ROWTYPE，使用此标记可以定义表中一行记录的类型。当用户使用了“select……into……”将表中的一行记录设置为ROWTYPE类型的变量中时，就可以利用“rowtype变量.表字段”的方式取得表中每行对应的列数据。PLSQL编程基础7.2

变量的声明与赋值Declarev_deptrowdept%rowtype;----装载一行dept记录

Beginselect*intov_deptrowfromdeptwheredeptno=10;DBMS_output.put_line('部门编号：'||v_deptrow.deptno||'名称：'||v_deptrow.dname||'位置：'||v_deptrow.loc);End;PLSQL编程基础7.3

标量类型数值型标量类型字符型日期型大对象布尔PLSQL编程基础7.3

标量类型No.分类数据类型描述1数值型Number(数据总长度[,小数位长度])Number是一种表示数字的数据类型。可声明保存数据类型的整数位和小数位的精度，在数据库中是以十进制格式存储，在计算时，系统将其变成二进制数据运算，占32字节。BINARY_INTEGER不存储在数据库中，只能在PL/SQL中使用的带符号整数，范围为-231-231，如果发生溢出，则自动变为number型数据。23PLS_INTEFER有符号的整数，范围为-231-231，可以直接进行数学运算，溢出则触发异常，与Number相比，其占空间小，性能好4BINARY_FLOAT单精度32位浮点类型，占5个字节5BINARY_DOUBLE双精度浮点数类型，占9个字节PLSQL编程基础7.3

标量类型No.分类数据类型描述12字符型RAW保存固定长度的二进制数据，最多可以存放2000个字节的数据LONGRAW存储二进制数据（图片、音乐等），最多可以存储2G大小数据1314ROWID数据表中每行记录的唯一物理地址标示，只支持物理行ID，不支持逻辑行ID15UROWID支持物理行ID和逻辑行IDPLSQL编程基础7.3

标量类型No.分类数据类型描述16日期型DATADATA是一个7字节的列，可以保存时期和时间，不包含毫秒TIMESTAMPDATA子类型，包含日期和时间，时间部分包含毫秒，有TIMESTAMPWITHTIMEZONE和TIMESTAMPWITHLOCALTIMEZONE1718INTERVALDATA的子类型，用于管理时间间隔，有INTERVALDAY

TOSECOND和INTERVALYEARTOMONTH两种子类型PLSQL编程基础7.3

标量类型No.分类数据类型描述19大对象CLOBCLOB数据类型代表字符型大对象，最大存储4GB的字符串数据NCLOB存储UNICODE编码的大文本数据，最多可以存储4GB的非结构化的二进制数据2021BLOBBLOB数据类型列可以包含最大4GB的非结构化二进制数据22BFILE包含存储在外部文件系统上文件的索引，最大不超过4GB23布尔BOOLEAN布尔类型，可以设置的内容为TRUE、FALSE、NULLPLSQL编程基础7.4

程序结构PL/SQL程序与其他编程语言一样，也拥有自己的3中程序结构，即顺序结构、分支结构、循环结构。开始语句1语句2语句n结束顺序结构判断条件语句2语句n结束语句1分支结构语句1语句2判断条件循环结构PLSQL编程基础7.4.1

分支结构IF语句If语句IF……else语句IF……elseif……else语句If判断条件Then

满足条件执行语句；Endif;If判断条件then

满足条件执行语句；Else

不满足条件执行语句；EndIF;If判断条件1

then

满足条件1执行语句；Elseif判断条件2then……Else

所有条件不满足执行语句；ENDif;PLSQL编程基础7.4.1

分支结构范例：查询emp表的工资。输入员工编号，根据编号查询工资。如果工资高于3000元，则显示高工资；如果工资大于2000元则显示中等工资；如果小于2000元，则显示低工资。PLSQL编程基础DECLARE v_empSal emp.sal%TYPE; --定义变量与emp.sal字段类型相同

v_empName emp.ename%TYPE; --定义变量与emp.ename字段类型相同

v_eno emp.empno%TYPE; --定义变量与emp.empno字段类型相同BEGIN v_eno:=&inputEmpno; --用户输入要查找的雇员编号

--根据输入的雇员编号查找雇员姓名及工资

SELECTename,salINTOv_empName,v_empSalFROMempWHEREempno=v_eno; IFv_empSal>3000THEN --判断

DBMS_OUTPUT.put_line(v_empName||'的工资属于高工资！'); ELSIFv_empSal>2000THEN --判断

DBMS_OUTPUT.put_line(v_empName||'的工资属于中等工资！'); ELSE DBMS_OUTPUT.put_line(v_empName||'的工资属于低工资！'); ENDIF;END;7.4.1

分支结构PLSQL编程基础范例：用户输入一个雇员编号，根据它所在的部门给上涨工资，规则：·10部门上涨10%，20上涨20%，30上涨30%；·

但是要求最高不能超过5000，超过5000就停留在5000。7.4.1

分支结构PLSQL编程基础7.4.1

分支结构DECLARE v_empSal emp.sal%TYPE; --定义变量与emp.sal字段类型相同 v_dno emp.deptno%TYPE; --定义变量与emp.deptno字段类型相同 v_eno emp.empno%TYPE; --定义变量与emp.empno字段类型相同BEGIN v_eno:=&inputEmpno; --用户输入要查找的雇员编号 SELECTdeptno,salINTOv_dno,v_empSalFROMempWHEREempno=v_eno; IFv_dno=10THEN IFv_empSal*1.1>5000THEN UPDATEempSETsal=5000WHEREempno=v_eno; ELSE UPDATEempSETsal=sal*1.1WHEREempno=v_eno; ENDIF; ELSIFv_dno=20THEN IFv_empSal*1.2>5000THEN UPDATEempSETsal=5000WHEREempno=v_eno; ELSE UPDATEempSETsal=sal*1.2WHEREempno=v_eno; ENDIF; ELSIFv_dno=30THEN IFv_empSal*1.3>5000THEN UPDATEempSETsal=5000WHEREempno=v_eno; ELSE UPDATEempSETsal=sal*1.3WHEREempno=v_eno; ENDIF; ELSE null; ENDIF;END;PLSQL编程基础7.4.1

分支结构CASE语句Case语句是一种多条件的判断语句，其功能与IF……elseif……else类似Case语法：Case[变量]when[值|表达式]THEN执行语句块；when[值|表达式]THEN执行语句块；Else条件都不满足时执行语块；EndcasePLSQL编程基础7.4.1

分支结构范例：使用CASE进行多条件判断DECLARE v_salary emp.sal%TYPE; v_eno emp.empno%TYPE;BEGIN v_eno:=&inputEmpno; SELECTsalINTOv_salaryFROMempWHEREempno=v_eno; CASE WHENv_salary>=3000THEN DBMS_OUTPUT.put_line('雇员：'||v_eno||'的收入为高工资。'); WHENv_salary>=2000ANDv_salary<3000THEN DBMS_OUTPUT.put_line('雇员：'||v_eno||'的收入为中等工资。'); ELSE DBMS_OUTPUT.put_line('雇员：'||v_eno||'的收入为低工资。'); ENDCASE;END;/PLSQL编程基础7.4.1

分支结构范例：输入雇员编号，根据雇员的职位进行工资提升，提升要求如下

·如果职位是办事员（CLERK），工资增长5%；

·如果职位是销售人员（SALESMAN），工资增长8%；

·如果职位为经理（MANAGER），工资增长10%；

·如果职位为分析员（ANALYST），工资增长20%；

·如果职位为总裁（PRESIDENT），工资不增长。PLSQL编程基础7.4.2

循环结构在PL/SQL程序中，循环结构一共定义了两种，及LOOP循环和for循环。其中，LOOP主要使用在不确定循环次数的操作中，而FOR使用在明确知道循环次数的操作中。LOOP循环While……LOOPLOOP

循环执行的语句块；exitwhen循环结束条件；

循环结束条件修改；ENDLOOPWHILE（循环结束条件）LOOP

循环执行的语句块；

循环结束条件修改；ENDLOOPPLSQL编程基础7.4.2

循环结构For循环语法FOR循环索引IN[REVERSE]循环区域下限…循环区域上限LOOP循环执行的语句块；ENDLOOP;PLSQL编程基础7.4.2

循环结构范例：使用LOOP循环DECLARE v_iNUMBER:=1; --定义一个变量，用于循环BEGIN LOOP DBMS_OUTPUT.put_line('v_i='||v_i); EXITWHENv_i>=3; v_i:=v_i+1; ENDLOOP;END;PLSQL编程基础7.4.2

循环结构范例：使用WHILE…LOOP循环DECLARE v_iNUMBER:=1; --定义一个变量，用于循环BEGIN WHILE(v_i<=3)LOOP DBMS_OUTPUT.put_line('v_i='||v_i); v_i:=v_i+1; ENDLOOP;END;PLSQL编程基础7.4.2

循环结构范例：使用FOR循环DECLARE v_iNUMBER:=1; --定义一个变量，用于循环BEGIN FORv_iIN1..3LOOP DBMS_OUTPUT.put_line('v_i='||v_i); ENDLOOP;END;PLSQL编程基础7.4.2

循环结构循环控制在正常循环操作中，如果需要结束循环或者退出当前循环，可以使用EXIT与CONTINUE语句来完成。范例：使用EXIT结束循环操作DECLARE v_iNUMBER:=1; --定义一个变量，用于循环BEGIN FORv_iIN1..10LOOP IFv_i=3THEN --当v_i变量增长到3时结束循环 EXIT; ENDIF; DBMS_OUTPUT.put_line('v_i='||v_i); ENDLOOP;END;PLSQL编程基础7.4.2

循环结构循环控制范例：使用CONTINUE控制循环操作DECLARE v_iNUMBER:=1; --定义一个变量，用于循环BEGIN FORv_iIN1..10LOOP IFMOD(v_i,2)=0THEN --为偶数的时候不执行后续方法体 CONTINUE; ENDIF; DBMS_OUTPUT.put_line('v_i='||v_i); ENDLOOP;END;PLSQL编程基础7.5

内部程序块对每个PL/SQL程序块，其基本的组成部分就是DECLARE、BEGIN、END，如果用户有需要，也可以在一个程序中定义多个子程序模块，语法如下：DECLARE----声明部分，例如定义变量、常量、游标BEGIN--程序编写、SQL语句；DECLARE--子程序编写、SQL语句EXCEPTION--子程序处理异常END；EXCEPTION--异常处理END;

PLSQL编程基础7.5

内部程序块范例：定义内部程序块DECLARE v_x NUMBER:=30; --此为全局变量BEGIN DECLARE v_xVARCHAR2(40):='MLDNJAVA';--此为局部变量，--只能在内部程序块中使用 v_y NUMBER:=20; BEGIN DBMS_OUTPUT.put_line('内部程序块输出：v_x='||v_x); DBMS_OUTPUT.put_line('内部程序块输出：v_y='||v_y); END; DBMS_OUTPUT.put_line('外部程序块输出：v_x='||v_x);END;PLSQL编程基础7.6

异常处理程序开发中会由于设计错误、编码错误、硬件故障或其他原因奇异程序运行出错，PL/SQL中的异常处理机制，使得在出现错误的时候程序仍能执行。异常类型编译型异常：程序语法出现错误导致的异常运行型异常：程序语法没有语法错误，但因为程序运算或者返回结果而出现错误PLSQL编程基础7.6

异常处理异常处理语法：WHEN异常类型|用户定义异常|异常代码|OTHERSTHEN异常处理；在一个EXCEPTION语句块中可以同时编写多个WHEN，用于判断不同的异常类型。异常可以是系统定义的，也可以用户自定义的异常类型。PLSQL编程基础7.6

异常处理常见预定义异常NO.异常名称异常代码触发条件1ACCESS_INTO_NULLORA-06530试图访问未初始化的对象属性是触发2CASE_NOT_FOUNDORA-06592Case语句格式有误，没有分支语句触发3INVALID_CURSORORA-01001在游标操作中指针出现异常（未打开或已关闭）4SUBSCRIPT_OUTSIDE_LIMFITORA-06532当访问嵌套表或可变数组时使用了非法索引值触发5TIMEOUT_ON_RESOUrCEORA-00051访问锁定资源时间过长时触发6VALUE_ERRORORA-06502试图将一个变流量内容赋值给另一个不能容纳变量内容时触发7ZERO_DIVIDEORA-01476当使用出发计算被除数为0时触发PLSQL编程基础7.6

异常处理范例：处理SQL异常——找不到数据DECLARE v_eno emp.empno%TYPE; v_ename emp.ename%TYPE;BEGIN v_eno:=&empno; --由键盘输入雇员编号 SELECTenameINTOv_enameFROMempWHEREempno=v_eno; DBMS_OUTPUT.put_line('编号为：'||v_eno||'雇员的名字为：'||v_ename);EXCEPTION WHENno_data_foundTHEN DBMS_OUTPUT.put_line('没有这个雇员！');END;PLSQL编程基础7.6

异常处理用户自定义异常：范例：使用用户定义异常DECLARE v_data NUMBER; v_myexp EXCEPTION