我们可以在准备好的语句(PostgreSQL)中使用DDL命令吗?

问题描述

DDL命令如下:

CREATE TABLE —用用户提供的列名创建一个表.

CREATE TABLE — creates a table with the column names the user provides.

DROP TABLE-删除所有行，并从数据库中删除表定义.

DROP TABLE — deletes all rows and removes the table definition from the database.

ALTER TABLE-在表中添加或删除列.

ALTER TABLE — adds or removes a column from a table.

如果可以在PostgreSQL和Java中使用这些命令，我​​需要几个示例?

I need few examples if there is a possibility of using these Commands in PostgreSQL and Java?

public boolean create(Employee employee) {

    try {

        callableStatement = openConnection().prepareCall("{call insert_employee(?,?,?)}");
        callableStatement.setInt(1, employee.getEid());
        callableStatement.setString(2, employee.getEname());
        callableStatement.setInt(3, employee.getSid());

        i = callableStatement.execute();

        callableStatement.close();

        closeConnection();



    } catch (SQLException e) {
        e.printStackTrace();
    } catch (Exception e) {
        e.printStackTrace();
    }
    return i;

}

是否有可能以这种类型使用DDL CREATE命令?使用准备好的语句?

is there any chance of using DDL CREATE command in such type? using prepared statements?

推荐答案

是的，如果您使用EXECUTE并将其包装在FUNCTION中，则可以.该函数调用允许您传递参数，并且在FUNCTION内部使用字符串操作来修改DDL语句.最后，在FUNCTION中使用EXECUTE可以做到这一点.这是参数化的CREATE SEQUENCE语句的简单示例...

Yes you can, if you use EXECUTE and wrap it in a FUNCTION. The function call allows you to pass parameters, and inside the FUNCTION you use string manipulation to modify the DDL statement. Finally, the use of EXECUTE in the FUNCTION makes it so. Here is a simple example of a parameterized CREATE SEQUENCE statement...

DROP FUNCTION sf.start_mc(integer);

CREATE FUNCTION sf.start_mc(thefirst integer) RETURNS void AS $$
BEGIN
    EXECUTE format('CREATE SEQUENCE sf.mastercase START %s',thefirst);
END;

$$ LANGUAGE plpgsql;

我们使用字符串函数"format"来处理该语句，并包含传递给该函数的参数.当然，您的SQL看起来很不寻常，特别是如果在调用它之前包含CREATE FUNCTION的话.这个例子来自我最近做的数据迁移工作.创建函数之后，我们像这样使用它:

We use the string function "format" to manipulate the statement and include the parameter that was passed to the function. Of course, your SQL looks rather unusual, particularly if you include the CREATE FUNCTION before you call it. This example comes from a data migration job that I recently did. After CREATEing the function we used it like this:

DROP SEQUENCE sf.mastercase;

-- the following uses the above function to set the starting value of a new sequence based on the last used
-- in the widget table
select sf.start_mc((select substring("widgetId",4)::integer + 1 from widgets
where "widgetId" like 'MC-%'
order by "widgetId" desc
limit 1));

请注意，外部SELECT不会选择任何内容，它只是提供了进行函数调用的位置.作为参数传递的数字来自包装在括号内的内部SELECT.一个更简单的调用是

Note that the outer SELECT doesn't select anything, it just makes a place to do the function call. The number that is passed as a parameter comes from the inner SELECT which is wrapped in parentheses. A simpler call would be

select sf.start_mc(42);

您可以将任何内容包装在CREATED FUNCTION中.但这确实意味着您必须遵守PostgreSQL，并且需要以一流的公民身份将DB模式和模式更改集成到开发过程中.

You can wrap anything in a CREATEd FUNCTION. But this does mean that you are stuck with PostgreSQL and that you need to integrate your DB schema, and schema changes, into your development process as a first class citizen.

这篇关于我们可以在准备好的语句(PostgreSQL)中使用DDL命令吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！