PLSQL语法以及程序编写介绍

PL/SQL语法以及程序编写介绍,一、PL/SQL介绍PL/SQL是一种高级数据库程序设计语言，该语言专门用于在各种环境下对Oracle数据库进行访问。PL/SQL语言实现了将过程结构与OracleSQL的无缝集成，从而为用户提供了一种功能强大的结构化程序设计语言。,二、引入PL/SQL的原因PL/SQL语言可以将第四代语言的强大功能和灵活性与第三代语言的过程结构的优势融为一体。,三、PL/SQL内容PL/SQL是在SQL语言中扩充了面向过程语言中使用的程序结构，如：变量和类型（即可以予定义也可以由用户定义）控制语句（如IF-THEN-ELSE）和循环过程和函数对象类型和方法（PL/SQL8.0版本以上）,四、变量的声明在PL/SQL中声明变量与其他语言不太一样，它采用从右往左的方式声明。C语言声明变量方式：intv_id;PL/SQL声明变量方式：v_idnumber;五、变量的赋值不能用”=”应该用”:=”,即形式为v_id:=5;,六、数据类型PL/SQL主要用于数据库编程，所以其所有数据类型跟oracle数据库里的字段类型是一一对应的，常用的数据类型大体上有数字型、布尔型、字符型和日期型。数字型用来存储整型或者浮点型数值。范围为E-13010E125，其声明方式为：number（precision,scale）其中（precision,scale）是可选的，precision表示所有数字的个数，scale表示小数点右边数字的个数。,实际数据定义在oralce中的数据,7456123.89NUMBER7456123.89NUMBER(9)7456123.89NUMBER(9,2)7456123.89NUMBER(9,1)7456123.89NUMBER(6)7456123.89NUMBER(7,-2)7456123.89NUMBER(7,2),7456123.89,7456124,7456123.89,7456123.9,溢出,7456100,溢出,布尔型布尔型BOOLEAN，用于逻辑判断，只有TRUE,FALSE两种值。字符型主要说明字符类型的存储格式。主要包括char、varchar2和long等几种类型。CHAR：用于描述定长的字符型数据，长度=2000字节。如果实际值不够定义的长度，系统将以空格填充。,v_charchar(10):=abcde;Length(v_char)=,10,VARCHAR2：用于描述变长的字符型数据，长度=4000字节。它的声明方式如下VARCHAR2(L)，L为字符串长度，没有缺省值。,v_charvarchar2(10):=abcde;Length(v_char)=,5,NCHAR：来存储Unicode字符集的定长字符型数据，长度=1000字节。它的声明方式与CHAR相同。NVARCHAR2：用来存储Unicode字符集的变长字符型数据，长度=1000字节。它的声明方式与VARCHAR2相同。LONG：用来存储最大长度为2GB的变长字符数据。,日期型DATE：用于存储日期与时间信息。虽然日期信息也可以用字符型变量或者数字型变量来表示，但是DATE型变量有它特有的属性。每一个DATE型的数据都存储了以下信息：年、月、日、小时、分钟以秒。,TO_DATE函数TO_DATE函数可以将一个字符型变量按照程序员的要求转换成日期型变量。,假设有一DATE类型变量v_date，现在用to_date函数将v_date赋值为2006年4月6日,V_date:=to_date(2006-04-06,yyyy-mm-dd);,V_date:=to_date(2006-04-0610:10:10,yyyy-mm-ddhh24:mi:ss);,V_date:=to_date(20060406,yyyymmdd);,V_date:=to_date(2006/04/06,yyyy/mm/dd);,V_date:=to_date(20060406101010,yyyymmddhh24miss);,V_date:=to_date(2006/04/0610,10,10,yyyy/mm/ddhh24,mi,ss);,七、条件逻辑在PL/SQL中，可以使用命令if,else和elsif来控制可执行命令段的命令流。下面列出了可用的条件逻辑命令的格式（循环条件除外）格式1：ifthenelsifthenelseendif;,格式2：ifthenifthenendif;elseendif;总的说来除了格式上与C略有区别，其它都是一样的,例：判断通过映射关系，将邮编信息填入到i_addr_zipcode字段中去ifi_area_id=1theni_addr_zipcode:=325200;elsifi_area_id=2theni_addr_zipcode:=325400;elsifi_area_id=3theni_addr_zipcode:=325800;elsifi_area_id=4theni_addr_zipcode:=325100;elsifi_area_id=5theni_addr_zipcode:=325600;elsifi_area_id=6theni_addr_zipcode:=325700;elsifi_area_id=7theni_addr_zipcode:=325300;elsifi_area_id=8theni_addr_zipcode:=325500;elsei_addr_zipcode:=325000;endif;,八、循环在一个单独的PL/SQL代码块中可以使用循环处理多个记录。PL/SQL支持三种类型的循环。,简单循环,一个重复运动的循环，直到碰到循环中的exit或者exitwhen语句时才结束循环,WHILE循环,一个重复运行的循环，直到满足条件才退出,FOR循环,一个重复运行指定遍数的循环,例：一个简单循环举例i_variable:=10;i_sum:=0;loopi_sum:=i_sum+i_variable;i_variable:=i_variable1;exitwheni_variable0;endloop;,例：用while循环来完成上例i_variable:=10;i_sum:=0;whilei_variable0loopi_sum:=i_sum+i_variable;i_variable:=i_variable1;endloop;,例：用for循环来完成简单循环i_variable:=10;i_sum:=0;fori_variablein0.10loopi_sum:=i_sum+i_variable;i_variable:=i_variable1;endloop;,九、游标当查询返回结果超过一行时，就需要一个显式游标，也就是我们常说的游标。常用的游标要在程序中使用游标，大致需要以下几个步骤：1）定义游标2）打开游标3）从游标中提取数据4）关闭游标,例：i_cust_namevarchar2(20);i_cust_idnumber(13);cursorcur_cust_infoisselectname,cust_idfromcustwherests=A;-abeginopencur_cust_info;-bloopfetchcur_cust_infointoi_cust_name,i_cust_id;-cexitwhencur_cust_info%notfound;endloop;closecur_cust_info;-dend;,带参数的游标与存储过程和函数相似，可以将参数传递给游标并在查询中使用。这对于处理在某种条件下打开游标的情况非常有用,例：i_cust_namevarchar2(20);i_cust_idnumber(13);i_stsvarchar2(10):=A;cursorcur_cust_info(p_stsvarchar2)isselectname,cust_idfromcustwherests=p_sts;-abeginopencur_cust_info(i_sts);-bloopfetchcur_cust_infointoi_cust_name,i_cust_id;-cexitwhencur_cust_info%notfound;endloop;closecur_cust_info;-dend;,REFCURSORi_cust_namevarchar2(20);i_cust_idnumber(13);typecur_refisrefcursor;cur_cust_infocur_ref;-abeginopencur_cust_infoforselectname,cust_idfromcustwherests=A;-bloopfetchcur_cust_infointoi_cust_name,i_cust_id;-cexitwhencur_cust_info%notfound;endloop;closecur_cust_info;-dend;,十、PL/SQL程序编写块的介绍PL/SQL程序基本结构是块（BLOCK）所有的PL/SQL程序都是由块组成的，这些块之间还可以相互嵌套。通常，程序中的每一块都实现一个逻辑操作，从而把不同的任务进行分割，由不同的块来实现。,块的结构DECLARE/*Declarativesection-PL/SQLvariables,types,cursors,andlocalsubprogramsgohere.*/-声明部分BEGIN/*Executablesection-proceduralandSQLstatementsgohere.Thisisthemainsectionoftheblockandtheonlyonethatisrequired.*/-执行部分EXCEPTION/*Exception-handlingsection-error-handlingstatementsgohere.*/-出错部分END;,Declare-声明部分l_servidnumber(12);begin-执行部分forrecin(select*fromtemp_ctzj_sts_cmpwhereserv_id_97isnull)loopbeginselectserv_idfromserv_acc_nbrwherenxx_nbr=rec.nxx_nbrandline_nbr=rec.line_nbrandsts=A)whererownum=1;exceptionwhenno_data_foundthennull;end;beginupdatetemp_ctzj_sts_cmpsetserv_id_97=to_char(l_servid)wherenxx_nbr=rec.nxx_nbrandline_nbr=rec.line_nbr;,exceptionwhenothersthendbms_output.put_line(i_servid);end;i_servid:=null;endloop;exception-出错部分whenothersthenrollback;dbms_output.put_line(SQLERRM);end;,过程与函数PL/SQL的过程和函数的运行方式非常类似于其他第3代程序设计语言使用的过程和函数。它们之间具有许多共同的特征属性。总体来说，过程和函数统称为子程序。,创建过程语句的语法如下所示：CREATEORREPLACEPROCEDUREprocedure_name(argumentIN|OUT|INOUTdatatype,argumentIN|OUT|INOUTdatatype)IS|ASprocedure_body,IN/OUT/INOUT参数,模式说明,IN,OUT,INOUT,当过程被调用时，实参的值将传入该过程。在该过程内部，形参类似PL/SQL使用的常数，即该值具有只读属性不能对其修改。当该过程结束时，控制将返回到调用环境，这时，对应的实参没有改变,当过程被调用时，实参具有的任何值将被忽略不计。在该过程内部，形参的作用类似没有初始化的PL/SQL变量，其值为空（NULL）。该变量具有读写属性。当该过程结束时，控制将返回调用环境，形参的内容将赋予对应的实参。,该模式是模式IN和OUT的组合。当调用过程时，实参的值将被传递到该过程中。在该过程内部，形参相当于初始化的变量，并具有读写属性。当该过程结束时，控制将返回到调用环境中，形参的内容将赋予实参,过程体过程体是一种带有声明部分，可执行语句部分和异常部分的PL/SQL块。该声明部分是位于关键字IS或AS和关键字BEGIN之间的语句。可执行部分（该部分是必须要有的）是位于关键字BEGIN和EXCEPTION之间的语句。最后，异常部分位于关键字EXCEPTION和关键字END之间的语句。,综上所述，过程的结构应具有下面所示的特征：CREATEORREPLACEPROCEDUREprocedure_nameparameter_listAS/*Declarativesectionishere*/-声明部分BEGIN/*Executablesectionishere*/-执行部分EXCEPTION/*Exceptionsectionishere*/-异常部分ENDprocedure_name;,函数函数的创建与过程的创建十分相似，具体语法如下：CREATEORREPLACEFUNCTIONfunction_name(argumentIN|OUT|INOUTdatatype,argumentIN|OUT|INOUTdatatype)RETURNdatatypeIS/ASFUCTIONBODY;,包包的说明（也叫做包头）包含了有关包内容的信息。然而，该部分中不包括包的代码部分。CREATEORREPLACEPACKAGEpackage_nameIS|AStype_definition|-结构定义procedure_specification|-过程声明function_specification|-函数声明variable_declaration|-变量声明exception_declaration|-出错声明cursor_declaration|-游标声明pragma_declarationENDpackage_name;,通俗地讲：包头只是起到声明作用，其他具体的实现都是在包体中完成的。,包体介绍包头和包体中的过程和函数的说明必须一致，其中包括子程序名和其参数名，以及参数的模式,包头CREATEORREPLACEPACKAGEpkg_valIS-Author:MRC-Created:2005-4-814:07:30-Purpose:VALIDATETHEVALUESOFEACHENTITYINCLUDECUST,ACCT,SERV,PROD,ORDER-修改：王兴明2005/10/20-客户信息校验PROCEDUREprc_val_cust_src(i_src_filesetVARCHAR2,i_sib_process_statusVARCHAR2,i_batch_noNUMBER);-账户信息校验PROCEDUREprc_val_acct_src(i_src_filesetVARCHAR2,i_sib_process_statusVARCHAR2,i_batch_noNUMBER);-用户信息校验PROCEDUREprc_val_serv_src(i_src_filesetVARCHAR2,i_sib_process_statusVARCHAR2,i_batch_noNUMBER);-子产品信息校验PROCEDUREprc_val_prod_src(i_src_filesetVARCHAR2,i_sib_process_statusVARCHAR2,i_batch_noNUMBER);-优惠信息校验PROCEDUREprc_val_disc_src(i_src_filesetVARCHAR2,i_sib_process_statusVARCHAR2,i_batch_noNUMBER);END;,CREATEORREPLACEPACKAGEBODYpkg_valASPROCEDUREprc_val_cust_src(i_src_filesetvarchar2,i_sib_process_statusvarchar2,i_batch_nonumber)ASi_process_nameVARCHAR2(50):=;i_error_infoVARCHAR2(2000);-错误详细描述BEGINEND;PROCEDUREprc_val_acct_src(i_src_filesetvarchar2,i_sib_process_statusvarchar2i_batch_nonumber)ASEND;,包的作用域包头中声明的任何对象都是在其作用域中，并且可在其外部使用包名作为前缀对其进行引用。,beginarg1datatype:=xxx;arg2datatype:=xxx;package_cedure_name(arg1,arg2);end;,十一、常用工具与调试方式（SQLNavigatorPL/SQLDeveloper）SQLNavigator调试SQLNavigator的调试很简单，大致分为以下几步：,1进入Debug模式,2设置调试参数,3正式调试,4正常结束调试或者中断调试,Serveroutput按钮，可以显示调试过程中oracle执行的情况,Startdebugger按钮，点击按钮进入调试模式，调试必须点这个按钮,进入DEBUG模式,Stepover按钮，点击进入Console界面（调试控制台）,设置调试参数需要完成两项工作,1）设置IN参数,2）设置提交方式,正式调试,直接运行,跑到断点处停下来，按钮F4。,单步调试，遇到调用其他存储过程或者函数时跳过，按钮F8。,单步调试，遇到调用其他存储过程或者函数时进入该存储过程或者函数中，按钮F7。,当调试时进入了调用存储过程或者函数后，该按钮可以帮助跳出该调用存储过程或者函数，回到调试的上层函数中，按钮Shift+F7。,断点设置1）点击需要设置断点的代码行，点击右键，出现菜单，选择togglebreakpoint选项，该行应该成红色，此时断点即设置成功。2）点击需要设置断点的代码行，按按钮F5也能设置断点。设置断点之后，按F4按钮，程序可以连续运行，直到碰到设置的断点为止。当然，设置的断点需要在程序运行能够执行道的代码段上，否则程序将直接执行下去,察看变量1）将鼠标移到变量上方，一秒之后SQLNavigator就会显示出变量的值