oracle数据工程师面试题.pdfVIP

问题：1

假设有一个表T_Str中有两列，类型都为varchar2,coll为主键:

collcol2

aa3,1,4,6,8,0

bb5,9,11,4,73

现要将co12字符串中的数值升序排序出：

collCO12

aa0,1,3,4,6,8

bb4,4,5,7,9,11

解答：

一创建表并插入测试数据

createtablet_str(col1varchar2(1000),col2varchar2(1000));

isertitot_strvalues(aa/5,9,l1,4,7,4)；

--建立函数FN_SPLIT_STRING

CREATEORREPLACEFUNCTIONFN_SPLIT_STRING(P_STR

VARCHAR2)RETURNT.STRINGAS

PV_STRT,STRING:=T_STRING();

PV_DELIMITERVARCHAR2(10)

PV_LENINTEGER:=0;

PVLOOPINTEGER:=0;

PVSTARTPOSINTEGER:=0;

PV_ENDPOSINTEGER:=0;

PV_COLLISTVARCHAR2(1000);

BEGIN

PV_LEN:=LENGTH(PV_DELIMITER);

PVCOLLIST:=PSTR;

LOOP

PV_ENDPOS:=INSTR(PV_COLLIST,PV_DELIMITER);

PVLOOP:=PVLOOP+1;

PV_STR.EXTEND(1);

IF(PV.ENDPOS)0THEN

PV_STR(PV_LOOP):=SUBSTR(PV_COLLIST,0,

PV_ENDPOS-1);

PVSTARTPOS:=PVENDPOS+PVLEN;

PV.COLLIST:=SUBSTR(PV_COLLIST,PV.STARTPOS);

ELSE

PV_STR(PV_LOOP):=PV_COLLIST;

ENDIF;

EXITWHENPVENDPOS=0;

ENDLOOP;

RETURNPV.STR;

END;

--测试结果

DECLARE

V_AVARCHAR2[100):=NULL;

BEGIN

FORMYCURIN(SELECTCOL1,COL2FROMT_STR)LOOP

FORMYCUR1IN(SELECTCOLUMN_VALUEA

FROM

TABLE(FN_SPLIT_STRING(MYCUR.COL2))

ORDERBYTO.NUMBER(A)DESC)LOOP

V_A:=MYCUR1.A||V||V_A;

ENDLOOP;

11

DBMS_OUTPUT.PUT_LINE(MYCUR.COL1||||V_A);

V_A:=NULL;

ENDLOOP;

END;

问题：2

有表「该表只有