procedure的新增刪除修改 |
答題得分者是:st33chen
|
darkblackword
一般會員 發表:3 回覆:7 積分:2 註冊:2008-10-12 發送簡訊給我 |
各位大大好...
我想請問一下我想建一個有新增刪除修改的procedure請問我這樣的寫法有錯誤嗎? 謝謝~~ [code sql] create or replace procedure sp_linA(a_name in varchar2, a_number in number, a_price in number, a_cost in number, effect in varchar2, n_count out integer) is begin if effect = 'INS' THEN INSERT INTO lin_A (A_NAME, A_NUMBER, A_PRICE, A_COST) values (A_NAME, A_NUMBER, A_PRICE, A_COST); ELSIF effect = 'upd' then UPDATE LIN_A SET a_name = a_name, a_number = a_number, a_price = a_price, a_cost = a_cost; ELSIF effect = 'del' then DELETE FROM LIN_A where a_name = a_name; END IF; Select Count(*) into n_Count from LIN_A; end sp_linA; [/code] |
st33chen
尊榮會員 發表:15 回覆:591 積分:1201 註冊:2005-09-30 發送簡訊給我 |
您好,
不知道您為何這麼做, 單從語法來看: 因為 參數名 和 table 的欄位名 相同, db engine 應該分不出來 建議 更改 參數名, 例如; create or replace procedure sp_linA(xa_name in varchar2, xa_number in number, xa_price in number, xa_cost in number, xeffect in varchar2, xn_count out integer) isbegin if xeffect = 'INS' THEN INSERT INTO lin_A (A_NAME, A_NUMBER, A_PRICE, A_COST) values (xA_NAME, xA_NUMBER, xA_PRICE, xA_COST); ELSIF xeffect = 'upd' then UPDATE LIN_A SET a_name = xa_name, a_number = xa_number, a_price = xa_price, a_cost = xa_cost; ELSIF xeffect = 'del' then DELETE FROM LIN_A where a_name = xa_name; END IF; Select Count(*) into xn_Count from LIN_A; end sp_linA; ===================引 用 darkblackword 文 章=================== 各位大大好... 我想請問一下我想建一個有新增刪除修改的procedure請問我這樣的寫法有錯誤嗎? 謝謝~~ [code sql] create or replace procedure sp_linA(a_name in varchar2, a_number in number, a_price in number, a_cost in number, effect in varchar2, n_count out integer) is begin if effect = 'INS' THEN INSERT INTO lin_A (A_NAME, A_NUMBER, A_PRICE, A_COST) values (A_NAME, A_NUMBER, A_PRICE, A_COST); ELSIF effect = 'upd' then UPDATE LIN_A SET a_name = a_name, a_number = a_number, a_price = a_price, a_cost = a_cost; ELSIF effect = 'del' then DELETE FROM LIN_A where a_name = a_name; END IF; Select Count(*) into n_Count from LIN_A; end sp_linA; [/code]
------
IS IT WHAT IT IS 我是 李慕白 請倒著唸. 又想把老話拿出來說, 請用台語發音 : 專家專家全是ROBOT CAR (滷肉腳啦); 都已接手這麼久了, 績效還是那麼爛, 講話還那麼大聲. |
darkblackword
一般會員 發表:3 回覆:7 積分:2 註冊:2008-10-12 發送簡訊給我 |
感謝大大的回答~~
我還有個問題想請教~~!! 我的table有兩個..一個是table=lin_a 一個是lin_aLog...由lin_a更新時寫入lin_alog 但我不太瞭解tigger是要如何寫@@ 有試了方法但似乎寫不太出來一直出錯~~想麻煩大大幫我看一下..謝~~!! [code sql] create or replace trigger tigg_lin before insert or Delete or update on Lin_a for each row declare --放變數的地方 upd_user varchar2(10); upd_time varchar2(10); begin if inserting then insert into lin_alog (na_name, na_number, na_price, na_cost, upd_name, upd_time) values (na_name, na_number, na_price, na_cost, upd_name, upd_time); if Deleting then Delete from lin_alog where a_name = :old.a_name; if updating then na_name := na_name, na_number := na_number, na_price := na_price, na_cost := na_cost, upd_name := upd_name, upd_time := sysdate where na_name = a_name; end tigg_lin; [/code] |
st33chen
尊榮會員 發表:15 回覆:591 積分:1201 註冊:2005-09-30 發送簡訊給我 |
您好,
if inserting then insert into lin_alog (na_name, na_number, na_price, na_cost, upd_name, upd_time) values ( :new.na_name, :new.na_number, :new.na_price, :new.na_cost, :new.upd_name, :new.upd_time); update, delete 用 :old. 參考一下 ===================引 用 darkblackword 文 章=================== 感謝大大的回答~~ 我還有個問題想請教~~!! 我的table有兩個..一個是table=lin_a 一個是lin_aLog...由lin_a更新時寫入lin_alog 但我不太瞭解tigger是要如何寫@@ 有試了方法但似乎寫不太出來一直出錯~~想麻煩大大幫我看一下..謝~~!! [code sql] create or replace trigger tigg_lin before insert or Delete or update on Lin_a for each row declare --放變數的地方 upd_user varchar2(10); upd_time varchar2(10); begin if inserting then insert into lin_alog (na_name, na_number, na_price, na_cost, upd_name, upd_time) values (na_name, na_number, na_price, na_cost, upd_name, upd_time); if Deleting then Delete from lin_alog where a_name = :old.a_name; if updating then na_name := na_name, na_number := na_number, na_price := na_price, na_cost := na_cost, upd_name := upd_name, upd_time := sysdate where na_name = a_name; end tigg_lin; [/code]
------
IS IT WHAT IT IS 我是 李慕白 請倒著唸. 又想把老話拿出來說, 請用台語發音 : 專家專家全是ROBOT CAR (滷肉腳啦); 都已接手這麼久了, 績效還是那麼爛, 講話還那麼大聲. |
darkblackword
一般會員 發表:3 回覆:7 積分:2 註冊:2008-10-12 發送簡訊給我 |
大大您好~~我已修改但是還是跑不出來~~
是否把欄位放錯了呢? 對於刪除我已下條件..但仍會刪除全部資料..這是為什麼呢..謝謝 [code sql] create or replace trigger tigg_lin before insert or Delete or update on Lin_a for each row declare begin if inserting then insert into lin_alog (na_name,na_number,na_price,na_cost, upd_name, upd_time) values (:new.a_name,:new.a_number,:new.a_price,:new.a_cost,'sw',to_char(sysdate)); end if; if Deleting then Delete from lin_alog where na_name = :old.a_name; end if; if updating then update lin_alog set na_name = :old.na_name, na_number = :old.na_number, na_price = :old.na_price, na_cost = :old.na_cost, upd_name = 'sw', upd_time = sysdate; end if; end tigg_lin; [/code] |
christie
資深會員 發表:30 回覆:299 積分:475 註冊:2005-03-25 發送簡訊給我 |
Hi, Try
create or replace trigger tigg_lin AFTER insert or Delete or update on Lin_a for each row declare begin if inserting then insert into lin_alog (na_name,na_number,na_price,na_cost, upd_name, upd_time) values (:new.a_name,:new.a_number,:new.a_price,:new.a_cost,'sw',sysdate); end if; . . . ===================引 用 darkblackword 文 章=================== 大大您好~~我已修改但是還是跑不出來~~ 是否把欄位放錯了呢? 對於刪除我已下條件..但仍會刪除全部資料..這是為什麼呢..謝謝 [code sql] create or replace trigger tigg_lin before insert or Delete or update on Lin_a for each row declare begin if inserting then insert into lin_alog (na_name,na_number,na_price,na_cost, upd_name, upd_time) values (:new.a_name,:new.a_number,:new.a_price,:new.a_cost,'sw',to_char(sysdate)); end if; if Deleting then Delete from lin_alog where na_name = :old.a_name; end if; if updating then update lin_alog set na_name = :old.na_name, na_number = :old.na_number, na_price = :old.na_price, na_cost = :old.na_cost, upd_name = 'sw', upd_time = sysdate; end if; end tigg_lin; [/code]
------
What do we live for if not to make life less difficult for each other? |
darkblackword
一般會員 發表:3 回覆:7 積分:2 註冊:2008-10-12 發送簡訊給我 |
謝謝大大的回答~~
已經修改沒什麼問題了~~ 最後修改的結果↓ [code sql] create or replace trigger tigg_lin after insert or Delete or update on Lin_a for each row declare begin if inserting then insert into lin_alog (na_name,na_number,na_price,na_cost, upd_name, upd_time) values (:new.a_name,:new.a_number,:new.a_price,:new.a_cost,'sw',sysdate); end if; if Deleting then Delete from lin_alog where na_name = :old.a_name; end if; if updating then update lin_alog SET na_name = :new.a_name, na_number = :new.a_number, na_price = :new.a_price, na_cost = :new.a_cost, upd_name = 'upd_name', upd_time = sysdate WHERE na_name = :new.a_name; end if; end tigg_lin; [/code] 但~~after和before 我還是不太懂要怎麼用...可以麻煩大大解釋一下嗎? 3QQQ~~~ |
christie
資深會員 發表:30 回覆:299 積分:475 註冊:2005-03-25 發送簡訊給我 |
Hi, Trigger: Before/After INSERT
http://www.techonthenet.com/oracle/triggers/before_insert.php http://www.techonthenet.com/oracle/triggers/after_insert.php
------
What do we live for if not to make life less difficult for each other? |
darkblackword
一般會員 發表:3 回覆:7 積分:2 註冊:2008-10-12 發送簡訊給我 |
本站聲明 |
1. 本論壇為無營利行為之開放平台,所有文章都是由網友自行張貼,如牽涉到法律糾紛一切與本站無關。 2. 假如網友發表之內容涉及侵權,而損及您的利益,請立即通知版主刪除。 3. 請勿批評中華民國元首及政府或批評各政黨,是藍是綠本站無權干涉,但這裡不是政治性論壇! |