oracle stored procedure directly to save the query results as XML

2010-10-31  来源:本站原创  分类:Database  人气:129 

Stored procedure is written by someone else, but can not execute the first execution, the original is the first step does not set the lead, and it will write out the process to share with you the first step CREATE OR REPLACE DIRECTORY UTL_FILE_DIR as 'k: \ datafile'; - save xml files

- 'K: \ datafile' is your local directory, must exist, this step must be to have you visit the oracle user create DIRECTORY privilege, without the permission, please add the application administrator privileges.

Second generation test table
create table PEOPLE
(
PERSONID VARCHAR2 (114) not null,
NAME VARCHAR2 (150),
ADDRESS VARCHAR2 (200),
TEL VARCHAR2 (50),
FAX VARCHAR2 (50),
EMAIL VARCHAR2 (100)
)
Third stored procedure
CREATE OR REPLACE PROCEDURE OUTPUTPEOPLE (sqlStr VARCHAR2, file_path VARCHAR2)
AS
personRow PEOPLE% ROWTYPE; - get cursor row variable
TYPE person_cur IS REF CURSOR; - Custom REF cursor
cur person_cur;
tempSql varchar2 (500): = 'SELECT * FROM PEOPLE'; - the initial query
doc xmlDom.DOMDocument: = xmldom.newDOMDocument; - create a document object
main_node xmlDom.DOMNode: = xmldom.makeNode (doc); - get the document node
root_elmt xmlDom.DOMElement: = xmldom.createElement (doc, 'PEOPLE'); - create root element
--================================================ ==
- The following definitions element
person_elmt xmlDom.DOMElement; - definition of PERSON elements
name_elmt xmlDom.DOMElement; - NAME element defined
address_elmt xmlDom.DOMElement; - definition of ADDRESS elements
tel_elmt xmlDom.DOMElement; - TEL elements defined
email_elmt xmlDom.DOMElement; - elements defined EMAIL
--================================================ ==
- The following defines the node
root_node xmlDom.DOMNode; - defined PEOPLE node
person_node xmlDom.DOMNode; - defined node PERSON
name_node xmlDom.DOMNode; - define NAME node
address_node xmlDom.DOMNode; - defined node ADDRESS
tel_node xmlDom.DOMNode; - TEL node defined
email_node xmlDom.DOMNode; - defined EMAIL node
temp_node xmlDom.DOMNode;
BEGIN
- Added to the document root node: <PEOPLE> </ PEOPLE>
root_node: = xmldom.appendChild (main_node, xmldom.makeNode (root_elmt));
- Additional query
tempSql: = tempSql | | sqlStr;
- Open the cursor
OPEN cur FOR tempSql;
- Traversal cursor
LOOP
FETCH cur INTO personRow;
EXIT WHEN cur% NOTFOUND;
--================================================ ===========================--
person_elmt: = xmldom.createElement (doc, 'PERSON'); - PERSON elements to create
- Node added to the PEOPLE PERSON node <PERSON> </ PERSON>
person_node: = xmldom.appendChild (root_node, xmldom.makeNode (person_elmt));
--================================================ ===========================--
- Node to add the property PERSONID PERSON
xmlDom.SETATTRIBUTE (person_elmt, 'PERSONID', personRow.personId);
--================================================ ===========================--
name_elmt: = xmldom.createElement (doc, 'NAME'); - create a NAME element
- Add a child node to node PERSON NAME
name_node: = xmldom.appendChild (person_node, xmlDom.makeNode (name_elmt));
- Adding text nodes to NAME
temp_node: = xmldom.appendChild (name_node, xmlDom.makeNode (xmldom.createTextNode (doc, personRow.name)));
--================================================ ===========================--
address_elmt: = xmldom.createElement (doc, 'ADDRESS'); - create ADDRESS element
- Add a child node to node PERSON ADDRESS
address_node: = xmldom.appendChild (person_node, xmlDom.makeNode (address_elmt));
- Add text to the ADDRESS node
temp_node: = xmldom.appendChild (address_node, xmlDom.makeNode (xmldom.createTextNode (doc, personRow.address)));
--================================================ ===========================--
tel_elmt: = xmldom.createElement (doc, 'TEL'); - TEL elements created
- Add a child node to node NAME TEL
tel_node: = xmldom.appendChild (person_node, xmlDom.makeNode (tel_elmt));
- Adding text nodes to TEL
temp_node: = xmldom.appendChild (tel_node, xmlDom.makeNode (xmldom.createTextNode (doc, personRow.tel)));
--================================================ ===========================--
email_elmt: = xmldom.createElement (doc, 'EMAIL'); - Creating Elements EMAIL
- Add a child node to node PERSON EMAIL
email_node: = xmldom.appendChild (person_node, xmlDom.makeNode (email_elmt));
- Add text to the node EMAIL
temp_node: = xmldom.appendChild (email_node, xmlDom.makeNode (xmldom.createTextNode (doc, personRow.email)));
--================================================ ===========================--
END LOOP;
CLOSE cur;
- Written to disk

xmlDom.writeToFile (doc, 'UTL_FILE_DIR'||'/'|| file_path,' GBK ');
EXCEPTION
WHEN OTHERS THEN
DBMS_output.PUT_LINE (SQLERRM);
END OUTPUTPEOPLE;

