oracle 存储过程执行动态sql范例

create or replace PROCEDURE PC_TEST_001
IS
/************************************************

存储过程执行动态sql范例

*************************************************/
BEGIN
    DECLARE
        vCount number;
        vTableName VARCHAR2(80);
        vSql varchar2(2000);
        vRegion varchar2(50);
        vCountryCode varchar2(50);
    BEGIN
        vCountryCode := 'US';
        SELECT COUNT(1)
        INTO vCount
        FROM emesu.ERP_TO_SFC_COUNTRY_INFO_PARTNO
        WHERE part_no = '5890-1691-00E0';
        IF vCount > 0 THEN
            BEGIN
                SELECT region_table_name
                INTO vTableName
                FROM emesu.ERP_TO_SFC_COUNTRY_INFO_PARTNO
                WHERE part_no = '5890-1691-00E0';
                
                --先查询有没有资料
                vSql := 'select count(1) from ' || vTableName || ' where COUNTRY_CODE =:contry_code';
                EXECUTE IMMEDIATE vSql INTO vCount USING vCountryCode;
                
                if vCount<=0 then
                    DBMS_OUTPUT.PUT_LINE('没有找到对应的地区');
                else
                    vSql := 'select nvl(REGION,'''') from ' || vTableName || ' where COUNTRY_CODE =:contry_code';
                    DBMS_OUTPUT.PUT_LINE('tableName:'||vTableName);
                    DBMS_OUTPUT.PUT_LINE('vSql:'||vSql);
                    --select REGION from EMESU.ERP_TO_SFC_COUNTRY_INFO_SPEC ;
                    EXECUTE IMMEDIATE vSql INTO vRegion USING vCountryCode;
                    DBMS_OUTPUT.PUT_LINE(vRegion);
                end if; 
            END;
        END IF;
    END;
END;