oracle学习历程之存储过程

来源：互联网发布：手机助手 mac 编辑：程序博客网时间：2024/06/14 02:46

oracle学习历程之存储过程

(1) 存储过程通过参数传递,安全性高,可防止注入式攻击.

(2) 查询的语句在存储过程里,与程序不相关,如果以后要修改程序或者数据库,都不会出现连锁反应,增加系统可扩展性.

(3) 网站执行查询的时候,只需要传递简单的参数就可以了,无论是代码优化上还是查询优化上都可以做到高效.

(4) 允许模块化编程,即,可以将一组查询写在一个过程里面,然后在程序里直接调用,而不必每次都写若干个语句来实现相应功能

【普通方法】
create or replace procedure procetest(paramin in varchar2, paramout out varchar2,paraminout in out varchar2)
as
varsparam varchar2(28);
begin
varsparam:=paramin;
paramout:=varsparam||paraminout;
end;

测试

set serveroutput on
declare
param_out varchar2( 28 );
param_inout varchar2( 28 );
begin
param_inout:='111';
mydata.procetest( 'qqqq',param_out,param_inout );
dbms_output.put_line( param_out );
end;

C#
       public string listResult(string xx1, string xx2)
        {
           OracleParameter[] parameters ={
           new OracleParameter( "paramin",OracleType.VarChar,20 ),   //输入参数
           new OracleParameter( "paramout",OracleType.VarChar,20 ), //输出参数
           new OracleParameter( "paraminout",OracleType.VarChar,20 ) //进出匀可
         };
            parameters[0].Value = xx1;
            parameters[2].Value = xx2;

            parameters[0].Direction = ParameterDirection.Input;
            parameters[1].Direction = ParameterDirection.Output;
            parameters[2].Direction = ParameterDirection.InputOutput;
            try
            {
                RunProcedure("procetest", parma);
                return parameters[1].Value.ToString();

            }
            catch (Exception e)
            {
                throw e;
            }
        }

string mytext = listResult(textBox1.Text.Trim (),textBox2.Text.Trim ());
label1.Text = mytext;

一个执行过程的方法（下面要用到）
给只要给出存储过程的名字（storedProcName）和参数（parameters 可能是多个参数因此这里设了一个数组）
private int RunProcedure(string storedProcName, OracleParameter[] parameters)
        {
            conn = new OracleConnection(strConn);
            OracleCommand cmd = new OracleCommand();
            cmd.Connection = conn;
            conn.Open();
            cmd.CommandText = storedProcName;//声明存储过程名
            cmd.CommandType = CommandType.StoredProcedure;//向程序指明釆用存储过程

            foreach (OracleParameter parameter in parameters)//通过循环“赋值”
            {
                cmd.Parameters.Add(parameter);
            }
            int signNum = -1;
            signNum=cmd.ExecuteNonQuery();//执行存储过程
            return signNum;
        }

【插入数据】
oracle中：

create or replace procedure insert_studentdata
(xxstuid in mydata.studentbase.stuid%type,
xxstuname in mydata.studentbase.stuname%type,
xxstuage in mydata.studentbase.stuage%type,
xxstuaddress in mydata.studentbase.stuaddress%type
) is
begin
insert into mydata.studentbase
(
stuid,stuname,stuage,stuaddress
)
values
(
xxstuid,xxstuname,xxstuage,xxstuaddress
);
end ;

测试存储过程

set serveroutput on
declare
xxstuid mydata.studentbase.stuid%type;
xxstuname mydata.studentbase.stuname%type;
xxstuage mydata.studentbase.stuage%type;
xxstuadd mydata.studentbase.stuaddress%type;
begin
xxstuid:=1009;
xxstuname:='lugy';
xxstuage:=12;
xxstuadd:='qqqqqqqdsdasskdj';
mydata.insert_studentdata(xxstuid,xxstuname,xxstuage,xxstuadd);
commit;//这个不能忘记！！否则只执行，不提交
end;

