Introducing Oracle PLSQL

Introducing Oracle PL/SQL

PL/SQL is Oracle s procedural language that allows you to add programming constructs around SQL. PL/SQL is primarily used for adding procedures and functions to a database to implement business logic. PL/SQL contains standard programming constructs such as the following:

• Blocks

• Variable declarations

• Conditionals

• Loops

• Cursors

• The ability to define procedures and functions

The following CREATE PROCEDURE statement defines a procedure named update_product_ price(). The procedure multiplies the price of a product by a factor ”the product ID and the factor are passed as parameters to the procedure. If the specified product doesn t exist, the procedure takes no action; otherwise , it updates the product price by the factor.

Note  

Don t worry too much about the details of the PL/SQL shown in the following listing for now ”you ll learn the details as you progress through this book. I just want you to get a feel for PL/SQL at this stage.

 CREATE OR REPLACE PROCEDURE update_product_price (p_product_id IN products.product_id%TYPE,  p_factor IN NUMBER) AS  product_count INTEGER; BEGIN  -- count the number of products with the  -- supplied product_id (should be 1 if the product exists)  SELECT COUNT(*)  INTO product_count  FROM products  WHERE product_id = p_product_id;  -- if the product exists (product_count = 1) then  -- update that product's price  IF product_count = 1 THEN  UPDATE products  SET price = price * p_factor  WHERE product_id = p_product_id;  COMMIT;  END IF; EXCEPTION  WHEN OTHERS THEN  ROLLBACK; END update_product_price; / 

Exceptions are used to handle errors that occur in PL/SQL code. The EXCEPTION block in the previous example performs a ROLLBACK if any exception is thrown in the code.

You ll learn more about PL/SQL in Chapter 11.