Change price and output the result : Update Data « PL SQL « Oracle PL / SQL

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

Change price and output the result

    
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>
SQL> SET SERVEROUTPUT ON ESCAPE OFF
SQL>
SQL> DECLARE
  2     v_price BOOKS.PRICE%TYPE;
  3  BEGIN
  4
  5     SELECT price INTO v_price FROM books WHERE isbn = '3';
  6
  7     DBMS_OUTPUT.PUT_LINE('The original price for isbn 3 was: '||v_price);
  8
  9     v_price := v_price * .9;
 10
 11     UPDATE books SET price = v_price WHERE isbn = '3';
 12
 13     DBMS_OUTPUT.PUT_LINE(CHR(0));
 14     DBMS_OUTPUT.PUT_LINE('The discounted price for isbn 3 is: '||v_price);
 15
 16  EXCEPTION
 17     WHEN OTHERS
 18        THEN DBMS_OUTPUT.PUT_LINE (SQLERRM);
 19  END;
 20  /
The original price for isbn 3 was: 39.99
The discounted price for isbn 3 is: 35.991

PL/SQL procedure successfully completed.

SQL> drop table books;

Table dropped.

SQL>

   
    
    
    
  

Related examples in the same category