Increase gift price : Procedure Definition « Stored Procedure Function « Oracle PL / SQL






  1. Oracle PL / SQL
  2. Stored Procedure Function
  3. Procedure Definition

Increase gift price

    

SQL>
SQL>
SQL> CREATE TABLE gifts (
  2    gift_id             INTEGER          CONSTRAINT gifts_pk PRIMARY KEY,
  3    gift_type_id        INTEGER          ,
  4    name                   VARCHAR2(30)     NOT NULL,
  5    description            VARCHAR2(50),
  6    price                  NUMBER(5, 2)
  7  );

Table created.

SQL>
SQL> INSERT INTO gifts (gift_id, gift_type_id, name, description, price) VALUES (1, 1, 'Flower', 'Birthday', 19.95);

1 row created.

SQL> INSERT INTO gifts (gift_id, gift_type_id, name, description, price) VALUES (2, 1, 'Computer', 'New Year', 30.00);

1 row created.

SQL> INSERT INTO gifts (gift_id, gift_type_id, name, description, price) VALUES (3, 2, 'iPod', 'Birthday', 25.99);

1 row created.

SQL> INSERT INTO gifts (gift_id, gift_type_id, name, description, price) VALUES (4, 2, 'iPhone', 'New Year', 13.95);

1 row created.

SQL> INSERT INTO gifts (gift_id, gift_type_id, name, description, price) VALUES (5, 2, 'Book', 'Birthday', 49.99);

1 row created.

SQL>
SQL> CREATE OR REPLACE PROCEDURE updatePrice(p_gift_id IN gifts.gift_id%TYPE,p_factor IN NUMBER) AS
  2    v_gift_count INTEGER;
  3  BEGIN
  4    SELECT COUNT(*) INTO v_gift_count FROM gifts WHERE gift_id = p_gift_id;
  5
  6    IF v_gift_count = 1 THEN
  7      UPDATE gifts  SET price = price * p_factor WHERE gift_id = p_gift_id;
  8      COMMIT;
  9    END IF;
 10  EXCEPTION
 11    WHEN OTHERS THEN
 12      ROLLBACK;
 13  END updatePrice;
 14  /

Procedure created.

SQL>
SQL>
SQL> drop table gifts;

Table dropped.








Related examples in the same category

1.Creat an empty procedure
2.replace a procedure
3.Create a stored procedure and how to call it.
4.Define procedure to insert data
5.Define and call procedure
6.Create procedure with authid
7.Create a stored procedure with authid
8.Mutually exclusive local subprograms.
9.A forward declaration.
10.Inner procedure
11.Save calculation result to a table in procedure
12.exception throwed out of the procedure
13.AUTHID clause in a CREATE PROCEDURE statement indicates that this procedure is being created with user's or invoker's rights
14.A local subprogram within a stored procedure
15.Overloaded local procedures: number and varchar2
16.Using all the default values
17.Forward Referencing
18.Mark procedure with authid current_user
19.Reference package variable in a procedure
20.Only manager can change the password