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;

沒有留言:

張貼留言

2023 Promox on Morefine N6000 16GB 512GB

2023 Promox on Morefine N6000 16GB 512GB Software Etcher 100MB (not but can be rufus-4.3.exe 1.4MB) Proxmox VE 7.4 ISO Installer (1st ISO re...