如何编写批处理与SQLPLUS(例子)

第一部分: 批处理文件

generate_partition_sql.BAT

@echo *************************************************************************
@echo        数据库分区脚本生成
@echo        1.执行批处理文件时,需要输入数据库用户密码(如用启\密码@SID)以便连接上数据库
@echo        数据库连接串举例:LCAM_DEVELOP/密码@LACM5130
@echo        进行分区时，数据库用户需要拥有DDL 执行权限
@echo *************************************************************************

@echo off
set /p p_user="请输入用户名如(LCAM_DEVELOP):"
set /p p_owner="请输入表的owner(如LCAM_DEVELOP):"
set /p p_password="请输入用户密码(如lcam4ddl):"
set /p p_sid="请输入数据库连接串(TNS名)(如lacm5130):"
set /p p_path="请输入文件存放盘符(如输入E):"
set /p p_table="请输入需要分区的表名(如MM_PURCHASE):"
set /p p_column="请输入分区字段(如data_area):"
set /p p_partition_type="请输入分区类型(range/hash):"
sqlplus %p_user%/%p_password%@%p_sid%  @"%p_path%:\generate_partition_table_part.sql" '%p_owner%' '%p_path%' '%p_table%'
if "%p_partition_type%"=="range" (
sqlplus %p_user%/%p_password%@%p_sid%  @"%p_path%:\generate_partition_by_range.sql" '%p_owner%' '%p_path%' '%p_column%' '%p_table%'
)
if "%p_partition_type%"=="hash" (
sqlplus %p_user%/%p_password%@%p_sid%  @"%p_path%:\generate_partition_by_hash.sql" '%p_owner%' '%p_path%' '%p_column%'
)
sqlplus %p_user%/%p_password%@%p_sid%  @"%p_path%:\generate_partition_constraint_part.sql" '%p_owner%' '%p_path%' '%p_table%'
sqlplus %p_user%/%p_password%@%p_sid%  @"%p_path%:\generate_partition_index_part.sql" '%p_owner%' '%p_path%' '%p_table%'
sqlplus %p_user%/%p_password%@%p_sid%  @"%p_path%:\generate_table_comments_part.sql" '%p_owner%' '%p_path%' '%p_table%'
if exist %p_path%:\generate_partition_table_script.sql (
  type generate_partition_table_script.sql >> %p_table%_partition_script.sql
) else (
  echo 文件generate_partition_table_script.sql不存在
)
if exist %p_path%:\generate_partition_script.sql (
  type generate_partition_script.sql >> %p_table%_partition_script.sql
) else (
  echo 文件generate_partition_script.sql不存在
)
if exist %p_path%:\generate_partition_constraint_script.sql (
  type generate_partition_constraint_script.sql >> %p_table%_partition_script.sql
) else (
  echo 文件generate_partition_constraint_script.sql不存在
)
if exist %p_path%:\generate_partition_index_script.sql (
  type generate_partition_index_script.sql >> %p_table%_partition_script.sql
) else (
  echo 文件generate_partition_script.sql不存在
)
if exist %p_path%:\generate_table_comments_script.sql (
  type generate_table_comments_script.sql >> %p_table%_partition_script.sql
) else (
  echo 文件generate_table_comments_script.sql不存在
)
if exist %p_path%:\generate_partition_table_script.sql (
  del generate_partition_table_script.sql
)
if exist %p_path%:\generate_partition_script.sql (
  del generate_partition_script.sql
)
if exist %p_path%:\generate_table_comments_script.sql (
  del generate_table_comments_script.sql
)
if exist %p_path%:\generate_partition_index_script.sql (
  del generate_partition_index_script.sql
)
if exist %p_path%:\generate_partition_constraint_script.sql (
  del generate_partition_constraint_script.sql
)

第二部分 SQLPLUS

generate_partition_table_part.sql

