1 / 32

Stored Procedure used in PosgreSQL

Stored Procedure used in PosgreSQL. Professor: Dr. Shu-Ching Chen TA: Hsin -Yu Ha. What are stored procedures. A subroutine available to applications that access a relational database system. PL/ pgSQL : A loadable procedural language. Creates functions and trigger procedures

carnig
Télécharger la présentation

Stored Procedure used in PosgreSQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

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

  2. What are stored procedures • 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

  3. Why do we need stored procedure • Reduce roundtrips across the network • Can make security easier to manage • Are precompiled Internet Wait, receive, process/compute One Query Database Server

  4. Structure of PL/pgSQL

  5. Declarations (1) • Declaring PL/pgSQL variable

  6. Declarations (2) • Declaring PL/pgSQL variable and assigning values

  7. Declarations (3) • Declaring Function Parameters (1) directly give a name to the parameter in the command (2) name ALIAS FOR $n;

  8. Declarations (4) • Directly using argument variables

  9. Declarations (5) • Attributes • %TYPE attribute

  10. Declarations (6) • Attributes • %ROWTYPE attribute

  11. Comment syntax • Single-line comments • Block comments

  12. Basic Statements (1) • Assignment • Executing a Command with NO RESULT – PERFORM

  13. Basic Statements (2) • Executing a Command with a Single-row result

  14. Basic Statements (3) • Example

  15. Basic Statements (4)

  16. Basic Statements (5) • FOUND – Boolean variable

  17. Control Structures(1) • RETURN expression

  18. Control Structures(2) • IF statements • IF … THEN • IF … THEN … ELSE • IF … THEN … ELSIF … THEN … ELSE

  19. Control Structures(3) • CASE statements • CASE … WHEN … THEN … ELSE … END CASE • CASE WHEN … THEN … ELSE … END CASE

  20. Control Structures(4) • LOOP • EXIT

  21. Control Structures(5) • CONTINUE • WHILE

  22. Control Structures(6) • FOR (Integer Variant)

  23. Control Structures(7) • FOR (Looping through query results)

  24. Control Structures(8) • Trapping Errors • http://www.postgresql.org/docs/9.1/static/errcodes-appendix.html#ERRCODES-TABLE

  25. Cursors (1) • Declaring Cursor Variables • OPEN FOR query

  26. Cursors (2) NEXT PRIOR FIRST LAST ABSOLUTE count RELATIVE count FORWARD BACKWORD • Using Cursors • FETCH • MOVE

  27. Cursors (3) • Using Cursors • CLOSE • Returning Cursor

  28. Cursors (4) • Looping Through a Cursor’s Result

  29. Errors and Messages • RAISE • Example

  30. 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

  31. Stored Procedure in PgAdmin 2 3 1

  32. Stored Procedure in PgAdmin

More Related