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;

沒有留言:

張貼留言

202501 Debian USB LAN Card Bridge

 202501 Debian USB LAN Card Bridge ChatGPT Question I have a machine running debian 12 with a LAN port using a cable to connect to my office...