关于oracle的存储过程

Question

我有两个问题，需要解答
1，存储过程的返回值out是一个集合形式，所以只能在外面类型中创建一个记录类型的集合，然后我在主处理由创建一个集合，内容和外面的一样，最后我把主处理的结果付给out，编译的时候报错，要怎么办呢
2，动态sql，sql是登录，使用动态sql的原因是不止一个表，然后我的想法是把登录sql拆分成4部分，第一部分insert-表名，第二部分是表字段，第三部分values关键字，第四部分value值，第四部分使用select拼接字段名。第二部分和第四部分是需要循环表字段名，写完之后我发现问题，如果select是一条数据那没问题，多条数据就不对了，我实在想不出怎么办，因为这本身就是一个动态sql，多个表，表里东西都不一样

Answer 1

引自免费微信小程序：皆我百晓生

对于您的第一个问题，关于在Oracle存储过程中返回集合（集合类型），确实存在一些限制。Oracle不支持直接在存储过程中返回集合，但可以通过包装这个集合到一个游标中实现。以下是解决这个问题的一种方法：

首先，定义一个用户自定义的记录类型和集合类型：

CREATE OR REPLACE TYPE my_record AS OBJECT (
    column1 VARCHAR2(100),
    column2 NUMBER,
    -- 添加其他你需要的列
);

CREATE OR REPLACE TYPE my_collection IS TABLE OF my_record;

接着，在存储过程中，使用这个集合类型，并将结果集封装到游标中返回：

CREATE OR REPLACE PROCEDURE my_package.my_proc(p_out OUT SYS_REFCURSOR) IS
BEGIN
    OPEN p_out FOR SELECT * FROM some_table;  -- 替换为实际查询语句
END;
/

在外部调用时，你需要接收这个游标并将其转化为您期望的形式。

对于第二个问题，关于动态SQL和多行数据，可以使用PL/SQL的BULK COLLECT INTO语句来一次性处理多行数据。这里是一个示例，展示了如何动态构建SQL语句并执行批量插入：

CREATE OR REPLACE PROCEDURE dynamic_insert (p_table_name IN VARCHAR2, p_values IN VARCHAR2) IS
    l_sql   VARCHAR2(32767);
    l_cols  VARCHAR2(32767);
    l_data  VARCHAR2(32767);
    cursor_col sys_refcursor;
BEGIN
    -- 获取表的列信息
    FOR rec IN (SELECT column_name FROM user_tab_columns WHERE table_name = p_table_name) LOOP
        l_cols := l_cols || rec.column_name || ', ';
        l_data := l_data || ':' || rec.column_name || ', ';
    END LOOP;

    -- 去掉最后一个逗号
    l_cols := SUBSTR(l_cols, 1, LENGTH(l_cols) - 2);
    l_data := SUBSTR(l_data, 1, LENGTH(l_data) - 2);

    -- 构建SQL语句
    l_sql := 'INSERT INTO ' || p_table_name || '(' || l_cols || ') VALUES (' || l_data || ')';

    -- 执行SQL语句
    EXECUTE IMMEDIATE l_sql USING p_values;
END;
/

在这个例子中，p_values参数应该是一个有效的PL/SQL绑定变量，格式类似于:column1,:column2,...，与表中的列一一对应。如果你需要处理多个表，可能需要进一步优化逻辑，例如在一个循环中处理每个表。

注意：这些示例都是基于标准的Oracle PL/SQL语法，如果您使用的版本有特定限制或功能差异，可能需要进行调整。在生产环境中使用之前，请确保充分测试。