Ajust price based on price range : Update Data « PL SQL « Oracle PL / SQL

1. Oracle PL / SQL
2. PL SQL
3. Update Data

Ajust price based on price range

    
SQL>
SQL> CREATE TABLE books (
  2    isbn      CHAR(10) PRIMARY KEY,
  3    category  VARCHAR2(20),
  4    title     VARCHAR2(100),
  5    num_pages NUMBER,
  6    price     NUMBER,
  7    copyright NUMBER(4),
  8    emp1   NUMBER,
  9    emp2   NUMBER,
 10    emp3   NUMBER
 11  );

Table created.

SQL>
SQL> INSERT INTO books (isbn, category, title, num_pages, price, copyright, emp1, emp2, emp3)
  2             VALUES ('1', 'Database', 'Oracle', 563, 39.99, 2009, 1, 2, 3);

1 row created.

SQL> INSERT INTO books (isbn, category, title, num_pages, price, copyright, emp1, emp2)
  2             VALUES ('2', 'Database', 'MySQL', 765, 44.99, 2009, 4, 5);

1 row created.

SQL> INSERT INTO books (isbn, category, title, num_pages, price, copyright, emp1, emp2, emp3)
  2             VALUES ('3', 'Database', 'SQL Server', 404, 39.99, 2001, 6, 7, 8);

1 row created.

SQL> INSERT INTO books (isbn, category, title, num_pages, price, copyright, emp1, emp2, emp3)
  2             VALUES ('4', 'Database', 'SQL', 535, 39.99, 2002, 4, 5, 9);

1 row created.

SQL> INSERT INTO books (isbn, category, title, num_pages, price, copyright, emp1, emp2)
  2             VALUES ('5', 'Database', 'Java', 487, 39.99, 2002, 10, 11);

1 row created.

SQL> INSERT INTO books (isbn, category, title, num_pages, price, copyright, emp1, emp2)
  2             VALUES ('6', 'Database', 'JDBC', 592, 39.99, 2002, 12, 13);

1 row created.

SQL> INSERT INTO books (isbn, category, title, num_pages, price, copyright, emp1, emp2, emp3)
  2             VALUES ('7', 'Database', 'XML', 500, 39.99, 2002, 1, 2, 3);

1 row created.

SQL>
SQL>
SQL> DECLARE
  2     v_price books.price%TYPE;
  3     v_isbn books.isbn%TYPE := '3';
  4  BEGIN
  5     SELECT price INTO v_price FROM books WHERE isbn = v_isbn;
  6
  7     DBMS_OUTPUT.PUT_LINE('Starting price: '||v_price);
  8
  9     IF v_price < 40
 10     THEN
 11        DBMS_OUTPUT.PUT_LINE('This book is already discounted');
 12     ELSIF v_price BETWEEN 40 AND 50
 13     THEN
 14        v_price := v_price - (v_price * .10);
 15        UPDATE books SET price = v_price WHERE isbn = v_isbn;
 16     ELSIF v_price > 50
 17     THEN
 18        v_price := v_price - (v_price * .10);
 19        UPDATE books SET price = v_price WHERE isbn = v_isbn;
 20     END IF;
 21
 22     DBMS_OUTPUT.PUT_LINE('Ending price: '||v_price);
 23
 24     ROLLBACK;
 25  EXCEPTION
 26     WHEN OTHERS
 27     THEN
 28        DBMS_OUTPUT.PUT_LINE(SQLERRM);
 29        ROLLBACK;
 30  END;
 31  /
Starting price: 39.99
This book is already discounted
Ending price: 39.99

PL/SQL procedure successfully completed.

SQL> drop table books;

Table dropped.

SQL>

   
    
    
    
  

Related examples in the same category