一.存储过程
1.定义
指存储在数据库中供所有用户程序调用的子程序叫存储过程,存储函数。
2.语法
create [or replace] PROCEDURE 过程名(参数列表)
AS PLSQL子程序体;
3.实例
1).入门程序--创建存储过程
create or replace PROCEDUREhelloworldAS
BEGINdbms_output.put_line('Hello,world');END;
调用存储过程:
BEGINHELLOWORLD();END;
2)例:给员工涨工资
CREATE OR REPLACE
PROCEDURE raiseSalary(eno IN NUMBER) ISpsal emp.sal%TYPE;BEGIN
SELECT sal INTO psal FROM emp WHERE empno =eno;UPDATE emp SET sal=sal+100 WHERE empno =eno;
dbms_output.put_line('涨前:'||psal||',涨后:'||(psal+100));END raiseSalary;
二.存储函数
例):查询某个员工的年收入
CREATE OR REPLACE
FUNCTION queryEmpIncome (eno IN NUMBER)RETURN NUMBER
ASpsal 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 queryEmpIncome;
存储过程和函数的IN和OUT
在过程和函数中可以通过out指定一个或多个输出参数,利用out参数返回多个值
使用原则:若只返回一个值用函数,若返回多个值用存储过程out返回
三.存储过程与存储函数的区别
四.Java程序调用存储过程
1.调用存储函数
public void testProcedure(){
String sql = "{call queryEmpInformation(?,?,?,?)}";
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+",薪水:"+sal+",职位:"+job);
}catch (Exception e){
e.printStackTrace();
}finally {
JDBCUtils.release(conn,call,null);
}
}
2.调用存储函数
public voidtestFun(){
String sql= "{?=call queryEmpIncome(?)}";
Connection conn= null;
CallableStatement call= null;try{
conn=JDBCUtils.getConnection();
call=conn.prepareCall(sql);
call.registerOutParameter(1,OracleTypes.NUMBER);
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);
}
}
五.包
1.包头
CREATE OR REPLACEPACKAGE mypackageASTYPE empcursorIS REF CURSOR;PROCEDURE queryEmpList(dno IN NUMBER,empList OUT empcursor);end mypackage;
2.包体
CREATE OR REPLACEPACKAGE BODY MYPACKAGEAS
PROCEDURE queryEmpList(dno IN NUMBER,empList OUT empcursor)AS
BEGIN
OPEN empList FOR SELECT * FROM emp WHERE deptno =dno;END;end MYPACKAGE;
3.java调用
public voidpackageTest(){
String sql= "{call mypackage.queryEmpList(?,?)}";
Connection conn= null;
CallableStatement call= null;try{
conn=JDBCUtils.getConnection();
call=conn.prepareCall(sql);
call.setInt(1,10);
call.registerOutParameter(2,OracleTypes.CURSOR);
call.execute();//执行
OracleCallableStatement oraCall =(OracleCallableStatement) call;
ResultSet rs= oraCall.getCursor(2);while(rs.next()){
String ename= rs.getString("ename");double sal = rs.getDouble("sal");
System.out.println("姓名:"+ename+",工资:"+sal);
}
}catch(Exception e){
e.printStackTrace();
}finally{
JDBCUtils.release(conn,call,null);
}
}
六.触发器
1.定义
数据库的触发器是一个表关联的,存储的PL/SQL程序。每当一个特定的数据库操作语句(Insert,update,delete)在指定表上发出时,Oracle自动执行触发器中定义的语句序列。
2.触发器的应用
1)数据确认
2)实施复杂的安全性检查
3) 做审计,跟踪表上所做的数据操作等
4) 数据的备份和同步
3.触发器的语法
CREATE [OR REPLACE] TRIGGER 触发器名
{BEFORE | AFTER}
{DELETE | INSERT | UPDATE [OF 列名称]}
ON 表名
[FOR EACH ROW [WHERE条件]]
PLSQL块
4.触发器的类型
1)语句级触发器
在指定操作之前或之后执行一次,不管这条语句影响了多少行
2)行级触发器
触发语句作用的每一条记录都被触发,在行级触发器中使用:old和:new伪记录变量。
5.入门程序:插入后打印
CREATE triggerfirstTrigger
AFTERINSERT
ONempDECLARE
BEGINdbms_output.put_line('成功插入新员工');END;