Differences
This shows you the differences between two versions of the page.
| Next revision | Previous revision | ||
|
jvx:server:db:table_functions [2020/02/25 22:22] admin created |
jvx:server:db:table_functions [2020/06/29 13:00] (current) cduncan Edited for English grammar (capitalization, punctuation, correct verb conjugation) |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| ~~NOTRANS~~ | ~~NOTRANS~~ | ||
| - | ~~Title: Using table functions~~ | + | ~~Title: Using Table Functions~~ |
| + | [[https://docs.oracle.com/cd/B19306_01/appdev.102/b14289/dcitblfns.htm|Table or pipelined functions]] are a cool thing. Use them to dynamically create records directly -- without a physical table -- in the database with all available database features. Not all database systems support table functions but Oracle and [[https://postgresrocks.enterprisedb.com/t5/The-Knowledgebase/Oracle-Workaround-PIPELINED/ta-p/152|PostgreSql]] do. Here's an example for a table function with Oracle. | ||
| + | |||
| + | First, we need the table function: | ||
| + | |||
| + | <code plsql> | ||
| + | -- the type | ||
| + | DROP TYPE type_testtable; | ||
| + | DROP TYPE type_testrow; | ||
| + | |||
| + | CREATE TYPE type_testrow AS OBJECT ( | ||
| + | id NUMBER, | ||
| + | description VARCHAR2(50) | ||
| + | ); | ||
| + | / | ||
| + | |||
| + | CREATE TYPE test_testtable IS TABLE OF type_testrow; | ||
| + | / | ||
| + | |||
| + | -- the table function | ||
| + | CREATE OR REPLACE FUNCTION get_tablefunction(pRowCount IN NUMBER) RETURN type_testtable AS | ||
| + | result type_testtable := type_testtable(); | ||
| + | BEGIN | ||
| + | FOR i IN 1 .. pRowCount LOOP | ||
| + | result.extend; | ||
| + | result(result.last) := type_testrow(i, 'Description for record ' || i); | ||
| + | END LOOP; | ||
| + | RETURN result; | ||
| + | END; | ||
| + | / | ||
| + | </code> | ||
| + | |||
| + | To use the table function with a **DBStorage**, try following: | ||
| + | |||
| + | <code java> | ||
| + | DBStorage dbs = new DBStorage(); | ||
| + | dbs.setDBAccess(dba); | ||
| + | dbs.setFromClause("TABLE(get_tablefunction(:COUNT))"); | ||
| + | dbs.open(); | ||
| + | |||
| + | DirectObjectConnection con = new DirectObjectConnection(); | ||
| + | con.put("dbs", dbs); | ||
| + | |||
| + | MasterConnection macon = new MasterConnection(con); | ||
| + | macon.open(); | ||
| + | |||
| + | RemoteDataSource rds = new RemoteDataSource(macon); | ||
| + | rds.open(); | ||
| + | |||
| + | RemoteDataBook rdb = new RemoteDataBook(); | ||
| + | rdb.setDataSource(rds); | ||
| + | rdb.setName("dbs"); | ||
| + | rdb.setFilter(new Equals("COUNT", BigDecimal.valueOf(20))); | ||
| + | rdb.open(); | ||
| + | |||
| + | rdb.fetchAll(); | ||
| + | </code> | ||