Introduction to SQL Programing Techniques B 4 hibi

Introduction to SQL Programing Techniques B 4 hibi 1 S

Outline 0. Introduction 1. Database Programming Techniques and Issues 2. Embedded SQL, Dynamic SQL, and SQLJ 3. Database Programming with Function Calls 4. Database Stored Procedures and SQL/PSM 5. Comparing the Three Approaches 3

Outline 0. Introduction 1. Database Programming Techniques and Issues 2. Embedded SQL, Dynamic SQL, and SQLJ 3. Database Programming with Function Calls 4. Database Stored Procedures and SQL/PSM 5. Comparing the Three Approaches 11

2. Embedded SQL, Dynamic SQL, and SQLJ 12

2 -1. Retrieving Single Tuples with Embedded SQL S 親言語の中でSQL文の記述は以下のように埋め込まれる EXEC SQL <SQLの記述> END EXEC ( or ; ) 13

SQLとC言語での型の対応 SQL C言語 INTEGER long SMALLINT short REAL float DOUBLE double CHAR[ i ] char[i+1] VARCHAR[ i ] varchar[i + 1] 16

Example of Embedded SQL 21

23

2 -6. SQLJ: Embedded SQL Commands in Java 28

2 -7. Processing Query Results in SQLJ Using Iterators S 名称付き反復子(named iterator) 29

2 -7. Processing Query Results in SQLJ Using Iterators S 位置反復子(positional iterator) 30

Outline 0. Introduction 1. Database Programming Techniques and Issues 2. Embedded SQL, Dynamic SQL, and SQLJ 3. Database Programming with Function Calls 4. Database Stored Procedures and SQL/PSM 5. Comparing the Three Approaches 31

35

36

38

39

Outline 0. Introduction 1. Database Programming Techniques and Issues 2. Embedded SQL, Dynamic SQL, and SQLJ 3. Database Programming with Function Calls 4. Database Stored Procedures and SQL/PSM 5. Comparing the Three Approaches 40

4. Database Stored Procedures and SQL/PSM l ストアドプロシージャの宣言（Declaring Stored Procedures ） CREATE PROCEDURE <procedure name> (<parameter>) <local declarations> <procedure body>; l 関数の宣言(Declaring Function) CREATE FUNCTION <procedure name> (<parameter>) RETURNS <return type> <local declarations> <function body>; 42

4. Database Stored Procedures and SQL/PSM S 宣言部のパラメータは、SQLのデータ型である、parameter型をも ち、さらにparameter modeを持つ →用途によってIN, OUT INOUT(それぞれinput only, both input and output)を使い分ける output only, S Stored Procedureや関数は、CALL statementで呼び出せる →CALL <procedure or function name>( <argument list>); 43

4. Database Stored Procedures and SQL/PSM S SQL/PSM →永続格納モジュール(stored prucedure)の書き方を指定する 標準SQLで、 ・条件分岐（Conditional Branching Statement）と ・ループ （Looping Statement）がある 44

4. Database Stored Procedures and SQL/PSM S 条件分岐（Conditional Branching Statement） 45

4. Database Stored Procedures and SQL/PSM S ループ（Looping Statement） 46

Outline 0. Introduction 1. Database Programming Techniques and Issues 2. Embedded SQL, Dynamic SQL, and SQLJ 3. Database Programming with Function Calls 4. Database Stored Procedures and SQL/PSM 5. Comparing the Three Approaches 47