一、如何创建存储过程procedure
1、创建一个存储过程用于保存已上架商品的数量
| CREATE OR REPLACE PROCEDURE getGoodCount IS |
| SELECT COUNT (*) INTO goodCount FROM table_good where status = '3' ; |
| DBMS_OUTPUT.PUT_LINE( 'good表共有' ||goodCount|| '笔上架商品' ); |
2、根据商品编号,查询商品信息:
| CREATE OR REPLACE PROCEDURE getgoodinfo(goodid IN NUMBER) IS |
| title table_good.good_title%TYPE; |
| SELECT good_title INTO title FROM table_good WHERE table_good.id=goodid; |
| DBMS_OUTPUT.PUT_LINE(goodid|| '号商品名称为' ||title); |
| DBMS_OUTPUT.PUT_LINE( '没有找到该商品' ); |
3、创建有输入和输出参数的过程:
| CREATE OR REPLACE PROCEDURE getgoodinforeturn(goodid IN NUMBER,v_re out VARCHAR2) IS |
| SELECT good_title INTO v_re FROM table_good WHERE table_good.id=goodid; |
| DBMS_OUTPUT.PUT_LINE( '没有找到该商品' ); |
| getgoodinforeturn(2170,title); |
| DBMS_OUTPUT.PUT_LINE(title); |
4、创建输入输出同类型参数的过程:
| CREATE OR REPLACE PROCEDURE getgoodinforeturn2(d IN OUT NUMBER) IS |
| SELECT table_good.goods_sales INTO d FROM table_good WHERE table_good.id=d; |
| DBMS_OUTPUT.PUT_LINE( '没有找到该商品' ); |
| getgoodinforeturn2(sales); |
| DBMS_OUTPUT.PUT_LINE(sales); |
5、默认值的过程
| CREATE OR REPLACE PROCEDURE addGood |
| content VARCHAR2 := 'CLERK' , |
| hdate DATE DEFAULT SYSDATE, |
| INSERT INTO table_good VALUES (id,title,content,mgr,hdate,sal,comm,deptNo); |
| EXEC addEmp(7776, 'zhangsan' , 'CODER' ,7788, '06-1月-2000' ,2000,0,10); |
| EXEC addEmp(7777, 'lisi' , 'CODER' ,7788, '06-1月-2000' ,2000, NULL ,10); |
| 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
| dbms_output.put_line(v_count); |
| dbms_output.put_line( 'game over' ); |
2、while
| dbms_output.put_line(v_count); |
| dbms_output.put_line( 'game over' ); |
3、for
| for v_count in 1 .. 10 loop |
| dbms_output.put_line(v_count); |
| for v_count in reverse 1 .. 10 loop |
| dbms_output.put_line(v_count); |
| dbms_output.put_line( 'game over' ); |
4、goto
| for v_count in 1 .. 10 loop |
| dbms_output.put_line(v_count); |
| for v_count in reverse 1 .. 10 loop |
| dbms_output.put_line(v_count); |
| dbms_output.put_line( 'game over' ); |
四、关于异常 Exception
预定义异常:
| v_id t_12580_o2o_good.id%type := &id; |
| v_sales t_12580_o2o_good.goods_sales%type; |
| select goods_sales into v_sales from t_12580_o2o_good where id = v_id; |
| dbms_output.put_line('the sales is :' || v_sales); |
| dbms_output.put_line('no data found!'); |
| dbms_output.put_line('to many rows!'); |
| dbms_output.put_line(sqlcode || ',' || sqlerrm); |
非预定义异常
02 | v_id t_12580_o2o_good.id%type := &id; |
05 | update t_12580_o2o_good set goods_sales = 1 where id = v_id; |
11 | dbms_output.put_line( 'no data be update' ); |
13 | dbms_output.put_line(sqlcode || '-----' || sqlerrm); |
五、关于游标:
--显式游标:
02 | v_id table_good.id%type; |
03 | v_sales table_good.goods_sales%type; |
05 | select id, goods_sales from table_good where id between 2000 and 3000; |
10 | while c_cursor%found loop |
12 | dbms_output.put_line(v_id || ' sales is : ' || v_sales); |
------------------------------------------------------------------------
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; |
06 | if v_cursor%isopen then |
07 | fetch v_cursor into o2o_record_type; |
08 | else open v_cursor(1000); |
09 | fetch v_cursor into o2o_record_type; |
11 | while v_cursor%found loop |
12 | dbms_output.put_line(o2o_record_type.id || ' sales is: ' || |
13 | o2o_record_type.goods_sales); |
17 | dbms_output.put_line(v_cursor%rowcount); |
--隐式游标
2 | v_deptno emp.deptno%type := &p_deptno; begin |
3 | delete from emp where deptno = v_deptno; |
5 | delete from dept where deptno = v_deptno; |
--给销量低于100的商品增加销售基数100
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; |
06 | select id, goods_sales from table_good where id between 1000 and 2000; |
12 | exit when c_cursor%notfound; |
16 | set goods_sales_base = v_sal_base |
18 | dbms_output.put_line(v_id || '' 's goods_sales_base has been update! the new goods_sales_base is: ' || v_sal_base); |
21 | dbms_output.put_line(c_cursor%rowcount); |
-- FOR 循环操作游标:
03 | select id,good_title,goods_sales from table_good where id between 2000 and 3000; |
05 | for v_record in c_cursor loop |
07 | if v_record.goods_sales <= 1200 then |
08 | update table_good set goods_sales_base = 100 where id = v_record.id; |
09 | dbms_output.put_line(v_record.good_title || '' 's sales_base has update!' ); |
16 | cursor c_cursor(v_status varchar2 default '3' ) is |
17 | select id, goods_sales, good_title |
19 | where status = v_status and id between 2000 and 3000; |
21 | for c_rec in c_cursor(30) loop |
22 | dbms_output.put_line(c_rec.id || ',' || c_rec.good_title || ',' || |
25 | for c_rec in c_cursor loop |
27 | dbms_output.put_line(c_rec.id || ',' || c_rec.good_title || ',' || |