oracle存储脚本案例

一、脚本数据迁移

DECLARE
–申明字段最好不要定义与表字段一样的不然可能会冲突：就像下面的更新一样。
ln_count number(3);
sql_sys_category_id number(12);
sys_group_id number(12);
sys_category_pid number(12);
–for循环
BEGIN
DELETE FROM SYS_CATEGORY WHERE SYS_CATEGORY_NAME=‘模型迁移系统分组’;
FOR cr IN (select a.* from(
SELECT
DISTINCT
ID
FROM
SYS_TENANT
WHERE
DEL_FLAG = 0
AND status = 1
AND ID NOT IN (- 1, 0)
) a ) loop --数目计算提交 0
–查询用into 赋值给定义的变量
select count(*) INTO ln_count from nai_model where tenant_id=cr.ID;
–if判断是否存在，存在就停止，否则继续
if ln_count = 0 then
–提示表中没有数据
continue;
end if;
select GROUP_ID INTO sys_group_id from SYS_GROUP where parent_group_id=-1 and tenant_id=cr.ID;
select sys_category_id INTO sys_category_pid from sys_category where group_id=sys_group_id and sys_category_pid=-1 and sys_category_type=‘sys-category’;
–给变量赋值并生成下一个序列号
sql_sys_category_id:= SEQ_SYS_CATEGORY.nextval;
insert into sys_category (SYS_CATEGORY_ID, SYS_CATEGORY_CODE, SYS_CATEGORY_NAME, SYS_CATEGORY_TYPE, SYS_CATEGORY_PID, GROUP_ID, TENANT_ID, CREATE_DATE, CHANGE_DATE, CREATOR_ID, DEL_FLAG)
values (sql_sys_category_id, lower(DBMS_RANDOM.STRING(‘X’,12)), ‘模型迁移系统分组’, ‘sys-category’, sys_category_pid, sys_group_id, cr.ID, SYSDATE, SYSDATE, 12, 0);

UPDATE DAST_CONTAINER_APP T1
SET T1.SYS_CATEGORY_ID = sql_sys_category_id where T1.SYS_CATEGORY_ID in (
SELECT
SYS_CATEGORY_ID
FROM
SYS_CATEGORY T3
WHERE
T3.SYS_CATEGORY_TYPE = ‘project’
AND T3.DEL_FLAG = 0 and T3.TENANT_ID = cr.ID);

–更新模型
UPDATE NAI_MODEL T1
SET T1.SYS_CATEGORY_ID =sql_sys_category_id where T1.SYS_CATEGORY_ID in (
SELECT
SYS_CATEGORY_ID
FROM
SYS_CATEGORY T3
WHERE
T3.SYS_CATEGORY_TYPE = ‘project’
AND T3.DEL_FLAG = 0 and T3.TENANT_ID = cr.ID );
END loop ;
–结束循环提交
COMMIT ;
END ;

–当等于5000条时候就提交重新赋值
if v_deal_count=5000 then
commit;
v_deal_count :=0;
end if;

机顶盒用户迁移说明

二、跨库操作

将客户中心的itv_users，USERS，customer3张表联合查询的数据插入到计费中心的CSC_ITV_USER中，ccs_nd为客户中心的数据库名称，csc为计费中心的数据库名称
1.登录数据库计费中心
2.判断序列化是否存在：
SEQ_CSC_ITV_USER为计费中心自增序列号
select SEQ_CSC_ITV_USER.nextval as v_itv_user_no from dual
不存在的话创建序列号：
CREATE SEQUENCE “CSC”.“SEQ_CSC_ITV_USER” MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE;

