📄 group__g__bind.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): Binding variables and arrays</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>Binding variables and arrays</h1><hr><a name="_details"></a><h2>Detailed Description</h2>OCILIB supports OCI data binding APIs<p>Programs variables can be binded to an Oracle SQL PL/SQL statement in order to :<p><ul><li>Provide input data for SQL statement</li><li>Provide input/output data for PL/SQL blocks</li></ul><p>OCILIB provides a set of binding functions to use with :<p><ul><li>Basic datatypes : string (char/wchar_t *), int, double, raw</li><li>Object datatypes : lobs, files,longs, dates, cursors, statements, timestamps, intervals, objects</li></ul><p>To use binding :<p><ul><li>Prepare a statement with <a class="el" href="group__g__exec.html#gd5a7fcffe7d24da001e44636cfc06578" title="Prepare a SQL statement or PL/SQL block.">OCI_Prepare()</a> (see Executing statements)</li><li>Bind variables by calling one if the OCI_Bindxxxxx() function for every input variable referenced by the SQL statement</li><li>Setup up values of the program variables</li><li>Call <a class="el" href="group__g__exec.html#g7189aa353845909aaedc8d5956429450" title="Execute a prepared SQL statement or PL/SQL block.">OCI_Execute()</a> as many times as needed</li><li>Each <a class="el" href="group__g__exec.html#g7189aa353845909aaedc8d5956429450" title="Execute a prepared SQL statement or PL/SQL block.">OCI_Execute()</a> call may be preceded by an update of the program variables (for INSERTs for example)</li></ul><p>OCILIB supports the OCI array Interface by binding arrays of C scalar types and OCILIB object types.<p><ul><li>all types supported the library can be used for array binding except <a class="el" href="struct_o_c_i___statement.html" title="Oracle SQL or PL/SQL statement.">OCI_Statement</a> and <a class="el" href="struct_o_c_i___long.html" title="Oracle Long datatype.">OCI_Long</a></li><li>Array binding is really fast for massive DML operations</li><li>For string/RAW arrays, the input array MUST BE a contiguous block of data and not an array of pointers. So to bind an array of 10 elements for a varchar2(30) column, binded variable must be a like array[10][31]</li></ul><p>OCILIB does not pre-parse statements (like other frameworks such as JDBC, ...) and lets Oracle recognize input variables embedded within the SQL statements.<p>Bind variables must be preceded in the SQL code by the character ':'.<p>Oracle and OCILIB supports two ways of binding :<p><ul><li>by name (default mode in OCILIB) : Oracle looks for variables in the SQL statement by searching their names provided to the binding function. So a variable can be binded once and used many times in the statement</li><li>by position : Oracle binds variables by position, so every variable is binded with a position number</li></ul><p>OCILIB Default binding mode is OCI_BIND_BY_NAME.<p>When using binding by position, provide the position to OCI_BindXXXX() call through the name parameter. Within this mode the bind name must be the position preceded by a semicolon like ':1', ':2', ....<p><dl class="user" compact><dt><b>Basic input bind 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">void</span>){ <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; <span class="keywordtype">int</span> code; <span class="keywordflow">if</span> (!<a class="code" href="group__g__init.html#gcdb642d75f7c8478e083634144bc813c" title="Initializes the library.">OCI_Initialize</a>(NULL, 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>(<span class="stringliteral">"db"</span>, <span class="stringliteral">"uer"</span>, pwd<span class="stringliteral">", OCI_SESSION_DEFAULT);</span><span class="stringliteral"> </span><span class="stringliteral"> st = OCI_StatementCreate(cn);</span><span class="stringliteral"> </span><span class="stringliteral"> OCI_Prepare(st, "</span><span class="keyword">delete</span> from test_fetch where code = :code<span class="stringliteral">");</span><span class="stringliteral"> OCI_BindInt(st, "</span>:code<span class="stringliteral">", &code);</span><span class="stringliteral"> </span><span class="stringliteral"> code = 5;</span><span class="stringliteral"> OCI_Execute(st);</span><span class="stringliteral"></span><span class="stringliteral"> code = 12;</span><span class="stringliteral"> OCI_Execute(st);</span><span class="stringliteral"> </span><span class="stringliteral"> OCI_Commit(cn);</span><span class="stringliteral"></span><span class="stringliteral"> OCI_Cleanup();</span><span class="stringliteral"> </span><span class="stringliteral"> return EXIT_SUCCESS;</span><span class="stringliteral">}</span></pre></div></dd></dl><dl class="user" compact><dt><b>Array interface 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">void</span>){ <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; <span class="keywordtype">int</span> tab_int[1000]; <span class="keywordtype">char</span> tab_str[1000][21]; <span class="keywordtype">int</span> i; <span class="keywordflow">if</span> (!<a class="code" href="group__g__init.html#gcdb642d75f7c8478e083634144bc813c" title="Initializes the library.">OCI_Initialize</a>(NULL, NULL, OCI_ENV_DEFAULT)) <span class="keywordflow">return</span> EXIT_FAILURE; <span class="comment">/* ... create connection and statement ... */</span> cn = <a class="code" href="group__g__connect.html#gdb6e2cdf759587dd81c2fda7c5f44338" title="Create a physical connection to an Oracle database server.">OCI_ConnectionCreate</a>(<span class="stringliteral">"db"</span>, <span class="stringliteral">"usr"</span>, <span class="stringliteral">"pwd"</span>, OCI_SESSION_DEFAULT); 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">/* binding */</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">"insert into products values(:i, :s)"</span>); <a class="code" href="group__g__bind.html#g60d87fbeb94afeaf410ec243eb8c0f98" title="Set the input array size for bulk operations.">OCI_BindArraySetSize</a>(st, 1000); <a class="code" href="group__g__bind.html#gaaaf4e6e0331f7a6e62a9a81d45c3a3e" title="Bind an array of integers.">OCI_BindArrayOfInts</a>(st, <span class="stringliteral">":i"</span>, (<span class="keywordtype">int</span>*) tab_int, 0); <a class="code" href="group__g__bind.html#g502cd4785691b17955f5d99276e48884" title="Bind an array of strings.">OCI_BindArrayOfStrings</a>(st, <span class="stringliteral">":s"</span>, (<span class="keywordtype">char</span>*) tab_str, 20, 0); <span class="comment">/* filling arrays */</span> <span class="keywordflow">for</span>(i=0;i<1000;i++) { tab_int[i] = i+1; sprintf(tab_str[i],<span class="stringliteral">"Name %d"</span>,i+1); } <span class="comment">/* execute */</span> <a class="code" href="group__g__exec.html#g7189aa353845909aaedc8d5956429450" title="Execute a prepared SQL statement or PL/SQL block.">OCI_Execute</a>(st); printf(<span class="stringliteral">"row processed : %d\n"</span>, <a class="code" href="group__g__exec.html#g1cf932261960da80cd36d650a08565c3" title="Return the number of rows affected by the SQL statement.">OCI_GetAffectedRows</a>(st)); <a class="code" href="group__g__transac.html#gee1ba614ed2dc5bd83bf788ca08f3e71" title="Commit current pending changes.">OCI_Commit</a>(cn); <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 boolean OCI_API </td><td class="memItemRight" valign="bottom"><a class="el" href="group__g__bind.html#g60d87fbeb94afeaf410ec243eb8c0f98">OCI_BindArraySetSize</a> (<a class="el" href="struct_o_c_i___statement.html">OCI_Statement</a> *stmt, unsigned int size)</td></tr><tr><td class="mdescLeft"> </td><td class="mdescRight">Set the input array size for bulk operations. <a href="#g60d87fbeb94afeaf410ec243eb8c0f98"></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__bind.html#gc4d78a514b3d9fa1cdeb561e98d8140d">OCI_BindArrayGetSize</a> (<a class="el" href="struct_o_c_i___statement.html">OCI_Statement</a> *stmt)</td></tr><tr><td class="mdescLeft"> </td><td class="mdescRight">Return the current input array size for bulk operations. <a href="#gc4d78a514b3d9fa1cdeb561e98d8140d"></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__bind.html#gf1aa3a489ea1c2f53273b986d46e2d68">OCI_AllowRebinding</a> (<a class="el" href="struct_o_c_i___statement.html">OCI_Statement</a> *stmt, boolean value)</td></tr><tr><td class="mdescLeft"> </td><td class="mdescRight">Allow different host variables to be binded using the same bind name or position between execution of a prepared statement. <a href="#gf1aa3a489ea1c2f53273b986d46e2d68"></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__bind.html#ga634e246d0b6af055545a49e0eae6aba">OCI_BindShort</a> (<a class="el" href="struct_o_c_i___statement.html">OCI_Statement</a> *stmt, const mtext *name, short *data)</td></tr><tr><td class="mdescLeft"> </td><td class="mdescRight">Bind an short variable. <a href="#ga634e246d0b6af055545a49e0eae6aba"></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__bind.html#gc1912a193654d5c3155d2a7a49f82bff">OCI_BindArrayOfShorts</a> (<a class="el" href="struct_o_c_i___statement.html">OCI_Statement</a> *stmt, const mtext *name, short *data, unsigned int nbelem)</td></tr><tr><td class="mdescLeft"> </td><td class="mdescRight">Bind an array of shorts. <a href="#gc1912a193654d5c3155d2a7a49f82bff"></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__bind.html#gbab9a06330466afe72efad7379b0235e">OCI_BindUnsignedShort</a> (<a class="el" href="struct_o_c_i___statement.html">OCI_Statement</a> *stmt, const mtext *name, unsigned short *data)</td></tr><tr><td class="mdescLeft"> </td><td class="mdescRight">Bind an unsigned short variable. <a href="#gbab9a06330466afe72efad7379b0235e"></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__bind.html#g390daed1f42a83ca070b625306e4b293">OCI_BindArrayOfUnsignedShorts</a> (<a class="el" href="struct_o_c_i___statement.html">OCI_Statement</a> *stmt, const mtext *name, unsigned short *data, unsigned int nbelem)</td></tr><tr><td class="mdescLeft"> </td><td class="mdescRight">Bind an array of unsigned shorts. <a href="#g390daed1f42a83ca070b625306e4b293"></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__bind.html#g4d08024d2242b6140fecea8c2ec05c81">OCI_BindInt</a> (<a class="el" href="struct_o_c_i___statement.html">OCI_Statement</a> *stmt, const mtext *name, int *data)</td></tr><tr><td class="mdescLeft"> </td><td class="mdescRight">Bind an integer variable. <a href="#g4d08024d2242b6140fecea8c2ec05c81"></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__bind.html#gaaaf4e6e0331f7a6e62a9a81d45c3a3e">OCI_BindArrayOfInts</a> (<a class="el" href="struct_o_c_i___statement.html">OCI_Statement</a> *stmt, const mtext *name, int *data, unsigned int nbelem)</td></tr><tr><td class="mdescLeft"> </td><td class="mdescRight">Bind an array of integers. <a href="#gaaaf4e6e0331f7a6e62a9a81d45c3a3e"></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__bind.html#gaaed296bca244acc22d4f0d2f07e63b9">OCI_BindUnsignedInt</a> (<a class="el" href="struct_o_c_i___statement.html">OCI_Statement</a> *stmt, const mtext *name, unsigned int *data)</td></tr><tr><td class="mdescLeft"> </td><td class="mdescRight">Bind an unsigned integer variable. <a href="#gaaed296bca244acc22d4f0d2f07e63b9"></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__bind.html#g112a5b357b526492a5e85fa2f0cc55da">OCI_BindArrayOfUnsignedInts</a> (<a class="el" href="struct_o_c_i___statement.html">OCI_Statement</a> *stmt, const mtext *name, unsigned int *data, unsigned int nbelem)</td></tr><tr><td class="mdescLeft"> </td><td class="mdescRight">Bind an array of unsigned integers. <a href="#g112a5b357b526492a5e85fa2f0cc55da"></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__bind.html#gcdaf2a1e36d9d79d2a0548b6f892b3a3">OCI_BindBigInt</a> (<a class="el" href="struct_o_c_i___statement.html">OCI_Statement</a> *stmt, const mtext *name, big_int *data)</td></tr><tr><td class="mdescLeft"> </td><td class="mdescRight">Bind a big integer variable. <a href="#gcdaf2a1e36d9d79d2a0548b6f892b3a3"></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__bind.html#g48fc61bc85c747270f3013a11b1fab03">OCI_BindArrayOfBigInts</a> (<a class="el" href="struct_o_c_i___statement.html">OCI_Statement</a> *stmt, const mtext *name, big_int *data, unsigned int nbelem)</td></tr><tr><td class="mdescLeft"> </td><td class="mdescRight">Bind an array of big integers. <a href="#g48fc61bc85c747270f3013a11b1fab03"></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__bind.html#gaa329e0d17c690fb13fc28374338c238">OCI_BindUnsignedBigInt</a> (<a class="el" href="struct_o_c_i___statement.html">OCI_Statement</a> *stmt, const mtext *name, big_uint *data)</td></tr><tr><td class="mdescLeft"> </td><td class="mdescRight">Bind an unsigned big integer variable. <a href="#gaa329e0d17c690fb13fc28374338c238"></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__bind.html#g73f989c2c40b46e8566ae0d5926ff6d1">OCI_BindArrayOfUnsignedBigInts</a> (<a class="el" href="struct_o_c_i___statement.html">OCI_Statement</a> *stmt, const mtext *name, big_uint *data, unsigned int nbelem)</td></tr><tr><td class="mdescLeft"> </td><td class="mdescRight">Bind an array of unsigned big integers. <a href="#g73f989c2c40b46e8566ae0d5926ff6d1"></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__bind.html#g49988b9ca8f00b55cc3e62ca2385778b">OCI_BindString</a> (<a class="el" href="struct_o_c_i___statement.html">OCI_Statement</a> *stmt, const mtext *name, dtext *data, unsigned int len)</td></tr>⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -