存储过程基础培训

存储过程基础培训日达计算机系统（成都）有限公司2007年4月简介•什么是存储过程呢？将常用的或很复杂的工作，预先用SQL语句写好并用一个指定的名称存储起来,那么以后要叫数据库提供与已定义好的存储过程的功能相同的服务时,只需调用execute,即可自动完成命令。•存储过程的优点：1.执行速度快:存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次.。2.当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使3.存储过程可以重复使用,可减少数据库开发人员的工作量4.安全性高,可设定只有某此用户才具有对指定存储过程的使用权种类•1.系统存储过程：以sp_开头,用来进行系统的各项设定.取得信息.相关管理工作,如sp_help就是取得指定对象的相关信息•2.扩展存储过程以XP_开头,用来调用操作系统提供的功能如：execmaster..xp_cmdshell'ping10.8.16.1'•3.用户自定义的存储过程,这是我们所指的存储过程Sqlplus•Sqlplus用户明/密码◎查看有几张表select*fromtab;◎ed可以进行编辑◎/重复命令◎合并字符串||◎查看表的结构desc表名◎sql中字符串用单引号◎主建可以由多个字段组成◎外键不能为空，且一定要在另张表中存在◎可以使用@加文件名就可以在sqlplus中执行其中的plsql语句◎使用％type使得定义存储过程中的变量的时候与表中的类型一致创建及执行•创建脚本CREATEPROCEDURE存储过程名ASselectuseridfromUSERSorderbyuseriddesc•执行脚本executeprocedure存储过程名(参数1,参数2)CreateCREATEPROCEDURE存储过程名字(参数1INNUMBER,参数2INNUMBER)AS/IS--变量定义Declare变量类型;--初始化参数变量1INTEGER:=0;变量2DATE;BEGINEND例如:CREATEPROCEDUREsam.credit(acc_noINNUMBER,amountINNUMBER)ASEGINUPDATEaccountsSETbalance=balance+amountWHEREaccount_id=acc_no;END;备注:注释用:--说明•V_TEST:=123;•可以使用createorreplaceprocedure语句，这个语句的用处在于，你之前赋予的excute权限都将被保留。•IN,OUT,INOUT用来修饰参数。IN:表示这个变量必须被调用者赋值然后传入进行处理。OUT:表示PRCEDURE通过这个变量将值传回给调用者。INOUT:则是这两种的组合。•变量赋值:例如V_TEST:=123;变量SELECTINTOSTATEMENT•将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条记录，否则抛出异常(如果没有记录抛出NO_DATA_FOUND)•例子：•BEGIN•SELECTcol1,col2into变量1,变量2FROMtypestructwherexxx;•EXCEPTION•WHENNO_DATA_FOUNDTHEN•xxxx;•END;•...例子•createorreplaceprocedureget_newsasaanumber;beginselectcount(*)intoaafromcf_news;dbms_output.put_line('aa='||aa);end;IFIF条件THENBEGINdosomethingEND;ENDIF;空语句•if(true)thennull;endif;whileWHILE条件LOOPBEGINdosomethingEND;ENDLOOP;cursor•用forin使用cursor...ISCURSORcurISSELECT*FROMxxx;BEGINFORcur_resultincurLOOPOpencur_result；-打开游标V_SUM:=cur_result.列名1+cur_result.列名2END;ENDLOOP;closecur_result；-关闭游标END;带参数的cursor•带参数的cursorCURSORC_USER(C_IDNUMBER)ISSELECTNAMEFROMUSERWHERETYPEID=C_ID;OPENC_USER(变量值);LOOPFETCHC_USERINTOV_NAME;EXITFETCHC_USER%NOTFOUND;dosomethingENDLOOP;CLOSEC_USER;传回值存储过程的3种传回值:•1.以Return传回整数•2.以output格式传回参数•3.Recordset区别:•output和return都可在批次程式中用变量接收,而recordset则传回到执行批次的客户端中调试•1、sqlplus上执行“setserveroutputon”命令•2、在存储过程中可以用DBMS_OUTPUT.PUT_LINE(VarName);来打印出来•3、可以借助第三方工具，比如Pl/SqlDeveloper例子createorreplaceproceduretestisEmp_nameVARCHAR2(10);Cursorc1ISSELECTEnameFROMEMPWHEREDeptno=20;BEGINOPENc1;LOOPFETCHc1INTOEmp_name;EXITWHENc1%NOTFOUND;DBMS_OUTPUT.PUT_LINE(Emp_name);ENDLOOP;endtest;练习1、表1：sysfee（某费率表格式，里面无数据）字段如下：Idint(触发器，设置自动增加)字冠char(20)（关键字）基本通话时长Int基本费用int通话间隔int通话费用int触发器名称：trig_id表2：source_fee（格式同sysfee，里面假设有10k条数据）存储过程要求实现如下：1、过程参数为目的表名2、要求输入目的表名后，在存储过程中自动按照sysfee的格式创建目的表，并自动将source_fee里面的数据导入到目的表。sequence1、要建一个sequence,以实现自动累加功能createsequenceAAAAminvalue1maxvalue999999999999999999999999999startwith1incrementby1nocache;trigger2、建一个trigger--写在存储过程createorreplacetriggertrg_insertbeforeinsertontableforeachrowdeclarebeginifINSERTINGtheninsertintotable(id)selectAAAA.nextvalfromdual;endif;endtrg_insert;参考答案createorreplaceprocedureaaa(v_tnamevarchar2)assqlvarchar2(255);beginsql:='createtable'||v_tname||'asselect*fromsource_fee';executeimmediatesql;--建立trigger到v_tname;end;