CHAPTER 9 Procedural Language SQL and Advanced SQL

INDEX 9. 4 SQL FUNCTIONS • 9. 4. 1 Date and Time Functions •

9. 4. 1 日付/時刻関数 (Date and Time Functions) EMPLOYEEテーブル(P. 503の表を改変) NUM LNAME FNAME DOB

MS Access と SQL Serverの日付/時刻関数 • YEAR（date）：指定されたdateの年を返す。４文字の数字。 Ex) 1966年生まれの従業員のリストアップ。 NUMSELECT LNAME, FNAME,

MS Access と SQL Serverの日付/時刻関数 • MONTH（date）：指定されたdateの月を返す。２文字の数字。 Ex) 11月生まれの従業員のリストアップ。 NUM 100 101

MS Access と SQL Serverの日付/時刻関数 • DAY（date）：指定されたdateの日付を返す。 Ex) 14日生まれの従業員のリストアップ。 NUM 100 101 102

MS Access と SQL Serverの日付/時刻関数 • DATEADD（datepart, number, date）：datepartに指定された日付要素に関してnumberの値を dateに加算する。SQL Server限定。 •

MS Access と SQL Serverの日付/時刻関数 • DATEDIFF（datepart, startdate, enddate）：datepartに指定した日付要素に関して、startdateと enddateの差分を返す。SQL Server限定。 Ex)

ORACLEの日付/時刻関数 • TO_CHAR（data, fmt） : dataをfmtの日付書式の文字列に書き換え、返す。 Ex) TO_CHAR（‘ 2013 -05 -10’ , YYYY/MM/DD） →

ORACLEの日付/時刻関数 • SYSDATE: 現在の日にちを返す。 • ADD_MONTHS（date, n）: dateにnの月数を加算する。 Ex) ADD_MONTHS（’ 2013 -05 -10 12:

MYSQLの日付/時刻関数 • DATE_ADD（date, INTERVAL day DAY） : dateにday分加算する。 MYSQLはORACLE が開発するDBMS の実装の１つ EX) DATE_ADD（’ 2013

Oracleの数値関数 • • • ABS(number): 数字の絶対値を返す。 ROUND(number, p): numberの桁pでの四捨五入 TRUNC(number, p): numberの桁pでの切り捨て CEIL(number): number以上で最小の整数値を返す。

9. 4. 3 文字関数（String Functions） • 文字操作はプログラミングで最も多く使用されている。 • この節で紹介されてる関数は以下のとおり。 • • || （Oracle限定）

文字関数（|| と＋） • || (Oracle 限定) • ＋ (MS AccessとSQL Server限定) ：２つの異なるデータを連結させて、１つの列を返す。

文字関数（|| と＋） NUM LNAME FNAME DOB DOH 100 Kolmycz FULLNAMEGeorge 15 -Jun-1942

文字関数（LENGTH と LEN ） • LENGTH（string）（Oracle限定） • LEN（string）（SQL Server限定） : stringの文字列の長さを返す。

変換関数（数字から文字への変換） • TO_CHAR（number, fmt）: numberをfmt形式に変換し、返す。（Oracle限定） • CAST（numeric AS varchar(length)） • CONVERT（varchar(length), numeric）

変換関数（日付から文字への変換） • TO_CHAR（date, fmt） : date を fmt 形式に変換する。（Oracle限定） • CAST（date AS

変換関数（条件式による変換） • CASE （SQL Server限定） SYNTAX: CASE WHEN condition THEN value 1 ELSE

