关键字查Oracle手册 试试使用C语言开发一个DLL怎么样? 1.以数据库管理员权限登录,建立必要的用户名(暂定为cdx)和全局同义名: create user cdx identified by "123 "; grant connect to cdx; grant create library to cdx; grant create procedure to cdx; create public synonym cdxpack for cdx.cdxpack; alter user cdx default tablespace users temporary tablespace temp; alter user cdx quota unlimited on users; 2. 建立必要的日志表纪录(根据需要,这一步也可以不要)用于当向表中增加纪录时, 调用触发器进行根据合法性检查,根据结果(是否为真)判断是否调用外部命令。 create table cdx.cmdrunlog ( usr_name varchar2(50), run_date date, cmd_str varchar2(50), para_str varchar2(50), run_rtecode number(10) ); 3. 编辑文件extproc.c,保存在:e:extproc目录 #include <stdio.h> #include <stdlib.h> #include <time.h> int RunSysCmd(char * str,char * parastr) { char CmdStr[300]; if ( str == NULL ) return 1; if (parastr != NULL) sprintf(CmdStr, "%s %s ",str,parastr); else sprintf(CmdStr, "%s ",str); return system(CmdStr); } 3. 运行命令 cl -I. /LD -Zi extproc.c /link msvcrt.lib /nod:libcmt /DLL 生成动态库文件extproc.dll,注意它的存放路径。 4. 运行命令 sqlplus cdx/123 登陆后,输入如下脚本,创建自定义的进程库: CREATE OR REPLACE LIBRARY cdxlib IS 'e:extprocextproc.dll '; / show errors; CREATE OR REPLACE PACKAGE cdxpack IS FUNCTION RunSysCmd(str IN CHAR,parastr IN CHAR) RETURN BINARY_INTEGER; end cdxpack; / show errors; CREATE OR REPLACE PACKAGE BODY cdxpack IS FUNCTION RunSysCmd(str IN CHAR,parastr IN CHAR) RETURN BINARY_INTEGER IS EXTERNAL NAME "RunSysCmd " LIBRARY cdxlib PARAMETERS ( str BY REFERENCE, parastr BY REFERENCE); end cdxpack; / show errors; 5. 编辑一个文本文件测试上面操作的正确性,文件名为testpack.sql: declare str1 CHAR(50) := 'dir '; parastr1 char(50) := 'e: > e:extprocpack.txt '; nRetVal binary_integer; begin nRetVal := cdxpack.RunSysCmd(str1,parastr1); dbms_output.put_line( 'cdxpack.RunSysCmd: '|| nRetVal); end; / exit; /
晕,最精确的结果要根据你的数据库版本去查找的,下面是10.2.0.1文档的描述。写个成程序尝试一下就明白了。 1、parameters 用来覆盖默认数据类型map关系的。如下面程序片断 CREATE OR REPLACE FUNCTION plsToCparse_func ( x IN BINARY_INTEGER, Y IN OUT CHAR) RETURN CHAR AS LANGUAGE C LIBRARY c_utils NAME "C_parse" PARAMETERS ( x, -- stores value of x x INDICATOR, -- stores null status of x y, -- stores value of y y LENGTH, -- stores current length of y y MAXLEN, -- stores maximum length of y RETURN INDICATOR, RETURN);char *C_parse(x, x_ind, y, y_len, y_maxlen, retind) int x; short x_ind; char *y; int *y_len; int *y_maxlen; short *retind;INDICATOR 是数据类型,相当于C的short类型(默认),在数据库中相当于type下标(BY VALUE,BY REFERENCE) return是返回值,用来存储C程序的返回。
试试使用C语言开发一个DLL怎么样?
1.以数据库管理员权限登录,建立必要的用户名(暂定为cdx)和全局同义名:
create user cdx identified by "123 ";
grant connect to cdx;
grant create library to cdx;
grant create procedure to cdx;
create public synonym cdxpack for cdx.cdxpack;
alter user cdx default tablespace users temporary tablespace temp;
alter user cdx quota unlimited on users;
2. 建立必要的日志表纪录(根据需要,这一步也可以不要)用于当向表中增加纪录时, 调用触发器进行根据合法性检查,根据结果(是否为真)判断是否调用外部命令。
create table cdx.cmdrunlog ( usr_name varchar2(50), run_date date, cmd_str varchar2(50), para_str varchar2(50), run_rtecode number(10) ); 3. 编辑文件extproc.c,保存在:e:extproc目录
#include <stdio.h> #include <stdlib.h>
#include <time.h>
int RunSysCmd(char * str,char * parastr)
{ char CmdStr[300];
if ( str == NULL ) return 1;
if (parastr != NULL) sprintf(CmdStr, "%s %s ",str,parastr);
else sprintf(CmdStr, "%s ",str); return system(CmdStr); }
3. 运行命令
cl -I. /LD -Zi extproc.c /link msvcrt.lib /nod:libcmt /DLL
生成动态库文件extproc.dll,注意它的存放路径。
4. 运行命令 sqlplus cdx/123 登陆后,输入如下脚本,创建自定义的进程库:
CREATE OR REPLACE LIBRARY cdxlib
IS 'e:extprocextproc.dll '; /
show errors;
CREATE OR REPLACE PACKAGE cdxpack
IS
FUNCTION RunSysCmd(str IN CHAR,parastr IN CHAR) RETURN BINARY_INTEGER;
end cdxpack; /
show errors;
CREATE OR REPLACE PACKAGE BODY cdxpack
IS
FUNCTION RunSysCmd(str IN CHAR,parastr IN CHAR)
RETURN BINARY_INTEGER IS EXTERNAL NAME "RunSysCmd " LIBRARY cdxlib
PARAMETERS ( str BY REFERENCE, parastr BY REFERENCE);
end cdxpack; /
show errors;
5. 编辑一个文本文件测试上面操作的正确性,文件名为testpack.sql:
declare
str1 CHAR(50) := 'dir ';
parastr1 char(50) := 'e: > e:extprocpack.txt ';
nRetVal binary_integer;
begin
nRetVal := cdxpack.RunSysCmd(str1,parastr1);
dbms_output.put_line( 'cdxpack.RunSysCmd: '|| nRetVal);
end;
/
exit;
/
1、parameters 用来覆盖默认数据类型map关系的。如下面程序片断
CREATE OR REPLACE FUNCTION plsToCparse_func (
x IN BINARY_INTEGER,
Y IN OUT CHAR)
RETURN CHAR AS LANGUAGE C
LIBRARY c_utils
NAME "C_parse"
PARAMETERS (
x, -- stores value of x
x INDICATOR, -- stores null status of x
y, -- stores value of y
y LENGTH, -- stores current length of y
y MAXLEN, -- stores maximum length of y
RETURN INDICATOR,
RETURN);char *C_parse(x, x_ind, y, y_len, y_maxlen, retind)
int x;
short x_ind;
char *y;
int *y_len;
int *y_maxlen;
short *retind;INDICATOR 是数据类型,相当于C的short类型(默认),在数据库中相当于type下标(BY VALUE,BY REFERENCE)
return是返回值,用来存储C程序的返回。