Oracle 存储过程练习小样例

建立更新的存储过程，输错订单号显示订单不存在：

create or replace PROCEDURE upd_shipdate(orderid NUMBER,shipdate DATE) ISe_no_row EXCEPTION;BEGINupdate ord SET v_shipdate=shipdate where v_orderid=orderid;if SQL%NOTFOUND THEN RAISE e_no_row;END IF;EXCEPTIONWHEN e_no_rowTHENraise_application_error(-20004,'订单不存在');end upd_shipdate;

添加的存储过程：

CREATE OR REPLACE PROCEDURE add_ord (    V_ORDERID NUMBER,    V_ORDERDATE DATE,    V_CUSTID NUMBER,    V_SHIPDATE DATE,    V_TOTAL NUMBER) IS e_integrity EXCEPTION ; e_shipdate EXCEPTION ;  pragma exception_init(e_integrity,-2291);BEGINIF V_SHIPDATE >= V_ORDERDATE THEN    INSERT INTO ORD VALUES    (        V_ORDERID,        V_ORDERDATE,        V_CUSTID,        V_SHIPDATE,        V_TOTAL    ) ;ELSE    RAISE e_shipdate ;END IF;exceptionwhen dup_val_on_index then raise_application_error(-20001,'订单不存在');when e_integrity then raise_application_error(-20002,'该客户不存在');when e_shipdate then raise_application_error(-20003,'交付日期不能早于预订日期');end;

删除存储过程：

create or replace procedure del_ord(v_orderid number)ise_integrity exception;pragma exception_init(e_integrity,-2292);begindelete from ord where v_orderid=v_orderid;if sql%notfound then raise_application_error(-20005,'该订单不存在');end if;exception when e_integrity thenraise_application_error(-20006,'该订单存在相应条款');end;

PL/SQL块：

declare vid number(20);beginselect v_custid into vid from ord where v_orderid=&no;dbms_output.put_line('顾客id:'||vid); //注意格式;&no为SQL/PLUS替代变量exception                    //异常提示when NO_DATA_FOUND then      dbms_output.put_line('请输入正确的雇员号');end;