set echo off
set feedback off
set newpage none
set pagesize 5000
set linesize 5000
set verify off
set heading off
set termout off
set trimspool  on
set long 100000
define owner='&1'
define path='&2'
define table_name='&3'
spool &path:\generate_partition_table_script.sql
select
*
from
(
select
case when a.DATA_TYPE like '%CHAR%' then
decode(a.COLUMN_ID,c.min_column,'CREATE TABLE'||Chr(32)||a.TABLE_NAME||'_PART(','')
||Chr(32)||a.COLUMN_NAME|| Chr(32)|| a.DATA_TYPE||'('||a.DATA_LENGTH||')' || a.DATA_PRECISION||a.DATA_SCALE|| ' '||decode(a.NULLABLE,'N','not null',Chr(32)) ||
decode(a.COLUMN_ID,c.max_column,')',',')
when a.DATA_TYPE like '%NUMBER%' then
decode(a.COLUMN_ID,c.min_column,'CREATE TABLE'||a.TABLE_NAME||'_MAPING',Chr(32)) ||a.COLUMN_NAME|| Chr(32)|| a.DATA_TYPE||'('|| a.DATA_PRECISION||','||a.DATA_SCALE||')'||Chr(32)||decode(a.NULLABLE,'N','not null','') ||decode(a.COLUMN_ID,c.max_column,')',',')
when a.DATA_TYPE like '%TIMESTAMP%' then
decode(a.COLUMN_ID,c.min_column,'CREATE TABLE'||a.TABLE_NAME||'_MAPING',Chr(32)) ||a.COLUMN_NAME|| Chr(32)|| a.DATA_TYPE||decode(a.NULLABLE,'N','not null','') ||decode(a.COLUMN_ID,c.max_column,')',',')
when a.DATA_TYPE like '%DATE%' then
decode(a.COLUMN_ID,c.min_column,'CREATE TABLE'||a.TABLE_NAME||'_MAPING',Chr(32)) ||a.COLUMN_NAME|| Chr(32)|| a.DATA_TYPE||decode(a.NULLABLE,'N','not null','') ||decode(a.COLUMN_ID,c.max_column,')',',')
end as column_name
from user_tab_columns a,
(select max(b.COLUMN_ID) max_column,min(b.COLUMN_ID) min_column,b.TABLE_NAME
 from user_tab_columns b
 where b.table_name=upper(trim('&table_name'))
 group by b.TABLE_NAME
) c
where a.TABLE_NAME=c.table_name
and a.table_name=upper(trim('&table_name'))
order by a.COLUMN_ID asc
)
;
spool off
exit

generate_partition_by_hash.sql

set echo off
set feedback off
set newpage none
set pagesize 5000
set linesize 5000
set verify off
set heading off
set termout off
set trimspool  on
set long 100000
define owner='&1'
define path='&2'
define column_name='&3'
spool &path:\generate_partition_script.sql
select 'partition by hash('||'&column_name'||')'||Chr(32)||'partitions'||Chr(32)||20||';'from dual
;
spool off
exit

generate_partition_by_range.sql

set echo off
set feedback off
set newpage none
set pagesize 5000
set linesize 5000
set verify off
set heading off
set termout off
set trimspool  on
set long 100000
define owner='&1'
define path='&2'
define column_name='&3'
define table_name='&4'
spool &path:\generate_partition_script.sql
select 'partition by range('||'&column_name'||')(' from dual
union all
select 'partition '||Chr(32)||substr('&table_name',instr('&table_name','_')+1,3)||'PRT'||rownum||Chr(32)||'values less than ('||''||COLUMN_NAME||'),' from
(
select DISTINCT
CASE WHEN (select a.DATA_TYPE from user_tab_columns a
where a.TABLE_NAME=upper(trim('&table_name'))
and a.COLUMN_NAME=upper(trim('&column_name')))=UPPER('DATE') or
(select a.DATA_TYPE from user_tab_columns a
where a.TABLE_NAME=upper(trim('&table_name'))
and a.COLUMN_NAME=upper(trim('&column_name'))) like 'TIMESTAMP%'
THEN
'to_date('||''''||to_char(trunc(&column_name,'MM'),'YYYY-MM-DD')||''''||','||''''||'YYYY-MM-DD'||''''||')'
else
 ''''||upper(trim(&column_name))||''''
END AS COLUMN_NAME
from &table_name p
order by COLUMN_NAME asc
)
union all
select 'partition '||Chr(32)||'&table_name'||'PART_OTHER'||Chr(32)||'values less than (MAXVALUE));' from dual
;
spool off
exit

