How to write stored procedure for inserting into many to many relationships

user8816970

I have two tables: User and Project each with a integer PK of "id" and single data field of "name".
I have created a junction table ProjectsUsers that implements the many to many relationships between Users and Projects.

Can someone help me write a stored procedure to, given a User.id and a new project name, create a new row in the project table and update the junction table appropriately so it is owned by the user.

(1) I assume we want to start a new transaction
(2) What failures could occur that would cause me to want to roll back the transaction?
(3) How do I detect failures that would cause me to roll back the transaction? Do I check a global variable for errors? Or do I assume the insert statement will throw an exception I can catch?
(4) How do I get the id of the newly created project row and return it back to the calling program?
(5) How do I communicate to the calling program that the transaction failed and must be tried again?
Thanks
Siegfried

jeneesh

user8816970 wrote:
I have two tables: User and Project each with a integer PK of "id" and single data field of "name".
I have created a junction table ProjectsUsers that implements the many to many relationships between Users and Projects.

Can someone help me write a stored procedure to, given a User.id and a new project name, create a new row in the project table and update the junction table appropriately so it is owned by the user.

Please don't expect others to write a full code for you.. Better, try yourself, come back for help when you are facing a specific issue.
And read the FAQ: {message:id=9360002}

And if you are new to ORACLE technologies and PL/SQL, start learning : {message:id=9359970}
>

(1) I assume we want to start a new transaction

What does that mean? You only knows the requirements - if you assume, who will confirm?

(2) What failures could occur that would cause me to want to roll back the transaction?

That you need to ask your business requirement team or the technical design team..Without knowing these things, how can you start coding?

(3) How do I detect failures that would cause me to roll back the transaction? Do I check a global variable for errors? Or do I assume the insert statement will throw an exception I can catch?

Read about exceptions in the FAQ: {message:id=9360013}

(4) How do I get the id of the newly created project row and return it back to the calling program?

Read about sequences..

(5) How do I communicate to the calling program that the transaction failed and must be tried again?

That depends on the calling program. What is it ? How it calls? We dont know any of these things..How do you expect us to help you?

dilipkumar10285

hi,

Hope this will help.

create table Users (
id		number(5),
name	varchar2(30),
constraint pk_users_id primary key (id)
);

create sequence seq_users;

create table Projects (
id		number(5),
name	varchar2(30),
constraint pk_projects_id primary key (id)
);
create sequence seq_projects;

create table ProjectsUsers (
id		number(5),
user_id		number(5),
project_id		number(5),
constraint pk_projectsusers_id primary key (id)
);

create sequence seq_projectsusers;

create or replace procedure add_project_to_user(
	user_id IN Users.id%type,
	project_name IN Projects.name%type,
	project_id out Projects.id%type,
	error_msg out varchar2,
	error_key out varchar2
) as 
begin
	insert into Projects (id,name) values ( seq_projects.nextval,project_name) returning id into project_id;
	insert into ProjectsUsers (id,user_id,project_id) values (seq_projectsusers.nextval,user_id,project_id);
	commit;
	exception 
		when value_error then
             error_key := sqlcode;
			 error_msg := sqlerrm;
             rollback;
		when others then
             error_key := sqlcode;
			 error_msg := sqlerrm;
             rollback;
end add_project_to_user;


declare
	i_user_id Users.id%type := &User_id; -- any user_id which is present in the users table
	i_project_name Projects.name%type := '&ProjectName'; -- project name which u want to insert
	o_project_id Projects.id%type;-- project id which you will get after insertion into table.
	o_error_msg varchar2(255) := null;-- error msg for reference
	o_error_key varchar2(255) := null; -- error msg for reference
begin
	add_project_to_user(
		i_user_id,
		i_project_name,
		o_project_id,
		o_error_msg,
		o_error_key
	);
	
	if o_error_key is null then
		dbms_output.put_line('Newly inserted project id ' ||o_project_id); -- you can use your own logic here to use project_id
	else
		dbms_output.put_line('Error occcured ' ||o_error_msg ||' Error Key '||o_error_key); -- you can use your own logic here to check error
	end if;
end;


 drop procedure add_project_to_user;
 drop sequence seq_projectsusers;
 drop sequence seq_Projects;
 drop sequence seq_users;
 drop table Users;
 drop table Projects;
 drop table ProjectsUsers;

cheers
Dilipkumar

user8816970

@error) that I can check to tell me if a SQL insert statement was successful in increasing the number of rows in a table?
Can I assume that the above code did not use such a feature it is because it would be superfluous with the try and catch?

Might I want to use a while loop to repeatedly try to call the stored procedure until there are not errors because they only reason an insert would fail is because of temporary contention problems? I suppose I would not want to loop if I was running out of disk space...

How do you handle errors caused by contention?

Thanks
Siegfried

Edited by: user8816970 on Mar 22, 2013 9:41 AM

Edited by: user8816970 on Mar 22, 2013 9:42 AM