Oracle存储过程模板

1、无参数存储过程

 1 CREATE OR REPLACE PROCEDURE stu_proc AS
 2     --声明语句段
 3     v_name VARCHAR2(20);
 4 BEGIN
 5     --执行语句段
 6     SELECT o.sname INTO v_name FROM student o WHERE o.id=4;
 7     dbms_output.put_line(v_name);
 8 EXCEPTION
 9     --异常处理语句段
10     WHEN NO_DATA_FOUND THEN dbms_output.put_line('NO_DATA_FOUND');
11 END;

2、仅带入参的存储过程

 1 CREATE OR REPLACE PROCEDURE stu_proc(v_id IN student.id%type) AS
 2     --声明语句段
 3     v_name varchar2(20);
 4 BEGIN
 5     --执行语句段
 6     SELECT o.sname INTO v_name FROM student o where o.id=v_id;
 7     dbms_output.put_line(v_name);
 8 EXCEPTION
 9     --异常处理语句段
10     WHEN NO_DATA_FOUND THEN dbms_output.put_line('NO_DATA_FOUND');
11 END;

3、仅带出参的存储过程

 1 CREATE OR REPLACE PROCEDURE stu_proc(v_name OUT student.sname%type) AS
 2     --声明语句段
 3 BEGIN
 4     --执行语句段
 5     SELECT o.sname INTO v_name FROM student o where o.id=1;
 6     dbms_output.put_line(v_name);
 7 EXCEPTION
 8     --异常处理语句段
 9     WHEN NO_DATA_FOUND THEN dbms_output.put_line('NO_DATA_FOUND');
10 END;

4、带入参和出参的存储过程

 1 --此种存储过程不能直接用call来调用，这种情况的调用将在下面oracle函数调用中说明
 2 CREATE OR REPLACE PROCEDURE stu_proc(v_id IN student.id%type, v_name OUT student.sname%type) AS
 3     --声明语句段
 4 BEGIN
 5     --执行语句段
 6     SELECT o.sname INTO v_name FROM student o where o.id=v_id;
 7     dbms_output.put_line(v_name);
 8 EXCEPTION
 9     --异常处理语句段
10     WHEN NO_DATA_FOUND THEN dbms_output.put_line('NO_DATA_FOUND');
11 END;