Simple is Beautiful.

1. 프로시저에서 콘솔로그(메세지) 출력

dbms_output.put_line('message');

• 프로시저 내부에서 사용한 변수도 출력 할 수 있다.

1. Object Relational Mapping (ORM) 이란

OOP언어와 데이터를 다루는 RDBMS의 상이한 시스템을 매핑해 데이터와 관련된 OOP프로그래밍을 더 쉽게 만들어주는 기술로 객체와 관계형 테이블을 서로 매핑해준다.

객체를 테이블에 저장할 수 있도록하며 테이블의 데이터로부터 객체를 만들어 낼 수 있도록 한다.

2. 장점

↑ 데이터 계층과 프레젠테이션 계층의 분리.

↑ 재사용, 유지보수, 편리성의 증대.

↑ DBMS에 대한 종속성 감소.

↑ 객체지향적 특징으로 인한 생산성과 가독성의 증대.

↑ 트랜잭션에 락을 제공해준다.

↑ FK가 없더라고 무결성을 보장해 줄 수 있다.

3. 단점

↓ DB를 직접제어하기 힘들다.

↓ ORM으로만 서비스를 구현하기 어렵다.

↓ 설계가 어렵다.

↓ 복잡한 프로젝트일수록 사용 난이도가 상승한다.

↓ 이미 프로시저가 많은 시스템에서 ORM을 도입해도 객체지향적 장점을 살리기 힘들다.

↓ 기술적인 지식 뿐 아니라 업무 및 구조에 대한 전반적인 지식을 모두 갖춰야 능숙히 사용할 수 있다.

1. Stored Procedure란

여러 쿼리문을 하나의 함수처럼 쓸 수 있도록 한 쿼리의 집합.

2. 장점

↑ DB 보안의 향상.

SQL문법의 취약성을 노린 공격이 어려워진다.

↑ 기능의 추상화

단순 쿼리문으로는 불가능한 복잡한 요구사항.을 충족시킬 수 있다.

↑ 절차적 기능의 구현가능.

SQL문에 제어문을 사용할 수 있다.

↑ 코드의 구분.

개발 업무에서의 구분을 용이하게 하며 API처럼 사용할 수 있다.

↑ 트래픽 감소.

한번의 요청으로 여러 쿼리를 실행할 수 있다.

3. 단점

↓ 낮은 처리 성능.

↓ DB서버 자원을 직접 사용해 부하를 초래한다.

↓ 디버그가 어렵다.

↓ 종속성이 생길 우려가 있다.

↓ 프로파일이 어려워 진다.

1. pgcrypto 설치

[root@localhost smoh]# yum install postgresql-contrib

2. 암호화

CREATE EXTENSION pgcrypto;

SELECT CRYPT('12345', GEN_SALT('md5'));

CREATE EXTENSION pgcrypto;

DROP TABLE IF EXISTS members; 

CREATE TABLE members (

  id  TEXT  PRIMARY KEY,

  password TEXT

);

INSERT INTO members(id, password) VALUES ('id01', CRYPT('12345', GEN_SALT('md5')));

SELECT * FROM members;

--SELECT id FROM members WHERE id = 'id01' AND password = CRYPT('12345', password);

1. 새 테이블에 기존 테이블 상속

CREATE TABLE computers (

  model  TEXT PRIMARY KEY,

  price  money

)INHERITS(brands);

2. 전체 예시

DROP TABLE IF EXISTS computers;

DROP TABLE IF EXISTS brands;

CREATE TABLE brands (

  name  VARCHAR(100) PRIMARY KEY,

  nation TEXT

);

CREATE TABLE computers (

  model  TEXT PRIMARY KEY,

  price  money

)INHERITS(brands);

INSERT INTO computers(model, price, name, nation) VALUES('Series9', '1300000', 'Samsung', 'Korea');

INSERT INTO computers(model, price, name, nation) VALUES('Series5', '800000', 'Samsung', 'Korea');

INSERT INTO computers(model, price, name, nation) VALUES('MacBook Pro', '1800000', 'Apple', 'U.S.A.');

SELECT * FROM computers;

3. 참고사항

부모 테이블의 check 제약 조건과 NOT NULL 제약은 자식 테이블에 자동으로 상속된다. 

부모 테이블의 Unique 제약 조건 기본 키, 외래 키 제약 조건은 상속되지 않는다.

SELECT * FROM brands; 를 실행해보자.

1. 트랜잭션 설정.

BEGIN;

UPDATE accounts SET balance = balance - '1000000' WHERE name = 'Alice';

SAVEPOINT save_location;

UPDATE accounts SET balance = balance + '1000000' WHERE name = 'Bob';

ROLLBACK TO save_location;

UPDATE accounts SET balance = balance + '1000000' WHERE name = 'Sam';

COMMIT;

2. 전체 sql

DROP TABLE IF EXISTS accounts;

CREATE TABLE accounts (

  name VARCHAR(20) PRIMARY KEY,

  balance MONEY

);

INSERT INTO accounts VALUES('Alice', '5000000');

INSERT INTO accounts VALUES('Bob', '500000');

INSERT INTO accounts VALUES('Sam', '1000000');

BEGIN;

UPDATE accounts SET balance = balance - '1000000' WHERE name = 'Alice';

SAVEPOINT save_location;

UPDATE accounts SET balance = balance + '1000000' WHERE name = 'Bob';

ROLLBACK TO save_location;

UPDATE accounts SET balance = balance + '1000000' WHERE name = 'Sam';

COMMIT;

SELECT * FROM accounts;

1. 제약조건 추가.

ALTER TABLE cities ADD CONSTRAINT name PRIMARY KEY(name);

ALTER TABLE weather ADD CONSTRAINT city FOREIGN KEY(city) REFERENCES cities(name);

2. 제약조건 삭제.

ALTER TABLE weather DROP CONSTRAINT city;

ALTER TABLE cities DROP CONSTRAINT name;

1. 뷰 삭제

DROP VIEW myview;

2. 뷰 생성

CREATE VIEW myview AS

  SELECT city, temp_lo, temp_hi, prcp, date, location

  FROM weather, cities

  WHERE city = name;

3. 뷰 조회

SELECT * from myview;

1. 테이블 생성

CREATE TABLE weather ( city  varchar(80), temp_lo  int, temp_hi  int, prcp  real, date  date );

CREATE TABLE cities ( name  varchar(80), location  point );

2. 데이터 삽입

INSERT INTO weather VALUES('Seoul', 17, 29, 0.75, '2017-06-15');

INSERT INTO cities VALUES('Seoul', '(37.0, 126.0)');

INSERT INTO weather VALUES('Busan', 22, 31, 0.85, '2017-06-15');

INSERT INTO cities VALUES('Busan', '(35.0, 128.0)');

3. 데이터 조회

SELECT * FROM weather;

SELECT * FROM cities;

4. 데이터 수정

UPDATE weather SET temp_hi = temp_hi +2, temp_lo = temp_lo -2 WHERE date > '2000-01-01';

5. 데이터 삭제

DELETE FROM weather WHERE city ='Busan';

DELETE FROM cities WHERE name = 'Busan';

6.  테이블 삭제

DROP TABLE weather, cities CASCADE;