如何使用pl/sql进行批处理操作 [英] How to do the batch operation using pl/sql

问题描述

我必须为许多表添加主键，并且我从stackoverflow获得了以下脚本(对于一个表):

I have to add primary key for lots of tables, and I got the following scripts (for one table) from stackoverflow:

ALTER TABLE table1 ADD ID NUMBER(12);
CREATE SEQUENCE table1Seq START WITH 1;
UPDATE table1 SET table1ID = table1Seq.NEXTVAL;
ALTER TABLE table1 ADD PRIMARY KEY (ID);
CREATE OR REPLACE TRIGGER table1PKSet
BEFORE INSERT ON table1
FOR EACH ROW
BEGIN
  :NEW.ID := table1Seq.NEXTVAL;
END;
/

但是现在我已经对用户的所有表进行了相同的操作.

However now I have do the same operations for all the tables of the user.

所以我想知道是否可以使用pl/sql循环用户的所有表，并执行上述操作?

So I wonder if I can use the pl/sql to loops all the tables of the user, and do the above operation?

推荐答案

在我开始之前，我只想说这是毛骨悚然的.如果要创建自动执行数据库创建的脚本，我将放弃以下查询，而直接进行复制/粘贴，因为这太可怕了，它不属于您的数据库部署脚本.

I'm just going to say this is gross before I start. If you're creating scripts that automate the database creation, I would ditch the below query and just go with copy/pasting because this is so horrible it does NOT belong in your database deployment scripts.

DECLARE
    CURSOR TABLES IS SELECT * FROM USER_TABLES
                     WHERE 0 = (SELECT COUNT(*)
                                FROM USER_CONSTRAINTS
                                WHERE USER_CONSTRAINTS.TABLE_NAME = USER_TABLES.TABLE_NAME 
                                AND USER_CONSTRAINTS.CONSTRAINT_TYPE = 'P'
                               );
BEGIN
    FOR T IN TABLES LOOP
        EXECUTE IMMEDIATE 'ALTER TABLE '||T.TABLE_NAME||' ADD ID NUMBER(12)';
        EXECUTE IMMEDIATE 'CREATE SEQUENCE '||T.TABLE_NAME||'Seq START WITH 1';
        EXECUTE IMMEDIATE 'UPDATE '||T.TABLE_NAME||' SET ID = '||T.TABLE_NAME||'Seq.NEXTVAL';
        EXECUTE IMMEDIATE 'ALTER TABLE '||T.TABLE_NAME||' ADD PRIMARY KEY (ID)';
        EXECUTE IMMEDIATE 'CREATE OR REPLACE TRIGGER '||T.TABLE_NAME||'PKSet '||CHR(10)
                          ||'BEFORE INSERT ON '||T.TABLE_NAME||' '||CHR(10)
                          ||'FOR EACH ROW '||CHR(10)
                          ||'BEGIN '||CHR(10)
                          ||':NEW.ID := '||T.TABLE_NAME||'Seq.NEXTVAL; '||CHR(10)
                          ||'END; ';
    END LOOP;
END;
/

这是做什么的?

基本上，它获取表列表并动态构建SQL以执行涉及的各种任务. EXECUTE IMMEDIATE接受我们在其中构建SQL的字符串并执行它. CHR(10)难处是换行符.我想在其中添加空格，因为我不知道将其留空会如何影响Oracle的解析.请注意，在某些地方，我们将表名直接连接到其他文本，以生成序列或PK约束名称.

What does this do?

Basically, it gets a list of tables and dynamically builds the SQL to do the various tasks involved. EXECUTE IMMEDIATE takes the string where we built the SQL and executes it. The CHR(10) nastiness is a newline. I wanted the whitespace in there because I don't know how leaving it out would affect Oracle's parsing. Note that in several places we concatenate the table name directly to some other text to generate a sequence or PK constraint name.

如果在创建过程中引用了表名并且使用了小写字母，则可能会或不会出错.如果确实出错，请记住，每个语句都包含一个提交.错误将意味着该过程完成了一半.如果架构不是当前用户，它也会失败. (您需要将USER_TABLES更改为ALL_TABLES，并在where子句中添加一个适当的过滤器，并在构建SQL使其在另一种模式下工作时在表名之前添加该模式.)

This may or may not error out if you quoted your table names during creation and are using some lower case characters. If it DOES error out, keep in mind that every statement involves a commit. An error will mean the process is half done. It also fails if the schema is not the current user. (You'll need to change USER_TABLES to ALL_TABLES and add an appropriate filter in the where clause and add the schema in front of the table name when building the SQL to make it work on another schema.)

实际可用的SQLFiddle: http://sqlfiddle.com/#!4/b67fc/1 (我不敢相信这在SQLFiddle上确实有效.)在这种情况下，由于SQL Fiddle仅允许在查询中使用SELECT，因此我们对模式定义的查询结束了.

An actual working SQLFiddle: http://sqlfiddle.com/#!4/b67fc/1 (I cannot believe this actually worked on SQLFiddle.) In this case, the query we're interested in is over in the schema definition since SQL Fiddle only allows SELECT in the query.

祝你好运.您将需要它.不要用脚开枪射击自己.

Good luck. You'll need it. Don't shoot yourself in the foot.

这篇关于如何使用pl/sql进行批处理操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！