3…执行脚本
DECLARE
v_itv_user_no number(15);
v_deal_count number(8);
begin
v_deal_count:=0;
for cr in (select a.* from(
SELECT
distinct
A .user_id,
A .user_name,
b.msisdn,
b.customer_id,
c. NAME customer_name,
A .home_city,
A .status,
to_char(A .create_time, ‘YYYYMMDDHH24MISS’) create_time,
to_char(sysdate,‘yyyyMMddHH24miss’) change_time,
0 DEL_FLAG,
A .payment_user_id,
to_char(A .stop_time, ‘YYYYMMDDHH24MISS’) stop_time,
A .order_level,
A .business_type,
A .sp_id,
A .business_class,
304275 BUSINESS_ID,
0 SMART_SHORT_STATUS
FROM
ccs_nd.itv_users A,
ccs_nd.USERS b,
ccs_nd.customer c
WHERE
A .payment_user_id = b.user_id
AND A .home_city = b.home_city
AND A .sp_id IN (100001, 100002)
AND A.BUSINESS_TYPE IN (6, 8,9,11)
AND A .status NOT IN (44, 45)
AND b.customer_id = c.customer_id
AND b.home_city = c.home_city
) a ) loop
v_itv_user_no :=0;
–备份
–插入新纪录
insert into csc.CSC_ITV_USER (ITV_USER_NO,USER_ID, USER_NAME, MSISDN, CUSTOMER_ID, CUSTOMER_NAME, HOME_CITY, STATUS, CREATE_TIME, CHANGE_TIME, DEL_FLAG, PAYMENT_USER_ID, STOP_TIME, ORDER_LEVEL, BUSINESS_TYPE, SP_ID, BUSINESS_CLASS, BUSINESS_ID, SMART_SHORT_STATUS)
values(csc.SEQ_CSC_ITV_USER.nextval,cr.USER_ID, cr.user_name, cr.msisdn, cr.customer_id, cr.customer_name, cr.home_city, cr.status, cr.create_time, cr.change_time, cr.del_flag, cr.payment_user_id, cr.stop_time, cr.order_level, cr.business_type, cr.sp_id, cr.business_class, cr.business_id, cr.smart_short_status);
–数目计算提交
v_deal_count := v_deal_count +1;
if v_deal_count=5000 then
commit;
v_deal_count :=0;
end if;

end loop;
commit;
end;

三、’数据链操作

1.登录数据库计费中心

2.环境变量配置
1.1 linkName:to_kf
1.2 Ip1：客户中心的数据库ip
1.3Port1:客户中心的数据库端口
1.4 dataServiceName：客户中心的数据库服务名
1.5 passwd:客户中心的数据库密码
1.6 username:客户中心的数据库用户名

3.创建数据链
create public database link linkName
　　 connect to username identified by passwd
　　 using ‘(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = Ip1)(PORT = Port1)))(CONNECT_DATA =(SERVICE_NAME = dataServiceName)))’;
4.查询数据链是否成功
select * from 该数据链接的表名@linkName;
5.判断序列化是否存在：
SEQ_CSC_ITV_USER为计费中心自增序列号
select SEQ_CSC_ITV_USER.nextval as v_itv_user_no from dual
不存在的话创建序列号：
CREATE SEQUENCE “CSC”.“SEQ_CSC_ITV_USER” MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE;

6. 执行脚本
   将客户中心的itv_users，USERS，customer3张表联合查询的数据插入到计费中心的CSC_ITV_USER中
   SEQ_CSC_ITV_USER为计费中心自增序列号

