Oracle存储过程基本语法

Oracle存储过程基本语法 存储过程1CREATEORREPLACEPROCEDURE存储过程名2IS3BEGIN4NULL;5END;行1:CREATEORREPLACEPROCEDURE是一个SQL语句通知Oracle数据库去创建一个叫做skeleton存储过程,如果存在就覆盖它;行2:IS关键词表明后面将跟随一个PL/SQL体。行3:BEGIN关键词表明PL/SQL体的开始。行4:NULLPL/SQL语句表明什么事都不做，这句不能删去，因为PL/SQL体中至少需要有一句;行5:END关键词表明PL/SQL体的结束存储过程创建语法：createorreplaceprocedure存储过程名（param1intype，param2outtype）as变量1类型（值范围）;-vs_msgVARCHAR2(4000);变量2类型（值范围）;BeginSelectcount(*)into变量1from表Awhere列名=param1；If(判断条件)thenSelect列名into变量2from表Awhere列名=param1；Dbms_output。Put_line(打印信息);Elsif(判断条件)thenDbms_output。Put_line(打印信息);ElseRaise异常名（NO_DATA_FOUND）;Endif;ExceptionWhenothersthenRollback;End;注意事项：1，存储过程参数不带取值范围，in表示传入，out表示输出类型可以使用任意Oracle中的合法类型。2，变量带取值范围，后面接分号3，在判断语句前最好先用count（*）函数判断是否存在该条操作记录4，用o。给变量赋值5，在代码中抛异常用raise+异常名CREATEORREPLACEPROCEDURE存储过程名(-定义参数is_ymINCHAR(6),the_countOUTNUMBER,)AS-定义变量vs_msgVARCHAR2(4000);-错误信息变量vs_ym_begCHAR(6);-起始月份vs_ym_endCHAR(6);-终止月份vs_ym_sn_begCHAR(6);-同期起始月份vs_ym_sn_endCHAR(6);-同期终止月份-定义游标(简单的说就是一个可以遍历的结果集)CURSORcur_1ISSELECT。FROM。WHERE。GROUPBY。;BEGIN-用输入参数给变量赋初值，用到了Oralce的SUBSTRTO_CHARADD_MONTHSTO_DATE等很常用的函数。vs_ym_beg:=SUBSTR(is_ym,1,6);vs_ym_end:=SUBSTR(is_ym,7,6);vs_ym_sn_beg:=TO_CHAR(ADD_MONTHS(TO_DATE(vs_ym_beg,yyyymm),-12),yyyymm);vs_ym_sn_end:=TO_CHAR(ADD_MONTHS(TO_DATE(vs_ym_end,yyyymm),-12),yyyymm);-先删除表中特定条件的数据。DELETEFROM表名WHEREym=is_ym;-然后用内置的DBMS_OUTPUT对象的put_line方法打印出影响的记录行数，其中用到一个系统变量SQL%rowcountDBMS_OUTPUT.put_line(del上月记录=|SQL%rowcount|条);INSERTINTO表名(area_code,ym,CMCODE,rmb_amt,usd_amt)SELECTarea_code,is_ym,CMCODE,SUM(rmb_amt)/10000,SUM(usd_amt)/10000FROMBGD_AREA_CM_M_BASE_TWHEREym=vs_ym_begANDym0thenbeginx:=0-x;end;endif;ifx=0thenbeginx:=1;end;endif;endtest;2、For循环For.in.LOOP-执行语句endLOOP;(1)循环遍历游标createorreplaceproceduretest()asCursorcursorisselectnamefromstudent;namevarchar(20);beginfornameincursorLOOPbegindbms_output.putline(name);end;endLOOP;endtest;(2)循环遍历数组createorreplaceproceduretest(varArrayinmyPackage.TestArray)as-(输入参数varArray是自定义的数组类型，定义方式见标题6)inumber;begini:=1;-存储过程数组是起始位置是从1开始的，与java、C、C+等语言不同。因为在Oracle中本是没有数组的概念的，数组其实就是一张-表(Table),每个数组元素就是表中的一个记录，所以遍历数组时就相当于从表中的第一条记录开始遍历foriin1.varArray.countLOOPdbms_output.putline(TheNo.|i|recordinvarArrayis:|varArray(i);endLOOP;endtest;3、While循环while条件语句LOOPbeginend;endLOOP;E.gcreateorreplaceproceduretest(iinnumber)asbeginwhilei10LOOPbegini:=i+1;end;endLOOP;endtest;4、数组首先明确一个概念：Oracle中本是没有数组的概念的，数组其实就是一张表(Table),每个数组元素就是表中的一个记录。使用数组时，用户可以使用Oracle已经定义好的数组类型，或可根据自己的需要定义数组类型。(1)使用Oracle自带的数组类型xarray;-使用时需要需要进行初始化e.g:createorreplaceproceduretest(youtarray)isxarray;beginx:=newarray();y:=x;endtest;(2)自定义的数组类型(自定义数据类型时，建议通过创建Package的方式实现，以便于管理)createorreplacepackagemyPackageisPublictypedeclarationstypeinfoisrecord(namevarchar(20),ynumber);typeTestArrayistableofinfoindexbybinary_integer;-此处声明了一个TestArray的类型数据，其实其为一张存储Info数据类型的Table而已，及TestArray就是一张表，有两个字段，一个是name，一个是y。需要注意的是此处使用了Indexbybinary_integer编制该Table的索引项，也可以不写，直接写成：typeTestArrayistableofinfo，如果不写的话使用数组时就需要进行初始化：varArraymyPackage.TestArray;varArray:=newmyPackage.TestArray();endTestArray;5.游标的使用Oracle中Cursor是非常有用的，用于遍历临时表中的查询结果。其相关方法和属性也很多，现仅就常用的用法做一二介绍：(1)Cursor型游标(不能用于参数传递)createorreplaceproceduretest()iscusor_1Cursorisselectstd_namefromstudentwhere.;-Cursor的使用方式1cursor_2Cursor;beginselectclass_nameintocursor_2fromclasswhere.;-Cursor的使用方式2可使用ForxincursorLOOP.endLOOP;来实现对Cursor的遍历endtest;(2)SYS_REFCURSOR型游标，该游标是Oracle以预先定义的游标，可作出参数进行传递createorreplaceproceduretest(rsCursoroutSYS_REFCURSOR)iscursorSYS_REFCURSOR;namevarhcar(20);beginOPENcursorFORselectnamefromstudentwhere.-SYS_REFCURSOR只能通过OPEN方法来打开和赋值LOOPfetchcursorintoname-SYS_REFCURSOR只能通过fetchinto来打开和遍历exitwhencursor%NOTFOUND;-SYS_REFCURSOR中可使用三个状态属性：-%NOTFOUND(未找到记录信息)%FOUND(找到记录信息)-%ROWCOUNT(然后当前游标所指向的行位置)dbms_output.putline(name);endLOOP;rsCursor:=cursor;endtest;实例下面写一个简单的例子来对以上所说的存储过程的用法做一个应用：现假设存在两张表，一张是学生成绩表(studnet)，字段为：stdId,math,article,language,music,sport,total,average,step一张是学生课外成绩表(out_school),字段为:stdId,parctice,comment通过存储过程自动计算出每位学生的总成绩和平均成绩，同时，如果学生在课外课程中获得的评价为A，就在总成绩上加20分。createorreplaceprocedureautocomputer(stepinnumber)isrsCursorSYS_REFCURSOR;commentArraymyPackage.myArray;mathnumber;articlenumber;languagenumber;musicnumber;sportnumber;totalnumber;averagenumber;stdIdvarchar(30);recordmyPackage.stdInfo;inumber;begini:=1;get_comment(commentArray);-调用名为get_comment()的存储过程获取学生课外评分信息OPENrsCursorforselectstdId,math,article,language,music,sportfromstudenttwheret.step=step;LOOPfetchrsCursorintostdId,math,article,language,music,sport;exitwhenrsCursor%NOTFOUND;total:=math+article+language+music+sport;mentArray.countLOOPrecord:=commentArray(i);ifstdId=record.stdIment=Athenbegintotal:=total+20;gotonext;-使用goto跳出for循环end;endif;end;endif;endLOOP;average:=total/5;updatestudenttsett.total=totalandt.average=averagewheret.stdId=stdId;endLOOP;end;endautocomputer;-取得学生评论信息的存储过程createorreplaceprocedureget_comment(commentArrayoutmyPackage.myArray)isrsSYS_REFCURSOR；recordmyPackage.stdInfo;stdIdvarchar(30);commentvarchar(1);inumber;beginopenrsforselectstdId,commentfromout_schooli:=1;LOOPfetchrsintostdId,comment;exitwhenrs%NOTFOUND;record.stdId:=stdId;ment:=comment;recommentArray(i):=record;i:=i+1;endLOOP;endget_comment;-定义数组类型myArraycreateorreplacepackagemyPackageisbegintypestdInfoisrecord(stdIdvarchar(30),commentvarchar(1);typemyArrayistableofstdInfoindexbybinary_integer;endmyPackage;byebyePL/SQL的存储过程和函数(原创) 博客分类： PL/SQL存储过程概述存储过程是子程序的一种类型，能够完成一些任务，作为schema对象存储于数据库。是一个有名字的PL/SQL代码块，支持接收或不接受参数，同时也支持参数输出。一个存储过程通常包含定义部分，执行部分，Exception部分，可以被其他子程序调用，也可以被重用。过程定义CREATE OR REPLACEPROCEDURE procedure_name(argument_name IN | OUT | IN OUT argument_type)AS | ISBEGIN procedure_body;END procedure_name;存储过程中参数的类型IN：表示是一个输入参数，可以指定缺省值。如省略参数类型，则缺省为in类型OUT：表示是一个输出参数IN OUT：既可以作为一个输入参数，也可以作为一个输出参数来输出结果过程调用EXECUTE CALL procedure_name (argument_list)例：定义一个过程，以JOB为参数，查询该JOB的最高工资、最低工资、平均工资。create or replace procedure query_sal(v_job in emp.job%type) as v_min_sal emp.sal%type; v_max_sal emp.sal%type; v_avg_sal emp.sal%type;begin select min(sal) into v_min_sal from emp where job = v_job; select max(sal) into v_max_sal from emp where job = v_job; select avg(sal) into v_avg_sal from emp where job = v_job; dbms_output.put_line(This job is minimum salary is | v_min_sal); dbms_output.put_line(This job is maximum salary is | v_max_sal); dbms_output.put_line(This job is average salary is | v_avg_sal);exception when no_data_found then dbms_output.put_line(Not Record Found);end;SQL set serveroutput onSQL exec query_sal(SALESMAN);This job is minimum salary is 1250This job is maximum salary is 1600This job is average salary is 1400PL/SQL procedure successfully completed.参数及其传递方式在 建立过程时，传递的参数为可选项，如果省略参数选项，则过程为无参过程（定义时不指定参数，调用时也不需要参数）。如果指定参数选项，则过程为有参过程 （定义时需要指定参数名字、模式、数据类型，调时时需要给出对应的参数值），定义时的参数，称为形参，调用时的参数称为实参。无参过程create or replace procedure display_systimeasbegin dbms_output.put_line(Current Time is | sysdate);end;SQL exec display_systime;Current Time is 24-FEB-13有参过程定义时需要指定参数的名字、模式、数据类型例：定义一个添加记录的过程（全部为输入参数）create or replace procedure add_emp(v_no in emp.empno%type,v_name in emp.ename%type,v_dept in emp.deptno%type default 20 -缺省的部门号)asbegin insert into emp (empno,ename,deptno) values(v_no,v_name,v_dept);exception when dup_val_on_index then dbms_output.put_line(Record Exists);end ;SQL exec add_emp(7369,TEST,20); -调用Record Exists例：定义一个输入员工编号，修改记录，再返回修改后的结果（姓名和工资）。create or replace procedure ed_emp(v_no in emp.empno%type, -定义了一个in类型，二个out类型的参数v_name out emp.ename%type,v_sal out emp.sal%type)asbegin update emp set sal = sal + 100 where empno = v_no; select ename,sal into v_name,v_sal from emp where empno = v_no;exception when no_data_found then dbms_output.put_line(Not Data Found);end;/Procedure created.SQLVARIABLE t_name varchar2(20);SQLVARIABLE t_sal number;SQL exec ed_emp(7369,:t_name,:t_sal);PL/SQL procedure successfully completed.SQL print t_nameT_NAME-SMITHSQL print t_sal T_SAL- 900例：IN OUT类型参数的使用create or replace procedure comp(num1 in out number,num2 in out number)as v1 number; v2 number;begin v1 := num1 + num2; v2 := num1 * num2; num1 := v1; num2 := v2;end;SQL var v1 numberSQL var v2 numberSQL exec :v1 := 3PL/SQL procedure successfully completed.SQL exec :v2 := 5PL/SQL procedure successfully completed.SQL exec comp(:v1,:v2);SQL print v1 v2 V1- 8 V2- 15SQL exec comp(:v1,:v2);PL/SQL procedure successfully completed.SQL print v1 v2 V1- 23 V2- 120可以看到in out类型的参数既作为输入参数又作为输出参数。存储过程参数的传递方式：按位置传递：实参按顺序将值传给形参EXECUTE ED_EMP(7900,:t_name,:t_sal);EXECUTE ED_EMP(8000,TEST2,20);按名字传递EXECUTE ED_EMP(v_name=ABCDE,v_dept=10,v_no=8003); 混合传递EXECUTE ED_EMP(8005,v_dept=20,v_name=TEST5);过程管理查看系统过程信息DBA_OBJECTSDBA_PROCEDURESDBA_SOURCE使用desc procedure_name 查看存储过程的参数信息SQLdesc ed_emp;PROCEDURE ed_empArgument Name Type In/Out Default?- - - -V_NO NUMBER(4) INV_NAME VARCHAR2(10) OUTV_SAL NUMBER(7,2) OUT 从dba_objects获得存储过程的信息SQLselect owner,object_name,object_type,status from dba_objects where object_name = ED_EMP;OWNER OBJECT_NAME OBJECT_TYPE STATUS- - - -SCOTT ED_EMP PROCEDURE VALID SQLselect object_name,procedure_name,interface,authid from user_procedures;OBJECT_NAME PROCEDURE_NAME INT AUTHID- - - -DISPLAY_SAL NO DEFINERED_EMP NO DEFINER查看存储过程的源代码SQLselect line, text from user_source where name=ED_EMP;LINE TEXT- - 1 PROCEDURE ed_emp 2 ( 3 v_no IN emp.empno%TYPE, 4 v_name OUT emp.ename%TYPE, 5 v_sal OUT emp.sal%TYPE 6 ) 7 AS 8 BEGIN 9 UPDATE emp SET sal=sal+100 WHERE empno=v_no;10 SELECT ename,sal INTO v_name,v_sal FROM emp WHERE empno=v_no;11 EXCEPTION12 WHEN NO_DATA_FOUND THEN13 DBMS_OUTPUT.PUT_LINE(NOT FOUND RECORD!);14 END ed_emp;查看错误信息SHOW ERRORS函数概述函数通常用于返回特定的数据。其实质是一个有名字的PL/SQL块，作为一个schema对象存储于数据库，可以被反复执行。函数通常被作为一个表达式来调用或存储过程的一个参数，具有返回值。一、建立函数的语法CREATE OR REPLACE FUNCTION function_name (argument1 mode1 datatype1, argument2 mode2 datetype2, .)RETURN datatypeIS | AS local_variable_declarations;.BEGIN -actions; RETURN expression;END function_name;建立函数的几点注意事项1.指定参数数据类型时(argument)，不能指定其长度2.函数头部必须指定return子句，函数体内至少要包含一条return语句3.可以指定in参数，也可以指定out参数，以及in out 参数4.可以为参数指定缺省值。指定缺省值时使用default关键字。如arg1 varchar2 default SCOTT使用函数的优点:1.增加了代码的灵活性，可以完成一些较为复杂的任务，以及仅仅通过SQL无法完成的任务2.可以直接将函数使用到where子句中来过滤数据3.可以作为存储过程的参数使用，是存储过程的一种补充建立函数建立不带参数的函数create or replace function get_userreturn varchar2as v_user varchar2(20);begin select username into v_user from user_users; return v_user;end;使用全局变量接收函数的返回值SQL var v1 varchar2(20);SQL exec :v1 := get_user;PL/SQL procedure successfully completed.SQL print v1V1-SCOTT使用本地变量接收函数的返回值SQL declare user_name varchar2(20); 2 begin 3 user_name := get_user(); 4 dbms_output.put_line(Current User: | user_name); 5 end; 6 /Current User: SCOTTPL/SQL procedure successfully completed.在SQL语句中直接调用函数SQL select get_user from dual;GET_USER-SCOTT使用dbms_output调用函数(此调用作为存储过程的一个参数来进行调用) SQL set serveroutput on;SQL exec dbms_output.put_line(Current user: |get_user);Current user: SCOTT 建立带有in 参数的函数create or replace function raise_sal(name in varchar2)return numberas new_sal emp.sal%type;begin select sal * 1.2 into new_sal from emp where upper(ename) = upper(name); return new_sal;exception when no_data_found then raise_application_error(-20000,Current Employee does not exists);end;SQL select sal,raise_sal(SCOTT) from emp where ename=SCOTT; SAL RAISE_SAL(SCOTT)- - 3000 3600SQL select sal,raise_sal(SCOTTT) from emp where ename=SCOTT;select sal,raise_sal(SCOTTT) from emp where ename=SCOTT *ERROR at line 1:ORA-20000: Current Employee does not existsORA-06512: at SCOTT.RAISE_SAL, line 11建立带有out参数的函数create or replace function get_info(name varchar2,titile out varchar2)return varchar2as deptname dept.dname%type;begin select e.job,d.dname into titile,deptname from emp e,dept d where e.deptno = d.deptno and upper(e.ename) = upper(name); return deptname;exception when no_data_found then raise_application_error(-20000,Current Employee does not exists);end;/注意对于使用out参数的函数，不能使用SQL语句来调用。而必须定义变量接收out参数和函数的返回值。调用如下SQL var job varchar2(20);SQL var dname varchar2(20);SQL exec :dname := get_info(scott,:job);PL/SQL procedure successfully completed.SQL print dname jobDNAME-RESEARCHJOB-ANALYSTSQL select get_info(scott) from dual 2 ;select get_info(scott) from dual *ERROR at line 1:ORA-06553: PLS-306: wrong number or types of arguments in call to GET_INFO建立带有in out参数的函数create or replace function comp(num1 number,num2 in out number)return numberas v_result number(6); v_remainder number;begin v_result := num1 * num2; v_remainder := mod(num1,num2); num2 := v_remainder; return v_result;exception when zero_divide then raise_application_error(-20000,Divsion by zero);end;/SQL var n1 number SQL var n2 numberSQL exec :n2 := 10;PL/SQL procedure successfully completed.SQL exec :n1 := comp(16,:n2);PL/SQL procedure successfully completed.SQL print n1 n2 N1- 160 N2- 6函数的调用及限制1.函数的调用(其具体调用方法参照上面的演示)a.使用全局变量接收函数的返回值b.使用本地变量接受函数的返回值c.在SQL语句中直接调用函数d.使用dbms_output调用函数注：函数在调用的时候需要按位置指定参数，没有存储过程参数传递灵活必须具有execute 函数的权限2.函数在SQL中调用的主要场合由于函数必须要返回数据，因此只能作为表达式的一部分调用。此外函数可以在SQL语句的以下部分调用a. select 命令的选择列表或子查询中b. 条件表达式where, having子句中c. connect by , start with ,order by 以及group by 子句中d