StoredFunctionのすゝめ

めぐろLT #24 「データベース」
https://meguro-lt.connpass.com/event/343511/

6ページ目のサンプルコード → https://gist.github.com/motooka/4af3345c21025a5a958142ccee84ab04

Transcript

1. CZ
   !U@NPUPPLB TUPSFE
   GVODUJPO
   ͷ͢ʍΊ 
   Ί͙Ζ-5
   
   σʔλϕʔε
2. ୭ʁ w !U@NPUPPLB
   w ॴଐɿͻͱΓ๏ਓ
   w ೥݄
   Ί͙Ζ-5
   
   ʮΈΜͳͷ͘͠͡Γେ৆ʯ
   େ৆Λड৆🥇
   w ςΫχΧϧΤϯδχΞʢσʔλϕʔεʣ
   ˞೥य़औಘ
   w Իָԋ૗ܥ:PV5VCFS
   ʮU@NPUPPLBͷ
   ͻͱΓۚ؅Ξϯαϯϒϧʯ
3. 4UPSFE
   'VODUJPO
   ͱ͸ʁ w σʔλϕʔε্ͷϢʔβఆٛؔ਺
   w 3%#.4͝ͱʹݴޠ΍࢓༷ͳͲ͸େ͖͘ҟͳΔ
   w 4UPSFE
   1SPDFEVSF
   ͱ͍͏΋ͷ΋͋Δ
4. 4UPSFE
   'VODUJPO
   
   Կ͕خ͍͠ͷʁ w ΞϓϦḉͱͷ௨৴ྔ΍ճ਺Λ࡟ݮͰ͖Δ
   ˠ
   ύϑΥʔϚϯεվળ
   w ঢ়گ΍ઃఆʹΑͬͯ͸ɺ͖ͪΜͱΩϟογϡ͞ΕΔ
   ˠ
   ύϑΥʔϚϯεվળ
   w ΞϓϦଆ͕ൃߦ͢Δ42-͕୯७ʹͳΔ
   w ෳ਺ͷΫϥΠΞϯτʢΞϓϦḉʣͰॲཧΛڞ௨Խ
   ʮ͜ͷूܭσʔλग़ͯ͠🙏ʯܥλεΫ΋42-ͰҰൃ✅
5. 4UPSFE
   'VODUJPO
   
   Կ͕೉͍͠ͷʁ w ιʔείʔυͷόʔδϣϯ؅ཧ
   ˠ
   ͓͢͢ΊɿΞϓϦḉͷ
   %#
   NJHSBUJPO
   ͷதʹؚΊͯ͠·͏
   w VOJU
   UFTU
   ˠ
   ͓͢͢ΊɿΞϓϦḉͷ
   VOJU
   UFTU
   ͷதͰςετ͢Δ
   ɹ
   ʢTUPSFE
   GVODUJPO
   ΛݺͿ͚ͩͷίʔυΛ༻ҙͯ͠ɺͦΕΛςετʣ
   w EFQMPZ
   ˠ
   ৽ن࡞੒͸
   %#
   NJHSBUJPO
   ʹೖΕΕ͹ָউɻ
   ɹ
   ߋ৽͸ɺݕ౼ࣄ߲͕୔ࢁ͋ͬͯจࣈ਺ʢ͍΍ɺ͔࣌ؒʜ😇ʣ
   ɹ
   ˠ
   ࠙਌ձͷ࿩୊ʹ🍻
6. .Z42-ͷ৔߹ͷྫɿʮཌӦۀ೔Λฦ͢ʯ https://gist.github.com/motooka/ 4af3345c21025a5a958142ccee84ab04
7. 4UPSFE
   'VODUJPO
   
   ࢖͍Ͳ͜Ζ w %#্ͷσʔλʹབྷΉʢPS
   42-ΫΤϦͷதʣͷɺ
   ෳࡶͳॲཧ͕Կ౓΋͋Δͱ͖
   w ʢࠓͰ͸ϨΞ͚ͩͲʣ8JOEPX
   'VODUJPO
   ͕࢖͑ͳ͍؀ڥͰɺ
   "
   IBT
   NBOZ
   #
   Ͱʮ࠷৽ͷ#ʯΛऔΔɺΈ͍ͨͳॲཧ
   ˠʮ/ ໰୊ʯͷҰ෦ɻʮ"ͷJEΛ༩͑ͨΒ࠷৽ͷ#ͷJEΛฦ͢ʯؔ਺Λ࡞Δ
   w ॲཧܥʹΑͬͯࠩҟ͕ग़ͦ͏ͳܭࢉΛશͯ%#ʹͤͯ҆͞ఆԽΛ໨ࢦ͢
   ˠ
   ුಈখ਺఺਺ͳͲ
   w $)&$,੍໿Λ؆୯ʹॻ͘
   ˞.Z42-͸ແཧ
   ˞1PTUHSF42-Ͱ͸*..65"#-&͚ͩʹ͠Α͏ɻ45"#-&ؔ਺͸ϦετΞ࣌ʹࢮ͵😇