DECLARE
v_itv_user_no number(15);
v_deal_count number(8);
begin
v_deal_count:=0;
for cr in (select a.* from(
SELECT
distinct
A .user_id,
A .user_name,
b.msisdn,
b.customer_id,
c. NAME customer_name,
A .home_city,
A .status,
to_char(A .create_time, ‘YYYYMMDDHH24MISS’) create_time,
to_char(sysdate,‘yyyyMMddHH24miss’) change_time,
0 DEL_FLAG,
A .payment_user_id,
to_char(A .stop_time, ‘YYYYMMDDHH24MISS’) stop_time,
A .order_level,
A .business_type,
A .sp_id,
A .business_class,
304275 BUSINESS_ID,
0 SMART_SHORT_STATUS
FROM
itv_users@linkName A,
USERS@linkName b,
customer@linkName c
WHERE
A .payment_user_id = b.user_id
AND A .home_city = b.home_city
AND A .sp_id IN (100001, 100002)
AND A.BUSINESS_TYPE IN (6, 8,9,11)
AND A .status NOT IN (44, 45)
AND b.customer_id = c.customer_id
AND b.home_city = c.home_city
) a ) loop
v_itv_user_no :=0;
–备份
–插入新纪录
insert into CSC_ITV_USER (ITV_USER_NO,USER_ID, USER_NAME, MSISDN, CUSTOMER_ID, CUSTOMER_NAME, HOME_CITY, STATUS, CREATE_TIME, CHANGE_TIME, DEL_FLAG, PAYMENT_USER_ID, STOP_TIME, ORDER_LEVEL, BUSINESS_TYPE, SP_ID, BUSINESS_CLASS, BUSINESS_ID, SMART_SHORT_STATUS)
values(SEQ_CSC_ITV_USER.nextval,cr.USER_ID, cr.user_name, cr.msisdn, cr.customer_id, cr.customer_name, cr.home_city, cr.status, cr.create_time, cr.change_time, cr.del_flag, cr.payment_user_id, cr.stop_time, cr.order_level, cr.business_type, cr.sp_id, cr.business_class, cr.business_id, cr.smart_short_status);
–数目计算提交
v_deal_count := v_deal_count +1;
if v_deal_count=5000 then
commit;
v_deal_count :=0;
end if;

end loop;
commit;
end;
7.验证是否成功查询数据
select count() from CSC_ITV_USER;
select count() from (select a.* from(
SELECT
distinct
A .user_id,
A .user_name,
b.msisdn,
b.customer_id,
c. NAME customer_name,
A .home_city,
A .status,
to_char(A .create_time, ‘YYYYMMDDHH24MISS’) create_time,
to_char(sysdate,‘yyyyMMddHH24miss’) change_time,
0 DEL_FLAG,
A .payment_user_id,
to_char(A .stop_time, ‘YYYYMMDDHH24MISS’) stop_time,
A .order_level,
A .business_type,
A .sp_id,
A .business_class,
304275 BUSINESS_ID,
0 SMART_SHORT_STATUS
FROM
itv_users@linkName A,
USERS@linkName b,
customer@linkName c
WHERE
A .payment_user_id = b.user_id
AND A .home_city = b.home_city
AND A .sp_id IN (100001, 100002)
AND A .status NOT IN (44, 45)
AND b.customer_id = c.customer_id
AND b.home_city = c.home_city
) a)

四 、导入dmp导出dmp操作

1.环境变量配置
1.1 ccsIp：客户中心的数据库ip
1.2 ccsPort:客户中心的数据库端口
1.3 ccsServername：客户中心的数据库服务名
1.4 ccsPasswd:客户中心的数据库密码
1.5 ccsUserName:客户中心的数据库用户名
1.6 cscIp：计费中心的数据库ip
1.7 cscPort:计费中心的数据库端口
1.8 cscServername：计费中心的数据库服务名
1.9 cscPasswd:计费中心的数据库密码
2.0 cscUserName:计费中心的数据库用户名

