Oracle 存储过程实例集锦

一、如何创建存储过程procedure

1、创建一个存储过程用于保存已上架商品的数量

CREATE ORREPLACE PROCEDURE getGoodCount IS

goodCount int;

BEGIN

SELECT COUNT(*)INTO goodCount FROMtable_good where status = '3';

DBMS_OUTPUT.PUT_LINE('good表共有'||goodCount||'笔上架商品');

END getGoodCount;

call getGoodCount();

 

2、根据商品编号，查询商品信息：

CREATE ORREPLACE PROCEDURE getgoodinfo(goodid IN NUMBER)IS

title table_good.good_title%TYPE;

BEGIN

SELECT good_titleINTO title  FROMtable_good WHERE table_good.id=goodid;

DBMS_OUTPUT.PUT_LINE(goodid||'号商品名称为'||title);

EXCEPTION

WHEN NO_DATA_FOUNDTHEN

DBMS_OUTPUT.PUT_LINE('没有找到该商品');

END;

call getgoodinfo(2170);

 

3、创建有输入和输出参数的过程：

CREATE ORREPLACE PROCEDURE getgoodinforeturn(goodid IN NUMBER,v_re out VARCHAR2)IS

BEGIN

SELECT good_titleINTO v_re  FROMtable_good WHERE table_good.id=goodid;

EXCEPTION

WHEN NO_DATA_FOUNDTHEN

DBMS_OUTPUT.PUT_LINE('没有找到该商品');

END;

DECLARE

title VARCHAR2(100);

BEGIN

getgoodinforeturn(2170,title);

DBMS_OUTPUT.PUT_LINE(title);

END;

 

4、创建输入输出同类型参数的过程：

CREATE ORREPLACE PROCEDURE getgoodinforeturn2(d IN OUT NUMBER) IS

BEGIN

SELECT table_good.goods_salesINTO d  FROMtable_good WHERE table_good.id=d;

EXCEPTION

WHEN NO_DATA_FOUNDTHEN

DBMS_OUTPUT.PUT_LINE('没有找到该商品');

END;

DECLARE

sales Number(10);

BEGIN

sales:=4003;

getgoodinforeturn2(sales);

DBMS_OUTPUT.PUT_LINE(sales);

END;

 

5、默认值的过程

CREATE ORREPLACE PROCEDURE addGood

(

id NUMBER,

title VARCHAR2,

content  VARCHAR2 :='CLERK',

mgr  NUMBER,

hdate DATE DEFAULT SYSDATE,

sal  NUMBER  DEFAULT1000,

comm  NUMBER  DEFAULT0,

deptNo NUMBER  DEFAULT30

)

AS

BEGIN

INSERT INTOtable_good VALUES(id,title,content,mgr,hdate,sal,comm,deptNo);

END;

EXEC addEmp(7776,'zhangsan','CODER',7788,'06-1月-2000',2000,0,10); --没有使用默认值

EXEC addEmp(7777,'lisi','CODER',7788,'06-1月-2000',2000,NULL,10); --可以使用NULL值

EXEC addEmp(7778,'wangwu',mgr=>7788); --使用默认值

EXEC addEmp(mgr=>7788,empNo=>7779,eName=>'sunliu'); --更改参数顺序

...... ...... 还可以update,delete等等

二、常用命令

1、删除存储过程

DROP PROCEDURE Proc_Name;

 

2、查看过程状态

SELECT object_name,status  FROM USER_OBJECTS WHERE object_type='PROCEDURE';

 

3、重新编译过程

ALTER PROCEDURE Proc_Name COMPILE;

 

4、查看过程代码

SELECT * FROM USER_SOURCE WHERE TYPE='PROCEDURE';

三、关于循环：

1、loop

declare

v_count number(2) := 0;

begin

loop

-- 循环开始

v_count := v_count + 1;

dbms_output.put_line(v_count);

exit whenv_count = 10; --当v_count等于10 时退出循环。

end loop; -- 循环结束

dbms_output.put_line('game over');

end;

 

2、while

declare

v_count number(2) := 0;

begin

while v_count < 10 loop

-- 当v_count 小于10 执行循环

v_count := v_count + 1;

dbms_output.put_line(v_count);

end loop;

dbms_output.put_line('game over');

end;

 

3、for

declare

v_count number(2) := 0; -- 此值对for 循环执行的次数没有影响

begin

for v_count in 1 .. 10 loop

-- 此v_count 变量不是上面声明的变量，循环10次

dbms_output.put_line(v_count);

end loop;

for v_count in reverse 1 .. 10 loop

--反序输出

dbms_output.put_line(v_count);

end loop;

dbms_output.put_line('game over');

end;

 

4、goto

declare

v_count number(2) := 0;

begin

for v_count in 1 .. 10 loop

dbms_output.put_line(v_count);

end loop;

for v_count in reverse 1 .. 10 loop

dbms_output.put_line(v_count);

if v_count = 5 then

goto endofloop;-- 跳至循环体外标签处执行，循环结束

end if;

end loop;

<<endofloop>>

dbms_output.put_line('game over');-- 此处必须要有语句可以执行，若没有也要写 'null;'

