2024年10月oracle常用的存储过程（oracle存储过程基本语法）
⑴oracle常用的存储过程（oracle存储过程基本语法
⑵oracle存储过程基本语法
⑶一：存储过程创建存储过程，需要有CREATEPROCEDURE或CREATEANYPROCEDURE的系统权限??删除存储过程，是过程的创建者或者拥有DROPANYPROCEDURE系统权限?修改存储过程，则只能是过程的创建者或者拥有ALTERANYPROCEDURE系统权限的人执行(或调用)存储过程，是过程的创建者或是拥有EXECUTEANYPROCEDURE系统权限的人或是被拥有者授予EXECUTE权限的人：语法?创建CREATE{AS|IS}BEGIN可执行部分[EXCEPTION错误处理部分]END;?说明：??ORREPLACE?表示如果存在就覆盖存储过程??参数有三种形式：IN、OUT和INOUT。则默认为IN。??关键字AS也可以写成IS，后跟过程的说明部分，可以在此定义过程的局部变量。删除存储过程：DROPPROCEDURE存储过程名；修改存储过程：???ALTERPROCEDURE存储过程名PILE；?执行存储过程????EXECUTE模式名.存储过程名;????或????BEGIN?模式名.存储过程名;END;另外：参数可以是变量、常量或表达式?要其它用户执行存储过程须要给其它用户授权GRANTEXECUTEON存储过程名TO用户名?：参数说明IN定义一个输入参数变量，用于传递参数给存储过程OUT定义一个输出参数变量，用于从存储过程获取数据INOUT定义一个输入、输出参数变量，兼有以上两者的功能?参数名IN数据类型DEFAULT值；??????定义一个输入参数变量，用于传递参数给存储过程。??????可以是常量、有值变量或表达式??????DEFAULT关键字为可选项，用来设定参数的默认值。如果在调用存储过程时不指明参数，则参数变量取默认值??????在存储过程中，输入变量接收主程序传递的值，但不能对其进行赋值。参数名OUT数据类型；??????定义一个输出参数变量，用于从存储过程获取数据，即变量从存储过程中返回值给主程序。?????在调用存储过程时，主程序的实际参数只能是一个变量，而不能是常量或表达式。??????在存储过程中，参数变量只能被赋值而不能将其用于赋值，而且必须给输出变量至少赋值一次。参数名INOUT数据类型DEFAULT值；???????定义一个输入、输出参数变量，兼有以上两者的功能。??????在调用存储过程时，主程序的实际参数只能是一个变量，而不能是常量或表达式。??????DEFAULT关键字为可选项，用来设定参数的默认值。??????在存储过程中，变量接收主程序传递的值，同时可以参加赋值运算，也可以对其进行赋值。在存储过程中必须给变量至少赋值一次。补充：如果省略IN、OUT或INOUT，则默认模式是IN。??????调用它时参数个数与位置可以不一致，用以下形式调用：EXECUTECHANGE_SALARY(P_RAISE=》,P_EMPNO=》);?//=》运算符左侧是参数名，右侧是参数表达式?二：函数创建函数，需要有CREATEPROCEDURE或CREATEANYPROCEDURE的系统权限删除函数，需要是函数的创建者或者是拥有DROPANYPROCEDURE系统权限的人修改函数，需要是函数的创建者或者拥有ALTERANYPROCEDURE系统权限的人执行函数，需要是函数的创建者或拥有EXECUTEANYPROCEDURE系统权限的人：语法创建：CREATERETURN数据类型{AS|IS}BEGIN可执行部分RETURN(表达式)[EXCEPTION??错误处理部分]END;?说明：???参数是可选的，但只能是IN类型(IN关键字可以省略)。???在定义部分的RETURN数据类型，用来表示函数的数据类型，也就是返回值的类型，不可省略。???在可执行部分的RETURN(表达式)，用来生成函数的返回值，其表达式的类型应该和定义部分说明的函数返回值的数据类型一致。在函数的执行部分可以有多个RETURN语句，但只有一个RETURN语句会被执行，一旦执行了RETURN语句，则函数结束并返回调用环境。?删除????DROPFUNCTION函数名；修改????ALTERPROCEDURE函数名PILE；执行????变量名:=函数名(...)?三：对存储过程和函数的查看（可以通过对数据字典【USER_SOURCE】的访问来查询存储过程或函数的有关信息?：查询某个存储过程序内容selectTEXTfromuser_sourceWHERENAME=’存储过程名’;?：查看数据字殿DESCRIBEUSER_SOURCE;//命令行中?：查看存储过程的参数DESCRIBEsay_hello;//后面是过程名?：查看发生错误SHOWERRORS;?：查询一个存储过程或函数是否是有效状态(即编译成功)SELECTSTATUSFROMUSER_OBJECTSWHEREOBJECT_NAME=’过程名’;//注意大小写VALID表示该存储过程有效(即通过编译)，INVALID表示存储过程无效或需要重新编译。它的状态会改变，这与它依赖外部表（表删除修改等操作有关系：查看存储过程与表的依赖关系SELECTREFERENCED_NAME,REFERENCED_TYPEFROMUSER_DEPENDENCIESWHERENAME=’SAY_HELLO’;?说明NAME为实体名，TYPE为实体类型，REFERENCED_OWNER为涉及到的实体拥有者账户，REFERENCED_NAME为涉及到的实体名，REFERENCED_TYPE为涉及到的实体类型。问题：如果一个用户A被授予执行属于用户B的一个存储过程的权限，在用户B的存储过程中，访问到用户C的表，用户B被授予访问用户C的表的权限，但用户A没有被授予访问用户C表的权限，那么用户A调用用户B的存储过程是失败的还是成功的呢？答案是成功的。
⑷oracle存储过程的例子
⑸基本语法：xdxacreateorreplaceprocedurep--存储过程名称xdxaxdxaend;xdxa一般创建表不写存储过程：xdxacreatetableUSERS(xdxauseridnumber()identityprimarykey,xdxausernamevarchar(),xdxapasswordvarchar()xdxa);xdxainsertintousersvalues(’AA’,’’);xdxainsertintousersvalues(’bb’,’’);xdxa查询的存储过程：输入用户名，查询密码xdxacreateorreplaceprocedurepxdxa(xdxap_usernameinusers.username%type,p_passwordoutusers.password%typexdxa)asxdxabeginxdxaselectpasswordintop_passwordfromuserswhereusername=p_username;xdxaexceptionxdxawhenno_data_foundthendbms_output.put_line(’用户不存在’);xdxaend;xdxacreateorreplaceprocedurepxdxa(xdxap_usernameinusers.username%type,p_passwordinusers.password%typexdxa)asxdxabeginxdxainsertintousersvalues(p_username,p_password);xdxaend;
⑹ORACLE创建存储过程，存储函数
⑺基本概念存储过程和存储函数相当于一个东西。存储过程在Oracle里叫procedure。存储过程没有返回值。存储函数在Oracle里叫function。存储函数有返回值。基本语法createorreplaceprocedure名字--createorreplace意思是创建或者替换as--可以在此定义参数begin语句;end;例：create?or?replace?procedure?sayhelloas--说明相当与declarebegindbms_output.put_line(’HelloWorld’);end;基本调用begin--Calltheprocedure?sayhello;?sayhello;?sayhello;??end;带参数的存储过程--查询某个员工的年收入createorreplaceprocedureupmoney(testnameintest_procedure.name%type)?asbegin?updatetest_proceduretsett.money=t.money+?wheret.name=testname;?end?upmoney;特别的地方，参数要指明是输入参数还是输出参数。存储函数createorreplacefunctionFupmoney(tnameinvarchar)??returnnumberas??--定义月薪参数tmoneytest_procedure.money%type;begin?--得到月薪?selectt.money??intotmoney??fromtest_proceduret?wheret.name=tname;?dbms_output.put_line(tmoney*);?return(tmoney*);end;创建一个多输出参数的存储函数例子createorreplaceproceduremanyparm(tnameinvarchar,??????????????????tjoboutvarchar,??????????????????tmoneyoutnumber,??????????????????tdeptoutvarchar)isbegin??selectt.job,t.money,t.dept???intotjob,tmoney,tdept???fromtest_proceduret???wheret.name=tname;endmanyparm;
⑻oracle的存储过程
⑼存储过程可以使得程序执行效率更高、安全性更好，因为过程建立之后已经编译并且储存到数据库，直接写sql就需要先分析再执行因此过程效率更高，直接写sql语句会带来安全性问题，如：sql注入.建立过程不会很耗系统资源，因为过程只是在调用才执行。.存储过程可以用于降低网络流量，存储过程代码直接存储于数据库中，所以不会产生大量T-sql语句的代码流量。.使用存储过程使您能够增强对执行计划的重复使用，由此可以通过使用远程过程调用(RPC)处理服务器上的存储过程而提高性能。RPC封装参数和调用服务器端过程的方式使引擎能够轻松地找到匹配的执行计划，并只需插入更新的参数值。.可维护性高，更新存储过程通常比更改、测试以及重新部署程序集需要较少的时间和精力。.代码精简一致，一个存储过程可以用于应用程序代码的不同位置。.增强安全性：a、通过向用户授予对存储过程（而不是基于表的访问权限，它们可以提供对特定数据的访问；b、提高代码安全，防止SQL注入（但未彻底解决，例如，将数据操作语言－－DML，附加到输入参数；c、SqlParameter类指定存储过程参数的数据类型，作为深层次防御性策略的一部分，可以验证用户提供的值类型（但也不是万无一失，还是应该传递至数据库前得到附加验证。缺点就是：、大量的利用过程，会对服务器压力比较大。
⑽oracle的存储过程
⑾第一步：找到procedures,右键新建第二步：在name中输入存储过程名字（这里输入的就是你以后需要调用的名字，parameters后是参数，在里面定义参数的类型。第三步：编写存储过程语句。第四步：存储过程调用CallableStatementcs=conn.divpareCall(“{callmymd_gis_pro(?,?,?,?,?)}“);//代表第一个参数，setint表示参数类型是int型cs.setInt(,);cs.setInt(,);cs.setInt(,);......//执行Oracle存储过程cs.execute();
⑿如何查看ORACLE包体内的存储过程
⒀打开PL/SQL，新建sql窗口
⒁输入包体的用户名及包名，例如用户名.包名，选中右击，选择viewspec&body查看，包体有两页，第页为包里过程的目录，第也为具体内容。
⒂输入包体的用户名、包名及包里的过程名，例如用户名.包名.过程名，按住ctrl,然后右击即可查看，但只能查看包体的过程内容，过程目录无法查看。
⒃同样在PL/SQL中，打开Tool目录，确保ObjectBrowser前面已打勾，然后口窗口右侧可以找到Objects小窗口，点击《currentuser》,选择自己包体所在目录，如果是当前用户，则不用选择，然后展开Packages目录，目录下即是包名，可以找到自己需要的包体，右击选择viewspec&body查看。
⒄oracle存储过程
⒅还是看看你的数据吧，两个操作的结果应该是一样的看。是不是进行了其它的操作造成的。
⒆Oracle存储过程及举例（几种参数情况的存储
⒇createtableTESTTABLE(idVARCHAR(),nameVARCHAR())selectt.id,t.namefromTESTTABLEtinsertintoTESTTABLE(ID,NAME)values(’’,’zhangsan’);insertintoTESTTABLE(ID,NAME)values(’’,’lisi’);insertintoTESTTABLE(ID,NAME)values(’’,’wangwu’);insertintoTESTTABLE(ID,NAME)values(’’,’xiaoliu’);insertintoTESTTABLE(ID,NAME)values(’’,’laowu’);---创建存储过程createorreplaceproceduretest_countasv_totalnumber();beginselectcount(*)intov_totalfromTESTTABLE;DBMS_OUTPUT.put_line(’总人数：’||v_total);end;--准备--线对scott解锁：alteruserscottaountunlock;--应为存储过程是在scott用户下。还要给scott赋予密码---alteruserscottidentifiedbytiger;---去命令下执行EXECUTEtest_count;----在ql/spl中的sql中执行begin--Calltheproceduretest_count;end;createorreplaceprocedureTEST_LISTAS---是用游标CURSORtest_cursorISselectt.id,t.namefromTESTTABLEt;beginforTest_recordINtest_cursorloop---遍历游标，在打印出来DBMS_OUTPUT.put_line(Test_record.id||Test_record.name);ENDLOOP;test_count;--同时执行另外一个存储过程（TEST_LIST中包含存储过程test_countend;-----执行存储过程TEST_LISTbeginTEST_LIST;END;---存储过程的参数---IN定义一个输入参数变量，用于传递参数给存储过程--OUT定义一个输出参数变量，用于从存储过程获取数据---INOUT定义一个输入、输出参数变量，兼有以上两者的功能--这三种参数只能说明类型，不需要说明具体长度比如varchar()，defaul可以不写，但是作为一个程序员最好还是写上。---创建有参数的存储过程createorreplaceproceduretest_param(p_idinVARCHARdefault’’)asv_namevarchar();beginselectt.nameintov_namefromTESTTABLEtwheret.id=p_id;DBMS_OUTPUT.put_line(’name：’||v_name);end;----执行存储过程begintest_param(’’);end;default’’---创建有参数的存储过程createorreplaceproceduretest_paramout(v_nameOUTVARCHAR)asbeginselectnameintov_namefromTESTTABLEwhereid=’’;DBMS_OUTPUT.put_line(’name：’||v_name);end;----执行存储过程DECLAREv_nameVARCHAR();BEGINtest_paramout(v_name);DBMS_OUTPUT.PUT_LINE(’name：’||v_name);END;-------INOUT---创建存储过程createorreplaceproceduretest_paramINOUT(p_phonenumberinoutvarchar)asbeginp_phonenumber:=’-’||p_phonenumber;end;----DECLAREp_phonenumberVARCHAR();BEGINp_phonenumber:=’’;test_paramINOUT(p_phonenumber);DBMS_OUTPUT.PUT_LINE(’新的电话号码：’||p_phonenumber);END;-----sql命令下，查询当前用户的存储过程或函数的源代码，-----可以通过对USER_SOURCE数据字典视图的查询得到。USER_SOURCE的结构如下：SQL》DESCRIBEUSER_SOURCE;NameTypeNullableDefaultments----------------------------------------------------------------------------------------------------------------------------------------------NAMEVARCHAR()YNameoftheobjectTYPEVARCHAR()YTypeoftheobject:“TYPE“,“TYPEBODY“,“PROCEDURE“,“FUNCTION“,“PACKAGE“,“PACKAGEBODY“or“JAVASOURCE“LINENUMBERYLinenumberofthislineofsourceTEXTVARCHAR()YSourcetextSQL》---查询出存储过程的定义语句selecttextfromuser_sourceWHERENAME=’TEST_COUNT’;----查询存储过程test_paramINOUT的参数SQL》DESCRIBEtest_paramINOUT;ParameterTypeModeDefault?-----------------------------------P_PHONENUMBERVARCHARINOUTSQL》---查看当前的存储过程的状态是否正确，---VALID为正确，INVALID表示存储过程无效或需要重新编译SELECTSTATUSFROMUSER_OBJECTSWHEREOBJECT_NAME=’TEST_COUNT’;-----如果要检查存储过程或函数的依赖性，可以通过查询数据字典USER_DENPENDENCIES来确定，该表结构如下：SQL》DESCRIBEUSER_DEPENDENCIES;NameTypeNullableDefaultments----------------------------------------------------------------------------------------------------------NAMEVARCHAR()NameoftheobjectTYPEVARCHAR()YTypeoftheobjectREFERENCED_OWNERVARCHAR()YOwnerofreferencedobject(remoteownerifremoteobject)REFERENCED_NAMEVARCHAR()YNameofreferencedobjectREFERENCED_TYPEVARCHAR()YTypeofreferencedobjectREFERENCED_LINK_NAMEVARCHAR()YNameofdblinkifthisisaremoteobjectSCHEMAIDNUMBERYDEPENDENCY_TYPEVARCHAR()YSQL》---查询存储过程TEST_COUNT的依赖关系SELECTREFERENCED_NAME,REFERENCED_TYPEFROMUSER_DEPENDENCIESWHERENAME=’TEST_COUNT’;
⒈Oracle怎么导出存储过程
⒉首先登录到数据库中，点击右上角有个工具栏，点击工具栏上的tools。
⒊选择导出用户对象，点击tools工具之后，向下查找ExportUserObjects功能，通过这个功能导出数据。
⒋选择需要导出的表或者序列或者存储过程等。在Type列中展示的是对象属性，表、序列和存储过程等。这里要选择存储过程或者序列。
⒌选择多个导出的对象。按住ctrl键进行多选导出的对象。点击OutputFile输出文件路径，选择文件路径输入文件名称，后缀是sql。如果后缀是sql，则是一些数据库语句，可以直接在其他应用上执行，
⒍开始导出数据脚本。点击Export按钮，开始对选中的对象进行导出成数据脚本。出的时候需要选择一个目录。
⒎查看导出文件。导出完成后，双击打开导出后的文件，就可以看到是一些建表存储过程等脚本。查看导出文件就可以看到是具体的脚本语言。
⒏oracle存储过程
⒐基本结构CREATEORREPLACEPROCEDURE存储过程名字(参数INNUMBER,参数INNUMBER)IS变量INTEGER:=;变量DATE;BEGINEND存储过程名字你那个ENDP_UPDATELOCKCMPINFO;没看懂是啥意思.SELECTINTOSTATEMENT将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条记录，否则抛出异常(如果没有记录抛出NO_DATA_FOUND)例子：BEGINSELECTcol,colinto变量,变量FROMtypestructwherexxx;EXCEPTIONWHENNO_DATA_FOUNDTHENxxxx;END;....IF判断IFV_TEST=THENBEGINdosomethingEND;ENDIF;.while循环WHILEV_TEST=LOOPBEGINXXXXEND;ENDLOOP;.变量赋值V_TEST:=;.用forin使用cursor...ISCURSORcurISSELECT*FROMxxx;BEGINFORcur_resultincurLOOPBEGINV_SUM:=cur_result.列名+cur_result.列名END;ENDLOOP;END;.带参数的cursorCURSORC_USER(C_IDNUMBER)ISSELECTNAMEFROMUSERWHERETYPEID=C_ID;OPENC_USER(变量值);LOOPFETCHC_USERINTOV_NAME;EXITFETCHC_USER%NOTFOUND;dosomethingENDLOOP;CLOSEC_USER;.用pl/sqldeveloperdebug连接数据库后建立一个TestWINDOW在窗口输入调用SP的代码,F开始debug,CTRL+N单步调试简单实例，通过DBMS_OUTPUT来看结果CREATEORREPLACEPROCEDUREbb(lic_paraINVARCHAR,out_paraOUTVARCHAR)AStempVARCHAR();BEGINSELECTlic_noINTOtempFROMt_vehicle_infoWHERElic_no=lic_para;out_para:=temp;DBMS_OUTPUT.put_line(out_para);ENDbb;下面是调用：begin--Calltheprocedurebb(lic_para=》:lic_para,out_para=》:out_para);end;可以在命令行里敲sqlplus“yonghuming/mimadbname”接着调试存储过程。但是最好用的是在pl/sqlDeveloper工具里面用点存储过程里的TEST来调用,它能自动生成调用的语句并有栏目让你输入参数值，包括输入参数和输出参数，并把结果返回到输出参数里面，在结果栏可见，这点pl/sqlDeveloper比TOAD要强，TOAD在存储过程上右键后点击EXECUTEProcedure也可以执行，只是结果在那看我不晓得，而在pl/sqlDeveloper按F可以调试，ctrl+N可以单步跟踪，的确爽