存储过程详解

存储过程详解存储过程的in参数宽度又外部决定，而out和inout的宽度是由存储过程内部决定。此外in后面还可以带默认值，而out和inout不能带默认值。1.createorreplaceprocedureprocdefault(p1varchar2,p2varchar2default'mark')2.as3.begin4.dbms_output.put_line(p2);5.end;6.SQLsetserveroutputon;7.SQLexecprocdefault('a');一、集合：索引表,也称为pl/sql表，不能存储于数据库中，元素的个数没有限制,下标可以为负值。1.typet_tableistableofvarchar2(20)indexbybinary_integer;2.v_studentt_table;嵌套表，索引表没有indexby子句就是嵌套表，它可以存放于数据库中，元素个数无限，下标从1开始，并且需要初始化1.typet_nestTableistableofvarchar2(20);2.v_classt_nestTable;仅是这样声明是不能使用的，必须对嵌套表进行初始化，对嵌套表进行初始化可以使用它的构造函数v_class:=t_nestTable('a','b','c');变长数组，变长数组与高级语言的数组类型非常相似，下标以1开始，元素个数有限。也需要进行初始化。typet_arrayisvarray(20)ofvarchar2(20);由此可见，如果仅仅是在存储过程中当作集合变量使用，索引表是最好的选择。二、游标：显示游标分为：普通游标，参数化游标和游标变量三种。游标循环最佳策略我们在进行PL/SQL编程时，经常需要循环读取结果集的数据。进行逐行处理，这个过程就需要对游标进行循环。对游标进行循环的方法有多种，我们在此一一分析。行PL/SQL编程时，经常需要循环读取结果集的数据。进行逐行处理，这个过程就需要对游标进行循环。对游标进行循环的方法有多种，我们在此一一分析。1.createorreplaceprocedureproccycle(pvarchar2)2.as3.cursorc_postypeisselectpos_type,descriptionfrompos_type_tblwhererownum6;4.v_postypevarchar2(20);5.v_descriptionvarchar2(50);6.begin7.openc_postype;8.ifc_postype%foundthen9.dbms_output.put_line('foundtrue');10.elsifc_postype%found=falsethen11.dbms_output.put_line('foundfalse');12.else13.dbms_output.put_line('foundnull');14.endif;15.–-以下是loop循环--16.loop17.fetchc_postypeintov_postype,v_description;18.exitwhenc_postype%notfound;19.dbms_output.put_line('postype:'||v_postype||',description:'||v_description);20.endloop;21.closec_postype;22.dbms_output.put_line('---loopend---');23.openc_postype;24.–-以下是while循环--25.fetchc_postypeintov_postype,v_description;26.whilec_postype%foundloop27.dbms_output.put_line('postype:'||v_postype||',description:'||v_description);28.fetchc_postypeintov_postype,v_description;29.endloop;30.closec_postype;31.dbms_output.put_line('---whileend---');32.–-以下是for循环--33.forv_posinc_postypeloop34.v_postype:=v_pos.pos_type;35.v_description:=v_pos.description;36.dbms_output.put_line('postype:'||v_postype||',description:'||v_description);37.endloop;38.dbms_output.put_line('---forend---');39.end;使用游标之前需要开打游标,opencursor，循环完后再关闭游标closecursor.这是使用游标应该慎记于心的法则。第一种使用loop循环1.loop2.fetchc_postypeintov_postype,v_description;3.exitwhenc_postype%notfound;4.……5.endloop这里需要注意，exitwhen语句一定要紧跟在fetch之后。必避免多余的数据处理。处理逻辑需要跟在exitwhen之后。这一点需要多加小心。循环结束后要记得关闭游标。第二种使用while循环1.fetchc_postypeintov_postype,v_description;2.whilec_postype%foundloop3.……4.fetchc_postypeintov_postype,v_description;5.endloop;我们知道了一个游标打开后，必须执行一次fetch语句，游标的属性才会起作用。所以使用while循环时，就需要在循环之前进行一次fetch动作。而且数据处理动作必须放在循环体内的fetch方法之前。循环体内的fetch方法要放在最后。否则就会多处理一次。这一点也要非常的小心。总之，使用while来循环处理游标是最复杂的方法。第三种for循环1.forv_posinc_postypeloop2.v_postype:=v_pos.pos_type;3.v_description:=v_pos.description;4.…5.endloop;可见for循环是比较简单实用的方法。首先，它会自动open和close游标。解决了你忘记打开或关闭游标的烦恼。其它,自动定义了一个记录类型及声明该类型的变量，并自动fetch数据到这个变量中。我们需要注意v_pos这个变量无需要在循环外进行声明，无需要为其指定数据类型。它应该是一个记录类型，具体的结构是由游标决定的。这个变量的作用域仅仅是在循环体内。把v_pos看作一个记录变量就可以了，如果要获得某一个值就像调用记录一样就可以了。如v_pos.pos_type由此可见，for循环是用来循环游标的最好方法。高效，简洁，安全。但遗憾的是，常常见到的却是第一种方法。所以从今之后得改变这个习惯了。三、selectinto不可忽视的问题我们知道在pl/sql中要想从数据表中向变量赋值，需要使用selectinto子句。但是它会带动来一些问题，如果查询没有记录时，会抛出no_data_found异常。如果有多条记录时，会抛出too_many_rows异常。这个是比较糟糕的。一旦抛出了异常，就会让过程中断。特别是no_data_found这种异常，没有严重到要让程序中断的地步，可以完全交给由程序进行处理。1.createorreplaceprocedureprocexception(pvarchar2)2.as3.v_postypevarchar2(20);4.begin5.selectpos_typeintov_postypefrompos_type_tblwhere1=0;6.dbms_output.put_line(v_postype);7.end;执行这个程序会报no_date_found异常。处理这个异常有三个办法：1．直接加上异常处理1.createorreplaceprocedureprocexception(pvarchar2)2.as3.v_postypevarchar2(20);4.begin5.selectpos_typeintov_postypefrompos_type_tblwhere1=0;6.dbms_output.put_line(v_postype);7.exception8.whenno_data_foundthen9.dbms_output.put_line('没找到数据');10.end;这样做换汤不换药，程序仍然被中断。可能这样不是我们所想要的。2.selectinto做为一个独立的块，在这个块中进行异常处理1.createorreplaceprocedureprocexception(pvarchar2)2.as3.v_postypevarchar2(20);4.begin5.begin6.selectpos_typeintov_postypefrompos_type_tblwhere1=0;7.dbms_output.put_line(v_postype);8.exception9.whenno_data_foundthen10.v_postype:='';11.end;12.dbms_output.put_line(v_postype);13.end;这是一种比较好的处理方式了。不会因为这个异常而引起程序中断。3.使用游标1.createorreplaceprocedureprocexception(pvarchar2)2.as3.v_postypevarchar2(20);4.cursorc_postypeisselectpos_typefrompos_type_tblwhere1=0;5.begin6.openc_postype;7.fetchc_postypeintov_postype;8.closec_postype;9.dbms_output.put_line(v_postype);10.end;这样就完全的避免了no_data_found异常。完全交由程序员来进行控制了。第二种情况是too_many_rows异常的问题。Too_many_rows这个问题比起no_data_found要复杂一些。给一个变量赋值时，但是查询结果有多个记录。处理这种问题也有两种情况：1．多条数据是可以接受的，也就是说从结果集中随便取一个值就行。这种情况应该很极端了吧，如果出现这种情况，也说明了程序的严谨性存在问题。2．多条数据是不可以被接受的，在这种情况肯定是程序的逻辑出了问题，也说是说原来根本就不会想到它会产生多条记录。对于第一种情况，就必须采用游标来处理，而对于第二种情况就必须使用内部块来处理，重新抛出异常。多条数据可以接受,随便取一条，这个跟no_data_found的处理方式一样，使用游标。我这里仅说第二种情况，不可接受多条数据，但是不要忘了处理no_data_found哦。这就不能使用游标了，必须使用内部块。1.createorreplaceprocedureprocexception2(pvarchar2)2.as3.v_postypevarchar2(20);4.begin5.begin6.selectpos_typeintov_postypefrompos_type_tblwhererownum5;7.exception8.whenno_data_foundthen9.v_postype:=null;10.whentoo_many_rowsthen11.raise_application_error(-20000,'对v_postype赋值时，找到多条数据');12.end