Use number functions when creating a cursor value : Cursor Value « Cursor « Oracle PL / SQL






Use number functions when creating a cursor value

    

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 is
  2          priceValue number;
  3          cursor dataCursor is select nvl(round(price * 1.15,2),0) from products where product_id = in_product_id;
  4  begin
  5            open dataCursor;
  6            fetch dataCursor into priceValue;
  7            return priceValue;
  8  exception
  9         when others then priceValue := 0;
 10         return priceValue;
 11  end;
 12  /

Function created.

SQL>
SQL> select product_id, price, GetProductTaxIn(product_id)
  2  from products
  3
SQL> drop table products;

Table dropped.

SQL>

   
    
    
    
  








Related examples in the same category

1.Cursor with a single value
2.Update statement with cursor variable
3.Server-side cursor variables.
4.A cursor FOR loop.
5.Cursor Variable Example 2
6.Read full table data from a cursor
7.Single column cursor
8.Use Complex cursor to simplify the pl/sql logic
9.Use cursor to do full table scan
10.Use data referenced by cursor to update table
11.Form sentences from database data
12.Two identical queries to demonstrate the impact of changes