我知道这似乎是一件基本的事情,但我以前从未这样做过.
作为Oracle PL / sql函数的结果,我想从现有表中返回单个记录.我已经找到了一些不同的方法,但是我对最好的方法感兴趣(阅读：我对我发现的东西并不满意).
我正在做的是这个……我有一个名为’users’的表,我想要一个函数’update_and_get_user’,它给出一个UserName(以及关于所述用户的其他可信信息)将可能执行各种操作’users’表,然后从所述表返回零或一行/记录.
这是我头脑中代码的基本轮廓(读：不知道语法是否接近正确)：
CREATE FUNCTION update_and_get_user(UserName in VARCHAR2,OtherStuff in VARCHAR2)
RETURN users PIPELINED IS
TYPE ref0 IS REF CURSOR;
cur0 ref0;
output_rec users%ROWTYPE;
BEGIN
-- Do stuff
-- Return the row (or nothing)
OPEN cur0 FOR 'SELECT * FROM users WHERE username = :1'
USING UserName;
LOOP
FETCH cur0 INTO output_rec;
EXIT WHEN cur0%NOTFOUND;
PIPE ROW(output_rec);
END LOOP;
END update_and_get_user;
我已经看到了返回记录或表的示例,事先已创建/声明的记录或表的类型,但似乎如果已经定义了表,我应该能够利用它,因此没有如果进行了表更改,则担心同步类型声明代码.
我对所有可能的解决方案和评论持开放态度,但我确实希望将其保留在单个PL / sql函数中(与其他一些与数据库进行多次通信的语言/框架中的代码相反,完成某种形式的’SELECT * FROM users WHERE username = blah’)作为调用函数的系统,数据库本身可能是不同的城市.超出这个限制,我愿意改变我的想法.
我就是这样做的.变量/表名/列名在Oracle中不区分大小写,因此我将使用user_name而不是UserName.
CREATE TABLE users( UserName varchar2(20),OtherStuff VARCHAR2(20) );
函数update_and_get_user.请注意,我返回的是ROWTYPE而不是Pipelined Tables.
CREATE OR REPLACE FUNCTION update_and_get_user(
in_UserName IN users.UserName%TYPE,in_OtherStuff IN users.OtherStuff%TYPE )
RETURN users%ROWTYPE
IS
output_rec users%ROWTYPE;
BEGIN
UPDATE users
SET OtherStuff = in_OtherStuff
WHERE UserName = in_UserName
RETURNING UserName,OtherStuff
INTO output_rec;
RETURN output_rec;
END update_and_get_user;
这就是你怎么称呼它.您无法将ROWTYPE检查为NULL,但您可以检查用户名,例如.
DECLARE
users_rec users%ROWTYPE;
BEGIN
users_rec := update_and_get_user('user','stuff');
IF( users_rec.username IS NOT NULL ) THEN
dbms_output.put_line('FOUND: ' || users_rec.otherstuff);
END IF;
END;
使用PIPED ROWS的解决方案如下,但它不起作用.您无法更新查询中的表.
SELECT * FROM TABLE(update_and_get_user('user','stuff'))
ORA-14551: cannot perform a DML operation inside a query
解决方案看起来像这样：
CREATE OR REPLACE TYPE users_type
AS OBJECT
(
username VARCHAR2(20),otherstuff VARCHAR2(20)
)
CREATE OR REPLACE TYPE users_tab
AS TABLE OF users_type;
CREATE OR REPLACE FUNCTION update_and_get_user(
in_UserName IN users.username%TYPE,in_OtherStuff IN users.otherstuff%TYPE )
RETURN users_tab PIPELINED
IS
output_rec users%ROWTYPE;
BEGIN
UPDATE users
SET OtherStuff = in_OtherStuff
WHERE UserName = in_UserName
RETURNING username,otherstuff
INTO output_rec;
PIPE ROW(users_type(output_rec.username,output_rec.otherstuff));
END;
