各位大大好...
我想請問一下我想建一個有新增刪除修改的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]

您好,
不知道您為何這麼做, 單從語法來看:
因為 參數名 和 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 (滷肉腳啦);
都已接手這麼久了, 績效還是那麼爛, 講話還那麼大聲.

感謝大大的回答~~
我還有個問題想請教~~!!
我的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]

您好,

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 (滷肉腳啦);
都已接手這麼久了, 績效還是那麼爛, 講話還那麼大聲.

大大您好~~我已修改但是還是跑不出來~~
是否把欄位放錯了呢?
對於刪除我已下條件..但仍會刪除全部資料..這是為什麼呢..謝謝

[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]

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?

謝謝大大的回答~~
已經修改沒什麼問題了~~
最後修改的結果↓
[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~~~

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?

感謝大大的回覆~~~
讓我嚇到 >口<..因為太即時了..
真的太棒了啦~~
超感動的 ^~^..
等我有實力回答回人時,肯定努力回答^Q^