如何在 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

我在谷歌搜索后发现的一个解决方案是忽略返回码.我们有更优雅的方式来做到这一点吗?

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

谢谢

更新:在下面的答案的帮助下，我们编写了一个如下的过程来删除过程

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 存在部分只是对该视图的 EXISTS 查询，具有正确的 proc 名称.

(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屋！