SQL DMLシ壼宛蠕。譁

謇狗カ壹″蝙玖ィ隱

ProceduralLanguageSQL
OracleDatabas逕ィ繝励Ο繧ー繝ゥ繝險隱
髱樊焔邯壹″蝙玖ィ隱槭〒縺ゅkSQL繧呈焔邯壹″蝙玖ィ隱槭↓諡。蠑オ縺励※縺繧九
螟画焚縲,ASE縲´OOP遲峨ョ蛻カ蠕。譁繧貞茜逕ィ縺励◆荳翫〒SQL繧貞茜逕ィ縺ァ縺阪k縲
霎樊嶌/謇狗カ壹″蝙九サ髱樊焔邯壹″蝙玖ィ隱縲榊盾辣ァ
繝サ繧ケ繝医い繝峨励Ο繧ー繝ゥ繝縺ョ蜀驛ィ縺ォ險倩ソー
繝サSQL*Plus遲峨ョ繧ッ繝ゥ繧、繧「繝ウ繝医励Ο繧ー繝ゥ繝縺九i螳溯。後☆繧九せ繧ッ繝ェ繝励ヨ繝輔ぃ繧、繝ォ縺ォ險倩ソー
繝サSQL*Plus遲峨ョ繧ッ繝ゥ繧、繧「繝ウ繝医励Ο繧ー繝ゥ繝縺九i逶エ謗・險倩ソー
遲峨ョ菴ソ縺譁ケ縺後〒縺阪k縲
PL/SQL繧貞縺ォ縺励◆險隱槭ょ錐遘ー縺ッPL/pgSQL

PL/SQL繝悶Ο繝繧ッ

DECLARE
縲螳」險驛ィ
螟画焚 蝙;
BEGIN
縲螟画焚 := 蛟、;

SELECT ス WHERE 蛻 = 螟画焚; 竊舌お繝ゥ繝シ
縲PL/SQL蜀縺ョSQL縺ク螟画焚繧貞沂繧∬セシ繧縺ォ縺ッ鄂ョ謠帛、画焚縲√ヰ繧、繝ウ繝牙、画焚繧貞茜逕ィ縺吶k

縲SELECT ス WHERE 蛻 = :繝舌う繝ウ繝牙、画焚;
縲SELECT ス WHERE 蛻 = &鄂ョ謠帛、画焚;
END;
/

VAR myVal NUMBER;
SQL*Plus縺ョ螟画焚(繝舌う繝ウ繝牙、画焚)縺ッPL/SQL繝悶Ο繝繧ッ螟悶〒螳」險
Oracle SQL*Plus菴ソ逕ィ譁ケ豕/繝舌う繝ウ繝牙、画焚縲榊盾辣ァ
DECLARE
縲:myVal = 5;
SQL*Plus螟画焚繧よ桃菴懷庄閭ス
BEGIN

END;
/

DECLARE
縲螳」險驛ィ
螟画焚 蝙;
BEGIN
縲蜃ヲ逅
END;

險ュ螳壼、画峩

繧ィ繝ゥ繝シ縺檎匱逕溘☆繧九→MS-DOS縺ョerrorlevel螟画焚縺ォ1縺梧シ邏阪&繧後k
\set ON_ERROR_STOP
\set AUTOCOMMIT off
BEGIN;
ス;
COMMIT;

繧ケ繝医い繝

繧ケ繝医い繝峨励Ο繧ー繝ゥ繝蜀縺ァPL/SQL繧剃スソ逕ィ縺ァ縺阪k縲
SQL DDLシ壹せ繝医い繝牙ョ夂セゥ縲榊盾辣ァ

萓句、門ヲ逅

繝。繝繧サ繝シ繧ク陦ィ遉コ險ュ螳
SET SERVEROUTPUT ON

