PL/SQL 编译错误，预期出现以下情况之一时遇到符号"CREATE"

Question

作为参考，我正在使用sqldeveloper-22.2.1.234.1810-x64，并且我面临以下错误：

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.

这只是一个计算前三名飞行员工资平均值的程序。我不想手动创建表，我宁愿把所有的东西都编码下来。

这是代码：

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;

Answer 1

正如Alex所评论的，在Oracle中，我们通常不会在PL/SQL中创建表；此外，在使用Oracle时，请使用其语法。

一旦你修复了错误，样本表就是

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

因为你的问题是如何

计算前三名飞行员工资的平均值

您实际上并不需要PL/SQL、循环之类的东西——一个查询就可以做到这一点。在下面的例子中，TEMPCTE用于"；排序"；工资按降序排列。要做到这一点，使用rank分析函数是因为——如果两个(或多个(飞行员的工资相同，则应计算所有飞行员的工资。然后，最终的select只计算前三名工资的平均值(正如我所说，这并不意味着这是三名飞行员！(：

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>

---

如果您坚持使用PL/SQL，则所有内容都必须是动态的，因为如果不是，则如果表(pilot(不存在，则PL/SQL过程将不会编译。因此：

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>

请注意，动态SQL可能会变得复杂且难以维护，因此。。。三思而后行。我建议你不要用它，除非你别无选择。