1 2 3 4 Назад Вперед 53 Ответы Последний ответ: 27.10.2008 19:30, автор: Satyaki_De

    Procedure to insert rows into signout board for recurring weekly entires

    656658
      I need help to Create a simple procedure to Insert rows into the Signout Board database for "recurring weekly" entries.
      The schema is SIGNOUT in the DEV10G database.
      There are three main tables: UKIMR, REASON, and SIGNOUT
      My initial thought is that the procedure takes as arguments:
      - last name
      - first name
      - start date
      - # of weeks
      - entry type (e.g. "Telecommuting")
      - comment
      Note: I'm using last/first name as args because that is how the columns are setup in the UKIMR table.
      So all it would do is create entries on the same day-of-the-week as the *"start date"* for as many weeks as you indicate in the *"# of weeks"* arg.
      So this would be a sample calling script:_
      *exec STP_InsertWeekly( 'Lastname', 'firstname', '10/3/2008', 5, 'Telecommuting', null );
      That would insert 5 SIGNOUT rows for the 5 Fridays in October.

      Edited by: user4653174 on Oct 3, 2008 10:28 AM

      Edited by: user4653174 on Oct 3, 2008 10:29 AM

      Edited by: user4653174 on Oct 3, 2008 10:31 AM
        • 1. Re: Procedure to insert rows into signout board for recurring weekly entires
          635300
          Can you please try yourself and let us know wherever you get error?
          • 2. Re: Procedure to insert rows into signout board for recurring weekly entires
            Nicolas.Gasparotto
            And how is your procedure code so far ?

            Nicolas.
            • 3. Re: Procedure to insert rows into signout board for recurring weekly entires
              Frank Kulash
              Hi,

              I assume you know how to insert a single row, on start_date, using INSERT ... VALUES.
              Code that much first.
              Then change the INSERT ... VALUES to INSERT ... <sub-query>, building on this as the sub-query:
              SELECT      start_date + (7 * (LEVEL - 1))
              FROM        dual
              CONNECT BY  LEVEL <= num_of_weeks;
              Run the query above by itself to see what it does.

              Edited by: Frank Kulash on Oct 3, 2008 2:32 PM

              Typo corrected.
              • 4. Re: Procedure to insert rows into signout board for recurring weekly entires
                Nicolas.Gasparotto
                The result is depending of the version.

                Nicolas.
                • 5. Re: Procedure to insert rows into signout board for recurring weekly entires
                  656658
                  I got the following error:
                  [1]: (Error): ORA-00923: FROM keyword not found where expected
                  • 6. Re: Procedure to insert rows into signout board for recurring weekly entires
                    Frank Kulash
                    Hi,

                    Sorry, I omitted a ( before LEVEL.

                    I meant:
                    SELECT      start_date + (7 * (LEVEL - 1))
                    FROM        dual
                    CONNECT BY  LEVEL <= num_of_weeks;
                    • 7. Re: Procedure to insert rows into signout board for recurring weekly entires
                      656658
                      Kulash,
                      unfortunately I doin't know how to insert a single row on start_date. It should be something like this:
                      INSERT INTO start_date( , )
                      VALUES(value1, value2, etc)
                      Is this correct?
                      • 8. Re: Procedure to insert rows into signout board for recurring weekly entires
                        656658
                        SELECT start_date + (7 * (LEVEL - 1))
                        FROM dual
                        CONNECT BY LEVEL <= num_of_weeks;

                        Did you mean adding a bracket infront of LEVEL like CONNECT BY (LEVEL <= num_of_weeks);
                        • 9. INSERT
                          Frank Kulash
                          Hi,
                          user4653174 wrote:
                          Kulash,
                          unfortunately I doin't know how to insert a single row on start_date. It should be something like this:
                          INSERT INTO start_date( , )
                          VALUES(value1, value2, etc)
                          Is this correct?
                          Yes, that's the basic idea, assuming your table is called start_date.

                          If you have a table called signout, which contains VARCHAR2 columns called last_name and first_name, and a DATE column called event_date, you can create a single new row in that table by saying:
                          INSERT INTO signout (first_name, last_name, event_date)
                                       VALUES ('Arpit',    'Shah',    TO_DATE ('10/03/2008', 'mm/dd/yyyy');
                          If you have a table (table_x) that contains a DATE column called dt, and you want to create a new row in singnout for every row in table_x, using a given name and the date from table_x.dt, here's how you could do that:
                          INSERT INTO signout (first_name, last_name, event_date)
                                       SELECT  'Arpit',    'Shah',    dt
                                       FROM    table_x;
                          Note that the word VALUES indicates you're doing a single-row INSERT, without a sub-query.

                          This is very fundamental and important stuff. Read the section on INSERT in any good introductory SQL textbook, and the [SQL Language Reference manual|http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_9014.htm#sthref8944].
                          • 10. Re: Procedure to insert rows into signout board for recurring weekly entires
                            Frank Kulash
                            Hi,
                            user4653174 wrote:
                            SELECT start_date + (7 * (LEVEL - 1))
                            FROM dual
                            CONNECT BY LEVEL <= num_of_weeks;

                            Did you mean adding a bracket infront of LEVEL like CONNECT BY (LEVEL <= num_of_weeks);
                            Sorry I was ambiguous.

                            No, I meant the first reference to LEVEL, in the SELECT-clause, as posted.
                            Adding ( before LEVEL in the CONNECT BY clause, and a balancing ) later on, as you did, won't help or hurt.

                            When you put in appropriate values for start_date and num_of_weeks, is it working now?
                            • 11. Re: Procedure to insert rows into signout board for recurring weekly entires
                              Maestro_Vineet
                              We can help you unless and until you provide us the piece of code that you have written or provide some sample data along with the desired output.

                              -----Vineet
                              • 12. Re: Procedure to insert rows into signout board for recurring weekly entires
                                656658
                                I know what I want but have no experince on how to do it. I am pretty new to PL/SQL that's why I am here asking for help.What I want is to use the procedure which will take the following:
                                - last name
                                - first name
                                - start date
                                - # of weeks
                                - entry type (e.g. "Telecommuting")
                                - comment

                                Note:  I'm using last/first name as args because that is how the columns are setup in the ISEMP table.
                                There are three main tables: ISEMP, REASON, and SIGNOUT

                                The procedure will create entries on the same day-of-the-week as the "start date" for as many weeks as you indicate in the "# of weeks" arg.

                                A sample calling script should look like this:
                                exec STP_InsertWeekly( 'wekau', 'Tinana', '10/3/2008', 5, 'Telecommuting', null );
                                This would insert 5 SIGNOUT rows for the 5 Fridays in October.
                                • 13. Re: Procedure to insert rows into signout board for recurring weekly entires
                                  Frank Kulash
                                  Hi,

                                  You want to do a multi-row INSERT, the basic sytax for which is:
                                  INSERT INTO signout (first_name, last_name, event_date)
                                               SELECT  'Arpit',    'Shah',    dt
                                               FROM    table_x;
                                  Instead of using literals, like 'Arpit', you will be using PL/SQL variables, passed to your procedure.
                                  In this case you do not have a table called table_x that provides the event_dates; but you can get the appropriate dates from the dual table, like this:
                                  SELECT      start_date + (7 * (LEVEL - 1))
                                  FROM        dual
                                  CONNECT BY  LEVEL <= num_of_weeks;
                                  I always write code in small amounts, test it, then add a few lines more. I suggest you do the same. Here are the steps I would recommend for this task:
                                  (1) In SQL (not PL/SQL) write a query, based on the dual table, that produces the dates you want.
                                  (2) In SQL (not PL/SQL) modify the query form (1) to include all the data you want to insert. (You may want to do this in several steps). Most of the columns will be literals, corresponding to the parameters you ultimately want to pass to the procedure.
                                  (3) In SQL (not PL/SQL) write an INSERT statement to add all the data from (2) in a single statement.
                                  (4) Write a PL/SQL procedure that does something (anything, such as dbms_output.put_line ('Hello, world!');) to verify that you can write a procedure.
                                  (5) Add a num_of_weeks parameter to that procedure.
                                  (6) Include num_of_weeks in the printout, to verify you are receiving the parameter correctly.
                                  (7) Add an INSERT statement to the procedure, to INSERT just the dates.
                                  (8) Add one other parameter to the procedure, and to the INSERT statement.
                                  (9) Add the other parameters to the procedure, and to the INSERT statement.

                                  If you get stuck, post
                                  (a) your code and
                                  (b) the error message, if any
                                  • 14. Re: Procedure to insert rows into signout board for recurring weekly entires
                                    656658
                                    More information for the prodecure:
                                    Tables: USER
                                    has the following columns:
                                    USER_KEY not null
                                    LName
                                    FName

                                    Table SIGNOUT
                                    has the following column
                                    USER_KEY not null
                                    ONDATE not null
                                    TIME_FROM
                                    TOME_TO
                                    REASON_KEY not null
                                    COMMENTS

                                    Table REASON
                                    has the following columns
                                    REASON_KEY not null
                                    REASON

                                    How is the procedure used? The employees will use a form to add and update signout entries. They will enter their name, date, time, reason, and comments.
                                    The entry can be for something repetitive like say work from home every Friday. So this will insert the dates they will work from home for the number of weeks they enter.

                                    Edited by: user4653174 on Oct 6, 2008 10:53 AM
                                    1 2 3 4 Назад Вперед