存储过程案例

1.创建表

CREATE TABLE book
(
       bookId NUMBER,
       bookName VARCHAR2(50),
       publishHouse Varchar2(50)
)

2.----------有输入参数的存储过程（添加操作）---------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE sp_pro    --创建一个名字为sp_pro的存储过程；
(      --需要输入的参数
       spBookId IN NUMBER,            --图书ID            
       spBookName IN VARCHAR2,        --图书名字
       publishHouse IN VARCHAR2       --图书出版社
)
IS     --连接关键字
BEGIN  --块，以begin开始，以end结尾；
  INSERT INTO book VALUES(spBookId,spBookName,publishHouse);  --执行部分，将输入的值按顺序插入到名为book的表里
END;

CALL sp_pro(3,'book3','china');

3.----------有输入和输出参数的存储过程（查询操作）---------------------------------------------------------------

CREATE OR REPLACE PROCEDURE sp_pro2
(
       spno IN NUMBER,             --in 表示输入的值，不写默认为 in
       spName OUT VARCHAR2,        --out 表示输出的值
       spSal OUT NUMBER,
       spJob OUT VARCHAR2
)
IS 
BEGIN 
  SELECT ename,sal,job INTO spName,spSal,spJob FROM emp WHERE empno = spno; --通过输入的值查询结果并赋给输出的值
END;

4.------------返回结果集的存储过程------------------------------------------------------------------------------------

--1.创建一个包，自定义一个游标类型
CREATE OR REPLACE PACKAGE testpackage AS       --创建一个包
TYPE tesr_cursor IS REF CURSOR;                --在包中定义一个名字为tesr_cursor的类型，这个类型是个游标
END testpackage;                               --关闭包
--2.创建过程
CREATE OR REPLACE PROCEDURE sp_pro3
(
       spNo IN NUMBER,
       p_cursor OUT tesr_cursor        --输出参数p_cursor，它的类型为tesr_cursor（包中定义的类型，是个游标）
)
IS
BEGIN
  OPEN p_cursor FOR SELECT * FROM emp WHERE deptno = spNo;    --打开游标（open  游标名) 接收参数（for sql语句）
END;

5.-----------存储过程分页---------------------------------------------------------------------

--1. 创建一个包，在包中定义一个类型，这个类型是一个游标
CREATE OR REPLACE PACKAGE testpackage AS
TYPE test_cursor IS REF CURSOR;
END testpackage;
--2. 创建分页存储过程
CREATE OR REPLACE PROCEDURE fenye
(
       pageSize IN NUMBER,                  -- 页显示条数（每页显示几条数据）
       pageNow IN NUMBER,                   -- 当前页
       myrows OUT NUMBER,                   -- 总记录数（总共有多少条数据）
       mypagecounts OUT NUMBER,             -- 总页数（总共多少页）
       p_cursor OUT testpackage.test_cursor -- 返回记录集
)
IS
v_sql VARCHAR2(1000);                       -- 存储 sql 语句
v_begin NUMBER := (pageNow-1)*pageSize + 1; -- 计算开始位置
v_end NUMBER := pageSize * pageNow;         -- 计算结束位置
BEGIN 
  v_sql := 'select * from ( select t1.*,rownum rn from 
  ( SELECT * FROM '||tableName||') t1 where rownum<='||v_end||')
   where rn>='||v_begin||'';
  OPEN test_cursor FOR v_sql;                  -- 打开游标,存放结果集
  -- 重新组织一个 sql ，计算总页数
 v_sql:= 'SELECT * FROM '||tableName;
 -- （EXECUTE IMMEDIATE）立即执行sql 将结果赋值给 myrows
 EXECUTE IMMEDIATE v_sql INTO myrows;
 -- 计算总页数，如果不能被整除（取模MOD（除数，被除数）），则结果加一
 IF MOD(myrose,pageSize)=0 THEN
   mypagecontents = myrows/pageSize;
 ELSE 
    mypagecontents = myrows/pageSize+1;
 END IF;
 -- 关闭游标
 CLOSE test_cursor;
END;

6.---------游标（参照类型）------------------------------------------------------------------

DECLARE
--定义游标类型
TYPE sp_emp_cursor IS REF CURSOR;
--定义游标变量
test_cursor sp_emp_cursor;
--定义变量
v_ename emp.ename%TYPE;
v_sal emp.sal%TYPE;
BEGIN
  OPEN test_cursor FOR SELECT ename,sal FROM emp WHERE deptno = &NO;       --打开游标，将游标与SQL关联；
  LOOP                -- 循环取出（相当于do--while(),必须执行一次）
    FETCH test_cursor INTO v_ename,v_sal;    --取出游标里的内容赋值（fetch）
    EXIT WHEN test_cursor%NOTFOUND;     --当游标为空时退出（必须判断退出，否则死循环）(%notfound判断是否为空)
    dbms_output.put_line('名字：'||v_ename ||' 工资：'||v_sal);
  END LOOP;                            
  CLOSE test_cursor;
END;