在将它放入db2 9.1之前，如何检查过程/视图/表是否存在？ [英] How to check a procedure/view/table exists or not before dropping it in db2 9.1?

问题描述

我们如何在db2中写下下面的伪代码，

How do we write below pseudo code in db2,

If (Proc exists)
  Drop Proc
  Create Proc
Else
 Create Proc

googling是忽略返回码。我们有更优雅的方式吗？

One solution I found, after googling is to ignore the return codes. Do we have a more elegant way to do this?

感谢

更新：在下面的答案的帮助下，我们写了一个proc如下所示，删除过程

Update: With the help of the answer below we wrote a proc as below to drop the procedures

  CREATE PROCEDURE SVCASNDB.DROPSP(IN P_SPECIFICNAME VARCHAR(128))
        SPECIFIC DROPSP

        P1: BEGIN


        -- Drop the SP if it already exists
        if exists (SELECT SPECIFICNAME FROM SYSIBM.SYSROUTINES WHERE SPECIFICNAME = trim(upper(p_SpecificName))) then
            begin
                DECLARE v_StmtString VARCHAR (1024);
                SET v_StmtString = 'DROP SPECIFIC PROCEDURE SCHEMA.' || p_SpecificName;
                PREPARE stmt1 FROM v_StmtString ;
                EXECUTE stmt1;
            end;
        end if;

    END P1

推荐答案

查询：

SELECT DISTINCT ROUTINENAME, RESULT_SETS, REMARKS 
FROM SYSIBM.SYSROUTINES 
WHERE ROUTINESCHEMA='<schema>' AND FUNCTION_TYPE NOT IN ('S', 'T')

您的模式名称在占位符）
给你一个模式中的所有procs。因此，Proc存在部分只是对具有正确的proc名称的视图的一个EXISTS查询。

(where you specify your schema name at the placeholder) gives you all procs in a schema. So the Proc exists part is simply an EXISTS query on that view with the proper proc name.

这篇关于在将它放入db2 9.1之前，如何检查过程/视图/表是否存在？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！