如何在Oracle中将表名作为参数传入？兼询函数与存储过程差异

Hey there! Let's break down your two questions clearly:

Since your original query uses static SQL (where table names can't be directly substituted with bind variables), you'll need to use dynamic SQL to make the table name a parameter. Here are two practical approaches for Oracle:

Option 1: Use a PL/SQL Anonymous Block (for ad-hoc execution) #

This lets you define the table name as a variable, safely construct the dynamic query, and execute it. We'll use DBMS_ASSERT.SQL_OBJECT_NAME to validate the table name and prevent SQL injection:

DECLARE
    v_table_name VARCHAR2(30) := 'EMPLOYEES'; -- Replace this with your parameter
    v_sql CLOB;
BEGIN
    -- Validate the table exists in your schema to avoid injection
    v_table_name := DBMS_ASSERT.SQL_OBJECT_NAME(v_table_name);
    
    v_sql := 'SELECT q1.table_name, q1.column_name, q1.data_type, q1.nullable, q2.comments ' ||
             'FROM (SELECT table_name, column_name, data_type, nullable FROM USER_TAB_COLUMNS WHERE TABLE_NAME = :tbl) q1 ' ||
             'JOIN (SELECT column_name, comments FROM USER_COL_COMMENTS WHERE TABLE_NAME = :tbl) q2 ' ||
             'ON q1.column_name = q2.column_name';
    
    -- Execute the query and output results (adjust based on your needs)
    FOR rec IN EXECUTE IMMEDIATE v_sql USING v_table_name, v_table_name LOOP
        DBMS_OUTPUT.PUT_LINE('Table: ' || rec.table_name || ', Column: ' || rec.column_name || ', Comment: ' || rec.comments);
    END LOOP;
END;
/

Option 2: Create a Reusable Stored Procedure #

If you need to run this frequently, wrap the logic in a procedure that accepts the table name as an input parameter:

CREATE OR REPLACE PROCEDURE get_table_columns(p_table_name IN VARCHAR2)
IS
    v_sql CLOB;
BEGIN
    p_table_name := DBMS_ASSERT.SQL_OBJECT_NAME(p_table_name);
    
    v_sql := 'SELECT q1.table_name, q1.column_name, q1.data_type, q1.nullable, q2.comments ' ||
             'FROM (SELECT table_name, column_name, data_type, nullable FROM USER_TAB_COLUMNS WHERE TABLE_NAME = :tbl) q1 ' ||
             'JOIN (SELECT column_name, comments FROM USER_COL_COMMENTS WHERE TABLE_NAME = :tbl) q2 ' ||
             'ON q1.column_name = q2.column_name';
    
    FOR rec IN EXECUTE IMMEDIATE v_sql USING p_table_name, p_table_name LOOP
        DBMS_OUTPUT.PUT_LINE(rec.table_name || ' | ' || rec.column_name || ' | ' || rec.data_type || ' | ' || rec.nullable || ' | ' || rec.comments);
    END LOOP;
END;
/

-- To call it:
EXEC get_table_columns('EMPLOYEES');

Note: If you're running this from an application (like Java, Python, etc.), you can pass the table name as a parameter to your dynamic SQL string there—just make sure to sanitize the input to avoid SQL injection risks.

Here's a straightforward breakdown of their core distinctions:

• Return Value Requirement: Functions must return exactly one value (or a pipelined collection for multiple rows), while procedures have no mandatory return value. Procedures use OUT or IN OUT parameters to send data back to the caller.
• Call Context: You can call functions directly within SQL statements (e.g., SELECT my_function(col) FROM table), but procedures can't be called in SQL—they have to be executed via PL/SQL blocks or EXEC commands.
• Primary Purpose: Functions are typically used for calculations or data retrieval that returns a result (like getting a user's full name from first/last names). Procedures are better for performing actions or business logic (like inserting records, updating data, or executing a sequence of steps).
• Transaction Handling: Both can include transaction logic, but procedures are more commonly used for operations that modify data (since they don't have the constraint of returning a value). Functions used in SQL can't perform DML operations unless they're defined as DETERMINISTIC or in an autonomous transaction.
• Exception Handling: While both can use EXCEPTION blocks, procedures often handle exceptions related to business processes, whereas functions might handle exceptions tied to calculation errors.

内容的提问来源于stack exchange，提问作者Natali