c调用存储过程
A. 如何调用存储过程，有返回值的，有参数的，存储过程中调用存储过程
create procere proc_a as declare b int; c varchar2(10); begin proc_b(b, c); dbms_output.put_line (c); end procere a; 你照我这个写吧 proc_b(b, c); proc_b是你调用的带返回参数的存储过程 b是输入变量，c是输出变量
B. c程序该怎么样调用oracle存储过程并获取存储过程中的输出参数的值
我以前这样用pro*c 这样玩的，仅供参考
EXEC sql EXECUTE
DECLARE
lsid varchar2(60);
lss_this_error_code number;
BEGIN
proctest(:ls_name,lsid); --ls_name 输入参数 lss_id 输出
:chId := lsid;-- lsid 输出返回给C变量chId
END;
END-EXEC;
C. 如何通过c或c++调用存储过程
给个例子给你看下：
...
_CommandPtr m_cmdptr;
m_cmdptr.CreateInstance(__uuidof( Command ) );
_ParameterPtr inParam2=NULL;
_ParameterPtr inParam3=NULL;
_ParameterPtr inParam4=NULL;
_ParameterPtr outParam1=NULL;
_ParameterPtr outParam2=NULL;
_ParameterPtr outParam3=NULL;
inParam2.CreateInstance(__uuidof(Parameter));
inParam3.CreateInstance(__uuidof(Parameter));
inParam4.CreateInstance(__uuidof(Parameter));
outParam1.CreateInstance(__uuidof(Parameter));
outParam2.CreateInstance(__uuidof(Parameter));
outParam3.CreateInstance(__uuidof(Parameter));
m_cmdptr->ActiveConnection=m_pConnection;
m_cmdptr->CommandType=adCmdStoredProc;
m_cmdptr->CommandText=_bstr_t("proc_XXX");//存储过程的名称
//输入参数
inParam2=m_cmdptr->CreateParameter(_bstr_t("@P1"),adVarChar,adParamInput,32,_bstr_t(P1));
m_cmdptr->Parameters->Append(inParam2);
inParam3=m_cmdptr->CreateParameter(_bstr_t("@P2"),adVarChar,adParamInput,20,_bstr_t(P2));
m_cmdptr->Parameters->Append(inParam3);
//输出参数
outParam1=m_cmdptr->CreateParameter("@P4",adInteger,adParamOutput,sizeof(int));
m_cmdptr->Parameters->Append(outParam1);
outParam2=m_cmdptr->CreateParameter("@P5",adInteger,adParamOutput,sizeof(int));
m_cmdptr->Parameters->Append(outParam2);
outParam3=m_cmdptr->CreateParameter(_bstr_t("@P6"),adVarChar,adParamOutput,20,_bstr_t(P6));
m_cmdptr->Parameters->Append(outParam3);
//执行
m_cmdptr->Execute(NULL,NULL,adCmdStoredProc);
//取返回值
long p1=m_cmdptr->Parameters->GetItem("@P4")->Value;
long p2=m_cmdptr->Parameters->GetItem("@P5")->Value;
VARIANT vtP6 =m_cmdptr->Parameters->GetItem("@P6")->Value;
if (vtP6.vt != VT_NULL)
strcpy(tp,(_bstr_t)vtP6);
m_cmdptr.Detach;
D. linux-c 嵌入式SQL如何调用存储过程
EXEC SQL CALL TEST_PROC(: test_str);
EXEC SQL COMMIT;
应该是这样调用，你试试
E. 求大神：C语言如何执行SQL sever的存储过程
执行带参数的存储过程的方法如下：
Exec sp_configure 'allow updates',1 --允许更新系统表。
exec dbo.User_ChangeObjectOwnerBatch 'OldOwner','dbo'
以上是两个例子。
SQL Server中执行带参数的存储过程的方法是：
EXEC 存储过程名字 '参数1','参数2',数值参数
EXEC 是一个关键字。
字符串参数使用单引号括起来，数值参数不需要使用单引号
F. 如何在VC中调用存储过程
定义好一个Connection连接,然后再产生一个Command对象,利用Command 对象进行存储过程调用
_ConnectionPtr m_Conn;
_CommandPtr m_Command
_variant_t vNULL;//定义为无参数
vNULL.vt = VT_ERROR;
vNULL.scode = DISP_E_PARAMNOTFOUND
m_Conn.CreateInstance( __uuidof( ADODB::Connection ));//初始化一个连接
m_Command.CreateInstance(__uuidof( ADODB::Command));;//初始化一个Command
m_Conn->Open("Provider=sqloledb;Data Source=yjf;Initial Catalog=pubs;User ID=sa;Password=sa","","",adModeUnknown);//在这里用你直接的Connection连接语句
_bstr_t sp_execstr;
sp_execstr="Exec sp_name";//sp_name为存储过程名,这句话应该可以在SQL Server中直接执行的
m_Command->ActiveConnection=m_Conn;
m_Command->m_CommandTxt=sp_execstr;
m_pRecordset = m_pCommand->Execute(&vNULL,&vNULL,adCmdText);
**********************************************************************************
_ConnectionPtr m_pConnection;
_CommandPtr m_pCommand;
//.cpp中在函数中执行
//建立ado连接
HRESULT hr;
hr=m_pConnection.CreateInstance(__uuidof(Connection));
try
{
if(SUCCEEDED(hr))
{
hr=m_pConnection->Open(_bstr_t(L"Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=Viper;Data Source=Viper"),_bstr_t (L"sa"),_bstr_t (L""),adModeUnknown);
}
}
catch(_com_error & err)
{
AfxMessageBox(err.Description(),MB_OK,0);
AfxMessageBox(err.ErrorMessage(),MB_OK,0);
AfxMessageBox("无法连接SQL SERVER 服务器，程序将退出。请检查网络设备",MB_OK,0);
exit(0);
}
//执行储存过程
CString cvar1,cvar2;
int cvar3;
cvar1=”ddd”;
cvar2=”";
cvar3=0;
try
{
m_pCommand.CreateInstance(__uuidof(Command));
m_pCommand->ActiveConnection=app->m_pConnection;
m_pCommand->CommandType=adCmdStoredProc;
m_pCommand->CommandText=_bstr_t(”pr_zs_dzdy”);
_variant_t vvar1,vvar2,vvar3;
vvar1=_variant_t(_bstr_t(cvar1));
vvar2=_variant_t(_bstr_t(cvar2));
vvar3=_variant_t(cvar3);
_ParameterPtr mp_var1,mp_var2,mp_var3;
mp_var1.CreateInstance(__uuidof(Parameter));
mp_var2.CreateInstance(__uuidof(Parameter));
mp_var3.CreateInstance(__uuidof(Parameter));
mp_var1=m_pCommand->CreateParameter
(
_bstr_t(”var1″),
adVarChar,
adParamInput,
3,
vvar1
);
m_pCommand->Parameters->Append(mp_var1);
mp_var2=m_pCommand->CreateParameter
(
_bstr_t(”var2″),
adVarChar,
adParamOutput,
3,
vvar2
);
m_pCommand->Parameters->Append(mp_var2);
mp_var3=m_pCommand->CreateParameter
(
_bstr_t(”var3″),
adIntger,
adParamOutput,
9,
vvar3
);
m_pCommand->Parameters->Append(mp_var3);
_variant_t vNull;
vNull.vt=VT_ERROR;
vNull.scode=DISP_E_PARAMNOTFOUND;
m_pCommand->Execute(&vNull,&vNull,adCmdStoredProc);
cvar2=mp_var2->Value.bstrVal;
cvar3=mp_var3->Value;
}
catch(_com_error &error)
{
MessageBox(error.ErrorMessage(),”ADO错误!”);
MessageBox(error.Description(),”ADO错误!”);
}
********************************************************************************
如果用ODBC访问数据库的话，可参考下列代码：
CDatabase * pDatabase = new CDatabase;
TRY
{
pDatabase->OpenEx( _T("DSN=ODBCName;UID=***;PWD=***"), CDatabase::noOdbcDialog);
}
CATCH (CDBException, e)
{
delete pDatabase;
return;
}END_CATCH
SQL.Format("exec sp_Name");//有参数的话直接写再后面
pDatabase->ExecuteSQL(SQL);
pDatabase->Close();
delete pDatabase;
****************************
below is from MSDN,you can find a lot of technical article in MSDN if you search"stored procere and VC"
good luck
Steps To Reproce Behavior
In the SQL Server 7.0 Query Analyzer select the test database Pubs.
Create the following stored procere. This stored procere returns a recordset and an out parameter count.
if exists (select * from sysobjects where id = object_id(N'[dbo].[GetJobs]') and OBJECTPROPERTY(id, N'IsProcere') = 1)
drop proc GetJobs
go
create proc GetJobs @id as int, @count as int [out] as
begin
Select @count = Count(*) from jobs where job_id >@id
Select * from jobs where job_id >@id
end
go
Use VC App Wizard to create a new console application and modify the code as follows:
#include "stdafx.h"
#include "stdio.h"
#import "C:\PROGRA~1\COMMON~1\System\ado\msado15.dll" no_namespace rename ("EOF", "EOF2")
struct InitOle {
InitOle() { ::CoInitialize(NULL); }
~InitOle() { ::CoUninitialize(); }
} _init_InitOle_;
int main(int argc, char* argv[])
{
_variant_t varErr((long)0, VT_ERROR);
_CommandPtr comm(__uuidof(Command));
_ConnectionPtr conn(__uuidof(Connection));
_bstr_t connstr="Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=pubs;Data Source=(local)";
conn->Open(connstr, "", "", adConnectUnspecified);
comm->ActiveConnection=conn;
comm->CommandText="GetJobs";
comm->CommandType = adCmdStoredProc ;
comm->Parameters->Refresh();
_variant_t recs;
comm->Parameters->Item[_variant_t((short)1)]->Value= _variant_t((long)5);
_RecordsetPtr rs = comm->Execute(&recs, &vtMissing,adCmdStoredProc);
_variant_t recordcount= comm->Parameters->Item[_variant_t((short)2)]->Value;
printf("recordcount = %li\n", (long)recordcount);
return 0;
}
Change the Datasource, User ID and password in the connection string above.
The recordcount variant that the above code returns is of type VT_NULL rather than the number of records that the stored procere returns.
G. c程序该怎么样调用oracle存储过程并获取存
如果已经存在一个存储过程A（参数）；
那么在存储过程B中可以直接通过：
values := A(参数);即可实现调用。
备注：values必须在初始化过程中进行定义如： values VARCHAR(30);并且此类型必须与A存储过程返回参数类型一致。
H. C语言 怎么 调存储过程
C语言里没有存储过程，只有函数，函数调用有两种方式
传值调用(call by value)和传址调用(call by reference)
例如
f1(x)
{
int a;
a=3*1-1；
f2(x,a);
return a+x;
}
f2(int r,int s)
{
int y;
y=2*r+1;
s=x*r;
r=s-y;
return;
}
f1中调用了f2函数，若第一个参数采用传值方式（无返回值，即f1中x=3 不变，只是把值传给f2使用而已）第二个参数采用传址方式（返回的是s的地址引用,即f1中a的值与f2中s的值相等）若x=3,则a+x=24
I. 关于存储过程创建和C#调用
declare
@insertId
intBEGIN
TRY
BEGIN
TRAN
--准备事务
intsert
into
a(f1,f2)values
('111','2222')
set
@InsertId=(select
@@IDENTITY)
insert
into
b(f1,f2)values(@InsertId,'3333') COMMIT
TRAN
end
TRY
BEGIN
catch
ROLLBACK--
回滚事务
END
CATCH -----我用的是mssql2008的，估计05的也能运行以上代码，2000的估计就不行了
J. C#中使用存储过程、触发器、范式！
use
OlayOnlineShop
go
--
=============================================
--
创建触发器
当管理员删除某个产品时先把购物车对应的产品清空
--
=============================================
IF
EXISTS
(SELECT
name
FROM
sysobjects
WHERE
name
=
'trig_ProctInfo')
DROP
TRIGGER
trig_ProctInfo
GO
CREATE
TRIGGER
trig_ProctInfo
ON
ProctInfo
INSTEAD
OF
DELETE
AS
BEGIN
--产品ID
DECLARE
@ProctId
INT
SELECT
@ProctId=ProctId
FROM
DELETED
--判断购物车中是否有该产品
IF
EXISTS
(SELECT
CartId
FROM
Cart
WHERE
ProctId=@ProctId)
BEGIN
DELETE
FROM
Cart
WHERE
ProctId=@ProctId
END
DELETE
FROM
ProctInfo
WHERE
ProctId=@ProctId
END
GO
--
=============================================
--
创建触发器
当删除购物车时先清空对应的定单信息
--
=============================================
IF
EXISTS
(SELECT
name
FROM
sysobjects
WHERE
name
=
'trig_Cart')
DROP
TRIGGER
trig_Cart
GO
CREATE
TRIGGER
trig_Cart
ON
Cart
INSTEAD
OF
DELETE
AS
BEGIN
--购物车ID，订单ID
DECLARE
@UserId
INT,
@CartId
INT,@OrderId
INT
--提取
用户ID，购物车ID和订单ID
SELECT
@UserId=UserId
FROM
DELETED
DECLARE
cart_cursor
CURSOR
SCROLL
DYNAMIC
FOR
--查询数据
存入游标
SELECT
CartId
FROM
Cart
WHERE
UserId=@UserId
OPEN
cart_cursor
FETCH
NEXT
FROM
cart_cursor
INTO
@CartId
--循环删除订单和购物车
WHILE(@@fetch_status=0)
BEGIN
IF
EXISTS
(SELECT
OrderId
FROM
OrderInfo
WHERE
CartId=@CartId)
BEGIN
SELECT
@OrderId=OrderId
FROM
OrderInfo
WHERE
CartId=@CartId
DELETE
FROM
OrderInfo
WHERE
OrderId=@OrderId
END
FETCH
NEXT
FROM
cart_cursor
INTO
@CartId
END
DELETE
FROM
Cart
WHERE
UserId=@UserId
CLOSE
cart_cursor
DEALLOCATE
cart_cursor
END
GO
---
=============================================
--
创建出发器
当用户下了订单,给用户加消费积分
--
==============================================
IF
EXISTS
(SELECT
name
FROM
sysobjects
WHERE
name
='trig_OrderInfo'
)
DROP
TRIGGER
trig_OrderInfo
GO
CREATE
TRIGGER
trig_OrderInfo
ON
OrderInfo
FOR
INSERT
AS
BEGIN
--定义购物车ID,用户ID,产品ID
以及产品价格和积分值
DECLARE
@cardId
INT,@userId
INT,@proctID
INT,@price
MONEY,@cent
INT
--获取购物车ID和,用户ID,产品ID以及产品价格
SELECT
@cardId
=
CartId
FROM
INSERTED
SELECT
@userId
=
UserId
FROM
Cart
WHERE
CartId=@cardId
SELECT
@proctID
=ProctId
FROM
Cart
WHERE
CartId=@cardId
SELECT
@price=ProctPrice
FROM
ProctInfo
WHERE
ProctId=@proctId
SET
@cent=@price/50
---获取积分,以50为等级
IF(@cent<1)
RETURN
ELSE
BEGIN
UPDATE
UserInfo
SET
Mark=Mark+@cent
WHERE
UserId=@userId
END
END
GO
--
=============================================
--
创建触发器
当同一用户购买同一商品时数量增加
--
=============================================
IF
EXISTS
(SELECT
name
FROM
sysobjects
WHERE
name
=
'trig_Cart'
)
DROP
TRIGGER
trig_Cart
GO
CREATE
TRIGGER
trig_Cart
ON
Cart
INSTEAD
OF
INSERT
AS
BEGIN
DECLARE
@userId
INT,@proctId
INT,@count
INT
DECLARE
@Date
DATETIME,@Remark
VARCHAR(200),@Remark2
VARCHAR(200)
--取出用户ID和产品ID等
SELECT
@userId=UserId
,@proctId=ProctId,@count=[Count],
@Date=[Date],@Remark=Remark,@Remark2=Remark2
FROM
INSERTED
--判断用户是否购买过产品
IF
EXISTS(SELECT
UserId
FROM
Cart
WHERE
UserId=@userId)
BEGIN
--判断用户购买的产品是否跟上次一致
IF
EXISTS(SELECT
ProctId
FROM
Cart
WHERE
ProctId=@proctId)
BEGIN
--取出同一产品的数量
UPDATE
Cart
SET
[Count]=[Count]+@count
WHERE
UserId=@UserId
AND
ProctId=@proctId
END
ELSE
BEGIN
--用户购买的产品不一致
INSERT
INTO
Cart
Values(@userId,@proctId,@count,@Date,@Remark,@Remark2)
END
END
ELSE
BEGIN
--用户没有购买过产品
INSERT
INTO
Cart
Values(@userId,@proctId,@count,@Date,@Remark,@Remark2)
END
END
GO
------用户信息表UserInfo测试数据存储过程
:
proc_userInfo_all
USE
OlayOnlineShop
IF
EXISTS
(SELECT
name
FROM
sysobjects
WHERE
name
=
'proc_userInfo_all')
DROP
PROCEDURE
proc_userInfo_all
GO
CREATE
PROCEDURE
proc_userInfo_all
@LoginName
VARCHAR(20),@UserName
VARCHAR(20),
@Password
NVARCHAR(50),@Gender
BIT,@Age
INT,@Email
VARCHAR(50),
@Address
VARCHAR(500),
@Date
DATETIME,@Role
INT,@Mark
INT,@Remark
VARCHAR(200)
AS
INSERT
INTO
UserInfo
(LoginName,UserName,[Password],Gender,Age,Email,Address,[Date],Role,Mark,Remark)
VALUES
(@LoginName
,@UserName
,@Password
,@Gender,
@Age,@Email,@Address
,@Date
,@Role
,@Mark,@Remark
)
GO
--
EXEC
proc_userInfo_all
'KK','潇潇','999999',1,18,'[email protected]','广西南宁','2007-9-19',1,999,''
-----产品类别信息表Category测试数据存储过程
:
pro_Category_all
IF
EXISTS
(SELECT
name
FROM
sysobjects
WHERE
name
=
'proc_Category_all')
DROP
PROCEDURE
proc_Category_all
GO
CREATE
PROCEDURE
proc_Category_all
@CategoryName
VARCHAR(50),@Description
VARCHAR(500),
@Remark
VARCHAR(200)
AS
INSERT
INTO
Category
(CategoryName,[Description],Remark)
VALUES(@CategoryName,@Description,@Remark)
GO
--EXEC
proc_Category_all
'保养类','能保养皮肤等等....','很棒的哦!!!!'
---产品信息表ProctInfo测试数据存储过程
:
pro_ProctInfo_all
IF
EXISTS
(SELECT
name
FROM
sysobjects
WHERE
name
=
'proc_ProctInfo_all')
DROP
PROCEDURE
proc_ProctInfo_all
GO
CREATE
PROCEDURE
proc_ProctInfo_all
@ProctName
VARCHAR(200),@CategoryId
INT,
@ProctBigPicture
VARCHAR(200),@ProctSmallPicture
VARCHAR(200),
@ProctPrice
MONEY,@Description
VARCHAR(200),@Virtue
VARCHAR(1500),
@Element
VARCHAR(1500),@HowToUse
VARCHAR(1500),@Date
DATETIME,@Remark
VARCHAR(200)
AS
INSERT
INTO
ProctInfo
(ProctName,CategoryId,ProctBigPicture,ProctSmallPicture,ProctPrice,[Description],
Virtue,Element,HowToUse,[Date],Remark)
VALUES
(@ProctName,@CategoryId,@ProctBigPicture,@ProctSmallPicture,@ProctPrice,@Description,
@Virtue,@Element,@HowToUse,@Date,@Remark)
GO
--
EXEC
proc_ProctInfo_all
'防晒油',1,'f.jpg','b.jpg',80.00,'女士专用','保护女士外出游玩','使用方法很简单','产品成分','2007-10-29','备注'
----购物车信息表Cart测试数据存储过程
:pro_Cart_all
IF
EXISTS
(SELECT
name
FROM
sysobjects
WHERE
name
=
'proc_Cart_all')
DROP
PROCEDURE
proc_Cart_all
GO
CREATE
PROCEDURE
proc_Cart_all
@UserId
INT,@ProctId
INT,
@Date
DATETIME,@Count
INT,
@Remark
VARCHAR(200)
AS
INSERT
INTO
Cart(UserId,ProctId,[Date],Count,Remark)
VALUES(@UserId,@ProctId,@Date,
@Count,@Remark)
GO
--EXEC
proc_Cart_all
1,1,'2007-10-29',1,'购物车'
---定单信息表OrderInfo
测试数据存储过程:pro_OrderInfo_all
IF
EXISTS
(SELECT
name
FROM
sysobjects
WHERE
name
=
'proc_OrderInfo_all')
DROP
PROCEDURE
proc_OrderInfo_all
GO
CREATE
PROCEDURE
proc_OrderInfo_all
@CartId
INT,@Date
DATETIME,@Postalcode
CHAR(6),
@BillerAddress
VARCHAR(500),@BillerName
VARCHAR(20),
@Phone
VARCHAR(20),@Remark
VARCHAR(200)
AS
INSERT
INTO
OrderInfo
(CartId,[Date],Postalcode,BillerAddress,BillerName,Phone,Remark)
VALUES(@CartId,@Date,@Postalcode,@BillerAddress,@BillerName,@Phone,@Remark)
GO
--EXEC
proc_OrderInfo_all
1,'2007-10-29','534000','南宁东葛路66号','95599
8153
95063
52662','2024188','定单'
---留言信息表MessageBoard测试数据存储过程
:
pro_MessageBoard_all
IF
EXISTS
(SELECT
name
FROM
sysobjects
WHERE
name
=
'proc_MessageBoard_all')
DROP
PROCEDURE
proc_MessageBoard_all
GO
CREATE
PROCEDURE
proc_MessageBoard_all
@UserId
INT,@Topic
VARCHAR(200),@Content
VARCHAR(200),
@Date
DATETIME,@Remark
VARCHAR(200)
AS
INSERT
INTO
MessageBoard
(UserId,Topic,Content,[Date],Remark)
VALUES(@UserId,@Topic,@Content,@Date,@Remark)
GO
--EXEC
proc_MessageBoard_all
1,'2007-10-29','我想购买防晒油','2007-10-29','留言'
---搜索产品信息
IF
EXISTS
(SELECT
name
FROM
sysobjects
WHERE
name
=
'proc_ProctInfo')
DROP
PROCEDURE
proc_ProctInfo
GO
CREATE
PROCEDURE
proc_ProctInfo
@CategoryName
VARCHAR(50),@ProctName
VARCHAR(200),
@MinMoney
VARCHAR(100),@MaxMoney
VARCHAR(100)
AS
DECLARE
@sql
VARCHAR(800)
DECLARE
@min
VARCHAR(100)
DECLARE
@max
VARCHAR(300)
SET
@min='0'
SET
@max='100'
SELECT
@sql='select
C.CategoryId,C.CategoryName,P.ProctId,P.ProctName,P.ProctSmallPicture,P.ProctPrice
from
ProctInfo
P
inner
join
Category
C
on
P.CategoryId=C.CategoryId'
--判断产品类别是否为空
IF
(@CategoryName
<>
''
AND
@CategoryName
is
not
null)
BEGIN
--判断是否搜索全部产品
IF
(@CategoryName!='全部')
BEGIN
SELECT
@sql
=
@sql+'
where
C.CategoryName
like
''%'
+
@CategoryName
+
'%'''
END
END
--判断产品名称是否存在
IF
(@ProctName
<>
''
AND
@ProctName
is
not
null)
BEGIN
--判断是否搜索全部产品
IF
(@CategoryName='全部')
BEGIN
SELECT
@sql
=
@sql+'
where
P.ProctName
like
''%'
+
@ProctName
+
'%'''
END
ELSE
BEGIN
SELECT
@sql
=
@sql+'
and
P.ProctName
like
''%'
+
@ProctName
+
'%'''
END
END
--判断小金额是否有
IF
(@MinMoney
!=
'')
BEGIN
SET
@min
=
@MinMoney
END
--判断大金额是否有
IF
(@MaxMoney
!=
'')
BEGIN
SET
@max
=@MaxMoney
END
IF
(@MinMoney
!=
''
OR
@MaxMoney
!=
'')
BEGIN
SELECT
@sql
=
@sql+'
and
P.ProctPrice
between
'+@min+'
and
'+@max
END
EXEC(@sql)
GO
EXEC
proc_ProctInfo
'新生','玉兰','0','28'