-----------只有输入参数的存储过程-----------------
CREATE OR REPLACE PROCEDURE TESTA(param_id IN Number,
param_name IN VARCHAR2)
AS
BEGIN
INSERT INTO employee (empid,empname)
VALUES (param_id, param_name);
END TESTA;
call student.TESTA(108,'testA');
select * from employee;
----------同时带有输入和输出参数的存储过程-------------
CREATE OR REPLACE PROCEDURE TESTB(param_id IN Number,
param_name OUT VARCHAR2)
AS
BEGIN
SELECT empname INTO param_name FROM student.employee
WHERE empid= param_id;
END TESTB;
declare empname varchar2(10);
begin
TESTB(1001,empname);
dbms_output.put_line(empname);
end;
-- 使用ref cursor返回多行存储过程执行结果---------
CREATE OR REPLACE PACKAGE TESTPACKAGE AS
TYPE Test_CURSOR IS REF CURSOR;
end TESTPACKAGE;
CREATE OR REPLACE PROCEDURE TESTC(param_name IN VARCHAR2,p_CURSOR out TESTPACKAGE.Test_CURSOR)
AS
BEGIN
OPEN p_CURSOR FOR SELECT * FROM student.employee
where empname like param_name;
END TESTC;
---分页存储过程----------------
create or replace procedure pagedproc(
p_cursor out testpackage.Test_CURSOR,lowerNum in numeric,higherNum in numeric)
as
begin
open p_cursor for
select * from
(select rownum num,r.* from
(select * from employee order by empid) r where rownum <=higherNum)
where num >=lowerNum;
end pagedproc;
转载于:https://blog.51cto.com/xiangzhengxian/1593050