DECLARE
迢ャ閾ェ縺ョ萓句、悶r螳夂セゥ
MYERROR EXCEPTION;
BEGIN
縲ス
縲IF ス THEN
縲縲萓句、悶r逋コ逕溘&縺帙k
縲縲RAISE MYERROR;
縲END IF;

EXCEPTION
縲WHEN MYERROR THEN
縲縲逕サ髱「縺ォ繝。繝繧サ繝シ繧ク陦ィ遉コ
縲縲DBMS_OUTPUT.PUT_LINE("迢ャ閾ェ繧ィ繝ゥ繝シ");
縲縲ROLLBACK;
縲縲
縲WHEN SQLERROR THEN
縲縲DBMS_OUTPUT.PUT_LINE("シ、シ「繧ィ繝ゥ繝シ");
縲縲ROLLBACK;
縲縲
縲WHEN OTHERS THEN
縲縲DBMS_OUTPUT.PUT_LINE("縺昴ョ莉悶ョ繧ィ繝ゥ繝シ");
縲縲ROLLBACK;
END;
/

BEGIN TRY
縲縲ス
END TRY

BEGIN CATCH
縲縲
END CATCH;

繝ュ繝シ繧ォ繝ォ繧オ繝悶励Ο繧ー繝ゥ繝(繧、繝ウ繝ゥ繧、繝ウ髢「謨ー)

DECLARE
縲PROCEDURE TEST(prm NUMBER)
縲IS
縲BEGIN
縲縲UPDATE ス SET ス = prm;
縲END;

BEGIN
繝ュ繝シ繧ォ繝ォ繧オ繝悶励Ο繧ー繝ゥ繝螳溯。
TEST(5);
END;

螟画焚

螳夂セゥ

DECLARE
螟画焚 蝙;
縲VAL1 NUMBER;
縲VAL2 VARCHAR(10);
縲VAL3 陦ィ蜷.蛻怜錐%TYPE;
縲縲SQL DMLシ壹ョ繝シ繧ソ蝙縲榊盾辣ァ
BEGIN
縲:VAL1 = 5;
縲:VAL2 = '5';
END;
RETURNS
縲INT;
謌サ繧雁、縺ョ蝙
縲VOID;
謌サ繧雁、辟。縺励ョ蝣エ蜷
DECLARE
螟画焚 蝙;
縲VAL1 INT;
縲VAL2 VARCHAR(10) := '5';
螳」險縺ィ蜷梧凾縺ォ蛻晄悄蛹悶b蜿ッ閭ス
BEGIN
縲VAL1 := 5;
縲VAL2 := '5';
END;

SELECT譁縺九i蜿門セ

SET SERVEROUTPUT ON

DECLARE
VAL1 陦ィ蜷.蛻怜錐1%TYPE;
VAL1 陦ィ蜷.蛻怜錐2%TYPE;
BEGIN
謚ス蜃コ邨先棡繧呈シ邏
PL/SQL縺ァ縺ッ蜊倡エ斐↑SELECT譁逋コ陦後ッ繧ィ繝ゥ繝シ
縲SELECT 蛻怜錐1, 蛻怜錐2 INTO VAL1,VAL2
縲FROM 陦ィ蜷
縲WHERE ROWNUM <= 1
縲ORDER BY ス;

DBMS_OUTPUT.PUT_LINE(VAL1);
縲DBMS_OUTPUT.PUT_LINE(VAL2);


ORACLE縺ァ縺ッUPDATE譁縺ォORDER BY繧剃スソ逕ィ縺ァ縺阪↑縺轤コ縲≫代〒荳サ繧ュ繝シ繧貞叙蠕励@縺ヲ譚。莉カ縺ィ縺吶k
縲UPDATE 陦ィ蜷 SET 蛻怜錐 = ス
縲WHERE 蛻怜錐1 = VAL1 AND 蛻怜錐2 = VAL2;

END;
/

RETURNS
縲INT;
DECLARE
VAL INT;
BEGIN
縲SELECT 蛻 INTO VAL FROM 陦ィ;
縲RETURN VAL;
END;

