Oracle PL/SQL – CREATEファンクションの例

投稿日: 2019-11-05 2019-11-05
タグ: function, oracle, palindrome, plsql

この記事では、ユーザー定義関数を作成する方法を理解するのに役立ちます。ストアドファンクションまたはユーザーファンクションとも呼ばれます。

ユーザー定義関数はプロシージャーに似ています. 唯一の違い

その関数が常に値を返すかどうかです。

ユーザー定義関数は、SQL式の一部として使用できます.

1.機能 – フォーマットされたアドレスを取得する

この例では、人の名前を指定して書式付きアドレスを取得する関数を作成します。

1.1テーブルと関数を作成します。

-- creating table person__info

CREATE TABLE person__info
（
  PERSON__ID番号（5）主キー、
  FIRST__NAME varchar2（20）、
  LAST__NAME varchar2（20）
）;

--creating table person__address__details

CREATE TABLE person__address__details
(
  PERSON__ADDRESS__ID number(5) primary key,
  PERSON__ID number(5) references person__info(person__id),
  CITY varchar2(15),
  STATE varchar2(15),
  COUNTRY varchar2(20),
  ZIP__CODE varchar2(10)
);

--creating function get__complete__address

ファンクションの作成または置換get__complete__address（in__person__id IN NUMBER）RETURN VARCHAR2 IS person__details VARCHAR2（130）;

ベギン

SELECT 'Name-' || person.first__name || ' '|| person.last__name || '、City-' ||アドレス帳|| '、国家' ||アドレス帳|| '、国 - ' ||住所郡|| '、郵便番号 - ' ||住所.zip__code INTO person__details FROM person__info person、person__address__details住所WHERE人.person__id = in__person__id AND address.person__id = person.person__id;

RETURN（person__details）;

END get__complete__address;

1.2テストのためにデータを挿入する。

INSERT INTO person__info VALUES（10、 'Luis'、 'Thomas'）; INSERT INTO person__info VALUES（20、 'Wang'、 'Moris'）;

INSERT INTO person__address__details VALUES（101,10、 'Vegas'、 'Nevada'、 'US'、 '88901'）;
INSERT INTO person__address__details VALUES（102,20、 'Carson'、 'Nevada'、 'US'、 '90220'）;

1.3データを表示します。

PERSON__INFOから** を選択します。

[cols = “,,”、options = “header”、]| ==========================
| PERSON

ID | FIRST

NAME | LAST__NAME
| 10 |ルイス|トーマス
| 20 |王|モリス
| ==========================

PERSON__ADDRESS__DETAILSから** を選択してください。

[cols = “,,,,,”、options = “header”、]| =========================================== ===========
| PERSON

ADDRESS

ID | PERSON

ID | CITY | STATE | COUNTRY | ZIP

CODE
| 101 | 10 |ラスベガス|ネバダ州| US | 88901
| 102 | 20 |カーソン|ネバダ州| US | 90220
| =========================================== ===========

1.4関数を呼び出す。私たちは関数を多くの方法で呼び出すことができます。ここでまずは
それは
SELECT文`で呼び出すでしょう。それから、私たちはそれを `dbms output.put line

SELECT get__complete__address（10）DUALからの "Person Address";

-- output
-- Name-Luis Thomas, City-Vegas, State-Nevada, Country-US, ZIP Code-88901

SET SERVEROUTPUT ON; EXECUTE dbms__output.put__line（get__complete__address（20））;

-- output
-- Name-Wang Moris, City-Carson, State-Nevada, Country-US, ZIP Code-90220

2. Function – Check Palindrome String

In this example, we will create a function to check whether a given
string is palindrome or not.

パリンドロームは、単語、フレーズ、数字、または他の文字列です
これはマダムやレースカーのように、フォワードと同じものを後方に読み込みます。

https://en.wikipedia.org/wiki/Palindrome
2.1関数の作成。
CREATE OR REPLACE FUNCTION checkForPalindrome（inputString VARCHAR2）RETURN VARCHAR2 IS結果VARCHAR2（75）;戻り値は次のとおりです。 reversedString VARCHAR2（50）; BEGIN SELECT REVERSE（インプットストリング）INTO reversedString FROM DUAL; -  UPPERを使用して大文字と小文字の区別を無視する。

IF UPPER（inputString）= UPPER（reversedString）
      その後
      戻り値（inputString || '回文列である'）;
      END IF;
      RETURN（inputString || '回文ではありません。'）;
      
    END checkForPalindrome;/....

