时间戳

首页 > 解决方案 > 在 Oracle SQL 的功能块中声明数字变量时出错

oracle - 在 Oracle SQL 的功能块中声明数字变量时出错

问题描述

SQL 工作表

create or replace FUNCTION checkSubmitted (pStudentId IN VARCHAR, pCourseCode IN VARCHAR, pAssignmentNumber IN NUMBER)
    RETURN VARCHAR
    IS output VARCHAR(20);
    DECLARE mark_result NUMBER;
BEGIN
    SELECT assignment.mark INTO mark_result FROM Assignment WHERE student_id = pStudentId AND course_code = pCourseCode AND assignment_number = pAssignmentNumber;
        IF (mark_result IS NOT NULL) THEN
            RETURN 'Submitted';
        ELSE
            RETURN 'Not Submitted';
        END IF;
END;

编译器 - 日志

LINE/COL  ERROR
--------- -------------------------------------------------------------
4/5       PLS-00103: Encountered the symbol "DECLARE" when expecting one of the following:     begin function pragma procedure subtype type <an identifier>    <a double-quoted delimited-identifier> current cursor delete    exists prior The symbol "begin" was substituted for "DECLARE" to continue. 
12/4      PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:     ( begin case declare end exception 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 
Errors: check compiler log

我想创建一个 SQL 检查函数，我需要在函数中声明一个变量。请告知我该如何实现这一目标。

标签： oracleplsqlstored-functions

解决方案

正如所评论的，您当前的问题很容易解决。

但是，我想指出另一个可能的“错误”——如果没有这些参数值组合的数据会发生什么？SELECT不会返回NULL- 它会引发NO_DATA_FOUND异常，你应该处理它。（我认为查询返回多个值的可能性不大；如果是，也处理它）。

另外，尽量避免使用多个RETURN语句。程序可能很长，您可能会迷失在返回的地点和返回的内容上。设置输出变量的值（您声明但从未使用过），并在过程结束时返回它。

因此，考虑这样的事情：

create or replace function checksubmitted 
  (pstudentid in varchar, pcoursecode in varchar, passignmentnumber in number)
return varchar
is 
  mark_result number;
  output      varchar(100);
begin
  select a.mark 
    into mark_result 
    from assignment a
    where a.student_id        = pstudentid 
      and a.course_code       = pcoursecode 
      and a.assignment_number = passignmentnumber;
      
  if mark_result is not null then
     output := 'Submitted';
  else
     output := 'Not Submitted';
  end if;
  
  return output;

exception
  when no_data_found then
    output := 'There is no data for these input parameters';
    return output;
end;
/

推荐阅读