Views Sequence and Stored Procedure used in Posgre

Views, Sequence, and Stored Procedure used in Posgre. SQL Professor: Dr. Shu-Ching Chen TA: Sheng Guan

What are stored procedures �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 (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) �A pg. SQL cursor allows us to encapsulate a query and process each individual row at a time. We use cursors when we want to divide a large result set into parts and process each part individually.

Cursors (2) �Declaring Cursor Variables v DECLARE my_cursor REFCURSOR; q. Postgre. SQL provides us with a special type called REFCURSOR to declare a cursor variable v cursor_name [ [NO] SCROLL ] CURSOR [( name datatype, name data type, . . . )] FOR query; q. Declare a cursor that bounds to a query

Cursors (3) Examples: DECLARE cur_films CURSOR FOR SELECT * FROM film; cur_films 2 CURSOR (year integer) FOR SELECT * FROM film WHERE release_year = year; The cur_films is a cursor that encapsulates all rows in the film table. The cur_films 2 is a cursor that encapsulates film with a particular release year in the film table.

Cursors (4) �Cursors must be opened before they can be used to query rows. �Opening unbound cursors q OPEN unbound_cursor_variable [ [ NO ] SCROLL ] FOR query; q Because unbound cursor variable is not bounded to any query when we declared it, we have to specify the query when we open it. q Example: OPEN my_cursor FOR SELECT * FROM city WHERE counter = p_country; �Opening bound cursors q OPEN cursor_variable[ (name: =value, . . . )]; q Just need to pass the arguments to the query; q Example: OPEN cur_films(year: =2005);

Cursors (5) �Using Cursors--After opening a cursor, we can NEXT manipulate it using FETCH, MOVE, UPDATE, or PRIOR DELETE statement. FIRST �FETCH LAST ABSOLUTE count RELATIVE count FORWARD BACKWORD �The FETCH statement gets the next row from the cursor and assign it a target_variable �Example: FETCH cur_films INTO row_film; FETCH LAST FROM row_film INTO title, release_year;

Cursors (6) �Moving the cursor MOVE [ direction { FROM | IN } ] cursor_variable; If you want to move the cursor only without retrieving any row, you use the MOVE statement. Examples: MOVE cur_films 2; MOVE LAST FROM cur_films; MOVE RELATIVE -1 FROM cur_films; MOVE FORWARD 3 FROM cur_films;

Cursors (7) �Deleting or updating row Once a cursor is positioned, we can delete or update row identifying by the cursor UPDATE table_name SET column = value, . . . WHERE CURRENT OF cursor_variable; DELETE FROM table_name WHERE CURRENT OF cursor_variable; Example: UPDATE film SET release_year = p_year WHERE CURRENT OF cur_films;

Cursors (8) �To close an opening cursor, we use CLOSE statement as follows: CLOSE cursor_variable; The CLOSE statement releases resources or frees up cursor variable to allow it to be opened again using OPEN statement. An integrated example can be found in : http: //www. postgresqltutorial. com/plpgsql-cursor/ get_film_titles(integer)

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

Stored Procedure in Pg. Admin 3 2 1

Stored Procedure in Pg. Admin