• 存储过程 有用


    --- 给某员工涨工资(打印涨前的工资和涨后的工资)  可以改成商品的涨价前和涨价后

    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

  • 相关阅读:
    Facade
    Adapter
    Bridge
    Factory
    Singleton
    Decorator
    Template Method
    设计模式
    寻找最小的k个数
    java并发编程(4)--线程池的使用
  • 原文地址:https://www.cnblogs.com/shan13936/p/13801053.html
Copyright © 2020-2023  润新知