V. JDBC and PL/SQL

JDBC

Using SQL in Other Programming Languages

일반 목적의 프로그래밍 언어(C++/Java 등)에서 SQL을 함께 써야할 때가 있음

• SQL의 모든 쿼리를 표현할 수 있는 것은 아님
  • 몇몇 쿼리는 일반 프로그래밍 언어에서 더 쉽게 쓸 수 있음
• 비선언적인 행동들은 SQL에서는 수행할 수 없음
  • report 출력
  • 유저와 상호작용
  • GUI에 쿼리 결과를 전송

JDBC and ODBC

• database server와 상호작용하는 프로그램을 위한 API
• 어플리케이션은
  • 데이터베이스 서버에 연결할 수 있고
  • 데이터베이스 서버에 쿼리를 전송할 수 있고
  • 결과의 tuple을 프로그램 변수로 불러올 수 있음

• ODBC (Open Database Connetivity)는 C, C++, C#, Visual Basic등에서 사용
• JDBC (Java Database Connectivity)는 Java와 사용

JDBC

• JDBC는 SQL을 위한 Java API임
  1. Open connection
  2. "Statement" object 생성
  3. "Statement" object를 이용하여 쿼리를 보내고 결과를 받아옴
  4. 에러 핸들링을 위한 exception

public static void JDBCexample(String dbid, String userid,
                               String passwd)
{
  	try {
    	Connection conn = DriverManager.getConnection(
                                  "jdbc:postgresql://localhost/db_name",
                                  userid, passwd);
        Statement stmt = conn.createStatement();
        /*… Do Actual Work … shown in below */
        stmt.close();
        conn.close();
  	}
  	catch (SQLException sqle) {
  		System.out.println("SQLException : " + sqle);
  	}
}

• DB update