end;

四、关于异常 Exception

预定义异常：

declare

v_id    t_12580_o2o_good.id%type := &id;

v_sales t_12580_o2o_good.goods_sales%type;

begin

select goods_sales into v_sales from t_12580_o2o_good where id = v_id;

dbms_output.put_line('the sales is :' || v_sales);

exception

when no_data_found then

dbms_output.put_line('no data found!');

when too_many_rows then

dbms_output.put_line('to many rows!');

when others then

dbms_output.put_line(sqlcode || ',' || sqlerrm);

end;

 

非预定义异常

01

declare

02	v_id t_12580_o2o_good.id%type := &id;

03	no_result exception;

04

begin

05	update t_12580_o2o_goodset goods_sales = 1 where id = v_id;

06	if sql%notfound then

07	raise no_result;

08

end if;

09

exception

10	when no_resultthen

11	dbms_output.put_line('no data be update');

12	when others then

13	dbms_output.put_line(sqlcode || '-----'|| sqlerrm);

14

end;

五、关于游标：

--显式游标:

01

declare

02	v_id    table_good.id%type;

03	v_sales table_good.goods_sales%type;

04	cursor c_cursoris

05	select id, goods_salesfrom table_good whereid between 2000 and 3000;

06

begin

07	open c_cursor;-- 打开游标

08	fetch c_cursor

09	into v_id, v_sales;--获取数据

10	while c_cursor%found loop

11	-- 当游标里有数据就执行下面的打印操作

12	dbms_output.put_line(v_id || ' sales is : '|| v_sales);

13	fetch c_cursor

14	into v_id, v_sales;

15

end loop;

16	close c_cursor;

17

end;

 

------------------------------------------------------------------------

 

01

declare

02	-- 记录类型变量，在游标中存放所有列的数据。

03	o2o_record_type table_good%rowtype;

04	cursor v_cursor(v_sales table_good.goods_sales%type)is select * from table_good where goods_sales > v_sales;

05

begin

06	if v_cursor%isopen then

07	fetch v_cursorinto o2o_record_type;

08	else  openv_cursor(1000); -- 若没有打开，就先打开，再取数据

09	fetch v_cursorinto o2o_record_type;

10

end if;

11	while v_cursor%found loop

12	dbms_output.put_line(o2o_record_type.id ||' sales is: ' ||

13	o2o_record_type.goods_sales);

14	fetch v_cursor

15	into o2o_record_type;

16

end loop;

17	dbms_output.put_line(v_cursor%rowcount); -- 游标里的数据的行数

18	close v_cursor;

19

end;

 

--隐式游标

1

declare

2	v_deptno emp.deptno%type := &p_deptno;begin

3	delete fromemp where deptno = v_deptno;   -- 删除 emp 表中对应部门号下的员工信息

4	if sql%notfound then            -- 如果对应部门没有员工，则删除 dept 表中对应的部门号，

5	delete fromdept where deptno = v_deptno;

6

commit;

7

end if;

8	rollback;      -- 如果对应部门下有员工，则回滚至删除前

9

end;

 

--给销量低于100的商品增加销售基数100

01

declare

02	v_id       table_good.id%type;

03	v_sal      table_good.goods_sales%type;

04	v_sal_base table_good.goods_sales_base%type;

05	cursor c_cursoris

06	select id, goods_salesfrom table_good whereid between 1000 and 2000;

07

begin

08	open c_cursor;

09

loop

10	fetch c_cursor

11	into v_id, v_sal;

12	exit whenc_cursor%notfound;

13	if v_sal <= 100 then

14	v_sal_base := 100;

15	update table_good

16	set goods_sales_base = v_sal_base

17	where id = v_id;

18	dbms_output.put_line(v_id || '''s goods_sales_base has been update! the new goods_sales_base is: '|| v_sal_base);

19

end if;

20

end loop;

21	dbms_output.put_line(c_cursor%rowcount);

22	close c_cursor;

23

end;

 

-- FOR 循环操作游标：

view source print ?

01

declare

02	cursor c_cursoris

03	select id,good_title,goods_salesfrom table_good whereid between 2000 and 3000;

04

begin

05	for v_recordin c_cursor loop

06	-- 隐式地打开游标，取数据

07	if v_record.goods_sales <= 1200 then

08	update table_goodset goods_sales_base = 100where id = v_record.id;

09	dbms_output.put_line(v_record.good_title ||'''s sales_base has update!');

10

end if;

11	-- 隐式地关闭游标

12

end loop;

13

end;

14	-- 带参数的游标：

15

declare

16	cursor c_cursor(v_status varchar2default '3')is

17	select id, goods_sales, good_title

18	from table_good

19	where status = v_statusand id between2000 and 3000;

20

begin

21	for c_rec in c_cursor(30) loop

22	dbms_output.put_line(c_rec.id || ','|| c_rec.good_title || ','||

23	c_rec.goods_sales);

24

end loop;

25	for c_rec in c_cursor loop

26	-- 此处将会用默认值 20；

27	dbms_output.put_line(c_rec.id || ','|| c_rec.good_title || ','||

28	c_rec.goods_sales);

29

end loop;

30

end;