存储过程 学习笔记二

原创 2013年12月04日 09:56:01

--编写一个过程,可以输入一个雇员的名字,如果该雇员的工资低于2000,就给该雇员的工资增加10%
create or replace procedure sp_pro6(spName varchar2) is
v_sal scott.emp.sal%type;
begin
select sal into v_sal from scott.emp where ename=spName;
if v_sal<2000 then
update scott.emp set sal=sal*1.1 where ename=spName;
end if;
end;

SQL> call sp_pro6('JAMES');
SQL> COMMIT;

二重条件分支if--else
可以输入一个雇员的名字,如果该雇员的补助不是0就在原来的基础上增加100;如果补助为0就把补助设为200
create or replace procedure sp_pro7(spName varchar2) is
v_comm scott.emp.comm%type;
begin
select comm into v_comm from scott.emp where ename=spName;
if v_comm<>0 then
update scott.emp set comm=comm+100 where ename=spName;
else
update scott.emp set comm=comm+200 where ename=spName;
end if;
end;

多重条件分支if--then--elsif--else
注意:elsif没有e
可以输入一个员工的编号,如果该雇员的职位是president就给他增加工资1000,如果该雇员的职位是manager就给他增加500,其余的员工增加200元。
create or replace procedure sp_pro8(spNo number)is
v_job scott.emp.job%type;
begin
select job into v_job from scott.emp where empno=spNo;
if v_job='PRESIDENT' then
update scott.emp set sal=sal+1000 where empno=spNo;
elsif v_job='MANAGER' then
update scott.emp set sal=sal+500 where empno=spNo;
else
update scott.emp set sal=sal+200 where empno=spNo;
end if;
end;

循环语句
loop至少执行一次
编写一个过程,输入一个用户名,并循环添加10个到users表中,用户编号从1开始增加
create table hbusers(
userNO number,
userName varchar2(40)
);

create or replace procedure sp_pro9(spName varchar2)
is
--:=表示赋值
v_num number:=1;
begin
loop
 insert into hbusers values(v_num,spName);
 --判断是否要推出循环
 exit when v_num=10;
 --自增长
 v_num:=v_num+1;
end loop;
end;

使用while循环(只有条件为true时才执行循环体语句)
例子:编写一个过程,输入一个用户名,并循环添加10个到users表中,用户编号从11开始增加
create or replace procedure sp_pro10(spName varchar2)
is
--:=表示赋值
v_num number:=11;
begin
while v_num<=20 loop
 --执行体
 insert into hbusers values(v_num,spName);
 --自增长
 v_num:=v_num+1;
end loop;
end;

使用for循环
create or replace procedure sp_pro11(spName varchar2) is
begin
 for i in reverse 21..30 loop
 insert into hbusers values(i,'彪彪');
 end loop;
end;


oracle分页
1、select * from scott.emp;
2、select p1.*,rownum from (select * from scott.emp) p1;
3、select p1.*,rownum from (select * from scott.emp) p1 where rownum <=10;
4、select * from (select p1.*,rownum rn from (select * from scott.emp) p1 where rownum <=10) where rn>=6;

开始编写分页的过程
开发一个包
create or replace package testpackage as
type test_cursor is ref cursor;
end testpackage;

create or replace procedure fenye
(tableName in varchar2,--表明
pageSize in number,--页面数
pageNow in number,--当前页
myRows out number,--总记录数
myPageCount out number,--总页数
p_cursor out testpackage.test_cursor--返回记录子集
)is
--定义部分
--定义一个sql字符串(用于拼接)
v_sql varchar2(1000);
--定义两个整数
v_begin number:=(pageNow-1)*pageSize+1;
v_end number:=pageNow*pageSize;
begin
--执行部分
v_sql:='select * from (select p1.*,rownum rn from (select * from  '||tableName||') p1 where rownum <='||v_end||') where rn>='||v_begin;
--把游标和SQL关联
open p_cursor for v_sql;
--计算myRows和myPageCount
--组织一个sql
v_sql:='select count(*) from '||tableName;
--执行sql,并把返回值赋给myrows
execute immediate v_sql into myrows;
--计算myPageCount
if mod(myrows,pageSize)=0 then
myPageCount := myrows/pageSize;
else
myPageCount := myrows/pageSize+1;
end if;
--关闭游标
--close p_cursor;
end;

要求:按照薪水从低到高排序之后再取数据的方法

create or replace procedure fenye2
(tableName in varchar2,--表明
pageSize in number,--页面数
pageNow in number,--当前页
myRows out number,--总记录数
myPageCount out number,--总页数
p_cursor out testpackage.test_cursor--返回记录子集
)is 
--定义部分
--定义一个sql字符串(用于拼接)
v_sql varchar2(1000);
--定义两个整数
v_begin number:=(pageNow-1)*pageSize+1;
v_end number:=pageNow*pageSize;
begin
--执行部分
v_sql:='select * from (select p1.*,rownum rn from (select * from  '||tableName||' order by sal) p1 where rownum <='||v_end||') where rn>='||v_begin;
--把游标和SQL关联
open p_cursor for v_sql;
--计算myRows和myPageCount
--组织一个sql
v_sql:='select count(*) from '||tableName;
--执行sql,并把返回值赋给myrows
execute immediate v_sql into myrows;
--计算myPageCount
if mod(myrows,pageSize)=0 then
myPageCount := myrows/pageSize;
else 
myPageCount := myrows/pageSize+1;
end if;
--关闭游标
--close p_cursor;
end;

 

 

