用了两年Oracle还没写过存储过程,真是十分惭愧,从今天开始学习Oracle存储过程,完全零起点,争取每日一篇学习笔记,可能开始认识的不全面甚至有错误,但坚持下来一定会有收获。
1. 建立一个存储过程
create or replace PROCEDURE firstPro
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello World!');
END;
其中IS关键字替换为AS关键字结果不会出现任何变化,大多认为他们是等同的,但也有一种说法解释为:一般PACKAGE 或者单独的FUNCTION, PROCEDURE 都用AS,PACKAGE 中的FUNCTION, PROCEDURE 用IS。
DBMS_OUTPUT.PUT_LINE('Hello World!'); 是一个输出语句。
2. 执行存储过程
Oracle返回结果需要使用包,那么存储过程似乎只能在数据库中执行或被其他调用,编程语言似乎并不能直接调用存储过程返回数据,是否能执行他有待研究。那么首先在数库中执行上面的存储过程。
BEGIN
FirstPro();//注意有括号
END;
运行后输出Hello World。
3. 下面写一个稍复杂的存储过程,他定义了变量,进行了运算,输出一个count操作所用的时间。
CREATE OR REPLACE procedure testtime
is
n_start number;
n_end number;
samplenum number;
use_time number;
begin
n_start:=dbms_utility.get_time;
select count(*) into samplenum from emp;
n_end:=dbms_utility.get_time;
use_time:= n_end - n_start;
dbms_output.put_line('This statement cost '|| use_time ||' miliseconds');
end;
4. 下面试验下怎么能给存储过程赋值
CREATE OR REPLACE procedure test(num in number) is
begin
dbms_output.put_line('The input numer is:' || num);
end ;
今天的就到这,明天将调用这个存储过程,并试验一写对表的操作。
1. 首先把昨天带参的存储过程执行一下
declare
n number;
begin
n:=1;
test(num=>n);
end;
注;在调用存储过程时,=>前面的变量为存储过程的形参且必须于存储过程中定义的一致,而=>后的参数为实际参数。当然也不可以不定义变量保存实参,可写成如下形式:
Begin
test(num=>1);
end;
这样我们就能更清楚得看到给存储过程赋值的格式了。后面打算用存储过程操作一些表,按照增删改查的顺序依次建立存储过程。
2. 插入
CREATE OR REPLACE
procedure proc_test_Insert_Single(e_no in number,e_name in varchar ,s in varchar,d in varchar)
as
begin
insert into emp (emp_id,emp_name,salary,birthday) values (e_no,e_name,s,d);
end;
调用:
DECLARE
i NUMBER;
n varchar(5);
s varchar(11);
d varchar(10);
BEGIN
i:=10;
n := 'text11';
s:='3998';
d:='1998-02-02';
PROc_TEST_Insert_single(e_no => i,e_name=>n,s=>s,d=>d);
END;
注:调用存储过程声明varchar时,必须限定长度,即斜体的部分不能少。同时如果给变量赋值时大于限定的长度了,则会提示ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小。
3. 更新
create or replace procedure proc_test_update_Single(e_no in number,s in varchar)
as
begin
UPDATE emp set salary =s where emp_id=e_no;
end;
调用:
DECLARE
n NUMBER;
s varchar(11);
BEGIN
n := 2;
s:=3998;
PROc_TEST_UPdate_single(e_no => n,s=>s);
END;
4. 号外,今天在开发过程中正好有个数据库更新操作可用存储过程实现,顺便练习一下,需求是将一个表中的ID字段,查出来更新到另一个表中,两个表通过b_bs和b_kgh关联。存储过程如下:
create or replace procedure update_yygzdbid
as
bs varchar(20);
kgh varchar(20);
bid number;
cursor c_db is select b_id,b_bs,b_kgh from pmdcdb;
begin
for temp in c_db loop
update yygz_db set b_id= temp.b_id where g_bs=temp.b_bs and g_bh=temp.b_kgh;
end loop;
end;
运行这个存储过程:
Begin
update_yygzdbid();
end;
说明:
(1).在没有参数的存储过程定义时存储过程的名称不需要括号,写成update_yygzdbid()是错误的,
(2). cursor c_db是定义一个游标,获得查询语句的结果集,
(3). For temp in c_bd loop
Begin
End;
End loop
是循环游标,其形式类似于C#中的foreach, 获得字段:temp.b_id。
5. 查询
最后我们做一个查询的存储过程,能够返回一个值,注意不是结果集,结果集是明天的目标。
CREATE OR REPLACE
procedure proc_test_Select_Single(t in varchar,r out varchar )
as
begin
select salary into r from emp where emp_name=t;
end;
这个存储过程使用了2个参数,并分别出现了IN和OUT,in代表输入,out用于输出,从下面的语句也可以看到salary写入到变量r中了,这个r我们可以在调用存储过程后得到。
这时编译后会出现一个Warning(1,48): PLW-07203: 使用 NOCOPY 编译器提示可能对参数 'R' 有所帮助,那么nocopy是什么呢,nocopy主要是针对in|out record/index-by table/varray/varchar2提高效率使用的, 对于number使用nocopy与否基本没有影响.所以在'enable:performance'情况下不会对number提示warning.
我们把第一行改为:procedure proc_test_Select_Single(t in varchar,r out nocopy varchar )
现在即使对in的varchar没有使用nocopy也不会提示警告,
DECLARE
T varchar2(4);
R VARCHAR2(4);
BEGIN
T := 'zz';
PROC_TEST_SELECT_SINGLE(T => T,R => R );
DBMS_OUTPUT.PUT_LINE('R = ' || R);
END;
运行后即可在输出中看到结果了。
三、
1. 今天我们首先写一个涨工资的存储过程,给每个低于5k工资的人涨点钱。
CREATE OR REPLACE PROCEDURE p_test(forRaise in number)
as
begin
for v_emp in (select * from emp) loop
if(v_emp.salary<'5000') then
update emp set salary =(v_emp.salary+forRaise) where emp_id=v_emp.emp_id;
end if;
end loop;
end;
调用:
DECLARE
FORRAISE NUMBER;
BEGIN
FORRAISE :=1;
P_TEST(FORRAISE => FORRAISE);
END;
这里要注意两个地方:
(1) 循环中begin和end不是必须的
(2) 这里增加了if语句,其格式比较简单就不细说了。
(3) 这里没有定义游标,在游标的位置直接用select语句代替了。
2. 这里顺便介绍下另外一种循环,while循环,实现同样的功能
CREATE OR REPLACE PROCEDURE p_test(forRaise in number)
as
cursor c is select * from emp;
v_row emp%rowtype;
begin
open c;
fetch c into v_row;
while c%found Loop
if(v_row.salary<'5000') then
update emp set salary =(v_row.salary+forRaise) where emp_id=v_row.emp_id;
end if;
fetch c into v_row;
end loop;
close c;
end;
说明:
(1) 这里需要定义一个游标,还要定义一个emp%rowtype类型的变量,%前面是表名,后面表示这个表的一行,
(2) 在使用游标前还要显示的打开游标,并将其赋值到row中,使用后关闭游标。
(3) C%found表示只有row中有值的时候才会进行循环。
(4) 经过对比发现于while循环相比,for循环更像是C#中的foreach,使用起来方便很多。
(5) 另从9i开始提供的动态游标类型sys_refcursor,以前的版本必须要先创建一个ref cursor的类型,现在多个
3. 现在我们使用程序调用下涨工资的存储过程,这个存储过程是没有返回值的。
OracleConnection conn = new OracleConnection(); //创建一个新连接
conn.ConnectionString = "Data Source='ds';user id='id ';password='pwd';"; OracleCommand cmd = newOracleCommand("P_TEST", conn);
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter p1 = new OracleParameter("forRaise", OracleType.UInt32);
p1.Value = 1;
p1.Direction = System.Data.ParameterDirection.Input;
cmd.Parameters.Add(p1);
conn.Open();
int r=cmd.ExecuteNonQuery();
conn.Close();
这样我们就可以给员工涨工资了,说明:
(1) 虽然给多个人涨了公司,但r的值是1,他只调用了1个存储过程,或者说受影响的只是1个。
(2) 参数P1的名字必须和存储过程中的一样否则会提示:调用 'P_TEST' 时参数个数或类型错误。
4. 现在我们试着从存储过程中得到点结果吧,我先看看我给几个人涨了工资,我每个月一共要多付多少钱了。
改动存储过程:
CREATE OR REPLACE PROCEDURE p_test(forRaise in number,res out number)
is
begin
res:=0;
for v_emp in (select * from emp) loop
if(v_emp.salary<'4000') then
update emp set salary =(v_emp.salary+forRaise) where emp_id=v_emp.emp_id;
res:=res+1;
end if;
end loop;
end;
增加了一个out 的number型,记录改动的次数。然后相应的调整C#程序,获得这个改动的次数。
OracleCommand cmd = new OracleCommand("P_TEST", conn);
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter p1 = new OracleParameter("forRaise", OracleType.UInt32);
p1.Value = 4;
p1.Direction = System.Data.ParameterDirection.Input;
OracleParameter p2 = new OracleParameter("res", OracleType.UInt32);
p2.Value = 10;
p2.Direction = System.Data.ParameterDirection.Output;
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
conn.Open();
int r=cmd.ExecuteNonQuery();
conn.Close();
MessageBox.Show(“你已经给:”+p2.Value.ToString()+“人涨了工资”);
好了,今天就到这,下次返回数据集。
Oracle使用存储过程返回结果集必须使用包,包包括包头和包体两部分,包头是定义部分包体是具体的实现
包头:
CREATE OR REPLACE
PACKAGE pkg_test_select_mul
AS
TYPE myrctype IS REF CURSOR;
PROCEDURE proc(s number, res OUT myrctype);
END pkg_test_select_mul;
这里定义了个一个游标和一个存储过程。
包体:
CREATE OR REPLACE
PACKAGE BODY "PKG_TEST_SELECT_MUL" AS
PROCEDURE proc(s in number,res OUT myrctype)
IS
BEGIN
OPEN res FOR Select emp_id,emp_Name, salary,birthday From emp where salary> s;
END proc;
END PKG_TEST_SELECT_MUL;
这里实现里包头中定义的存储过程,实现了查询工资超过一定数额的人的信息,而游标则不用重新定义了,且存储过程中的参数名必须和定义中的一致。下面我们看一下C#的调用部分。
OracleConnection conn = new OracleConnection(); //创建一个新连接
conn.ConnectionString = "Data Source='" + "MyTest" + "';user id='" + "azkaser" +"';password='" + "sti" + "';"; //写连接串
OracleCommand cmd = new OracleCommand("PKG_TEST_SELECT_MUL.proc", conn);
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter p1 = new OracleParameter("s", OracleType.Number);
p1.Value = 4000;
p1.Direction = ParameterDirection.Input;
OracleParameter p2 = new OracleParameter("res", OracleType.Cursor);
p2.Direction = ParameterDirection.Output;
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
conn.Open();
OracleDataReader myReader = cmd.ExecuteReader();
while (myReader.Read())
{
MessageBox.Show(myReader.GetString(1));
}
conn.Close();
程序将得到的结果存放在OracleDataReader的对象中。