orecle的存储过程之连接c#

1.什么是存储过程

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。在数据量特别庞大的情况下利用存储过程能达到倍速的效率提升。

• 简单的说就是专门干一件事一段sql语句。
• 可以由数据库自己去调用，也可以由java程序去调用。
• 在oracle数据库中存储过程是procedure。

2.基本语法

(1).运算符

(2).SELECT INTO STATEMENT语句

该语句将select到的结果赋值给一个或多个变量，例如：

CREATE OR REPLACE PROCEDURE DEMO_CDD1 IS
s_name VARCHAR2;   --学生名称
s_age NUMBER;      --学生年龄
s_address VARCHAR2; --学生籍贯
BEGIN
  --给单个变量赋值
  SELECT student_address INTO s_address
  FROM student where student_grade=100;
   --给多个变量赋值
  SELECT student_name,student_age INTO s_name,s_age
  FROM student where student_grade=100;
  --输出成绩为100分的那个学生信息
  dbms_output.put_line('姓名：'||s_name||',年龄：'||s_age||',籍贯：'||s_address);
END

上面脚本中：

存储过程名称：DEMO_CDD1, student是学生表，要求查出成绩为100分的那个学生的姓名，年龄，籍贯

(3).选择语句

a.IF…END IF

学生表的sex字段：1-男生；0-女生

IF s_sex=1 THEN
  dbms_output.put_line('这个学生是男生');
END IF

b.IF…ELSE…END IF

IF s_sex=1 THEN
  dbms_output.put_line('这个学生是男生');
ELSE
  dbms_output.put_line('这个学生是女生');
END IF

3.基本用法

(1).基本结构

Oracle存储过程包含三部分：过程声明，执行过程部分，存储过程异常（可写可不写，要增强脚本的容错性和调试的方便性那就写上异常处理）

(2).无参存储过程

CREATE OR REPLACE PROCEDURE demo AS/IS
	变量2 DATE;
	变量3 NUMBER;
BEGIN
	--要处理的业务逻辑
	EXCEPTION    --存储过程异常
END 

这里的as和is一样任选一个，在这里没有区别，其中demo是存储过程名称。

(3).有参存储过程

a.带参数的存储过程

CREATE OR REPLACE PROCEDURE 存储过程名称(param1 student.id%TYPE)
AS/IS
name student.name%TYPE;
age number :=20;
BEGIN
  --业务处理.....
END

b.带参数的存储过程并且进行赋值

CREATE OR REPLACE PROCEDURE 存储过程名称(
       s_no in varchar,
       s_name out varchar,
       s_age number) AS
total NUMBER := 0;
BEGIN
  SELECT COUNT(1) INTO total FROM student s WHERE s.age=s_age;
  dbms_output.put_line('符合该年龄的学生有'||total||'人');
  EXCEPTION
    WHEN too_many_rows THEN 
    DBMS_OUTPUT.PUT_LINE('返回值多于1行'); 
END

其中参数IN表示输入参数，是参数的默认模式。
OUT表示返回值参数，类型可以使用任意Oracle中的合法类型。
INTO关键字，把查到的结果赋给total变量
输出查询结果，在数据库中“||”用来连接字符串

4.c#调用存储过程

1.执行不带参数的Oracle存储过程：

首先编写一个不带参数的oracle存储过程：

create or replace procedure proc1
is
begin  insert into test(ID,name,sex,age) values(1,'liheng','男',25);
commit;//记得写上这段事务提交语句
end;

C#调用代码如下：

  OracleConnection conn = new OracleConnection("server=cw;uid=ls0019999;pwd=aaaaaa");
        conn.Open();
        OracleCommand cmd = conn.CreateCommand();
        cmd.CommandType = CommandType.StoredProcedure;//指明是执行存储过程
        cmd.CommandText = "proc1";
        cmd.ExecuteNonQuery();
        conn.Close();

2.执行输入类型参数的存储过程：

首先编写一个输入类型参数的存储过程：

create or replace procedure proc2
(
v_id number,
v_name varchar2
)
is
begin
insert into test(id,name) values(v_id,v_name);
commit;
end;

