- 语法:
create or replace procedure 存储过程名称
(参数1 类型,参数2 类型....)
is/as
变量声明部分;
begin
逻辑处理部分;
return 要返回的变量;
[exception
异常处理]
end;
参数类型说明:
1)in 传入参数(默认)
2)out 传出参数,通常用于返回程序运行结果
3)in out 传入传出参数
- 示例:
--新建stu_id序列。
create sequence seq_students start with 1010;
create or replace procedure proc_insertstu(v_name varchar2,
v_age number,
v_gender varchar2,
v_class_id number) is
begin
insert into students
(stu_id, name, age, gender, class_id)
values
(seq_students.nextval, v_name, v_age, v_gender, v_class_id);
commit;
end;
- PL/SQL中调用:
call proc_insertstu('李四',33,'男',3);
- 带传出参数的存储过程
create or replace procedure proc_insertstu2(v_name varchar2,
v_age number,
v_gender varchar2,
v_class_id number,
v_stu_id out number) is
begin
select seq_students.nextval into v_stu_id from dual;
insert into students
(stu_id, name, age, gender, class_id)
values
(v_stu_id, v_name, v_age, v_gender, v_class_id);
commit;
end;
- 带传出参数的存储过程调用
declare
v_stu_id number;--定义传出参数变量
begin
proc_insertstu2('李丽', 34, '女', 4, v_stu_id);
dbms_output.put_line(v_stu_id);
end;
查看students表