2.2関数を呼び出す。
SELECT checkForPalindrome（ ‘COMPUTER’）FROM DUAL;
– 出力
– コンピュータは回文ではありません。

SELECT checkForPalindrome（ ‘MAdam’）FROM DUAL;
– 出力
– MAdamは回文です。

SELECT checkForPalindrome（ ‘KANAK’）FROM DUAL;

— Output — KANAK IS a palindrome.
=== 3. Function – Calculate income tax

In this example, we will create a function to calculate income tax,
assumed tax rate is 30% of all annual income from salary.

3.1 Create tables and function.
–creating table person

CREATE TABLE person
(
PERSON__ID number(5) primary key,
FULLNAME varchar2(20)
);
–creating table person

salary

details

CREATE TABLE person

salary

details
（
  SALARY

ID number（5）主キー、
  PERSON

ID番号（5）参照人（person__id）、
  SALARY番号（8）、
  MONTH varchar2（9）、
  年番号（4）
）;
–creating function

CREATE OR REPLACE FUNCTION calculate__tax(personId NUMBER)
RETURN NUMBER
IS tax NUMBER(10,2);

BEGIN
tax := 0;
SELECT (sum(salary)** 30)/100 INTO tax FROM person__salary__details WHERE person__id = personId;
RETURN tax;
END calculate__tax;
3.2テスト用のデータを挿入します。
INSERT INTO person VALUES (101,’Mark Phile’);

INSERT INTO person

salary

details VALUES (1,101,15000,’JANUARY’,2016);
INSERT INTO person

salary

details VALUES (2,101,15000,’FEBRUARY’,2016);
INSERT INTO person

salary

details VALUES (3,101,15000,’MARCH’,2016);
INSERT INTO person

salary

details VALUES (4,101,18000,’APRIL’,2016);
INSERT INTO person

salary

details VALUES (5,101,18000,’MAY’,2016);
INSERT INTO person

salary

details VALUES (6,101,18000,’JUNE’,2016);
INSERT INTO person

salary

details VALUES (7,101,18000,’JULY’,2016);
INSERT INTO person

salary

details VALUES (8,101,18000,’AUGUST’,2016);
INSERT INTO person

salary

details VALUES (9,101,18000,’SEPTEMBER’,2016);
INSERT INTO person

salary

details VALUES (10,101,18000,’OCTOBER’,2016);
INSERT INTO person

salary

details VALUES (11,101,18000,’NOVEMBER’,2016);
INSERT INTO person

salary

details VALUES (12,101,18000,’DECEMBER’,2016);
3.3データを表示する。
select ** from PERSON;
[cols=",",options="header",]

| ================= | PERSON__ID | FULLNAME | 101 |マーク・フィール| =================
select ** from PERSON

SALARY

DETAILS;
[cols=",,,,",options="header",]

| ========================= | SALARY__ID | PERSON__ID | SALARY | MONTH |年| 1 | 101 | 15000 | 1月| 2016 | 2 | 101 | 15000 | 2月| 2016 | 3 | 101 | 15000 | 3月| 2016 | 4 | 101 | 18000 | APRIL | 2016 | 5 | 101 | 18000 | MAY | 2016 | 6 | 101 | 18000 | 6月| 2016 | 7 | 101 | 18000 | 7月| 2016 | 8 | 101 | 18000 | 8月| 2016 | 9 | 101 | 18000 | 9月| 2016 | 10 | 101 | 18000 | OCTOBER | 2016 | 11 | 101 | 18000 | NOVEMBER | 2016 | 12 | 101 | 18000 | DECEMBER | 2016 | ========================= ==============

3.4関数を呼び出す。
SELECT person.fullname, sum(sal.salary) AS AnnualSalary, sal.year,calculate

tax(101) AS tax
FROM person,person

salary

details sal
WHERE person.person

id = 101 and sal.year = 2016
GROUP BY person.fullname, sal.year;
出力

[cols=",,,",options="header",]

| ================= | FULLNAME |年次|年|税金|マーク・フィール| 207000 | 2016 | 62100 | ===========================

=== 参考文献

.  https://docs.oracle.com/cd/B19306__01/server.102/b14200/functions231.htm[ユーザー

定義された関数: -  Oracle公式ドキュメント
。 https://docs.oracle.com/cd/B19306__01/server.102/b14200/statements__5009.htm#i2153260[Create

機能: - オラクルの公式ドキュメント]

link://tag/function/[function]link://タグ/oracle/[oracle]link://タグ/palindrome/[palindrome]link://タグ/plsql/[plsql]

getdocs

13036