4 Replies Latest reply: Feb 20, 2009 7:23 AM by 453921 RSS

    Working with multiple tables

    453921
      Hello:

      I want to create an update form and a create form. I have three tables I need to use: TableA, TableB, and TableC. I have data in each of these tables that need to be updated. I cannot seem to figure out a way to create an update statement which will allow me to update all three tables.

      I'm having the same issue with inserting into multiple tables.

      When creating an APEX page, what type of page should I be selecting: form, report, etc.?
      Or, what kind of process do I need to create in order to write an update or insert statement which will allow me to udpate/insert my records?

      When I want to create a View only report, no problem here, I write a SQL statement joining all my tables.

      I cannot figure out an efficient way to udpate and insert into multiple tables.

      Can someone help?

      Thanks.
        • 1. Re: Working with multiple tables
          438381
          Hello:

          You will need to do one of the following

          Create a form based on a view and then write instead of triggers on the view to do the DML
          Create a form based on a pl/sql procedure which has the code to do the DML
          Create a form manually and create pl/sql page processes to do the DML

          Varad
          • 2. Re: Working with multiple tables
            453921
            Hello:

            I'm a beginner with PL/SQL so I'm taking things step-by-step. Would something like this be correct, at least for an insert statement with two tables?

            I created a PL/SQL anonymous block as a process. This is the code I wrote:
            declere
              l_dept number;
            begin
             insert into test1
              (deptno, deptname, location)
             values
              (:P17_deptno, :P17_deptname, :P17_location)
            returning deptno into l_dept;
            
             insert into test2
              (empid, deptid, empname)
             values
              (:P17_empid, l_dept, :P17_empname);
            end;
            If I wanted to add a third table to the insert statement, would I just add another insert statement? Would I need to declare another variable similar to l_dept?


            And for an update statement, would this be correct?
            declere
              l_dept number;
            begin
             update test1
              set deptno=:P17_deptno, 
                   deptname=:P17_deptname,
                   location=:P17_location)
            returning deptno into l_dept;
            
             update test2
              set empid=:P17_empid,
                   deptid=l_dept,
                  empname=:P17_empname;
            end;
            Thank you for the help.
            • 3. Re: Working with multiple tables
              438381
              Hello:

              What you have is a good start save for a few syntax errors.
              If your form is going to provide a value for deptno then the 'l_dept' declaration is not needed.
              You want create one page process for each DML operation. This will make it easier to control the execution of the page processes.
              declare
                l_dept number;
              begin
               insert into test1
                (deptno, deptname, location)
               values
                (:P17_deptno, :P17_deptname, :P17_location)
              returning deptno into l_dept;
               
               insert into test2
                (empid, deptid, empname)
               values
                (:P17_empid, l_dept, :P17_empname);
              end;
              declare
                l_dept number;
              begin
               update test1
                set deptno=:P17_deptno, 
                     deptname=:P17_deptname,
                     location=:P17_location
              returning deptno into l_dept;
               
               update test2
                set empid=:P17_empid,
                     deptid=l_dept,
                    empname=:P17_empname;
              end;
              Varad
              • 4. Re: Working with multiple tables
                453921
                Hi,

                The test1 table will contain the deptno, deptname, and location for all company departments. The deptno is the primary key in this table. There will be times where new departments will be added as well as existing department information may change.

                The test2 table will contain information on all employees which include the department in which they are working. There will be times where an employee can move from one department to another and I would need to udpate their information to reflect the new department id.

                I can't remember why I declared l_dept. I thought that I needed to declare some kind of variable to hold the "new" department to which the employee will be assigned. I guess I don't have to do that. So, I should delete the declare piece as well as the anything reference the declared variable. Correct?

                Also, I can't seem to find any of other syntax errors.

                Thanks for the help.