generate_partition_constraint_part.sql

 

set echo off
set feedback off
set newpage none
set pagesize 5000
set linesize 5000
set verify off
set heading off
set termout off
set trimspool  on
set long 100000
define owner='&1'
define path='&2'
define table_name='&3'
spool &path:\generate_partition_constraint_script.sql
select
 case
 when d.constraint_type=upper('P') then
'alter table'||Chr(32)||'&table_name'||'_part'||Chr(32)||'add constraint'||Chr(32)||d.constraint_name||'_PT'||Chr(32)|| 'primary key ('||d.cn||');'
 when d.constraint_type=upper('R') then
 'alter table'||Chr(32)||'&table_name'||'_part'||Chr(32)||'add constraint'||Chr(32)||d.constraint_name||'_PT'||Chr(32)|| 'foreign key ('||d.crf||')'||Chr(32)||'references'||Chr(32)||d.r_table_name||'('||d.cnf||');'
 when d.constraint_type=upper('U') then
 'alter table'||Chr(32)||'&table_name'||'_part'||Chr(32)||'add constraint'||Chr(32)||d.constraint_name||'_PT'||Chr(32)|| 'unique ('||d.cnu||');'
 end as constraint_sql
from
(
select
c.constraint_name,
c.table_name,
c.r_table_name,
c.constraint_type,
CASE
WHEN c.max_position=1  and c.constraint_type=upper('P') then
    cn_1
WHEN c.max_position=2  and c.constraint_type=upper('P') then
    cn_1||','||cn_2
WHEN c.max_position=3  and c.constraint_type=upper('P') then
    cn_1||','||cn_2||','||cn_3
WHEN c.max_position=4  and c.constraint_type=upper('P') then
    cn_1||','||cn_2||','||cn_3||','||cn_4
WHEN c.max_position=5  and c.constraint_type=upper('P') then
    cn_1||','||cn_2||','||cn_3||','||cn_4||','||cn_5
WHEN c.max_position=6  and c.constraint_type=upper('P') then
    cn_1||','||cn_2||','||cn_3||','||cn_4||','||cn_5||','||cn_6
WHEN c.max_position=7  and c.constraint_type=upper('P') then
    cn_1||','||cn_2||','||cn_3||','||cn_4||','||cn_5||','||cn_6||','||cn_7
WHEN c.max_position=8  and c.constraint_type=upper('P') then
    cn_1||','||cn_2||','||cn_3||','||cn_4||','||cn_5||','||cn_6||','||cn_7||','||cn_8
WHEN c.max_position=9  and c.constraint_type=upper('P') then
    cn_1||','||cn_2||','||cn_3||','||cn_4||','||cn_5||','||cn_6||','||cn_7||','||cn_8||','||cn_9
WHEN c.max_position=10  and c.constraint_type=upper('P') then
    cn_1||','||cn_2||','||cn_3||','||cn_4||','||cn_5||','||cn_6||','||cn_7||','||cn_8||','||cn_9||','||cn_10
end cn,
CASE
WHEN c.max_position=1  and c.constraint_type=upper('U') then
    cn_1
WHEN c.max_position=2  and c.constraint_type=upper('U') then
    cn_1||','||cn_2
WHEN c.max_position=3  and c.constraint_type=upper('U') then
    cn_1||','||cn_2||','||cn_3
WHEN c.max_position=4  and c.constraint_type=upper('U') then
    cn_1||','||cn_2||','||cn_3||','||cn_4
WHEN c.max_position=5  and c.constraint_type=upper('U') then
    cn_1||','||cn_2||','||cn_3||','||cn_4||','||cn_5
WHEN c.max_position=6  and c.constraint_type=upper('U') then
    cn_1||','||cn_2||','||cn_3||','||cn_4||','||cn_5||','||cn_6
WHEN c.max_position=7  and c.constraint_type=upper('U') then
    cn_1||','||cn_2||','||cn_3||','||cn_4||','||cn_5||','||cn_6||','||cn_7
WHEN c.max_position=8  and c.constraint_type=upper('U') then
    cn_1||','||cn_2||','||cn_3||','||cn_4||','||cn_5||','||cn_6||','||cn_7||','||cn_8
WHEN c.max_position=9  and c.constraint_type=upper('U') then
    cn_1||','||cn_2||','||cn_3||','||cn_4||','||cn_5||','||cn_6||','||cn_7||','||cn_8||','||cn_9
WHEN c.max_position=10  and c.constraint_type=upper('U') then
    cn_1||','||cn_2||','||cn_3||','||cn_4||','||cn_5||','||cn_6||','||cn_7||','||cn_8||','||cn_9||','||cn_10
end cnu,
CASE
WHEN c.max_position=1  and c.constraint_type=upper('R') then
    cn_1
WHEN c.max_position=2  and c.constraint_type=upper('R') then
    cn_1||','||cn_2
WHEN c.max_position=3  and c.constraint_type=upper('R') then
    cn_1||','||cn_2||','||cn_3
WHEN c.max_position=4  and c.constraint_type=upper('R') then
    cn_1||','||cn_2||','||cn_3||','||cn_4
WHEN c.max_position=5  and c.constraint_type=upper('R') then
    cn_1||','||cn_2||','||cn_3||','||cn_4||','||cn_5
WHEN c.max_position=6  and c.constraint_type=upper('R') then
    cn_1||','||cn_2||','||cn_3||','||cn_4||','||cn_5||','||cn_6
WHEN c.max_position=7  and c.constraint_type=upper('R') then
    cn_1||','||cn_2||','||cn_3||','||cn_4||','||cn_5||','||cn_6||','||cn_7
WHEN c.max_position=8  and c.constraint_type=upper('R') then
    cn_1||','||cn_2||','||cn_3||','||cn_4||','||cn_5||','||cn_6||','||cn_7||','||cn_8
WHEN c.max_position=9  and c.constraint_type=upper('R') then
    cn_1||','||cn_2||','||cn_3||','||cn_4||','||cn_5||','||cn_6||','||cn_7||','||cn_8||','||cn_9
WHEN c.max_position=10  and c.constraint_type=upper('R') then
    cn_1||','||cn_2||','||cn_3||','||cn_4||','||cn_5||','||cn_6||','||cn_7||','||cn_8||','||cn_9||','||cn_10
end crf,
CASE
WHEN c.max_position=1  and c.constraint_type=upper('R') then
    cpn_1
WHEN c.max_position=2  and c.constraint_type=upper('R') then
    cpn_1||','||cpn_2
WHEN c.max_position=3  and c.constraint_type=upper('R') then
    cpn_1||','||cpn_2||','||cpn_3
WHEN c.max_position=4  and c.constraint_type=upper('R') then
    cpn_1||','||cpn_2||','||cpn_3||','||cpn_4
WHEN c.max_position=5  and c.constraint_type=upper('R') then
    cpn_1||','||cpn_2||','||cpn_3||','||cpn_4||','||cpn_5
WHEN c.max_position=6  and c.constraint_type=upper('R') then
    cpn_1||','||cpn_2||','||cpn_3||','||cpn_4||','||cpn_5||','||cpn_6
WHEN c.max_position=7  and c.constraint_type=upper('R') then
    cpn_1||','||cpn_2||','||cpn_3||','||cpn_4||','||cpn_5||','||cpn_6||','||cpn_7
WHEN c.max_position=8  and c.constraint_type=upper('R') then
    cpn_1||','||cpn_2||','||cpn_3||','||cpn_4||','||cpn_5||','||cpn_6||','||cpn_7||','||cpn_8
WHEN c.max_position=9  and c.constraint_type=upper('R') then
    cpn_1||','||cpn_2||','||cpn_3||','||cpn_4||','||cpn_5||','||cpn_6||','||cpn_7||','||cpn_8||','||cpn_9
WHEN c.max_position=10  and c.constraint_type=upper('R') then
    cpn_1||','||cpn_2||','||cpn_3||','||cpn_4||','||cpn_5||','||cpn_6||','||cpn_7||','||cpn_8||','||cpn_9||','||cpn_10
end cnf
from
(
select
b.constraint_name,
b.table_name,
p.table_name as r_table_name,
s.constraint_type,
max(b.POSITION) as max_position,
max(p.POSITION) as maxp_position,
MAX(decode(b.POSITION, 1, b.column_name, NULL)) cn_1,
MAX(decode(b.POSITION, 2, b.column_name, NULL)) cn_2,
MAX(decode(b.POSITION, 3, b.column_name, NULL)) cn_3,
MAX(decode(b.POSITION, 4, b.column_name, NULL)) cn_4,
MAX(decode(b.POSITION, 5, b.column_name, NULL)) cn_5,
MAX(decode(b.POSITION, 6, b.column_name, NULL)) cn_6,
MAX(decode(b.POSITION, 7, b.column_name, NULL)) cn_7,
MAX(decode(b.POSITION, 8, b.column_name, NULL)) cn_8,
MAX(decode(b.POSITION, 9, b.column_name, NULL)) cn_9,
MAX(decode(b.POSITION, 10, b.column_name, NULL)) cn_10,
MAX(decode(p.POSITION, 1, p.column_name, NULL)) cpn_1,
MAX(decode(p.POSITION, 2, p.column_name, NULL)) cpn_2,
MAX(decode(p.POSITION, 3, p.column_name, NULL)) cpn_3,
MAX(decode(p.POSITION, 4, p.column_name, NULL)) cpn_4,
MAX(decode(p.POSITION, 5, p.column_name, NULL)) cpn_5,
MAX(decode(p.POSITION, 6, p.column_name, NULL)) cpn_6,
MAX(decode(p.POSITION, 7, p.column_name, NULL)) cpn_7,
MAX(decode(p.POSITION, 8, p.column_name, NULL)) cpn_8,
MAX(decode(p.POSITION, 9, p.column_name, NULL)) cpn_9,
MAX(decode(p.POSITION, 10, p.column_name, NULL)) cpn_10
from (
select b.owner,b.constraint_name,b.table_name,b.column_name,b.position from
user_cons_columns b
where b.table_name=upper(trim('&table_name')))b,
user_constraints s,
user_cons_columns p
where b.owner=upper(trim(('&owner')))
and b.constraint_name=s.constraint_name(+)
AND s.r_constraint_name=p.constraint_name(+)
and s.constraint_type<>upper('C')
group by b.constraint_name,b.table_name,p.table_name,s.constraint_type
)c
)d
;

