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

Waseem · Accepted Answer

- 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

TheMadProfessor · Answer

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

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 <as suitable> return "replica # " + @copy_id + " appeared at"

Trending News

pl/sql help needed procedure?

3 Answers