SQL DDLシ壹せ繝医い繝牙ョ夂セゥ

繧ケ繝医い繝峨励Ο繧キ繝シ繧ク繝」螳夂セゥ

CREATE OR REPLACE PROCEDURE MyProcedure
(
prm IN NUMBER,
ret OUT NUMBER
) AS

BEGIN

縲ret := prm * 100;
蠑墓焚縺ョ螟画焚縺ッ縺昴ョ縺セ縺セ菴ソ逕ィ蜿ッ閭ス

EXCEPTION
縲WHEN OTHERS THEN
縲縲ret : =0;
END;

CREATE PROCEDURE 繧ケ繝医い繝牙錐 蠑墓焚 蝙区ュ蝣ア AS
縲縲SELECT 蛻怜錐 FROM 陦ィ蜷 WHERE 蛻怜錐 シ 蠑墓焚

繧ケ繝医い繝峨ヵ繧。繝ウ繧ッ繧キ繝ァ繝ウ螳夂セゥ

CREATE OR REPLACE FUNCTION MyFunction
(
縲PRM IN VARCHAR1
)
RETURN VARCHAR2 AS

縲RET VARCHAR2(4);

BEGIN

縲RET := PRM;

RETURN RET;

EXCEPTION
縲WHEN OTHERS THEN
縲縲RETURN NULL;

END MyFunction;

繝ヲ繝シ繧カ繝シ螳夂セゥ髢「謨ー縺ョ菴懈
CREATE OR REPLACE FUNCTION 髢「謨ー蜷(螟画焚蜷 蝙)
RETURNS
縲蝙
AS $$
DECLARE
縲螟画焚蜷 蝙;
BEGIN
縲蜃ヲ逅
縲RETURN 謌サ繧雁、;
END
$$ LANGUAGE plpgsql;


CREATE FUNCTION test() 菴懈
CREATE OR REPLACE FUNCTION test() 荳頑嶌縺
CREATE OR REPLACE FUNCTION test(prm1 INT, prm2 TEXT) 蠑墓焚譛峨j
RETURNS
縲INT 謌サ繧雁、譛峨j
縲VOID 謌サ繧雁、辟。縺
AS $$
DECLARE val INT; 逵∫払蜿ッ
BEGIN
蜃ヲ逅
縲RETURN val; 謌サ繧雁、辟。縺励ョ蝣エ蜷医ッ逵∫払蜿ッ
END
$$ LANGUAGE plpgsql;

繝ヲ繝シ繧カ繝シ螳夂セゥ髢「謨ー縺ョ蜑企勁
(謗・邯壼セ)
# drop function PL/pgSQL髢「謨ー蜷();
# drop function PL/pgSQL髢「謨ー蜷(蝙);
蠑墓焚譛/辟。縺ョ髢「謨ー縺ッ蛻・縲縺ォ蜑企勁縺吶k

# drop function if exists PL/pgSQL髢「謨ー蜷();
譚。莉カ莉倥″蜑企勁

繝代ャ繧ア繝シ繧ク

繝サ繧ケ繝医い繝峨励Ο繧キ繝シ繧ク繝」
繝サ繧ケ繝医い繝峨ヵ繧。繝ウ繧ッ繧キ繝ァ繝ウ
繧貞鬘槭☆繧狗ぜ縺ョ蜷咲ァー
Java縺ョ繝代ャ繧ア繝シ繧ク縺昴ョ繧ゅョ
.NET縺ァ險縺蜷榊燕遨コ髢

縲仙ョ夂セゥ縲

莉墓ァ倬Κ
繝代ャ繧ア繝シ繧ク縺ォ蜷ォ縺セ繧後k繝輔ぃ繝ウ繧ッ繧キ繝ァ繝ウ(繝励Ο繧キ繝シ繧ク繝」)縺ョ蜷榊燕縺ィ蠑墓焚縲∵綾繧雁、縺ョ縺ソ繧貞ョ夂セゥ

CREATE [OR REPLACE] PACKAGE (繝代ャ繧ア繝シ繧ク蜷) AS

縲FUNCTION 繝輔ぃ繝ウ繧ッ繧キ繝ァ繝ウ蜷(蠑墓焚1 IN 蝙1, 蠑墓焚2 IN 蝙2) RETURN 謌サ繧雁、蝙;

縲PROCEDURE 繝励Ο繧キ繝シ繧ク繝」蜷(蠑墓焚1 IN 蝙1, 蠑墓焚2 IN 蝙2);

END;

BODY驛ィ
螳溘ヵ繧。繝ウ繧ッ繧キ繝ァ繝ウ(繝励Ο繧キ繝シ繧ク繝」)縲√げ繝ュ繝シ繝舌Ν螟画焚

CREATE [OR REPLACE] PACKAGE BODY 繝代ャ繧ア繝シ繧ク蜷 AS

繧ー繝ュ繝シ繝舌Ν螟画焚
縲螟画焚蜷 蝙;

縲FUNCTION 繝輔ぃ繝ウ繧ッ繧キ繝ァ繝ウ蜷(蠑墓焚1 IN 蝙1, 蠑墓焚2 IN 蝙2) RETURN 謌サ繧雁、蝙
縲縲IS
縲縲縲ス
縲縲END 繝輔ぃ繝ウ繧ッ繧キ繝ァ繝ウ蜷;

縲PROCEDURE 繝励Ο繧キ繝シ繧ク繝」蜷(蠑墓焚1 IN 蝙1, 蠑墓焚2 IN 蝙2)
縲縲IS
縲縲縲ス
縲縲END 繝励Ο繧キ繝シ繧ク繝」蜷;

END 繝代ャ繧ア繝シ繧ク蜷;

縲仙炎髯、縲
DROP PACKAGE <繝代ャ繧ア繝シ繧ク蜷>;
DROP PACKAGE BODY <繝代ャ繧ア繝シ繧ク蜷>;

繧ケ繝医い繝峨励Ο繧ー繝ゥ繝縺ョ蜻シ縺ウ蜃コ縺

SQL> execute 繧ケ繝医い繝牙錐('ス', 'ス');
SQL> exe 繧ケ繝医い繝牙錐('ス', 'ス');
SQL> execute 繝代ャ繧ア繝シ繧ク.繧ケ繝医い繝牙錐('ス', 'ス');

繝ヲ繝シ繧カ繝シ螳夂セゥ髢「謨ー縺ョ螳溯。
(謗・邯壼セ)
# select PL/pgSQL髢「謨ー蜷();
# select PL/pgSQL髢「謨ー蜷(蠑墓焚);

EXECUTE 繧ケ繝医い繝牙錐
縲縲@Prm1 = @SyoriYMD1,
縲縲@Prm2 = 1000,
縲縲@Prm3 = 'TEST'
縲縲

Follow me!

蜑阪ョ險倅コ

Oracle DatabaseExpress