Oracle备忘录（十二）--存储过程，数据字典，触发器，jdbc调用存储过程

------***********----数据字典----********-------------
--表分两种：
--用户表：包含商业数据，用户自己维护
--数据字典：管理员维护（用户只能查，不能改）

--数据字典命名规则：
--USER  用户自己的
--ALL   用户可以访问到的
--DBA   管理员视图（管理员用户才能看到）
--V$    性能相关的设置（数据）


select * from dictionary;


--创建一个注释：
comment on table emp is '这是员工信息表';
--查看注释
select *from user_tab_comments where table_name='emp';


select * from session_privs; --查看用户有的权限





-------**********-----触发器-----***********---------
/*
成功插入员工后，自动输出“成功插入一个新员工”
   CREATE  [or REPLACE] TRIGGER  触发器名
   {BEFORE | AFTER}
   {DELETE | INSERT | UPDATE [OF 列名]}
   ON  表名
   PLSQL 块

*/
create or replace trigger sayNewEmp
after insert
on emp
begin
  dbms_output.put_line('成功插入一个新员工');
end;
/


/*
--实施复杂的安全性检查

--禁止在非工作时间 往emp表中插入数据
--   CREATE  [or REPLACE] TRIGGER  触发器名
--   {BEFORE | AFTER}
--   {DELETE | INSERT | UPDATE [OF 列名]}
--   ON  表名
--   PLSQL 块

--周末:to_char(sysdate,'day') in ('星期六','星期日')
--上班前 下班后: to_number(to_char(sysdate,'hh24')) not between 9 and 18
*/
create or replace trigger securityEmp
before insert
on emp
begin
  if to_char(sysdate,'day') in ('星期六','星期日') or 
      to_number(to_char(sysdate,'hh24')) not between 9 and 18 then 
      
    raise_application_error(-20001,'不能在非工作时间插入数据');

  end if;
end;
/


/*
--数据确认

--涨后的薪水不能少于涨前的薪水
--   CREATE  [or REPLACE] TRIGGER  触发器名
--   {BEFORE | AFTER}
--   {DELETE | INSERT | UPDATE [OF 列名]}
--   ON  表名
--   [FOR EACH ROW [WHEN(条件) ] ]
--   PLSQL 块（begin  ...  end; )
*/
create or replace trigger checksal
before update
on emp
--在一次操作表的语句中，每操作成功一行就会触发一次；
--不写的话，表示是表级触发器，则无论操作多少行，都只触发一次
for each row
begin
    --if 涨后的薪水< 涨前的薪水 then 
    if :new.sal < :old.sal then   
      raise_application_error(-20002,'涨后的薪水不能少于涨前的薪水.涨前:'||:old.sal||'  涨后:'||:new.sal);
    end if;
end;
/


--:NEW 和:OLD使用方法和意义：
--new 只出现在insert和update时，
--old只出现在update和delete时。
--在insert时new表示新插入的行数据，update时new表示要替换的新数据。
--old表示要被更改的原来的数据行，delete时old表示要被删除的数据。 



------**********-------存储过程---------************------
--1.存储过程可以使得程序执行效率更高、安全性更好，
--  因为过程建立之后 已经编译并且储存到数据库，
--  直接写sql就需要先分析再执行因此过程效率更高，
--  直接写sql语句会带来安全性问题，如：sql注入 


--2.建立过程不会很耗系统资源，因为过程只是在调用才执行。
--3.存储过程可以用于降低网络流量，存储过程代码直接存储于数据库中，
--  所以不会产生大量T-sql语句的代码流量。
--4.使用存储过程使您能够增强对执行计划的重复使用

/*
create [or replace] PROCEDURE 过程名(参数列表)  
AS 
        PLSQL子程序体；

打印Hello World

调用存储过程
1. exec sayHelloWorld();
2. begin
      sayHelloWorld();
      sayHelloWorld();
   end;
   /
*/

create or replace procedure sayHelloWorld
as
  --说明部分
begin
  dbms_output.put_line('Hello World');
end;
/

/*
create [or replace] PROCEDURE 过程名(参数列表)  
AS 
        PLSQL子程序体；

--为指定的员工涨100块钱  并打印涨前和涨后的薪水
*/
create or replace procedure raiseSalary(eno in number)
as
  psal emp.sal%type;
begin
  --涨前薪水
  select sal into psal from emp where empno=eno;
  
  --涨100
  update emp set sal=sal+100 where empno=eno;
  
  --要不要commit???
  dbms_output.put_line('涨前:'||psal||'   涨后:'||(psal+100));
end;
/


-----------------带out参数的调用-------------------
create or replace
procedure p1(pno in number,pname out varchar2)
as
begin
select ename into pname from emp where empno=pno;
dbms_output.put_line(pname||'              '||pno);


end;
/