蛻カ蠕。譁

IF譁

DECLARE
縲縲myNum Number := 10;
縲縲myChar VARCHAR2(255);
BEGIN
縲縲IF (myNum = 0) THEN
縲縲縲縲myChar := 'ZERO';
縲縲ELSIF (myNum > 0) THEN
縲縲ELSE IF縺ァ繧ELSEIF縺ァ繧ゅ↑縺
縲縲縲縲myChar := 'OVER';
縲縲ELSE
縲縲縲縲myChar := 'UNDER';
縲縲END IF;
縲縲
縲縲SYS.DBMS_OUTPUT.PUT_LINE(myChar);
END;

シ代▽縺ョ繝悶Ο繝繧ッ繧 BEGIN ス END 縺ァ蝗イ繧

IF (@prmTEST = 1)
縲縲BEGIN
縲縲
縲縲END
ELSE IF (@prmTEST = 2)
縲縲BEGIN
縲縲
縲縲END
ELSE
縲縲BEGIN
縲縲縲縲IF (@prmTEST = 3)
縲縲縲縲縲縲BEGIN
縲縲縲縲縲縲
縲縲縲縲縲縲END
縲縲END
縲縲

CASE譁繝サCASE蠑

CASE蠑
CASE 蛻、螳壼ッセ雎。 WHEN 蛻、螳壼ッセ雎。 THEN ス
シ昴@縺玖ゥ穂セ。縺ァ縺阪↑縺縲IS NULL 繧 荳咲ュ牙捷遲峨ッテ励

SELECT
縲NAME,
CASE NAME
縲縲WHEN 'AAA' THEN
縲縲WHEN NULL THEN ス橸シ堙
縲縲WHEN (NULL) THEN ス橸シ壹お繝ゥ繝シ
縲縲WHEN IS (NULL) THEN ス橸シ壹お繝ゥ繝シ

縲縲ELSE
END NullOrNotNull
FROM MYTABLE;

CASE譁
CASE WHEN 蛻、螳壼ッセ雎。 隧穂セ。蠑 蛻、螳壼ッセ雎。 THEN ス

SELECT
CASE
縲縲WHEN ID IN (1,3,5) THEN 'GroupA'
縲縲WHEN ID > 10 THEN 'GroupB'
縲縲ELSE 'GroupC'
END ID,
CASE
縲縲WHEN NAME IS NULL THEN 'NULL'
縲縲WHEN NAME IS NOT NULL THEN 'NOT NULL'
END NAME,
CASE
縲縲WHEN MYDATE BETWEEN '2000-01-01' AND '2010-12-31' THEN
縲縲ELSE
END MYDATE
FROM MYTABLE;

(蜿りシ咼ECODE髢「謨ー菴ソ逕ィ譎)
SELECT
縲NAME,
DECODE(NAME ,
縲縲'AAA' ,
縲縲'BBB' ,
縲縲ス (繝繝輔か繝ォ繝亥、))
縲END FieldNm
FROM MYTABLE;

窶サ繝繝輔か繝ォ繝亥、逵∫払譎ゅ∵欠螳壽擅莉カ縺ォ蠖薙※縺ッ縺セ繧峨↑縺蝣エ蜷医ッ(NULL)

竊代rCASE譁縺ァ陦ィ縺吝エ蜷
SELECT
縲NAME,
縲CASE NAME
縲縲WHEN 'AAA' THEN ス
縲縲WHEN 'BBB' THEN ス
縲縲ELSE ス
縲END FieldNm
FROM MYTABLE;

SELECT
縲縲ProjCd,
縲縲DiaCd,
縲縲CASE DiaCd
縲縲縲縲WHEN '1' THEN '蟷ウ譌・'
縲縲縲縲WHEN '2' THEN '蝨滓律逾'
縲縲縲縲ELSE ''
縲縲END縲縲AS 'DiaNm'
FROM
縲縲TimeTei

