plsql的存储过程，存储函数，触发器

于 2019-10-29 11:29:43 发布
阅读量422 收藏 2
点赞数
分类专栏： oracle
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/strong_yu/article/details/51778184
版权

一存储过程

1.什么是存储过程，为什么使用存储过程

存储过程是存储在数据库中供用户调用的子程序，由于存储过程是存储在数据库中已经编译好的程序，在调用的时候，不必再次进行编译，从而提高了程序的运行效率.

2.存储过程的语法

创建存储过程的语法

create [or replace] procedure procedure_name ([{in| out}]  data_type,parameter [{in| out}]  data_type..)

(is|as)

pl/sql子程序；

in:表示输入参数                                     out:输出参数

调用存储过程有两种方法：

第一种:execute(exec)  procedure_name(参数列表)

第二种:begin

procedure_name(参数列表)

end;

3.无参数的存储过程

 

create or replace
PROCEDURE empename as
emp_ename emp.ename%type;
emp_sal emp.sal%type;
begin
select ename ,sal into emp_ename,emp_sal from emp where empno=7934;
dbms_output.put_line(emp_ename||'的工资是'|| emp_sal);
end;

注意:声明变量不需要写declare

调用存储过程

set serveroutput on;
begin
empename;
end;

4.带参数的存储过程

create or replace
PROCEDURE empename(enum in number,emp_ename out emp.ename%type,emp_sal out emp.sal%type) 
as
begin
select ename ,sal into emp_ename,emp_sal from emp where empno=enum;
end;


调用存储过程：

set serveroutput on;
declare
emp_ename emp.ename%type;
emp_sal emp.sal%type;
begin
empename(7369, emp_ename, emp_sal);
dbms_output.put_line(emp_ename||'的工资是'|| emp_sal);
end;


二存储函数

函数为一命名的存储程序，可带参数，并返回一计算值

 

1.存储函数的语法

create oy replace function function_name([{in| out}]  data_type,parameter [{in| out}]  data_type..)

return 返回值的数据类型

begin

函数要做的操作

end;

in:表示输入参数                                     out:输出参数

 

create or replace
function emp_function(eno in number) RETURN NUMBER
as 
esal NUMBER;
ecommon NUMBER;
BEGIN
select sal ,comm into esal, ecommon from emp where empno=eno;
return esal+nvl(ecommon,0); 
end;


调用函数

set serveroutput on;
declare
sal_sum NUMBER;
begin
sal_sum:=emp_function(7369);
dbms_output.put_line('7369的年薪是'||sal_sum);
end;

 

 

三.使用jdbc怎么调用存储函数和存储过程

jdbc调用存储函数和存储过程使用CallableStatement接口，sql语句如下；

{?= call <procedure-name>[(<arg1>,<arg2>, ...)]}------------------------------------->存储函数

{call <procedure-name>[(<arg1>,<arg2>, ...)]}----------------------------------->存储过程

 

调用存储过程

 

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import oracle.jdbc.OracleTypes;
import com.hotelmanagersystem.util.JdbcUtil;

public class maintest {
	public static void main(String[] args) {
     Connection conn=null;
     CallableStatement sts=null;
     ResultSet rs=null;
     try {
		conn=JdbcUtil.getConnection();
		sts=conn.prepareCall("call empename(?,?,?)");
		//设置输入参数
		sts.setInt(1,7369);
		//注册输出参数
	    sts.registerOutParameter(2,OracleTypes.VARCHAR);
	    sts.registerOutParameter(3,OracleTypes.NUMBER);
		sts.execute();
		System.out.println(sts.getString(2)+"的工资是"+sts.getInt(3));
	} catch (ClassNotFoundException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}finally{
		JdbcUtil.closeAll(rs, sts, conn);
	}
     
	}

}


调用存储函数;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import oracle.jdbc.OracleTypes;
import com.hotelmanagersystem.util.JdbcUtil;

