Updating table using cursor in oracle

Rated 3.81/5 based on 925 customer reviews

There are two types of cursors − Implicit cursors are automatically created by Oracle whenever an SQL statement is executed, when there is no explicit cursor for the statement.Programmers cannot control the implicit cursors and the information in it.500000 LOOP SELECT DECODE(k, 'Transducer', 'Rectifier', 'Rectifier', 'Capacitor', 'Capacitor', 'Knob', 'Knob', 'Chassis', 'Chassis', 'Transducer') INTO k FROM dual; INSERT INTO parent VALUES (i, k); END LOOP; COMMIT; END; / SELECT COUNT(*) FROM parent; SELECT COUNT(*) FROM child; CREATE OR REPLACE PROCEDURE fast_way AUTHID CURRENT_USER IS TYPE Part Num IS TABLE OF parent.part_num%TYPE INDEX BY BINARY_INTEGER; pnum_t Part Num; TYPE Part Name IS TABLE OF parent.part_name%TYPE INDEX BY BINARY_INTEGER; pnam_t Part Name; BEGIN SELECT part_num, part_name BULK COLLECT INTO pnum_t, pnam_t FROM parent; FORALL i IN pnum_t. COUNT INSERT INTO airplanes2 VALUES l_data(i); EXIT WHEN l_data.In this chapter, we will discuss the cursors in PL/SQL. A cursor holds the rows (one or more) returned by a SQL statement.Then through grants (execute on the package) and revocations of grants (for example, don't allow DML operations on your tables outside of the package or more specifically the schema that owns the package), you can ensure that the only way those DML statements are executed is through the package API.This ability to construct a "hard shell" around your tables, controlling tightly access to and changes to underlying tables, is one of the most compelling reasons to use PL/SQL (and with other databases, corresponding procedural languages, such as Transact SQL with SQL Server).I would be very careful and wary about using database triggers to "update a table from another table." First, you should review your requirements. If your answer is something like "every time I insert a new row in table X, I need to write a row to an audit/log/history table" then a trigger might be a good solution.

', '

Leave a Reply