9. 5 ORACLE SEQUENCES （オラクルシーケンス） CREATE SEQUENCE name [START WITH CREATE SEQUENCE 補足のSYNTAX n][INCREMENT

9. 6 UPDATABLE VIEWS （更新可能なビュー）商品ID 商品詳細在庫(QOH) A 123 SCREWS 67 BX 34

9. 6 UPDATABLE VIEWS （更新可能なビュー）例）PRODMASTERの在庫状況を更新したい • まずビューの生成 CREATE VIEW PSVUPD AS ( SELECT

9. 6 UPDATABLE VIEWS （更新可能なビュー） • 更新と表示 UPDATE PSVUPD SET QOH = QOH –

9. 7 PROCEDURAL SQL（手続き型SQL） EX）ループを使った例 DECLARE W_P 1 NUMBER(3) : = 0; W_P

9. 7 PROCEDURAL SQL（手続き型SQL）出力結果 There are 5 Products with price between 0 and

9. 7. 1 トリガー（Trigger） PRODUCT テーブル P_COD E P_INDA TE P_QOH P_MIN P_PRIC

9. 7. 1 トリガー（Trigger） • オラクルにおいてのトリガーの生成方法 CREATE OR REPLACE TRIGGER trigger_name [BEFORE /

9. 7. 1 トリガー（Trigger）例）在庫量が最低量よりも下回ったら、P_REORDERを１にする。 P_COD P_INDA P_QOH P_MIN P_PRIC P_DISCOUNT P_REORDER CREATE

9. 7. 1 トリガー（Trigger）どうしたら０になるのか？例）在庫量が最低量よりも下回ったら、P_REORDERを１にする。 UPDATE PRODUCT SET P_QOH = P_QOH

9. 7. 1 トリガー（Trigger） CREATE OR REPLACE TRIGGER TRG_PRODUCT_REORDER BEFORE INSERT OR UPDATE

9. 7. 2 ストアドプロシージャ（Stored Procedures ）ストアドプロシージャのSYNTAX CREATE OR REPLACE PROCEDURE procedure_name [(argument [IN

9. 7. 2 ストアドプロシージャ（Stored Procedures ）例）最低量よりも在庫量が２倍あるとき、すべての商品を５％引きにする処理を作りたいとする。 CREATE OR REPLACE PROCEDURE PRC_PROD_DISCOUNT

9. 7. 2 ストアドプロシージャ（Stored Procedures ）例）最低量よりも在庫量が２倍あるとき、すべての商品を５％引きにする処理を作りたいとする。 0. 5ずつ増加している。 P_COD E E

9. 7. 2 ストアドプロシージャ（Stored Procedures ） • 割引の割合を引数をとることで決めることもできる。 CREATE OR REPLACE PROCEDURE PRC_PROD_DISCOUNT(WPI IN

9. 7. 3 カーソルを使ったPL/SQL処理（PL/SQL Processing with Cursors ） 74

9. 7. 4 格納関数（PL/SQL Stored Functions） • 格納関数のSYNTAX CREATE FUNCTION function_name (argument IN

9. 8 EMBEDDED SQL（埋め込みSQL） COBOLプログラムでSQLCODEを使った埋め込みSQLの例 EXEC SQL SELECT EMP_LNAME, INTO : W_EMP_FNAME, : W_EMP_LNAME

9. 8 EMBEDDED SQL（埋め込みSQL） • カーソルを使うときはカーソルを開く。 EXEC OPEN PROD_CURSOR; END-EXEC. • カーソル中のデータ行を処理するために FETCH を行う。また、エラーがないか

Slides: 91

Download presentation

CHAPTER 9 Procedural Language SQL and Advanced SQL (後半：P. 452〜 506) B４青木陽介

INDEX 9. 4 SQL FUNCTIONS • 9. 4. 1 Date and Time Functions • 9. 4. 2 Numeric Functions • 9. 4. 3 String Functions • 9. 4. 4 Conversion Functions 9. 5 ORACLE SEQUENCES 9. 6 UPDATABLE VIEWS 9. 7 PROCEDURAL SQL • 9. 7. 1 Triggers • 9. 7. 2 Stored Procedures • 9. 7. 3 PL/SQL Processing with Cursors • 9. 7. 4 PL/SQL Stored Functions 9. 8 EMBEDDED SQL 2

9. 4. 1 日付/時刻関数 (Date and Time Functions) EMPLOYEEテーブル(P. 503の表を改変) NUM LNAME FNAME DOB DOH 100 Kolmycz George 15 -Jun-1942 15 -May-1987 101 Lewis Rhonda 19 -May-1965 25 -Apr-1988 102 Vandam Rhett 14 -Nov-1958 20 -Dec-1992 103 Jones Anne 16 -Oct-1974 28 -Aug-2005 104 Lange John 08 -Nov-1971 20 -Oct-1996 105 Duzak Jeanine 12 -Feb-1968 05 -Jan-1991 106 Diante Jorge 21 -Aug-1974 02 -Jul-1996 107 Wiesenbach Paul 14 -Feb-1966 18 -Nov-1994 DOB: Date of Birthday（誕生日） DOH: Date of hire（雇い日） 6

MS Access と SQL Serverの日付/時刻関数 • YEAR（date）：指定されたdateの年を返す。４文字の数字。 Ex) 1966年生まれの従業員のリストアップ。 NUMSELECT LNAME, FNAME, DOB 100 Kolmycz George 15 -Jun-1942 YEAR(DOB) AS YEAR 101 FROM EMPLOYEE Lewis Rhonda 19 -May-1965 102 Rhett WHEREVandam YEAR(DOB) = 1966 14 -Nov-1958 ; 103 Jones 104 105 LNAME Wiesenbach 106 107 DOH 15 -May-1987 25 -Apr-1988 20 -Dec-1992 Anne 16 -Oct-1974 28 -Aug-2005 Lange FNAME Duzak John DOB Jeanine 08 -Nov-1971 YEAR 12 -Feb-1968 20 -Oct-1996 05 -Jan-1991 Diante. Paul 14 -Feb-1966 Jorge 1966 21 -Aug-1974 02 -Jul-1996 14 -Feb-1966 18 -Nov-1994 Wiesenbach Paul 7

MS Access と SQL Serverの日付/時刻関数 • MONTH（date）：指定されたdateの月を返す。２文字の数字。 Ex) 11月生まれの従業員のリストアップ。 NUM 100 101 102 103 LNAME FNAME DOB SELECT LNAME, FNAME, DOB, Kolmycz George AS MONTH 15 -Jun-1942 MONTH(DOB) 19 -May-1965 FROM Lewis EMPLOYEERhonda Vandam Rhett WHERE MONTH(DOB) = 11; 14 -Nov-1958 104 LNAME 105 Vandam 106 Lange 107 Jones Lange FNAME Duzak Rhett Diante John Wiesenbach Anne John DOB Jeanine 14 -Nov-1958 Jorge 08 -Nov-1971 Paul DOH 15 -May-1987 25 -Apr-1988 20 -Dec-1992 16 -Oct-1974 28 -Aug-2005 08 -Nov-1971 MONTH 12 -Feb-1968 11 21 -Aug-1974 11 14 -Feb-1966 20 -Oct-1996 05 -Jan-1991 02 -Jul-1996 18 -Nov-1994 8

MS Access と SQL Serverの日付/時刻関数 • DAY（date）：指定されたdateの日付を返す。 Ex) 14日生まれの従業員のリストアップ。 NUM 100 101 102 SELECT LNAME, FNAME, DOB, LNAME FNAME DOB DAY(DOB) AS DAY 15 -Jun-1942 Kolmycz George FROM Lewis EMPLOYEERhonda 19 -May-1965 WHERE DAY(DOB)Rhett = 14; 14 -Nov-1958 Vandam 103 104 LNAME Jones FNAME Lange Duzak. Rhett 105 Vandam Wiesenbach Diante. Paul 106 107 Wiesenbach Anne 16 -Oct-1974 DOB DAY John 08 -Nov-1971 14 -Nov-1958 12 -Feb-1968 14 Jeanine 14 -Feb-1966 14 Jorge 21 -Aug-1974 Paul 14 -Feb-1966 DOH 15 -May-1987 25 -Apr-1988 20 -Dec-1992 28 -Aug-2005 20 -Oct-1996 05 -Jan-1991 02 -Jul-1996 18 -Nov-1994 9

MS Access と SQL Serverの日付/時刻関数 • DATEADD（datepart, number, date）：datepartに指定された日付要素に関してnumberの値を dateに加算する。SQL Server限定。 • datepartには、minute, hour, day, week, month, quarter, year が使用できる。 Ex) 2013年 5月10日の１ヶ月後の日付 DATEADD（month, 1, ‘ 2013 -05 -10’ ） 2013 -06 -10 00: 00: 000 11

MS Access と SQL Serverの日付/時刻関数 • DATEDIFF（datepart, startdate, enddate）：datepartに指定した日付要素に関して、startdateと enddateの差分を返す。SQL Server限定。 Ex) 2013年 4月10日から2013年 5月10日までの日数 DATEDIFF（day, ‘ 2013 -04 -10’, ‘ 2013 -05 -10’ ） 30 12

ORACLEの日付/時刻関数 • TO_CHAR（data, fmt） : dataをfmtの日付書式の文字列に書き換え、返す。 Ex) TO_CHAR（‘ 2013 -05 -10’ , YYYY/MM/DD） → 2013/05/10 • TO_DATE（char, fmt）：dateをfmtの日付書式の文字列に書き換え、返す。 Ex) TO_DATE（’ 13 -05 -10’, RR-MM-DD） → 2013 -05 -10 00: 00 13

ORACLEの日付/時刻関数 • SYSDATE: 現在の日にちを返す。 • ADD_MONTHS（date, n）: dateにnの月数を加算する。 Ex) ADD_MONTHS（’ 2013 -05 -10 12: 30; 00’, 1） → 2013 -06 -10 12: 30: 00 • LAST_DAY(date)：dayの日にちの要素を同じ月の月末に変換する。 Ex) LAST_DAY（’ 2013 -05 -10 12: 30; 00’） → 2013 -05 -31 12: 30; 00 15

