Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 254
  • Last Modified:

pl/sql

1/I want to create two  procedures :one accept a number, returns a list of courses that are on that same level. For example, if 1
is entered, all freshman level (100) courses are listed. If 2 is entered all 200 level courses are listed.

2/The second If a word is entered, returns a list of courses in which that word is found in the course title.
But I can't get them to work

CREATE OR REPLACE PROCEDURE topic_cursor(p_course IN course.course_no%TYPE)
          IS
        v_count INTEGER := 0;
        BEGIN
                   FOR vr_course IN (SELECT select description, course_no
            from course
            WHERE course_no like ('%' || p_course || '%')
            LOOP
            v_count := v_count + 1;
            DBMS_OUTPUT.put_line(vr_course.description|| ',   '|| vr_course.course_no );
            END LOOP;
            DBMS_OUTPUT.put_line('There are ' || v_count || ' classes.');
        EXCEPTION
                 WHEN OTHERS
            THEN
            DBMS_OUTPUT.put_line('An error has occurred');
          END;  



create or replace PROCEDURE topic_cursor(p_course IN course.description%TYPE)
    IS
        v_count INTEGER := 0;
    BEGIN
       FOR vr_course IN (select description, course_no
                          from course
                          WHERE UPPER(description) like UPPER('%' || p_course || '%')
       LOOP
            v_count := v_count + 1;
            DBMS_OUTPUT.put_line(
                   vr_course.description
                || ',   '
                || vr_course.course_no );
        END LOOP;

        DBMS_OUTPUT.put_line('There are ' || v_count || ' classes.');
    EXCEPTION
        WHEN OTHERS
        THEN
            DBMS_OUTPUT.put_line('An error has occurred');
    END;
0
lojayn
Asked:
lojayn
  • 2
1 Solution
 
sdstuberCommented:
are course_no columns numeric?
0
 
sdstuberCommented:

you have "select select" in the first procedure
and you are missing a ")" before the LOOP in both of them

so, before addressing any logic issues, try these that fix the syntax problems
CREATE OR REPLACE PROCEDURE topic_cursor(p_course IN course.course_no%TYPE)
IS
    v_count INTEGER := 0;
BEGIN
    FOR vr_course IN (SELECT description, course_no
                        FROM course
                       WHERE course_no LIKE ('%' || p_course || '%'))
    LOOP
        v_count := v_count + 1;
        DBMS_OUTPUT.put_line(vr_course.description || ',   ' || vr_course.course_no);
    END LOOP;

    DBMS_OUTPUT.put_line('There are ' || v_count || ' classes.');
EXCEPTION
    WHEN OTHERS
    THEN
        DBMS_OUTPUT.put_line('An error has occurred');
END;

CREATE OR REPLACE PROCEDURE topic_cursor(p_course IN course.description%TYPE)
IS
    v_count INTEGER := 0;
BEGIN
    FOR vr_course IN (SELECT description, course_no
                        FROM course
                       WHERE UPPER(description) LIKE UPPER('%' || p_course || '%'))
    LOOP
        v_count := v_count + 1;
        DBMS_OUTPUT.put_line(vr_course.description || ',   ' || vr_course.course_no);
    END LOOP;

    DBMS_OUTPUT.put_line('There are ' || v_count || ' classes.');
EXCEPTION
    WHEN OTHERS
    THEN
        DBMS_OUTPUT.put_line('An error has occurred');
END;

Open in new window

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now