SELECT
縲縲ProjCd,
縲縲DiaCd,
縲縲CASE
縲縲縲縲WHEN DiaCd IN ('1') THEN '蟷ウ譌・'
縲縲縲縲WHEN DiaCd IN ('2','3') THEN '莨第律'
縲縲END縲縲AS 'DiaNm'
FROM
縲縲TimeTei

WHERE蜿・縺ァ縺ョCASE譁

蛻励=縲CASE 譚。莉カ THEN 0
竊 縺薙≧縺縺譖ク縺肴婿縺ッ縺ァ縺阪↑縺
CASE
縲縲WHEN 譚。莉カ THEN 蛻 = ス
縲縲ELSE 蛻 = ス
END

Select * From 陦ィ蜷
Where
(
縲CASE
縲縲WHEN 譚。莉カ THEN colmA
縲縲WHEN 譚。莉カ THEN colmB
縲END
) = 100
AND colC =
(
縲CASE
縲縲WHEN 譚。莉カ THEN 100
縲縲WHEN 譚。莉カ THEN 200
縲END
)


Select * From table1
Where colmA = 100 AND colC = 100

SELECT
縲縲*
WHERE

縲縲dbo.TNM_WorkTerm.SiyoKbn = @UseKbnON
縲縲
縲縲AND dbo.TBL_Proj.ProjectCd >=
縲縲縲縲CASE
縲縲縲縲縲縲WHEN @ProjectCd = 0 THEN 1
縲縲縲縲縲縲ELSE @ProjectCd
縲縲縲縲END縲'窶サAND dbo.TBL_Proj.ProjectCd >= 1
縲縲
縲縲AND dbo.TBL_Proj.ProjectCd <=
縲縲縲縲CASE
縲縲縲縲縲縲WHEN @ProjectCd = 0 THEN 999999
縲縲縲縲縲縲ELSE @ProjectCd
縲縲縲縲END縲'窶サAND dbo.TBL_Proj.ProjectCd <= 999999

For譁

DECLARE
縲縲ス
BEGIN
縲for i in 1..3 loop
縲縲for j in 1..3 loop
縲縲縲@test.sql d s
縲縲loop;
縲end loop;
END;

DECLARE
縲i INT;
縲j INT;
BEGIN
縲for i in 1..3 loop
縲縲for j in 1..3 loop
縲縲縲INSERT INTO 陦ィ VALUES(i, j);
縲縲loop;
縲end loop;
END;

Loop譁

BEGIN
縲loop
縲縲ス
縲縲exit when 譚。莉カ蠑
縲縲exit when 蛟、 > 5;
縲end loop;
END;

蜃コ蜉

繝ュ繧ー

SET SERVEROUTPUT ON;
BEGIN
縲DBMS_OUTPUT.PUT_LINE(ス);

EXCEPTION
縲DBMS_OUTPUT.PUT_LINE('繝繝舌ャ繧ー繝。繝繧サ繝シ繧ク');
縲DBMS_OUTPUT.PUT_LINE('SQL 繧ウ繝シ繝 = ' || SQLCODE);
縲DBMS_OUTPUT.PUT_LINE('繧ィ繝ゥ繝シ蜀螳ケ = ' || SQLERRM(SQLCODE));
END;

逕サ髱「

RAISE (繧ィ繝ゥ繝シ繝ャ繝吶Ν) (繝。繝繧サ繝シ繧ク譁蟄怜)
RAISE INFO 'aaa%aaa%aaa', 111, 222;
竊誕aa111aaa222aaa

蜍慕噪SQL

SQL譁繧呈枚蟄怜励〒逕滓舌サ螳溯。

WK_SQL VARCHAR(2000);

WK_SQL := '';縲
WK_SQL := WK_SQL || 'UPDATE ス FROM ス WHERE ';