C#调用代码如下：

        OracleConnection conn = new OracleConnection("server=cw;uid=ls0019999;pwd=aaaaaa");
        conn.Open();
        OracleCommand cmd = conn.CreateCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "proc2";
        cmd.Parameters.Add("v_id", OracleType.Number).Direction = ParameterDirection.Input;//指明传入的参数是输入给oracle存储过程用的
        cmd.Parameters["v_id"].Value =this.textBox1.Text.Trim();
        cmd.Parameters.Add("v_name",OracleType.NVarChar).Direction = ParameterDirection.Input;//指明传入的参数是输入给oracle存储过程用的
        cmd.Parameters["v_name"].Value =this.textBox2.Text.Trim();
        cmd.ExecuteNonQuery();
        conn.Close();

3. 执行输出类型参数的存储过程：

首先编写一个输出类型的存储过程：

create or replace procedure proc3
(
reccount out number
)
is
begin
select count(*) into reccount  from test;
end;

这段存储过程的意思求表中记录总数

C#调用代码如下：

  OracleConnection conn = new OracleConnection("server=cw;uid=ls0019999;pwd=aaaaaa");
        conn.Open();
        OracleCommand cmd = conn.CreateCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "proc3";
        cmd.Parameters.Add("reccount",OracleType.Number).Direction = ParameterDirection.Output;
        cmd.ExecuteNonQuery();
            //这里我用了一个文本框来接受执行存储过程之后返回的参数值
 this.textBox3.Text=cmd.Parameters["reccount"].Value.ToString();

5.存储函数

–定义
函数的定义和存储过程的定义是一样的，只不过函数必须要有返回值

create or replace function 存储函数名(参数名 in/out 数据类型)
    return 数据类型
    is|as
    begin
        return 具体的数据;
    end [存储函数名称];

eg:
–定义存储函数名计算年薪

create or replace function fun_salyears(f_no number)
    return number
    is
  sal_years number(9,2);
    begin
    select sal*12+nvl(comm,0) into sal_years from emp where empno=f_no;
        return sal_years;
    end ;

–使用存储函数

declare
  sal_yeats number(9,2);
  begin
    sal_yeats := fun_salyears(7876);
    dbms_output.put_line(sal_yeats);
  end;

–可简写

begin
    dbms_output.put_line(fun_salyears(7369));
  end;

--------存储过程和存储函数的区别--------
存储过程多用于项目之间的数据共享，存储函数多被用于存储过程调用.
存储函数可以在sql语句中调用，存储过程不能.

6.c#调用存储函数

FUNCTION TEST1(p_name VARCHAR2)  RETURN VARCHAR2 IS  
  BEGIN
      RETURN  'Good Morning ,'||p_name;                                                   
  END;

 private void btnLogin_Click(object sender, EventArgs e)
        {
            string sql = "MY_PUBLIC_PCK.TEST1";
            OracleParameter op1 = new OracleParameter("result", OracleDbType.Varchar2, 20);
            OracleParameter op2 = new OracleParameter("p_user_name", OracleDbType.Varchar2);
            op1.Direction = ParameterDirection.ReturnValue;
            op2.Direction = ParameterDirection.Input;
            op2.Value = this.tbUserName.Text;

            try
            {
                OracleConnection myConnect = new OracleConnection(constr);
                OracleCommand myCmd = new OracleCommand(sql,myConnect);
                myCmd.CommandType = CommandType.StoredProcedure;
                myConnect.Open();
                myCmd.Parameters.Add(op1);
                myCmd.Parameters.Add(op2);
                myCmd.ExecuteNonQuery();
                MessageBox.Show(op1.Value.ToString());   
            }
            catch (Exception ex)
            {
                this.tbUserName.Text = ex.ToString();
            }

 op1.Direction = ParameterDirection.ReturnValue;

如果传给后台的参数的方向是ReturnValue，即有返回值，那么调用的StoredProcedure将会被识别成函数。

OracleParameter op1 = new OracleParameter("result", OracleDbType.Varchar2, 20);

接收返回值的参数必须要定义长度大小，不然会报错

还有一点需要注意的是函数返回的值会return到myCmd.Parameters的第一个参数中，把上面的顺序改一下也会报错

 myCmd.Parameters.Add(op2);
 myCmd.Parameters.Add(op1);