pl/sql help needed procedure?

Question

create table MEMBER(
Member_ID NUMBER PRIMARY KEY,
First_Name VARCHAR2(25),
Last_Name VARCHAR2(25),
Street VARCHAR2(25),
City VARCHAR2(15),
Phone CHAR(11),
Valid_Date DATE,
message VARCHAR2(200))
/


this is my code:
it is suppose to add a new member if they do not already exist and give them a new memberid





CREATE OR REPLACE PROCEDURE add_check_member(
p_firstName member.first_name%TYPE, p_lastName member.last_name%TYPE,
p_street member.street%TYPE, p_city members.city%TYPE , p_phone member.phone%TYPE , p_valid member.valid_date%TYPE
, p_memberId member.member_id%TYPE, p_message member.message%TYPE)
is
p_memberId member.id%TYPE :=0;
CURSOR member_cur
IS
SELECT*
FROM member
WHERE member_id = p_memberId;
member_rec member_cur%rowtype;
BEGIN
OPEN member_cur;
FETCH member_cur INTO member_rec;
IF member_cur%notfound THEN
SELECT MAX(S_MEMBER_ID) +1
INTO p_memberId
FROM member;
INSERT INTO member VALUES(
p_memberId, p_firstName, p_lastName, p_street, p_city, p_phone, null);
DBMS_OUTPUT.PUT_LINE('member ID is : ' || p_memberId);
DBMS_OUTPUT.PUT_LINE('New member enrollment is done!! : ' );
ELSE
select memberId
From member
WHERE last_name=p_lastName;
DBMS_OUTPUT.PUT_LINE('members id is' || memberId );
END IF;
CLOSE member_cur;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error Code: ' || SQLCODE);
DBMS_OUTPUT.PUT_LINE('Error Message: ' || SQLERRM);

END add_check_member;

this is my error code:
PL/SQL: Compilation unit analysis terminated
PLS-00410: duplicate fields in RECORD,TABLE or argument list are
not permitted

Answer 1

- No need for any cursor

- DON'T include insert statement with any stored procedure without mentioning the targeted columns.

- I prefer creating a sequence and call the nextval of it through a database trigger that should fire before insert on the member table.



Create sequence seq_members

/



Create or replace trigger generate_new_member_id_trg

before insert on members

for each row

begin

:new.member_id := seq_members.nextval;

end;

/



--Now you can create your procedure like this



Create or replace procedure add_member (p_firstName member.first_name%TYPE,

p_lastName member.last_name%TYPE,

p_street member.street%TYPE,

p_city members.city%TYPE,

p_phone member.phone%TYPE,

p_valid member.valid_date%TYPE,

p_message member.message%TYPE)

is

begin

insert into members

(first_name,last_name,street,city,phone,valid_date,message) values -- don't miss the targeted fields

(p_firstName,p_lastName,p_street,p_city ,p_phone,p_valid,p_message)

end;

-- no way the sequence can duplicate any value, anyway you have a primary key for member id field.



Good luck

Answer 2

First off, I'm puzzled why you use a cursor to check for existence. Since (presumably) memberId is unique, a simple ROWTYPE variable and select would work equally well. Better still (since you don't really do anything with the data you retrieve anyway) would simply have an integer local variable and select a count into it of rows with the memberId you intend to insert...if count = 0, then you can go ahead with the insert.



I'm also curious why you pass a valid_date parm but don't insert it (I gather that's the null?) You also recompute a new memberId value even tho you've already established that the one you passed isn't being used. If you're going to assign one anyway, what was the point of passing one and the cursor to begin with?



Another part that doesn't make sense is what you do if you did get a hit...the subsequent select is illogical. Why would you expect an already existing row having the memberId you tried to insert happen to have the same last name as the one you're trying to insert?

Answer 3

in the beginning, i does no longer bypass lower back status, on account that (assuming a valid titleID) does no longer that's 'OUT' regardless (the two already out or basically appeared at)? instead, i could bypass lower back some thing like the two "appeared at" or "Reserved", per what befell. 2d, i do no longer see the clarification for c1 - i'm assuming that member_id is unique, so it may return at maximum a million row (so why use a cursor?) and in spite of everything, you have have already got the member identity...that's without doubt one in all the enter parms. Likewise, c2 as written is ineffective...that's the different enter parm. in spite of the shown fact that, on account that distinctive copies are a hazard, a cursor would suitable right here different than one quite nonetheless isn't needed. What i could do, if surpassed @memberID and @titleID: a) validate member go with count huge form(*) INTO @myCount FROM member the place memberID = @memberID IF myCount = 0, return "Member no longer got here across" end_if b) validate identify go with count huge form(*) INTO @myCount FROM title_copy the place titleID = @titleID IF myCount = 0, return "identify no longer got here across" end_if c) See if any copies available and, if no longer, make a reservartion go with count huge form(*) INTO @myCount FROM title_copy the place titleID = @titleID and status = "IN" IF @myCount = 0 INSERT INTO reservation VALUES (GETDATE(), @memberID, @titleID) return "Reserved" end_if d) Get an available replica, examine it out and log the condominium go with MIN(copy_id) INTO @copy_id) FROM title_copy the place titleID = @titleID and status = "IN" replace title_copy SET status = "OUT" the place titleID = @titleID AND copy_id = @copy_id INSERT INTO condominium VALUES return "replica # " + @copy_id + " appeared at"