您的位置: 首页  >  IT文章  >  PLSQL学习札记二

PLSQL学习札记二

分类: IT文章 • 2023-11-07 21:27:33
PLSQL学习笔记二

--编写一个过程，可以输入一个雇员的名字，如果该雇员的工资低于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用户好像没有办法操作成功，原因不明

相关推荐