Forum Stats

  • 3,814,956 Users
  • 2,258,936 Discussions
  • 7,892,896 Comments

Discussions

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

user8816970
user8816970 Member Posts: 48
edited Mar 22, 2013 12:45PM in SQL & PL/SQL
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
Tagged:

Answers

  • jeneesh
    jeneesh Member Posts: 7,168
    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?
  • 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
    user8816970 Member Posts: 48
    edited Mar 22, 2013 12:45PM
    @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
This discussion has been closed.