Oracle sql updating multiple rows

25-Jun-2019 03:42

SQL, SQL Server, Tutorials, Oracle, PL/SQL, Interview Questions & Answers, Joins, Multiple Choice Questions, Quiz, Stored Procedures, Select, Insert, Update, Delete and other latest topics on SQL, SQL Server and Oracle.REF Cursors A REF CURSOR or cursor variable is just a reference or a handle to a static cursor.But what happens when a cursor variable is defined with a SQL statement but returns unexpected values?Below is an example of defining a cursor variable of one type and the record as another.

The example below attempts to create a cursor %rowtype variable for processing.The PL/SQL compiler will verify that the "FETCH clause" has the correct variable/record for the cursor return type at compile time.Once the cursor type is defined, the actual variable can be defined as the cursor type.SQL declare 2 type auth_cursor is ref cursor; 3 c1 auth_cursor; 4 r_c1 c1%rowtype; 5 begin 6 null; 7 end; 8 / r_c1 c1%rowtype; * ERROR at line 4: ORA-06550: line 4, column 8: PLS-00320: the declaration of the type of this expression is incomplete or malformed ORA-06550: line 4, column 8: PL/SQL: Item ignored However, a strongly typed cursor can use the cursor variable to define the cursor record.SQL declare 2 type auth_cursor is ref cursor return author%rowtype; 3 c1 auth_cursor; 4 r_c1 c1%rowtype; 5 begin 6 null; 7 end; 8 / In this example, the auth_cursor type returns an author%rowtype.

The example below attempts to create a cursor %rowtype variable for processing.The PL/SQL compiler will verify that the "FETCH clause" has the correct variable/record for the cursor return type at compile time.Once the cursor type is defined, the actual variable can be defined as the cursor type.SQL declare 2 type auth_cursor is ref cursor; 3 c1 auth_cursor; 4 r_c1 c1%rowtype; 5 begin 6 null; 7 end; 8 / r_c1 c1%rowtype; * ERROR at line 4: ORA-06550: line 4, column 8: PLS-00320: the declaration of the type of this expression is incomplete or malformed ORA-06550: line 4, column 8: PL/SQL: Item ignored However, a strongly typed cursor can use the cursor variable to define the cursor record.SQL declare 2 type auth_cursor is ref cursor return author%rowtype; 3 c1 auth_cursor; 4 r_c1 c1%rowtype; 5 begin 6 null; 7 end; 8 / In this example, the auth_cursor type returns an author%rowtype.That is because the block successfully compiled and only threw the error when it was executed.