在 Oracle 函数中返回单行

Question

我正在尝试根据用户的输入从函数返回一行用户数据。我无法访问对象列的值。我已经在这里尝试了过程解决方案，并在这里复制了解决方案(使用ORACLE和SQLDeveloper(

CREATE OR REPLACE FUNCTION F_FINDBYSSN (SSN VARCHAR2)
RETURN T_USEROBJECT
AS
THISUSER   T_USEROBJECT;
BEGIN
SELECT T_USEROBJECT (A.USER_ID,
A.FIRSTNAME,
A.LASTNAME,
A.SOCIAL,
A.BIRTHDATE,
A.PHONE,
A.USER_ROLE_TYPE_ID,
A.USER_LOGIN_ID,
A.USER_LAST_LOGIN,
B.LOGIN_USERNAME,
B.LOGIN_PASSWORD)
INTO THISUSER
FROM BANK_USERS A
INNER JOIN LOGININFO B
ON     A.USER_LOGIN_ID = B.LOGIN_ID
AND A.SOCIAL = SSN;
RETURN THISUSER;
END;
/

对象是用户

CREATE OR REPLACE TYPE T_USEROBJECT IS OBJECT
(
USERID INTEGER,
FNAME VARCHAR2 (50),
LNAME VARCHAR2 (50),
SSN VARCHAR2 (15),
BIRTHDATE DATE,
USERPHONE VARCHAR2 (15),
ROLEID INT,
LOGINID INT,
LASTLOGIN DATE,
UNAME VARCHAR2 (50),
UPASS VARCHAR (50)
);
/

这将输出 [T_USEROBJECT]

SELECT *
FROM ( SELECT F_FINDBYSSN('123-45-6789') AS THISUSER FROM DUAL );

在 select 语句中使用任何列名都会导致"标识符无效"错误

Answer 1

我找到了一种方法，但是检索单行是否矫枉过正？

CREATE OR REPLACE TYPE t_singleuserset IS TABLE OF t_userobject;
/
CREATE OR REPLACE FUNCTION select_userrow (ssn IN VARCHAR)
RETURN t_singleuserset
AS
l_user   t_singleuserset := t_singleuserset ();
n        INTEGER := 0;
BEGIN
FOR i IN (SELECT a.user_id,
a.firstname,
a.lastname,
a.social,
a.birthdate,
a.phone,
a.user_role_type_id,
a.user_login_id,
a.user_last_login,
b.login_username,
b.login_password
FROM bank_users a
INNER JOIN logininfo b
ON     a.user_login_id = b.login_id
AND a.social = ssn)
LOOP
l_user.EXTEND ();
n := n + 1;
l_user (n) :=
t_userobject (i.user_id,
i.firstname,
i.lastname,
i.social,
i.birthdate,
i.phone,
i.user_role_type_id,
i.user_login_id,
i.user_last_login,
i.login_username,
i.login_password);
END LOOP;
RETURN l_user;
END;
/
SELECT * FROM TABLE (select_userrow ('123-45-6789'));

Answer 2

在这里找到了答案。 需要像这样使用治疗函数

SELECT TREAT(THISUSER AS T_USEROBJECT).USERID, TREAT(THISUSER AS T_USEROBJECT).FNAME
FROM ( SELECT * FROM (SELECT F_FINDBYSSN('123-45-6789') AS THISUSER FROM DUAL ));

如何从甲骨文11g中的对象类型列中选择？