在团队中使用 Oracle 中的存储过程的工具？

Tools to work with stored procedures in Oracle, in a team?

提问by Aurelio Martin Massoni

What tools do you use to develop Oracle stored procedures, in a team :

您在团队中使用哪些工具来开发 Oracle 存储过程：

• To automatically "lock" the current procedure you are working with, so nobody else in the team can make changes to it until you are finished.
• To automatically send the changes you make in the stored procedure, in an Oracle database, to a Subversion, CVS, ... repository

• 自动“锁定”您正在使用的当前程序，以便团队中的其他人在您完成之前无法对其进行更改。
• 自动将您在 Oracle 数据库中的存储过程中所做的更改发送到 Subversion、CVS、...存储库

Thanks!

谢谢！

回答by Dwayne King

I'm not sure if the original poster is still monitoring this, but I'll ask the question anyways.

我不确定原发帖者是否仍在监视此事，但无论如何我都会问这个问题。

The original post requested to be able to:

原帖要求能够：

To automatically "lock" the current procedure you are working with, so nobody else in the team can make changes to it until you are finished.

自动“锁定”您正在使用的当前程序，以便团队中的其他人在您完成之前无法对其进行更改。

Perhaps the problem here is one of development paradigm more than the inability of a product to "lock" the stored proc. Whenever I hear "I want to lock this so noone else changes it" I immediately get the feeling that people are sharing a schema and everyone is developing in the same space.

也许这里的问题是开发范式之一，而不是产品无法“锁定”存储过程。每当我听到“我想锁定它以便没有其他人更改它”时，我立即感觉到人们正在共享一个模式并且每个人都在同一个空间中开发。

If this is the case, why not simply let everyone have their own schema with a copy of the data model? I mean seriously folks, it doesn't "cost" anything to create another schema. That way, each developer can make changes until they're blue in the face without affecting anyone else.

如果是这种情况，为什么不简单地让每个人都有自己的模式和数据模型的副本？我的意思是认真的人，创建另一个模式不会“花费”任何东西。这样，每个开发人员都可以进行更改，直到他们面无表情为止，而不会影响其他任何人。

Another trick I've used in the past (on small teams) when it wasn't feasible to let every developer have their own copy of the data because of size, was to have a master schema with all the tables and code in it, with public synonyms pointing to it all. Then, if the developer wants to work on a stored proc, he simply creates it in hisschema. That way Oracle name resolution finds that one first instead of the copy in the master schema, allowing them to test their code without affecting anyone else. This does have it's drawbacks, but this was a very specific case where we could live with them. I would NEVER implement something like this in production obviously.

我过去（在小型团队中）使用的另一个技巧是，由于规模的原因，让每个开发人员都拥有自己的数据副本是不可行的，那就是拥有一个包含所有表和代码的主模式，公共同义词指向这一切。然后，如果开发人员想要处理存储过程，他只需在他的模式中创建它。这样 Oracle 名称解析首先找到一个而不是主模式中的副本，允许他们测试他们的代码而不会影响其他任何人。这确实有它的缺点，但这是一个非常特殊的案例，我们可以接受它们。显然，我永远不会在生产中实现这样的东西。

As for the second requirement:

至于第二个要求：

To automatically send the changes you make in the stored procedure, in an Oracle database, to a Subversion, CVS, ... repository

自动将您在 Oracle 数据库中的存储过程中所做的更改发送到 Subversion、CVS、...存储库

I'd be surprised to find tools out there smart enough to do this (perhaps an opportunity :). It would have to connect to your db, query the data dictionary (USER_SOURCE) and pull out the associated text. A tall order for source control systems where are almost universally file based.

我会惊讶地发现有足够聪明的工具来做到这一点（也许是一个机会:)。它必须连接到您的数据库，查询数据字典 (USER_SOURCE) 并提取相关文本。对于几乎普遍基于文件的源代码控制系统来说，这是一项艰巨的任务。