MYSQLの日付/時刻関数 • DATE_ADD（date, INTERVAL day DAY） : dateにday分加算する。 MYSQLはORACLE が開発するDBMS の実装の１つ EX) DATE_ADD（’ 2013 -05 -10’, INTERVAL 31 day） → 2013 -06 -10 • EXCTRACT(fmt FROM date) : 日付の抽出を行い、その値を返す。 EX) EXTRACT（YEAR FROM ‘ 2013 -05 -13’）→ 2013 16

Oracleの数値関数 • • • ABS(number): 数字の絶対値を返す。 ROUND(number, p): numberの桁pでの四捨五入 TRUNC(number, p): numberの桁pでの切り捨て CEIL(number): number以上で最小の整数値を返す。 FLOOR(number): number以下で最大の整数値を返す。 Ex) • ABS( -7 ) = 7 • ROUND(36, 1) = 40 • TRUNC(36, 1) = 30 • CEIL(7. 2) = 8 • FLOOR(7. 2) = 7 20

9. 4. 3 文字関数（String Functions） • 文字操作はプログラミングで最も多く使用されている。 • この節で紹介されてる関数は以下のとおり。 • • || （Oracle限定）＋ (MS AccessとSQL Server限定) UPPER（string） LOWER(string) SUBSTR(string, p, l) (Oracle限定) SUBSTRING(string, p, l) (SQL Server限定) LENGTH(string) (Oracle限定) LEN(string) (SQL Server限定) 21

