DECLARE
   CURSOR c1 IS SELECT ename, dname FROM emp, dept
      WHERE emp.deptno = dept.deptno AND job = 'MANAGER'
      FOR UPDATE OF sal;

create or replace tigger auto_change
after insert or udpate or delete on aaa
for each row
begin
if inserting then
insert  into bbb values (:new.empno,:new.ename,:new.job,:new.sal,:new.dname);
elsif updating then
update bbb ......
elsif deleting then
delete bbb where empno=:old.empno;

SQL> desc t;
Name Type Nullable Default Comments
---- ------------ -------- ------- --------
A NUMBER(3) Y
B VARCHAR2(10) Y
C VARCHAR2(10) Y

SQL> create or replace procedure t_pro(v_a number, v_b varchar, v_c varchar) is
2 t_string varchar(200);
3 begin
4 t_string := 'insert into t values ('||v_a||','||v_b||','||v_c||')' ;
5 execute immediate t_string;
6 end;
7 /

Procedure created

SQL> call t_pro(123,'asd','dfg');

call t_pro(123,'asd','dfg')

ORA-00984: column not allowed here
ORA-06512: at "SYSTEM.T_PRO", line 5

 
         引用  ?告 回复  
 
 junsansi
?名?把

?自 bj
精??? 3
技??分 3107 (388)
社??分 267 (1611)
注?日期 2006-7-17
??徽章:32
     
         

 #2?表于 2007-12-7 17:40  
呵呵，????一下你就明白?什么了~~~

t_string := 'insert into t values ('||v_a||','||v_b||','||v_c||')' ;
dbms_output.put_line(t_string);
execute immediate t_string;

 
 
update a set a.col1 =
(
  select b.col1 from b where a.col2=b.col2
)
where exists
(
  select 1 from b where a.col2=b.col2
)

這trigger怎麼寫

在trigger中如果原來字段的值為null再更新一個新值後不去執行trigger如下

create or replace trigger MRPLIB.irescp00_Update
  before update of
  rsc_remark,
  rsc_mrp_no,
  rsc_type,
  rsc_dn_no,
  rsc_due_date,
  rsc_bank_ref_date8
  on irescp00
  for each row
declare
  -- local variables here
  mcat varchar2(2000);
  mcat1 varchar2(1);
begin
  mcat:='修改Project資料:';
  mcat1:='N';

  if nvl(ld.rsc_remark,'')<>:new.rsc_remark then
    mcat1:='Y';
    mcat:=mcat||'Remark:'||nvl(ld.rsc_remark,'')||'變成:'||:New.rsc_remark;
  end if ;

  if nvl(ld.rsc_mrp_no,'')<>nvl(:new.rsc_mrp_no,'') then
    mcat1:='Y';
    mcat:=mcat||'Mrp_no:'||nvl(ld.rsc_mrp_no,'')||'變成:'||:New.rsc_mrp_no;
  end if ;

  if nvl(ld.rsc_type,'')<>nvl(:new.rsc_type,'') then
    mcat1:='Y';
    mcat:=mcat||'Type:'||nvl(ld.rsc_type,'')||'變成:'||:New.rsc_type;
  end if ;

  if nvl(ld.rsc_due_date,'')<>:new.rsc_due_date then
    mcat1:='Y';
    mcat:=mcat||'Due_date:'||nvl(ld.rsc_due_date,'')||'變成:'||:New.rsc_due_date;
  end if ;

  if nvl(ld.rsc_bank_ref_date8,'')<>:new.rsc_bank_ref_date8 then
    mcat1:='Y';
    mcat:=mcat||'1st C.S.D.:'||nvl(ld.rsc_bank_ref_date8,'')||'變成:'||:New.rsc_bank_ref_date8;
  end if ;

  if mcat1 ='Y' then
    INSERT INTO IRESEPLOG
      (log_id, log_desc,log_enter,log_enter_date)
    VALUES
      (:new.rsc_job_no,mcat,user,sysdate);
   end if ;

  --select to_char(sysdate,'yyyymmdd') into date1 from dual;
end irescp00_Update;

**原來nvl(值，'')還是null