甲骨论2012版oracle视频教程-相克军老师plsql编程简介

PL/SQL编程简介Oracle在数据库中引入了一种过程化编程语言，称为PL/SQL。（ProceduralLanguage）PL/SQL构建在SQL之上，可以用来编写包含SQL语句的程序。块结构典型的PL/SQL块代码包含如下内容：[DECLARE declarationstatements]BEGIN executable_statements[EXCEPTION exception_handling_statements]END;1、declare负责声明该块其他部分将会使用的变量，这些变量对于这个块来说是局部变量，也就是说不能从块的外部访问这些变量。2、DECLARE和EXCEPTION是可选的。3、每条语句都是以;来结束。触发了异常处理。变量和类型变量声明包含名称和类型。1、经典用法一 product_id integer; product_type_id integer; name varchar2(30); description varchar2(50); price number(5,2);2、通过使用%TYPE关键字来定义变量的类型，变量将会使用和表中指定的列相同的数据类型 product_price product.price%type;条件逻辑在PL/SQL中，IF、THEN、ELSE、ELSEIF、ENDIF等关键字用于执行条件逻辑。IFcondition1THEN statements1ELSEIFcondition2THEN statements2ELSE statements3ENDIF;如果condition1为真，则执行statements1。如果condition1为假，而condition2为真，则执行statements2。如果condition1和condition2都为假，则执行statements3。IFcount>0then message:=‘countispositive’;IFarea>0then message:=‘countandareaarepositive’;ENDIFELSEIFcount=0THEN message:=‘countiszero’;ELSE message:=‘countisnegative’;ENDIF;也可以在一个IF语句中嵌入另一个IF语句。循环PL/SQL中有三类循环：1、简单循环2、WHILE循环3、FOR循环简单循环LOOP statements;ENDLOOP;count:=0;LOOP counter:=counter+1;EXITWHENcount=5;ENDLOOP;WHILE循环WHILEconditionLOOP statementsENDLOOP;count:=0;WHILEcount<6LOOP count:=count+1;ENDLOOP;FOR循环FOR循环会运行预先确定的次数，可通过给循环变量指定上限和下限来确定循环运行的次数，然后，循环变量在每次循环中递增（或递减）。FORloop_variableIN[REVERSE]lower_bound..upper_boundLOOP statementsENDLOOP;循环变量的值在每一次循环中都增加（如果使用reverse关键字则减少）1.FORcountIN1..10LOOP dbms_output.put_line(count);ENDLOOP;如果指定了reverse，那么count从10开始递减。游标当select语句从数据库中返回的记录多于一条时，就可以使用游标（cursor）。游标可以理解为可以一次访问一个的一组记录。使用游标需要遵循下面的5个步骤：1、声明一些变量，用于保存select语句返回的列值。2、声明游标，并指定select语句。3、打开游标。4、从游标获取记录。5、关闭游标。声明用于保存列值的变量注意：这些变量必须与列的类型兼容。 declare v_product_duct_id%type; v_%type; v_priceproducts.price%type;声明游标 CURSORcursor_nameIS SELECT_statements; CURSORcv_product_cursorIS selectproduct_id,name,price fromproducts orderbyproduct_id;打开游标这一步是真正的执行select语句 OPENcursor_name; OPENcv_product_cursor;从游标获取记录使用FETCH语句从游标获取一条记录。 FETCHcursor_name INTOvariable[,variable…]; FETCHcv_product_cursor INTOv_product_id,v_name,v_price;游标可能包含多条记录：因此要通过循环依次读取每一条记录。为了确定循环是否结束，可以使用布尔变量cv_product_cursor%NOTFOUND。当FETCH语句到达游标中的最后一条记录时，不能再读出更多的记录时，这个变量就为真。LOOPFETCHcv_product_cursorINTOv_product_id,v_name,v_price;EXITWHENcv_product_cursor%NOTFOUND;DBMS_OUTPUT.PUT_LINE(‘v_product_id=’||v_product_id||’,v_name=’||v_name||‘,v_price=’||v_price);ENDLOOP;关闭游标关闭游标可以释放资源CLOSEcv_product_cursor;setserveroutputondeclare v_product_duct_id%type; v_%type; v_priceproducts.price%type; CURSORcv_product_cursorIS selectproduct_id,name,price fromproducts orderbyproduct_id;beginOPENcv_product_cursor;LOOPFETCHcv_product_cursorINTOv_product_id,v_name,v_price;EXITWHENcv_product_cursor%NOTFOUND;DBMS_OUTPUT.PUT_LINE('v_product_id='||v_product_id||',v_name='||v_name||',v_price='||v_price);ENDLOOP;CLOSEcv_product_cursor;end;执行结果游标与FOR循环游标与FOR循环是很经典的用法。在使用FOR循环的时候，可以不用显式的打开和关闭游标——for循环会自动执行这些操作。setserveroutputondeclare cursorcv_product_cursoris selectproduct_id,name,price fromproducts orderbyproduct_id;begin forv_productincv_product_cursorloop dbms_output.put_line( 'product_id='||v_duct_id||',name='||v_ ||',price='||v_product.price ); endloop;end;异常用于处理PL/SQL块中出现的错误。我们在编写PL/SQL的时候，通常将一些常见的EXCEPTION写入到程序中，当程序遇到错误的时候，会自动的被捕获到，然后进行错误处理。我们下面讲解一些常见的异常：1、ZERO_DIVIDE异常setserveroutputonbegindbms_output.put_line(1/0);exceptionwhenzero_dividethendbms_output.put_line('Divisionbyzero');end;这个语句会发生异常（故意设置）。当异常发生时，程序的控制权将交给EXCEPTION块，其中的WHEN子句负责检查与何种异常相匹配。如果没有匹配的异常，则将该异常传播到外围模块中。2、DUP_VAL_ON_INDEX异常如果试图向具有唯一性索引约束的列中插入重复的值，就会引发DUP_VAL_ON_INDEX异常。setserveroutputonbegininsertintocustomers(customer_id,first_name,last_name)values(1,'Greg','Green');exceptionwhendup_val_on_indexthendbms_output.put_line('Duplicatevalueonanindex');end;3、INVALID_NUMBER异常当试图将无效的字符串转换成数字时，就会引发INVALID_NUMBER异常。setserveroutputonbegin insertintocustomers(customer_id,first_name,last_name) values('123X','Greg','Green');exception wheninvalid_numberthen dbms_output.put_line('Conversionofstringtonumberfailed');end;4、OTHERS异常OTHERS异常可以处理所有异常setserveroutputonbegindbms_output.put_line(1/0);exception whenothersthen dbms_output.put_line('Anexceptionoccurred');end;注意：因为others可处理所有的异常，因此必须在exception块中所有特定的异常之后使用该异常。如果试图在其他地方使用others异常，数据库就会返回PLS/00370错误。setserveroutputonbegindbms_output.put_line(1/0);exception whenzero_dividethen dbms_output.put_line('Divisionbyzero'); whenothersthen dbms_output.put_line('Anexceptionoccurred');end;setserveroutputonbegindbms_output.put_line(1/0);exception whenothersthen dbms_output.put_line('Anexceptionoccurred'); whenzero_dividethen dbms_output.put_line('Divisionbyzero');end;过程可以创建包含一组SQL和PL/SQL语句的过程。1、创建过程2、调用过程3、获取过程信息4、删除过程5、查看过程中的错误创建存储过程create[orreplace]procedureprocedure_name[(parameter_name[in|out|inout]type[,...])]{is|as}beginprocedure_body;endprocedure_name;1、IN、OUT、INOUT：定义了参数的模式，每一个参数可以选择下列的模式之一：IN：参数的默认模式，这种模式定义的参数在程序运行的时候已经具有值，在过程体中这个值不会改变。OUT：该模式定义的参数只在过程体内部赋值。INOUT：该模式定义的参数当过程运行时可能已经具有值，但是在过程体中也可以修改。CREATEORREPLACEPROCEDUREupdate_product_price(p_product_idINduct_id%TYPE,p_factorINNUMBER)ASv_product_countINTEGER;BEGIN--countthenumberofproductswiththe--suppliedproduct_id(shouldbe1iftheproductexists)SELECTCOUNT(*)INTOv_product_countFROMproductsWHEREproduct_id=p_product_id;--iftheproductexists(v_product_count=1)then--updatethatproduct'spriceIFv_product_count=1THENUPDATEproductsSETprice=price*p_factorWHEREproduct_id=p_product_id;COMMIT;ENDIF;EXCEPTIONWHENOTHERSTHENROLLBACK;ENDupdate_product_price;/调用过程获取有关过程的信息该过程是否包含聚合函数。该过程是否支持并行查询。删除过程DROPPROCEDUREupdate_product_price;查看过程中的错误错误显示错误函数函数和过程唯一的区别是函数必须向调用它的语句返回一个值。create[orreplace]functionfunction_name[(parameter_name[in|out|inout]type,...)]returntype{as|is}beginfunction_bodyendfunction_name;CREATEORREPLACEFUNCTIONcircle_area(p_radiusINNUMBER)RETURNNUMBERASv_piNUMBER:=3.1415926;v_areaNUMBER;BEGINv_area:=v_pi*POWER(p_radius,2);RETURNv_area;ENDcircle_area;/CREATEORREPLACEFUNCTIONaverage_product_price(p_product_type_idININTEGER)RETURNNUMBERASv_average_product_priceNUMBER;BEGINSELECTAVG(price)INTOv_average_product_priceFROMproductsWHEREproduct_type_id=p_product_type_id;RETURNv_average_product_price;ENDaverage_product_price;/调用函数获取有关函数的信息删除函数DROPfunctioncircle_area;包下面将要讲述如何把过称和函数组织到包中。包可以将彼此相关的功能划分到一个自包含单元中，实现PL/SQL的模块化。包通常由两个部分组成：1、规范（specification）2、包体（body）包的规范包含有关包的信息，其中列出可用的过程和函数。规范中通常不包括构成这些过程和函数的代码，包体中才包含实际的代码。注意：规范中包含的过程和函数可被外部访问，但是只在包体中包含的过程和函数只能被包体自身访问，他们对这个包来说是私有的，而规范中的过程和函数是公有的。创建包规范create[orreplace]packagepackage_name{as|is} package_specificationendpackage_name;指定用户可以使用的过程和函数的列表（同时包括变量、类型定义和游标）CREATEORREPLACEPACKAGEproduct_packageASTYPEt_ref_cursorISREFCURSOR;FUNCTIONget_products_ref_cursorRETURNt_ref_cursor;PROCEDUREupdate_product_price(p_product_idINduct_id%TYPE,p_factorINNUMBER);ENDproduct_package;/创建包体create[orreplace]packagebodypackage_name{is|as} package_bodyendpackage_name;CREATEORREPLACEPACKAGEBODYproduct_packageASFUNCTIONget_products_ref_cursorRETURNt_ref_cursorISproducts_ref_cursort_ref_cursor;BEGIN--gettheREFCURSOROPENproducts_ref_cursorFORSELECTproduct_id,name,priceFROMproducts;--returntheREFCURSORRETURNproducts_ref_cursor;ENDget_products_ref_cursor;PROCEDUREupdate_product_price(p_product_idINduct_id%TYPE,p_factorINNUMBER)ASv_product_countINTEGER;BEGIN--countthenumberofproductswiththe--suppliedproduct_id(shouldbe1iftheproductexists)SELECTCOUNT(*)INTOv_product_countFROMproductsWHEREproduct_id=p_product_id;--iftheproductexists(v_product_count=1)then--updatethatproduct'spriceIFv_product_count=1THENUPDATEproductsSETprice=price*p_factorWHEREproduct_id=p_product_id;COMMIT;ENDIF;EXCEPTIONWHENOTHERSTHEN--performarollbackwhenanexceptionoccursROLLBACK;ENDupdate_product_price;ENDproduct_package;/函数get_products_ref_cursor()打开一个游标，然后从products表中检索product_id、name和price列的值，该函数返回包含产品product_id、name和price的游标。获取有关包中函数和过程的信息删除包DROPPACKAGEproduct_package;触发器触发器是在特定的SQLDML语句，如insert、update、delete语句在特定的数据库表上运行时，由数据库自动运行（自动激活）的过程。触发器对于实现表中某个列值的高级变更审计等功能非常有用。触发器运行的时机触发器可以在SQL语句运行之前和之后激活，同时由于DML语句可能同时作用于多行，所以触发器的过程代码可能在所用的每一行上都运行一次（行级触发器），也可能只在所有的行上运行一次（语句级触发器）。如果一个update语句可以更新10行，同时还有一个行级触发器在执行update语句时激活，那么这个触发器执行10次，其中每一行都执行一次。然而，如果触发器是语句级的，那么对于整条update语句来说，这个触发器只激活一次。注意：当update语句在某个列上激活行级触发器时，这个触发器可以同时访问该列的原值和新值。下面实现的触发器，他可以在产品价格降低超过25%时将其记录下来。当这一事件发生时，触发器向product_price_audit表中添加一行。CREATETABLEproduct_price_audit(product_idINTEGERCONSTRAINTprice_audit_fk_productsREFERENCESproducts(product_id),old_priceNUMBER(5,2),new_priceNUMBER(5,2));createorreplacetriggertrigger_name{before|after|insteadof}trigger_eventontable_name[foreachrow[whentrigger_