C＃代码
      OracleParameter[] parma = new OracleParameter[4];
            parma[0] = new OracleParameter("xxstuid", OracleType.Int32, 10);
            parma[1] = new OracleParameter("xxstuname", OracleType.VarChar, 10);
            parma[2] = new OracleParameter("xxstuage", OracleType.Int32, 3);
            parma[3] = new OracleParameter("xxstuaddress", OracleType.VarChar, 100);

            parma[0].Direction = ParameterDirection.Input;
            parma[1].Direction = ParameterDirection.Input;
            parma[2].Direction = ParameterDirection.Input;
            parma[3].Direction = ParameterDirection.Input;
            parma[0].Value = xxid;
            parma[1].Value = xxname;
            parma[2].Value = xxage;
            parma[3].Value = xxstuadd;

            try
            {
               int sign= RunProcedure("insert_studentdata", parma);
               if (sign > 0)
               {
                   MessageBox.Show("OK!!");
               }
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }

【更新数据】

create or replace procedure updata_studentname
(
xxstuid in mydata.studentbase.stuid%type,
xxstuname in mydata.studentbase.stuname%type
)
is
begin
update mydata.studentbase set stuname=xxstuname where stuid=xxstuid;
end updata_studentname;

C＃代码
与上相同

---补充代码
更新时
采用exception返回一些信息使程序更加完善

create or replace procedure updateNew_studentname
(
xxstuid in mydata.studentbase.stuid%type,
xxstuname in mydata.studentbase.stuname%type,
xxsign out varchar2
)
as
v_count number:=0;
begin
select count(1) into v_count from studentbase where stuid=xxstuid and
stuname=xxstuname;

if v_count>0 then
update studentbase set stuname=xxstuname where stuid=xxstuid;
Commit;
xxsign:='更新成功！';
return;
end if;
exception
when others then
xxsign:='更新失败！';
return;
end updateNew_studentname;

【返回数据集】
与sql server 不同 oracle中的存储过程比较特殊
要釆用“包”和“游标”，也很好用的。。。

此方法采用“包”与“游标”

oracle包包含包的说明(包头)和包体,
包头声明对外可用的函数的声明、过程的声明、变量等，

包体包含包的实现，如专用变量、函数的实现、过程的实现等。

包和包体一般是不可分的，包中定义变量；
包体中书写操作程序。
（备注：在数据库端得包体中编写数据库操作代码，简单，快捷，方便）。
前台只需要调用oracle的包体函数就可以实现该包体中的数据库操作语言，完成数据存储，修改，删除，等等功能。
除上述以外还有:视图，触发器，队列，数据库连接等等。
总之，oracle非常好用！！！！

------包体类似于C语言的公共类函数。_
下面的例子是：从用户mydata下的studentbase表中选出一些数据，这些数据的条件是年龄（stuage）等于us_age（外界输入的）
之后把这些（有可能是一条记录，也可能有多个记录）记录放到游标my_cursor（用于输出的）中，等待外界使用。

创建包
create or replace package pkg_select_student
as
type my_cursor is ref cursor;
procedure Getusername
(
us_age in mydata.studentbase.stuage%type,
cur_someName out my_cursor
);
end pkg_select_student;

创建包体

create or replace package body pkg_select_student
is
procedure Getusername(us_age in mydata.studentbase.stuage%type,cur_someName out my_cursor)
as
begin
open cur_someName for select * from studentbase where stuage=us_age;
end Getusername;
end pkg_select_student;

C＃中代码部分

           OracleConnection conn = new OracleConnection(strConn);
            OracleCommand cmd = new OracleCommand();
            cmd.Connection = conn;
            cmd.CommandText = "PKG_SELECT_STUDENT.Getusername";//包PKG_SELECT_STUDETN下面的Getusername的存储过程
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("us_age", OracleType.Int16, 10).Value = int.Parse(textBox3.Text.Trim());//添加参数
            cmd.Parameters.Add("cur_someName", OracleType.Cursor);
            cmd.Parameters["cur_someName"].Direction = ParameterDirection.Output;
            cmd.Parameters["us_age"].Direction = ParameterDirection.Input;

            OracleDataAdapter adp = new OracleDataAdapter(cmd);
            DataSet ds = new DataSet();
            adp.Fill(ds);
            dataGridView1.DataSource = ds.Tables[0].DefaultView; //绑定