8. 4UPSFE
   'VODUJPO
   
   ద͞ͳ͍ͱ͜Ζ w ࢓༷มߋͷଟ͍৔ॴʢ࢒೦ͳ͕ΒΞϓϦଆͰؤுΖ͏ʣ
   w ʢ.Z42-ͷ৔߹ʣ਺ඵؒͷμ΢ϯλΠϜ͕ڐ͞Εͳ͍γεςϜ
   ɹ㲎
   ࢓༷มߋ࣌͸
   ESPQ
   ͯ͠
   DSFBUF
   ͢Δ͔Β
   ɹ˞
   ࢓༷มߋ࣌͸ผ໊ͷؔ਺ʹ͢Δ͜ͱͰରԠՄೳ
   w ʢ.Z42-ͷ৔߹ʣ$)&$,੍໿ʹ͸࢖͑ͳ͍
   ɹ㲎ESPQͨ͠ͱ͖ͷڍಈ͕ΞϨ͔ͩΒʁʢͨͿΜʣ
   w ෭࡞༻Λ࢓ࠐΈ͍ͨͱ͖ʢQSPDFEVSFʹ͢Δͷ͕Φεεϝʣ
9. 4UPSFE
   'VODUJPO
   
   ࢖༻ྫ w EBUFΛ༩͑ͯɺͦͷ݄ͷ݄຤೔Λฦ͢
   ೿ੜɿͦͷ݄ͷ೔ɺલ݄຤೔ɺͳͲͳͲ
   w ֹ݄՝ۚͷ೔ׂΓܭࢉ
   ର৅೥݄ɺܖ໿೔ɺղ໿೔ OVMMBCMF ɺຬֹ
   Λ༩͑ͯɺֹۚΛฦ͢
   w EBUFΛ༩͑ͯɺফඅ੫཰Λฦ͢ʢUBCMFͰ࣋ͭΑΓ࢖͍қ͍ʂʣ
   ˞ܰݮ੫཰ͷΑ͏ͳG LJOHͳ࢓༷มߋʹ͸஫ҙ⚠
   w ΧλΧφˠ൒֯Ŝū
   ม׵
10. ͦͷଞͷখωλ w .Z42-͸࠷৽൛ͩͱ+BWB4DSJQUͰ
    4UPSFE
    'VODUJPO
    ॻ͚ΔΑ͏ʹͳͬͨ
    ʢ·ͩࢼͯ͠ͳ͍ʣ
    w 1PTUHSF42-͸ੲ͔Βଟ༷ͳݴޠΛ֦ுͰ࢖͑Δ
    ʢࢲ͸͍ͭ΋ඪ४ͷ
    1-QH42-
    Λ࢖ͬͯΔʣ
    w ඪ४ؔ਺ͱಉ༷ʹɺ
    /6--
    Λ༩͑ͨΒ
    /6--
    Λฦ͢
    ͱ޾ͤʹͳΕΔ🥰
    ౰ࣾൺ
    w 1PTUHSF42-ͳΒू໿ؔ਺΋࡞ΕΔͬΆ͍ʢ೉ͦ͠͏͚ͩͲʣ
    IUUQTXXXQPTUHSFTRMPSHEPDTYBHHSIUNM
    w ࠶ؼݺͼग़͠͸μϝͬΆ͍
11. CZ
    !U@NPUPPLB TUPSFE
    GVODUJPO
    ͷ͢ʍΊ 
    Ί͙Ζ-5
    
    σʔλϕʔε ׬