回答by Mac

Treat PL/SQL as usual code : store it in files, and manage these files with your revision control tool and your internal procedures.

将 PL/SQL 视为通常的代码：将其存储在文件中，并使用您的修订控制工具和您的内部程序管理这些文件。

If you do not already have a revision control tool, then write your requirements down and pick one up. A lot of people it seems use Subversion, associated to TortoiseSVNas a client on Windows (I do).

如果您还没有版本控制工具，请写下您的要求并选择一个。很多人似乎使用Subversion，与TortoiseSVN相关联作为 Windows 上的客户端（我这样做）。

The thing is : use your tool as is recommended, and adapt your procedures accordingly. For instance, Subversion uses a copy-modify-mergemodel by default, as opposed to a lock-modify-unlockmodel which you seem to favor.

问题是：按照建议使用您的工具，并相应地调整您的程序。例如，Subversion默认使用复制-修改-合并模型，而不是您似乎喜欢的锁定-修改-解锁模型。

In my case, I like to use TortoiseSVN, as stated above. And as is usual with this tool :

就我而言，我喜欢使用 TortoiseSVN，如上所述。和这个工具一样：

• I never lock any files. This is very manageable with small teams, and it requires ahead planning on larger ones, which is always a good thing IMHO.
• I send my changes manually back to the server, because ... I don't think there's another way with Subversion (plus, internal procedures forbid a commit without a message, which is also a good thing IMHO).

• 我从不锁定任何文件。这对于小团队来说是很容易管理的，它需要提前规划更大的团队，恕我直言，这总是一件好事。
• 我手动将更改发送回服务器，因为...我认为 Subversion 没有其他方法（另外，内部程序禁止在没有消息的情况下提交，恕我直言，这也是一件好事）。

And whatever your choice, I recommend reading this post (and related ones) about database versioning.

无论您选择什么，我都建议您阅读这篇关于数据库版本控制的文章（和相关文章）。

回答by Jason Stevenson

Oracle's new SQL Developer has version control built-in.

Oracle 的新 SQL Developer 具有内置的版本控制。

Here is a link to the product.

这是产品的链接。

http://www.oracle.com/technology/products/database/sql_developer/files/what_is_sqldev.html

http://www.oracle.com/technology/products/database/sql_developer/files/what_is_sqldev.html

http://www.oracle.com/technology/products/database/sql_developer/images/what_version.png http://www.oracle.com/technology/products/database/sql_developer/images/what_version.png

http://www.oracle.com/technology/products/database/sql_developer/images/what_version.png http://www.oracle.com/technology/products/database/sql_developer/images/what_version.png

回答by cagcowboy

A relatively simple (if slightly old-fashioned) solution might be to use a "locking" rather than "merge" mode version control system.... Subversion or CVS generally use a "merge" mode (although I believe Subversion can be made to "lock" files?)

一个相对简单（如果有点老式）的解决方案可能是使用“锁定”而不是“合并”模式版本控制系统....Subversion 或 CVS 通常使用“合并”模式（尽管我相信可以使 Subversion “锁定”文件？）

"Locking" mode version control systems do have their own drawbacks of course.....

“锁定”模式版本控制系统当然有其自身的缺点......

The only way I can think of doing in in Oracle might be some of of BEFORE CREATE TRIGGER, maybe referencing a table to look-up who can run a package in. Sounds a bit nasty though?

我能想到的在 Oracle 中做的唯一方法可能是一些 BEFORE CREATE TRIGGER，也许引用一个表来查找可以运行包的人。虽然听起来有点讨厌？

回答by michristofides

Using Source Control for Oracleyou get a lot of what you're looking for.

使用Oracle 源代码管理，您可以获得很多您正在寻找的东西。

Stored procedures (as well as packages, functions, tables etc.) can be locked manually using the interface, not automatically, but this does prevent others making changes.

存储过程（以及包、函数、表等）可以使用界面手动锁定，而不是自动锁定，但这确实可以防止其他人进行更改。

