Trace:
Differences
This shows you the differences between two versions of the page.
| Next revision | Previous revision Next revision Both sides next revision | ||
|
jvx:server:db:table_functions [2020/02/25 22:22] admin created |
jvx:server:db:table_functions [2020/02/26 08:15] admin |
||
|---|---|---|---|
| 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 it 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> | ||