Msdao15调用oracle存储过程

最新推荐文章于 2021-04-05 00:18:41 发布

wangyilongnj

最新推荐文章于 2021-04-05 00:18:41 发布

阅读量435

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/wangyilongnj/article/details/46562009

本文介绍了如何在Oracle中创建和使用存储过程，包括函数getmaxcatid()和调用该函数的存储过程GetNewItemId。接着展示了C++如何利用MSDAO库通过Msado15.dll调用这些Oracle存储过程，进行参数输入和输出的处理。最后提到了访问权限的设置问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1：oracle函数写法

create or replace FUNCTION getmaxcatid(cat1 IN NUMBER,
cat2 IN NUMBER,
cat3 IN NUMBER)

RETURN NVARCHAR2

AS

--示例，从两张表中查找123分类的最大流水并加1;

nvitem NVARCHAR2(30);
nvcat1 NVARCHAR2(10);
nvcat2 NVARCHAR2(10);
nvcat3 NVARCHAR2(10);
avg1 NVARCHAR2(50);
avgtemp NVARCHAR2(50);
avgall NVARCHAR2(50);
BEGIN

--处理1的类型;
IF cat1 > 0 AND cat1 < 10 THEN
nvcat1 := substr('0' || cat1, 1, 5);
ELSIF cat1 < 100 AND cat1 > 9 THEN
nvcat1 := cat1;
END IF;

--处理2的类型;
IF cat2 > 0 AND cat2 < 10 THEN
nvcat2 := substr('00' || cat2, 1, 5);
ELSIF cat2 < 100 AND cat2 >= 10 THEN

nvcat2 := substr('0' || cat2, 1, 5);

ELSIF cat2 < 1000 AND cat2 >= 100 THEN
nvcat2 := cat2;
END IF;

--处理3的类型;
IF cat3 > 0 AND cat3 < 10 THEN
nvcat3 := substr('00' || cat3, 5);
ELSIF cat3 < 100 AND cat3 >= 10 THEN
nvcat3 := substr('0' || cat3, 5);
ELSIF cat3 < 1000 AND cat3 >= 100 THEN
nvcat3 := cat3;
END IF;
nvitem := nvcat1 || nvcat2 || nvcat3 || '%';

SELECT MAX(item_number)
INTO avgtemp
FROM table1
WHERE cat1 = cat1
AND cat2 = cat2
AND cat3 = cat3
AND item_number LIKE nvitem;
SELECT MAX(item_number)
INTO avgall
FROM table1
WHERE cat1 = cat1
AND cat2 = cat2
AND cat3 = cat3
AND item_number LIKE nvitem;

IF avgtemp IS NULL THEN
avg1 := avgall;
ELSIF avgall IS NULL THEN
avg1 := avgtemp;
ELSIF avgall > avgtemp THEN
avg1 := avgall;
ELSE
avg1 := avgtemp;

END IF;

IF avg1 IS NULL OR avg1 = '' THEN

avg1 := nvcat1 || nvcat2 || nvcat3 || '000001';

ELSE
avg1 := avg1 + 1;
IF cat1 < 10 AND cat1 > 0 THEN
avg1 := '0' || avg1;
END IF;

END IF;
--DBMS_OUTPUT.put_line(avg1) ;
RETURN avg1;
END ;

2：oracle存储过程写法

create or replace PROCEDURE GetNewItemId
( Cat1 IN VARCHAR2
, Cat2 IN VARCHAR2
, Cat3 IN VARCHAR2
, itemCode OUT VARCHAR2
) AS
avgtemp NVARCHAR2(50);
BEGIN
select getmaxcatid(Cat1,Cat2,Cat3) INTO avgtemp from dual;
itemcode := avgtemp;
END;

3：C++调用存储过程

CString strSql = _T("GetNewItemId");
_bstr_t btSql(strSql);

_CommandPtr pCommand = nullptr;

HRSULT hr = pCommand.CreateInstance( __uuidof( Command ));

//pConnection 位MSADO的 _ConnectionPtr 类型，需要先进行open;

pCommand->ActiveConnection = pConnection;
pCommand->CommandText = btSql;
pCommand->CommandType = adCmdStoredProc;

//定义参数;

CComVariant varCat1;

varCat1 = L"15";

_ParameterPtr pParameterCat1 = pCommand->CreateParameter(L"Cat1", DataTypeEnum::adChar,ParameterDirectionEnum::adParamInputOutput,50,varCat1);
pCommand->Parameters->Append(pParameterCat1);
CComVariant varCat2;
varCat2 = L"121";
_ParameterPtr pParameterCat2 = pCommand->CreateParameter(L"Cat2", DataTypeEnum::adChar,ParameterDirectionEnum::adParamInputOutput,50,varCat2);
pCommand->Parameters->Append(pParameterCat2);
CComVariant varCat3;
varCat3 = L"103";
_ParameterPtr pParameterCat3 = pCommand->CreateParameter(L"Cat3", DataTypeEnum::adChar,ParameterDirectionEnum::adParamInputOutput,50,varCat3);
pCommand->Parameters->Append(pParameterCat3);
CComVariant varInOut = nullptr;
//如果参数是IN OUT 则varInOut 必须赋初值;
//varInOut = L"";
_ParameterPtr pParameterItemNumberPtr = pCommand->CreateParameter(L"ITEMCODE", DataTypeEnum::adChar,ParameterDirectionEnum::adParamOutput,50,varInOut);
pCommand->Parameters->Append(pParameterItemNumberPtr);

try
{
hr = pCommand->Execute( NULL, NULL, adCmdStoredProc);
}
catch(_com_error e)
{
CString errormessage;
errormessage.Format(_T("打开数据库失败！\r\n错误信息：%s"), e.ErrorMessage());
AfxMessageBox(errormessage);
return;
}
CComVariant varValue;
pParameterItemNumberPtr->get_Value(&varValue);

CString strValue = varValue.bstrVal;
strValue.Trim();
AfxMessageBox(strValue);