Stored Procedure used in Posgre SQL Professor Dr

Stored Procedure used in Posgre. SQL Professor: Dr. Shu-Ching Chen TA: Hsin-Yu Ha

What are stored procedure �A subroutine available to applications that access a relational database system. �PL/pg. SQL : 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 Internet Database Server Wait, receive, process/compute �Reduce roundtrips across the network �Can make security easier to manage �Are precompiled

Structure of PL/pg. SQL

Declarations (1) �Declaring PL/pg. SQL variable

Declarations (2) �Declaring PL/pg. SQL variable and assigning values

Declarations (3) �Declaring Function Parameters by using Alias �(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 … 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 �Postgre. SQL Manuals Postgre. SQL 9. 1 �http: //www. postgresql. org/docs/9. 1/static/index. html �Practical Postgre. SQL �http: //www. faqs. org/docs/ppbook/c 19610. htm