首页 > 代码库 > 存储过程案例
存储过程案例
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;
存储过程案例
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。