PL/SQL compilation error, Encountered the symbol "CREATE" when expecting one of the follow-CodePudding

For reference, I'm using sqldeveloper-22.2.1.234.1810-x64 and I'm facing the following error:

Error report -
ORA-06550: line 12, column 1:
PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:

   ( begin case declare exit for goto if loop mod null pragma
   raise return select update while with <an identifier>
   <a double-quoted delimited-identifier> <a bind variable> <<
   continue close current delete fetch lock insert open rollback
   savepoint set sql execute commit forall merge pipe purge
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.

This is simply a program that calculates the average of the top 3 pilot salaries. I don't want to create the tables manually, I would rather have everything coded down.

Here is the code:

DECLARE
v_max1 REAL := 0.0;
v_max2 REAL := 0.0;
v_max3 REAL := 0.0;
moy REAL := 0.0;
temp REAL := 0.0;
v_num INTEGER := 0;

BEGIN

CREATE TABLE IF NOT EXISTS pilot(
id INTEGER PRIMARY KEY, name VARCHAR(64), city VARCHAR(64), age INTEGER, salary REAL);

INSERT INTO OR IGNORE pilot(1, "john", "portland", 34, 35000.0)
INSERT INTO OR IGNORE pilot(2, "sam", "nyc", 29, 30000.0)
INSERT INTO OR IGNORE pilot(3, "kim", "nashville", 39, 40000.0)
INSERT INTO OR IGNORE pilot(4, "dan", "san antonio", 41, 45000.0)


SELECT COUNT(id) INTO v_num
SELECT MIN(salary) INTO v_max1

v_max2 := v_max1
v_max3 := v_max2


FOR I IN 1..v_num LOOP
    SELECT salary INTO temp FROM pilot WHERE id = I
        IF temp > v_max1 THEN
            v_max2 := v_max1
            v_max1 := temp
        ELSE IF temp > v_max2 THEN
            v_max3 := v_max2
            v_max2 := temp
        ELSE IF temp > v_max3 THEN
            v_max3 := temp
        END IF;   
END LOOP;

moy := (v_max1   v_max2   v_max3 / 3)

END;

CodePudding user response：

As Alex commented, in Oracle, we usually don't create tables in PL/SQL; also, when working with Oracle, use its syntax.

Once you fix the errors, sample table is

SQL> CREATE TABLE pilot(
  2  id INTEGER PRIMARY KEY, name VARCHAR(64), city VARCHAR(64), age INTEGER, salary REAL);

Table created.

SQL> INSERT INTO  pilot values (1, 'john', 'portland', 34, 35000.0);

1 row created.

SQL> INSERT INTO  pilot values (2, 'sam', 'nyc', 29, 30000.0);

1 row created.

SQL> INSERT INTO  pilot values (3, 'kim', 'nashville', 39, 40000.0);

1 row created.

SQL> INSERT INTO  pilot values (4, 'dan', 'san antonio', 41, 45000.0);

1 row created.

SQL> select * from pilot order by salary desc;

        ID NAME       CITY                AGE     SALARY
---------- ---------- ------------ ---------- ----------
         4 dan        san antonio          41      45000   --> these are
         3 kim        nashville            39      40000   --> the top 3
         1 john       portland             34      35000   --> salaries
         2 sam        nyc                  29      30000

As your problem is how to

calculate the average of the top 3 pilot salaries

you don't really need PL/SQL, loops and stuff - a single query does that. In the following example, the TEMP CTE is used to "sort" salaries in descending order. To do that, the rank analytic function is used because - if two (or more) pilots have the same salary, all of them should be accounted. Then, the final select just calculates average for the top 3 salaries (which, as I said, doesn't mean that these are 3 pilots!):

SQL> with temp as
  2    (select id, name, city, age, salary,
  3       rank() over (order by salary desc) rn
  4     from pilot
  5    )
  6  select avg(salary) as avg_salary
  7  from temp
  8  where rn <= 3;

AVG_SALARY
----------
     40000

SQL>

If you insist on PL/SQL, everything has to be dynamic because - if it isn't, PL/SQL procedure won't compile if table (pilot) doesn't exist. So:

SQL> drop table pilot;

Table dropped.

SQL>
SQL> set serveroutput on
SQL> declare
  2    l_avg number;
  3  begin
  4    execute immediate 'CREATE TABLE pilot(
  5    id INTEGER PRIMARY KEY, name VARCHAR(64), city VARCHAR(64), age INTEGER, salary REAL)';
  6
  7    execute immediate q'[INSERT INTO  pilot values (1, 'john', 'portland', 34, 35000.0)]';
  8    execute immediate q'[INSERT INTO  pilot values (2, 'sam', 'nyc', 29, 30000.0)]';
  9    execute immediate q'[INSERT INTO  pilot values (3, 'kim', 'nashville', 39, 40000.0)]';
 10    execute immediate q'[INSERT INTO  pilot values (4, 'dan', 'san antonio', 41, 45000.0)]';
 11
 12    execute immediate
 13    'with temp as
 14      (select id, name, city, age, salary,
 15         rank() over (order by salary desc) rn
 16       from pilot
 17      )
 18    select avg(salary)
 19    from temp
 20    where rn <= 3' into l_avg;
 21
 22    dbms_output.put_line('Average salary = ' || l_avg);
 23  end;
 24  /
Average salary = 40000

PL/SQL procedure successfully completed.

SQL>

Note that dynamic SQL can get complex and difficult to maintain, so ... think twice. I'd suggest you not to use it unless you absolutely have no other choice.