oracle PL/SQL 函数返回 varchar2/numbers
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33750936/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PL / SQL Function to return varchar2 / numbers
提问by rob
I have this PL / SQL function that accepts the name of a student (f_name). The function then displays all of the information for the given student from a premade table called students. The table contains 5 columns, 2 number type, and 3 varchar2 type. If the name isn't found in the table an error message is returned. My code so far is
我有这个接受学生姓名 (f_name) 的 PL/SQL 函数。然后，该函数从名为 student 的预制表中显示给定学生的所有信息。该表包含 5 列、2 个数字类型和 3 个 varchar2 类型。如果在表中找不到该名称，则会返回一条错误消息。到目前为止我的代码是
CREATE OR REPLACE FUNCTION studentName(
f_name IN VARCHAR2)
RETURN
IS
v_test students%rowtype;
CURSOR c1
IS
SELECT * FROM students WHERE first_name = f_name;
BEGIN
OPEN c1;
FETCH c1 INTO v_test;
IF c1%notfound THEN
v_test := NULL;
END IF;
CLOSE c1;
RETURN v_test;
END;
I keep getting:
我不断得到：
PLS-00382: expression is of wrong type
PLS-00382: 表达式类型错误
I believe from my initial return varchar2 statement. How do I allow the return to accept both varchar2 type and number type?
我相信从我最初的 return varchar2 声明。如何允许返回同时接受 varchar2 类型和数字类型？
回答by Lalit Kumar B
RETURN varchar2
返回 varchar2
You need to return the rowtype, but you are returning a scalar. VARCHAR2cannot hold a row, it can hold only a string value.
您需要返回rowtype，但您返回的是scalar。VARCHAR2不能容纳一行，它只能容纳一个字符串值。
Modify it to:
修改为：
RETURN students%rowtype;
Demousing standard EMP table:
使用标准 EMP 表的演示：
SQL> CREATE OR REPLACE FUNCTION studentName(
2 f_name IN VARCHAR2)
3 RETURN emp%rowtype
4 IS
5 v_test emp%rowtype;
6 CURSOR c1
7 IS
8 SELECT * FROM emp WHERE ename = f_name;
9 BEGIN
10 OPEN c1;
11 FETCH c1 INTO v_test;
12 IF c1%notfound THEN
13 v_test := NULL;
14 END IF;
15 CLOSE c1;
16 RETURN v_test;
17 END;
18 /
Function created.
SQL> sho err
No errors.
NOTE: %ROWTYPEimplies PL/SQL record typeand PL/SQL types are not known to SQL. So you won't be able to use the function directly in plain SQL. You need to use SQL object type. Else you will get:
注意：%ROWTYPE暗示PL/SQL 记录类型和 PL/SQL 类型对于SQL 来说是未知的。因此，您将无法直接在纯 SQL 中使用该函数。您需要使用SQL 对象类型。否则你会得到：
ORA-06553: PLS-801: internal error [55018]
ORA-06553：PLS-801：内部错误 [55018]
Workaround to use it in SQL:
在 SQL 中使用它的解决方法：
SQL> create or replace
2 type student_obj_type
3 as object(
4 student_id number,
5 stu_name varchar2(20),
6 dept varchar2(20)
7 )
8 /
Type created.
Use student_obj_typeinstead of students%rowtypeto use the function in SQL.
使用student_obj_type而不是student%rowtype来使用SQL 中的函数。
