数据数据库培培训PLSQL培训内容PL/SQL程序结构基本语法要素流程控制事务处理游标异常处理存储过程和函数程序包触发器2PL/SQL程序结构PL/SQL：过程化SQL语言PL/SQL块语法DECLARE-declarationstatementsBEGIN-executablestatementsEXCEPTION-exceptionstatementsEND3基本语法要素常量变量符合数据类型变量表达式函数ODS变量命名规范4常量语句格式：常量名常量名 CONSTANT 类型标识符类型标识符 not null := 值值 ; 例：avc_acct_idCONSTANTVARCHAR2(5)NOTNULL:=AP001;5变量语句格式变量名变量名 类型标识符类型标识符 not null := 值值 ;例：avc_serv_idVARCHAR2(5)NOTNULL:=SV001;基本数据类型numberintchar()varchar()varchar2()longdateboolean:turefalsenull6复合数据类型变量使用type%定义变量avc_nodept.deptno%type;定义记录类型变量使用rowtype%定义变量一维表类型变量多维表类型变量7表达式算术表达式逻辑表达式字符表达式关系表达式8函数数字函数字符函数转换函数日期函数常规函数9ODS变量命名规范createorreplaceprocedurep_tf_acct_income_mon(/*统计分析_收入情况（月）模块名称：p_tf_acct_income_mon生成周期：按月执行数据来源：FAS.TF_ACCT_ITEM数据目标：统计分析_收入情况（月）（TF_ACCT_INCOME_MON）*/avc_cycleidvarchar2,-数据周期avc_lastcycleidvarchar2,-上一次成功数据周期an_returnoutnumber,-返回值(0:表示成功-1:表示失败)an_syserroutnumber,-系统错误号an_recordoutnumber,-总记录数avc_syserrtextoutvarchar2-系统错误文本)isn_pointvarchar2(10);vc_sqlvarchar2(4000);begin.endp_tf_acct_income_mon;10流程控制条件控制条件控制循环控制循环控制11条件控制条件控制If 条件条件1 then 语句段语句段1;Elsif 条件条件2 then if (条件条件4) then 语句段语句段2; end if;Else 语句段语句段3;End if ;12循环控制循环控制Loop循环循环While 循环循环For 循环循环13Loop循环循环Loop 循环语句循环语句1 If 条件语句条件语句 then exit； else 语句语句2 end if;End loop;14Loop循环循环Loop 循环语句循环语句1 语句语句2exit when 条件语句条件语句 ; End loop;15For 循环循环 for 循环变量循环变量 in reverse 循环上届循环上届.下届循环下届循环 loop 循环处理语句；循环处理语句； End loop ;16事务处理Commit命令用setautoonoff;来打开，关闭自动提交Rollback命令Savepoint命令17游标游标的作用隐式游标显示游标游标属性引用游标/动态游标18游标的作用从数据库中提取出数据，以临时表的形式放在内存中。初始指向首记录，利用fetch移动指针，对游标中的数据进行处理，然后写到结果表中。19显示游标select语句上使用显式游标，明确能访问结果集FOR循环游标(常用的一种游标)转换函数fetch游标参数游标20FOR循环游标定义游标定义游标变量使用for循环来使用这个游标前向游标只能往一个方向走效率很高declare-类型定义cursorccisselectempno,ename,job,salfromempwherejob=MANAGER;-定义一个游标变量ccreccc%rowtype;begin-for循环forccrecinccloopdbms_output.put_line(ccrec.empno|-|ccrec.ename|-|ccrec.job|-|ccrec.sal);endloop;end;21fetch游标使用的时候必须要明确的打开和关闭declare-类型定义cursorccisselectempno,ename,job,salfromempwherejob=MANAGER;-定义一个游标变量ccreccc%rowtype;begin-打开游标opencc;-loop循环loop-提取一行数据到ccrec中fetchccintoccrec;-判断是否提取到值，没取到值就退出-取到值cc%notfound是false-取不到值cc%notfound是trueexitwhencc%notfound;dbms_output.put_line(ccrec.empno|-|ccrec.ename|-|ccrec.job|-|ccrec.sal);endloop;-关闭closecc;end;22游标属性游标的属性4种%notfoundfetch是否提到数据没有true提到false%foundfetch是否提到数据有true没提到false%rowcount已经取出的记录的条数%isopen布尔值游标是否打开declare-类型定义cursorccisselectempno,ename,job,salfromempwherejob=MANAGER;-定义一个游标变量ccreccc%rowtype;begin-打开游标opencc;-loop循环loop-提取一行数据到ccrec中fetchccintoccrec;-判断是否提取到值，没取到值就退出-取到值cc%notfound是false-取不到值cc%notfound是trueexitwhen(cc%notfoundorcc%rowcount=3);dbms_output.put_line(cc%rowcount|-|ccrec.empno|-|ccrec.ename|-|ccrec.job|-|ccrec.sal);endloop;-关闭closecc;end;23参数游标按部门编号的顺序输出部门经理的名字declare-部门cursorc1isselectdeptnofromdept;-参数游标c2,定义参数的时候-只能指定类型,不能指定长度-参数只能出现在select语句=号的右侧cursorc2(nonumber,pjobvarchar2)isselectemp.*fromempwheredeptno=noandjob=pjob;c1recc1%rowtype;c2recc2%rowtype;-定义变量的时候要指定长度v_jobvarchar2(20);begin-部门forc1recinc1loop-参数在游标中使用forc2recinc2(c1rec.deptno,MANAGER)loopdbms_output.put_line(c1rec.deptno|-|c2rec.ename);endloop;endloop;end;24综合例子题目求购买的商品包括了顾客“Dennis”所购买商品的顾客（姓名）；createtablepurcase(productidnumber,customeridnumber)；createtablecustomer(customeridnumber,namevarchar(30)；思路：Dennis(A,B)别的顾客(A,B,C)(A,C)(B,C)C25declare-Dennis所购买的商品cursorcur_dennisisselectproductidfrompurcasewherecustomerid=(selectcustomeridfromcustomerwherename=Dennis);-除Dennis以外的每个顾客cursorcur_custisselectcustomeridfromcustomerwherenameDennis;-每个顾客购买的商品cursorcur_prod(idvarchar2)isselectproductidfrompurcasewherecustomerid=id;jnumber;inumber;rec_denniscur_dennis%rowtype;rec_custcur_cust%rowtype;rec_prodcur_prod%rowtype;avc_namevarchar2(10);begin-顾客循环forrec_custincur_custloopi:=0;j:=0;forrec_dennisincur_dennisloopi:=i+1;-每个顾客买的东西forrec_prodincur_prod(rec_cust.customerid)loopif(rec_prod.productid=rec_dennis.productid)thenj:=j+1;endif;endloop;endloop;if(i=j)thenselectnameintoavc_namefromcustomerwherecustomerid=rec_cust.customerid;DBMS_output.put_line(avc_name);endif;endloop;end;26隐式游标隐式游标也叫sql游标，是用来处理所有sql语句的环境区域指针；不能通过专门的语句来打开，PL/SQL隐式的打开sql游标处理完后自动关闭。单条sql语句所产生的结果集合用关键字SQL表示隐式游标4个属性%rowcount影响的记录的行数整数%found影响到了记录true%notfound没有影响到记录true%isopen是否打开布尔值永远是false多条sql语句隐式游标SQL永远指的是最后一条sql语句的结果主要使用在update和delete语句上27游标变量/动态游标select语句是动态的declare-定义一个类型(refcursor)弱类型typecurisrefcursor;-定义一个refcursor类型的变量curacur;c1recemp%rowtype;c2recdept%rowtype;beginDBMS_output.put_line(输出员工);opencuraforselect*fromemp;loopfetchcuraintoc1rec;exitwhencura%notfound;DBMS_output.put_line(c1rec.ename);endloop;DBMS_output.put_line(输出部门);opencuraforselect*fromdept;loopfetchcuraintoc2rec;exitwhencura%notfound;DBMS_output.put_line(c2rec.dname);endloop;closecura;end;28异常处理系统预定义的异常自定义异常声明：异常名EXCEPION；产生异常：raise语句异常处理29存储过程和函数没有名字的PL/SQL块(匿名)有名字的PL/SQL块(子程序-存储过程和函数)存储过程createorreplaceprocedurep1asbeginexceptionend;createorreplaceprocedurep_jdashellovarchar2(20);beginselectHelloWorldintohellofromdual;dbms_output.put_line(hello);end;执行存储过程的方法executep_jd;(SQL*PLUS中SQL)beginp_jd;end;30带参数的存储过程-输入参数in-不写in的参数都是输入参数-根据部门编号查员工姓名createorreplaceprocedurep_getemp(nonumber)ascursorc1isselect*fromempwheredeptno=no;c1recc1%rowtype;begin-no:=20;输入参数是不能赋值的forc1recinc1loopdbms_output.put_line(c1rec.ename);endloop;end;31带参数的存储过程-输出参数out-根据部门编号查出部门的平均工资,返回平均工资的值-in输入(在procedure中是不能赋值的)-out输出(在procedure中是能赋值的)-定义参数是不能指定长度的-定义变量是必须指定长度的createorreplaceprocedurep_getavgsal(nonumber,avgsaloutnumber)-no输入参数-avgsal输出参数asaavarchar2(10);-变量beginselectavg(sal)intoavgsalfromempwheredeptno=no;end;调用它只能使用PL/SQL块declareavnumber;beginp_getavgsal(10,av);dbms_output.put_line(平均工资:|round(av,2);end;32带参数的存储过程-一个参数同时可以输入,也可以输出-输入输出参数createorreplaceprocedurep_getavgsal(ninoutnumber)asbeginselectavg(sal)intonfromempwheredeptno=n;end;declareavnumber;beginav:=10;p_getavgsal(av);dbms_output.put_line(平均工资:|round(av,2);end;33带多个参数的存储过程-带多个参数的存储过程createorreplaceprocedurep_getM(nonumber,pjobvarchar2)as-参数游标c2,定义参数的时候-只能指定类型,不能指定长度-参数只能出现在select语句=号的右侧cursorc2(no1number,pjob1varchar2)isselect*fromempwheredeptno=no1andjob=pjob1;c2recc2%rowtype;-定义变量的时候要指定长度v_jobvarchar2(20);begin-参数在游标中使用forc2recinc2(no,pjob)loopdbms_output.put_line(c2rec.deptno|-|c2rec.ename);endloop;end;调用方法:executep_getm(10,MANAGER);-按位置-no=10,pjob=MANAGERexecutep_getm(pjob=MANAGER,no=10);-按参数的名字来传值34函数必须要有返回值只能返回一个值35函数例子-根据部门编号查出部门的平均工资,返回平均工资的值(利用函数)createorreplacefunctionf_getavgsal(nonumber)returnnumberasavgsalnumber(7,2);beginselectavg(sal)intoavgsalfromempwheredeptno=no;-返回值returnavgsal;end;36一个函数返回2个值-带输出参数-每个部门的平均工资和工资总额-一个函数返回2个值createorreplacefunctionf_getavgsal(nonumber,sumsaloutnumber)returnnumberasavgsalnumber(7,2);begin-平均工资selectavg(sal)intoavgsalfromempwheredeptno=no;-工资总额selectsum(sal)intosumsalfromempwheredeptno=no;-返回值returnavgsal;end;37函数的调用方法块调用declareaanumber;beginaa:=f_getavgsal(10);dbms_output.put_line(to_char(aa);end;SQL语句来调用(DML)selectf_getavgsal(10)fromdual;selectdeptno,f_getavgsal(deptno)fromdept;含有修改语句，select语句是无法调用createorreplacefunctionf1returnnumberasupdateempsetcomm=1000wherejob=CLERK;returnsql%rowcount;end;-select语句是无法调用它的,因为其中含有修改语句38程序包PACKAGE用途:模块化-公司的员工的管理1.增加一个员工2.员工离职包中的变量是全局变量返回结果集合39PACKAGE的例子createorreplacepackagetest_pack-包头asproceduregetname(enonumber,enmoutvarchar2);functionf_get_name(enonumber)returnvarchar2;end;createorreplacepackagebodytest_pack-包体asproceduregetname(enonumber,enmoutvarchar2)asbeginselectenameintoenmfromempwhereempno=eno;end;functionf_get_name(enonumber)returnvarchar2asnvarchar2(20);beginselectenameintonfromempwhereempno=eno;returnn;end;end;调用方法declareenmvarchar2(30);begintest_pack.getname(10,enm);end;40触发器用途:加强约束条件的-希望在表中插入的数据的日期大于系统时间Createtabletest(xhnumber(2)primarykey,hdatedatecheck(hdatesysdate);数据库无法建立的Createtabletest(xhnumber(2)primarykey,hdatedatecheck(hdateto_date(20050501,yyyymmdd);触发器实现41触发器建立触发器的语法createorreplacetriggerafter/beforeinsert/update/deleteonforeachrowbeginexceptionend;-触发器中的PL/SQL块(DML/tcl)可以写什么样的SQL语句呢？DML语句别的都不能写(COMMIT/rollback都不能写)after/before以后/以前insert/update/delete触发的SQL语句foreachrow行级-语句级sql语句和触发器的Pl/SQL形成一个整体的事务42触发器例子-希望在表中插入的数据的日期大于系统时间Createtabletest(xhnumber(2)primarykey,hdatedate);createorreplacetriggertr_testafterinsertontestforeachrowbeginif:new.hdate=sysdatethenraise_application_error(-20001,小于系统时间);endif;end;insertintotestvalues(1,to_date(20050401,yyyymmdd);insertintotestvalues(1,sysdate+1);43谢谢