1 stick vs 1000 marines how to#
1 stick vs 1000 marines update#
1 stick vs 1000 marines code#

These lines declare new variables based on the new types. The second line declares a new type that matches the product.active_status data type. The INDEX BY PLS_INTEGER will index each record of this type with an integer, so it can be referred to in the procedure by number. The first line declares a new type called product_ids_t which is the same type as the product.id column. These lines declare two new вЂњtypesвЂќ, which are like declaring a custom data type in PL/SQL. TYPE product_ids_t IS TABLE OF product.id%TYPE INDEX BY PLS_INTEGER This is similar to the original procedure. These lines declare the procedure called increasePriceImproved and a parameter of pctToIncrease. CREATE OR REPLACE PROCEDURE increasePriceImproved (

1 stick vs 1000 marines code#

What does this code do? LetвЂ™s take a look. IF (product_active_statuses(ix) = 'active') THENДЄctive_ids(active_ids.COUNT + 1) := product_ids(ix) Product_active_statuses product_active_t Д«ULK COLLECT INTO product_ids, product_active_statuses TYPE product_active_t IS TABLE OF product.active_status%TYPE INDEX BY PLS_INTEGER HereвЂ™s what our procedure would look like with these two features.

FORALL: a feature to let you execute the same DML statement multiple times for different valuesДЄ combination of these should improve our stored procedure.

BULK COLLECT: a clause to let you fetch multiple rows into a collection.

We can use a combination of two features in PL/SQL: The context switch happens on each loop and in each IF statement.

1 stick vs 1000 marines update#

If itвЂ™s active, an update statement is run. In each loop iteration, the active_status is checked. The records are selected and stored in the productRec variable. If this is done many times then it slows down the code. Each time a context switch happens it adds a little overhead. The reason this is not ideal is that it can massively slow down the execution of a procedure. Whenever a procedure or function needs to switch between these two areas, itвЂ™s called a context switch. In the Oracle database, there is an area that runs the SQL code (SELECT, UPDATE, etc) and an area that runs the PL/SQL code (LOOP, IF, etc). The main issue is that it uses something called вЂњcontext switchingвЂќ. This procedure does the job we want, but there are a few issues. CREATE OR REPLACE PROCEDURE increasePrice ( LetвЂ™s take another look at that procedure we wrote. This is expected because product 2 is inactive. We can see that the price has doubled for products 1 and 3, but not 2. HereвЂ™s the function call: CALL increasePrice(100) If we run this procedure with a parameter value of 100, we would expect the prices to double (increase by 100%).

SET p.price = p.price + (p.price * pctToIncrease / 100) IF (productRec.active_status = 'active') THEN HereвЂ™s the stored procedure to make our updates: CREATE OR REPLACE PROCEDURE increasePrice ( This procedure would check if a product is active, and if so, increase the price by the percentage specified. LetвЂ™s say we had to write a procedure to increase the price of a product that met certain criteria. The real table has 1,000 records, but IвЂ™ve only shown a few here. HereвЂ™s the table with some sample data: id

1 stick vs 1000 marines how to#

In this guide, youвЂ™ll learn what these techniques are, see examples of them, and see how they can improve the performance of your code.ДЇor this article, weвЂ™ll use some sample code to demonstrate how to use these two features and what the performance impact is. These techniques are BULK COLLECT and FORALL. If youвЂ™re working with PL/SQL code and processing multiple rows, there are two techniques you can use to improve the performance of your code.