哪位高手帮忙给写个c#调用oracle存储过程的小例子?
一个简单的条件查询就行(SELECT ID,NAME,SEX,AGE,ADDRESS FROM USERINFO WHERE ADDRESS='上海')。
我对这个一窍不通、在网上找例子也看不懂、请高手们帮帮忙、谢谢!!!
一个简单的条件查询就行(SELECT ID,NAME,SEX,AGE,ADDRESS FROM USERINFO WHERE ADDRESS='上海')。
我对这个一窍不通、在网上找例子也看不懂、请高手们帮帮忙、谢谢!!!
其实几乎水平是0了
麻烦帮忙写个完整的例子让我参考一下、谢谢!
is
flag number;
begin
select rownum into flag from dual;
dbms_output.putline(flag); end ;
CREATE OR REPLACE PROCEDURE userinfo_proc(i_address VARCHAR2, o_cur OUT SYS_REFCURSOR)
IS
sqlstr VARCHAR2(200); -- 定义变量,用以存放SQL语句
BEGIN
sqlstr := 'SELECT Id, Name, Sex, Age, Address FROM userinfo WHERE Address = :i_address'; -- 给SQL变量赋值,其中 :i_address 是绑定变量,以提高执行效率!
OPEN o_cur FOR sqlstr USING i_address; -- 给游标变量赋值
END;
/
id number(18,0),
name varchar2(10),
sex char(4),
age number(18,0),
address varchar2(50)
);INSERT INTO userinfo VALUES(1,'luoyoumou','男',24,'北京朝阳');
INSERT INTO userinfo VALUES(1,'zhangtao','男',35,'上海');CREATE OR REPLACE PACKAGE userinfo_pkg
AS
TYPE myrctype IS REF CURSOR;
PROCEDURE userinfo_proc(i_address IN VARCHAR2, p_rc OUT myrctype);
END userinfo_pkg;
/CREATE OR REPLACE PACKAGE BODY userinfo_pkg
AS
PROCEDURE userinfo_proc(i_address IN VARCHAR2, p_rc OUT myrctype)
IS
sqlstr VARCHAR2(1000);
BEGIN
sqlstr := 'SELECT ID, NAME, SEX, AGE, ADDRESS FROM USERINFO WHERE address = :i_address';
open p_rc for sqlstr using i_address;
END userinfo_proc;
END userinfo_pkg;
/-------------------------- 测试: -------------------------------------
set serveroutput on;
var p_rc refcursor;
exec userinfo_pkg.userinfo_proc('上海',:p_rc);
print p_rc;
create or replace procedure p1
as
v_id varchar2(10);
v_name varchar2(20);
v_sex varchar2(2);
v_age number;
v_add varchar2(100);
cursor c1 is SELECT ID,NAME,SEX,AGE,ADDRESS FROM USERINFO WHERE ADDRESS='上海'
begin
for c1_rec in c1
loop
v_id:=c1_rec.id;
v_name:=c1_rec.name;
v_sex:=c1_rec.sex;
v_age:=c1_rec.age;
v_add:=c1_rec.address;
dbms_output.put_line(v_id||' '||v_name||' '||v_sex||' '||to_char(v_age)||' '||v_add);
end loop;
end;
using (OracleCommand cmd = new OracleCommand())
{
OracleConnection connection = new OracleConnection("Data Source=数据库名;Persist Security Info=True;User ID=用户名;Password=密码;Unicode=True");
cmd.Connection = connection;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "存储过程名";
//类似这样添加所需参数,如果是输出型参数,无需指定参数值,但需要指明数据类型和输出方向。
cmd.Parameters.Add("参数名",参数值); //执行语句
cmd.ExecuteNonQuery();
}介于你的水平问题,我还没办法把高难度代码贴出来,我现在是将代码都包装好了用,所以数据库操作的代码是相当复杂的,估计你看不懂,但是用起来相当简单,包装的目的就是为了使用方便。
using (OracleCommand cmd = new OracleCommand())
{
using(OracleConnection connection = new OracleConnection("Data Source=数据库名;Persist Security Info=True;User ID=用户名;Password=密码;Unicode=True"))
{
connection.Open();
cmd.Connection = connection;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "存储过程名";
//类似这样添加所需参数,如果是输出型参数,无需指定参数值,但需要指明数据类型和输出方向。
cmd.Parameters.Add("参数名",参数值); //执行语句
cmd.ExecuteNonQuery();
}
}