--- 给某员工涨工资（打印涨前的工资和涨后的工资） 可以改成商品的涨价前和涨价后
create or replace procedure updateSal(eno in number ,psal in number)
is
oldsal number;
newsal number;
begin
-- 打印涨前的工资
select sal into oldsal from emp where empno = eno;
dbms_output.put_line('涨前的工资：'||oldsal);
-- 涨工资
update emp set sal = sal + psal where empno = eno;
commit;
-- 打印涨后的工资
select sal into newsal from emp where empno = eno;
dbms_output.put_line('涨后的工资：'||newsal);
end;
-- 访问只有输入参数的存储过程
call updateSal(7788,100);
如何创建存储过程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;
<>
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;
非预定义异常 01declare
02v_id t_12580_o2o_good.id%type := &id;
03no_result exception;
04begin
05update t_12580_o2o_goodset goods_sales = 1 where id = v_id;
06if sql%notfound then
07raise no_result;
08end if;
09exception
10when no_resultthen
11dbms_output.put_line('no data be update');
12when others then
13dbms_output.put_line(sqlcode || '-----'|| sqlerrm);
14end;
五、关于游标：
--显式游标: 01declare
02v_id table_good.id%type;
03v_sales table_good.goods_sales%type;
04cursor c_cursoris
05select id, goods_salesfrom table_good whereid between 2000 and 3000;
06begin
07open c_cursor;-- 打开游标
08fetch c_cursor
09into v_id, v_sales;--获取数据
10while c_cursor%found loop
11-- 当游标里有数据就执行下面的打印操作
12dbms_output.put_line(v_id || ' sales is : '|| v_sales);
13fetch c_cursor
14into v_id, v_sales;
15end loop;
16close c_cursor;
17end;
------------------------------------------------------------------------ 01declare
02-- 记录类型变量，在游标中存放所有列的数据。
03o2o_record_type table_good%rowtype;
04cursor v_cursor(v_sales table_good.goods_sales%type)is select * from table_good where goods_sales > v_sales;
05begin
06if v_cursor%isopen then
07fetch v_cursorinto o2o_record_type;
08else openv_cursor(1000); -- 若没有打开，就先打开，再取数据
09fetch v_cursorinto o2o_record_type;
10end if;
11while v_cursor%found loop
12dbms_output.put_line(o2o_record_type.id ||' sales is: ' ||
13o2o_record_type.goods_sales);
14fetch v_cursor
15into o2o_record_type;
16end loop;
17dbms_output.put_line(v_cursor%rowcount); -- 游标里的数据的行数
18close v_cursor;
19end;
--隐式游标 1declare
2v_deptno emp.deptno%type := &p_deptno;begin
3delete fromemp where deptno = v_deptno; -- 删除 emp 表中对应部门号下的员工信息
4if sql%notfound then -- 如果对应部门没有员工，则删除 dept 表中对应的部门号，
5delete fromdept where deptno = v_deptno;
6commit;
7end if;
8rollback; -- 如果对应部门下有员工，则回滚至删除前
9end;
--给销量低于100的商品增加销售基数100 01declare
02v_id table_good.id%type;
03v_sal table_good.goods_sales%type;
04v_sal_base table_good.goods_sales_base%type;
05cursor c_cursoris
06select id, goods_salesfrom table_good whereid between 1000 and 2000;
07begin
08open c_cursor;
09loop
10fetch c_cursor
11into v_id, v_sal;
12exit whenc_cursor%notfound;
13if v_sal <= 100 then
14v_sal_base := 100;
15update table_good
16set goods_sales_base = v_sal_base
17where id = v_id;
18dbms_output.put_line(v_id || '''s goods_sales_base has been update! the new goods_sales_base is: '|| v_sal_base);
19end if;
20end loop;
21dbms_output.put_line(c_cursor%rowcount);
22close c_cursor;
23end;
-- FOR 循环操作游标： view sourceprint? 01declare
02cursor c_cursoris
03select id,good_title,goods_salesfrom table_good whereid between 2000 and 3000;
04begin
05for v_recordin c_cursor loop
06-- 隐式地打开游标，取数据
07if v_record.goods_sales <= 1200 then
08update table_goodset goods_sales_base = 100where id = v_record.id;
09dbms_output.put_line(v_record.good_title ||'''s sales_base has update!');
10end if;
11-- 隐式地关闭游标
12end loop;
13end;
14-- 带参数的游标：
15declare
16cursor c_cursor(v_status varchar2default '3')is
17select id, goods_sales, good_title
18from table_good
19where status = v_statusand id between2000 and 3000;
20begin
21for c_rec in c_cursor(30) loop
22dbms_output.put_line(c_rec.id || ','|| c_rec.good_title || ','||
23c_rec.goods_sales);
24end loop;
25for c_rec in c_cursor loop
26-- 此处将会用默认值 20;
27dbms_output.put_line(c_rec.id || ','|| c_rec.good_title || ','||
28c_rec.goods_sales);
29end loop;
30end