在您刚回答的问题里为什么这样不行 declare sql varchar(1000); set sql=concat('alter table this_month_day add column ', col_name,' int'); prepare stmt from sql; execute stmt; deallocate prepare stmt;但是这样就可以? set @sql=concat('alter table this_month_day add column ', col_name,' int'); prepare stmt from @sql; execute stmt; deallocate prepare stmt;
prepare stmt from 后只能使用 session 变量。
可以帮忙说以下在Postgresql里面如何定义一个变量吗 如:create or replace trigger LOG_EIM_INSTRUMENTS after insert or update or delete on T_EIM_INSTRUMENTS for each row declare L_operate T_EIM_INSTRUMENTS_LOG.OPERATE%type; L_operateType T_EIM_INSTRUMENTS_LOG.OPERATETYPE%type; L_logId T_EIM_INSTRUMENTS_LOG.Logid%type; L_statusNo t_Sys_Dictionary.No%type; L_statusNoOld t_Sys_Dictionary.No%type; L_statusId T_EIM_INSTRUMENTS_LOG.Statusid%type; L_statusIdOld T_EIM_INSTRUMENTS_LOG.Statusid%type; L_flag boolean; begin select S_EIM_INSTRUMENTS_LOG.NEXTVAL into L_logId from dual; case WHEN INSERTING THEN L_operate := '教学科研仪器基本信息'; L_operateType := '添加'; insert into T_EIM_INSTRUMENTS_LOG values (:new.id, :new.VERSION, :new.CREATETIME, sysdate, :new.creator, :new.creator, :new.status, :new.labid, :new.instrumentid, :new.no, :new.statusid, :new.purchasetime, :new.photo, :new.price, :new.functionary, :new.tel, :new.dictionaryid, :new.productor, :new.usemethods, :new.re, :new.location, :new.sourceid, L_logId, L_operateType, L_operate, 'InstrumentsLog'); WHEN UPDATING THEN if ((:new.labid <> :old.labid or (:old.labid is null and :new.labid is not null)) or (:new.instrumentid <> :old.instrumentid or (:old.instrumentid is null and :new.instrumentid is not null)) or (:new.no <> :old.no or (:old.no is null and :new.no is not null)) or (:new.statusid <> :old.statusid or (:old.statusid is null and :new.statusid is not null)) or (:new.purchasetime <> :old.purchasetime or (:old.purchasetime is null and :new.purchasetime is not null)) or (:new.photo <> :old.photo or (:old.photo is null and :new.photo is not null)) or (:new.price <> :old.price or (:old.price is null and :new.price is not null)) or (:new.functionary <> :old.functionary or (:old.functionary is null and :new.functionary is not null)) or (:new.tel <> :old.tel or (:old.tel is null and :new.tel is not null)) or (:new.dictionaryid <> :old.dictionaryid or (:old.dictionaryid is null and :new.dictionaryid is not null)) or (:new.productor <> :old.productor or (:old.productor is null and :new.productor is not null)) or (:new.usemethods <> :old.usemethods or (:old.usemethods is null and :new.usemethods is not null)) or (:new.re <> :old.re or (:old.re is null and :new.re is not null)) or (:new.location <> :old.location or (:old.location is null and :new.location is not null)) or (:new.sourceid <> :old.sourceid or (:old.sourceid is null and :new.sourceid is not null))) then --新的仪器现状 L_statusId := :new.statusId; select t.no into L_statusNo from t_sys_dictionary t, t_sys_diccategory t1 where t1.id = t.diccategoryid and L_statusId = t.id; --旧的仪器现状 L_statusIdOld := :old.statusId; select t.no into L_statusNoOld from t_sys_dictionary t, t_sys_diccategory t1 where t1.id = t.diccategoryid and L_statusIdOld = t.id; if L_statusNo = '3' or L_statusNo = '4' or L_statusNoOld = '3' THEN L_flag := false; else L_operate := '教学科研仪器基本信息'; L_operateType := '修改'; L_flag := true; end if; if L_flag = true then insert into T_EIM_INSTRUMENTS_LOG values (:new.id, :new.VERSION, :new.CREATETIME, sysdate, :new.creator, :new.operator, :new.status, :new.labid, :new.instrumentid, :new.no, :new.statusid, :new.purchasetime, :new.photo, :new.price, :new.functionary, :new.tel, :new.dictionaryid, :new.productor, :new.usemethods, :new.re, :new.location, :new.sourceid, L_logId, L_operateType, L_operate, 'InstrumentsLog'); end if; end if; END CASE; end LOG_EIM_INSTRUMENTS; 这是一个Oracle的触发器 如果改成Postgresql,该怎么改呢 请高手看看。。
存储过程内的变量@b=0
SESSION级的变量。
帮助中有说明。
MySQL官方文档 http://dev.mysql.com/doc/refman/5.1/zh/index.html
declare sql varchar(1000);
set sql=concat('alter table this_month_day add column ', col_name,' int');
prepare stmt from sql;
execute stmt;
deallocate prepare stmt;但是这样就可以?
set @sql=concat('alter table this_month_day add column ', col_name,' int');
prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;
after insert or update or delete on T_EIM_INSTRUMENTS
for each row
declare
L_operate T_EIM_INSTRUMENTS_LOG.OPERATE%type;
L_operateType T_EIM_INSTRUMENTS_LOG.OPERATETYPE%type;
L_logId T_EIM_INSTRUMENTS_LOG.Logid%type;
L_statusNo t_Sys_Dictionary.No%type;
L_statusNoOld t_Sys_Dictionary.No%type;
L_statusId T_EIM_INSTRUMENTS_LOG.Statusid%type;
L_statusIdOld T_EIM_INSTRUMENTS_LOG.Statusid%type;
L_flag boolean;
begin
select S_EIM_INSTRUMENTS_LOG.NEXTVAL into L_logId from dual;
case
WHEN INSERTING THEN
L_operate := '教学科研仪器基本信息';
L_operateType := '添加';
insert into T_EIM_INSTRUMENTS_LOG
values
(:new.id,
:new.VERSION,
:new.CREATETIME,
sysdate,
:new.creator,
:new.creator,
:new.status,
:new.labid,
:new.instrumentid,
:new.no,
:new.statusid,
:new.purchasetime,
:new.photo,
:new.price,
:new.functionary,
:new.tel,
:new.dictionaryid,
:new.productor,
:new.usemethods,
:new.re,
:new.location,
:new.sourceid,
L_logId,
L_operateType,
L_operate,
'InstrumentsLog');
WHEN UPDATING THEN
if ((:new.labid <> :old.labid or
(:old.labid is null and :new.labid is not null)) or
(:new.instrumentid <> :old.instrumentid or
(:old.instrumentid is null and :new.instrumentid is not null)) or
(:new.no <> :old.no or (:old.no is null and :new.no is not null)) or
(:new.statusid <> :old.statusid or
(:old.statusid is null and :new.statusid is not null)) or
(:new.purchasetime <> :old.purchasetime or
(:old.purchasetime is null and :new.purchasetime is not null)) or
(:new.photo <> :old.photo or
(:old.photo is null and :new.photo is not null)) or
(:new.price <> :old.price or
(:old.price is null and :new.price is not null)) or
(:new.functionary <> :old.functionary or
(:old.functionary is null and :new.functionary is not null)) or
(:new.tel <> :old.tel or
(:old.tel is null and :new.tel is not null)) or
(:new.dictionaryid <> :old.dictionaryid or
(:old.dictionaryid is null and :new.dictionaryid is not null)) or
(:new.productor <> :old.productor or
(:old.productor is null and :new.productor is not null)) or
(:new.usemethods <> :old.usemethods or
(:old.usemethods is null and :new.usemethods is not null)) or
(:new.re <> :old.re or
(:old.re is null and :new.re is not null)) or
(:new.location <> :old.location or
(:old.location is null and :new.location is not null)) or
(:new.sourceid <> :old.sourceid or
(:old.sourceid is null and :new.sourceid is not null))) then
--新的仪器现状
L_statusId := :new.statusId;
select t.no
into L_statusNo
from t_sys_dictionary t, t_sys_diccategory t1
where t1.id = t.diccategoryid
and L_statusId = t.id;
--旧的仪器现状
L_statusIdOld := :old.statusId;
select t.no
into L_statusNoOld
from t_sys_dictionary t, t_sys_diccategory t1
where t1.id = t.diccategoryid
and L_statusIdOld = t.id;
if L_statusNo = '3' or L_statusNo = '4' or L_statusNoOld = '3' THEN
L_flag := false;
else
L_operate := '教学科研仪器基本信息';
L_operateType := '修改';
L_flag := true;
end if;
if L_flag = true then
insert into T_EIM_INSTRUMENTS_LOG
values
(:new.id,
:new.VERSION,
:new.CREATETIME,
sysdate,
:new.creator,
:new.operator,
:new.status,
:new.labid,
:new.instrumentid,
:new.no,
:new.statusid,
:new.purchasetime,
:new.photo,
:new.price,
:new.functionary,
:new.tel,
:new.dictionaryid,
:new.productor,
:new.usemethods,
:new.re,
:new.location,
:new.sourceid,
L_logId,
L_operateType,
L_operate,
'InstrumentsLog');
end if;
end if;
END CASE;
end LOG_EIM_INSTRUMENTS; 这是一个Oracle的触发器 如果改成Postgresql,该怎么改呢 请高手看看。。