oracle开发常用,oracle开发常用

****************************************************************

*                                                              *

*                   oracle开发常用                             *

*                                                              *

****************************************************************

prompt  打印功能

spool

功能：将窗口上spool  ...和spool off之间的所有字符保存到相应的文件

语法：1.spool  [保存路径]  [选项]

2.spool off

选项：选项有三个：新建(create)、追加(不记得了)、重写(压根不记得)，默认的就是重写

package

用package不仅能把存储过程分门别类，而且在package里可以定义公共的变量/类型，既方便了编程，又减少了服务器的编译开销。

select count(*) as y0_ from BLACK_LIST this_ where CONVERT(varchar,locked_time,112)>=2012-12-03 and this_.TENANT_ID=?

select count(*) as y0_ from BLACK_LIST this_ where CONVERT(varchar,locked_time,112)>=2012-12-03 and CONVERT(varchar,locked_time,112)<=2012-12-06 and this_.TENANT_ID=?

****************************************************************

*                                                              *

*                   sql server开发常用                         *

*                                                              *

****************************************************************

字符串截取

select * from BLACK_LIST where CONVERT(varchar,locked_time,112)>='2012-12-03'

and CONVERT(varchar,locked_time,112)<='2012-12-06';

convert(date,CONF_DATE,20)

convert(datetime,CONF_DATE,20)

substring(convert(varchar,RESCHEDULED_TIME, 20),0,17)

获取系统时间

select getdate() as dte,dateadd(day,-1,getdate()) as nowdat

数据类型转换，varchar2>varchar

属性转换，j>@j

符号：初始赋值转换，j int :=0 > @j int;set @j=0;    ||换成+号   ;去掉

函数的转换，length()>len()

while 中Loop去掉，把loop..end loop换成begin,end

end if …end 函数名都换成end

exec(@sql)

****************************************************************

*                                                              *

*                   Oracle --> sql server                      *

*                                                              *

****************************************************************

oracle存储过程转化成sqlserver

1. 变量的定义 ，sqlserver变量名前加@. eg: a varchar2(4) --> @a varchar(4).

2. 类型转化，将varchar2转化成varchar. eg: 参上.

3. 变量的赋值，将 :=替换成set. eg: a :='abc' --> set @a = 'abc'.

4. 字符串的拼接，将||替换成+.eg: a = 'ab'||'c' --> a='ab'+'c'.

5. 语句结构,去掉所有;.

6. if结构的改变. eg: if ... then end if; --> if ..begin ..end end.

7. 返回结果集.oracle可以定义游标类型返回.

java中采用注册输出参数，然后获取结果集.

sqlserver中不返回结果集，改成exec(@querySql)

java中采用executeQuery获取结果集.

8. 注意拼接sql语句是，如果需要用单引号，需要用两个单引号进行转义.

1个引号时，而且是前置时，就是表示所辖内容为字符串.

2个引号时，而且是在语句中，表示一个'.第一个为转义

3个引号时，而且是前置时，第一个表示字符串前引号，第二个是转义字符，第三个表示引号(字符内容)

eg：a = ''' 相当于一个'

set @retJSON = '{msg:''获取页面准备数据出错:' + SUBSTRING(ERROR_MESSAGE(), 1, 200) + ''''

/**

* 通用结构

*/

-- 存在则删除

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[XXX]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[XXX]

GO

create procedure XXX(@x1 varchar,...,@put varchar out)

as

begin

-- 定义变量

declare @a  datetime;

declare @b  varchar(20);

declare @c  date;

...;

-- 定义函数体

begin try

begin transaction

-- 变量赋值

set @b = convert(datetime,@x1,120)

select @b=name from t

-- with (xlock holdlock)  表锁直至事务释放

where id =@a

set @a  = @a + 'b' + @b

-- 使用if结构

if @b is not null or @b = 'b'

begin

delete from call_list

where  convert(datetime, substring(call_list.id, 0, 8), 112) <= @queryTime

end

-- 使用游标

declare v_cur cursor for select 1 from a;

open v_cur

fetch next from v_cur into @a

while @@fetch_status=0

begin

...

end

close v_cur

deallocate v_cur -- 删除对游标命名变量的引用

print @a -- 打印语句

--创建call_list索引wsj-begin

set @v_SQL=' create index PKL_TASKID_'+@i_tenantId+' on  CALL_LIST_' + @i_tenantId +' (TASK_FK)'

Exec (@v_SQL)

exec (@querySql)  -- 返回结果集，无需输出参数java可以直接调用获取

commit   end try   begin catch     rollback       set @retCode = '-1'       set @retJSON = '{msg:''获取页面准备数据出错:' + SUBSTRING(ERROR_MESSAGE(), 1, 200) + ''''   end catch end