-- 删除存储过程
drop procedure EMP_COUNT(存储过程名称)
-- 查询存储过程
select distinct name From user_source where type = 'PROCEDURE'
----------------------------------
-- 存储过程的基本语法:
CREATE OR REPLACE PROCEDURE 存储过程名(param1 in type,param2 out type)
IS
变量1 类型(值范围);
变量2 类型(值范围);
BEGIN
select count(*) into 变量1 from 表名 where 列名=param1;
if (判断条件) then
select 列名 into 变量2 from 表名 where 列名=param1;
DBMS_OUTPUT.put_line('打印信息');
Elsif (判断条件) then
dbms_output.put_line('打印信息');
Else
Raise 异常名 (NO_DATA_FOUND);
End if;
Exception
When others then
Rollback;
END;
-------------------------------------------------------------------------
-- 没有参数的过程
create or replace procedure test_count
is
v_total int;
v_date varchar(20);
begin
select count(*) into v_total from dual;
select to_char(sysdate,'yyyy-mm-dd') into v_date from dual;
DBMS_OUTPUT.put_line('总人数:'||v_total);
DBMS_OUTPUT.put_line('当前时间:'||v_date);
end;
-- 存储过程调用方法
begin
test_count;
end;
-------------------------------------------
-- 仅带输出参数的过程
create or replace procedure test_count2(v_name out varchar2)
is
begin
select c_name into v_name from tb_store where c_stono='1101';
exception
when no_data_found then dbms_output.put_line('no_data_found');
end;
-- 调用方法:
declare
v_name varchar(200);
begin
test_count2(v_name);
dbms_output.put_line(v_name);
end;
-------------------------------------------------
-- 带输入参数和输出参数的存储过程
create or replace procedure test_count3(v_id in int,v_name out varchar2)
is
begin
select c_name into v_name from tb_store where c_stono=v_id;
dbms_output.put_line(v_name);
exception
when no_data_found then dbms_output.put_line('no_data_found');
end;
-- 调用方法:
declare
v_name varchar(200);
begin
test_count3('1101',v_name);
end;