具有多个IN参数的存储过程 [英] Stored Procedure with multiple IN Parameter

问题描述

我得到了以下过程:

create or replace PROCEDURE create_indexes 
(tbl_name_index IN VARCHAR2, 
tbl_name_vehicle IN VARCHAR2, 
tbl_name_dealer IN VARCHAR2, 
tbl_name_hst IN VARCHAR2, 
tbl_name_dms IN VARCHAR2, 
tbl_name_usertype IN VARCHAR2, 
tbl_name_search IN VARCHAR2) as

  COUNT_INDEXES INTEGER;
BEGIN
  SELECT COUNT(*)
  INTO COUNT_INDEXES
  FROM USER_INDEXES
  WHERE table_name = tbl_name_index and index_name not like '%UNIQUE%';

  IF COUNT_INDEXES <= 0 THEN
         EXECUTE IMMEDIATE 'COMMAND';


    end If;
end;

如您所见，其中定义了多个参数.我的问题是，如何使用多个输入执行此过程?

As you can see there are several parameters defined. My problem is, how can I execute this procedure with multiple inputs?

推荐答案

在SQL工作表中，从

From an SQL Worksheet, call your procedure from an anonymous block:

begin
  create_indexes (
    tbl_name_index    => 'TABLE_NAME',
    tbl_name_vehicle  => 'Vehicle name',
    tbl_name_dealer   => 'value 3',
    tbl_name_hst      => 'value 4',
    tbl_name_dms      => 'value 5',
    tbl_name_usertype => 'value 6',
    tbl_name_search   => 'value 7');
end;
/

正如Jeffrey Kemp所指出的，最好使用命名参数表示法，而不要使用位置表示法.它使其更清晰，有助于避免错误(尤其是使参数的顺序错误)，可以减少以后对过程的影响，并且在具有默认值的参数时更加灵活-因此，您可以跳过任何不包含默认值的参数要明确设置.

As Jeffrey Kemp noted, it's a good idea to use the named parameter notation, and not positional notation; it makes it clearer, helps avoids mistakes (particularly getting the order of arguments wrong), can reduce the impact of future changes to the procedure, and is more flexible when you have parameters with default values - so you can skip any you don't want to explicitly set.

您还可以使用 execute命令作为快捷方式，但是具有很多参数，坚持匿名块可能更容易-无论如何都是相同的.许多SQL * Plus文档也适用于SQL Developer.

You can also use the execute command as a shortcut, but with a lot of parameters it's probably easier to stick to an anonymous block - it's the same under the hood anyway. A lot of the SQL*Plus documentation also applied to SQL Developer.

SQL Developer文档还向您展示了如何执行并调试过程.

The SQL Developer documentation also shows you how to execute and debug a procedure.

如果这是您首次尝试PL/SQL，那么可能值得阅读文档.我不确定您要使用显示的代码去哪里；似乎有点困惑.在PL/SQL中创建诸如索引之类的对象是不寻常的，因为在创建或更新模式时应将它们构建一次，因此将其作为可重复使用的代码似乎毫无意义.

If this is your first foray into PL/SQL then it might be worth reviewing the documentation. I'm not sure where you're going with the code you've shown; it seems to be a bit confused. It's unusual to create objects like indexes in PL/SQL as they should be built once when the schema is created or updated, so having it as reusable code seems a bit pointless.

这篇关于具有多个IN参数的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！