对于oracle 存储过程是很优秀的一种脚本语言。下面是一些整理:
一,Plsql 调试存储过程:
1、 在oracle的 配置和移植工具 àNET MANAGER中配置连接
2、 在plsql中新建SQL窗口,编写存储过程
3、 在plsql中新建测试窗口,在begin end之间调用
4、 查看编译错误:在命令窗口中show errors procedure procedure_name
或者 编辑的方式打开存储过程,在编译时会有错误提示
一,Plsql 调试存储过程:
1、 在oracle的 配置和移植工具 àNET MANAGER中配置连接
2、 在plsql中新建SQL窗口,编写存储过程
3、 在plsql中新建测试窗口,在begin end之间调用
4、 查看编译错误:在命令窗口中show errors procedure procedure_name
或者 编辑的方式打开存储过程,在编译时会有错误提示
二,oracle存储过程基本语法
1、 oracle存储过程结构:
CREATE OR REPLACE PROCEDURE 存储过程名
(
输入输出参数
)
IS
变量定义
BEGIN
代码;
END 存储过程名;
2、 if语句:
If 逻辑表达式 then
Begin
End;
End if;
If 逻辑表达式 then
Begin
End;
Else
Begin
End;
End if;
If 逻辑表达式 then
Begin
End;
Elseif 逻辑表达式 then
Begin
End;
Else
Begin
End;
End if;
3、 For循环
For in loop
…
End loop;
E.g:
CREATE OR REPLACE PROCEDURE TEST
is
BEGIN
for i in 1..100 LOOP
DBMS_OUTPUT.put_line(i);
end LOOP;
END TEST;
注:这里的i可以不用申明,并且在循环体中,i会自增
4、 while循环
While 条件语句 loop
Begin
End;
End LOOP;
E.g:
CREATE OR REPLACE PROCEDURE TEST
is
i int := 1;
BEGIN
while i < 100 LOOP
DBMS_OUTPUT.put_line(i);
i:=i+1;
end LOOP;
END TEST;
注:这里的i需要申明,并且循环体中,要对i进行改变
5、 游标的使用:
定义游标
打开游标
提取数据
关闭游标
E.g:
CREATE OR REPLACE PROCEDURE TEST
is
v_fid nvarchar2(80);
Cursor cur_test is
select fid from t_pm_user;--定义游标
BEGIN
open cur_test;--打开游标
if cur_test%notfound
then --判断游标中数据是否为空
rollback;
end if;
loop--循环游标
fetch
cur_test into v_fid;--提取游标数据
DBMS_OUTPUT.put_line(v_fid);
EXIT
WHEN cur_test%NOTFOUND; --游标数据为空后退出循环
end loop;
close cur_test;--关闭游标
END TEST;
三,oracle与sqlserver部分差异
1、 常用函数差异
Ø 绝对值
S:select abs(-1) value
O:select abs(-1) value from dual
Ø 取整(大)
S:select ceiling(-1.001) value
O:select ceil(-1.001) value from dual
Ø 取整(小)
S:select floor(-1.001) value
O:select floor(-1.001) value from dual
Ø 取整(截取)
S:select cast(-1.002 as int) value
O:select trunc(-1.002) value from dual
Ø 四舍五入
S:select round(1.23456,4) value 1.23460
O:select round(1.23456,4) value from dual 1.2346
Ø 取随机数
S:select rand() value
O:select sys.dbms_random.value(0,1) value from dual
Ø 如何处理null值(F2中的null以10代替)
S:select F1,IsNull(F2,10) value from Tbl
O:select F1,nvl(F2,10) value from Tbl
Ø 连接
S:select '11'+'22' value
O:select CONCAT('11','22') value from dual
Ø 子串位置 --返回3
S:select CHARINDEX('s','sdsq',2) value
O:select INSTR('sdsq','s',2) value from dual
Ø 求子串
S:select substring('abcd',2,2) value
O:select substr('abcd',2,2) value from dual
Ø 子串代替 返回aijklmnef
S:SELECT STUFF('abcdef', 2, 3, 'ijklmn') value
O:SELECT Replace('abcdef', 'bcd', 'ijklmn') value from dual
Ø 子串全部替换
S:没发现
O:select Translate('fasdbfasegas','fa','我' ) value from dual
Ø 长度
S:len,datalength
O:length
Ø 左补空格(LPAD的第一个参数为空格则同space函数)
S:select space(10)+'abcd' value
O:select LPAD('abcd',14) value from dual
Ø 右补空格(RPAD的第一个参数为空格则同space函数)
S:select 'abcd'+space(10) value
O:select RPAD('abcd',14) value from dual
Ø 删除空格
S:ltrim,rtrim
O:ltrim,rtrim,trim
Ø 系统时间
S:select getdate() value
O:select sysdate value from dual
Ø 求日期
S:select convert(char(10),getdate(),20) value
O:select trunc(sysdate) value from dual
select to_char(sysdate,'yyyy-mm-dd') value from dual
Ø 求时间
S:select convert(char(8),getdate(),108) value
O:select to_char(sysdate,'hh24:mm:ss') value from dual
Ø 字符串转时间
S:可以直接转或者select cast('2004-09-08'as datetime) value
O:SELECT To_date('2004-01-05 22:09:38','yyyy-mm-dd hh24-mi-ss') vaule FROM
DUAL
Ø 求两日期某一部分的差(比如秒)
S:select datediff(ss,getdate(),getdate()+12.3) value
O:直接用两个日期相减(比如d1-d2=12.3)
SELECT (d1-d2)*24*60*60 vaule FROM DUAL
Ø 根据差值求新的日期(比如分钟)
S:select dateadd(mi,8,getdate()) value
O:SELECT sysdate+8/60/24 vaule FROM DUAL
2、 其它差异
Ø 返回记录集
S:直接在存储过程中用select即可
O:一般在参数中定义一个游标类型的输出参数,在过程体中用 open 游标名称 for select 的方式返回记录集
Ø 代码格式
S:语句结尾不用加分号
O:语句结尾需要加分号
Ø 临时表
S:一般动态创建临时表,临时表是一种”内存表”,不用后会自动删除表结构
O:提前创建好临时表,oracle中临时表保存的是一个会话或者事务的数据,当断开连接或事务提交回滚后,临时表中的数据自动清空(清空的只是当前会话的临时表数据),但表结构还存在。由于Oracle不是一种内存中的数据库.
所以如果Oracle类似SQL Server 频繁的对临时表进行建立和删除,必定会影响性能.所以Oracle会保留临时表的定义直到用户DROP
TABLE。
Ø 自增长字段
S:有专门的自增长字段
O:需要在数据库中创建序列(sequence),使用时用序列名称. Nextval获得
Ø 执行字符串的sql语句
S:exec(sql字符串)
O:execute immediate sql字符串;
Ø 字符串连接
S:用+号
O:用||号