关闭

oracle存储过程含参数的插入数据

2413人阅读 评论(0) 收藏 举报
分类:


create or replace
procedure proczipcodebyzipinsert(
  i_zipcode  in  zipcode.zip%type,
  i_city in zipcode.city%type,
  i_state in zipcode.state%type
)
as
  v_zipcode zipcode.zip%type;
  v_city zipcode.city%type;
  v_state zipcode.state%type;
  v_dummy zipcode.zip%type;
begin
 v_zipcode:=i_zipcode;
 v_city:=i_city;
 v_state:=i_state;
 select zip into v_dummy from zipcode where zip=v_zipcode;
 dbms_output.put_line('数据存在您不能插入');
exception
  when no_data_found then
  insert into zipcode values(v_zipcode,v_city,v_state,user,sysdate,user,sysdate);
  dbms_output.put_line('数据插入成功');
  when others then
  dbms_output.put_line('数据错误');
end;



set   serveroutput on

set serveroutput on
begin
 proczipcodebyzipinsert('046000','山西长治','cz');
end;
rollback


1
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:44746次
    • 积分:665
    • 等级:
    • 排名:千里之外
    • 原创:25篇
    • 转载:2篇
    • 译文:0篇
    • 评论:4条
    文章分类
    最新评论