spool off
exit

 

generate_partition_index_part.sql

set echo off
set feedback off
set newpage none
set pagesize 5000
set linesize 5000
set verify off
set heading off
set termout off
set trimspool  on
set long 100000
define owner='&1'
define path='&2'
define table_name='&3'
spool &path:\generate_partition_index_script.sql
select
case when length('IDX_'||substr(d.table_name,4,3)||substr(a.index_name,instr(a.index_name,'_')))>30 then
'create index'||Chr(32)||'IDX_'||substr(d.table_name,4,3)||substr(a.index_name,instr(a.index_name, '_'),instr(a.index_name, '_',-1)-instr(a.index_name, '_'))||Chr(32)||'on'||Chr(32)||'&table_name'||'_PART'|| '('||d.cn||');'
when length('IDX_'||substr(d.table_name,4,3)||substr(a.index_name,instr(a.index_name,'_')))<=30 and length(a.index_name)>20 then
'create index'||Chr(32)||'IDX_'||substr(d.table_name,4,3)||substr(a.index_name,instr(a.index_name,'_'))||Chr(32)||'on'||Chr(32)||'&table_name'||'_PART'|| '('||d.cn||');'
when length('IDX_'||substr(d.table_name,4,3)||substr(a.index_name,instr(a.index_name,'_')))<=30 and length(a.index_name)<=20 then
'create index'||Chr(32)||'IDX_'||substr(d.table_name,4,3)||a.index_name||Chr(32)||'on'||Chr(32)||'&table_name'||'_PART'|| '('||d.cn||');'
end as index_name
from
user_indexes a,
(
select
c.index_name,
c.table_name,
CASE
WHEN c.max_position=1 then
    cn_1
WHEN c.max_position=2 then
    cn_1||','||cn_2
WHEN c.max_position=3 then
    cn_1||','||cn_2||','||cn_3
WHEN c.max_position=4 then
    cn_1||','||cn_2||','||cn_3||','||cn_4
WHEN c.max_position=5 then
    cn_1||','||cn_2||','||cn_3||','||cn_4||','||cn_5
WHEN c.max_position=6 then
    cn_1||','||cn_2||','||cn_3||','||cn_4||','||cn_5||','||cn_6
WHEN c.max_position=7 then
    cn_1||','||cn_2||','||cn_3||','||cn_4||','||cn_5||','||cn_6||','||cn_7
WHEN c.max_position=8 then
    cn_1||','||cn_2||','||cn_3||','||cn_4||','||cn_5||','||cn_6||','||cn_7||','||cn_8
WHEN c.max_position=9 then
    cn_1||','||cn_2||','||cn_3||','||cn_4||','||cn_5||','||cn_6||','||cn_7||','||cn_8||','||cn_9
WHEN c.max_position=10 then
    cn_1||','||cn_2||','||cn_3||','||cn_4||','||cn_5||','||cn_6||','||cn_7||','||cn_8||','||cn_9||','||cn_10
end cn
from
(
select
b.index_name,
b.table_name,
max(b.column_position) as max_position,
MAX(decode(b.column_position, 1, b.column_name, NULL)) cn_1,
MAX(decode(b.column_position, 2, b.column_name, NULL)) cn_2,
MAX(decode(b.column_position, 3, b.column_name, NULL)) cn_3,
MAX(decode(b.column_position, 4, b.column_name, NULL)) cn_4,
MAX(decode(b.column_position, 5, b.column_name, NULL)) cn_5,
MAX(decode(b.column_position, 6, b.column_name, NULL)) cn_6,
MAX(decode(b.column_position, 7, b.column_name, NULL)) cn_7,
MAX(decode(b.column_position, 8, b.column_name, NULL)) cn_8,
MAX(decode(b.column_position, 9, b.column_name, NULL)) cn_9,
MAX(decode(b.column_position, 10, b.column_name, NULL)) cn_10
from user_ind_columns b
where b.table_name=upper(trim('&table_name'))
group by b.index_name,b.table_name
) c
)d
where 1=1
and a.index_name=d.index_name
and a.table_name=d.table_name
and a.table_name=upper(trim('&table_name'))
and a.uniqueness<>upper('UNIQUE')
;
spool off
exit

