Oracle数据库迁移培训 FINAL

Oracle 数据迁移修改要点
（前台SQL的修改）
• 关键字：group by （存储过程适用）
在Informix中，GROUP BY语句可以写成如下形式：GROUP BY 1,2,3。 但在Oracle中，这种语法不被支持，只能写成GROUP BY COL_NAME1, COL_NAME2, COL_NAME3。 注：order by ？
Oracle存储过程简介
存储过程if控制
IF str1 > str2 then result :=1; ELSIF str2 > str1 THEN result := -1; ELSE result := 0; END IF;
Oracle存储过程简介
存储过程if条件表达式
• • • • • •
Oracle 数据迁移修改要点
前台SQL的修改
Oracle 数据迁移修改要点
（前台SQL的修改）
• 关键字：first （存储过程适用）
对于first语句，如果不包含排序，例如"select frist 1 * from tabx"，可以修改为" select * from tabx where rownum<=1"； 如果包含排序，"select frist 1 * from tabx order by pid"，则只能修改为子查询的形式，在子查询 内部排序，然后在外部增加rownum条件，例如 "select * from (select * from tabx order by pid ) where rownum<=1"
Oracle 数据迁移修改要点
（前台SQL的修改）
• 关键字：outer （存储过程适用）
– 当只有1个outer时，需要在WHERE子句中这个表所有字段的后面加上(+)标识。
例如：查询所有团队，及其所辖26职级的人： Informix: "select * from t50_tdept b, outer(t01_psn a) where a.tid=b.tid and a.t01actrank='26' " Oracle: "select * from t01_psn a, t50_tdept b where a.tid(+)=b.tid and a.t01actrank(+)='26' "
+ * /
如：sp_str1=“ABC”||”DEF” ,则： sp_str1=“ABCDEF”
• || 合并
Oracle存储过程简介
存储过程异常控制
Exception when too_many_rows then DBMS_OUTPUT.PUT_LINE('返回值多于1行'); when others then DBMS_OUTPUT.PUT_LINE('在RUNBYPARMETERS 过程中出错！');
Oracle存储过程简介
存储过程的建立
编写后缀名为sql的文件，一个存储过程一个文件。
过程格式：
CREATE OR REPLACE PROCEDURE 存储过程名字 ( 参数1 IN NUMBER, 参数2 IN NUMBER ) IS 变量1 INTEGER :=0; 变量2 DATE; BEGIN END 存储过程名字;
比较符 <， >，<=，>=，=，<>，!= AND， OR， NOT (NOT) BETWEEN AND (NOT) IN ( , , , ,) IS (NOT) NULL (NOT) LIKE
Oracle存储过程简介
%type定义方法
• 在pl/sql中可以将变量和常量声明为内建或用户定义
的数据类型,以引用一个列名,同时继承他的数据类型 和大小. 注: v_a number(5):=10; v_b v_a%type:=15; v_c v_a%type;
BEGIN FOR cur_result REVERSE 1..5 LOOP BEGIN DBMS_OUTPUT.PUT_LINE(cur_result ); END; END LOOP; END;
Oracle存储过程简介
存储过程while循环
Counter :=0; WHILE counter < 6 LOOP counter :=counter +1; END LOOP;
Oracle存储过程简介
存储过程变量
• • • •
先声明且必须声明才能使用。 Begin end 块外声明的变量影响全局。 Begin end 块内声明的变量影响本Begin end 。 变量声明必须在存储过程开头或者Begin end 块的开 头部分
Oracle存储过程简介
存储过程操作符
• • • •
• 注释 : 同一存储过程中，变量名最好不要重复。
Oracle存储过程简介
存储过程赋值语句
• :=
• • • •
realjob := „work‟ ; Product_id := 100001 ; realname := „张三'; Price := 3.1415;
• this_day := TODAY;
Oracle存储过程简介
存储过程变量定义、常用变Βιβλιοθήκη Baidu类型
• • • • •
realsal realname realjob Price Product_id emp.sal%type; --与emp表中的sal字段类型相同 varchar2(40); varchar2(40); number(5,2); -- 543.21 interger;
Oracle存储过程简介
函数结构
CREATE OR REPLACE FUNCTION 函数名(
参数1 IN NUMBER, 参数2 IN NUMBER
) RETURN 类型 IS | AS BEGIN FUNCTION _BODY END; 注: 函数与存储过程相似,唯一区别函数必须向调用他的语句返回一个值,它与存 储过合起来被称为存储子程序,从某种意义讲为小程序.
Oracle存储过程简介
包头和包体结构
CREATE [OR REPLACE ] PACKAGE 包名 { IS | AS } PACKAGE _SPECIFICATION END包名; CREATE [OR REPLACE ] PACKAGE BODY 包名 { IS | AS } PACKAGE 过程 END包名; 注: PACKAGE _SPECIFICATION 是指包的用户使用的过程和函数的列表. PACKAGE 过程 是指包的用户可以使用的存储过程和函数的列表(包含变量,类型, 定义和游标) 包主要分为 :规范和包体.它的用途主要是把存储过程和函数组织到包中.通过 PL/SQL代码模块化,构 建其他编程人员重用的代码库.
– 当有2个或多个outer时，需要写成标准的outer join 的形式，例如
informix："select * from filelines t1,outer(filelines t2), outer(pathlines t3 ） where t1.lines=t2.lines and t1.path=t3.path" Oracle:"select * from filelines t1 left outer join filelines t2 on t1.lines=t2.lines left outer join pathlines t3 on t1.path=t3.path"
Oracle存储过程简介
运行查看信息
• 1.SQL>EXECUTE skeleton; •
注释(SQL*Plus输出一下信息确信存储过程成功执 即PL/SQLproceduresuccessfullycompleted). 2.在SQL*Plus命令行提示符,键入: SQL>SET SERVEROUTPUT ON 再次敲入SQL>EXECUTE skeleton即可. 注释: 查看存储过程中的打印语句信息,实行以上命令.
Oracle存储过程简介
存储过程结构块
BEGIN BEGIN 第一步处理； END; BEGIN 第二步处理； END; BEGIN 第三步处理； END; END;
Oracle存储过程简介
存储过程游标
• 带参数的游标
CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID = C_ID NUMBER;
OPEN C_USER(变量值); LOOP FETCH C_USER INTO V_NAME; EXIT WHEN C_USER%NOTFOUND; END LOOP; CLOSE C_USER;
Oracle存储过程简介
存储过程游标
• 不带参数的游标
CURSOR C_USER IS SELECT NAME FROM USER WHERE TYPEID = C_ID NUMBER;
Oracle 数据迁移
• Oracle存储过程简介 • Informix转Oracle修改要点
– 前台修改 – 后台修改
• 研发中心测试环境 • 存储过程测试方法
Oracle存储过程简介
• • • • • • • • •
存储过程的建立 存储过程的语法结构 存储过程的控制语句 存储过程的开发 存储过程的运行 存储过程的调试 Informix与oracle存储过程的差异 函数 包
OPEN C_USER; LOOP FETCH C_USER INTO V_NAME; EXIT WHEN C_USER%NOTFOUND; END LOOP; CLOSE C_USER;
Oracle存储过程简介
存储过程LOOP循环
Counter :=0; LOOP counter :=counter +1 ; EXIT WHEN counter =5; END LOOP;
Oracle存储过程简介
删除一个存储过程
• 1.在SQL>命令提示符下输入以下命令： • 命令 : SQL>DROP PROCEDURE 存储过程名;
存储过程的注释
• -- 注释一行
Oracle存储过程简介
存储过程入参与返回值
create or replace procedure runbyparmeters ( isal in emp.sal%type, sname out varchar, sjob in out varchar) as icount number; begin if icount=1 then .... else .... end if; end;
Oracle存储过程简介
创建一个简单存储过程的方式
• 1. SQL*Plus并且从SQL*Plus登录到你的数据库;打开test.sql文件. • 2.在SQL>命令提示符下输入以下命令：
•
SQL>@skeleton 注释:(SQL*Plus装载stest.sql文件的内容到SQL*Plus缓冲区, 并且执行SQL*Plus语句;SQL*Plus会通知你存储过程已经被成功地创建) 3.写一个存储过程,实例: CREATE OR REPLACE PROCEDURE test IS BEGIN DBMS_OUTPUT.PUT_LINE('HelloWorld!'); END;
Oracle存储过程简介
存储过程调用方式（２种方式）
Declare realsal emp.sal%type; realname varchar(40); realjob varchar(40); BEGIN realsal:=1100; realname:=''; realjob:='CLERK'; （1） runbyparmeters(realsal,realname,realjob); －－必须按顺序 （2） runbyparmeters(sname=>realname,isal=>realsal,sjob=>realjob); --不按顺序 DBMS_OUTPUT.PUT_LINE(REALNAME||' '||REALJOB); (输出模式) END;
Oracle存储过程简介
存储过程for循环 • 方式一:
BEGIN FOR cur_result in cur(结果集) LOOP BEGIN V_SUM :=cur_result.列名1+cur_result.列名2 ; END; END LOOP; END;
Oracle存储过程简介
存储过程for循环 • 方式二: