通过代码块创建功能

问题描述：

如何将此代码转换为具有相同输出的函数？通过代码块创建功能

declare 
    record_name employees%ROWTYPE; 
begin 
    FOR record_name IN (SELECT (a.first_name || ' ' || a.last_name) complete_name, 
         b.DEPARTMENT_NAME complete_name2 
        FROM employees a , departments b 
        WHERE ROWNUM < 1000 and a.DEPARTMENT_ID=b.DEPARTMENT_ID) 
    LOOP 
    DBMS_OUTPUT.PUT_LINE('Employee name: ' || record_name.complete_name 
          ||'DEPARTMENT name: '||record_name.complete_name2); 
    END LOOP; 
END;

你只想打印还是希望函数返回记录集以便进一步操作？对于前者，请使用@Sajmon给出的答案，用Put_Line替换累积级联。对于后者，您需要创建一个表值函数。 – SQLCurious

答

create or replace function SOME_FUNCTION 
RETURN VARCHAR 
AS 
    record_name employees%ROWTYPE; 
    out_stmt varchar(4000); 
    TOO_LONG EXCEPTION; 
BEGIN 
    FOR record_name IN (SELECT (a.first_name || ' ' || a.last_name) complete_name, 
         b.DEPARTMENT_NAME complete_name2 
        FROM employees a , departments b 
        WHERE ROWNUM < 1000 and a.DEPARTMENT_ID=b.DEPARTMENT_ID) 
    LOOP 
    IF (LENGTH(out_stmt) < 4000) THEN 
     out_stmt := out_stmt || 'Employee name: ' || record_name.complete_name || 'DEPARTMENT name: '||record_name.complete_name2; 
    ELSE 
     RAISE TOO_LONG; 
    END IF; 
    END LOOP; 
    return out_stmt; 
EXCEPTION 
    WHEN TOO_LONG THEN 
     RETURN 'OVERFLOW'; 
END;

快速的例子，我不知道是否是某种类型没有限制的完整性，因为并不总是会varchar(4000)足够多。但我听说过这种类型的东西，但现在我不知道它是否存在。

当你将在应用层使用它时，必须以某种方式格式化返回的字符串。

从Oracle 10g开始，max varchar是4000.而且，你不觉得'+'符号赢了吗？为连接起作用？ [out_stmt：= out_stmt +'员工姓名：'] - 应该是|| – codingbiz

哦，我的傻瓜，谢谢，所以T-SQL对我有不好的影响.. – Sajmon

可能还想在那里有一个异常处理程序，以防你溢出你的out_stmt变量。 – DCookie

答

CREATE TYPE some_employees_record IS RECORD 
    (emp_name VARCHAR2(4000) 
    ,dept_name VARCHAR2(4000)); 
CREATE TYPE employees_tab_type IS TABLE OF some_employees_record; 

CREATE OR REPLACE 
FUNCTION some_employees 
RETURN employees_tab_type 
PIPELINED 
IS 
    rec some_employees_record; 
BEGIN 
    FOR record_name IN (SELECT (a.first_name || ' ' || a.last_name) complete_name, 
         b.DEPARTMENT_NAME complete_name2 
        FROM employees a , departments b 
        WHERE ROWNUM < 1000 and a.DEPARTMENT_ID=b.DEPARTMENT_ID) 
    LOOP 
    rec.emp_name := record_name.complete_name; 
    rec.dept_name := record_name.complete_name2; 
    PIPE ROW (rec); 
    END LOOP; 
    RETURN; 
END;

您可以通过两种方式调用上述函数。在SQL：

SELECT * FROM TABLE(some_employees);

或PL/SQL：

DECLARE 
    rt employees_tab_type; 
BEGIN 
    rt := some_employees; 
    FOR i IN 1..rt.COUNT LOOP 
    DBMS_OUTPUT.put_line(rt(i).emp_name || ', ' || rt(i).dept_name); 
    END LOOP; 
END;

OT：你知不知道该查询返回一个有效的随机（不确定性）一套999条记录，如果记录数量超过999 ？

顺便说一句，这只是为了举例的目的。如果你唯一的要求是一个函数运行查询，我不会这样做。我会返回一个游标，或者（甚至更好）将查询放在视图中。

通过代码块创建功能

相关推荐