generate_table_comments_part.sql

set echo off
set feedback off
set newpage none
set pagesize 5000
set linesize 5000
set verify off
set heading off
set termout off
set trimspool  on
set long 100000
define owner='&1'
define path='&2'
define table_name='&3'
spool &path:\generate_table_comments_script.sql
select 'comment on table'||Chr(32)||s.table_name||'_PART'||Chr(32)||'is'||Chr(32)||''''||s.comments||''''||';' as table_name_comments  from user_tab_comments s
where s.table_name=upper(trim('&table_name'))
union all
select 'comment on column'||Chr(32)||a.table_name||'_PART'||'.'||a.column_name||Chr(32)||'is'||Chr(32)||''''||a.comments||''''||';' as column_comment
from user_col_comments a
where a.table_name=upper(trim('&table_name'))
;
spool off
exit

第三部分 在线分区

run_partition_script.sql

 set echo off
set feedback off
set newpage none
set pagesize 5000
set linesize 5000
set verify off
set heading off
set termout off
set trimspool  on
set long 100000
define owner='&1'
define path='&2'
define table_name='&3'
define table_namepart='&3'_PART
spool &path:\generate_partition_constraint_script.sql
EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('&owner','&table_name',DBMS_REDEFINITION.CONS_USE_PK);
EXEC DBMS_REDEFINITION.START_REDEF_TABLE('&owner', '&table_name', '&table_namepart');
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('&owner', '&table_name', '&table_namepart');
spool off
exit

 

 

======================================

若要循环对多个表分别分区

victim.txt

A,B,C,D  ----表名

 

可以在generate_partition_sql.BAT加上如下:

 

@echo off
　　@if "%1"=="" goto usage
　　@for /f "tokens=1,2,3 delims= " %%i in (victim.txt) do start call generate_partition_sql %%i %%j %%k
　　@goto end

　　:usage
　　@echo run this batch in dos modle.or just double-click it.

　　:end