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;
訂閱:
張貼留言 (Atom)
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 ...
-
On CentOS 7, using yum install gives you cmake version 2.8.11 [root@thrift1 ~]# cat /etc/*release CentOS Linux release 7.2.1511 (Core) ...
-
Synology DSM 5.2 on Virtual Box Files On my Google Drive "2016DSM5.2" or download link below (3 files total: pat, iso, exe) ...
-
Static ZeroMQ (difficult setup) cd /d C:\ mkdir Repos cd /d C:\Repos\ git clone https://github.com/Microsoft/vcpkg cd /d C:\Repos\v...
沒有留言:
張貼留言