Ben Ben - 1 month ago 9
SQL Question

SQL Procedure - SELECT INTO issues

I am trying to SELECT INTO a scalar variable I created which I am getting errors when compiling on:

create or replace PROCEDURE NEW_COURSE
(
c_course_ID COURSE.COURSEID%type,
c_dept_ID COURSE.DEPTID%type,
c_coursename COURSE.COURSENAME%type,
c_textbook COURSE.TEXTBOOK%type,
c_credithour COURSE.CREDITHOUR%type,
c_max_enrol COURSE.MAX_ENROL%type,
c_actual_enrol COURSE.ACTUAL_ENROL%type,
c_availability COURSE.AVAILABILITY%type,
p_prerequisite_ID HAS_PREREQUISITE.PREREQUISITEID%type,
)

AS

BEGIN
DECLARE l_prerequisite_course VARCHAR2(10);
select c.courseid into l_prerequisite_course
from course c
where c.courseid = p_prerequisite_id;

IF substr(l_prerequisite_course, 1, 4) = substr(p_prerequisite_id, 1, 4)
OR substr(l_prerequisite_course, 5, 1) < substr(p_prerequisite_id, 5, 1)
THEN
INSERT INTO COURSE (COURSEID, DEPTID, COURSENAME, TEXTBOOK, CREDITHOUR, MAX_ENROL, ACTUAL_ENROL, AVAILABILITY)
VALUES (c_course_ID, c_dept_ID, c_coursename, c_textbook, c_credithour, c_max_enrol, c_actual_enrol, c_availability);
ELSE RAISE NO_DATA_FOUND;
END IF;

EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('Please enter a valid prerequisite!');


END NEW_COURSE;


Errors occurring:


Error(20,1): PLS-00103: Encountered the symbol "SELECT" when expecting
one of the following: begin function pragma procedure subtype type
current
cursor delete exists prior

Answer

The variable declarations go before the begin:

create or replace PROCEDURE NEW_COURSE (
    c_course_ID      COURSE.COURSEID%type,
    c_dept_ID         COURSE.DEPTID%type,
    c_coursename    COURSE.COURSENAME%type,
    c_textbook         COURSE.TEXTBOOK%type,
    c_credithour       COURSE.CREDITHOUR%type,
    c_max_enrol       COURSE.MAX_ENROL%type,
    c_actual_enrol    COURSE.ACTUAL_ENROL%type,
    c_availability      COURSE.AVAILABILITY%type,
    p_prerequisite_ID  HAS_PREREQUISITE.PREREQUISITEID%type,
) as
    l_prerequisite_course VARCHAR2(10);
begin
    select c.courseid into l_prerequisite_course
    from course c 
    where c.courseid = p_prerequisite_id;
. . . .
Comments