database programming techniques
TRANSCRIPT
![Page 1: Database Programming Techniques](https://reader034.vdocuments.us/reader034/viewer/2022042615/55a6a8851a28abfb6a8b4595/html5/thumbnails/1.jpg)
Database Programming Techniques
CMPS 277
Raji Ghawi
7 April 2015
![Page 2: Database Programming Techniques](https://reader034.vdocuments.us/reader034/viewer/2022042615/55a6a8851a28abfb6a8b4595/html5/thumbnails/2.jpg)
Interaction with Databases
• Interactive interface – SQL commands typed directly into a monitor
– Execute file of commands • @<filename>
• Application programs or database applications– Used as canned transactions by the end users access a
database
– May have Web interface
– Host language: Java, C/C++/C# , …
– Database language: SQL
![Page 3: Database Programming Techniques](https://reader034.vdocuments.us/reader034/viewer/2022042615/55a6a8851a28abfb6a8b4595/html5/thumbnails/3.jpg)
Database Programming Approaches
• Embedded SQL Approach
– Embedded SQL ( C language)
– SQLJ (Java language)
• Library of Function Calls Approach.
– JDBC
– SQL/CLI
• Database Programming Language Approach
– Stored Procedures
![Page 4: Database Programming Techniques](https://reader034.vdocuments.us/reader034/viewer/2022042615/55a6a8851a28abfb6a8b4595/html5/thumbnails/4.jpg)
Database Programming Approaches
• Embedded SQL Approach
– Embedded SQL ( C language)
– SQLJ (Java language)
• Library of Function Calls Approach.
– JDBC
– SQL/CLI
• Database Programming Language Approach
– Stored Procedures
![Page 5: Database Programming Techniques](https://reader034.vdocuments.us/reader034/viewer/2022042615/55a6a8851a28abfb6a8b4595/html5/thumbnails/5.jpg)
JDBCOne API to Access Them All
![Page 6: Database Programming Techniques](https://reader034.vdocuments.us/reader034/viewer/2022042615/55a6a8851a28abfb6a8b4595/html5/thumbnails/6.jpg)
Introduction
• JDBC: Java DataBase Connectivity
• JDBC is a standard interface that lets you access virtually any tabular data source from the Java programming language
– relational databases, spreadsheets, flat files
• The JDBC classes and interfaces are in the java.sqlpackage
![Page 7: Database Programming Techniques](https://reader034.vdocuments.us/reader034/viewer/2022042615/55a6a8851a28abfb6a8b4595/html5/thumbnails/7.jpg)
General Architecture
Java Application or Applet
JDBC Driver Manager
Oracle Driver
MySQLDriver
PostgreSQLDriver
Oracle PostgreSQLMySQL
• The Driver Manager provides a consistent layer between your Java application and back-end database.
![Page 8: Database Programming Techniques](https://reader034.vdocuments.us/reader034/viewer/2022042615/55a6a8851a28abfb6a8b4595/html5/thumbnails/8.jpg)
• Is an interpreter that translates JDBC method calls to vendor-specific database commands
• Implements interfaces in java.sql
• Can also provide a vendor’s extensions to the JDBC standard
DriverJDBC calls
Database commands
Database
A JDBC Driver
![Page 9: Database Programming Techniques](https://reader034.vdocuments.us/reader034/viewer/2022042615/55a6a8851a28abfb6a8b4595/html5/thumbnails/9.jpg)
Query
Close
Connect
Process results
Overview of Querying a Database With JDBC
![Page 10: Database Programming Techniques](https://reader034.vdocuments.us/reader034/viewer/2022042615/55a6a8851a28abfb6a8b4595/html5/thumbnails/10.jpg)
Register the driver
Connect to the database
Stage 1: Connect
Query
Close
Connect
Process results
![Page 11: Database Programming Techniques](https://reader034.vdocuments.us/reader034/viewer/2022042615/55a6a8851a28abfb6a8b4595/html5/thumbnails/11.jpg)
1. Register the driver.
2. Connect to the database.
DriverManager.registerDriver(new org.postgresql.Driver());
Connection conn = DriverManager.getConnection
(URL, userid, password);
Connection conn = DriverManager.getConnection
("jdbc:postgresql://localhost/University",
"xxxx", "xxxx");
How to Make the Connection
![Page 12: Database Programming Techniques](https://reader034.vdocuments.us/reader034/viewer/2022042615/55a6a8851a28abfb6a8b4595/html5/thumbnails/12.jpg)
Using Connection
java.sql.Connection Creating Statement
Transaction Management
Get database metadata
Conneciton related
createStatment()prepareStatment(String)prepareCall(String)
commit()rollback()
getMetaData()
close()isClosed()
![Page 13: Database Programming Techniques](https://reader034.vdocuments.us/reader034/viewer/2022042615/55a6a8851a28abfb6a8b4595/html5/thumbnails/13.jpg)
List of JDBC Drivers
DBMS Driver / URL
PostgreSQLorg.postgresql.Driver
jdbc:postgresql://[host]/[DB]
MySQLcom.mysql.jdbc.Driver
jdbc:mysql://[host]/[DB]
Oracle
oracle.jdbc.driver.OracleDriver
jdbc:oracle:thin:@[host]:[port]:[db]jdbc:oracle:oci:@[host]:[port]:[db]
SQL Servercom.microsoft.sqlserver.jdbc.SQLServerDriver
jdbc:sqlserver://[host];databaseName=[db];
ODBC bridgesun.jdbc.odbc.JdbcOdbcDriver
jdbc:odbc:[db]
![Page 14: Database Programming Techniques](https://reader034.vdocuments.us/reader034/viewer/2022042615/55a6a8851a28abfb6a8b4595/html5/thumbnails/14.jpg)
Demonstration
import java.sql.*;
public class MyDBApp1 {
public static void main(String[] args) {
String url = "jdbc:postgresql://localhost/University";
String username = "xxxx";
String passwd = "xxxx";
try {
Class.forName("org.postgresql.Driver");
Connection connection = DriverManager.getConnection(url, username, passwd);
// do something with connection
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
![Page 15: Database Programming Techniques](https://reader034.vdocuments.us/reader034/viewer/2022042615/55a6a8851a28abfb6a8b4595/html5/thumbnails/15.jpg)
Create a statement
Query the database
Stage 2: Query
Query
Close
Connect
Process results
![Page 16: Database Programming Techniques](https://reader034.vdocuments.us/reader034/viewer/2022042615/55a6a8851a28abfb6a8b4595/html5/thumbnails/16.jpg)
The Statement Object
• A Statement object sends your SQL statement to the database.
• You need an active connection to create a JDBC statement.
• Statement has three methods to execute a SQL statement:– executeQuery() for SELECT statements– executeUpdate() for INSERT, UPDATE, DELETE, or
DDL statements– execute() for either type of statement
![Page 17: Database Programming Techniques](https://reader034.vdocuments.us/reader034/viewer/2022042615/55a6a8851a28abfb6a8b4595/html5/thumbnails/17.jpg)
1. Create an empty statement object.
2. Execute the statement.
Statement stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery(statement);
int count = stmt.executeUpdate(statement);
boolean isquery = stmt.execute(statement);
How to Query the Database
![Page 18: Database Programming Techniques](https://reader034.vdocuments.us/reader034/viewer/2022042615/55a6a8851a28abfb6a8b4595/html5/thumbnails/18.jpg)
Statement stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery
("SELECT fname, lname FROM student");
Statement stmt = conn.createStatement();
int rowcount = stmt.executeUpdate
("DELETE FROM student WHERE studid = '201231521'");
Querying the Database: Examples
• Execute a select statement.
• Execute a delete statement.
![Page 19: Database Programming Techniques](https://reader034.vdocuments.us/reader034/viewer/2022042615/55a6a8851a28abfb6a8b4595/html5/thumbnails/19.jpg)
Step through the results
Assign results to Java variables
Stage 3: Process the Results
Close
Connect
Process results
Query
![Page 20: Database Programming Techniques](https://reader034.vdocuments.us/reader034/viewer/2022042615/55a6a8851a28abfb6a8b4595/html5/thumbnails/20.jpg)
The ResultSet Object
• JDBC returns the results of a query in a ResultSet object.
• A ResultSet maintains a cursor pointing to its current row of data.
• Use next() to step through the result set row by row.
• getString(), getInt(), and so on assign each value to a Java variable.
![Page 21: Database Programming Techniques](https://reader034.vdocuments.us/reader034/viewer/2022042615/55a6a8851a28abfb6a8b4595/html5/thumbnails/21.jpg)
1. Step through the result set.
2. Use getXXX() to get each column value.
while (rset.next()) { … }
String val =
rset.getString(colname);
while (rset.next()) {
String fname = rset.getString("fname");
String email = rset.getString("email");
// Process or display the data
}
String val =
rset.getString(colIndex);
How to Process the Results
![Page 22: Database Programming Techniques](https://reader034.vdocuments.us/reader034/viewer/2022042615/55a6a8851a28abfb6a8b4595/html5/thumbnails/22.jpg)
while (rset.next()) {String email = rset.getString("email");if (rset.wasNull() {
… // Handle null value}
…}
How to Handle SQL Null Values
• Java primitive types cannot have null values.
• Do not use a primitive type when your query might return a SQL null.
• Use ResultSet.wasNull() to determine whether a column has a null value.
![Page 23: Database Programming Techniques](https://reader034.vdocuments.us/reader034/viewer/2022042615/55a6a8851a28abfb6a8b4595/html5/thumbnails/23.jpg)
Mapping Database Types to Java Types
• ResultSet maps database types to Java types.
ResultSet rset = stmt.executeQuery("SELECT id, birth_date, name FROM student");
int id = rset.getInt(1);Date birthdate = rset.getDate(2); String name = rset.getString(3);
Column Name Type
id INTEGER
birthdate DATE
name VARCHAR
![Page 24: Database Programming Techniques](https://reader034.vdocuments.us/reader034/viewer/2022042615/55a6a8851a28abfb6a8b4595/html5/thumbnails/24.jpg)
JDBC Type Java Type
BIT boolean
TINYINT byte
SMALLINT short
INTEGER int
BIGINT long
REAL float
FLOATDOUBLE
double
BINARYVARBINARYLONGVARBINARY
byte[]
CHARVARCHARLONGVARCHAR
String
Mapping Database Types to Java Types
JDBC Type Java Type
NUMERICDECIMAL
BigDecimal
DATE java.sql.Date
TIMETIMESTAMP
java.sql.Timestamp
CLOB Clob*
BLOB Blob*
ARRAY Array*
STRUCT Struct*
REF Ref*
JAVA_OBJECT underlying Java class
* SQL3 data type supported in JDBC 2.0
![Page 25: Database Programming Techniques](https://reader034.vdocuments.us/reader034/viewer/2022042615/55a6a8851a28abfb6a8b4595/html5/thumbnails/25.jpg)
Close the result set
Close the statement
Close the connection
Stage 4: Close
Close
Connect
Query
Process Results
![Page 26: Database Programming Techniques](https://reader034.vdocuments.us/reader034/viewer/2022042615/55a6a8851a28abfb6a8b4595/html5/thumbnails/26.jpg)
1. Close the ResultSet object.
2. Close the Statement object.
3. Close the connection.
rset.close();
stmt.close();
conn.close();
How to Close the Connection
![Page 27: Database Programming Techniques](https://reader034.vdocuments.us/reader034/viewer/2022042615/55a6a8851a28abfb6a8b4595/html5/thumbnails/27.jpg)
Demonstration
public static void main(String[] args) {
String url = "jdbc:postgresql://localhost/University";
String username = "xxxx";
String passwd = "xxxx";
try {
Class.forName("org.postgresql.Driver");
Connection connection = DriverManager.getConnection(url, username, passwd);
Statement stmt = connection.createStatement();
String sql2 = "SELECT * FROM student";
ResultSet rs = stmt.executeQuery(sql2);
while (rs.next()) {
int id = rs.getInt("studId");
String fname = rs.getString("fname");
String lname = rs.getString("lname");
String email = rs.getString("email");
String major = rs.getString("major");
System.out.printf("%-12d %-10s %-10s %-25s %-6s \n", id, fname, lname, email, major);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
![Page 28: Database Programming Techniques](https://reader034.vdocuments.us/reader034/viewer/2022042615/55a6a8851a28abfb6a8b4595/html5/thumbnails/28.jpg)
Demonstration
create statement object
SQL query
execute a query, returns a ResultSet object
loop over results
fetch results from ResultSet object into Java variables
format and print results
close the result set and the statement
![Page 29: Database Programming Techniques](https://reader034.vdocuments.us/reader034/viewer/2022042615/55a6a8851a28abfb6a8b4595/html5/thumbnails/29.jpg)
Demonstration
![Page 30: Database Programming Techniques](https://reader034.vdocuments.us/reader034/viewer/2022042615/55a6a8851a28abfb6a8b4595/html5/thumbnails/30.jpg)
Improve the structure of your program
make a global connection
move connecting code into a separate method
call your connect method from the constructor
![Page 31: Database Programming Techniques](https://reader034.vdocuments.us/reader034/viewer/2022042615/55a6a8851a28abfb6a8b4595/html5/thumbnails/31.jpg)
Improve the structure of your program
separate database operations into
methods
![Page 32: Database Programming Techniques](https://reader034.vdocuments.us/reader034/viewer/2022042615/55a6a8851a28abfb6a8b4595/html5/thumbnails/32.jpg)
Improve the structure of your program
call operational methods from main() as needed
![Page 33: Database Programming Techniques](https://reader034.vdocuments.us/reader034/viewer/2022042615/55a6a8851a28abfb6a8b4595/html5/thumbnails/33.jpg)
Improve your program much more
• Create a pretty Graphical User Interface
– Swing: JPanel, JTable, …
• Make Java classes for your database entities
• Use suitable Design Pattern
– Singleton pattern
![Page 34: Database Programming Techniques](https://reader034.vdocuments.us/reader034/viewer/2022042615/55a6a8851a28abfb6a8b4595/html5/thumbnails/34.jpg)
Improve your program much more
![Page 35: Database Programming Techniques](https://reader034.vdocuments.us/reader034/viewer/2022042615/55a6a8851a28abfb6a8b4595/html5/thumbnails/35.jpg)
Improve your program much more
![Page 36: Database Programming Techniques](https://reader034.vdocuments.us/reader034/viewer/2022042615/55a6a8851a28abfb6a8b4595/html5/thumbnails/36.jpg)
Security Issue
SQL Injection
![Page 37: Database Programming Techniques](https://reader034.vdocuments.us/reader034/viewer/2022042615/55a6a8851a28abfb6a8b4595/html5/thumbnails/37.jpg)
Prepared Statements
• A PreparedStatement object holds precompiled SQL statements.
• Use this object for statements you want to execute more than once.
• A prepared statement can contain variables that you supply each time you execute the statement.
![Page 38: Database Programming Techniques](https://reader034.vdocuments.us/reader034/viewer/2022042615/55a6a8851a28abfb6a8b4595/html5/thumbnails/38.jpg)
How to Create a Prepared Statement
1.Register the driver and create the database connection.
2.Create the prepared statement, identifying variables with a question mark (?).
PreparedStatement pstmt =conn.prepareStatement("UPDATE studentSET email = ? WHERE studID = ?");
PreparedStatement pstmt =conn.prepareStatement("SELECT deptName FROMdepartment WHERE deptCode = ?");
![Page 39: Database Programming Techniques](https://reader034.vdocuments.us/reader034/viewer/2022042615/55a6a8851a28abfb6a8b4595/html5/thumbnails/39.jpg)
How to Execute a Prepared Statement
1. Supply values for the variables.
2. Execute the statement.
pstmt.setXXX(index, value);
pstmt.executeQuery();
pstmt.executeUpdate();
PreparedStatement pstmt =conn.prepareStatement("UPDATE studentSET email = ? WHERE studID = ?");
pstmt.setString(1, "[email protected]");pstmt.setInt(2, studId);pstmt.executeUpdate();
![Page 40: Database Programming Techniques](https://reader034.vdocuments.us/reader034/viewer/2022042615/55a6a8851a28abfb6a8b4595/html5/thumbnails/40.jpg)
Demonstration
SQL query with placeholders
Supply values to the placeholders
Create PreparedStatement object
Execute the prepared update statement
![Page 41: Database Programming Techniques](https://reader034.vdocuments.us/reader034/viewer/2022042615/55a6a8851a28abfb6a8b4595/html5/thumbnails/41.jpg)
Much more still to do
• Transaction Management
• Scrollable Result Set
• Updatable Result Set
• Callable Statements
• Metadata
– DatabaseMetaData
– ResultSetMetaData
![Page 42: Database Programming Techniques](https://reader034.vdocuments.us/reader034/viewer/2022042615/55a6a8851a28abfb6a8b4595/html5/thumbnails/42.jpg)
JDBC Resources
• JDBC Tutorials
– http://www.oracle.com/technetwork/java/index-141229.html
• JDBC Online Courses
– http://www.oracle.com/technetwork/java/index-137757.html
• JDBC Books
– http://www.oracle.com/technetwork/java/index-142052.html
![Page 43: Database Programming Techniques](https://reader034.vdocuments.us/reader034/viewer/2022042615/55a6a8851a28abfb6a8b4595/html5/thumbnails/43.jpg)
Database Programming Approaches
• Embedded SQL Approach
– Embedded SQL ( C language)
– SQLJ (Java language)
• Library of Function Calls Approach.
– JDBC
– SQL/CLI
• Database Programming Language Approach
– Stored Procedures
![Page 44: Database Programming Techniques](https://reader034.vdocuments.us/reader034/viewer/2022042615/55a6a8851a28abfb6a8b4595/html5/thumbnails/44.jpg)
Stored Procedures
Persistent Stored Modules
![Page 45: Database Programming Techniques](https://reader034.vdocuments.us/reader034/viewer/2022042615/55a6a8851a28abfb6a8b4595/html5/thumbnails/45.jpg)
Stored Procedures
Views
Way to register queries inside DBMS
Stored Procedures
Way to register code inside DBMS
![Page 46: Database Programming Techniques](https://reader034.vdocuments.us/reader034/viewer/2022042615/55a6a8851a28abfb6a8b4595/html5/thumbnails/46.jpg)
Stored Procedures
• What is stored procedure?– Piece of code stored inside the DBMS
– SQL allows you to define procedures and functions and store them inside DBMS
• Advantages– Reusability: do not need to write the code again and again
– Programming language-like environment• Assignment, Loop, For, IF statements
– Call it whenever needed• From select statement, another procedure, or another function
![Page 47: Database Programming Techniques](https://reader034.vdocuments.us/reader034/viewer/2022042615/55a6a8851a28abfb6a8b4595/html5/thumbnails/47.jpg)
SQL/PSM
• SQL/Persistent Stored Modules
• ISO standard defining an extension of SQL with a procedural language for use in stored procedures.
PL/SQL Transact-SQL
SQL PLMySQL
stored procedures
PL/pgSQL
![Page 48: Database Programming Techniques](https://reader034.vdocuments.us/reader034/viewer/2022042615/55a6a8851a28abfb6a8b4595/html5/thumbnails/48.jpg)
Stored Procedures in PostgreSQL
• PostgreSQL allows user-defined functions to be written in other languages besides SQL and C:
– PL/pgSQL
– PL/Perl
– PL/Tcl
– PL/Python
![Page 49: Database Programming Techniques](https://reader034.vdocuments.us/reader034/viewer/2022042615/55a6a8851a28abfb6a8b4595/html5/thumbnails/49.jpg)
PL/pgSQL
• PL/pgSQL: Procedural Language postgreSQL
• The design goals of PL/pgSQL were to create a procedural language that
– can be used to create functions and trigger procedures,
– adds control structures to the SQL language,
– can perform complex computations,
– inherits all user-defined types, functions, and operators,
– is easy to use.
![Page 50: Database Programming Techniques](https://reader034.vdocuments.us/reader034/viewer/2022042615/55a6a8851a28abfb6a8b4595/html5/thumbnails/50.jpg)
Structure of PL/pgSQL functions
CREATE [OR REPLACE] FUNCTION <functionName> (<paramList>) RETURNS [<type> | VOID]AS $$[ DECLARE
<declarations> ]BEGIN
<functionBody>;END;$$ LANGUAGE plpgsql;
If exists, then drop it and create it again
A parameter in the paramList is specified as:<name> <mode> <type>
Mode:IN input parameter (default)OUT output parameterINOUT input and output parameter
![Page 51: Database Programming Techniques](https://reader034.vdocuments.us/reader034/viewer/2022042615/55a6a8851a28abfb6a8b4595/html5/thumbnails/51.jpg)
Example 1
CREATE FUNCTION remove_emp(empID INTEGER) RETURNS void AS $$
BEGIN
DELETE FROM employee
WHERE employee.emp_id = empID ;
RETURN ;
END;
$$ LANGUAGE plpgsql;
Function name Parameter list nothing to return
RETURN means exit the function
parameter used inside SQL
SELECT remove_emp(110);
Stored procedures can be called:
• from SQL• from other functions• from applications (JDBC CallableStatement)
![Page 52: Database Programming Techniques](https://reader034.vdocuments.us/reader034/viewer/2022042615/55a6a8851a28abfb6a8b4595/html5/thumbnails/52.jpg)
Declarations
• Examples
quantity INTEGER DEFAULT 32;
url VARCHAR := 'http://mysite.com';
user_id CONSTANT INTEGER := 10;
name [CONSTANT] type [NOT NULL] [{DEFAULT | := } expression];
![Page 53: Database Programming Techniques](https://reader034.vdocuments.us/reader034/viewer/2022042615/55a6a8851a28abfb6a8b4595/html5/thumbnails/53.jpg)
Control Structures (Conditionals)
IF boolean-expression THENstatements
END IF;
IF-THEN
IF boolean-expression THENstatements
ELSEstatements
END IF;
IF-THEN-ELSE
IF boolean-expression THENstatements
[ ELSIF boolean-expression THENstatements
[ ELSIF boolean-expression THENstatements...]]
[ ELSEstatements ]
END IF;
IF-THEN-ELSIF
CASE search-expressionWHEN expression [, expression [ ... ]] THEN
statements[ WHEN expression [, expression [ ... ]] THEN
statements... ]
[ ELSEstatements ]
END CASE;
Simple CASE
CASEWHEN boolean-expression THEN
statements[ WHEN boolean-expression THEN
statements... ]
[ ELSEstatements ]
END CASE;
Searched CASE
![Page 54: Database Programming Techniques](https://reader034.vdocuments.us/reader034/viewer/2022042615/55a6a8851a28abfb6a8b4595/html5/thumbnails/54.jpg)
Control Structures (Loops)
[ <<label>> ]
LOOP
statements
END LOOP [ label ];
LOOP
EXIT [ label ] [ WHEN boolean-expression ];
EXIT
[ <<label>> ]
WHILE boolean-expression LOOP
statements
END LOOP [ label ];
WHILE
CONTINUE [ label ] [ WHEN boolean-expression ];
CONTINUE
FOR (Integer Variant)
[ <<label>> ]
FOR name IN [ REVERSE ] expression .. expression [ BY expression ] LOOP
statements
END LOOP [ label ];
[ <<label>> ]
FOR target IN query LOOP
statements
END LOOP [ label ];
FOR (Query Results Variant)
![Page 55: Database Programming Techniques](https://reader034.vdocuments.us/reader034/viewer/2022042615/55a6a8851a28abfb6a8b4595/html5/thumbnails/55.jpg)
Example 2
• Raise the salary of employees of a given department by a certain ratio.
– dept_id = 1
– ratio = 0.10
• Keep track of salary changes.
emp_id emp_name salary dept_id
101 John 1000 1
102 Jack 1100 1
103 Smith 1200 2
104 Walter 1000 2
105 Mike 1500 2
106 Sarah 1600 3
107 Judie 1250 3
emp_id change_date old_salary new_salary
Employee
Salary_History
![Page 56: Database Programming Techniques](https://reader034.vdocuments.us/reader034/viewer/2022042615/55a6a8851a28abfb6a8b4595/html5/thumbnails/56.jpg)
Example 2CREATE FUNCTION raise_salary(deptID INTEGER, ratio REAL)
RETURNS void AS $$
DECLARE
oldSal REAL; newSal REAL;
curs1 CURSOR FOR
SELECT * FROM employee
WHERE employee.dept_id = deptID;
BEGIN
FOR var IN curs1 LOOP
oldSal := var.salary;
newSal := oldSal + oldSal * ratio;
UPDATE employee
SET salary = newSal
WHERE CURRENT OF curs1;
INSERT INTO salary_history
VALUES(var.emp_id, current_date, oldSal, newSal);
END LOOP;
RETURN;
END ;
$$ LANGUAGE plpgsql;
Use cursor to iterate rows
Define a cursor that references the input parameter
variable assignments
Dec
lara
tio
n
Sect
ion
update the row which the cursor is positioned on
Implicit row-variable
parameter used inside Cursor
Fun
ctio
n B
od
y
![Page 57: Database Programming Techniques](https://reader034.vdocuments.us/reader034/viewer/2022042615/55a6a8851a28abfb6a8b4595/html5/thumbnails/57.jpg)
Runemp_id emp_name salary dept_id
101 John 1000 1
102 Jack 1100 1
103 Smith 1200 2
104 Walter 1000 2
105 Mike 1500 2
106 Sarah 1600 3
107 Judie 1250 3
SELECT raise_salary(1, 0.10);
emp_id change_date old_salary new_salary
emp_id emp_name salary dept_id
101 John 1100 1
102 Jack 1210 1
103 Smith 1200 2
104 Walter 1000 2
105 Mike 1500 2
106 Sarah 1600 3
107 Judie 1250 3
emp_id emp_name salary dept_id
101 John 1100 1
102 Jack 1210 1
103 Smith 1380 2
104 Walter 1150 2
105 Mike 1725 2
106 Sarah 1600 3
107 Judie 1250 3
emp_id change_date old_salary new_salary
101 2015-04-06 1000 1100
102 2015-04-06 1100 1210
emp_id change_date old_salary new_salary
101 2015-04-06 1000 1100
102 2015-04-06 1100 1210
103 2015-04-07 1200 1380
104 2015-04-07 1000 1150
105 2015-04-07 1500 1725
SELECT raise_salary(2, 0.15);
Employee Salary_History
![Page 58: Database Programming Techniques](https://reader034.vdocuments.us/reader034/viewer/2022042615/55a6a8851a28abfb6a8b4595/html5/thumbnails/58.jpg)
Much more still to do
• Exception handling
• Complex data types
– Arrays, Tables
• User defined data types
– (Object-Relational Model)
• Triggers
– (Active Databases)
![Page 59: Database Programming Techniques](https://reader034.vdocuments.us/reader034/viewer/2022042615/55a6a8851a28abfb6a8b4595/html5/thumbnails/59.jpg)
References
• PostgreSQL Documentation
PL/pgSQL - SQL Procedural Language
– http://www.postgresql.org/docs/8.3/static/plpgsql.html
• Fundamentals of Database Systems, Elmasri and Navathe, 6th Edition, Chapter 13
Some slides are adopted from:– www.cse.lehigh.edu/~glennb/oose/ppt/JDBC.ppt
![Page 60: Database Programming Techniques](https://reader034.vdocuments.us/reader034/viewer/2022042615/55a6a8851a28abfb6a8b4595/html5/thumbnails/60.jpg)
Thank you