文字関数（|| と＋） • || (Oracle 限定) • ＋ (MS AccessとSQL Server限定) ：２つの異なるデータを連結させて、１つの列を返す。 EX) Oracleの場合 SELECT LNAME || ‘ , ‘ || FNAME AS FULLNAME FROM EMPLOYEE; MS AccessとSQL Serverの場合 SELECT LNAME + ‘ , ‘ + FNAME AS FULLNAME FROM EMPLOYEE; 22

文字関数（|| と＋） NUM LNAME FNAME DOB DOH 100 Kolmycz FULLNAMEGeorge 15 -Jun-1942 15 -May-1987 101 Lewis Rhonda Kolmycz , George Vandam Lewis , Rhonda. Rhett 19 -May-1965 25 -Apr-1988 14 -Nov-1958 20 -Dec-1992 Jones Vandam , Rhett. Anne Lange. Jones , Anne John 16 -Oct-1974 28 -Aug-2005 08 -Nov-1971 20 -Oct-1996 12 -Feb-1968 05 -Jan-1991 21 -Aug-1974 02 -Jul-1996 14 -Feb-1966 18 -Nov-1994 102 103 104 105 106 107 Duzak. Lange , John. Jeanine Diante Duzak , Jeanine. Jorge Wiesenbach Paul Diante , Jorge Wiesenbach , Paul 23

