[開発][SQL] FUNCTION PROCEDURE

---DIFINITION OF PACKAGE---
CREATE OR REPLACE PACKAGE test_package

IS
TYPE REF_CURSOR IS REF CURSOR;
FUNCTION test_function RETURN REF_CURSOR;
FUNCTION test_function2 RETURN varchar2;
PROCEDURE test_procedure;
PROCEDURE test_procedure2;

END test_package;
/

---DIFINITION OF PACKAGE BODY---
CREATE OR REPLACE PACKAGE BODY test_package
IS

-- ===================================
-- test_function
-- -----------------------------------
FUNCTION test_function
RETURN REF_CURSOR

IS
INPUT_RESOURCE REF_CURSOR;
/*It must be INPUT_RESOURCE to use iBatis? or Spring? or sth else.*/

BEGIN
OPEN INPUT_RESOURCE
FOR
SELECT * FROM hoge_table WHERE hoge_col = 'hoge';
RETURN INPUT_RESOURCE;
END;

-- ===================================
-- test_function2
-- -----------------------------------
FUNCTION test_function RETURN varchar2 AS hoge_col_value varchar2(30);
BEGIN
select hoge_col into hoge_col_value from hoge_table;
RETURN hoge_col_value;
END;

-- ===================================
-- test_procedure
-- -----------------------------------
PROCEDURE test_procedure
IS
BEGIN
UPDATE hoge_table set hoge_col2 = 'aaa' where hoge_col = 'hoge';
END;

-- ===================================
-- test_procedure2
-- -----------------------------------
PROCEDURE test_procedure2
IS
BEGIN
INSERT INTO hoge_table (hoge_col, hoge_col2, hoge_col3) values ('hoge', 'piyo', '2008');
END;
-- ===================================

END test_package;
/


select test_package.test_function from dual;
select test_package.test_function2 from dual;
select test_package.test_procedure from dual;
select test_package.test_procedure2 from dual;
2008-01-20 23:12 : 開発 : コメント : 0 : トラックバック : 0 :
コメントの投稿
非公開コメント

« next  ホーム  prev »

search

ad



counter


tag cloud

category cloud