{"id":1378,"date":"2018-09-06T15:22:09","date_gmt":"2018-09-06T06:22:09","guid":{"rendered":"https:\/\/www.oqiita.com\/?p=1378"},"modified":"2018-09-06T15:22:09","modified_gmt":"2018-09-06T06:22:09","slug":"%e3%80%90pl-sql%e3%80%91helloworld%e3%81%a8%e3%81%8b","status":"publish","type":"post","link":"https:\/\/www.oqiita.com\/?p=1378","title":{"rendered":"\u3010PL\/SQL\u3011HelloWorld\u3068\u304b"},"content":{"rendered":"<p>\u6b21\u306e\u73fe\u5834\u3067PL\/SQL\u304c\u5fc5\u8981\u305d\u3046\u306a\u306e\u3067\u52c9\u5f37\u4e2d\u3002<\/p>\n<p><!--more--><\/p>\n<p><\/p>\n<p>\u3068\u308a\u3042\u3048\u305a\u3044\u308d\u3044\u308d\u7a2e\u985e\u304c\u3042\u308b\u3063\u307d\u3044\u3002<br \/>\n\u30fb\u7121\u540d\u30d6\u30ed\u30c3\u30af<br \/>\n\u30fb\u30b9\u30c8\u30a2\u30c9\u30d7\u30ed\u30b7\u30fc\u30b8\u30e3 \u2013 PROCEDURE<br \/>\n\u30fb\u30b9\u30c8\u30a2\u30c9\u30d5\u30a1\u30f3\u30af\u30b7\u30e7\u30f3 \u2013 FUNCTION<br \/>\n\u30fb\u30b9\u30c8\u30a2\u30c9\u30d1\u30c3\u30b1\u30fc\u30b8 \u2013 PACKAGE<\/p>\n<hr>\n<p><!-- \uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d --><\/p>\n<h3 id=\"i-0\">\u30c6\u30fc\u30d6\u30eb\u3068\u304b<\/h3>\n<p>\u904e\u53bb\u306b\u4f5c\u6210\u3057\u305f\u9069\u5f53\u306a\u30c6\u30fc\u30d6\u30eb\u3068\u30c7\u30fc\u30bf\u3067\u3059\u3002\u3002\u3002\u3002<br \/>\n<a href=\"https:\/\/www.oqiita.com\/wp-content\/uploads\/2018\/09\/PLSQL001.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.oqiita.com\/wp-content\/uploads\/2018\/09\/PLSQL001.png\" alt=\"\" width=\"577\" height=\"290\" class=\"alignnone size-full wp-image-1401\" srcset=\"https:\/\/www.oqiita.com\/wp-content\/uploads\/2018\/09\/PLSQL001.png 577w, https:\/\/www.oqiita.com\/wp-content\/uploads\/2018\/09\/PLSQL001-300x151.png 300w\" sizes=\"auto, (max-width: 577px) 100vw, 577px\"><\/a><\/p>\n<hr>\n<p><!-- \uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d --><\/p>\n<h3 id=\"i-1\">PL\/SQL\u6a19\u6e96\u51fa\u529b<\/h3>\n<p>\u3068\u308a\u3042\u3048\u305a\u6a19\u6e96\u51fa\u529b\u3092\u8a31\u53ef\u3002<\/p>\n<pre class=\"line-numbers\"><code class=\"language-plsql\">-- DBMS_OUTPUT \u51fa\u529bON\nSET SERVEROUTPUT ON;\n<\/code><\/pre>\n<p>  <a class=\"reference table\" href=\"https:\/\/www.shift-the-oracle.com\/sqlplus\/system-variable\/serveroutput.html\" target=\"_blank\" rel=\"noopener noreferrer\"><br \/>\n    <span class=\"tbcell refttl\">\u53c2\u8003<\/span><br \/>\n    <span class=\"tbcell refcite\">SET SERVEROUTPUT<span>\u30aa\u30e9\u30af\u30eb\u30fbOracle SQL*Plus \u30ea\u30d5\u30a1\u30ec\u30f3\u30b9<\/span><\/span><br \/>\n  <\/a><\/p>\n<hr>\n<p><!-- \uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d --><\/p>\n<h3 id=\"i-2\">\u7121\u540d\u30d6\u30ed\u30c3\u30af \u305d\u306e1<\/h3>\n<pre class=\"line-numbers\"><code class=\"language-plsql\">BEGIN\n  DBMS_OUTPUT.PUT_LINE('Hello World');\nEND;\n\/<\/code><\/pre>\n<h4 id=\"i-3\">\u5b9f\u884c\u7d50\u679c<\/h4>\n<pre>\nPL\/SQL\u30d7\u30ed\u30b7\u30fc\u30b8\u30e3\u304c\u6b63\u5e38\u306b\u5b8c\u4e86\u3057\u307e\u3057\u305f\u3002\n\nHello World\n<\/pre>\n<h3 id=\"i-4\">\u7121\u540d\u30d6\u30ed\u30c3\u30af \u305d\u306e2<\/h3>\n<pre class=\"line-numbers\"><code class=\"language-plsql\">DECLARE\n  -- \u5909\u6570\u8a2d\u5b9a\n  hoge VARCHAR2(10) := 'test';\n\nBEGIN\n  DBMS_OUTPUT.PUT_LINE('hello world:' || hoge);\n\nEXCEPTION\n  WHEN OTHERS THEN\n  DBMS_OUTPUT.PUT_LINE('Error!!');\n\nEND;\n\/<\/code><\/pre>\n<h4 id=\"i-5\">\u5b9f\u884c\u7d50\u679c<\/h4>\n<pre>\nPL\/SQL\u30d7\u30ed\u30b7\u30fc\u30b8\u30e3\u304c\u6b63\u5e38\u306b\u5b8c\u4e86\u3057\u307e\u3057\u305f\u3002\n\nHello World:test\n<\/pre>\n<hr>\n<p><!-- \uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d --><\/p>\n<h3 id=\"i-6\">\u30b9\u30c8\u30a2\u30c9\u30d7\u30ed\u30b7\u30fc\u30b8\u30e3<\/h3>\n<div class=\"pre_tag\"><span><i class=\"fa fa-code\"><\/i> get_name.sql<\/span><\/p>\n<pre class=\"line-numbers\"><code class=\"language-plsql\">CREATE OR REPLACE PROCEDURE GET_NAME (\n  v_id IN NUMBER,\n  v_name OUT VARCHAR2\n) AS\nBEGIN\n\n  SELECT NAME INTO v_name FROM TRANSACTION1 WHERE ID = v_id;\n\nEND GET_NAME;\n\/<\/code><\/pre>\n<\/div>\n<h4 id=\"i-7\">\u7121\u540d\u30d6\u30ed\u30c3\u30af \u304b\u3089\u547c\u3073\u51fa\u3057<\/h4>\n<pre class=\"line-numbers\"><code class=\"language-plsql\">DECLARE\n\n  v_name varchar2(40);\n\nBEGIN\n\n  GET_NAME(v_id =&gt; 2, v_name =&gt; v_name);\n  DBMS_OUTPUT.PUT_LINE(v_name);\n\nEND;\n\/<\/code><\/pre>\n<pre>\nPL\/SQL\u30d7\u30ed\u30b7\u30fc\u30b8\u30e3\u304c\u6b63\u5e38\u306b\u5b8c\u4e86\u3057\u307e\u3057\u305f\u3002\n\nBBB\n<\/pre>\n<hr>\n<p><!-- \uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d --><\/p>\n<h3 id=\"i-8\">\u30b9\u30c8\u30a2\u30c9\u30d5\u30a1\u30f3\u30af\u30b7\u30e7\u30f3<\/h3>\n<div class=\"pre_tag\"><span><i class=\"fa fa-code\"><\/i> get_name2.sql<\/span><\/p>\n<pre class=\"line-numbers\"><code class=\"language-plsql\">CREATE OR REPLACE FUNCTION GET_NAME2(v_id IN NUMBER)\n  RETURN VARCHAR2 AS\n  v_name VARCHAR2(40);\n\nBEGIN\n\n  SELECT NAME INTO v_name FROM TRANSACTION1 WHERE ID = v_id;\n  RETURN v_name;\n\nEND GET_NAME2;\n\/<\/code><\/pre>\n<\/div>\n<h4 id=\"i-9\">SQL \u304b\u3089\u547c\u3073\u51fa\u3057<\/h4>\n<pre class=\"line-numbers\"><code class=\"language-sql\">SELECT GET_NAME2(3) FROM DUAL;<\/code><\/pre>\n<pre>\nGET_NAME2(3)\n--------------------------------------------------------------------------------\nCCC\n<\/pre>\n<h4 id=\"i-10\">\u7121\u540d\u30d6\u30ed\u30c3\u30af \u304b\u3089\u547c\u3073\u51fa\u3057<\/h4>\n<pre class=\"line-numbers\"><code class=\"language-plsql\">DECLARE\n  v_name VARCHAR2(40);\n\nBEGIN\n\n  v_name := GET_NAME2(v_id =&gt; 3);\n  DBMS_OUTPUT.PUT_LINE(v_name);\n\nEND;\n\/<\/code><\/pre>\n<pre>\nPL\/SQL\u30d7\u30ed\u30b7\u30fc\u30b8\u30e3\u304c\u6b63\u5e38\u306b\u5b8c\u4e86\u3057\u307e\u3057\u305f\u3002\n\nCCC\n<\/pre>\n<hr>\n<p><!-- \uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d --><\/p>\n<h3 id=\"i-11\">IF\u6587<\/h3>\n<pre class=\"line-numbers\"><code class=\"language-plsql\">DECLARE\n  v_id NUMBER;\n\nBEGIN\n\n  v_id := 10;\n  \n  IF v_id BETWEEN 1 AND 5 THEN\n    DBMS_OUTPUT.PUT_LINE('1\uff5e5');\n    \n  ELSIF v_id BETWEEN 6 AND 9 THEN\n    DBMS_OUTPUT.PUT_LINE('6\uff5e9');\n    \n  ELSE\n    DBMS_OUTPUT.PUT_LINE('ELSE\uff1a' || TO_CHAR(v_id));\n  END IF;\n\nEND;\n\/<\/code><\/pre>\n<h4 id=\"i-12\">\u5b9f\u884c\u7d50\u679c<\/h4>\n<pre>\nPL\/SQL\u30d7\u30ed\u30b7\u30fc\u30b8\u30e3\u304c\u6b63\u5e38\u306b\u5b8c\u4e86\u3057\u307e\u3057\u305f\u3002\n\nELSE\uff1a10\n<\/pre>\n<hr>\n<p><!-- \uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d --><\/p>\n<h3 id=\"i-13\">%TYPE \u3068 %ROWTYPE<\/h3>\n<pre class=\"line-numbers\"><code class=\"language-plsql\">DECLARE\n  v_name          TRANSACTION1.NAME%TYPE;\n  v_transaction1  TRANSACTION1%ROWTYPE;\n\nBEGIN\n\n  SELECT NAME INTO v_name FROM TRANSACTION1 WHERE ID = 1;\n  SELECT * INTO v_transaction1 FROM TRANSACTION1 WHERE ID = 2;\n\n  DBMS_OUTPUT.PUT_LINE(v_name);\n  DBMS_OUTPUT.PUT_LINE(v_transaction1.KBN);\n\nEND;\n\/<\/code><\/pre>\n<h4 id=\"i-14\">\u5b9f\u884c\u7d50\u679c<\/h4>\n<pre>\nPL\/SQL\u30d7\u30ed\u30b7\u30fc\u30b8\u30e3\u304c\u6b63\u5e38\u306b\u5b8c\u4e86\u3057\u307e\u3057\u305f\u3002\n\nAAA\nKBN001\n<\/pre>\n<hr>\n<p><!-- \uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d --><\/p>\n<h3 id=\"i-15\">LOOP<\/h3>\n<pre class=\"line-numbers\"><code class=\"language-plsql\">BEGIN\n\n  FOR v_count IN 1..10 LOOP\n    DBMS_OUTPUT.PUT_LINE(v_count);\n  END LOOP;\n\nEND;\n\/<\/code><\/pre>\n<h4 id=\"i-16\">\u5b9f\u884c\u7d50\u679c<\/h4>\n<pre>\nPL\/SQL\u30d7\u30ed\u30b7\u30fc\u30b8\u30e3\u304c\u6b63\u5e38\u306b\u5b8c\u4e86\u3057\u307e\u3057\u305f\u3002\n\n1\n2\n3\n4\n5\n6\n7\n8\n9\n10<\/pre>\n<hr>\n<p><!-- \uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d --><\/p>\n<h3 id=\"i-17\">\u30b9\u30c8\u30a2\u30c9\u30d1\u30c3\u30b1\u30fc\u30b8<\/h3>\n<p>\u30d1\u30c3\u30b1\u30fc\u30b8\u306f\u30d1\u30c3\u30b1\u30fc\u30b8\u4ed5\u69d8\u3068\u30d1\u30c3\u30b1\u30fc\u30b8\u672c\u4f53\u306b\u5206\u3051\u308b\u306e\u304c\u5b9a\u77f3\u306e\u3088\u3046\u3067\u3059\u3002<\/p>\n<div class=\"pre_tag\"><span><i class=\"fa fa-code\"><\/i> PKG_TEST.sql<\/span><\/p>\n<pre class=\"line-numbers\"><code class=\"language-plsql\">CREATE OR REPLACE PACKAGE PKG_TEST\nAS\n  FUNCTION TEST(p_number NUMBER)\n  RETURN NUMBER;\n\t\n  FUNCTION TEST(p_char NVARCHAR2)\n  RETURN NVARCHAR2;\n\t\n  FUNCTION TEST(p_date DATE)\n  RETURN DATE;\nEND;\n\/<\/code><\/pre>\n<\/div>\n<div class=\"pre_tag\"><span><i class=\"fa fa-code\"><\/i> PKG_TEST_BODY.sql<\/span><\/p>\n<pre class=\"line-numbers\"><code class=\"language-plsql\">CREATE OR REPLACE PACKAGE BODY PKG_TEST\nAS\n\n  FUNCTION TEST(p_number in NUMBER)\n  RETURN NUMBER \n  AS\n  BEGIN\n    RETURN p_number + 1;  -- \u52a0\u7b97\n  END;\n\n  FUNCTION TEST(p_char in NVARCHAR2)\n  RETURN NVARCHAR2 \n  AS\n  BEGIN\n    RETURN p_char || ' - TEST';  -- \u7d50\u5408\n  END;\n\n  FUNCTION TEST(p_date in DATE) \n  RETURN DATE \n  AS\n  BEGIN\n    RETURN p_date + 1;  -- \u52a0\u7b97\n  END;\nEND;\n\/<\/code><\/pre>\n<\/div>\n<h4 id=\"i-18\">\u5b9f\u884c\u7d50\u679c<\/h4>\n<pre class=\"line-numbers\"><code class=\"language-sql\">-- SQL\u304b\u3089\u5b9f\u884c\nSELECT PKG_TEST.TEST(100) FROM DUAL;\nSELECT PKG_TEST.TEST('oqiita') FROM DUAL;\nSELECT PKG_TEST.TEST(SYSDATE) FROM DUAL;\n<\/code><\/pre>\n<pre>\nPKG_TEST.TEST(100)\n---------------------------------------\n101\n\nPKG_TEST.TEST('OQIITA')                                                        \n--------------------------------------------------------------------------------\noqiita - TEST                                                                   \n\nPKG_TEST.TEST(SYSDATE)\n----------------------\n2018-09-06 14:26:32   \n<\/pre>\n<hr>\n<p><!-- \uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d\uff1d --><\/p>\n<h3 id=\"i-19\">\u307e\u3068\u3081<\/h3>\n<p>\u3044\u308d\u3044\u308d\u7a2e\u985e\u304c\u3042\u3063\u3066\u30e0\u30ba\u3044\u306a\uff01\uff01<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u6b21\u306e\u73fe\u5834\u3067PL\/SQL\u304c\u5fc5\u8981\u305d\u3046\u306a\u306e\u3067\u52c9\u5f37\u4e2d\u3002<\/p>\n","protected":false},"author":1,"featured_media":733,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[177],"tags":[92,179,178],"class_list":{"0":"post-1378","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","7":"category-pl-sql","8":"tag-oracle","9":"tag-pl","10":"tag-sql","11":"entry"},"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/www.oqiita.com\/wp-content\/uploads\/2018\/05\/sango_db_icatch.png","jetpack_shortlink":"https:\/\/wp.me\/p8QluP-me","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.oqiita.com\/index.php?rest_route=\/wp\/v2\/posts\/1378","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.oqiita.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.oqiita.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.oqiita.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.oqiita.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1378"}],"version-history":[{"count":27,"href":"https:\/\/www.oqiita.com\/index.php?rest_route=\/wp\/v2\/posts\/1378\/revisions"}],"predecessor-version":[{"id":1406,"href":"https:\/\/www.oqiita.com\/index.php?rest_route=\/wp\/v2\/posts\/1378\/revisions\/1406"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.oqiita.com\/index.php?rest_route=\/wp\/v2\/media\/733"}],"wp:attachment":[{"href":"https:\/\/www.oqiita.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1378"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.oqiita.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1378"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.oqiita.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1378"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}