一直在用transTitle翻译字段,结果却不知道它怎么建立的,记下来慢慢研究
prompt
prompt Creating type STRSPLITprompt ======================
prompt
CREATE OR REPLACE TYPE 数据库用户名.strSplit IS TABLE OF VARCHAR2 (4000)
/
prompt
prompt Creating function SPLITSTR
prompt ==========================
prompt
CREATE OR REPLACE FUNCTION 数据库用户名.splitStr(p_string IN VARCHAR2, p_delimiter IN VARCHAR2)
RETURN strSplit
PIPELINED
AS
v_length NUMBER := LENGTH(p_string);
v_start NUMBER := 1;
v_index NUMBER;
BEGIN
WHILE(v_start <= v_length)
LOOP
v_index := INSTR(p_string, p_delimiter, v_start);
IF v_index = 0
THEN
PIPE ROW(SUBSTR(p_string, v_start));
v_start := v_length + 1;
ELSE
PIPE ROW(SUBSTR(p_string, v_start, v_index - v_start));
v_start := v_index + 1;
END IF;
END LOOP;
RETURN;
END splitstr;
/
prompt
prompt Creating function TRANSTITLES
prompt =============================
prompt
CREATE OR REPLACE FUNCTION 数据库用户名.transTitles(
data IN VARCHAR2,
tableName IN VARCHAR2,
columnId IN VARCHAR2,
columnName IN VARCHAR2
) RETURN VARCHAR2 IS
titles VARCHAR2(200);
CURSOR cur_ids IS SELECT column_value FROM TABLE(splitstr(data, ','));
cur_row cur_ids%ROWTYPE;
BEGIN
titles := '';
FOR cur_row IN cur_ids LOOP
EXECUTE IMMEDIATE 'select ''' || titles || '''||' || columnName || '||'',''' || ' from ' || tableName ||
' where ' || columnId || ' = ''' || cur_row.column_value || '''' INTO titles;
END LOOP;
RETURN(substr(titles, 0, length(titles) - 1));
END transTitles;
/