首页 > 代码库 > 存储过程案例

存储过程案例

1.创建表

1 CREATE TABLE book2 (3        bookId NUMBER,4        bookName VARCHAR2(50),5        publishHouse Varchar2(50)6 )

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

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

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

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

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

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

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

 1 --1. 创建一个包,在包中定义一个类型,这个类型是一个游标 2 CREATE OR REPLACE PACKAGE testpackage AS 3 TYPE test_cursor IS REF CURSOR; 4 END testpackage; 5 --2. 创建分页存储过程 6 CREATE OR REPLACE PROCEDURE fenye 7 ( 8        pageSize IN NUMBER,                  -- 页显示条数(每页显示几条数据) 9        pageNow IN NUMBER,                   -- 当前页10        myrows OUT NUMBER,                   -- 总记录数(总共有多少条数据)11        mypagecounts OUT NUMBER,             -- 总页数(总共多少页)12        p_cursor OUT testpackage.test_cursor -- 返回记录集13 )14 IS15 v_sql VARCHAR2(1000);                       -- 存储 sql 语句16 v_begin NUMBER := (pageNow-1)*pageSize + 1; -- 计算开始位置17 v_end NUMBER := pageSize * pageNow;         -- 计算结束位置18 BEGIN 19   v_sql := ‘select * from ( select t1.*,rownum rn from 20   ( SELECT * FROM ‘||tableName||‘) t1 where rownum<=‘||v_end||‘)21    where rn>=‘||v_begin||‘‘;22   OPEN test_cursor FOR v_sql;                  -- 打开游标,存放结果集23   -- 重新组织一个 sql ,计算总页数24  v_sql:= ‘SELECT * FROM ‘||tableName;25  -- (EXECUTE IMMEDIATE)立即执行sql 将结果赋值给 myrows26  EXECUTE IMMEDIATE v_sql INTO myrows;27  -- 计算总页数,如果不能被整除(取模MOD(除数,被除数)),则结果加一28  IF MOD(myrose,pageSize)=0 THEN29    mypagecontents = myrows/pageSize;30  ELSE 31     mypagecontents = myrows/pageSize+1;32  END IF;33  -- 关闭游标34  CLOSE test_cursor;35 END;


6.---------游标(参照类型)------------------------------------------------------------------

 1 DECLARE 2 --定义游标类型 3 TYPE sp_emp_cursor IS REF CURSOR; 4 --定义游标变量 5 test_cursor sp_emp_cursor; 6 --定义变量 7 v_ename emp.ename%TYPE; 8 v_sal emp.sal%TYPE; 9 BEGIN10   OPEN test_cursor FOR SELECT ename,sal FROM emp WHERE deptno = &NO;       --打开游标,将游标与SQL关联;11   LOOP                -- 循环取出(相当于do--while(),必须执行一次)12     FETCH test_cursor INTO v_ename,v_sal;    --取出游标里的内容赋值(fetch)13     EXIT WHEN test_cursor%NOTFOUND;     --当游标为空时退出(必须判断退出,否则死循环)(%notfound判断是否为空)14     dbms_output.put_line(‘名字:‘||v_ename ||‘ 工资:‘||v_sal);15   END LOOP;                            16   CLOSE test_cursor;17 END;

 

存储过程案例