📄 group__g__plsql.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): PL/SQL Support</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>PL/SQL Support</h1><hr><a name="_details"></a><h2>Detailed Description</h2>OCILIB has a strong PL/SQL support :<p><ul><li>Blocks, procedures and function can be used with OCILIB statements.</li><li>Ref cursors</li><li>Nested tables</li><li>Tables (indexed by integer types)</li><li>Access to the server side output generated by the DBMS_OUTPUT package</li></ul><p>Stored procedures/functions calls, blocks declarations are done like regular SQL calls using <a class="el" href="group__g__exec.html#gd5a7fcffe7d24da001e44636cfc06578" title="Prepare a SQL statement or PL/SQL block.">OCI_Prepare()</a>, <a class="el" href="group__g__exec.html#g7189aa353845909aaedc8d5956429450" title="Execute a prepared SQL statement or PL/SQL block.">OCI_Execute()</a>, <a class="el" href="group__g__exec.html#gf2163ec44e644cc734c393188707985b" title="Parse and execute a SQL statement or PL/SQL block.">OCI_ExecuteStmt()</a> and <a class="el" href="group__g__format.html#ge1b4805b4ed6530b496cd637b52a2adf" title="Parse and execute a formatted SQL statement or PL/SQL block.">OCI_ExecuteStmtFmt()</a> functions.<p>All PL/SQL statements must :<p><ul><li>start with a "begin' or "declare" keyword</li><li>end with a "end;" keyword</li></ul><p>Binding Host arrays to PL/SQL tables is done with OCI_BindArrayXXX() calls<p><dl class="user" compact><dt><b>Using a PL/SQL block with OCILIB</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> res = 0; <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">"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">/* pl/sql call */</span> <a class="code" href="group__g__exec.html#gd5a7fcffe7d24da001e44636cfc06578" title="Prepare a SQL statement or PL/SQL block.">OCI_Prepare</a>(st, MT(<span class="stringliteral">"begin :res := trunc(sysdate+1)-trunc(sysdate-1); end;"</span>)); <a class="code" href="group__g__bind.html#g4d08024d2242b6140fecea8c2ec05c81" title="Bind an integer variable.">OCI_BindInt</a>(st, MT(<span class="stringliteral">":res"</span>), &res); <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">"result : %i\n"</span>, res); <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><dl class="user" compact><dt><b>Binding host arrays to PL/SQL tables parameters of a stored procedure</b></dt><dd><div class="fragment"><pre class="fragment"><span class="preprocessor">#include "ocilib.h"</span><span class="preprocessor">#define SIZE_ARRAY 10</span><span class="preprocessor"></span><span class="preprocessor">#define SIZE_NAME 20</span><span class="preprocessor"></span><span class="preprocessor">#define SIZE_VALUE 100</span><span class="preprocessor"></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> i; <span class="keywordtype">char</span> tab_names [SIZE_ARRAY][SIZE_NAME + 1]; <span class="keywordtype">char</span> tab_values[SIZE_ARRAY][SIZE_VALUE + 1]; <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">"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">/* set values */</span> <span class="keywordflow">for</span> (i = 0; i < SIZE_ARRAY; i++) { sprintf(tab_names[i], <span class="stringliteral">"name %03d"</span>, i+1); sprintf(tab_values[i], <span class="stringliteral">"value %03d"</span>, i+1); } <span class="comment">/* prepare call and bind local arrays */</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 test.test(:tab_names, :tab_values); END;"</span>); <a class="code" href="group__g__bind.html#g502cd4785691b17955f5d99276e48884" title="Bind an array of strings.">OCI_BindArrayOfStrings</a>(st, <span class="stringliteral">":tab_names"</span>, (<span class="keywordtype">char</span>*) tab_names, SIZE_NAME , SIZE_ARRAY); <a class="code" href="group__g__bind.html#g502cd4785691b17955f5d99276e48884" title="Bind an array of strings.">OCI_BindArrayOfStrings</a>(st, <span class="stringliteral">":tab_values"</span>, (<span class="keywordtype">char</span>*) tab_values, SIZE_VALUE, SIZE_ARRAY); <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); <span class="comment">/* cleanup */</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><dl class="user" compact><dt><b>Retrieve the output generated by the dbms_output package on the server</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="keyword">const</span> dtext *p; <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">"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">/* pl/sql call */</span> <span class="comment">/* server output */</span> <a class="code" href="group__g__plsql.html#geff8f38ba15b9e757cec70d91e4ffe13" title="Enable the server output.">OCI_ServerEnableOutput</a>(cn, 32000, 5, 255); <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">"begin "</span> <span class="stringliteral">" dbms_output.put_line('First line'); "</span> <span class="stringliteral">" dbms_output.put_line('Second line'); "</span> <span class="stringliteral">" dbms_output.put_line('Third line'); "</span> <span class="stringliteral">"end;"</span> ); <span class="keywordflow">while</span> (p = <a class="code" href="group__g__plsql.html#gbb13df960f035cd9e48e774c39db425a" title="Retrieve one line of the server buffer.">OCI_ServerGetOutput</a>(cn)) { printf(p); printf(<span class="stringliteral">"\n"</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 boolean OCI_API </td><td class="memItemRight" valign="bottom"><a class="el" href="group__g__plsql.html#geff8f38ba15b9e757cec70d91e4ffe13">OCI_ServerEnableOutput</a> (<a class="el" href="struct_o_c_i___connection.html">OCI_Connection</a> *con, unsigned int bufsize, unsigned int arrsize, unsigned int lnsize)</td></tr><tr><td class="mdescLeft"> </td><td class="mdescRight">Enable the server output. <a href="#geff8f38ba15b9e757cec70d91e4ffe13"></a><br></td></tr>⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -