Oracle数据库存储过程
1、Oracle存储过程实例CREATE OR REPLACE PROCEDURE proc_page( p_tblName IN VARCHAR, p_fields IN VARCHAR, p_order IN VARCHAR, p_pageSize IN INT := 10, p_pageIndex IN INT DEFAULT 1, p_rowCount OUT INT, p_returnDesc OUT VARCHAR, p_sel_cur OUT sys_refcursor /*在oracle中不能像mssql那样直接返回结果集查询结果必须以游标形式返回*/)AS /*AS 到 Begin部分用于声明我们在存储过程中可能用到的变量*/ v_sqlCount VARCHAR(200); v_sql VARCHAR(300); v_fields VARCHAR(300);BEGIN IF(length(trim(p_tblName)) = 0) THEN p_returnDesc := '表名必须输入!'; RETURN; END IF; v_sqlCount := 'select count(*) from '||p_tblName; /**动态执行sql语句并将执行结果保存到一个变量中*/ execute immediate v_sqlCount INTO p_rowCount; /**执行分页查询*/ IF(TRIM(p_fields)='*') Then v_fields := p_tblName||'.'||p_fields; END IF; IF(TRIM(p_fields)!='*') Then v_fields := p_fields; END IF; v_sql := 'select * from (select '||v_fields||',rownum rn from '||p_tblName||' where rownum <= '; v_sql := v_sql || (p_pageIndex*p_pageSize) || ' ) tmp where tmp.rn > '; v_sql := v_sql || (p_pageIndex-1)*p_pageSize; DBMS_OUTPUT.put_line(v_sql); OPEN p_sel_cur FOR v_sql; /**将查询结果放入游标中*/END;2、MySQl存储过程实例----（对比Oracle）drop procedure if exists proc_page;/**更改执行标识符，默认情况是分号，此处我们将其更改为$$*/delimiter $$CREATE PROCEDURE proc_page(p_tblName VARCHAR(255),p_fields VARCHAR(255),p_order VARCHAR(255),p_pageSize INT,p_pageIndex INT,p_where VARCHAR(300),OUT p_returnDesc VARCHAR(500),OUT p_rowCount INT)BEGIN /** 参数检查*/IF(LENGTH(LTRIM(RTRIM(p_tblName))) = 0) THEN SET p_returnDesc := '请输入表名!';END IF;/**统计记录总数*/ SET @count_sql := concat('select count(*) into @t_rowcount from ',p_tblName);IF(LENGTH(LTRIM(RTRIM(p_where))) > 0) THEN SET @count_sql := concat(@count_sql,' where ',p_where);END IF;/**执行动态语句*/PREPARE stmt_count From @count_sql;EXECUTE stmt_count;DEALLOCATE PREPARE stmt_count;/**将用户变量的值赋给输出变量*/SET p_rowCount := @t_rowcount;/**获取分页数据*/SET @sql := concat('select ',p_fields,' from ',p_tblName);IF(LENGTH(LTRIM(RTRIM(p_where))) > 0) THEN SET @sql := concat(@sql,' where ',p_where);END IF;SET @sql := concat(@sql,' limit ',(p_pageIndex-1)*p_pageSize,' , ',p_pageSize);PREPARE stmt_page FROM @sql;execute stmt_page;DEALLOCATE PREPARE stmt_page;END;$$delimiter ; ?