oracle-plsql练习

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


--编写一个过程，可以输入一个雇员
--如果雇员的补助不是0，就在原来的基础上增加100
--如果是0就把补助设为200
create or replace procedure sp_pro6(spName varchar2) is
--定义部分
v_comm emp.comm%type;
begin
  select comm into v_comm from emp where ename=spName;
  --判断
  if v_comm<>0 then
    update emp set comm=v_comm+100 where ename=spName;
    else
       update emp set comm=200 where ename=spName;
    end if;
end;

--编写一个过程，可以输入一个雇员编号，如果该雇员的职位是
--PRESIDENT 就给他的工资增加1000，如果该雇员的职位是MANAGER
--就给他的工资增加500，其他职位的雇员工资增加200
create or replace procedure sp_pro6(spNo number) is
  v_job emp.job%type;
 begin
   select job into v_job from emp where EMPNO=spNo;
   if v_job='PRESIDENT'
     then update emp set sal=sal+1000 where EMPNO=spNo;
   elsif v_job='MANAGER'
     then update emp set sal=sal+500 where EMPNO=spNo;
   else
     update emp set sal=sal+200 where EMPNO=spNo;  
    end if;
 end;
 
--请编写一个过程，可输入用户名，并循环添加10个用户到users表中，用户编号从1开始
create or replace procedure sp_pro6(spName varchar2)is
v_time number:=0 ;
begin
  loop
   insert into student(XH,XM) values(v_time+1,spName) ;
   --判断是否要退出循环
   v_time:=v_time+1;
  exit when v_time=10;
  end loop;  
end;

--请编写一个过程，可输入用户名，并循环添加10个用户到users表中，用户编号从1开始
--while循环
create or replace procedure sp_pro6(spName varchar2)is
v_time number:=10 ;
begin
  while v_time<20
  loop
   insert into student(XH,XM) values(v_time+1,spName) ;
   --判断是否要退出循环
   v_time:=v_time+1;
  end loop;  
end;

--练习
create table book(
bookId number,
bookName varchar2(50),
publishHouse varchar2(50)
)
 --编写过程
 --int:表示这是一个输入参数，为默认值
 --out:表示一个输出参数
create or replace procedure sp_pro7(spBookId in number,spBookName in varchar2,sppublishHouse in varchar2) is
begin
  insert into book values(spBookId,spBookName,sppublishHouse);
end;
 --在java中调用
 
--有输入和输出的存储过程
create or replace procedure sp_pro8(spno in number,spName out varchar2) is
begin
  select ename into spName from emp where empno=spno;
end;

 --返回结果集的过程
 --1.创建一个包，在该包中，我定义类型 test_cursor,是一个游标
create or replace package tespackage is
type test_cursor is ref cursor;
end;
 --2.创建过程
 create or replace procedure sp_pro9(spNo in number,p_cursor out tespackage.test_cursor)is
 begin
   open p_cursor for select * from emp where deptno=spNo;
 end;
 --3.如何在java中调用该过程
 
 
 --分页练习
 create or replace procedure sp_pro10(
 TableName in varchar,MaxCount in integer,PageNow in integer,
 CountNum out integer,PageCount out integer,resultlist out tespackage.test_cursor
 )is
 v_sql varchar(1000);
 begin
   v_sql:='select count(*) from '|| TableName;
   --执行sql语句，并把返回值，赋给my
   execute immediate v_sql into CountNum;
   if mod(CountNum,MaxCount)=0
     then PageCount:=floor(CountNum/MaxCount);
   else
     PageCount:=floor(CountNum/MaxCount)+1;
   end if;
   
    v_sql:='select * from(select t1.*,rownum rn from '|| TableName ||' t1 where rownum<='|| MaxCount*PageNow ||') where rn>'||MaxCount*(PageNow-1);
    open resultlist for v_sql;
    --关闭游标
    --close resultlist;
 end;

java:

package com.sp;

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

public class test1 {
	//调用一个无返回值的过程
	public static void main(String[] args) throws Exception {
		// TODO Auto-generated method stub
		Class.forName("oracle.jdbc.driver.OracleDriver");
		Connection ct=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","scott","hyj84884824");
//		CallableStatement cs=ct.prepareCall("{call sp_pro7(?,?,?)}");
//		cs.setInt(1, 1);
//		cs.setString(2, "Thinking in Java");
//		cs.setString(3, "我的出版社");
//		cs.execute();
//		cs.close();
//		ct.close();
		
		//如何调用有返回值的过程
//		CallableStatement cs=ct.prepareCall("{call sp_pro8(?,?)}");
//		
//		 //给第一个问好辅助
//		cs.setInt(1, 7788);
//		 //给第二个?赋值
//		cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
//		 //执行
//		cs.execute();
//		 //取出返回值,要注意问号的顺序
//		String name=cs.getString(2);
//		System.out.println("7788的名字是"+name);
//		cs.close();
//		ct.close();
		
		//获得返回结果集
//		CallableStatement cs=ct.prepareCall("{call sp_pro9(?,?)}");
//		cs.setInt(1, 10);
//		cs.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);
//		cs.execute();
//		//得到结果集
//		ResultSet rs=(ResultSet) cs.getObject(2);
//		while(rs.next()){
//			System.out.println(rs.getString(1)+"  "+rs.getString(2));
//		}
//		cs.close();
//		ct.close();
		
		
		//分页练习
		CallableStatement cs=ct.prepareCall("{call sp_pro10(?,?,?,?,?,?)}");
		cs.setString(1, "emp");
		cs.setInt(2, 4);
		cs.setInt(3, 1);
		cs.registerOutParameter(4, oracle.jdbc.OracleTypes.INTEGER);
		cs.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER);
		cs.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);
		cs.execute();
		
		System.out.println("共有"+cs.getString(4)+"条记录");
		System.out.println("共有"+cs.getString(5)+"页");
		ResultSet rs=(ResultSet) cs.getObject(6);
		while(rs.next()){
			System.out.println(rs.getString(1)+" "+rs.getString(2));
		}
		
		rs.close();
		cs.close();
		ct.close();
	}

}