I am working on night shift. To keep me awake, I decided to enhance my pl/sql knowledge during the shift if I have some free time. I build this test case for the conditional expressions when reading the Oracle application developer guide document.
At high level, I have a table called trader:
NAME EMAIL INTEREST
-------------------- ------------------------------ --------------------------------------------------
name1 vishu@abc.com symbol = 'ABC' and price > 31
name2 denis@abc.com symbol = 'ABC' and price < 31
name3 Nadeem@abc.com symbol = 'ABC' and price = 31
name4 Sam@abc.com symbol = 'ABC' and price <> 5.2
name5 John@abc.com symbol = 'ABC' and price < 31 and change = 5.2
name6 John@abc.com symbol = 'ABC' and price < 31 and change < 5.2
name7 denis@abc.com symbol = 'CCC' and price < 31
name8 denis@abc.com symbol = 'AAA' and price < 31
name9 vishu@abc.com symbol = 'ABC' and price > 25
name10 denis@abc.com symbol = 'ABC' and price < 25
name11 Nadeem@abc.com symbol = 'ABC' and price = 25
name12 Sam@abc.com symbol = 'ABC' and price <> 5.2
name13 John@abc.com symbol = 'ABC' and price > 25 and change = 5.2
name14 John@abc.com symbol = 'ABC' and price > 25 and change < 5.2
name15 denis@abc.com symbol = 'CCC' and price < 25
name16 denis@abc.com symbol = 'AAA' and price > 25
Then I issue a query and the output is as follows:
denis@test10g> select name, email
2 from traders
3 where evaluate ( interest,
4 'symbol=> ''ABC'',
5 price=>31,
6 change=>5.2'
7 ) = 1;
NAME EMAIL
-------------------- ------------------------------
name3 Nadeem@abc.com
name9 vishu@abc.com
name13 John@abc.com
I don't understand fully why the output looks like that, but at this time I decided to move on, leaving it to sometime later to figure it out
The following gives the whole output of the test case:
denis@test10g> @cond_expr
denis@test10g> rem script: cond_expr.sql
denis@test10g> rem
denis@test10g> rem Representing Conditional Expressions as Data
denis@test10g> rem
denis@test10g> rem The Oracle Expression Filter feature enables you to store
denis@test10g> rem conditional expressions as data in the database. The Expression Filter
denis@test10g> rem provides a mechanism that you can use to place a constraint on a VARCHAR2
denis@test10g> rem column to ensure that the values stored are valid SQL WHERE clause
denis@test10g> rem expressions. This mechanism also identifies the set of attributes
denis@test10g> rem that are legal to reference in the conditional expressions.
denis@test10g> rem
denis@test10g> rem Reference: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_sqltypes.htm
denis@test10g> rem
denis@test10g>
denis@test10g>
denis@test10g> -- (1) create a table traders holds data for a stock trading accont holder:
denis@test10g> create table traders
2 ( name varchar2(50),
3 email varchar2(50),
4 interest varchar2(50)
5 );
Table created.
denis@test10g>
denis@test10g> -- (2) create the user-defined datatype ticker with attributes for trading
denis@test10g> -- symbol, limit price, and amount of change in the stock price
denis@test10g>
denis@test10g> create or replace type ticker
2 as object
3 ( symbol varchar2(20),
4 price number,
5 change number
6 );
7 /
Type created.
denis@test10g> -- (3) create an attribute set ticker based on the ticker datatype
denis@test10g>
denis@test10g> begin
2 dbms_expfil.create_attribute_set ( attr_set => 'ticker',
3 from_type => 'YES');
4 end;
5 /
PL/SQL procedure successfully completed.
denis@test10g>
denis@test10g> -- (4)
denis@test10g> -- Associate the attribute set with the expression set stored in the
denis@test10g> -- database column trader.interest
denis@test10g> -- The following code places a constraint on the interest column that
denis@test10g> -- ensures the column stores valid conditional expressions
denis@test10g>
denis@test10g> begin
2 dbms_expfil.assign_attribute_set (attr_set => 'ticker',
3 expr_tab => 'traders',
4 expr_col => 'interest');
5 end;
6 /
PL/SQL procedure successfully completed.
denis@test10g>
denis@test10g>
denis@test10g> -- (5) Populate the table with trader names, email addresses and conditional
denis@test10g> -- expressions that represents a stock the trader is interested in at
denis@test10g> -- a particular price:
denis@test10g>
denis@test10g> insert into traders (name, email, interest)
2 values ('name1', 'vishu@abc.com', 'symbol = ''ABC'' and price > 31');
1 row created.
denis@test10g>
denis@test10g> insert into traders (name, email, interest)
2 values ('name2', 'denis@abc.com', 'symbol = ''ABC'' and price < 31');
1 row created.
denis@test10g>
denis@test10g> insert into traders (name, email, interest)
2 values ('name3', 'Nadeem@abc.com', 'symbol = ''ABC'' and price = 31');
1 row created.
denis@test10g>
denis@test10g> insert into traders (name, email, interest)
2 values ('name4', 'Sam@abc.com', 'symbol = ''ABC'' and price <> 5.2');
1 row created.
denis@test10g>
denis@test10g> insert into traders (name, email, interest)
2 values ('name5', 'John@abc.com', 'symbol = ''ABC'' and price < 31 and change = 5.2 ');
1 row created.
denis@test10g>
denis@test10g> insert into traders (name, email, interest)
2 values ('name6', 'John@abc.com', 'symbol = ''ABC'' and price < 31 and change < 5.2 ');
1 row created.
denis@test10g>
denis@test10g> insert into traders (name, email, interest)
2 values ('name7', 'denis@abc.com', 'symbol = ''CCC'' and price < 31');
1 row created.
denis@test10g>
denis@test10g> insert into traders (name, email, interest)
2 values ('name8', 'denis@abc.com', 'symbol = ''AAA'' and price < 31');
1 row created.
denis@test10g>
denis@test10g>
denis@test10g> insert into traders (name, email, interest)
2 values ('name9', 'vishu@abc.com', 'symbol = ''ABC'' and price > 25');
1 row created.
denis@test10g>
denis@test10g> insert into traders (name, email, interest)
2 values ('name10', 'denis@abc.com', 'symbol = ''ABC'' and price < 25');
1 row created.
denis@test10g>
denis@test10g> insert into traders (name, email, interest)
2 values ('name11', 'Nadeem@abc.com', 'symbol = ''ABC'' and price = 25');
1 row created.
denis@test10g>
denis@test10g> insert into traders (name, email, interest)
2 values ('name12', 'Sam@abc.com', 'symbol = ''ABC'' and price <> 5.2');
1 row created.
denis@test10g>
denis@test10g> insert into traders (name, email, interest)
2 values ('name13', 'John@abc.com', 'symbol = ''ABC'' and price > 25 and change = 5.2 ');
1 row created.
denis@test10g>
denis@test10g> insert into traders (name, email, interest)
2 values ('name14', 'John@abc.com', 'symbol = ''ABC'' and price > 25 and change < 5.2 ');
1 row created.
denis@test10g>
denis@test10g> insert into traders (name, email, interest)
2 values ('name15', 'denis@abc.com', 'symbol = ''CCC'' and price < 25');
1 row created.
denis@test10g>
denis@test10g> insert into traders (name, email, interest)
2 values ('name16', 'denis@abc.com', 'symbol = ''AAA'' and price > 25');
1 row created.
denis@test10g>
denis@test10g> commit;
Commit complete.
denis@test10g>
denis@test10g> -- (6) Use the EVALUATE operator to identify teh conditional expressions
denis@test10g> set linesize 120
denis@test10g> col name format a20
denis@test10g> col email format a30
denis@test10g>
denis@test10g>
denis@test10g> select * from traders;
NAME EMAIL INTEREST
-------------------- ------------------------------ --------------------------------------------------
name1 vishu@abc.com symbol = 'ABC' and price > 31
name2 denis@abc.com symbol = 'ABC' and price < 31
name3 Nadeem@abc.com symbol = 'ABC' and price = 31
name4 Sam@abc.com symbol = 'ABC' and price <> 5.2
name5 John@abc.com symbol = 'ABC' and price < 31 and change = 5.2
name6 John@abc.com symbol = 'ABC' and price < 31 and change < 5.2
name7 denis@abc.com symbol = 'CCC' and price < 31
name8 denis@abc.com symbol = 'AAA' and price < 31
name9 vishu@abc.com symbol = 'ABC' and price > 25
name10 denis@abc.com symbol = 'ABC' and price < 25
name11 Nadeem@abc.com symbol = 'ABC' and price = 25
name12 Sam@abc.com symbol = 'ABC' and price <> 5.2
name13 John@abc.com symbol = 'ABC' and price > 25 and change = 5.2
name14 John@abc.com symbol = 'ABC' and price > 25 and change < 5.2
name15 denis@abc.com symbol = 'CCC' and price < 25
name16 denis@abc.com symbol = 'AAA' and price > 25
16 rows selected.
denis@test10g>
denis@test10g> select name, email
2 from traders
3 where evaluate ( interest,
4 'symbol=> ''ABC'',
5 price=>31,
6 change=>5.2'
7 ) = 1;
NAME EMAIL
-------------------- ------------------------------
name3 Nadeem@abc.com
name9 vishu@abc.com
name13 John@abc.com
denis@test10g>
denis@test10g>
denis@test10g> -- clean up the test code:
denis@test10g> drop table traders;
Table dropped.
denis@test10g>
denis@test10g> begin
2 dbms_expfil.drop_attribute_set ( attr_set => 'ticker');
3 end;
4 /
PL/SQL procedure successfully completed.
denis@test10g> spool off
No comments:
Post a Comment