oracle_plsql编程

《oracle_plsql编程》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。

PL/SQL编程基础篇--PL/SQL基础什么是PL/SQL？加入了编程性的结构化得语言结构，使得标准SQL（注意：我们现在采用的SQL标准基于92标准）能够像程序语言一样，执行相关业务逻辑的处理。例如：异常处理业务程序逻辑等PL/SQL的特点是，将业务逻辑处理部分、变量声明部分、异常处理部分分开。将原来以散列结构的SQL语句编程一个批量化执行的SQL语句块。语句块：指的是有一个开始的地方和一个结束的地方，就称之为语句块。Begin---程序的开始End；----程序的结束结束的时候注意：“分号”PL/SQL的工作原理与工作过程1、执行过程相当于在执行程序的编译，该工作由PL/SQL引擎来完成，工作原理：PL/SQL引擎过程语句执行器先由PL/SQL引擎接受用户的PL/SQL语句块，进行翻译，然后由过程语句执行器解析控制过程语言部分，将SQL语句提取出来转发给SQL语句执行器。2、SQL语句执行器接收PL/SQL引擎发送过来的SQL语句进行数据库的操作，并将结果返回给PL/SQL引擎。3、PL/SQL引擎将服务器的执行结果返回给用户。PL/SQL注释PL/SQL注释有两种：单行注释、多行注释1、单行注释–-开始例如：--声明变量V_idnumber;2、多行注释/**/例如：/**对PL/SQL用途的一个说明*作者：*时间：*SQL的名称 */PL/SQL的基本结构PL/SQL的结构：变量定义部分Declare----定义变量开始Begin----结束逻辑部分Begin----程序开始End；----结束程序异常处理部分BeginException----异常处理End；PL/SQL块还可以被称之为：匿名的PL/SQL块注意：在日常开发中，写的PL/SQL块都要一次性将结构写完，然后再进行业务逻辑处理部分的写作。DeclareBeginExceptionEnd；PL/SQL变量的声明与赋值变量声明：1、标示符（要注意：命名规范和可用字符）2、数据类型（可以使用的数据库类型包括：基本数据库类型【Oracle所支持所有的基本数据类型】、复合数据类型【%type、%rowtype、record、table】）示例：----声明了一个number类型的变量V_idnumber;常量声明：（声明方式和变量的声明方式一样）。常量声明需要用关键字：CONSTANT来标示它为常量。常量必须在声明的时候赋值，以后不能作值的修改。变量和常量的赋值： 1、直接赋值使用“：=“可以为变量或常量直接赋值。例如：v_idnumber:=10;2、selectinto的语句或者fetchinto语句给变量或常量赋值例如：V_idnumber;采用selectinto赋值：Selectempnointov_idfromempwhereempno=7788；采用fetchinto赋值：Fetch游标into变量；----（游标在后续课程中作讲解）命名的规范示例：·当定义变量时，建议用v_作为前缀，如v_sal·当定义常量时，将以用c_作为前缀，如c_rate·当定义游标时，建议用_cursor作为后缀，如emp_cursor·当定义例外时，建议用e_作为前缀，如e_errorn屏幕输出包当我们在PL/SQL编辑器里面使用屏幕输出语句的时候，需要使用到系统给我们提供一个输出包：DBMS_OUTPUT----屏幕输出包在该包下面有几个我们常用过程：PUT_LINE----向屏幕输出一行字符，并换行NEW_LINE----向屏幕输出新的一行字符PUT----向屏幕输出一行字符，不换行注意：当我们执行的PL/SQL语句块是在SQLPLUS里面的话，需要先将屏幕输出选项打开。SETSERVEROUTPUTON;--PL/SQL基本数据类型基本数据类型包括：基本数据类型通常情况下面是用来声明一个简单的变量。--简单变量:只能够保存一个值，不包含其他的组件的变量。nBINARY_INTEGERnNUMBER[(precision,scale)]nCHAR[(maximum_length)]nVARCHAR2(maximum_length)nDATEnLONGnLONGRAWnCLOB/BLOB/BFILEnBOOLEAN 示例：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;declarev_genderchar(1);beginv_gender:=M;DBMS_OUTPUT.PUT_LINE(V_GENDER);end;--PL/SQL复合数据类型复合数据类型包括：1.%typeGender----隐藏此数据类型char(1);引用该数据类型来作为该变量的数据类型。V_sex数据类型未知；声明变量：v_sex用来获取Gender列的数据。现在我就只能使用对Gender这列的数据类型进行引用。数据类型的引用就使用%type来完成。日常PL/SQL编程过程中，当我们对表中的数据列不知道是何种基本数据类型的时候，通常是使用%type来作为一个简单变量的一个声明过程。声明过程中就使用该列的列名来当做数据类型的声明就可以了。示例：--声明一个%type的引用变量V_sex表名.列名%type；/**该PL/SQL用来处理一个员工信息的获取*作者：kenny*日期：2011/11/14*/declare--声明变量，不用赋初始值v_enameemp.ename%type;v_jobemp.job%type;v_salemp.sal%type; v_commemp.comm%type;begin--使用selectinto语句给变量赋值--在未使用游标之前，只能处理单行数据的返回&标示接收用户输入的占位符selectename,job,sal,commintov_ename,v_job,v_sal,v_commfromempwhereempno=&empno;--对变量的值进行输出dbms_output.put_line('员工姓名：'||v_ename);dbms_output.put_line('员工工作：'||v_job);dbms_output.put_line('员工工资：'||v_sal);dbms_output.put_line('员工补贴：'||nvl(v_comm,0));end;1.%rowtype跟%type一样，%rowtype也是用来处理数据类型引用的。它们不同的地方是：%type处理的是数据表中一列数据的数据类型的引用%rowtype处理的是数据表中一行数据的数据类型的引用在日常开中，通常情况下面使用%rowtype来处理要返回某张表的所有列数据的时候。示例：--声明一个%rowtype的引用变量V_empinfoemp%rowtype;--该变量里面包含了emp中得所有字段--使用变量来取值的时候，需要使用变量加上“.“列名来提取数据V_empinfo.ename；/**该PL/SQL用来获取一个员工的所有信息。*作者：kenny*日期：2011/11/14*/declare--声明一个%rowtype的变量，接收emp表中一行数据v_empinfoemp%rowtype;begin--获取员工表的所有信息并将该信息赋予该变量select*intov_empinfofromempwhereempno=&empno;--输出 dbms_output.put_line('员工姓名：'||v_empinfo.ename);dbms_output.put_line('员工工作：'||v_empinfo.job);dbms_output.put_line('员工工资：'||v_empinfo.sal);dbms_output.put_line('员工补贴：'||nvl(v_empinfo.comm,0));end;1.Record定义：record是自定义数据库类型在日常开发中，我们会使用%type、%rowtype来处理我们不知道类型的数据列的数据提取，但是存在一个问题？--当我们使用%type的时候，如果处理的数据列非常多得时候，代码量非常之大，并且代码的重用性非常低，当我们使用%rowtype来简化操作的时候，会出现不能满足需求的情况。所以我们需要根据我们自己的需要自行定义一种满足业务需求的数据类型。Record就是一种能够根据业务逻辑来去定义的一个自定义数据类。表1.当我们需要提取2张表或者更多表中的一部分数据列的时候，我们就可以自己定义数据类型了。表2.使用record来定义数据类型：1、定义数据类型Typerecord_nameisrecord(标示符数据类型,标示符数据类型);Type--指的是我们现在在自己定义一个类型Record_name--自定义的数据类型的名称是什么，类似于Java里面一个类定义Isrecord--告诉PL/SQL的引擎这个是一个自定的record数据类型（）当中的标示符和数据类型--指的是该自定义数据类型当中包含了那些数据（数据指的是你要用来处理业务逻辑所需要的表中的列字段或者是简单变量）2、使用自定义数据类型来定义变量要想使用自定义的数据类型，就使用自定义数据类声明变量，变量的类型就可以是自己定义的数据类型了。变量名record_name;示例：/**该PL/SQL用来演示record的使用。 *作者：kenny*日期：2011/11/14*/declare--声明一个record数据类型typeempinfo_recordisrecord(v_enamemp.ename%type,v_jobemp.job%type,v_salemp.sal%type,v_mgremp.ename%type,v_dnamdept.dname%type);--使用record数据类型声明变量v_empinfoempinfo_record;v_empnonumber:=&empno;begin--给使用record数据类型声明的变量赋值selecte.ename,e.job,e.sal,m.ename,d.dnameintov_empinfofromempe,deptd,empmwheree.deptno=d.deptnoande.mgr=m.empnoande.empno=v_empno;--输出使用record数据类型的变量的值--注意：输出的时候使用的数据类型的属性是你自己定义的列名dbms_output.put_line('员工姓名：'||v_empinfo.v_enam);dbms_output.put_line('员工工作：'||v_empinfo.v_job);dbms_output.put_line('员工工资：'||v_empinfo.v_sal);dbms_output.put_line('员工上司：'||v_empinfo.v_mgr);dbms_output.put_line('部门名：'||v_empinfo.v_dnam);end;1.TableTable在形式上就类似Java中数组。它可以存放一组相同数据类型的变量。数组在使用中，我们需要通过数组下标来去设置或者取得数组里面的值。在使用过程中，需要注意是：Table需要在内部定义数据列的数据类型，该数据列是一个简单变量。并且还需要定义下标，该下标是自行定义的。与Java数组的区别是，Java数组的下标是从0开始，Table的下标可以是从任何的自定义的下标开始（例如：0、100，-100等）使用Table来定义数组类型：1、定义数组类型Typetable_nameistableof数据类型indexbybinary_integer;数据类型--指的是该table能够存放哪种数据类型的数据binary_integer--指的是该数据的下标定义，在使用它的时候才定义下标值 2、使用自定义数组类型来定义变量要想使用自定义的数组类型，就使用自定义数组类型声明变量，变量的类型就可以是自己定义的数组类型了。变量名table_name;示例：/**该PL/SQL用来演示table的使用。*作者：kenny*日期：2011/11/14*/declare--定义一个table数组typeempinfo_tableistableofemp.ename%typeindexbybinary_integer;--声明一个table数组的变量empinfoempinfo_table;begin--给table数组的变量赋值empinfo(-1):='jack';empinfo(1):='rose';selectenameintoempinfo(-100)fromempwhereempno=&empno;--输出table数组的变量的值dbms_output.put_line('员工姓名：'||empinfo(-100));dbms_output.put_line('员工姓名：'||empinfo(-1));dbms_output.put_line('员工姓名：'||empinfo(1));end;/**该PL/SQL用来演示table数组的使用。*作者：kenny*日期：2011/11/14*/DECLARE--定义一个table数组TYPEarrayISTABLEOFNUMBERINDEXBYBINARY_INTEGER;--声明一个table数组的变量firstnumberarray;--声明一个table数组的变量secondnumberarray; BEGIN--给table数组的变量赋值firstnumber(0):='123456';firstnumber(1):='888888';secondnumber(0):='222222';secondnumber(1):='666666';--循环输出FORiIN0..firstnumber.count-1LOOPDBMS_OUTPUT.PUT_LINE('i='||i||',firstnumber[0]='||firstnumber(i)||',secondnumber='||secondnumber(i));ENDLOOP;END;练习：1.利用%type，统计某位员工的年薪和上交的个人所得税。（个人所得税是0.03）2.利用%type，输出员工的直属上司姓名、部门名和所在地。3.利用%type，输出员工的相关信息，包括：姓名，工作，薪水，补贴，部门名，工资等级。4.利用record自定数据类型，输出员工的相关信息，包括：姓名，薪水，为公司工作月份，年薪，个人所得税，部门名，所在地等。作业：使用4种复合类型，写出各种复合类型的使用示例。每种写2个示例。PL/SQL编程流程控制篇PL/SQL中支持的流程控制语句有：1、条件控制·If用作判断条件的时候，使用if语法：If表达式thenEndif；表达式指的是：一个可以返回一个boolean值的条件运算，在使用中条件表达式的结果为：trueorfalse示例：declare--声明变量 v_salemp.sal%type;begin--给变量赋值selectsalintov_salfromempwhereempno=&empno;--判断条件ifv_sal<800then--当条件满足情况下，输出一下语句dbms_output.put_line('工资太低了，没有办法生活下去了。');endif;end;·Ifelsedeclare--声明变量v_salemp.sal%type;begin--给变量赋值selectsalintov_salfromempwhereempno=&empno;--判断条件ifv_sal<800then--当条件满足情况下，输出一下语句dbms_output.put_line('工资太低了，没有办法生活下去了。');elsedbms_output.put_line('我很满意我现在的工资，哈哈');endif;end;·Ifelsifelse·CaseCase是当我们有多个选择条件的时候，要使用case进行条件的匹配。语法：CaseWhen条件表达式thenWhen条件表达式thenElse--else是必须有的条件Endcase； 示例：declare--声明变量v_salemp.sal%type;begin--给变量赋值selectsalintov_salfromempwhereempno=&empno;--判断条件casewhenv_sal<800then--当条件满足情况下，输出一下语句dbms_output.put_line('工资太低了，没有办法生活下去了。');elsedbms_output.put_line('我很满意我现在的工资，哈哈');endcase;end;1、循环控制·简单循环（loop）Loop在PL/SQL里面就是循环，它执行的条件是无限循环。如果说需要有退出的话，需要使用exit，还需要给出退出的条件when条件表达式。简单循环实际上就是不管什么样的条件都会先执行一次循环，然后再做条件的判断是否继续执行下一次循环。如果没有条件，会执行死循环。语法：LoopExitwhen条件表达式；Endloop；示例：--100以内的数字累加declarev_inumber:=0;v_sumnumber:=0;beginloop--进行数字的累加运算v_sum:=v_sum+v_i;--进行循环变量的累加v_i:=v_i+1;--给出退出循环的条件exitwhenv_i>100;endloop;--在循环之外输出结果 dbms_output.put_line(v_sum);end;·While循环先执行条件判断，然后再执行循环体。语法：While条件表达式loopEndloop；示例：--计算100以内的数字累加declarev_inumber:=1;v_sumnumber:=0;beginwhilev_i<=100loop--进行数字的累加运算v_sum:=v_sum+v_i;--进行循环变量的累加v_i:=v_i+1;endloop;--在循环之外输出结果dbms_output.put_line(v_sum);end;·For循环已经知道要循环的次数，然后根据循环的次数来执行循环体。语法：For循环变量in开始值..结束值loopEndloop；循环变量---指的是需要用来作为循环条件的变量。注意：该循环变量可以不用声明。In---指的是循环的次数所在的区间或者范围。开始值..结束值---指的是循环的次序的区间值。示例：--计算100以内的数字累加。declarev_sumnumber:=0;beginforiin1..100loop --进行数字的累加运算v_sum:=v_sum+i;endloop;--在循环之外输出结果dbms_output.put_line(v_sum);end;1、顺序控制Go标签----不作为讲解重点练习：1.利用流程控制完成以下功能：l打印99乘法表l计算100~110之间的素数l打印三角形、等腰三角形、空心菱形、空心正方形l完成一个可以计算长方形、三角形，圆形面积和周长的工具。PL/SQL编程异常处理篇什么是异常？在程序运行过程中，发生的错误（指的是不能继续将程序执行下去的情况），就是异常。注意：错误，是指的在编写程序的时候出现的一种不能让程序编译的。错误的情况可以是：语法错误，资源获取的错误等等。。。引申：程序当中如何对异常进行处理？·要让用户获得一个好的交互方式·要让用户获得一个处理异常的途径基于以上的需求，我们对程序当中出现的异常要进行归类的处理，通常情况下，要将异常处理部分视为程序中最重要的部分之一。System系统异常异常业务异常Application系统异常：·是程序在运行过程中发生的（读取文件，读取图片资源等）·程序在执行任务的过程（访问数据库，访问外部系统资源等等） 业务异常：·根据系统需求，要求有业务上的提示异常·当用户没有按照系统的要求进行操作系统的时候例如：登陆的时候（用户名不对，密码不正确）注意：异常是不能“吃”的。Try{}catch（）{什么都没做。}处理的方式：·传播----抛出异常，让上层进行处理。·处理掉（记录日志、回退操作。。。。）PL/SQL支持的异常有哪些？1.系统预定义异常Oracle系统给开发人员内部定义好的异常处理部分，能够满足基本需求。2.系统非预定义异常Oracle系统提供的，跟系统预定义异常不同的是，系统非预定义异常的名称由用户去决定，再跟Oracle异常系统关联。3.自定义异常在系统预订义异常不能满足需求的情况的时候，就可以采用自己定义异常处理来处理相关性的异常。PL/SQL异常处理部分语法：ExceptionWhen异常名称thenWhen异常名称thenWhenothersthen----othens指的是所有的异常都被捕捉End；Oracle提供的常用的系统预定义异常：异常错误名称错误代码描述access_into_null-6530当开发对象类型应用时，在引用对象属性之前，必须首先初始化对象。如果试图给一个没有初始化的对象属性赋值，就会引发该异常错误case_not_found-6592在CASE语句中没有包含必需的WHERE子句，并且没有包含ELSE子句cursor_alread_open-6511试图打开一个已经打开的游标。一个游标在被重复打开之前必须关闭。一个游标FOR循环会自动打开所涉及的游标，所以在游标循环中不能打开游标。 dup_val_on_index-1向有唯一性索引约束的列插入重复值invalid_cursor-1001试图执行一个无效的游标操作invalid_number-1722将字符串转换为数字时失败。在过程性语句中，将引发VALUE_ERROR错误login_denied-1017用无效的用户名或口令登录ORACLEno_data_found+100SELECTINTO语句没有返回任何行，或者程序引用一个嵌套表中已经被删除的元素，或索引表中一个没有被初始化的元素not_logged_on-01012在没有登录ORACLE数据的情况下，访问数据库program_error-6501ORACLE内在错误，通常是由PL/SQL本身造成的，这种情况下应该通知ORACLE公司的技术部门storage_error-6500PL/SQL程序在运行时内存不够或者内存有问题timeout_on_resource-51ORACLE在等待资源时发生超时现象too_many_rows-1422SELECTINTO语句返回多行value_error-6502发生了一个算法、转换、截断或者大小约束错误。如果在一个SQL语句中发生这些错误，则会引发INVALID_ERROR错误zero_divide-1476发生被0除的错误示例：begininsertintodeptvalues(40,'aaaa','aaaaaa');exception--捕获当前发生的系统异常，进行相关的处理whendup_val_on_indexthendbms_output.put_line('发生异常了。');end;Oracle提供的非预定义异常：系统异常不够用的时候，就采用非预定义异常。非预定义的异常需要几个元素：异常名称--是由开发人员定义异常编号--是由oracle系统定义好的要使用预定义异常需要通过以下三步：①定义异常名称②建立异常名称和oracle异常关系③处理异常 语法：progmaexception_init(exception_name,exception_number)注意：伪过程：存在oracle系统中能够提供特殊的功能的存储过程。伪列：Sqlcode（错误代码）、Sqlerrm（错误堆栈信息）示例：declare--定义异常名称e_null_errorexception;--建立于系统异常之间的关系pragmaexception_init(e_null_error,-1400);begininsertintodeptvalues(null,'demo1','bj1');exception--处理该异常whene_null_errorthendbms_output.put_line('不能向部门编号列插入null');end;自定义异常跟Oracle的异常没有任何关系，是由使用者自己定义，并且自己引发，然后处理的一种异常。要使用自定义异常需要通过以下几步：①定义异常名称②在业务逻辑处理部分引发异常③在异常处理部分处理异常语法：异常名称exception；Raise异常名称；ExceptionWhen异常名称thenEnd；示例：declarev_salemp.sal%type;--自定义异常异常的名称error_salexception;beginselectsalintov_salfromempwhereempno=&empno;ifv_sal>250then --根据业务需要抛出相应的异常raiseerror_sal;endif;exception--捕捉异常进行相应的处理whenerror_salthendbms_output.put_line('工资太低了');whentoo_many_rowsthendbms_output.put_line('请用游标');end;自定义带文本消息的异常同自定义异常不同的地方是，在使用的时候，不用Exception来定义一个异常名，直接使用伪过程Raise_application_error()直接抛出错误信息。在日常开发中，当我们碰到需要提示用户进行下一步操作的业务逻辑异常的时候，就可以用自定义带文本消息的异常提示。语法：Raise_application_error(error_code,message[,ture|false]·Error_code是自定义的错误代码（-2000~-20999）之间·Messagebox是自定义错误消息·True将自定义错误消息添加到错误消息栈中示例：declarev_salemp.sal%type;beginselectsalintov_salfromempwhereempno=&empno;ifv_sal>250then--将带有文本消息的异常抛出raise_application_error('-20500','出现异常了，工资怎么这么高啊！');endif;end;注意:在日常的PL/SQL编程开发过程中，经常会使用的异常相关的组件：①Sqlcode、sqlerrm②自定义异常、带文本消息的自定义异常③系统预定义异常练习：1、使用异常和流程控制，写一个ATM机的完整版。 PL/SQL编程游标篇什么是游标？游标实际上是oracle数据库里面的一块命名内存区域，主要负责用户在执行SELECT,INSERT,DELETE,UPDATE语句的时候数据地址的指向。能够快速的返回数据集。游标的分类：①显示游标----用户定义，用处处理多行数据集。②隐式游标----当用户在执行DML语句的时候自动创建的③Ref游标----用来处理动态SQL结果集的游标的使用：显示游标的使用过程：定义游标的语法：Cousorcousor_nameissqlquery;打开游标：Opencousor_name;提取游标中的数据Fetchcousor_nameinto变量;关闭游标：Closecousor_name;Fetchinto----用来提取数据行示例：declare --定义一个现实游标cursoremp_curisselect*fromemp;--定义一个变量empinfoemp%rowtype;begin--打开游标openemp_cur;--提取游标中的一行数据fetchemp_curintoempinfo;dbms_output.put_Line(empinfo.ename);dbms_output.put_Line(empinfo.job);dbms_output.put_Line(empinfo.sal);--关闭游标closeemp_cur;end;游标属性：游标提供了属性，用来判断游标目前所处状态和游标的一些相关信息。%isopen判断游标是否处于打开状态（true|false）%found、%notfound查看游标中是否还有数据行或者是没有数据行（true|false）%rowcount返回游标中数据行的当前行数这些属性可以帮助在使用显示游标的时候去判断游标的打开和关闭状态，存在数据和不存在数据的状态，还可以取得当前游标数据集中的行数。示例：--输出游标中的结果集行数declare--定义一个现实游标cursoremp_curisselect*fromemp;--定义一个变量empinfoemp%rowtype;begin--打开游标openemp_cur;--提取游标中的一行数据fetchemp_curintoempinfo;dbms_output.put_Line('当前取得的数据集是游标中的第'||emp_cur%rowcount||'行');dbms_output.put_Line(empinfo.ename);dbms_output.put_Line(empinfo.job); dbms_output.put_Line(empinfo.sal);--关闭游标closeemp_cur;end;declare--定义一个现实游标cursoremp_curisselect*fromemp;--定义一个变量empinfoemp%rowtype;begin--打开游标openemp_cur;loop--提取游标中的一行数据fetchemp_curintoempinfo;--使用游标的%notfound属性exitwhenemp_cur%notfound;dbms_output.put_Line('当前取得的数据集是游标中的第'||emp_cur%rowcount||'行');dbms_output.put_Line(empinfo.ename);dbms_output.put_Line(empinfo.job);dbms_output.put_Line(empinfo.sal);endloop;--关闭游标closeemp_cur;end;练习：1、将员工的信息使用游标输出。2、输出所有员工的年薪以及到公司工作的月份数。3、输出员工的姓名，上司名称，年薪，工资级别，部门名和所在地。参数游标：我们在开发过程中，想要限制我们返回的结果集，或者是我需要根据现有的查询结果集去查找下一个结果的情况的时候，我们需要使用一种带有参数的游标。例如：级联的菜单、联动效果的时候使用参数游标的时候，需要在游标声明的时候，传递一个参数。语法：Cursorcursor_name(参数)issqlquery;---该查询语句可以使用传入进来的参数作为条件示例： declare--声明一个带参数的游标cursorenamebyA_cur(keyvaluevarchar2)isselect*fromempwhereenamelike'%'||keyvalue||'%';empinfoemp%rowtype;begin--打开带参数的游标并传参数值openenamebyA_cur('A');loopfetchenamebyA_curintoempinfo;exitwhenenamebyA_cur%notfound;dbms_output.put_line(empinfo.ename);endloop;closeenamebyA_cur;end;隐式游标隐式游标是在执行INSERT、update、delete的时候，自动创建一种游标，该游标可以去判断所执行的SQL语句的结果或者是状态。SQL%foundSQL%NOFOUND该游标可以判断执行的SQL语句属于何种状态SQL%ROWCOUNT示例：begininsertintodeptvalues(50,'faaafsd','adfasd');--使用隐式游标判断执行的SQL结果ifSQL%FOUNDthen--使用隐式游标检查影响的数据库行数dbms_output.put_line('影响的行数为：'||SQL%ROWCOUNT);dbms_output.put_line('执行成功！');commit;endif;end;循环游标为了简化我们操作游标的过程，所以引入了循环游标，它能简化open，fetch，close等操作，还能够快速的提取游标中的数据行。语法：FORINLOOPENDLOOP; 示例：declare--定义一个现实游标cursoremp_curisselect*fromemp;--定义一个变量empinfoemp%rowtype;begin--使用循环游标提取数据行forempinfoinemp_curloopdbms_output.put_Line('当前取得的数据集是游标中的第'||emp_cur%rowcount||'行');dbms_output.put_Line(empinfo.ename);dbms_output.put_Line(empinfo.job);dbms_output.put_Line(empinfo.sal);endloop;end;在日常开发过程中可以使用循环游标来进行数据的更新。更新游标的时候需要使用锁，将要更新的数据行锁定。FORUPDATE；更新的时候需要使用CURRTEN进行条件的设置。语法：CURSORISSELECTstatementFORUPDATE;UPDATESETWHERECURRENTOF示例：----利用游标更新所有的行declarev_emp_recemp%rowtype;--利用游标锁定更新数据集cursorc1isselect*fromempforupdate;beginopenc1;loopfetchc1intov_emp_rec;exitwhenc1%notfound;ifv_emp_rec.deptno>=30then --利用游标去更新数据行updateempsetcomm=800--修改数据wherecurrentofc1;endif;endloop;commit;--提交已经修改的数据closec1;end;----利用更新游标去更新限制的列数据DECLAREnew_priceNUMBER;--利用游标锁定更新数据集，并限制更新的列CURSORcur_toyISSELECTtoypriceFROMtoysWHEREtoyprice<100FORUPDATEOFtoyprice;BEGINOPENcur_toy;LOOPFETCHcur_toyINTOnew_price;EXITWHENcur_toy%NOTFOUND;--利用游标去更新数据行，只能更新被限制的列UPDATEtoysSETtoyprice=1.1*new_priceWHERECURRENTOFcur_toy;ENDLOOP;CLOSEcur_toy;COMMIT;END;REF游标在日常开发中，我们需要动态的去决定要执行的SQL语句是什么，所以我们需要使用游标来当成一个变量使用。当游标被当成变量的时候，需要使用游标类型来去声明游标变量：语法：Typecursor_typeisrefcursorreturndatatype;Datatype----指的是游标的数据集返回类型返回类型为两种：强类型、弱类型强类型的时候需要用return来制定返回的类型是什么，弱类型不用。示例：--声明强类型游标类型和变量TYPEmy_curtypeISREFCURSORRETURNstud_det%ROWTYPE;order_curmy_curtype; ---声明弱类型游标类型和变量TYPEmy_ctypeISREFCURSOR;stud_curmy_ctype;示例：declare--声明强类型游标typedept_typeisrefcursorreturndept%rowtype;--游标变量dept_curdept_type;--声明变量，接收游标返回值deptinfodept_cur%rowtype;beginopendept_curforselect*fromdept;loopfetchdept_curintodeptinfo;exitwhendept_cur%notfound;dbms_output.put_line(deptinfo.dname);endloop;closedept_cur;end;declare--声明弱类型游标typedept_typeisrefcursor;--游标变量dept_curdept_type;--声明变量，接收游标返回值deptinfodept_cur%rowtype;beginopendept_curforselect*fromdept;loopfetchdept_curintodeptinfo;exitwhendept_cur%notfound;dbms_output.put_line(deptinfo.dname);endloop;closedept_cur;end;动态的构建SQL语句的游标语法：Opencursor_nameforsqlstringusing参数； 示例：DECLAREr_empemp%ROWTYPE;TYPEc_typeISREFCURSOR;curc_type;p_salaryNUMBER;BEGINp_salary:=2500;--使用动态方式给游标挂载参数OPENcurFOR'select*fromempwheresal>:1--指的是第一个参数orderbysaldesc'USINGp_salary;DBMS_OUTPUT.PUT_LINE('薪水大于'||p_salary||'的员工有：');LOOPFETCHcurINTOr_emp;EXITWHENcur%NOTFOUND;DBMS_OUTPUT.PUT_LINE('编号：'||r_emp.empno||'姓名：'||r_emp.ename||'薪水：'||r_emp.sal);ENDLOOP;CLOSEcur;END;注意：如果有多个参数，就是：2、：3，在using的时候用逗号隔开练习：PL/SQL编程存储过程、函数篇什么是存储过程？从本质上来看，过程就是命名的PL/SQL程序块，它可以被赋予参数并存储在数据库中，然后由另外一个应用或PL/SQL例程调用。存储过程是指用于特定操作的PL/SQL块，是由流控制和SQL语句书写的过程。存储过程经编译和SQL优化后存储在数据库服务器中，使用时只需要调用即可。在Oracle系统中，多个有联系的存储过程，可以组合在一起构成包。语法：[create[orreplace]]procedureprocedure_name[(parameter[,parameter]…)][authid{definer|current_schema}]{is|as}[pragmaautonomous_transaction;][local_declarations]begin executablestatements[exceptionexceptionhandlers]end[procedure_name];存储过程的参数类型：①输入参数in②输出参数out③输入输出参数inout使用参数的时候，有三种方式：①使用位置法参数所在的位置来进行参数的传递。示例：begin--Calltheprocedurehello_proc(‘helloproc’);end;②使用名称法参数的名称来进行参数的传递。示例：begin--Calltheprocedurehello_proc(v_string=>:v_string);end;③使用混合模式混合模式就是位置法和名称法的混合使用。示例：begin--Calltheprocedurehello_proc(v_string=>:v_string,’9527’);end;执行存储过程：方式一：Sqlplus里面执行使用exec来调用存储过程。如果要使用变量的话，使用variable来声明变量，并且可以赋值。示例：variablea1number;variablea2number; execute:a1:=get_salary_by_deptno(10,:a2);方式二：PL/SQLDEVEPLPER中调用：示例：Beginhello_proc(v_string=>:v_string,’9527’);End;练习：1.在存储过程中使用in，out，inout参数。2.在存储过程中使用记录record。3.在存储过程中使用数组table。4.在存储过程中使用事务进行操作。5.利用所学的PL/SQL知识，完成对SCOTT.EMP表的CRUD操作。什么是函数？函数就类似于Java中方法，必须有返回值。在开发过程中，函数通常使用的地方时，当你的业务需要有值返回给用户的时候就可以使用函数。例如：判断用户在数据库中是否有数据的时候。语法：[create[orreplace]]functionfunction_name[(parameter[,parameter]…)]returndatatype[authid{definer|current_schema}][parallel_enable][deterministic]{is|as}[pipelined[usingimplementation_type]][aggregate[updatevalue]{withexternalcontext}usingimplementation_type][pragmaautonomous_transaction;][localdeclarations]beginexecutablestatements[exceptionexceptionhandlers]end[name];示例：---返回该表中的数据行数createorreplacefunctionempcount_fun(empinfooutemp%rowtype) --定义返回类型returnnumberis----可以定义局部变量v_countnumber;cursoremp_curisselect*fromemp;beginforempinfoinemp_curloopv_count:=emp_cur%rowcount;dbms_output.put_line(empinfo.ename);endloop;--为函数返回值returnv_count;endempcount_fun;----调用函数declare--Non-scalarparametersrequireadditionalprocessingempinfoemp%rowtype;resultnumber;begin--Callthefunctionresult:=empcount_fun(empinfo=>empinfo);dbms_output.put_line(to_char(result));end;练习：1、用函数、存储过程模拟一个登陆过程。PL/SQL编程包、触发器篇什么是包？用于逻辑组合相关的PL/SQL类型、PL/SQL项和PL/SQL子程序。通过使用包，可以简化应用设计，提高应用性能，还可以实现信息的隐藏、子程序的重载等功能包中可以包含的结构： 包的结构包括三个部分：包头、包体、函数和存储过程定义包头语法：CREATE[ORREPLACE]PACKAGEpackage_nameIS|AS[PRAGMASERIALLY_REUSABLE;]公有数据类型定义公有变量声明公有常量声明公有异常错误声明公有游标,函数,过程声明ENDpackage_name;定义包体语法：CREATE[ORREPLACE]PACKAGEBODYpackage_nameIS|AS[PRAGMASERIALLY_REUSABLE;]公有数据类型定义公有变量声明公有常量声明公有异常错误声明公有游标,函数,过程声明ENDpackage_name;注意：创建包头和包体的时候，包体和包头的名字、包头中的函数和存储过程的名称必须相同。如果有局部使用的变量或者一些变量的初始化过程可以在包体中的begin和end中执行。示例：--创建包头createorreplacepackageuser_pkgis--定义一个游标类型typeemp_curisrefcursor;--定义一个记录类型 typeempinfo_recordisrecord(v_empinfoemp%rowtype);--定义存储过程procedureuser_proc(v_unameinuserinfo.uname%type,v_upwdinuserinfo.upwd%type,--定义out参数，使用记录类型为数据类型empinfooutempinfo_record,--定义out参数，使用游标类型为数据类型vempinfooutemp_cur);--定义函数functionislogin_fun(v_unameinuserinfo.uname%type,v_upwdinuserinfo.upwd%type)returnboolean;enduser_pkg;--创建包体createorreplacepackagebodyuser_pkgis--实现存储过程procedureuser_proc(v_unameinuserinfo.uname%type,v_upwdinuserinfo.upwd%type,empinfooutempinfo_record,vempinfooutemp_cur)is--定义局部变量flagboolean:=false;--定义局部变量v_emp_infoemp%rowtype;begin--打开游标，并从游标中提取数据行openvempinfoforselect*fromemp;loopfetchvempinfointov_emp_info;exitwhenvempinfo%notfound;dbms_output.put_Line(vempinfo%rowcount);dbms_output.put_Line(v_emp_info.ename);endloop;closevempinfo;--查询记录并将记录赋予给记录类型变量select*intoempinfo.v_empinfofromempwhereempno=7788;dbms_output.put_line(empinfo.v_empinfo.ename);--调用包中的函数flag:=islogin_fun(v_uname,v_upwd);ifflagthendbms_output.put_line('用户成功登陆！');elsedbms_output.put_line('用户登陆失败！');endif;enduser_proc;--实现函数 functionislogin_fun(v_unameinuserinfo.uname%type,v_upwdinuserinfo.upwd%type)returnbooleanisflagboolean:=false;v_countnumber:=0;beginselectcount(*)intov_countfromuserinfowhereuname=v_unameandupwd=v_upwd;ifv_count>0thenflag:=true;returnflag;elsereturnflag;endif;endislogin_fun;begin--包体执行程序开始的地方dbms_output.put_Line('该程序包用来演示package的应用。');enduser_pkg;--调用包declare--调用保重的记录类型声明变量v_empuser_pkg.empinfo_record;v_empinfouser_pkg.emp_cur;begin--调用包中的存储过程user_pkg.user_proc('jack','jack123',v_emp,v_empinfo);end;练习：1、使用包完成对SCOTT.EMP表的CRUD操作。2、写一个输出图形的包，要求可以输出图形：三角形、菱形、正方形等什么是触发器？触发器是一个数据库中的独立对象，由用户创建，数据库进行隐式调用。触发器由四个部分构成：·触发事件·在何种情况下触发TRIGGER·触发时间·该触发器是在触发时间发生之前（BEFORE）还是之后（AFTER）触发·触发器本身·触发TRIGGER被触发之后的目的和意图，触发器要做的事情·触发频率·触发器内定义的动作被执行的次数 触发器的分类：触发器的工作原理：BEFORE触发器工作原理 AFTER触发器工作原理触发器创建语法：示例：CREATE[ORREPLACE]TRIGGERtrigger_name{BEFORE|AFTER}{INSERT|DELETE|UPDATE[OFcolumn[,column…]]}ON[schema.]table_name[REFERENCING{OLD[AS]old|NEW[AS]new|PARENTasparent}][FOREACHROW][WHENcondition]trigger_body;典型应用示例1：---利用触发器向表中插入字段值--创建日志表LOGINFOcreatetableloginfo( log_idvarchar2(10),----log-sysdate+序列号log_infovarchar2(200),log_datetimestamp,----当前日期usernamevarchar2(20),log_operationvarchar2(20));--修改表中的字段ALTERTABLEloginfoMODIFYLOG_IDVARCHAR2(20);--创建序列，用于生成日志编号createsequencelogid_seqincrementby1startwith1000nomaxvaluenocyclenocache;--为LOGINFO表创建触发器，该出发能够完成对日志表中编号列值的自动生成createorreplacetriggerloginfo_tribeforeinsertonloginfo--行级触发器foreachrowbegin---使用伪列来给表中的字段生成插入值select'LOG-'||TO_CHAR(SYSDATE,'YYYYMMDD')||logid_seq.NextvalINTO:NEW.log_idFROMDUAL;end;--引发触发器工作INSERTINTOLOGINFO(log_info,log_date,username,log_operation)VALUES('AAAAA',SYSDATE,'SCOTT','AAAAAAAAAAA');典型应用示例2：----利用触发器记录操作日志 --创建一张用户信息表createtableuserinfo(user_idvarchar2(20),user_namevarchar2(20),user_pwdvarchar2(20),user_emailvarchar2(20),user_qqvarchar2(20));--创建序列，用于生成日志编号createsequenceuserinfo_seqincrementby1startwith1000nomaxvaluenocyclenocache;---创建触发器，用于对用户表USERINFO的更新操作记录日志createorreplacetriggeruserinfo_triafterinsertorupdateordeleteonuserinfoforeachrowbegincasewheninsertingthenINSERTINTOLOGINFO(log_info,log_date,username,log_operation)VALUES('AAAAA',SYSDATE,'SCOTT','AAAAAAAAAAA');whenupdatingthenINSERTINTOLOGINFO(log_info,log_date,username,log_operation)VALUES('AAAAA',SYSDATE,'SCOTT','AAAAAAAAAAA');whendeletingthenINSERTINTOLOGINFO(log_info,log_date, username,log_operation)VALUES('AAAAA',SYSDATE,'SCOTT','AAAAAAAAAAA');endcase;end;--验证触发器功能begininsertintouserinfovalues(userinfo_seq.nextval,'jack','jack123','jack@163.com','6464674');ifSQL%FOUNDTHENcommit;elserollback;endif;end;练习：