2015年1月27日 星期二

Oracle Database SQL Developer - How to write and call a stored procedure?



Assume you have below store procedure

create or replace PROCEDURE           RICMAP_TABLE_CLEANUP(TABLENAME IN VARCHAR2,RETURN_CODE OUT NUMBER, RETURN_MESSAGE OUT VARCHAR2)
AUTHID DEFINER
AS
BEGIN
   EXECUTE IMMEDIATE 'TRUNCATE TABLE '||UPPER(TABLENAME);
 
   FOR CONSTRAINT_LIST IN (SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = UPPER(TABLENAME) AND CONSTRAINT_TYPE = 'P')
   LOOP
   EXECUTE IMMEDIATE 'ALTER TABLE '||UPPER(TABLENAME)||' DISABLE CONSTRAINT '||CONSTRAINT_LIST.CONSTRAINT_NAME;
   END LOOP;
 
   FOR INDEX_LIST IN (SELECT INDEX_NAME FROM  USER_INDEXES WHERE TABLE_NAME = UPPER(TABLENAME))
   LOOP
   EXECUTE IMMEDIATE 'DROP INDEX '|| INDEX_LIST.INDEX_NAME;
   END LOOP;
   RETURN_CODE := 0;
   RETURN_MESSAGE := 'SUCCESS';
EXCEPTION
   WHEN OTHERS THEN
      RETURN_CODE := SQLCODE;
      RETURN_MESSAGE := SQLERRM;
END;


You can call the store procedure as below

select count (*) from stage_instruments;
var returnCode number;
var returnMessage varchar2;
execute RICMAP_TABLE_CLEANUP('stage_instruments', :returnCode, :returnMessage)
select count (*) from stage_instruments;

沒有留言:

張貼留言

2007 to 2023 HP and Dell Servers Comparison

  HP Gen5 to Gen11  using ChatGPT HP ProLiant Gen Active Years CPU Socket Popular HP CPUs Cores Base Clock Max RAM Capacity Comparable Dell ...