📄 group__g__collection.html
字号:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><html><head><meta http-equiv="Content-Type" content="text/html;charset=UTF-8"><title>OCILIB (C Driver for Oracle): Oracle collections (Varrays and Nested Tables)</title><link href="doxygen.css" rel="stylesheet" type="text/css"><link href="tabs.css" rel="stylesheet" type="text/css"></head><body><!-- Generated by Doxygen 1.5.4 --><div class="tabs"> <ul> <li><a href="index.html"><span>Main Page</span></a></li> <li><a href="modules.html"><span>Modules</span></a></li> <li><a href="annotated.html"><span>Data Structures</span></a></li> <li><a href="files.html"><span>Files</span></a></li> </ul></div><h1>Oracle collections (Varrays and Nested Tables)</h1><hr><a name="_details"></a><h2>Detailed Description</h2>OCILIB supports all Oracle collections :<p><ul><li>PL/SQL Tables : only available in PL/SQL, unbounded, sparse arrays of homogeneous elements.</li><li>Varrays : available in SQL and PL/SQL, they are bounded arrays of homogeneous elements</li><li>Nested Tables : available in SQL and PL/SQL, they are unbounded arrays of homogeneous elements and can become sparse through deletions</li></ul><p>PL/SQL tables are implemented by binding regular C arrays with the array interface (using OCI_BindArrayOfXXX() calls)<p>Varrays and Nested tables are implemented in OCILIB with the type <a class="el" href="struct_o_c_i___coll.html" title="Oracle Collections (VARRAYs and Nested Tables) representation.">OCI_Coll</a>. It's possible to bind and fetch Varrays and Nested tables using <a class="el" href="struct_o_c_i___coll.html" title="Oracle Collections (VARRAYs and Nested Tables) representation.">OCI_Coll</a> handle.<p>It's also possible to declare local collections based on some datasase type without using queries<p>OCI (and thus OCILIB ) offers the possibility to access collection elements :<p><ul><li>directly by index (<a class="el" href="group__g__collection.html#g65523dc5e889b8d1e956674dd05e9b8b" title="Return the element at the given position in the collection.">OCI_CollGetAt()</a> and <a class="el" href="group__g__collection.html#g5fa6e43afbe0dc39b56b52f8e09dc29a" title="Assign the given element value to the element at the given position in the collection...">OCI_CollSetAt()</a>)</li><li>using an iterator (<a class="el" href="struct_o_c_i___iter.html" title="Oracle Collection iterator representation.">OCI_Iter</a>) to iterate through the collection (<a class="el" href="group__g__collection.html#gdfd202892038306f96b043d06e795133" title="Get the next element in the collection.">OCI_IterGetNext()</a>, <a class="el" href="group__g__collection.html#gd038e8b14740eba7409f6d0c5f871466" title="Get the previous element in the collection.">OCI_IterGetPrev()</a>)</li></ul><p>Collection Items are implemented through the type <a class="el" href="struct_o_c_i___elem.html" title="Oracle Collection item representation.">OCI_Elem</a> and use the series of calls OCI_ElemGetXXX() and OCI_ElemSetXXX() to manipulate elements content values<p><dl class="user" compact><dt><b>Example</b></dt><dd><div class="fragment"><pre class="fragment"><span class="preprocessor">#include "ocilib.h"</span><span class="keywordtype">int</span> main(<span class="keywordtype">int</span> argc , <span class="keywordtype">char</span> **argv){ <a class="code" href="struct_o_c_i___connection.html" title="Oracle physical connection.">OCI_Connection</a> *cn; <a class="code" href="struct_o_c_i___statement.html" title="Oracle SQL or PL/SQL statement.">OCI_Statement</a> *st; <a class="code" href="struct_o_c_i___resultset.html" title="Collection of output columns from a select statement.">OCI_Resultset</a> *rs; <a class="code" href="struct_o_c_i___coll.html" title="Oracle Collections (VARRAYs and Nested Tables) representation.">OCI_Coll</a> *coll; <a class="code" href="struct_o_c_i___iter.html" title="Oracle Collection iterator representation.">OCI_Iter</a> *iter; <a class="code" href="struct_o_c_i___elem.html" title="Oracle Collection item representation.">OCI_Elem</a> *elem; <a class="code" href="struct_o_c_i___schema.html" title="Schema metadata handle.">OCI_Schema</a> *type; <a class="code" href="struct_o_c_i___object.html" title="Oracle Named types representation.">OCI_Object</a> *obj; <span class="keywordtype">int</span> i, n; <span class="keywordflow">if</span> (!<a class="code" href="group__g__init.html#gcdb642d75f7c8478e083634144bc813c" title="Initializes the library.">OCI_Initialize</a>(err_handler, NULL, OCI_ENV_DEFAULT)) <span class="keywordflow">return</span> EXIT_FAILURE; cn = <a class="code" href="group__g__connect.html#gdb6e2cdf759587dd81c2fda7c5f44338" title="Create a physical connection to an Oracle database server.">OCI_ConnectionCreate</a>(argv[ARG_DB], argv[ARG_USER], argv[ARG_PWD], OCI_SESSION_DEFAULT); <span class="comment">/* Varray binding -------------------------------------------------------- */</span> st = <a class="code" href="group__g__exec.html#g335822f983af0fb5c529431f06a9a17b" title="Create a statement object and return its handle.">OCI_StatementCreate</a>(cn); <span class="comment">/* create the collection */</span> type = <a class="code" href="group__g__desc.html#gba23217d4a9477dc1c373b7392bd15de" title="Retrieve the available schema information.">OCI_SchemaGet</a>(cn, <span class="stringliteral">"Varray_type"</span>, OCI_SCHEMA_TYPE); coll = <a class="code" href="group__g__collection.html#g60ff6fdf0c0b8b2761ddba752ffad42b" title="Create a local collection instance.">OCI_CollCreate</a>(type); <span class="comment">/* bind the local collection to a PL/SQL procedure */</span> <a class="code" href="group__g__exec.html#gd5a7fcffe7d24da001e44636cfc06578" title="Prepare a SQL statement or PL/SQL block.">OCI_Prepare</a>(st, <span class="stringliteral">"begin load_array(:array); end;"</span>); <a class="code" href="group__g__bind.html#g9399dea6317fcd8d5c6d5b6282ec0d7b" title="Bind a Collection variable.">OCI_BindColl</a>(st, <span class="stringliteral">":array"</span>, coll); <a class="code" href="group__g__exec.html#g7189aa353845909aaedc8d5956429450" title="Execute a prepared SQL statement or PL/SQL block.">OCI_Execute</a>(st); <span class="comment">/* the procedure has filled the collection and </span><span class="comment"> we can iterate it using an iterator */</span> iter = <a class="code" href="group__g__collection.html#g22abd2fab4b93cceda5de975574d1b6e" title="Create a iterator handle to iterate through a collection.">OCI_IterCreate</a>(coll); elem = <a class="code" href="group__g__collection.html#gdfd202892038306f96b043d06e795133" title="Get the next element in the collection.">OCI_IterGetNext</a>(iter); <span class="keywordflow">while</span> (elem != NULL) { printf(<span class="stringliteral">"value %s\n"</span>, <a class="code" href="group__g__collection.html#g3f37f83b33c65908812643ef68f31db1" title="Return the String value of the given collection element.">OCI_ElemGetString</a>(elem)); elem = <a class="code" href="group__g__collection.html#gdfd202892038306f96b043d06e795133" title="Get the next element in the collection.">OCI_IterGetNext</a>(iter); } <a class="code" href="group__g__collection.html#g9b186234fa7bfd143e2fecb126c7a8a4" title="Free a iterator handle.">OCI_IterFree</a>(iter); <a class="code" href="group__g__collection.html#g8663babe980c99ff0441feedff973912" title="Free a local collection.">OCI_CollFree</a>(coll); <span class="comment">/* Varray SQL fetch ------------------------------------------------------- */</span> <span class="comment">/* query on a table with varray column */</span> <a class="code" href="group__g__exec.html#gf2163ec44e644cc734c393188707985b" title="Parse and execute a SQL statement or PL/SQL block.">OCI_ExecuteStmt</a>(st, <span class="stringliteral">"SELECT * from table_article"</span>); rs = <a class="code" href="group__g__fetch.html#gf2a9e28b66a9538ba0ffb62bffb87c16" title="Retrieve the resultset handle from an executed statement.">OCI_GetResultset</a>(st); <span class="keywordflow">while</span> (<a class="code" href="group__g__fetch.html#g36ac26dcea78f6074421781e401f97ba" title="Fetch the next row of the resultset.">OCI_FetchNext</a>(rs)) { <span class="comment">/* iterate the collection using an iterator */</span> coll = <a class="code" href="group__g__fetch.html#gef57431fc441f687418f6fedd341ce5a" title="Return the current Collection value of the column at the given index in the resultset...">OCI_GetColl</a>(rs, 2); iter = <a class="code" href="group__g__collection.html#g22abd2fab4b93cceda5de975574d1b6e" title="Create a iterator handle to iterate through a collection.">OCI_IterCreate</a>(coll); elem = <a class="code" href="group__g__collection.html#gdfd202892038306f96b043d06e795133" title="Get the next element in the collection.">OCI_IterGetNext</a>(iter); printf(<span class="stringliteral">"article #%d\n"</span>, <a class="code" href="group__g__fetch.html#ga8d80dc30b2012eaddd13efa9dfb711d" title="Return the current integer value of the column at the given index in the resultset...">OCI_GetInt</a>(rs, 1)); <span class="keywordflow">while</span> (elem != NULL) { obj = <a class="code" href="group__g__collection.html#g2fafc0d1819eecec512e535cabcd284b" title="Return the object value of the given collection element.">OCI_ElemGetObject</a>(elem); printf(<span class="stringliteral">".... code %d, name%s \n"</span>, <a class="code" href="group__g__usertypes.html#g05bf984246cbaaa6d554af9cfb8d99e9" title="Return the integer value of the given object attribute.">OCI_ObjectGetInt</a>(obj, <span class="stringliteral">"ID"</span>), <a class="code" href="group__g__usertypes.html#g95ba08a32cf97b7bed6235cc48bd2300" title="Return the string value of the given object attribute.">OCI_ObjectGetString</a>(obj, <span class="stringliteral">"NAME"</span>)); elem = <a class="code" href="group__g__collection.html#gdfd202892038306f96b043d06e795133" title="Get the next element in the collection.">OCI_IterGetNext</a>(iter); } <a class="code" href="group__g__collection.html#g9b186234fa7bfd143e2fecb126c7a8a4" title="Free a iterator handle.">OCI_IterFree</a>(iter); } <span class="comment">/* Nested table fetch ------------------------------------------------------- */</span> <span class="comment">/* query on a table with nested table column */</span> <a class="code" href="group__g__exec.html#gf2163ec44e644cc734c393188707985b" title="Parse and execute a SQL statement or PL/SQL block.">OCI_ExecuteStmt</a>(st, <span class="stringliteral">"SELECT * from table_sales"</span>); rs = <a class="code" href="group__g__fetch.html#gf2a9e28b66a9538ba0ffb62bffb87c16" title="Retrieve the resultset handle from an executed statement.">OCI_GetResultset</a>(st); <span class="keywordflow">while</span> (<a class="code" href="group__g__fetch.html#g36ac26dcea78f6074421781e401f97ba" title="Fetch the next row of the resultset.">OCI_FetchNext</a>(rs)) { coll = <a class="code" href="group__g__fetch.html#gef57431fc441f687418f6fedd341ce5a" title="Return the current Collection value of the column at the given index in the resultset...">OCI_GetColl</a>(rs, 2); printf(<span class="stringliteral">"Sale #%d\n"</span>, <a class="code" href="group__g__fetch.html#ga8d80dc30b2012eaddd13efa9dfb711d" title="Return the current integer value of the column at the given index in the resultset...">OCI_GetInt</a>(rs, 1)); <span class="comment">/* iterate the collection by accessing element by index */</span> n = <a class="code" href="group__g__collection.html#g107e9fef3fc0bced6503e566a67dfcb5" title="Returns the current number of elements of the given collection.">OCI_CollGetSize</a>(coll); <span class="keywordflow">for</span>(i = 1; i <= n; i++) { elem = <a class="code" href="group__g__collection.html#g65523dc5e889b8d1e956674dd05e9b8b" title="Return the element at the given position in the collection.">OCI_CollGetAt</a>(coll, i); obj = <a class="code" href="group__g__collection.html#g2fafc0d1819eecec512e535cabcd284b" title="Return the object value of the given collection element.">OCI_ElemGetObject</a>(elem); printf(<span class="stringliteral">".... employee %s, amount %s \n"</span>, <a class="code" href="group__g__usertypes.html#g95ba08a32cf97b7bed6235cc48bd2300" title="Return the string value of the given object attribute.">OCI_ObjectGetString</a>(obj, <span class="stringliteral">"EMP"</span>), <a class="code" href="group__g__usertypes.html#g95ba08a32cf97b7bed6235cc48bd2300" title="Return the string value of the given object attribute.">OCI_ObjectGetString</a>(obj, <span class="stringliteral">"AMOUNT"</span>)); } } <a class="code" href="group__g__init.html#g639706aa8e9689c7ebffc018fac6d3ae" title="Clean up all resources allocated by the library.">OCI_Cleanup</a>(); <span class="keywordflow">return</span> EXIT_SUCCESS;}</pre></div> </dd></dl><p><table border="0" cellpadding="0" cellspacing="0"><tr><td></td></tr><tr><td colspan="2"><br><h2>Functions</h2></td></tr><tr><td class="memItemLeft" nowrap align="right" valign="top">OCI_EXPORT <a class="el" href="struct_o_c_i___coll.html">OCI_Coll</a> *OCI_API </td><td class="memItemRight" valign="bottom"><a class="el" href="group__g__collection.html#g60ff6fdf0c0b8b2761ddba752ffad42b">OCI_CollCreate</a> (<a class="el" href="struct_o_c_i___schema.html">OCI_Schema</a> *schema)</td></tr><tr><td class="mdescLeft"> </td><td class="mdescRight">Create a local collection instance. <a href="#g60ff6fdf0c0b8b2761ddba752ffad42b"></a><br></td></tr><tr><td class="memItemLeft" nowrap align="right" valign="top">OCI_EXPORT boolean OCI_API </td><td class="memItemRight" valign="bottom"><a class="el" href="group__g__collection.html#g8663babe980c99ff0441feedff973912">OCI_CollFree</a> (<a class="el" href="struct_o_c_i___coll.html">OCI_Coll</a> *coll)</td></tr><tr><td class="mdescLeft"> </td><td class="mdescRight">Free a local collection. <a href="#g8663babe980c99ff0441feedff973912"></a><br></td></tr><tr><td class="memItemLeft" nowrap align="right" valign="top">OCI_EXPORT boolean OCI_API </td><td class="memItemRight" valign="bottom"><a class="el" href="group__g__collection.html#g5121ce70006885b5f45df2c764ec8d8e">OCI_CollAssign</a> (<a class="el" href="struct_o_c_i___coll.html">OCI_Coll</a> *coll, <a class="el" href="struct_o_c_i___coll.html">OCI_Coll</a> *coll_src)</td></tr><tr><td class="mdescLeft"> </td><td class="mdescRight">Assign a collection to another another one. <a href="#g5121ce70006885b5f45df2c764ec8d8e"></a><br></td></tr><tr><td class="memItemLeft" nowrap align="right" valign="top">OCI_EXPORT unsigned int OCI_API </td><td class="memItemRight" valign="bottom"><a class="el" href="group__g__collection.html#g18518027318ac527c9cbefab4f16b7ba">OCI_CollGetType</a> (<a class="el" href="struct_o_c_i___coll.html">OCI_Coll</a> *coll)</td></tr><tr><td class="mdescLeft"> </td><td class="mdescRight">Return the collection type. <a href="#g18518027318ac527c9cbefab4f16b7ba"></a><br></td></tr><tr><td class="memItemLeft" nowrap align="right" valign="top">OCI_EXPORT int OCI_API </td><td class="memItemRight" valign="bottom"><a class="el" href="group__g__collection.html#gfdc86421c73ed6955a965097cb335a64">OCI_CollGetMax</a> (<a class="el" href="struct_o_c_i___coll.html">OCI_Coll</a> *coll)</td></tr><tr><td class="mdescLeft"> </td><td class="mdescRight">Returns the maximum number of elements of the given collection. <a href="#gfdc86421c73ed6955a965097cb335a64"></a><br></td></tr><tr><td class="memItemLeft" nowrap align="right" valign="top">OCI_EXPORT int OCI_API </td><td class="memItemRight" valign="bottom"><a class="el" href="group__g__collection.html#g107e9fef3fc0bced6503e566a67dfcb5">OCI_CollGetSize</a> (<a class="el" href="struct_o_c_i___coll.html">OCI_Coll</a> *coll)</td></tr><tr><td class="mdescLeft"> </td><td class="mdescRight">Returns the current number of elements of the given collection. <a href="#g107e9fef3fc0bced6503e566a67dfcb5"></a><br></td></tr><tr><td class="memItemLeft" nowrap align="right" valign="top">OCI_EXPORT boolean OCI_API </td><td class="memItemRight" valign="bottom"><a class="el" href="group__g__collection.html#gc5e37e3a0fd362c8c3aa150d5cb66c51">OCI_CollTrim</a> (<a class="el" href="struct_o_c_i___coll.html">OCI_Coll</a> *coll, int nb_elem)</td></tr><tr><td class="mdescLeft"> </td><td class="mdescRight">Trims the given number of elements from the end of the collection. <a href="#gc5e37e3a0fd362c8c3aa150d5cb66c51"></a><br></td></tr><tr><td class="memItemLeft" nowrap align="right" valign="top">OCI_EXPORT <a class="el" href="struct_o_c_i___elem.html">OCI_Elem</a> *OCI_API </td><td class="memItemRight" valign="bottom"><a class="el" href="group__g__collection.html#g65523dc5e889b8d1e956674dd05e9b8b">OCI_CollGetAt</a> (<a class="el" href="struct_o_c_i___coll.html">OCI_Coll</a> *coll, unsigned int index)</td></tr>⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -