【DM】ドリーム・データベースのパッケージ・インスタンス
15535 ワード
パッケージの実装
テストセットの作成DROP TABLE IF EXISTS TEST;
CREATE TABLE TEST(
ID INT IDENTITY(1,1),
NAME VARCHAR(50),
CITY VARCHAR(50)
);
INSERT INTO TEST(NAME,CITY) VALUES(' ',' '),(' ',' '),(' ',' ');
パッケージ仕様の作成CREATE OR REPLACE PACKAGE PERSON AS --
NOPERSON EXCEPTION; --
PERSONCOUNT INT; --
CSR CURSOR; --
PROCEDURE ADDPERSON(NAME VARCHAR(50),CITY VARCHAR(50)); --
PROCEDURE REMOVEPERSON(V_NAME VARCHAR(50),V_CITY VARCHAR(50)); --
PROCEDURE REMOVEPERSON(V_ID INT); -- 、
FUNCTION GETPERSONCOUNT RETURN INT; -- ,
PROCEDURE GETPERSONLIST; --
END PERSON; --
パッケージ本体の作成CREATE OR REPLACE PACKAGE BODY PERSON AS
PROCEDURE ADDPERSON(NAME VARCHAR(50),CITY VARCHAR(50)) AS
BEGIN
INSERT INTO TEST(NAME,CITY) VALUES(NAME,CITY);
PERSONCOUNT=PERSONCOUNT+SQL%ROWCOUNT;
END ADDPERSON;
PROCEDURE REMOVEPERSON(V_NAME VARCHAR(50),V_CITY VARCHAR(50)) AS
BEGIN
DELETE FROM TEST WHERE NAME LIKE V_NAME AND CITY LIKE V_CITY;
PERSONCOUNT=PERSONCOUNT-SQL%ROWCOUNT;
END REMOVEPERSON;
PROCEDURE REMOVEPERSON(V_ID INT) AS
BEGIN
DELETE FROM TEST WHERE ID=V_ID;
PERSONCOUNT=PERSONCOUNT-SQL%ROWCOUNT;
END REMOVEPERSON;
FUNCTION GETPERSONCOUNT RETURN INT AS
BEGIN
RETURN PERSONCOUNT;
END GETPERSONCOUNT;
PROCEDURE GETPERSONLIST AS
DECLARE
V_ID INT;
V_NAME VARCHAR(50);
V_CITY VARCHAR(50);
BEGIN
IF PERSONCOUNT=0 THEN
RAISE NOPERSON;
END IF;
OPEN CSR FOR SELECT ID,NAME,CITY FROM TEST;
LOOP
FETCH CSR INTO V_ID,V_NAME,V_CITY;
EXIT WHEN CSR%NOTFOUND;
PRINT (CAST(V_ID AS VARCHAR(50))||' '||V_NAME||', '||V_CITY);
END LOOP;
CLOSE CSR;
END GETPERSONLIST;
BEGIN
SELECT COUNT(*) INTO PERSONCOUNT FROM TEST;
END PERSON
パッケージの再コンパイルALTER PACKAGE PERSON COMPILE;
パケット内のADDPERSONプロシージャを呼び出し、テーブルにレコードを追加SQL> CALL PERSON.ADDPERSON(' ',' ');
DMSQL
: 1.963( ). :1925.
SQL> SELECT * FROM TEST;
ID NAME CITY
---------- ----------- ---- ----
1 1
2 2
3 3
4 4
: 0.412( ). :1926.
SQL>
REMOVEPERSONプロセスを呼び出し、追加したレコードを削除SQL> CALL PERSON.REMOVEPERSON(' ',' ');
DMSQL
: 13.914( ). :1979.
SQL> SELECT * FROM TEST;
ID NAME CITY
---------- ----------- ---- ----
1 1
2 2
3 3
: 0.159( ). :1980.
REMOVEPERSONプロセスはリロードされ、例えばIDを伝達して羅雲を削除するSQL> CALL PERSON.REMOVEPERSON(3);
DMSQL
: 49.736( ). :1989.
SQL> SELECT * FROM TEST;
ID NAME CITY
---------- ----------- ---- ----
1 1
2 2
: 0.342( ). :1990.
パッケージ内の変数を参照し、合計人数を取得SQL> SELECT PERSON.PERSONCOUNT;
PERSON.PERSONCOUNT
---------- ------------------
1 2
: 0.867( ). :1991.
パッケージ内の関数を呼び出し、合計人数を取得SQL> SELECT PERSON.GETPERSONCOUNT;
GETPERSONCOUNT
---------- --------------
1 2
: 0.623( ). :1992.
GETPERSONLISTプロシージャを呼び出してテーブルの詳細を表示SQL> CALL PERSON.GETPERSONLIST;
1 ,
2 ,
DMSQL
: 0.290( ). :1995.
DROP TABLE IF EXISTS TEST;
CREATE TABLE TEST(
ID INT IDENTITY(1,1),
NAME VARCHAR(50),
CITY VARCHAR(50)
);
INSERT INTO TEST(NAME,CITY) VALUES(' ',' '),(' ',' '),(' ',' ');
CREATE OR REPLACE PACKAGE PERSON AS --
NOPERSON EXCEPTION; --
PERSONCOUNT INT; --
CSR CURSOR; --
PROCEDURE ADDPERSON(NAME VARCHAR(50),CITY VARCHAR(50)); --
PROCEDURE REMOVEPERSON(V_NAME VARCHAR(50),V_CITY VARCHAR(50)); --
PROCEDURE REMOVEPERSON(V_ID INT); -- 、
FUNCTION GETPERSONCOUNT RETURN INT; -- ,
PROCEDURE GETPERSONLIST; --
END PERSON; --
CREATE OR REPLACE PACKAGE BODY PERSON AS
PROCEDURE ADDPERSON(NAME VARCHAR(50),CITY VARCHAR(50)) AS
BEGIN
INSERT INTO TEST(NAME,CITY) VALUES(NAME,CITY);
PERSONCOUNT=PERSONCOUNT+SQL%ROWCOUNT;
END ADDPERSON;
PROCEDURE REMOVEPERSON(V_NAME VARCHAR(50),V_CITY VARCHAR(50)) AS
BEGIN
DELETE FROM TEST WHERE NAME LIKE V_NAME AND CITY LIKE V_CITY;
PERSONCOUNT=PERSONCOUNT-SQL%ROWCOUNT;
END REMOVEPERSON;
PROCEDURE REMOVEPERSON(V_ID INT) AS
BEGIN
DELETE FROM TEST WHERE ID=V_ID;
PERSONCOUNT=PERSONCOUNT-SQL%ROWCOUNT;
END REMOVEPERSON;
FUNCTION GETPERSONCOUNT RETURN INT AS
BEGIN
RETURN PERSONCOUNT;
END GETPERSONCOUNT;
PROCEDURE GETPERSONLIST AS
DECLARE
V_ID INT;
V_NAME VARCHAR(50);
V_CITY VARCHAR(50);
BEGIN
IF PERSONCOUNT=0 THEN
RAISE NOPERSON;
END IF;
OPEN CSR FOR SELECT ID,NAME,CITY FROM TEST;
LOOP
FETCH CSR INTO V_ID,V_NAME,V_CITY;
EXIT WHEN CSR%NOTFOUND;
PRINT (CAST(V_ID AS VARCHAR(50))||' '||V_NAME||', '||V_CITY);
END LOOP;
CLOSE CSR;
END GETPERSONLIST;
BEGIN
SELECT COUNT(*) INTO PERSONCOUNT FROM TEST;
END PERSON
ALTER PACKAGE PERSON COMPILE;
SQL> CALL PERSON.ADDPERSON(' ',' ');
DMSQL
: 1.963( ). :1925.
SQL> SELECT * FROM TEST;
ID NAME CITY
---------- ----------- ---- ----
1 1
2 2
3 3
4 4
: 0.412( ). :1926.
SQL>
SQL> CALL PERSON.REMOVEPERSON(' ',' ');
DMSQL
: 13.914( ). :1979.
SQL> SELECT * FROM TEST;
ID NAME CITY
---------- ----------- ---- ----
1 1
2 2
3 3
: 0.159( ). :1980.
SQL> CALL PERSON.REMOVEPERSON(3);
DMSQL
: 49.736( ). :1989.
SQL> SELECT * FROM TEST;
ID NAME CITY
---------- ----------- ---- ----
1 1
2 2
: 0.342( ). :1990.
SQL> SELECT PERSON.PERSONCOUNT;
PERSON.PERSONCOUNT
---------- ------------------
1 2
: 0.867( ). :1991.
SQL> SELECT PERSON.GETPERSONCOUNT;
GETPERSONCOUNT
---------- --------------
1 2
: 0.623( ). :1992.
SQL> CALL PERSON.GETPERSONLIST;
1 ,
2 ,
DMSQL
: 0.290( ). :1995.