public class maintest {
	public static void main(String[] args) {
     Connection conn=null;
     CallableStatement sts=null;
     ResultSet rs=null;
     try {
		conn=JdbcUtil.getConnection();
		sts=conn.prepareCall("{?=call emp_function(?)}");
		//设置输入参数
		sts.setInt(2,7369);
		//注册输出参数
		sts.registerOutParameter(1,OracleTypes.NUMBER);
		sts.execute();
		System.out.println("年薪是"+sts.getInt(1));
	} catch (ClassNotFoundException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}finally{
		JdbcUtil.closeAll(rs, sts, conn);
	}
     
	}

}


注意：调用函数的大括号不能省略，调用存储过程可以省略。

四.包和包体

1.包和包体

前面我们看到的都是返回一行数据，如果返回多行数据怎么处理了，我们就可以使用游标，但是在存储过程和函数中使用游标只能在包中，现在来看包是什么

包就是一组相关的过程，函数，变量，常量和游标等PL/SQL 程序设计元素的组合，包分为包头和包体,

包头里面定义了包里面应该包含的 变量，过程，函数 等

在包头里面需要定义包里面的函数，存储过程等

包体里面对包头中定义的函数存储过程等进行实现

包头的定义语法:

CREATE    OR   REPLACE   PACKAGE package_name IS

//游标，变量，过程，函数等

 TYPE mycursor IS REF CURSOR;
 PROCEDURE myproc(outcursor IN OUT mycursor);
END package_name;

包体:

CREATE   OR    REPLACE    PACKAGE BODY mypack IS
PROCEDURE myproc(
        outcursor IN OUT mycursor
    )
IS
BEGIN
OPEN outcursor FOR
SELECT*FROM Student WHERE ROWNUM<10;
RETURN;
END myproc;
END;

包头代码:

CREATE OR REPLACE
PACKAGE MYPACKAGE AS
type empcursor is ref cursor;
PROCEDURE queryempList(dno in NUMBER,empList out empcursor);
  /* TODO 在此输入程序包声明 (类型, 异常错误, 方法等) */

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;


2.JDBC调用包和包体

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

import oracle.jdbc.OracleTypes;
import oracle.jdbc.internal.OracleCallableStatement;

import com.hotelmanagersystem.util.JdbcUtil;

public class maintest {
	public static void main(String[] args) {
     Connection conn=null;
     CallableStatement sts=null;
     ResultSet rs=null;
     try {
		conn=JdbcUtil.getConnection();
               
                //注意sql语句的写法
                 sts=conn.prepareCall("{call MYPACKAGE.queryempList(?,?)}");
		//设置输入参数
		sts.setInt(1,10);
		//注册输出参数,参数是游标型的
		sts.registerOutParameter(2,OracleTypes.CURSOR);
		sts.execute();
		//转换成oracle的statement类型，就会有cursor方法
	    rs=((OracleCallableStatement)sts).getCursor(2);
	    while(rs.next()){
	    String name =rs.getString("ename");
	    int sal = rs.getInt("sal");
	    System.out.println(name+"的薪水是"+sal);
	    }
	} catch (ClassNotFoundException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}finally{
		JdbcUtil.closeAll(rs, sts, conn);
	}
     
	}
}


五：触发器

pl/sql是一个与表相关的存储的pl/sql程序，每当一个特定的数据库操作语句(update,insert,delete)在指定的表上发出时，oracle自动的执行触发器中定义的语句序列。

创建触发器的语法格式:

create   [or replace]  trigger 触发器名

{before|after}

{delate | insert | updsate [of]列名 }

on 表名

[for each row [where( 条件)]]

pl/sql块

触发语句与伪列记录变量的值

触发语句

:old

:new

Insert

所有字段都是空(null)

将要插入的数据

Update

更新以前该行的值

更新后的值

delete

删除以前该行的值

所有字段都是空(null)

 

strong_yu
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值