Shulou(Shulou.com)06/01 Report--

Create a function in oracle that was intended to return an index table, but failed. Thinking that text can also transmit information, I was suddenly inspired to format the return value.

Considering that the amount of data returned may be large, the length of the varchar2 type is tight, so the return type is set to clob.

I am using the scott user's test table, and this is the function definition:

Create or replace function test_query_func (dept varchar2) return clobis type test_record is record (rec_empno emp.empno%type, rec_ename emp.ename%type, rec_job emp.job%type, rec_sal emp.sal%type); type test_query_arr is table of test_record index by binary_integer Cursor cur is select empno, ename, job, sal from emp where deptno = dept; test_query test_query_arr; I integer: = 0; ss varchar2 (200): =''; res clob: ='['; begin for c in cur loop i: = I + 1; test_query (I): = c; end loop For q in 1..test_query.count loop ss: ='(''| | test_query (Q). Rec_empno | |'',''| | test_query (Q). Rec_ename | |'',''| | test_query (Q). Rec_job | |'',''| test_query (Q). Rec_sal | |''); if Q < test_query.count then ss: = ss | |','; end if Res: = res | | ss; end loop; res: = res | |']'; return res;end

You can test the return value of this function in pl/sql developer:

Begin dbms_output.put_line (test_query_func ('30')); end

Output result:

[('7499,' ALLEN', 'SALESMAN',' 1600'), ('7521,' WARD', 'SALESMAN',' 1250'), ('7654,' MARTIN', 'SALESMAN',' 1250'), ('7698,' BLAKE', 'MANAGER',' 2850'), ('7844,' TURNER', 'SALESMAN',' 1500'), ('7900,' JAMES', 'CLERK',' 950')]

In fact, it has been defined as a style that contains tuple child elements in the list in python.

Here is the code in python:

Import cx_Oracle as ora;con = ora.connect ('scott/scott@oradb'); cur = con.cursor (); cur.execute (' select test_query_func (30) from dual'); res = cur.fetchall () [0] [0] .read (); cur.close (); con.close (); data = eval (res); import pandas as pd;df = pd.DataFrame (data, columns = ['empno',' ename', 'job',' sal']); print (df)

Thus, the long string value returned by the function in oracle is converted to a DataFrame object:

Empnoenamejobsal07499ALLENSALESMAN160017521WARDSALESMAN125027654MARTINSALESMAN125037698BLAKEMANAGER285047844TURNERSALESMAN150057900JAMESCLERK950

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.