文字関数（LENGTH と LEN ） • LENGTH（string）（Oracle限定） • LEN（string）（SQL Server限定） : stringの文字列の長さを返す。 EX) Oracleの場合 LENGTH（toyama） → 6 SQL Serverの場合 LEN（toyama） → 6 26

変換関数（数字から文字への変換） • TO_CHAR（number, fmt）: numberをfmt形式に変換し、返す。（Oracle限定） • CAST（numeric AS varchar(length)） • CONVERT（varchar(length), numeric） : numericをvarchar型に変換し、返す。（SQL Server限定） EX) TO_CHAR（’ 1234567890’ , ‘ 999 G 999’） → 1, 234, 567, 890 9 ：数値 L ：通貨記号 G ：３桁区切り D ：小数点 28

変換関数（日付から文字への変換） • TO_CHAR（date, fmt） : date を fmt 形式に変換する。（Oracle限定） • CAST（date AS varchar(length)）: dateをvarcharに変換する。 • CONVERT（varchar(length), date）：上に同じ。（SQL Server限定） EX) TO_CHAR（’ 15 -Jun-1942’, YYYY/MM/DD） → 1942/06/15 CAST（’ 15 -Jun-1942’ AS varchar(11)） → 15 -Jun-1942 CONVERT（varchar(11), 15 -Jun-1942） → 15 -Jun-1942 29

変換関数（条件式による変換） • CASE （SQL Server限定） SYNTAX: CASE WHEN condition THEN value 1 ELSE value 2 END • DECODE（e, x, y, d）（Oracle限定） SYNTAX: e: 属性 x: 属性の中の値 y: xに対する値 d: デフォルトの値 31

9. 5 ORACLE SEQUENCES （オラクルシーケンス） CREATE SEQUENCE name [START WITH CREATE SEQUENCE 補足のSYNTAX n][INCREMENT BY n][CACHE | NOCACHE] ; START WITH 1 seq 01 INCREMENT BY 1 CREATE SEQUENCE name MAXVALUE 500 • name はシーケンスの名前である。 [START WITH n] MINVALUE 1 • nはpositiveでnegativeであるint型の値である。 [INCREMENT BY n] CYCLE; [MAXVALUE NOMAXVALUE] • STARTn |WITHは最初のシーケンスの値を述べている。 [MINVALUE n | NOMINVALUE] 1から発番して、１ずつ増えていき、 • INCREMENT BY はシーケンスが増えるときの値を決定し [CYCLE | NOCYCLE] 最大 500まで発番されると再び１からている。（デフォルトだと１）発番するシーケンス。 [CACHE | NOCACHE] ; • CACHEやNOCACHE節は、オラクルがあらかじめメモリのなかにシーケンス番号をとっておくかどうかを示している。 38

9. 6 UPDATABLE VIEWS （更新可能なビュー）商品ID 商品詳細在庫(QOH) A 123 SCREWS 67 BX 34 NUTS 37 C 583 BOLTS 50 PRODMASTERテーブル商品ID 月間売り上げ (QTY) A 123 7 BX 34 3 PRODSALESテーブル • 在庫から今月の売り上げ分を差し引いた現在の在庫数に更新したい。 UPDATE SET WHERE PRODMASTER, PRODSALES PRODMASTER. QOH = QOH – QTY PRODMASTER. ID = PRODSALES. ID 44

9. 6 UPDATABLE VIEWS （更新可能なビュー）例）PRODMASTERの在庫状況を更新したい • まずビューの生成 CREATE VIEW PSVUPD AS ( SELECT PRODMASTER. ID, PRODMASTER. QOH, PRODSALES. QTY FROM PRODMASTER, PRODSALES WHERE PRODMASTER. ID = PRODSALES. ID ); 商品ID 在庫（ QOH）売り上げ（QTY） A 123 67 7 BX 34 37 3 ビュー PSVUPD 48

9. 6 UPDATABLE VIEWS （更新可能なビュー） • 更新と表示 UPDATE PSVUPD SET QOH = QOH – QTY; SELECT * FROM PRODMASTER; 商品ID 商品詳細在庫(QOH) A 123 SCREWS 67 A 123 SCREWS 60 BX 34 NUTS 37 BX 34 NUTS 34 C 583 BOLTS 50 更新前更新後 49

