在oracle函数中返回查询(returning query in oracle function)
我是oracle数据库的新手。 尝试创建一个简单的函数,但它返回单引号的完整选择查询。 我不知道为什么。 善意的帮助。 是的! 它在函数编译结束时也说“提示:nm参数从未使用过”我很困惑。
-- function to return a single letter grade create or replace function update_grade(nm number) return varchar2 as grd varchar2(3); begin grd := 'select gradeid from grade where nm between marks_s and markks_e'; return grd; end;i am new to oracle database. Trying to make a simple function but it is returning complete select query which is in single quotes. I do not know why. kindly help. and yes! it in the end of function compiling also says "hint: nm parameter never used" I am confused.
-- function to return a single letter grade create or replace function update_grade(nm number) return varchar2 as grd varchar2(3); begin grd := 'select gradeid from grade where nm between marks_s and markks_e'; return grd; end;最满意答案
您没有执行任何查询。 您只是将查询字符串分配给返回的结果变量。 我没有尝试编译它,但是这样的事情:
create or replace function update_grade(nm number) return varchar2 as grd varchar2(3); begin SELECT gradeid INTO grd from grade where nm between marks_s and markks_e; return grd; end;如果没有找到数据,您可以考虑为案例添加处理:
EXCEPTION WHEN NO_DATA_FOUND THEN grd:= NULL;You are not executing any query. You are just assigning the query string to the returned result variable. I've not tried to compile it, but something like this:
create or replace function update_grade(nm number) return varchar2 as grd varchar2(3); begin SELECT gradeid INTO grd from grade where nm between marks_s and markks_e; return grd; end;You might consider adding handling for the case when no data is found:
EXCEPTION WHEN NO_DATA_FOUND THEN grd:= NULL;
更多推荐
发布评论