方法一：
set serveroutput on
var a varchar2(20);
exec P1(7566,:a);


 
方法二：
set serveroutput on
declare
pname varchar2(20);
begin
p1(&pno,pname);
end;
/


------------------------
/*
--用java程序调用
create [or replace] PROCEDURE 过程名(参数列表)  
AS 
        PLSQL子程序体；
--查询并返回某个员工的姓名 月薪和职位
*/
create or replace procedure queryEmpInfo(eno in number,
                                         pename out varchar2,
                                         psal   out number,
                                         pjob   out varchar2)
as
begin
  select ename,sal,empjob into pename,psal,pjob from emp where empno=eno;
end;
/


------************存储函数-------------**********---
--oracle中的Function有两种:
--一种是系统内置的函数（直接拿来用就行）。
--一种是自定义的(自己写的完成特定功能的)。
--function可以在DML语句中执行。
--如select upper('AaBbCcDd') '大写' from dual 
--其中upper就是系统自带的函数，是把参数中的字符串变成大写字符。

/**
CREATE [OR REPLACE] FUNCTION 函数名(参数列表) 
 RETURN  函数值类型
AS
PLSQL子程序体；

--查询某个员工的年收入

*/
create or replace function queryEmpIncome(eno in number)
return number
as
  psal emp.sal%type;
  pcomm emp.comm%type;
begin
  select sal,comm into psal,pcomm from emp where empno=eno;
  return psal*12+nvl(pcomm,0);
end;
/


调用一：select   函数名  from  dual;


------------******package*****--------------
解决的问题：返回值是一个集合

查询某个部门中 所有员工的所有信息

包头
CREATE OR REPLACE PACKAGE MYPACKAGE AS 

  type empcursor is ref cursor;
  procedure queryEmpList(dno in number,empList out empcursor);

END MYPACKAGE;


包体
CREATE OR REPLACE PACKAGE BODY MYPACKAGE AS

  procedure queryEmpList(dno in number,empList out empcursor) AS
  BEGIN
   
    open empList for select * from emp where deptno=dno;
   
  END queryEmpList;

END MYPACKAGE;

package demo.test;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;
import org.junit.Test;
import demo.utils.JDBCUtils;

public class TestOracle {
/*
 * create or replace procedure queryEmpInfo(eno in number,
                                         pename out varchar2,
                                         psal   out number,
                                         pjob   out varchar2)
 */
	@Test
	public void testProcedure(){
		//{call <procedure-name>[(<arg1>,<arg2>, ...)]}
		String sql = "{call queryEmpInfo(?,?,?,?)}";
		
		Connection conn = null;
		CallableStatement call = null;
		try {
			conn = JDBCUtils.getConnection();
			call = conn.prepareCall(sql);
			
			//对于in参数，赋值
			call.setInt(1, 7839);
			
			//对于out参数,申明
			call.registerOutParameter(2, OracleTypes.VARCHAR);
			call.registerOutParameter(3, OracleTypes.NUMBER);
			call.registerOutParameter(4, OracleTypes.VARCHAR);
			
			//执行
			call.execute();
			
			//取出结果
			String name = call.getString(2);
			double sal = call.getDouble(3);
			String job = call.getString(4);
			
			System.out.println(name);
			System.out.println(sal);
			System.out.println(job);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			JDBCUtils.release(conn, call, null);
		}
	}

/*
 * create or replace function queryEmpIncome(eno in number)
return number
 */
	@Test
	public void testFunction(){
		// {?= call <procedure-name>[(<arg1>,<arg2>, ...)]}
		String sql = "{?=call queryEmpIncome(?)}";
		
		Connection conn = null;
		CallableStatement call = null;
		try {
			conn = JDBCUtils.getConnection();
			call = conn.prepareCall(sql);

			//对于out参数,申明
			call.registerOutParameter(1, OracleTypes.NUMBER);			
			
			//对于in参数，赋值
			call.setInt(2, 7839);
			
			//执行
			call.execute();
			
			//取出结果
			double income = call.getDouble(1);
			System.out.println(income);
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			JDBCUtils.release(conn, call, null);
		}
	}	


	@Test
	public void testCursor(){
		String sql = "{call MYPACKAGE.queryEmpList(?,?)}";
		
		Connection conn = null;
		CallableStatement call = null;
		ResultSet rs = null;
		try {
			conn = JDBCUtils.getConnection();
			call = conn.prepareCall(sql);
			
			//对于in参数，赋值
			call.setInt(1, 20);
			
			//对于out参数,申明
			call.registerOutParameter(2, OracleTypes.CURSOR);
			
			//执行
			call.execute();
			
			//取出结果
			rs = ((OracleCallableStatement)call).getCursor(2);
			while(rs.next()){
				String name = rs.getString("ename");
				double sal = rs.getDouble("sal");
				
				System.out.println(name+"的薪水是"+sal);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			JDBCUtils.release(conn, call, rs);
		}
	}
}