如何在存储过程中将模式名称作为参数传递 [英] How to pass schema name as parameter in stored procedure

问题描述

我需要将模式名称作为参数传递给存储过程.但是我最终出现错误ORA00942:table or view does not exist.我在Google上搜索了很多，但没有找到任何解决方案.

I need to pass a schema name as a parameter to a stored procedure. But I end up with error ORA00942: table or view does not exist. I googled a lot but didn't find any solution.

实际上，在我们的应用程序中，我们正在一个模式中编写存储过程(SP)，并为所有其他模式引用相同的SP.

Actually in our application we are writing a Stored procedure (SP) in One schema and referring the same SP for all other schemas.

考虑一下，我必须在其他模式(1个客户端的1个模式)中查找某项商品的库存.然后

Consider I have to find the stock of an item in a different schema (1 schema for 1 client). Then

select * from abc.stock_table where itemid=xxx;

在此查询中，我想用不同的架构名称替换abc.

In this query I want to replace abc with different schema names.

推荐答案

您需要使用Dynamic SQL，Internet上有很多资料. 例如在 Oracle网站

You need use Dynamic SQL, there are tons of material on Internet. e.g. on oracle website

CREATE OR REPLACE PROCEDURE query_invoice(
       month VARCHAR2, 
       year VARCHAR2) IS
    TYPE cur_typ IS REF CURSOR;
    c cur_typ;
    query_str VARCHAR2(200);
    inv_num NUMBER;
    inv_cust VARCHAR2(20);
    inv_amt NUMBER;
BEGIN
    query_str := 'SELECT num, cust, amt FROM inv_' || month ||'_'|| year 
      || ' WHERE invnum = :id';
    OPEN c FOR query_str USING inv_num;
    LOOP
        FETCH c INTO inv_num, inv_cust, inv_amt;
        EXIT WHEN c%NOTFOUND;
        -- process row here
    END LOOP;
    CLOSE c;
END;
/

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