Inserting updating records using pl sql cursor
I can even shorten things further and write CREATE PROCEDURE process_employee ( employee_id_in IN omag_employees.employee_id%TYPE) IS l_employee omag_employees%ROWTYPE; BEGIN SELECT * INTO l_employee FROM omag_employees WHERE employee_id = employee_id_in; END; The SELECT * syntax tells Oracle Database to fetch all the columns in the table.
I can also use %ROWTYPE to declare a record that has the same structure as a SELECT statement in a cursor.
If the database administrator changes the maximum length of the last_name column to 200, for instance, this procedure’s status will be changed to INVALID.
When the procedure is recompiled, the compiler will update the definition of the record in this procedure to match the table’s new structure.
It also takes a look at record types, which enable you to work with records that are not necessarily related to a relational table.
PL/SQL makes it very easy to declare records that have the same structure as a table, a view, or the result set of a cursor by offering the %ROWTYPE attribute.
For example, you want to keep track of your books in a library.Greens Technology Adyar provides Oracle SQL, PLSQL, Performance Tuning training in Chennai to professionals and corporates on advanced SQL, PL SQL with XML, Oracle Database 12c Analytic SQL for Data Warehousing and Performance Tuning.A PL/SQL record is a data structure that can hold data items of different kinds.The Oracle PL/SQL language was designed to be a portable, high-performance transaction processing language that is tightly integrated with the SQL language.It is rare, indeed, to find a PL/SQL program that does not either read from or make changes to tables in a database.
This way, when and if the SELECT list of the cursor changes, the number and type of fields in the record will change accordingly and everything will stay in sync.