|
SQL> CREATE TABLE products(
2 product_id NUMBER(6),
3 name VARCHAR2(50),
4 price NUMBER(8,2),
5 min_price NUMBER(8,2)
6 );
Table created.
SQL>
SQL> create or replace function GetProductTaxIn (in_product_id number) return number
2 is
3 priceValue number;
4 cursor dataCursor is select nvl(round(price * 1.15,2),0) from products where product_id = in_product_id;
5 begin
6 open dataCursor;
7 fetch dataCursor into priceValue;
8 return priceValue;
9 exception
10 when others then priceValue := 0;
11 return priceValue;
12 end;
13 /
Function created.
SQL>
SQL>
SQL>
SQL> select product_id, price, GetProductTaxIn(product_id)
2 from products
3 where GetProductTaxIn(product_id)>= 500
4
SQL>
SQL> drop table products;
Table dropped.
SQL>
SQL>
|