postgresql 학습하기

2012. 11. 21. 17:55DataBase/Postgresql


pgAdmin...오픈소스 데이타베이스라고 한다. 와우!


프로시저 or 함수를 사용해보자^-^
PL/pgSQL - SQL Procedural Language

36.11. Porting from Oracle PL/SQL 

This section explains differences between PostgreSQL's PL/pgSQL language and Oracle's PL/SQL language, to help developers who port applications from Oracle® to PostgreSQL.

PL/pgSQL is similar to PL/SQL in many aspects. It is a block-structured, imperative language, and all variables have to be declared. Assignments, loops, conditionals are similar. The main differences you should keep in mind when porting from PL/SQL to PL/pgSQL are:

  • There are no default values for parameters in PostgreSQL.

  • You cannot use parameter names that are the same as columns that are referenced in the function. Oracle allows you to do this if you qualify the parameter name using function_name.paramater_name.

  • You can overload function names in PostgreSQL. This is often used to work around the lack of default parameters.

  • No need for cursors in PL/pgSQL, just put the query in the FOR statement. (See Example 36-6.)

  • In PostgreSQL the function body must be written as a string literal. Therefore you need to use dollar quoting or escape single quotes in the function body. See Section 36.2.1.

  • Instead of packages, use schemas to organize your functions into groups.

  • Since there are no packages, there are no package-level variables either. This is somewhat annoying. You can keep per-session state in temporary tables instead.

예제 1 Example 36-5. Porting a Simple Function from PL/SQL to PL/pgSQL

예제 2 Example 36-6. Porting a Function that Creates Another Function from PL/SQL to PL/pgSQL

The following procedure grabs rows from a SELECT statement and builds a large function with the results in IF statements, for the sake of efficiency. Notice particularly the differences in the cursor and the FOR loop.

This is the Oracle version:

Here is how this function would end up in PostgreSQL:


작성한 function - 예외처리는 하지 않았다.(간단한 예제로 만들어보았다.)
만든 Function 실행하기!!
실행은 컴파일 -> 실행이 되겠다.

1) 아래와 같이 작성을 한 뒤에 F5(실행)을 시키면 컴파일이 된다.

2) 아래와 같이 SELECT function 이름(); 을 한 뒤 pgScript 수행(F6)을 누르면 수행이 되어진다.

3) 간단히 select를 날려 확인하면 된다. 

FOR 말고도 FECHE, CURSOR 등 다양한 반복적인 행위를 하는 것들이 있다.
오라클에서는 CURSOR를 많이 썼었는데 여기에서는 FOR를 한번 써보았다.

또한 리포트도 지원을 해준다.

웹에서 확인

또한..콘솔창도 열수가 있다^-^ 
앞으로도 학습 할 것이 많은 것으로 간주가 되어진다.~