当前位置：Gxlcms > 数据库问题 > Oracle 存储过程实例集锦

Oracle 存储过程实例集锦

时间：2021-07-01 10:21:17 帮助过：5人阅读

一、如何创建存储过程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 sourceprint?
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;

Oracle 存储过程实例集锦

标签：

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！