为公司一个项目没有接触过oracle的程序员准备的一个oracle如何使用proc实现增删改查,简单示例:
(
sid number not null primary key,
sname varchar2(10)
)
tablespace test;
declare
a number :=1;
begin
loop
insert into t1 values(a,'snow');
a:=a+1;
exit when a=100;
end loop;
end;
----1.insert
create or replace procedure proc_insert
(
sid number,
sname varchar2
)
is
begin
insert into scott.t1(sid,sname) values(sid,sname);
dbms_output.put_line(' 影响的行数: '||sql%rowcount);
commit;
end
;
set serveroutput on
exec proc_insert(101,'snow');
----2.update
create or replace procedure proc_update
(
isid in number ,
nsname in varchar2
)
is
begin
update scott.t1 set sname=nsname where sid=isid;
If SQL%Found Then
DBMS_OUTPUT.PUT_LINE('更新成功!');
Else
DBMS_OUTPUT.PUT_LINE('更新失败!');
End If;
commit;
end
;
set serveroutput on
exec proc_update(101,'ocpyang');
----3.delete
create or replace procedure proc_delete
(
isid in number
)
is
begin
delete scott.t1 where sid=isid;
If SQL%Found Then
DBMS_OUTPUT.PUT_LINE('删除成功!');
Else
DBMS_OUTPUT.PUT_LINE('删除失败!');
End If;
commit;
end
;
set serveroutput on
exec proc_delete(101);
--------------4.select
--4.1变量(select ....into):单行查询操作
create or replace proced