I have a SQL Server database that has a huge proliferation of stored procedures. Large numbers of stored procedures are not a problem in my Oracle databases because of the Oracle "package" feature.
我有一个SQL Server数据库，它有大量的存储过程。由于Oracle“包”特性，在我的Oracle数据库中，大量存储过程不是问题。
What do programmers do to get around the lack of a "package" feature like that of Oracle?
程序员如何避免缺少像Oracle那样的“包”特性呢?
8 个解决方案
#1
23
While SQL Server has nothing to offer by way of the "cool features" of encapsulation and package state like you are used to, you can organize your stored procedures into schemas.
虽然SQL Server不像您习惯的那样提供封装和包状态的“酷特性”，但您可以将存储过程组织为模式。
In enterprise manager, these procs are still all listed together which makes for a HUGE treelist if you have hundreds of procs. I too miss the organization and cool features of Oracle packages. However, all platforms have their strengths.
在企业管理器中，这些procs仍然被列出在一起，如果您有数百个procs，这就构成了一个巨大的树列表。我也想念Oracle包的组织和酷特性。然而，所有平台都有自己的优势。
NOTE: Writing stored procedures in the .NET language DOES give you encapsulation and state. It still does not however separate them in the EM treeview in any special way.
注意:用. net语言编写存储过程确实可以提供封装和状态。然而，它仍然没有以任何特殊的方式在EM树视图中分离它们。
#2
14
Come up with a good naming convention, use it, and enforce it.
想出一个好的命名约定，使用它，并执行它。
#3
8
Schemas may be used to organize stored procedures and other objects. Personally, I prefer to use schemas when they organize objects by functional area, and where those funcational areas correspond to security boundaries. An example of this is found in the AdventureWorks sample databse, which has schemas like "HumanResources" and "Sales". The theory being that a given user may need access to objects in "HumanResources", but may not need access to "Sales" information.
模式可以用来组织存储过程和其他对象。就我个人而言，当它们按功能区域组织对象时，我更喜欢使用模式，并且这些功能区域对应于安全边界。在AdventureWorks示例databse中可以找到这样的示例，该示例具有“HumanResources”和“Sales”这样的模式。理论上，给定用户可能需要访问“HumanResources”中的对象，但可能不需要访问“Sales”信息。
An alternative is to use a naming convention and enforce it, as James says above. I'll add that SQL Server Management Studio has a filter button that can be used to filter the list of objects displayed. For instance, one can click on the "Stored Procedures" folder and filter on Name contains "Add".
另一种选择是使用命名约定并执行它，正如James上面所说的。我将添加SQL Server Management Studio有一个过滤器按钮，可以用来过滤显示的对象列表。例如，可以单击“存储过程”文件夹，并对包含“添加”的名称进行筛选。
On my current project, I have pulled a number of SQL queries out of SSIS packages and into stored procedures. In order to distinguish between these stored procedures and those that should be of general use, I have prefixed the names with "ssis". It would certainly have been more pleasant if I could have created something similar to a namespace in C# or C++, and created "SSIS.SelectUserLookupData" instead of "ssis_SelectUserLookupData". It would be even nicer if these namespaces could be nested.
在我当前的项目中，我从SSIS包中取出了许多SQL查询，并将它们放入存储过程中。为了区分这些存储过程和应该使用的存储过程，我在名称前面加上了“ssis”。如果我能在c#或c++中创建类似名称空间的东西，并创建“SSIS”，那肯定会更令人愉快。SelectUserLookupData”而不是“ssis_SelectUserLookupData”。如果这些名称空间可以被嵌套，那就更好了。
If this is one of the featues of Packages in Oracle, then perhaps someone would let me know.
如果这是Oracle中包的特性之一，那么也许有人会让我知道。
#4
4
I've worked with both SQL Server and Oracle so have seen the good and bad of both. As the above comments have beena bit heated I'll try and keep this as neutral as possible...
我与SQL Server和Oracle都有过合作，所以看到了两者的优缺点。由于以上评论有些激烈，我将尽量保持中立……
So, what's an Oracle Package? Think of it like a database class
那么，什么是Oracle包呢?把它想象成一个数据库类
The Package has two elements: a header file and a body file. The header file is your public interface, and contains the signature (name, params and return type if applicable) of all the stored procedures or functions (in Oracle a function returns a value, a stored proc doesn't) that are directly callable. The package body must implement all the procedure signatures in the package header file.
该包有两个元素:头文件和主体文件。头文件是您的公共接口，并且包含所有存储过程或函数(在Oracle中，函数返回一个值，一个存储的proc没有)的签名(名称、参数和返回类型)，这些都是直接可调用的。包体必须实现包头文件中的所有过程签名。
The body element of the package contains all the stored procs and logic that actually do the work. You may have a Save procedure declared in the package header that calls an insert or update proc that exists in the body. The developer can only see the "Save" proc. It's important to keep in mind that the package body can also implement procs or functions not declared in the package header, they're just not accessible outside of the package itself.
包的主体元素包含实际执行工作的所有存储过程和逻辑。您可以在包头中声明一个保存过程，该过程调用存在于主体中的insert或update proc。开发人员只能看到“Save”proc。请记住，包主体也可以实现包头中未声明的proc或函数，它们在包本身之外是不可访问的。
I found packages to be really useful for a number of reasons:
我发现包非常有用有很多原因:
- You've got the concept of a public interface that can be provided to other developers
- 您已经有了可以提供给其他开发人员的公共接口的概念
- Packages can mirror your compiled classes. My Orders.Save() C# method will call my Oracle Orders.SaveLineItem method to save each line item and an Oracle SaveOrder method to save the order summary details.
- 包可以镜像编译的类。我的order . save () c#方法将调用我的Oracle order。SaveLineItem方法保存每一行的项目，Oracle SaveOrder方法保存订单摘要细节。
- My procs are grouped together in a nice, logical way inside the packages
- 我的procs以一种很好的、合乎逻辑的方式在包中分组
Personally, I would be love MS to implement some kind of package functionality as I think it makes for a cleaner database.
就我个人而言，我希望MS能够实现一些包功能，因为我认为它可以创建一个更干净的数据库。
#5
1
3) The best argument against oracle packages is that based on experience and research on the Ask Tom site, one can't update a package without taking it off line. This is unacceptable. With SQL Server, we can update stored procedures on the fly, without interrupting production operation.
3)反对oracle包的最佳论据是，根据Ask Tom站点的经验和研究，如果一个包不离线就不能更新。这是不可接受的。使用SQL Server，我们可以动态更新存储过程，而不会中断生产操作。
I understand the frustration of this statement, but I would not call id "unacceptable". In a true production environment, changes should never be tested in production. Updates should be moved from a test environment to production in a scheduled and orderly manner. In a 24/7 system, then redundant production environment should handle down time while servers are updated. Not only does the package have to be taken off line, but the new package, if not compiled, will fail when placed back on line. There is a DBA element required for Oracle databases. However, I do miss the Oracle packages.
我理解这种说法的沮丧，但我不认为id是“不可接受的”。在真实的生产环境中，不应该在生产中测试更改。更新应该按照计划和有序的方式从测试环境移动到生产环境。在24/7系统中，当服务器更新时，冗余生产环境应该处理停机时间。不仅包必须脱机，而且新的包如果没有编译，将在重新上线时失败。Oracle数据库需要一个DBA元素。但是，我确实错过了Oracle包。
#6
1
One additional feature of packages that was not mentioned is the ability to 'wrap' the body. The header is always public and can be viewed by anyone with permissions to execute the package. But that also allows them to view the code in the body. You can wrap the body, encrypting it, and prevent anyone from seeing what the code is actually doing. Its a nice feature where security is a big issue.
包的另一个未提及的特性是能够“包装”主体。header始终是公共的，任何具有执行包权限的人都可以查看它。但这也允许他们查看正文中的代码。您可以封装主体，对其进行加密，并防止任何人看到代码实际上在做什么。这是一个很好的特性，安全性是一个大问题。
#7
1
1) Like people have said, Schema's are a more logical and ANSI compliant way to organize database tables and procedures.
1)正如人们所说，模式是一种更符合逻辑、更符合ANSI的方式来组织数据库表和过程。
2) Software engineering best practices are that we should never make a change directly on any server. Since all database sprocs are scripted and under configuration control, we can arrange those scripts into any folder structure we want.
2)软件工程的最佳实践是，我们不应该直接在任何服务器上进行更改。由于所有数据库sproc都是脚本化的，并且处于配置控制之下，所以我们可以将这些脚本安排到我们想要的任何文件夹结构中。
3) The best argument against oracle packages is that based on experience and research on the Ask Tom site, one can't update a package without taking it off line. This is unacceptable. With SQL Server, we can update stored procedures on the fly, without interrupting production operation.
3)反对oracle包的最佳论据是，根据Ask Tom站点的经验和研究，如果一个包不离线就不能更新。这是不可接受的。使用SQL Server，我们可以动态更新存储过程，而不会中断生产操作。
Update: WeMartin, you say "In a true production environment, changes should never be tested in production. Updates should be moved from a test environment to production in a scheduled and orderly manner. In a 24/7 system, then redundant production environment should handle down time while servers are updated".
更新:WeMartin，您说“在一个真正的生产环境中，变化永远不应该在生产中测试。更新应该按照计划和有序的方式从测试环境移动到生产环境。在一个24/7系统中，冗余生产环境应该在服务器更新时处理停机时间。
I'm not at all implying that ANY changes are tested in production. Even if Changes are tested on 9 lower develop environments, these now completely and thoroughly tested changes will need to be deployed to the production server. At that point, using Oracle packages, the production server has to be brought down in all cases, even for minor sproc changes.
我并不是在暗示任何变更都要在生产中进行测试。即使更改在9个较低的开发环境中进行测试，也需要将这些现在已经完全地、彻底地测试过的更改部署到生产服务器上。在这一点上，使用Oracle包，生产服务器在所有情况下都必须被降低，即使是较小的sproc更改。
#8
0
I would thank my lucky stars that SQL Server doesn't have packages. Oracle packages suck.
我要感谢我的幸运星，SQL Server没有包。Oracle包吸。
Hmm, we need a way to take all these procedures and put them in one place. I know! Let's make developers create and maintain two files for each package. They will love us forever!
嗯，我们需要一种方法把所有这些程序放在一个地方。我知道!让开发人员为每个包创建和维护两个文件。他们将永远爱我们!
As long as MS never implements packages like Oracle did, it'll be a win in my book.
只要MS不像Oracle那样实现包，在我看来这将是一个胜利。
EDIT for commenters:
编辑评论:
Oracle Packages are simply a way to organize your stored procedures into, well, packages so that you don't have 100 stored procedures sitting around, but maybe 5 packages. They're not stackable like packages in Java or C# code. All packages are at the same level.
Oracle包只是将存储过程组织到包中的一种方式，这样就不会有100个存储过程，而是可能有5个包。它们不能像Java或c#代码中的包那样堆叠。所有包都位于同一级别。
A package requires two files: the headers file and the body file. This creates frustration when adding new procedures to an existing package, because you cannot add the body without adding the header, even though it contains the exact same information as is in the body.
一个包需要两个文件:头文件和主体文件。当向现有包添加新过程时，这会造成挫折，因为您不能在不添加头部的情况下添加主体，即使它包含与主体相同的信息。
For example, here is a snippet from the header file of one of my packages:
例如，这是我的一个包的头文件的一个片段:
PROCEDURE bulk_approve_events
(
i_last_updated_by IN VARCHAR2,
o_event OUT NUMBER
);
And here's the corresponding procedure in the body:
这是人体的相应过程:
PROCEDURE bulk_approve_events
(
i_last_updated_by IN VARCHAR2,
o_event OUT NUMBER
) IS
...
BEGIN
...
END;
No difference. The header file is useless and is simply another hurdle for the developer to step over when developing with packages. On my project, we have a convention that all the commented documentation for each procedure goes in the header, along with the details of when it was added and by whom, but that could just as easily be included in the body.
没有区别。头文件是无用的，它只是开发人员在使用包进行开发时需要跨越的另一个障碍。在我的项目中，我们有一个约定，即每个过程的所有注释文档都要放在标题中，以及何时添加以及由谁添加的细节，但这也可以很容易地包含在正文中。
#1
23
While SQL Server has nothing to offer by way of the "cool features" of encapsulation and package state like you are used to, you can organize your stored procedures into schemas.
虽然SQL Server不像您习惯的那样提供封装和包状态的“酷特性”，但您可以将存储过程组织为模式。
In enterprise manager, these procs are still all listed together which makes for a HUGE treelist if you have hundreds of procs. I too miss the organization and cool features of Oracle packages. However, all platforms have their strengths.
在企业管理器中，这些procs仍然被列出在一起，如果您有数百个procs，这就构成了一个巨大的树列表。我也想念Oracle包的组织和酷特性。然而，所有平台都有自己的优势。
NOTE: Writing stored procedures in the .NET language DOES give you encapsulation and state. It still does not however separate them in the EM treeview in any special way.
注意:用. net语言编写存储过程确实可以提供封装和状态。然而，它仍然没有以任何特殊的方式在EM树视图中分离它们。
#2
14
Come up with a good naming convention, use it, and enforce it.
想出一个好的命名约定，使用它，并执行它。
#3
8
Schemas may be used to organize stored procedures and other objects. Personally, I prefer to use schemas when they organize objects by functional area, and where those funcational areas correspond to security boundaries. An example of this is found in the AdventureWorks sample databse, which has schemas like "HumanResources" and "Sales". The theory being that a given user may need access to objects in "HumanResources", but may not need access to "Sales" information.
模式可以用来组织存储过程和其他对象。就我个人而言，当它们按功能区域组织对象时，我更喜欢使用模式，并且这些功能区域对应于安全边界。在AdventureWorks示例databse中可以找到这样的示例，该示例具有“HumanResources”和“Sales”这样的模式。理论上，给定用户可能需要访问“HumanResources”中的对象，但可能不需要访问“Sales”信息。
An alternative is to use a naming convention and enforce it, as James says above. I'll add that SQL Server Management Studio has a filter button that can be used to filter the list of objects displayed. For instance, one can click on the "Stored Procedures" folder and filter on Name contains "Add".
另一种选择是使用命名约定并执行它，正如James上面所说的。我将添加SQL Server Management Studio有一个过滤器按钮，可以用来过滤显示的对象列表。例如，可以单击“存储过程”文件夹，并对包含“添加”的名称进行筛选。
On my current project, I have pulled a number of SQL queries out of SSIS packages and into stored procedures. In order to distinguish between these stored procedures and those that should be of general use, I have prefixed the names with "ssis". It would certainly have been more pleasant if I could have created something similar to a namespace in C# or C++, and created "SSIS.SelectUserLookupData" instead of "ssis_SelectUserLookupData". It would be even nicer if these namespaces could be nested.
在我当前的项目中，我从SSIS包中取出了许多SQL查询，并将它们放入存储过程中。为了区分这些存储过程和应该使用的存储过程，我在名称前面加上了“ssis”。如果我能在c#或c++中创建类似名称空间的东西，并创建“SSIS”，那肯定会更令人愉快。SelectUserLookupData”而不是“ssis_SelectUserLookupData”。如果这些名称空间可以被嵌套，那就更好了。
If this is one of the featues of Packages in Oracle, then perhaps someone would let me know.
如果这是Oracle中包的特性之一，那么也许有人会让我知道。
#4
4
I've worked with both SQL Server and Oracle so have seen the good and bad of both. As the above comments have beena bit heated I'll try and keep this as neutral as possible...
我与SQL Server和Oracle都有过合作，所以看到了两者的优缺点。由于以上评论有些激烈，我将尽量保持中立……
So, what's an Oracle Package? Think of it like a database class
那么，什么是Oracle包呢?把它想象成一个数据库类
The Package has two elements: a header file and a body file. The header file is your public interface, and contains the signature (name, params and return type if applicable) of all the stored procedures or functions (in Oracle a function returns a value, a stored proc doesn't) that are directly callable. The package body must implement all the procedure signatures in the package header file.
该包有两个元素:头文件和主体文件。头文件是您的公共接口，并且包含所有存储过程或函数(在Oracle中，函数返回一个值，一个存储的proc没有)的签名(名称、参数和返回类型)，这些都是直接可调用的。包体必须实现包头文件中的所有过程签名。
The body element of the package contains all the stored procs and logic that actually do the work. You may have a Save procedure declared in the package header that calls an insert or update proc that exists in the body. The developer can only see the "Save" proc. It's important to keep in mind that the package body can also implement procs or functions not declared in the package header, they're just not accessible outside of the package itself.
包的主体元素包含实际执行工作的所有存储过程和逻辑。您可以在包头中声明一个保存过程，该过程调用存在于主体中的insert或update proc。开发人员只能看到“Save”proc。请记住，包主体也可以实现包头中未声明的proc或函数，它们在包本身之外是不可访问的。
I found packages to be really useful for a number of reasons:
我发现包非常有用有很多原因:
- You've got the concept of a public interface that can be provided to other developers
- 您已经有了可以提供给其他开发人员的公共接口的概念
- Packages can mirror your compiled classes. My Orders.Save() C# method will call my Oracle Orders.SaveLineItem method to save each line item and an Oracle SaveOrder method to save the order summary details.
- 包可以镜像编译的类。我的order . save () c#方法将调用我的Oracle order。SaveLineItem方法保存每一行的项目，Oracle SaveOrder方法保存订单摘要细节。
- My procs are grouped together in a nice, logical way inside the packages
- 我的procs以一种很好的、合乎逻辑的方式在包中分组
Personally, I would be love MS to implement some kind of package functionality as I think it makes for a cleaner database.
就我个人而言，我希望MS能够实现一些包功能，因为我认为它可以创建一个更干净的数据库。
#5
1
3) The best argument against oracle packages is that based on experience and research on the Ask Tom site, one can't update a package without taking it off line. This is unacceptable. With SQL Server, we can update stored procedures on the fly, without interrupting production operation.
3)反对oracle包的最佳论据是，根据Ask Tom站点的经验和研究，如果一个包不离线就不能更新。这是不可接受的。使用SQL Server，我们可以动态更新存储过程，而不会中断生产操作。
I understand the frustration of this statement, but I would not call id "unacceptable". In a true production environment, changes should never be tested in production. Updates should be moved from a test environment to production in a scheduled and orderly manner. In a 24/7 system, then redundant production environment should handle down time while servers are updated. Not only does the package have to be taken off line, but the new package, if not compiled, will fail when placed back on line. There is a DBA element required for Oracle databases. However, I do miss the Oracle packages.
我理解这种说法的沮丧，但我不认为id是“不可接受的”。在真实的生产环境中，不应该在生产中测试更改。更新应该按照计划和有序的方式从测试环境移动到生产环境。在24/7系统中，当服务器更新时，冗余生产环境应该处理停机时间。不仅包必须脱机，而且新的包如果没有编译，将在重新上线时失败。Oracle数据库需要一个DBA元素。但是，我确实错过了Oracle包。
#6
1
One additional feature of packages that was not mentioned is the ability to 'wrap' the body. The header is always public and can be viewed by anyone with permissions to execute the package. But that also allows them to view the code in the body. You can wrap the body, encrypting it, and prevent anyone from seeing what the code is actually doing. Its a nice feature where security is a big issue.
包的另一个未提及的特性是能够“包装”主体。header始终是公共的，任何具有执行包权限的人都可以查看它。但这也允许他们查看正文中的代码。您可以封装主体，对其进行加密，并防止任何人看到代码实际上在做什么。这是一个很好的特性，安全性是一个大问题。
#7
1
1) Like people have said, Schema's are a more logical and ANSI compliant way to organize database tables and procedures.
1)正如人们所说，模式是一种更符合逻辑、更符合ANSI的方式来组织数据库表和过程。
2) Software engineering best practices are that we should never make a change directly on any server. Since all database sprocs are scripted and under configuration control, we can arrange those scripts into any folder structure we want.
2)软件工程的最佳实践是，我们不应该直接在任何服务器上进行更改。由于所有数据库sproc都是脚本化的，并且处于配置控制之下，所以我们可以将这些脚本安排到我们想要的任何文件夹结构中。
3) The best argument against oracle packages is that based on experience and research on the Ask Tom site, one can't update a package without taking it off line. This is unacceptable. With SQL Server, we can update stored procedures on the fly, without interrupting production operation.
3)反对oracle包的最佳论据是，根据Ask Tom站点的经验和研究，如果一个包不离线就不能更新。这是不可接受的。使用SQL Server，我们可以动态更新存储过程，而不会中断生产操作。
Update: WeMartin, you say "In a true production environment, changes should never be tested in production. Updates should be moved from a test environment to production in a scheduled and orderly manner. In a 24/7 system, then redundant production environment should handle down time while servers are updated".
更新:WeMartin，您说“在一个真正的生产环境中，变化永远不应该在生产中测试。更新应该按照计划和有序的方式从测试环境移动到生产环境。在一个24/7系统中，冗余生产环境应该在服务器更新时处理停机时间。
I'm not at all implying that ANY changes are tested in production. Even if Changes are tested on 9 lower develop environments, these now completely and thoroughly tested changes will need to be deployed to the production server. At that point, using Oracle packages, the production server has to be brought down in all cases, even for minor sproc changes.
我并不是在暗示任何变更都要在生产中进行测试。即使更改在9个较低的开发环境中进行测试，也需要将这些现在已经完全地、彻底地测试过的更改部署到生产服务器上。在这一点上，使用Oracle包，生产服务器在所有情况下都必须被降低，即使是较小的sproc更改。
#8
0
I would thank my lucky stars that SQL Server doesn't have packages. Oracle packages suck.
我要感谢我的幸运星，SQL Server没有包。Oracle包吸。
Hmm, we need a way to take all these procedures and put them in one place. I know! Let's make developers create and maintain two files for each package. They will love us forever!
嗯，我们需要一种方法把所有这些程序放在一个地方。我知道!让开发人员为每个包创建和维护两个文件。他们将永远爱我们!
As long as MS never implements packages like Oracle did, it'll be a win in my book.
只要MS不像Oracle那样实现包，在我看来这将是一个胜利。
EDIT for commenters:
编辑评论:
Oracle Packages are simply a way to organize your stored procedures into, well, packages so that you don't have 100 stored procedures sitting around, but maybe 5 packages. They're not stackable like packages in Java or C# code. All packages are at the same level.
Oracle包只是将存储过程组织到包中的一种方式，这样就不会有100个存储过程，而是可能有5个包。它们不能像Java或c#代码中的包那样堆叠。所有包都位于同一级别。
A package requires two files: the headers file and the body file. This creates frustration when adding new procedures to an existing package, because you cannot add the body without adding the header, even though it contains the exact same information as is in the body.
一个包需要两个文件:头文件和主体文件。当向现有包添加新过程时，这会造成挫折，因为您不能在不添加头部的情况下添加主体，即使它包含与主体相同的信息。
For example, here is a snippet from the header file of one of my packages:
例如，这是我的一个包的头文件的一个片段:
PROCEDURE bulk_approve_events
(
i_last_updated_by IN VARCHAR2,
o_event OUT NUMBER
);
And here's the corresponding procedure in the body:
这是人体的相应过程:
PROCEDURE bulk_approve_events
(
i_last_updated_by IN VARCHAR2,
o_event OUT NUMBER
) IS
...
BEGIN
...
END;
No difference. The header file is useless and is simply another hurdle for the developer to step over when developing with packages. On my project, we have a convention that all the commented documentation for each procedure goes in the header, along with the details of when it was added and by whom, but that could just as easily be included in the body.
没有区别。头文件是无用的，它只是开发人员在使用包进行开发时需要跨越的另一个障碍。在我的项目中，我们有一个约定，即每个过程的所有注释文档都要放在标题中，以及何时添加以及由谁添加的细节，但这也可以很容易地包含在正文中。