IF ス THEN
縲WK_SQL := WK_SQL || ' (蛻怜錐1) = ''oracle'' AND ';
END IF;
IF ス THEN
縲WK_SQL := WK_SQL || ' (蛻怜錐2) = ''pl/sql'' AND ';
END IF;

竊全QL譁繧貞ョ溯。
EXECUTE IMMEDIATE WK_SQL;

SQL譁縺ク縺ョ蝓九a霎シ縺ソ

WK_SQL VARCHAR(2000);

INSERT/UPDATE/DELETE譁
WK_SQL := '';縲
WK_SQL := WK_SQL || 'UPDATE ス FROM ス ';
WK_SQL := WK_SQL || 'WHERE ス = :=val1 AND ス = val2 ';

竊全QL譁縺ォ蛟、繧偵ヰ繧、繝ウ繝峨@縺ヲ螳溯。
EXECUTE IMMEDIATE WK_SQL USING '1', '1';
窶サ繝舌う繝ウ繝牙、画焚シ嘛al1縲」al2縺ッ螳」險縺励↑縺上※濶ッ縺

INTO蜿・
EXECUTE IMMEDIATE WK_SQL INTOUSING '1', '1';
INTO蜿・縺ッ蜍慕噪SQL譁縺ョ譁蟄怜励↓蜈・繧後i繧後↑縺
EXECUTE IMMEDIATE 'SELECT (蛻) INTO (螟画焚) FROM (陦ィ)' 縺ッテ
EXECUTE IMMEDIATE 'SELECT (蛻) FROM (陦ィ)' INTO (螟画焚) 縺ッ笳

繧ォ繝シ繧ス繝ォ蜀縺ョSELECT譁
OPEN cu FOR 'SELECT ス FROM ス WHERE ス = :=val1 AND ス = val2 ' USING 1, '1';
繧ォ繝シ繧ス繝ォOPEN譎ゅ↓繝舌う繝ウ繝牙、画焚縺ォ蛟、繧貞沂繧∬セシ縺ソ
LOOP
縲FETCH cu INTO r;
縲EXIT WHEN cu%NOTFOUND;
縲ス
END LOOP;
CLOSE cu;

繧ォ繝シ繧ス繝ォ

繝輔ぉ繝繝√√き繝シ繧ス繝ォ遘サ蜍

declare
縲rec 蜿玲ウィm%rowtype;
cursor cu
is
縲縲select col1 from縲蜿玲ウィm
縲;
begin
open cu;
loop
縲縲fetch cu into rec;
縲縲exit when cu%notfound;
縲縲繝繝シ繧ソ蜿門セ怜ッセ雎。縺檎┌縺蝣エ蜷医ッ蜃ヲ逅繧呈栢縺代k
縲縲ス;
end loop;
close cu;
end;

DECLARE
縲cur CURSOR FOR SELECT 蛻 FROM 陦ィ;
縺セ縺溘ッ
cur CURSOR IS SELECT 蛻 FROM 陦ィ;
縲rec RECORD;
BEGIN
OPEN cur;
LOOP
縲縲FETCH cur INTO rec;
縲縲EXIT WHEN NOT FOUND;
縲縲縺セ縺溘ッ
縲縲IF NOT FOUND THEN
縲縲縲
縲縲縲EXIT;
縲縲END IF;
縲縲INSERT INTO 陦ィ VALUES ( rec.蛻 );
END LOOP;
END

(1)繧ォ繝シ繧ス繝ォ縺ョ螳」險
DECLARE シサ繧ォ繝シ繧ス繝ォ蜷搾シスCURSOR FORシサSELECT譁シス


DECLARE TestCursor CURSOR FOR
SELECT ス FROM TestTable

(2)繧ォ繝シ繧ス繝ォ繧帝幕縺
OPEN シサ繧ォ繝シ繧ス繝ォ蜷搾シス

OPEN TestCursor

