Oracle中的存储过程简单例子

---创建表
create table TESTTABLE
(
  id1  VARCHAR2(12),
  name VARCHAR2(32)
)
select t.id1,t.name from TESTTABLE t
insert into TESTTABLE (ID1, NAME)
values (‘1‘, ‘zhangsan‘);


insert into TESTTABLE (ID1, NAME)
values (‘2‘, ‘lisi‘);


insert into TESTTABLE (ID1, NAME)
values (‘3‘, ‘wangwu‘);


insert into TESTTABLE (ID1, NAME)
values (‘4‘, ‘xiaoliu‘);


insert into TESTTABLE (ID1, NAME)
values (‘5‘, ‘laowu‘);
---创建存储过程
create or replace procedure test_count
as
v_total number(1);
begin
  select count(*) into v_total from TESTTABLE;
  DBMS_OUTPUT.put_line(‘总人数：‘||v_total);
end;
--准备
--线对scott解锁：alter user scott account unlock; 
--应为存储过程是在scott用户下。还要给scott赋予密码
---alter user scott identified by tiger;
---去命令下执行
EXECUTE test_count;
----在ql/spl中的sql中执行
begin
  -- Call the procedure
  test_count;
end;

Oracle中的存储过程简单例子,布布扣,bubuko.com