procedural_sql_2 - Procedural SQL Part 2 CURSORs to Stored...

Info iconThis preview shows pages 1–8. Sign up to view the full content.

View Full Document Right Arrow Icon
Procedural SQL Part 2 – CURSORs to Stored Procedures & Functions
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
CURSOR a temporary work area created in the system memory when a SQL statement is executed; stands for CUR rent S et O f R ows Aptly called because it is a means of manipulating the “information within a SQL Statement.” , that is, it contains information on a select statement and the rows of data accessed by it. A cursor can hold more than one row, but can process only one row at a time.
Background image of page 2
Kinds of CURSORs Oracle implements 2 kinds: Explicit - SELECT statements that are DECLAREd explicitly in the declaration section. It uses OPEN, FETCH, and CLOSE in the execution or exception sections. Implicit - Whenever a SQL statement is directly in the execution or exception section of a PL/SQL block; includes INSERT, UPDATE, DELETE, and SELECT INTO statements. Unlike explicit cursors, implicit cursors do not need to be declared, OPENed, FETCHed, or CLOSEd
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Implicit Cursors When you execute DML statements like DELETE, INSERT, UPDATE and SELECT statements, implicit statements are created to process these statements. Oracle provides few attributes called as implicit cursor attributes to check the status of DML operations. The implicit cursor attributes available are: %FOUND, %NOTFOUND, %ROWCOUNT.
Background image of page 4
Implicit Cursor Attributes Attributes Return Value Example %FOUND The return value is TRUE, if the DML statements like INSERT, DELETE and UPDATE affect at least one row and if SELECT ….INTO statement return at least one row. SQL%FOUND The return value is FALSE, if DML statements like INSERT, DELETE and UPDATE do not affect row and if SELECT….INTO statement do not return a row. %NOTFOUND The return value is FALSE, if DML statements like INSERT, DELETE and UPDATE at least one row and if SELECT ….INTO statement return at least one row. SQL%NOTFOUND The return value is TRUE, if a DML statement like INSERT, DELETE and UPDATE do not affect even one row and if SELECT ….INTO statement does not return a row. %ROWCOUNT Return the number of rows affected by the DML operations INSERT, DELETE, UPDATE, SELECT SQL%ROWCOUNT
Background image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Cursor Attribute Sample DECLARE var_rows number(5); BEGIN UPDATE employees SET salary = salary + 1000; IF SQL%NOTFOUND THEN DBMS_OUTPUT.PUT_LINE('None updated'); ELSIF SQL%FOUND THEN var_rows := SQL%ROWCOUNT; DBMS_OUTPUT.PUT_LINE('Salaries for ' || var_rows || 'employees are updated'); END IF; END; --do not commit this example
Background image of page 6
Must be created when executing a SELECT statement that returns more than one row. Even though the cursor stores multiple records, only one record can be processed at a time, which is called as current row. When you fetch a row the current row position
Background image of page 7

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Image of page 8
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

Page1 / 40

procedural_sql_2 - Procedural SQL Part 2 CURSORs to Stored...

This preview shows document pages 1 - 8. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online