(3)1陦後#縺ィ縺ォ繝繝シ繧ソ繧貞叙繧雁コ縺
FETCH NEXT FROM シサ繧ォ繝シ繧ス繝ォ蜷搾シス INTO シサ繧ォ繝シ繧ス繝ォ逕ィ螟画焚シス
WHILE シサ譚。莉カ蠑擾シス
縲BEGIN
縲シサ蜃ヲ逅蜀螳ケシス
縲END


DECLRE @TestColumn1 INT
DECLRE @TestColumn2 INT

WHILE (@@fetch_status = 0)
繧ォ繝シ繧ス繝ォ蜈ィ莉カLoop
縲BEGIN
縲縲FETCH NEXT FROM TestCursor INTO @TestColumn1,@TestColumn2
縲縲繧ォ繝シ繧ス繝ォ縺九i蛟、繧貞叙繧雁コ縺励 TestColumn1縲2縺ク譬シ邏
縲縲
縲縲PRINT @TestColumn1 + ' ' + TestColumn2
縲END

(4)繧ォ繝シ繧ス繝ォ繧帝哩縺倥k
CLOSE [繧ォ繝シ繧ス繝ォ蜷江
DEALLOCATE [繧ォ繝シ繧ス繝ォ蜷江

CLOSE TestCursor
DEALLOCATE TestCursor

ForLoop

declare
縲cursor cu is select * from 蜿玲ウィm where 蜩∫分='a001';
begin
for cu_rec in cu loop
縲縲ス;
end loop;
end;
螟画焚(cu_rec)縺ッ螳」險荳崎ヲ
繧ォ繝シ繧ス繝ォ縺ョOPEN縲√ョ繝シ繧ソ蜿門セ励∫オゆコ縲,LOSE蜃ヲ逅縺ッ險倩ソー荳崎ヲ
螟画焚縺ョ蝙九ッTYPE縲ヽOWTYPE蝙

DECLARE
縲cur CURSOR FOR SELECT 蛻 FROM 陦ィ;
BEGIN
縲FOR rec IN cur LOOP
縲縲INSERT INTO 陦ィ VALUES ( rec.蛻 );
縲END LOOP;
END

繧ォ繝シ繧ス繝ォ繧丹PEN譎ゅ↓螳夂セゥ

TYPE CURSOR_INFO IS REF CURSOR;
cu CURSOR_INFO;

BEGIN
OPEN cu FOR 'SELECT ス FROM ス';
縲LOOP
縲縲FETCH cu INTO r;
縲縲EXIT WHEN cu%NOTFOUND;
縲縲ス
縲END LOOP;
CLOSE cu;
END

BEGIN
縲FOR rec IN ( SELECT 蛻 FROM 陦ィ ) LOOP
FOR rec IN SELECT 蛻 FROM 陦ィ LOOP
縲縲INSERT INTO 陦ィ VALUES ( rec.蛻 );
縲END LOOP;
END

繧ォ繝シ繧ス繝ォ繧貞虚逧縺ォ螟画峩

IF ( ス ) THEN
縲OPEN cu FOR 'SELECT ス FROM ス';
ELSIF ( ス ) THEN
縲OPEN cu FOR 'SELECT ス FROM ス WHERE ス = :=val1 ' USING 1;
ELSE ( ス ) THEN
縲OPEN cu FOR 'SELECT ス FROM ス WHERE ス = :=val1 AND ス = val2 ' USING 1, '1';
END IF;

LOOP
縲FETCH cu INTO r;
縲EXIT WHEN cu%NOTFOUND;
縲ス
END LOOP;
CLOSE cu;

DECLARE
蠑墓焚繧呈戟縺、
縲cur CURSOR(prm1 INT, prm2 TEXT) FOR SELECT 蛻 FROM 陦ィ WHERE 蛻1 = prm1 AND 蛻2 = prm2;
BEGIN
縲FOR rec IN cur(5, 'aaa') LOOP
縲縲INSERT INTO 陦ィ VALUES ( rec.蛻 );
縲END LOOP;
END

Follow me!