package hb.com;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import oracle.jdbc.OracleTypes;

public class FenyeTestProcedureDemo4 {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		Connection conn = null;
		Statement stmt = null;
		CallableStatement proc = null;
		ResultSet rs = null;
		int myRows=0;
		int myPageCount=0;
		try {
			DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
			System.out.println("driver is ok");
			conn = DriverManager.getConnection(
					"jdbc:oracle:thin:@localhost:1521:orcl", "scott", "admin");
			proc = conn.prepareCall("{call fenye2(?,?,?,?,?,?)}");
			proc.setString(1, "emp");
			proc.setInt(2, 5);
			proc.setInt(3, 2);
			proc.registerOutParameter(4, OracleTypes.INTEGER);
			proc.registerOutParameter(5, OracleTypes.INTEGER);
			proc.registerOutParameter(6, OracleTypes.CURSOR);
			proc.execute();
			myRows = proc.getInt(4);
			System.out.println("myRows"+myRows);
			myPageCount = proc.getInt(5);
			System.out.println("myPageCount"+myPageCount);
			rs = (ResultSet) proc.getObject(6);
			
			while (rs.next()) {
				System.out.println(rs.getInt(1));
				System.out.println("薪水:"+rs.getInt("sal"));
			}
			
		} catch (SQLException e) {
			try {
				// 如果出现异常将操作回滚,这样就能保证数据的一致性
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
		}

		if (stmt != null) {
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

}

 

备注:我这里调用的是scott的表,用huangbiao用户好像没有办法操作成功,原因不明

Oracle存储过程学习笔记

Oracle存储过程学习笔记
  • wahaa591
  • wahaa591
  • 2015年07月01日 17:29
  • 693

Hawq学习笔记 --- 存储过程生成数据

Hawq学习笔记 --- 存储过程生成数据
  • u012965373
  • u012965373
  • 2017年01月17日 12:19
  • 511

kafka学习笔记(2)

kafka的经典问题有这几个: 1.kafka节点之间如何备份? 2.kafka消息是否会丢失?为什么? 3.kafka最合理的配置是什么? 4.kafka的leader选举机制是什么? 5...
  • m0_37587447
  • m0_37587447
  • 2017年11月21日 15:37
  • 21

存储过程新人学习

存储过程(Stored Procedure)是一组为了完成特定功能的T-SQL语句集合,经编译后存储在SQL Server服务器中,利用存储过程可以加速SQL语句的执行。 在应用程序中,使用存储过程读...
  • mss359681091
  • mss359681091
  • 2016年04月06日 11:17
  • 1986

PCIe 学习笔记(二)

pci note 2 -v0.1 2014.11.18 *** draft  本文继续pci note 1, 介绍pci_create_root_bus函数, 内核版本为3.18-rc1 ...
  • scarecrow_byr
  • scarecrow_byr
  • 2014年12月01日 22:19
  • 1722

sqlserver存储过程学习(通俗易懂)

sqlserver存储过程学习(通俗易懂)
  • wanlong360599336
  • wanlong360599336
  • 2016年09月02日 17:02
  • 9632

存储过程学习总结

SQLSERVER存储过程使用说明书   引言 首先介绍一下什么是存储过程:存储过程就是将常用的或很复杂的工作,预先用SQL语句写好并用一个指定的名称存储起来,并且这样的语句是放在数据库中的,还...
  • zhoujn90
  • zhoujn90
  • 2013年08月27日 20:37
  • 3316

Oracle 存储过程学习

Oracle 存储过程学习 目录 Oracle 存储过程 1 Oracle存储过程基础知识 1 Oracle存储过程的基本语法 2 关于Oracle存储过程的若干问题备忘 4 1....
  • u010361662
  • u010361662
  • 2016年05月20日 21:57
  • 13603

随机过程的概念以及统计特性(读书笔记)

随机过程的概念以及统计特性通俗的来说,随机过程其实就是一组因为时间t而产生关联的随机变量的所组成的序列。改序列可以是连续的,也可以是离散的。同时,每个随机变量也同样可以是连续的或者离散的。 随机过程...
  • qq_33843297
  • qq_33843297
  • 2017年10月12日 12:24
  • 182

学习存储过程

创建过程: create or replace procedure update_emp is begin update emp set ename = 'hhdhjf' where empno ...
  • yuan_lo
  • yuan_lo
  • 2013年10月27日 09:57
  • 595
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:存储过程 学习笔记二
举报原因:
原因补充:

(最多只允许输入30个字)