2.登录ccs创建临时表：
CREATE TABLE “CSC”.“CSC_ITV_USER_TEMP”
( “USER_ID” NUMBER(15,0) NOT NULL ENABLE,
“USER_NAME” VARCHAR2(32) NOT NULL ENABLE,
“MSISDN” NUMBER(15,0) NOT NULL ENABLE,
“CUSTOMER_ID” NUMBER(15,0) NOT NULL ENABLE,
“CUSTOMER_NAME” VARCHAR2(50),
“HOME_CITY” NUMBER(12,0) NOT NULL ENABLE,
“STATUS” NUMBER(2,0),
“CREATE_TIME” VARCHAR2(19) NOT NULL ENABLE,
“CHANGE_TIME” VARCHAR2(19),
“DEL_FLAG” NUMBER(1,0) DEFAULT 0,
“PAYMENT_USER_ID” NUMBER(15,0),
“STOP_TIME” VARCHAR2(19),
“ORDER_LEVEL” NUMBER(2,0),
“BUSINESS_TYPE” NUMBER(2,0),
“SP_ID” VARCHAR2(15),
“BUSINESS_CLASS” NUMBER(3,0),
“BUSINESS_ID” VARCHAR2(32) NOT NULL ENABLE,
“SMART_SHORT_STATUS” NUMBER(6,0) DEFAULT 0,
CHECK (“USER_ID” IS NOT NULL) ENABLE,
CHECK (“USER_NAME” IS NOT NULL) ENABLE,
CHECK (“MSISDN” IS NOT NULL) ENABLE,
CHECK (“CREATE_TIME” IS NOT NULL) ENABLE,
CHECK (“BUSINESS_ID” IS NOT NULL) ENABLE,
CHECK (“HOME_CITY” IS NOT NULL) ENABLE,
CHECK (“CUSTOMER_ID” IS NOT NULL) ENABLE,
CHECK (“USER_ID” IS NOT NULL) ENABLE,
CHECK (“USER_NAME” IS NOT NULL) ENABLE,
CHECK (“MSISDN” IS NOT NULL) ENABLE,
CHECK (“CUSTOMER_ID” IS NOT NULL) ENABLE,
CHECK (“HOME_CITY” IS NOT NULL) ENABLE,
CHECK (“CREATE_TIME” IS NOT NULL) ENABLE,
CHECK (“BUSINESS_ID” IS NOT NULL) ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE “USERS”;
3.插入临时表：
insert into CSC_ITV_USER_TEMP(SELECT
A .user_id,
A .user_name,
b.msisdn,
b.customer_id,
c. NAME customer_name,
A .home_city,
A .status,
to_char(A .create_time, ‘YYYYMMDDHH24MISS’) create_time,
to_char(sysdate,‘yyyyMMddHH24miss’) change_time,
0 DEL_FLAG,
A .payment_user_id,
to_char(A .stop_time, ‘YYYYMMDDHH24MISS’) stop_time,
A .order_level,
A .business_type,
A .sp_id,
A .business_class,
304275 BUSINESS_ID,
0 SMART_SHORT_STATUS
FROM
itv_users A,
USERS b,
customer c
WHERE
A .payment_user_id = b.user_id
AND A .home_city = b.home_city
AND A .sp_id IN (100001, 100002)
AND A.BUSINESS_TYPE IN (6, 8,9,11)
AND A .status NOT IN (44, 45)
AND b.customer_id = c.customer_id
AND b.home_city = c.home_city);
commit

4.导出表数据
exp ccsUser/ccsPasswd@ccsIp:ccsPort/ccsServername file=‘d:CSC_ITV_USER_temp.dmp’ tables=(CSC_ITV_USER_TEMP);

5.导入数据库临时表和数据
imp cscUserName/cscPasswd@cscIp:cscPort/cscServerName file=‘d:CSC_ITV_USER_temp.dmp’ full=y;
如果报这个错误：
IMP-00013: 只有 DBA 才能导入由其他 DBA 导出的文件
grant dba to csc ;
5.临时表数据插入数据
insert into csc_itv_user select SEQ_CSC_ITV_USER.nextval as itv_User_No, c.* from(select * from CSC_ITV_USER_TEMP) c

附录：
参考样例：itvuser迁移脚本.sql
如有必要，可删除数据链：
DROP PUBLIC DATABASE LINK linkName;
如果执行脚本报错在插入表
执行以下脚本看是否语句有问题
insert into CSC_ITV_USER_TEM (ITV_USER_NO,USER_ID, USER_NAME, MSISDN, CUSTOMER_ID, CUSTOMER_NAME, HOME_CITY, STATUS, CREATE_TIME, CHANGE_TIME, DEL_FLAG, PAYMENT_USER_ID, STOP_TIME, ORDER_LEVEL, BUSINESS_TYPE, SP_ID, BUSINESS_CLASS, BUSINESS_ID, SMART_SHORT_STATUS)
values(SEQ_CSC_ITV_USER.nextval,1, ‘1’, 1, 1, ‘1’, 1, 1, ‘1’, ‘1’, 1, 1, ‘1’, 1, 1, 1, 1, ‘1’, 1);