try {
    stmt.executeUpdate(
    	"insert into instructor \\
    	values ( '77987', 'Kim', 'Physics', 98000)");
} catch (SQLException sqle) {
	System.out.println("Could not insert tuple." + sqle);
}

• 쿼리를 수행하고 결과를 출력

ResultSet rset = stmt.executeQuery(
					"select dept_name, avg (salary)
					   from instructor
					  group by dept_name");
while (rset.next()) {
	System.out.println(rset.getString("dept_name") + " " + rset.getFloat(2));
}

Warning: Statement is not save

stmt.executeUpdate(“SELECT dept_name FROM students " +
					“WHERE name= ’ " + name + " ’) " );

SQL Injection

• 유저가 Robert’; DROP TABLE students; --를 입력한다면
• 아래 statement를 실행하게 됨

SELECT dept_name FROM students WHERE name= 'Robert';
DROP TABLE students;

Prepared Statemnt

이를 막기 위해 PreparedStatement를 사용할 수 있음. 이안에서는 모든 따옴표가 escape됨.

PreparedStatement pStmt = conn.prepareStatement("insert into instructor values(?,?,?,?)");
pStmt.setString(1, "88877"); /*parameter index, value*/
pStmt.setString(2, "Perry");
pStmt.setString(3, "Finance");
pStmt.setInt(4, 125000);

pStmt.executeUpdate();
pStmt.setString(1, "88878");
pStmt.executeUpdate();

Metadata Features

Resultset의 메타데이터

ResultSet rset;
...
ResultSetMetaData rsmd = rset.getMetaData();
for(int i = 1; i <= rsmd.getColumnCount(); i++)
{
    System.out.println(rsmd.getColumnName(i));
    System.out.println(rsmd.getColumnTypeName(i));
}

DatabaseMetaData

DatabaseMetaData dbmd = conn.getMetaData();
ResultSet rset = dbmd.getColumns(null, "univdb",
									"department", "%");
// Returns: One row for each column;
// row has a number of attributes, e.g., COLUMN_NAME,
// TYPE_NAME, etc

while(rset.next()) {
    System.out.println(rset.getString("COLUMN_NAME"),
    					rset.getString("TYPE_NAME");
}

Transaction Control in JDBC

• 기본적으로 각 SQL 절은 분리된 tranction으로 자동으로 commit됨.
• connection에 대한 자동 커밋을 끌 수 있음

  conn.setAutoCommit(false);

• Transection은 반드시 명시적으로 commit되거나 rollback되어야 함.

  conn.commit(); // or
  conn.rollback();

ODBC

• Open DataBase Connectivity(ODBC) 표준
  • 데이터베이스와 연결
  • 쿼리 전송 및 업데이트
  • 결과 획득

PL/SQL

Procedural Extensions and Stored Procedures

• SQL은 선언적인 언어임
  • 모든 쿼리는 무엇을 원하는지만 말하고, 방법을 말하지 않음
  • 편리하지만 너무 제한적임
  • 가끔 아래 기능들을 필요로함
    • if-then-else
    • for loop
    • while loop
    • etc
• Stored Procedures
  • 데이터베이스 안에서 procedure를 구현하고 저장할 수 있음
  • call statement를 통해서 실행할 수 있음
  • DBMS 안에서 procedure를 실행할 수 있음

Function (PL/pgSQL)

CREATE [OR REPLACE] FUNCTION function_name (arguments)
RETURNS return_datatype AS $$
	DECLARE
		declaration;
		[...]
	BEGIN
		< function_body >
		[...]
		RETURN { variable_name | value }
	END;
	$$
LANGUAGE plpgsql;

• 전체 학생수를 반환하는 함수 선언

CREATE OR REPLACE FUNCTION total_students()
RETURNS integer AS $$
declare
	total integer;
BEGIN
	SELECT count(*) into total FROM STUDENT;
	RETURN total;
END;
$$
LANGUAGE plpgsql;

If-Else

IF <condition> then
	<statements>
ELSEIF <condition> then
	<statements>
ELSE
	<statements>
END IF

Case

CASE <expression>
	WHEN <value> then
		<statements>
	WHEN <value> then
		<statements>
	…
	ELSE
		<statements>
END CASE;

조건문들로도 만들 수 있다.

CASE
	WHEN <condition> then
		<statements>
	WHEN <condition> then
		<statements>
	…
	ELSE
		<statements>
END CASE;

예시:

DO $$
DECLARE
	letter VARCHAR(10);
	grade_value VARCHAR(10);
BEGIN
	FOR letter IN SELECT grade FROM takes
	LOOP
		grade_value := CASE letter
                            WHEN 'A' THEN '4'
                            WHEN 'B' THEN '3'
                            WHEN 'C' THEN '2’
							ELSE 'other'
						END;
		RAISE NOTICE 'Grade: %, Value: %', letter, grade_value;
	END LOOP;
END $$;

Simple Loop and Whilde Loop

LOOP
	-- some computations
	IF count > 0 THEN
		EXIT; -- exit loop
	END IF;
END LOOP;

WHILE var1 > 0 AND var2 > 0 LOOP
	-- some computations here
END LOOP;

For Loop

DO $$
DECLARE i INT;
BEGIN
	FOR i IN 1..10 LOOP
		RAISE NOTICE ‘i = %’, i;
	END LOOP;
END $$;

DO $$
DECLARE s RECORD;
BEGIN
	FOR s IN
		SELECT id, name FROM student
	LOOP
		RAISE NOTICE 'id= %, name = %', s.id, s.name;
	END LOOP;
END $$;

Foreach Loop

CREATE FUNCTION scan_rows(int[])
RETURNS void AS $$
DECLARE
	x int[];
BEGIN
	FOREACH x SLICE 1 IN ARRAY $1
	LOOP
		RAISE NOTICE 'row = %', x;
	END LOOP;
END;
$$ LANGUAGE plpgsql;

Triggers

• trigger는 데이터베이스 변경의 side effect로써 자동으로 실행되는 절을 의미함
  • 계좌 잔액이 $500 밑으로 떨어지면 $10의 초과 인출 수수료 부과
  • 직원의 급여 인상을 5% 이내로 제한

CREATE TRIGGER trigger-name
trigger-time trigger-event ON table-name
FOR EACH ROW
	trigger-action;

• trigger-time $\in$ {BEFORE, AFTER}
• trigger-event $\in$ {INSERT, DELETE, UPDATE}

CREATE OR REPLACE FUNCTION update_budget()
RETURNS TRIGGER AS $$
BEGIN
	IF NEW.dept_name IS NOT NULL THEN
		UPDATE department
		SET budget = budget + NEW.salary
		WHERE dept_name = NEW.dept_name;
	END IF;
	RETURN NEW; -- new refers to the new row inserted
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_budget
AFTER INSERT ON instructor
FOR EACH ROW
	EXECUTE PROCEDURE update_budget();