Oracle存储过程分析和总结.docx

(一)关于编写存储过程需要注意以下几点：1赋值符号:=

逻辑等=

2注释单行代码用 --

注释代码块用 /*---*/3字符串连接符 ||

4变量在变量声明区内进行声明:IS和BEGIN之间

5变量声明方式通常有如下三种：

单个变量

mo_flag NUMBER :=0;

content TABLE.CONTENT%TYPE;

数组

TYPEdata_typeISTABLEOFVARCHAR2(10)INDEXBYBINARY_INTEGER; --引用形式为

data_type(i)

TYPErule_typeISRECORD(data_idTABLE.ID%TYPE,

--引用形式为rule_type(i).data_id

6经常用到的一个内置函数DECODE:DECODE(id,1,Southlake,

data_nameTABLE.NAME%TYPE

);

2,SanFrancisco,3,NewJersey,4,Seattle,

Non-domestic)

当id是1时，返回Southlake；是2时，返回SanFrancisco；依次，如果都不是时，返回Non-domestic

(二)PL/SQL块结构1触发器

CREATEORREPLACETRIGGERtrigger_nameAFTER|BEFOREINSERT|UPDATE|DELETEONtable_nameFOREACHROW

DECLARE

(声明部分)BEGIN

(程序体)EXCEPTION

(异常处理部分)END;

2存储过程

CREATEORREPLACEPROCEDUREname(参数)IS

(声明部分)

BEGIN

(程序体)EXCEPTION

(异常处理部分)END;

3函数

CREATEORREPLACEFUNCTIONfunc_name(参数)RETURNdata_typeIS(声明部分)

BEGIN

(程序体)EXCEPTION

(异常处理部分)END;

注：DECLARE在触发器中进行变量声明的时候需要，存储过程和函数的变量声明时不需要

(三)应用和调试的时候需要注意以下几种应用：1SETSERVEROUTPUTONBUFFER20000;

用dbms_output.put_line(strin_content);可以在存储过程中输出信息，对存储过程进行调试；只有将serveroutput变量设为on后，信息才能显示在屏幕上

2EXECUTEprocedure_name;

执行一个存储过程

3selectfunction()fromdual;

通过sql语句执行一个函数4DBMS_JOB.SUBMIT(job,TEST_WARN_PROC;,sysdate,sysdate+30/(24*60));

表示这个Job的执行频率是每隔30分钟，DBMS_JOB.SUBMIT(任务号,要执行的存储过程,下次执行的时间,每次间隔的时间)

sysdate+x/24 每隔x小时执行一次sysdate+x/(24*60) 每隔x分钟执行一次sysdate+x/(24*60*60) 每隔x秒执行一次

5当报出错误信息的时候，其中指出的行号说明如下：

行号的计数从关键字DECLARE开始，如果没有则BEGIN语句的行号为1

1、用来插入大量测试数据的存储过程

CREATEORREPLACEPROCEDUREINSERTAMOUNTTEST(

ST_NUM

ED_NUM

IN

IN

NUMBER,

NUMBER

)

IS

BEGIN

declare

begin

i number;

FORiINST_NUM..ED_NUMLOOP

INSERTINTOtbvalues(i,i,3,3,3,100,0);ENDLOOP;

end;

END;

运行：

sqlexecuteINSERTAMOUNTTEST(1,45000) --一次插入45000条测试数据

2、从存储过程中返回值

createorreplaceprocedurespaddflowdate(

varAppTypeId invarchar2,

varFlowId invarchar2,

DateLength innumber,

ReturnValue outnumber --返回值

)

isbegin

insertintotdvalues(varAppTypeId,varFlowId,DateLength)returning1