首页 > 代码库 > 项目中oracle存储过程记录——常用语法备忘

项目中oracle存储过程记录——常用语法备忘

项目中oracle存储过程记录——常用语法备忘

 

项目中需要写一个oracle存储过程,需求是收集一个复杂查询的内容(涉及到多张表),然后把符合条件的记录插入到目标表中。其中原表之一的日期字段是timestamp类型,目标表的字段是varchar2类型;

       其中一些内容很常用,所以做下记录,供查找。

1、存储过程的格式

       oracle存储过程和函数都可以实现,一般没有返回值,则采用存储过程,函数比sqlserver的功能强大。oracle变量定义最好加上前缀如V_,查询条件中变量名称和字段名称不能重复。

CREATEOR REPLACE PROCEDURE PROC_NAME (

  v_interval IN NUMBER DEFAULT -3 –PARAM_NAME_LIST

)AS

  v_cnt            number(4); -- 定义变量及游标

BEGIN

--业务逻辑语句

ENDPROC_NAME

 

2、游标定义和循环

cursor  CURSOR_NAME is

              SELECT * FROM DUAL – SELECT 语句;

循环游标有多种方式,最简单的for方式,避免定义一些变量及打开、关闭游标,可以简化很多代码,但是如果需要访问游标记录条数,就需要loop或while循环。

forloop 语法:

FOR curRow   IN CURSOR_NAME  -- curRow是游标的行记录变量

LOOP

--直接通过curRow.游标字段取值(省略了变量的定义)

ENDLOOP;

 

3、日期的加减计算

DATE类型是一个7字节的定宽日期/时间数据类型。它总是包含7个属性,包括:世纪、世纪中哪一年、月份、月中的哪一天、小时、分钟和秒;TIMESTAMP类型与DATE非常类似,只不过另外还支持小数秒和时区。下面的N值可以为负数。

 

?  使用NUMTODSINTERVAL内置函数来增加小时、分钟和秒。

比如:DATE+NUMTODSINTERVAL(n,‘minute‘)

 

?  加一个简单的数来增加天。

比如:DATE+n

 

?  使用ADD_MONTHS内置函数来增加月和年。

比如:ADD_MONTHS(DATE,n)

 

4、日期类型转成字符

       select to_char(systimestamp,‘yyyy-mm-dd hh24:mi:ssxff‘)time1 from dual;

       --年月日时分秒及6位毫秒;

       select to_char(systimestamp  ,‘yyyy-mm-dd hh24:mi:ss.ff1‘) from dual;

       --年月日时分秒及毫秒(位数由ff后面的数字决定,1~9之间,ff3表示保留三位毫秒)

 

5、if exist 替代语法

       oracel没有sqlserver的if exist 语法,只能变形实现,建议使用下面的语法:

v_cnt            number(4); -- 声明变量;

selectcount(*) into v_cnt from dual where exists (SELECT 语句);

示例:

declare

  v_cnt number;

begin

  select count(*)  into v_cnt from dual

        where exists (select * from table_namewhere col_name=1);

  if v_cnt = 0 then

dbms_output.put_line(‘无记录, 在此写你的业务代码‘);

endif;

end;

 

6、空值

       如果字段不允许为空,使用nvl函数;如:nvl(field_name,’ ’),需要说明的两个单引号直接是空格,如果没有任何字符,oracle也视为null。

 

7、调试存储过程

       复杂的业务一般都需要调试,感觉PLSQLDeveloper调试比较方便。右键选择需要调试的存储过程,在测试窗口即可单步调试。


项目中oracle存储过程记录——常用语法备忘