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)
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...
-
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...
沒有留言:
張貼留言