9. 7 PROCEDURAL SQL（手続き型SQL） EX）ループを使った例 DECLARE W_P 1 NUMBER(3) : = 0; W_P 2 NUMBER(3) : = 10; W_NUM NUMBER(2) : = 0; BEGIN WHILE W_P 2 < 300 LOOP SELECT COUNT (P_CODE) INTO W_NUM FROM PRODUCT WHERE P_PRICE BETWEEN W_P 1 AND W_P 2; DBMS_OUTPUT. PUT_LINE(‘There are ’ || W_NUM || ‘Products with price between’ || W_P 1 || ‘and’ || W_P 2); W_P 1 : = W_P 2 + 1; W_P 2 : = W_P 2 + 50; END LOOP; END; / 56

9. 7 PROCEDURAL SQL（手続き型SQL）出力結果 There are 5 Products with price between 0 and 10 There are 6 Products with price between 11 and 60 There are 3 Products with price between 61 and 110 There are 1 Products with price between 111 and 160 There are 0 Products with price between 161 and 210 There are 1 Products with price between 211 and 260 例） PRICEが０〜１０までの商品が５つ。 57

9. 7. 1 トリガー（Trigger） PRODUCT テーブル P_COD E P_INDA TE P_QOH P_MIN P_PRIC E P_DISCOUNT P_REORDER 11 QER 03 -NOV 12 8 5 109. 99 0 0 1302/P 2 13 -DEC 13 32 15 14. 99 0. 05 0 23109 20 -JAN 13 23 10 9. 95 0. 1 0 89 -WRE 07 -FEB-Q 13 11 5 256. 99 0. 01 0 P. 474のFIGURE 9. 30を参照 59

9. 7. 1 トリガー（Trigger） • オラクルにおいてのトリガーの生成方法 CREATE OR REPLACE TRIGGER trigger_name [BEFORE / AFTER] [DELETE / INSERT / UPDATE OF column_name] ON table_name [FOR EACH ROW] [DECLARE] [variable_namedata type [: = initial_value] ] BEGIN PL/SQL instructions; END; 60

9. 7. 1 トリガー（Trigger）例）在庫量が最低量よりも下回ったら、P_REORDERを１にする。 P_COD P_INDA P_QOH P_MIN P_PRIC P_DISCOUNT P_REORDER CREATE OR REPLACE TRIGGER TRG_PRODUCT_REORDER EAFTER INSERT TE E OR UPDATE OF P_QOH ON PRODUCT BEGIN 03 -NOV 11 QER 8 10 5 109. 99 0 0 1 UPDATE PRODUCT 12 SET P_REORDER = 1 1313 -DEC- WHERE 32 15 14. 99 0. 05 0 P_QOH <= P_MIN; 02/P 2 13 END; 23109 20 -JAN 23 10 9. 95 0. 1 0 13 UPDATE PRODUCT 89 -WRE 07 -FEB 11 5 256. 99 0. 01 0 SET P_MIN = 10; -Q 13 WHERE P_CODE = ’ 11 QER’ 61

9. 7. 1 トリガー（Trigger）どうしたら０になるのか？例）在庫量が最低量よりも下回ったら、P_REORDERを１にする。 UPDATE PRODUCT SET P_QOH = P_QOH + 10; P_COD P_INDA P_QO P_MI P_PRIC WHERE P_CODE = ’ 11 QER’ E TE H N E P_DISCOUNT P_REORDER 11 QER 03 -NOV -12 18 8 10 109. 99 0 1 1302/P 2 13 -DEC -13 32 15 14. 99 0. 05 0 23109 20 -JAN 13 23 10 9. 95 0. 1 0 8907 -FEBWRE-Q 13 11 5 256. 99 0. 01 0 62

9. 7. 1 トリガー（Trigger） CREATE OR REPLACE TRIGGER TRG_PRODUCT_REORDER BEFORE INSERT OR UPDATE OF P_QOH, P_MIN, ON PRODUCT FOR EACH ROW BEGIN IF : NEW. P_QOH <= : NEW. P_MIN THEN : NEW. P_REORDER : = 1; ELSE : NEW. P_REORDER : = 0; END IF; END; 63

