oracle sql 脚本:安装时,如果表没有某列则进行添加
if (select count(column_name) from cols where table_name=upper("tablename")
and column_name=upper("columnname") == 0)
then
alter table tablenam add columnname;
end if;
卸载时,如果表有莫列则进行删除
if (select count(column_name) from cols where table_name=upper("tablename")
and column_name=upper("columnname") > 0)
then
alter table tablenam drop column columnname;
end if;两个都报错。
if (select count(column_name) from cols where table_name=upper("tablename")
and column_name=upper("columnname") == 0)
then
alter table tablenam add columnname;
end if;
卸载时,如果表有莫列则进行删除
if (select count(column_name) from cols where table_name=upper("tablename")
and column_name=upper("columnname") > 0)
then
alter table tablenam drop column columnname;
end if;两个都报错。
then
alter table tablenam add columnname;
end if;
if (string result = (select count(column_name) from cols where table_name=upper("tablename")and column_name=upper("columnname")) > 0)
then
alter table tablenam drop column columnname;
end if; 这样吗 也报错。
Invalid sql
v_sql := 'alter table ' || v_tablename || ' add ' || v_column;
execute immediate v_sql;
这样儿试试
if exists (select * from cols where table_name=upper("tablename") and column_name=upper("columnname"))
if exists (select count(column_name) from cols where table_name=upper("tablename")
and column_name=upper("columnname"))
alter table tablenam drop column columnname;
这样也报错 invalid sql...
num_col int default 0;
begin
select count(*)
into num_col
from user_tab_cols
where table_name = upper('emp')
and column_name = upper('add_col');
if (num_col = 0) then
execute immediate 'alter table emp_tmp add(add_col varchar2(20))';
end if;
end;
select * from emp_tmp;
num_col int default 0;
begin
select count(*)
into num_col
from user_tab_cols
where table_name = upper('emp')
and column_name = upper('add_col');
if (num_col = 0) then
execute immediate 'alter table emp_tmp add(add_col varchar2(20))';
end if;
end; if (num_col = 0) or if (num_col == 0)
v_sql varchar2(100);
v_table varchar2(30);
v_col varchar2(30);
v_coltype varchar2(30);
v_counter int;
begin
v_table := 'test';
v_col := 'value';
v_coltype := ' varchar2(10)';
select count(*) into v_counter
from cols
where table_name = upper(v_table) and column_name = upper(v_col); if v_counter = 0 then
v_sql := 'alter table ' || v_table || ' add ' || v_col || v_coltype;
execute immediate v_sql;
end if;
end;
/
2 num_col int default 0;
3 begin
4 select count(*) into num_col from user_tab_cols where table_name = upper('emp') and column_name = upper('add_col');
5 if (num_col = 0) then
6 execute immediate 'alter table emp_tmp add(add_col varchar2(20))'; ---- 这里一定要注意,用execute immediate才行。
7 end if;
8 end;
9 /
[[ ${tempRC} != ${EFAILURE} ]] && \
{
sqlplus -s ${DB_LOGIN} <<-EOF || tempRC=${EFAILURE}
whenever sqlerror exit failure
set head off
set echo off
set verify off
set pagesize 0
set feedback off
begin
update compl_parm set parm_val = to_char(to_date(parm_val,'mmddyyyy')-1,'mmddyyyy'), updt_dt = sysdate, updt_id = '${appId}'
where app_id= '${appId}'
and app_key = 'RUN_DATE';
end;
/
exit success
EOF
}
thensqlplus -s /nolog << SQLPLUS set feedback off connect ${DB_USER}/${ARIA_OPT_DB_PASSWORD}@${DB_SERVER} declare num_col int default 0; begin select count(*) into num_col from user_tab_cols where table_name = upper('masked')
and column_name = upper('reason'); if (num_col>0) then execute immediate 'alter table masked drop colum reason'; end if; end; / quit;SQLPLUSelif [[ $CONFIG_TYPE = "INSTALL" ]]
thensqlplus -s /nolog << SQLPLUS set feedback off connect ${DB_USER}/${DB_PASSWORD}@${DB_SERVER} declare num_col int default 0; begin select count(*) into num_col from user_tab_cols where table_name = upper('masked')
and column_name = upper('reason'); if (num_col = 0) then execute immediate 'alter table masked add reason varchar(50)'; end if; end; / quit;SQLPLUSelseLogInfo "ERROR:Unknown config_type!"fi这样执行INSTALL 表没有反应
#!/bin/ksh
if [[ ${CONFIG_TYPE} = "INSTALL" ]]
then
sqlplus -s ${DB_USER}/${DB_PASSWORD}@${DB_SERVER} <<-EOF
set feedback off
declare
num_col number := 0;
begin
select count(*) into num_col from user_tab_cols where table_name = upper('masked')
and column_name = upper('reason');
if (num_col = 0) then
execute immediate 'alter table masked add reason varchar(50)';
end if;
end;
/
exit successEOFfi cd /home/devteamoc/username/workspace/bin
export CONFIG_TYPE=INSTALL
export DB_USER=username
export DB_PASSWORD=pwd
export DB_SERVER=pwd1
nohup ./1.sh > sysout &
cat sysoutSQL> select * from masked; ID NAME
---------- --------------------------------------------------SQL> select * from masked; ID NAME REASON
---------- -------------------------------------------------- --------------------------------------------------SQL>