用ORACLE 的 declare， 如果对象不存在则创建，否则跳过

 

 

 

 

set define off ;
set serveroutput on;

--检查是否曾购买过特定产品
delete from  t_discount_obj where   Field_Code ='isHadBuySpecProd';
insert into t_discount_obj ( id,Field_Code,description,macro_code,del_flag)  values(s_id.nextval, 'isHadBuySpecProd','是否曾买过特定产品', 'isHadBuySpecProd',0);
delete from  t_ar_cacl_obj  where   Field_Code = 'isHadBuySpecProd';
insert into t_ar_cacl_obj   ( id,Field_Code,description,macro_code,del_flag)  values (s_id.nextval , 'isHadBuySpecProd','是否曾买过特定产品', 'isHadBuySpecProd',0);

--此为空壳函数，业务逻辑以本地为主
DECLARE
  ct integer;
BEGIN
  select COUNT(*) into ct from user_objects where object_name ='FUN_DISC_COND_ISHADBUYSPECPROD';
  if ct= 0 then
    dbms_output.put_line('fun_disc_cond_isHadBuySpecProd不存在，可以创建');  
    --单引号内的语句为创建函数语句
    execute immediate '
      --创建函数语句开始
      create or replace function fun_disc_cond_isHadBuySpecProd(v_id in number)
      return number as
      begin
        return 0 ;
      exception
        when others then
           return - 1;
      end fun_disc_cond_isHadBuySpecProd;
      --创建函数语句结束
    ' ;
  else
      dbms_output.put_line('fun_disc_cond_isHadBuySpecProd已经存在，跳过创建');  
  end if;
END;
/

 

 

 

参考：https://www.cnblogs.com/champaign/p/9468342.html

set serveroutput on
declare
i integer;
begin
select count(*) into i from user_tables where table_name = 'TMP_T_CATER_DELIVERYADDRESS';
if i > 0 then
dbms_output.put_line('该表已存在!');
execute immediate 'DROP TABLE TMP_T_CATER_DELIVERYADDRESS';
else
dbms_output.put_line('该表不存在');
end if;
execute immediate 'CREATE TABLE TMP_T_CATER_DELIVERYADDRESS(id int primary key,name varchar(50))';
end;