以下emp表是orcl数据库实例自带的表。
执行存储过程前,必须先执行 SET serveroutput ON;
show error; 可以显示具体的执行错误信息。
1. 不带参数
A. 创建语句:
create or replace PROCEDURE EMP_COUNT
AS
V_TOTAL NUMBER(10);
BEGIN
SELECT COUNT(*) INTO V_TOTAL FROM emp;
DBMS_OUTPUT.PUT_LINE('雇员总人数为:'||V_TOTAL);
END;
B. 执行语句:
不带参数时,EMP_COUNT() 可简写成EMP_COUNT
(1)
EXECUTE EMP_COUNT(); --或者: EXEC EMP_COUNT();
(2)
BEGIN
EMP_COUNT();
END;
(3)
DECLARE
BEGIN
EMP_COUNT();
END;
C. 执行结果
运行脚本(F5),结果如下:
anonymous block completed
雇员总人数为:14
其他实例:
create or replace procedure proc_user
AS icount number;
begin
select count(*) into icount from tb_user;
dbms_output.put_line('icount'||icount);
EXCEPTION
when too_many_rows then
dbms_output.put_line('返回值多于1行');
when others then
dbms_output.put_line('在proc_user过程中出错');
DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
end;
2. 带参数
create or replace procedure runbyparmeters_1 (
isal in emp.sal%type,
sjob in varchar)
as icount number;
begin
select count(*) into icount from emp where sal>isal and job=sjob;
DBMS_OUTPUT.PUT_LINE('符合条件的记录有 '|| icount || '条');
exception
when too_many_rows then
DBMS_OUTPUT.PUT_LINE('返回值多于1行');
when others then
DBMS_OUTPUT.PUT_LINE('在RUNBYPARMETERS过程中出错!');
end;
EXECUTE runbyparmeters_1(1100,'CLERK')
或者
declare
realsal emp.sal%type;
realjob varchar(40);
begin
realsal:=1100;
realjob:='CLERK';
runbyparmeters_1(realsal,realjob);
-- runbyparmeters_1(sjob=>realjob,isal=>realsal);
END;
输出结果:
anonymous block completed
符合条件的记录有 1条