The new SQL to create the object can then be checked into SVN or TFS (no CVS support unfortunately).

然后可以将用于创建对象的新 SQL 签入 SVN 或 TFS（不幸的是没有 CVS 支持）。

The tool is not free but has a free 28-day trial.

该工具不是免费的，但有 28 天的免费试用期。

回答by ropable

Using Oracle SQL Developer 1.5, you can easily create and manage connections to CVS or Subversion. To create a CVS connection (for example), click Versioning -> CVS -> Check out Module. You will run through a wizard to create the connection (host, username, etc), then you can check your procedures/functions out and in as normal.

使用Oracle SQL Developer 1.5，您可以轻松创建和管理与 CVS 或 Subversion 的连接。要创建 CVS 连接（例如），请单击Versioning -> CVS -> Check out Module。您将运行一个向导来创建连接（主机、用户名等），然后您可以照常检出和检入您的程序/功能。

Integration with CVS is also provided in Toad.

Toad 中还提供了与 CVS 的集成。

回答by Mike Farmer

You may also want to look at Aqua Data Studio. They have built in SVN as well and is a great Stored Proc editor.

您可能还想查看 Aqua Data Studio。他们也内置了 SVN，是一个很棒的存储过程编辑器。

回答by asalamon74

After searching for a tool to handle version control for Oracle objects with no luck we created the following (not perfect but suitable) solution:

在搜索了一个工具来处理 Oracle 对象的版本控制但没有运气后，我们创建了以下（不完美但合适）的解决方案：

1. Using dbms_metadatapackage we create the metadata dump of our Oracle server. We create one file per object, hence the result is not one huge file but a bunch of files. For recognizing deleted object we delete all the files before creating the dump again.
2. We copy all the files from the server to the client computer.
3. Using Netbeans we recognize the changes, and commit the changes to the CVS server (or check the diffs...). Any CVS-handler software would work here, but we were already using Netbeans for other purposes. And Netbeans also allows to create an ant task for calling the Oracle process mentioned in step 1, copying the files mention in step 2...

1. 使用dbms_metadata包，我们创建了 Oracle 服务器的元数据转储。我们为每个对象创建一个文件，因此结果不是一个巨大的文件，而是一堆文件。为了识别已删除的对象，我们在再次创建转储之前删除所有文件。
2. 我们将所有文件从服务器复制到客户端计算机。
3. 我们使用 Netbeans 识别更改，并将更改提交到 CVS 服务器（或检查差异...）。任何 CVS 处理程序软件都可以在这里工作，但我们已经将 Netbeans 用于其他目的。并且 Netbeans 还允许创建一个 ant 任务来调用步骤 1 中提到的 Oracle 进程，复制步骤 2 中提到的文件......

Here is the most imporant query for step 1:

这是第 1 步最重要的查询：

SELECT object_type, object_name, 
  dbms_metadata.get_ddl(object_type, object_name) object_ddl FROM user_objects
WHERE OBJECT_TYPE in ('INDEX', 'TRIGGER', 'TABLE', 'VIEW', 'PACKAGE', 
  'FUNCTION', 'PROCEDURE', 'SYNONYM', 'TYPE')  
ORDER BY OBJECT_TYPE, OBJECT_NAME

One file per object approach helps to identify the changes. If I add a field to table TTTT(not a real table name of course) then only TABLE_TTTT.SQLfile will be modified.

每个对象一个文件的方法有助于识别更改。如果我向表中添加一个字段TTTT（当然不是真正的表名），那么只会TABLE_TTTT.SQL修改文件。

Both step 1 and step 3 are slow processes. (several minutes for a few thousand of files)

第 1 步和第 3 步都是缓慢的过程。（几千个文件需要几分钟）

回答by asalamon74

Toad also does this without requiring CVS / SVN.

Toad 也无需 CVS / SVN 即可执行此操作。