1 2 3

candlewhynotData Management

Jan 31, 2013 (4 years and 11 months ago)

377 views

Professor: Dr.
Shu
-
Ching

Chen

TA:
Yimin

Yang

Stored Procedure used in
PosgreSQL

What are stored procedure


A subroutine available to applications that access a relational
database system.


PL/
pgSQL

: A loadable procedural language.


Creates functions and trigger procedures


Adds control structures


Performs complex computation


Inherits all user
-
defined types, functions


Can be defined to be trusted by the server


Easy to use


Why do we need stored procedure





One Query

Wait, receive, process/compute

Database
Server

Internet





Reduce roundtrips across the network


Can make security easier to manage


Are precompiled

Structure of PL/
pgSQL


Declarations (1)


Declaring PL/
pgSQL

variable




Declarations (2)


Declaring PL/
pgSQL

variable and assigning values

Declarations
(3)


Declaring Function Parameters

(1) directly give a name to the parameter in the command




(2) name
ALIAS FOR

$n;

Declarations (4)


Directly using argument variables

Declarations (5)


Attributes


%TYPE attribute

Declarations (6)


Attributes


%ROWTYPE attribute

Comment syntax


Single
-
line comments




Block comments

Basic Statements (1)


Assignment





Executing a Command with NO RESULT


PERFORM




Basic Statements (2)


Executing a Command with a Single
-
row result




Basic Statements (3)


Example










Basic Statements (4)


Basic Statements (5)


FOUND


Boolean variable

Control Structures(1)


RETURN expression

Control Structures(2)


IF statements


IF … THEN


IF … THEN … ELSE


IF … THEN … ELSIF … THEN … ELSE


Control Structures(3)


CASE statements


CASE … WHEN … THEN … ELSE … END CASE


CASE WHEN … THEN … ELSE … END CASE


Control Structures(4)


LOOP




EXIT

Control Structures(5)


CONTINUE






WHILE


Control Structures(6)


FOR (Integer Variant)

Control Structures(7)


FOR (Looping through query results)

Control Structures(8)


Trapping Errors








http://www.postgresql.org/docs/9.1/static/errcodes
-
appendix.html#ERRCODES
-
TABLE

Cursors (1)


Declaring Cursor Variables







OPEN FOR query

Cursors (2)


Using Cursors


FETCH








MOVE

NEXT

PRIOR

FIRST

LAST

ABSOLUTE count

RELATIVE count

FORWARD

BACKWORD


Cursors (3)


Using Cursors


CLOSE



Returning Cursor


Cursors (4)


Looping Through a Cursor’s Result

Errors and Messages


RAISE






Example

Reference


PostgreSQL

Manuals
PostgreSQL

9.1


http://www.postgresql.org/docs/9.1/static/index.html


Practical
PostgreSQL


http://www.faqs.org/docs/ppbook/c19610.htm

Stored Procedure in
PgAdmin

1

2

3

Stored Procedure in
PgAdmin