相关文章
  • oracle stored procedure directly to save the query results as XML 2010-10-31

    Stored procedure is written by someone else, but can not execute the first execution, the original is the first step does not set the lead, and it will write out the process to share with you the first step CREATE OR REPLACE DIRECTORY UTL_FILE_DIR as

  • oracle stored procedure directly saved as XML query results 2010-10-31

    Stored procedure is written by someone else, but not the implementation of the first performance, the original is the first result is not set, it will write out the process to share with you the first CREATE OR REPLACE DIRECTORY UTL_FILE_DIR as 'k: \

  • Oracle stored procedure 2010-03-30

    1. Overview Oracle stored procedure development key points: • Use Notepad text editor, using Oracle PL / SQL programming language to write a stored procedure; • In the Oracle database to create a stored procedure; • In the Oracle database using SQL *

  • Oracle stored procedure changes 2010-06-18

    The day before yesterday to do the statistics used Oracle stored procedure, not to write, but on the basis of direct changes in the original can, but when I open the stored procedure a look, day, 3 / 5 100 lines of stored procedure directly I give sc

  • Oracle stored procedure in the preparation of lessons learned 2010-06-23

    1, developers use other libraries if the Table or View, be sure to create a View in the current database to cross-database operations, it is best not to directly use the "databsevv.dbo.table_name", because it can not show that the SP sp_depends

  • oracle stored procedure and function calls return a result set 2010-07-28

    In program development, the common to the return result sets stored process, which in mysql and sql server li better deal, directly back query results Jiu may Le, But out in the oracle inside to out, the more of a step, for the unfamiliar brothers al

  • Oracle stored procedure using temporary tables 2010-10-08

    Oracle stored procedure using temporary tables One, Oracle temporary table knowledge In Oracle, the temporary table into SESSION, TRANSACTION two, SESSION-level data in the temporary table exists in the entire SESSION until the end of the SESSION; an

  • oracle stored procedure notes, and use stored procedures with Hibernate 2011-07-24

    Oracle stored procedure is mainly used to deal with complex business logic, the application of the logic (code, such as java form) into the database (pl / sql form) to perform. Through stored procedures to perform batch updates. Stored procedures run

  • Instance of Oracle stored procedure development 2010-10-25

    Instance of Oracle stored procedure development 2009-5-14 11:21:00 <script type="text/javascript"> </ script> <script src="http://images.sohu.com/cs/jsfile/js/ct.js" type="text/javascript"> </ script >

  • oracle stored procedure tuning Basics 2011-06-07

    1, use other libraries if the Table or View, be sure to create a View in the current library to implement cross-database, it is best not to use "databsevv.dbo.table_name", because it can not show that the SP sp_depends used cross-library table o

  • basic syntax oracle stored procedure 2010-03-29

    Keywords: oracle 1. The basic structure CREATE OR REPLACE PROCEDURE stored procedure name ( Parameter 1 IN NUMBER, Parameter 2 IN NUMBER ) IS Variable 1 INTEGER: = 0; Variable 2 DATE; BEGIN END stored procedure name 2.SELECT INTO STATEMENT To select

  • oracle stored procedure syntax 2010-03-30

    1. The basic structure CREATE OR REPLACE PROCEDURE stored procedure name ( Parameter 1 IN NUMBER, Parameter 2 IN NUMBER ) IS Variable 1 INTEGER: = 0; Variable 2 DATE; BEGIN END stored procedure name 2.SELECT INTO STATEMENT To select the query results

  • The basic syntax of oracle stored procedure and precautions 2010-03-31

    The basic syntax of oracle stored procedure 1. The basic structure CREATE OR REPLACE PROCEDURE stored procedure name ( Parameter 1 IN NUMBER, Parameter 2 IN NUMBER ) IS Variable 1 INTEGER: = 0; Variable 2 DATE; BEGIN END stored procedure name 2.SELEC

  • Oracle stored procedure to learn 2010-05-14

    The basic syntax of oracle stored procedure Create the stored procedure syntax: create or replace procedure stored procedure name (param1 in type, param2 out type) as Variable 1 type (range); Variable 2 type (range); Begin Select count (*) into the v

  • Oracle stored procedure and cursor 2010-06-02

    Oracle stored procedure and cursor Oracle stored procedure and cursor: select myFunc (parameter 1, parameter 2 ..) to dual; - one can perform some business logic: Oracle functions and stored procedures in the difference between: A: function must retu

  • Call oracle stored procedure with java Summary 2010-06-09

    1, what is stored procedure. Stored procedure is a section of the database server-side program, it has two types. A kind of similar to the SELECT query used to retrieve the data, the data can be retrieved in the form of a data set returned to the cus

  • [Change] debug oracle stored procedure and debug stored procedures sql server 2010-07-07

    On the stored procedure debugging, that method is very simple since, but do not know when a stored procedure to test the accuracy, print, insert a temporary table can be said to be employed every means, annoying annoying. I put the work in the follow

  • JAVA and ORACLE stored procedure 2010-07-19

    In large database systems, there are two very important functions, that is, stored procedures and triggers. Whether in the database system stored procedure or trigger, is through SQL statements and control flow statements to complete the set. In cont

  • Oracle stored procedure syntax to learn (Procedure example 1) 2010-07-26

    Stored procedure consists of three parts: a statement, the operative part, abnormal. Procedures and whether the parameter can be parameterized stored procedures. No reference procedure syntax 1 create or replace procedure NoParPro 2 as; 3 begin 4; 5

  • Oracle stored procedure [Back Qinbo comprehensive Detailed] 2010-10-13

    Oracle stored procedures Keywords: storedprocedure 1, What is stored procedure. A stored procedure is a database server-side program, it has two types. A similar SELECT query used to retrieve the data, the data can be retrieved in the form of a data