plsql generate xml function
create or replace function sunten_test_xml_clob return clob is
ctx dbms_xmlgen.ctxhandle;
ctxdept dbms_xmlgen.ctxhandle;
xml clob;
xmldept clob;
xmldeptemp clob;
xmldeptemp1 clob;
emp_no number := 7369;
xmlc varchar2(4000); -- required to convert lob to varchar2
off pls_integer := 1;
len pls_integer := 4000;
BEGIN
xml := to_clob(' ');
xmlDept := to_clob(' ');
xmlDeptEmp := to_clob('
for c1 in (select * from dept) loop
ctxDept := dbms_xmlgen.newContext('SELECT DEPTNO DNO,DNAME,LOC FROM dept where deptno = '|| c1.deptno);
dbms_xmlgen.setRowSetTag(ctxDept, 'DEPTMENT');
dbms_xmlgen.setRowTag(ctxDept, null);
xmlDept := dbms_xmlgen.getXML(ctxDept);
dbms_xmlgen.closeContext(ctxDept);
xmlc := replace(dbms_lob.substr(xmlDept,dbms_lob.getlength(xmlDept)),'','');
xmlc := replace(xmlc,'','');
if xmlc is not null then
dbms_lob.append(xmlDeptEmp,to_clob(xmlc));
end if;
ctx := dbms_xmlgen.newContext('SELECT * FROM emp WHERE deptno = '|| c1.deptno);
dbms_xmlgen.setRowSetTag(ctx, 'EMPLOYEES');
dbms_xmlgen.setRowTag(ctx, 'EMPLOYEE');
xml := dbms_xmlgen.getXML(ctx);
dbms_xmlgen.closeContext(ctx);
if dbms_lob.getlength(xml) > 0 then
xmlc := replace(dbms_lob.substr(xml,dbms_lob.getlength(xml)),'','');
else
xmlc := '';
end if;
xmlc := xmlc||'';
dbms_lob.append(xmlDeptEmp,to_clob(xmlc));
end loop;
dbms_lob.append(xmlDeptEmp,to_clob('
dbms_output.put_line(xmlDeptEmp);
return xmlDeptEmp;
END;
/
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/9976/viewspace-1024015/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- plsql中的procedure和function程式設計SQLFunction程式設計
- plsql_function_package_translate_instr_dbms_random測試小記SQLFunctionPackagerandom
- Oracle/PLSQL: Sys_Context Function(轉自http://www.techonthenet.com)OracleSQLContextFunctionHTTP
- Oracle/PLSQL: UserEnv Function(轉自http://www.techonthenet.com/oracle/functions/userenv.php)OracleSQLFunctionHTTPPHP
- javascript 中function(){},new function(),new Function(),Function 摘錄JavaScriptFunction
- 22. Generate Parentheses (recursion algorithm)Go
- nuxt-generate 靜態站UX
- Leetcode 22 Generate ParenthesesLeetCode
- Leetcode-Generate ParenthesesLeetCode
- Generate Parentheses leetcode javaLeetCodeJava
- Use the statspack to generate the accurate explain planAI
- leetcode22_Generate ParenthesesLeetCode
- Mybatis-Plus Generate 原始碼分析MyBatis原始碼
- Generate BKS File( Bouncy Castle KeyStore)AST
- C# generate listview and fill data no xamlC#View
- 【開發篇plsql】plsql遊標SQL
- $(function(){})與(function($){....})(jQuery)的區別FunctionjQuery
- 【開發篇plsql】plsql物件型別SQL物件型別
- oracle plsql(二)_plsql塊內之**冪OracleSQL
- 觸發器trigger中呼叫包package(包中含:儲存過程procedure及函式function)_plsql觸發器Package儲存過程函式FunctionSQL
- oracle plsqlOracleSQL
- PLSQL questionsSQL
- plsql tnsnamesSQL
- begin plsqlSQL
- PostgreSQL DBA(99) - Develop(generate random string)SQLdevrandom
- How to generate HMAC-SHA1 in C#?MacC#
- [LeetCode] Generate Parentheses 生成括號LeetCode
- Recipe 5.6. Using SQL to Generate SQLSQL
- Using Excel to generate Inserts for SQL ServerExcelSQLServer
- JavaScript FunctionJavaScriptFunction
- javascript Function()JavaScriptFunction
- sendDataByUdp FunctionUDPFunction
- Substr FunctionFunction
- Function : dumpFunction
- [Bash] functionFunction
- 【開發篇plsql】plsql事務處理SQL
- Leetcode - 022. Generate ParenthesesLeetCode
- SCRIPT TO GENERATE SQL*LOADER CONTROL FILESQL