如何将表名作为参数传递给存储过程? [英] how to pass a table name as parameter to stored procedure?

问题描述

是否可以为作为参数传递给Stored-Procedure的表名创建rowtype，以及如何知道在DBMS_OUUTPUT.PUT_LINE()语句中寻址它们的列.

Is it possible to create a rowtype for a table name which is passed as a parameter to a Stored-Procedure and also how do i know the columns to address them in the DBMS_OUUTPUT.PUT_LINE() statement.

最终用户可以提供任何用户名(模式)和表名

The end user can give any user name(schema) and table name

我想做以下事情，但是不起作用.

I want to do something as below but it does not work.

 CREATE OR REPLACE PROCEDURE SP_PASS(USER_NAME VARCHAR2,TAB_NAME IN VARCHAR2)
 AS
    TYPE REF_CUR IS REF CURSOR;
    V_ARR REF_CUR;
    V_SQL VARCHAR(200);
    V_ROWTYPE USER_NAME.TAB_NAME%ROWTYPE;
 BEGIN
     V_SQL := 'SELECT * FROM '||USER_NAME||'.'||TAB_NAME;
     OPEN V_ARR FOR V_SQL;
     LOOP
     FETCH V_ARR INTO V_ROWTYPE;
     EXIT WHEN V_ARR%NOT FOUND;
     DBMS_OUTPUT.PUT_LINE(V_ROWTYPE.COL1||','||V_ROWTYPE.COL2||','||V_ROWTYPE.COL3);
     END LOOP;
     CLOSE V_ARR;
END;

让我知道是否可行.

谢谢.

推荐答案

您无法为未知表创建%ROWTYPE变量，并且当您不知道表名时也不能静态引用列名在编译时.

You can't create a %ROWTYPE variable for an unknown table and you can't statically refer to column names when you don't know the table name at compile time.

您可以使用 dbms_sql包处理完全动态的SQL语句.您将需要准备SQL语句，描述列以找出列数及其数据类型，绑定适当的变量，然后获取数据.与您发布的示例相比，这是一种繁琐的代码编写方式，但是它为您提供了极大的灵活性.

You can use the dbms_sql package to deal with completely dynamic SQL statements. You'll need to prepare the SQL statement, describe the columns to find out the number of columns and their data types, bind appropriate variables, and then fetch the data. It's a much more cumbersome way of writing code than the example you posted but it gives you extreme flexibility.

我链接到的文档中有许多使用dbms_sql包的示例.您可能还需要查看Tom Kyte的 dump_csv函数，该函数使用UTL_FILE将任意查询的结果写入CSV文件.如果您确实想将数据写入DBMS_OUTPUT，则只需将UTL_FILE调用替换为DBMS_OUTPUT.但是我敢肯定，您要做的事情不仅仅是将数据写入DBMS_OUTPUT缓冲区，这样汤姆的过程可能更接近您要真正实现的过程.

There are a number of examples of using the dbms_sql package in the documentation I linked to. You may also want to check out Tom Kyte's dump_csv function which writes the result of an arbitrary query to a CSV file using UTL_FILE. If you really want to write the data to DBMS_OUTPUT, you could simply replace the UTL_FILE calls with DBMS_OUTPUT. But I'm pretty sure you want to do something more useful than just writing the data to the DBMS_OUTPUT buffer so Tom's procedure is probably closer to what you're really trying to accomplish.

这篇关于如何将表名作为参数传递给存储过程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！