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;