9. 7. 2 ストアドプロシージャ（Stored Procedures ）ストアドプロシージャのSYNTAX CREATE OR REPLACE PROCEDURE procedure_name [(argument [IN / OUT] data-type, …)] [IS / AS] [variable_name data type [: = initial_value] ] BEGIN PL/SQL or SQL statements; END; 67

9. 7. 2 ストアドプロシージャ（Stored Procedures ）例）最低量よりも在庫量が２倍あるとき、すべての商品を５％引きにする処理を作りたいとする。 CREATE OR REPLACE PROCEDURE PRC_PROD_DISCOUNT AS BEGIN UPDATE PRODUCT SET P_DISCOUNT = P_DISCOUNT + 0. 05 WHERE P_QOH >= P_MIN * 2; DBMS_OUTPUT. PUT_LINE (‘* * UPDATE finished * *’); END; 68

9. 7. 2 ストアドプロシージャ（Stored Procedures ）例）最低量よりも在庫量が２倍あるとき、すべての商品を５％引きにする処理を作りたいとする。 0. 5ずつ増加している。 P_COD E E P_INDA TE TE P_QOH P_MIN P_PRIC E E P_DISCOUNT 256. 99 0. 01 0. 06 P_REORDER CREATE OR REPLACE PROCEDURE PRC_PROD_DISCOUNT 11 QER 03 -NOV 8 5 109. 99 0 0 AS BEGIN 12 12 UPDATE PRODUCT 1313 -DEC 32 15 14. 99 0 1313 -DEC 32 15= P_DISCOUNT 14. 99 0. 1 0 SET P_DISCOUNT +0. 05 02/P 2 13 WHERE P_QOH >= P_MIN * 2; 23109 20 -JAN 23 10 9. 95 0. 1 finished * *’); 0 (‘* * UPDATE 23109 DBMS_OUTPUT. PUT_LINE 20 -JAN 23 10 9. 95 0. 15 0 13 13 END; 89 -WRE 07 -FEB-Q 13 11 11 5 5 0 0 69

9. 7. 2 ストアドプロシージャ（Stored Procedures ） • 割引の割合を引数をとることで決めることもできる。 CREATE OR REPLACE PROCEDURE PRC_PROD_DISCOUNT(WPI IN NUMBER) AS BEGIN IF ((WPI <= 0) OR (WPI >= 1)) THEN DBMS_OUTPUT. PUT_LINE(‘Error’); ELSE UPDATE PRODUCT SET P_DISCOUNT = P_DISCOUNT + WPI WHERE P_QOH >= P_MIN * 2; DBMS_OUTPUT. PUT_LINE (‘* * UPDATE finished * *’); END IF; END; 70

9. 7. 3 カーソルを使ったPL/SQL処理（PL/SQL Processing with Cursors ） 74

9. 7. 4 格納関数（PL/SQL Stored Functions） • 格納関数のSYNTAX CREATE FUNCTION function_name (argument IN datatype, …) RETURN data-type [IS] BEGIN PL/SQL statements; RETURN (value or expression); END; 78

9. 8 EMBEDDED SQL（埋め込みSQL） COBOLプログラムでSQLCODEを使った埋め込みSQLの例 EXEC SQL SELECT EMP_LNAME, INTO : W_EMP_FNAME, : W_EMP_LNAME WHERE EMP_NUM = : W_EMP_NUM; END-EXEC. IF SQLCODE = 0 THEN PERFORM DATA_ROUTINE ELSE PERFORM ERROR_ROUTINE END-IF 86

9. 8 EMBEDDED SQL（埋め込みSQL） • カーソルを使うときはカーソルを開く。 EXEC OPEN PROD_CURSOR; END-EXEC. • カーソル中のデータ行を処理するために FETCH を行う。また、エラーがないかをSQLCODE変数を使って確認する。 EXEC FETCH PROD_CURSOR INTO : W_P_CODE, : W_P_DESCRIPT; END-EXEC IF SQLCODE = 0 THEN PERFORM DATA_ROUTINE ELSE PERFORM ERROR_ROUTINE END-IF. 88