Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Posts
    16

    Unanswered: I need Help, PLEASE

    I have the code :

    DECLARE
    PROCEDURE inboxToTopKey (p_input_text IN VARCHAR2,
    p_option IN VARCHAR2,
    p_key_topic_id IN VARCHAR2,
    p_counter IN NUMBER) IS
    BEGIN
    IF INSTR(p_input_text, p_option) > 0 THEN
    INSERT INTO top_res (topic_result_id, topic_result_counter)
    VALUES (p_key_topic_id, p_counter);
    END IF;
    END;
    BEGIN
    << input_t_loop >>
    FOR cur_text IN (SELECT * FROM input_t) LOOP
    << top_key_loop >>
    FOR cur_tk IN (SELECT * from top_key) LOOP
    inboxToTopKey(cur_text.input_text, cur_tk.topic_keyword.option1, cur_tk.key_topic_id,
    cur_tk.topic_keyword_counter);
    inboxToTopKey(cur_text.input_text, cur_tk.topic_keyword.option2, cur_tk.key_topic_id,
    cur_tk.topic_keyword_counter);
    inboxToTopKey(cur_text.input_text, cur_tk.topic_keyword.option3, cur_tk.key_topic_id,
    cur_tk.topic_keyword_counter);
    inboxToTopKey(cur_text.input_text, cur_tk.topic_keyword.option4, cur_tk.key_topic_id,
    cur_tk.topic_keyword_counter);
    inboxToTopKey(cur_text.input_text, cur_tk.topic_keyword.option5, cur_tk.key_topic_id,
    cur_tk.topic_keyword_counter);
    inboxToTopKey(cur_text.input_text, cur_tk.topic_keyword.option6, cur_tk.key_topic_id,
    cur_tk.topic_keyword_counter);
    END LOOP top_key_loop;
    END LOOP input_t_loop;
    END;
    /

    DECLARE
    PROCEDURE topResToSelTop
    BEGIN
    select Topic_Result_ID from Top_Res
    IF -- dont know if this is needed
    (
    select Topic_Result_ID, count(*) Topic_Result_Counter from Top_Res -- used to be topic_count
    group by Topic_Result_ID
    order by Topic_Result_Counter desc -- used to be topic_count
    ) where rownum <2;
    --THEN
    insert into Sel_Top( Topic_Result_ID ) values ( v_result_id ); -- do i need to put .Sel_Topic_ID in ?? to show it where to go
    update Top_Key
    set Topic_Keyword_Counter = Topic_Keyword_Counter +1
    where Topic_Result_ID = v_result_id;

    ELSIF

    SELECT Topic_Result_ID
    FROM Top_Res
    IF
    (
    select Topic_Result_Counter
    order by Topic_Result_Counter desc
    ) where rownum <2;
    THEN
    insert into Sel_Top( Topic_Result_ID ) values ( v_result_id );
    update Top_Key
    set Topic_Keyword_Counter = Topic_Keyword_Counter +1
    where Topic_Result_ID = v_result_id;

    ELSE


    -- DO A RANDOM SELECT FORM AVALIBLE Topic_Result_ID's
    -- ADD 1 to Topic_Keyword_Counter
    -- INSERT Topic_Result_ID in Sel_Top table

    select Topic_Result_ID from
    ( select Topic_Result_ID from Top_Res order by dbms_random.value )
    where rownum = 1;
    then
    insert into Sel_Top( Topic_Result_ID ) values ( v_result_id );
    update Top_Key
    set Topic_Keyword_Counter = Topic_Keyword_Counter +1
    where Topic_Result_ID = v_result_id;

    END IF;
    END;
    /

    And it's all suppose to be in pl/sql but unfortunatly i seem to have writen it in a combination of both SQL and PL/SQL can anyone help me put it all in PL/SQL ???

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: I need Help, PLEASE

    Originally posted by innes_22
    ...
    And it's all suppose to be in pl/sql but unfortunatly i seem to have writen it in a combination of both SQL and PL/SQL can anyone help me put it all in PL/SQL ???
    I suspect not, without a spec. of what it is supposed to be doing. FYI, PL/SQL has 2 main ways of performing SELECT statements:

    1) "SELECT INTO". When your query is sure to return just one row you can do it like this:
    PHP Code:
    DECLARE
      
    v_name emp.ename%TYPE;
    BEGIN
      SELECT ename INTO v_ename
      FROM emp
      WHERE empno 
    123;
    END
    Your code may need to handle the exceptions NO_DATA_FOUND and/or TOO_MANY_ROWS for where the select returns 0 rows or >1 rows respectively.

    2) Cursors/loops:
    PHP Code:
    BEGIN
      
    FOR r IN (SELECT enameempno FROM emp
      
    LOOP
        DBMS_OUTPUT
    .PUT_LINE('Processing employee '||r.ename);
        ...
      
    END LOOP;
    END
    For more comprehensive details, see the PL/SQL Manual. As I have said before, it isn't really practical to try to learn PL/SQL via the forum. You should read the manual, or a good beginner's book, and start with a simple program and work up.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •