Oracle的plsql 函数、存储过程

---存储函数


--格式
--函数的声明(有参数的写在小括号里)
create or replace function func_name(v_param varchar2)
--返回值类型
return varchar2
is 
--PL/SQL块变量、记录类型、游标的声明(类似于前面的declare的部分)
begin
--函数体(可以实现增删改查等操作，返回值需要return)
       return 'helloworld'|| v_logo;
end;


create or replace fun_name(dept_id number,salary number)
return number
is
       --函数使用过程中，需要声明的变量，记录类型、cursor
begin
  --函数的执行体
exception
  --处理函数执行过程的异常 
end;


---函数的 helloworld: 返回一个 "helloworld" 的字符串
create or replace function hello_func
return varchar2
is
begin
       return 'helloworld';
end;


--执行函数


begin
    dbms_output.put_line(hello_func());
end;


或者： select hello_func() from dual;


---返回一个"helloworld: atguigu"的字符串，其中atguigu 由执行函数时输入。
--返回一个"helloworld: atguigu"的字符串，其中atguigu 由执行函数时输入。
--函数的声明(有参数的写在小括号里)
create or replace function hello_func(v_logo varchar2)
--返回值类型
return varchar2
is 
--PL/SQL块变量的声明
begin
--函数体
       return 'helloworld'|| v_logo;
end;


---执行函数
begin
    dbms_output.put_line(hello_func('weisg'));
end;


--或者
select hello_func('weisg') from dual;


---创建一个存储函数，返回当前的系统时间
create or replace function func1
return date
is
--定义变量
v_date date;
begin
  --函数体
  --v_date := sysdate;
       select sysdate into v_date from dual;
       dbms_output.put_line('我是函数哦');---如果这句不打印，得先把服务打开即先执行set serveroutput on;
       
       return v_date;
end;
--或者




create or replace function func1
return date
is
--定义变量
v_date date;
begin
  --函数体
  v_date := sysdate;
       --select sysdate into v_date from dual;
       --dbms_output.put_line('我是函数哦');
       
       return v_date;
end;


---定义带参数的函数: 两个数相加
create or replace function add_func(a number, b number)
return number
is
begin
       return (a + b);
end;


--执行
select add_func(12,35) from dual;


begin
    dbms_output.put_line(add_func(12, 13));
end;


----定义一个函数: 获取给定部门的工资总和, 要求:部门号定义为参数, 工资总额定义为返回值
create or replace function sum_sal(dept_id number)
       return number
       is
       
       cursor sal_cursor is select salary from employees where department_id = dept_id;
       v_sum_sal number(8) := 0;   
begin
       for c in sal_cursor loop
           v_sum_sal := v_sum_sal + c.salary;
       end loop;       


       --dbms_output.put_line('sum salary: ' || v_sum_sal);
       return v_sum_sal;
end;


---执行函数


begin
    dbms_output.put_line(sum_sal(80));
end;


---关于 OUT 型的参数: 因为函数只能有一个返回值, PL/SQL 程序可以通过 OUT 型的参数实现有多个返回值
--要求: 定义一个函数: 获取给定部门的工资总和 和 该部门的员工总数(定义为 OUT 类型的参数).
--要求: 部门号定义为参数, 工资总额定义为返回值.
--in不写默认就是in


create or replace function sum_sal(dept_id number, total_count out number)
       return number
       is
       
       cursor sal_cursor is select salary from employees where department_id = dept_id;
       v_sum_sal number(8) := 0;   
begin
       total_count := 0;


       for c in sal_cursor loop
           v_sum_sal := v_sum_sal + c.salary;
           total_count := total_count + 1;
       end loop;       


       --dbms_output.put_line('sum salary: ' || v_sum_sal);
       return v_sum_sal;
end;


---执行函数
declare 
  v_total number(3) := 0;


begin
    dbms_output.put_line(sum_sal(80, v_total));
    dbms_output.put_line(v_total);
end;




-------------------------------------------------------------------------------------------
----存储过程




---定义一个存储过程: 获取给定部门的工资总和(通过 out 参数), 要求:部门号和工资总额定义为参数
create or replace procedure sum_sal_procedure(dept_id number, v_sum_sal out number)
       is
       
       cursor sal_cursor is select salary from employees where department_id = dept_id;
begin
       v_sum_sal := 0;
       
       for c in sal_cursor loop
           --dbms_output.put_line(c.salary);
           v_sum_sal := v_sum_sal + c.salary;
       end loop;       


       dbms_output.put_line('sum salary: ' || v_sum_sal);
end;
---执行
declare 
     v_sum_sal number(10) := 0;
begin
     sum_sal_procedure(80,v_sum_sal);
end;
/*
自定义一个存储过程完成以下操作: 
对给定部门(作为输入参数)的员工进行加薪操作, 若其到公司的时间在 (? , 95) 期间,    为其加薪 %5
                                                               [95 , 98)             %3       
                                                               [98, ?)               %1
得到以下返回结果: 为此次加薪公司每月需要额外付出多少成本(定义一个 OUT 型的输出参数).
*/


create or replace procedure add_sal_procedure(dept_id number, temp out number)


is


       cursor sal_cursor is select employee_id id, hire_date hd, salary sal from employees where department_id = dept_id;
       a number(4, 2) := 0;
begin
       temp := 0;       


       for c in sal_cursor loop
           a := 0;    
       
           if c.hd < to_date('1995-1-1', 'yyyy-mm-dd') then
              a := 0.05;
           elsif c.hd < to_date('1998-1-1', 'yyyy-mm-dd') then
              a := 0.03;
           else
              a := 0.01;
           end if;
           
           temp := temp + c.sal * a;
           update employees set salary = salary * (1 + a) where employee_id = c.id;
       end loop;       
end;

create or replace function sum_user_age_like_name(username varchar2)
return number  --返回值类型
is
   cursor age_cursor is select age from mytable where user_id like '%'||username||'%';
   v_age_sum number := 0;--定义返回值并初始化
begin
  for a in age_cursor loop --for循环里存放的是记录对象，必须要通过对象去获取记录里对应的值
    v_age_sum := v_age_sum + a.age;
    dbms_output.put_line('age:'||a.age);
  end loop;--结束循环体
  return v_age_sum;--返回对应值
end;


-----

----存储过程
create or replace procedure sum_user_age_like_name_proce(username varchar2,v_age_sum out number)

is
   cursor age_cursor is select age from mytable where user_id like '%'||username||'%';
   
begin
  v_age_sum := 0;
  for a in age_cursor loop --for循环里存放的是记录对象，必须要通过对象去获取记录里对应的值
    v_age_sum := v_age_sum + a.age;
  end loop;--结束循环体
  dbms_output.put_line('sum age: ' || v_age_sum);
end;


---执行体

declare
  v_age_sum number := 0;
begin
  sum_user_age_like_name_proce('user_id',v_age_sum);
end;

package com.procedure;


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


import oracle.jdbc.OracleTypes;


import com.reflect.utils.DBUtils;


public class CallProcedure {
private static Connection conn = null; // 数据库连接对象  
    private static CallableStatement cs = null;// 存储过程执行对象  
    private static ResultSet rs = null;// 结果集对象 
public CallProcedure(){
conn = DBUtils.getConnect();
}

/** 
     *  
     *  @Discription 执行有参数，无返回值的存储过程 
     *  @return         void 
     *  @param conn 
     *  @throws Exception 
     */ 
/**
* --有参数无返回值 
* 
* create or replace procedure update_mytable_name(v_user_id varchar2,username varchar2)
is
begin
 update mytable m set m.name=username where m.user_id=v_user_id;
end;
---plsql命令执行体
declare 
 v_user_id varchar2(32) := 'user_id_4';
 username varchar2(32) := 'weisg81';
begin
 dbms_output.put_line('存储过程开始执行。。。');
 update mytable m set m.name=username where m.user_id=v_user_id;
end;
* 
*/
public void CallProcedure2(Connection conn){
try{
//指定调用的存储过程  
       cs = conn.prepareCall("{call update_mytable_name(?,?)}");  
       cs.setString(1, "user_id_4");//设置存储过程对应的输入参数  
       cs.setString(2, "weisg234");//对应下标从1 开始  
       //执行存储过程调用  
       cs.execute();
       System.out.println("调用CallProcedure(Connection conn)成功");
}catch(Exception e){
e.printStackTrace();
}
}
/** 
     *  
     *  @Discription 执行无参数，无返回值的存储过程 
     *  @return         void 
     *  @param conn 
     * @throws Exception  
     */
/**
* create or replace procedure insertMytableLine 
   as 
   begin 
         insert into mytable values('weisg','张三','test','管理员',34,23); 
   end;
*/
public void callProcedure(Connection conn) throws Exception  
    {  
        //指定调用的存储过程  
        cs = conn.prepareCall("{call insertMytableLine}");  
        //执行存储过程的调用  
        cs.execute();
        System.out.println("调用callProcedure(Connection conn)成功。。。");
    }


/** 
     *  
     *  @Discription 执行有参数，有返回值的存储过程 
     *  @return         void 
     *  @param conn 
     * @throws Exception  
     */ 
/**
* create or replace procedure deleteLine(v_user_id in varchar2,getCount out number) 
as 
begin 
      delete from mytable m where m.user_id = v_user_id; 
      select count(*) into getCount from mytable; 
end; 
*/
public void callProcedureYY(Connection conn) throws Exception  
    {  
        //指定调用的存储过程  
        cs = conn.prepareCall("{call deleteLine(?,?)}");  
        //设置参数  
        cs.setString(1, "user_id_8");  
        //这里需要配置OUT的参数新型  
        cs.registerOutParameter(2, OracleTypes.NUMBER);  
        //执行调用  
        cs.execute();  
        //输入返回值  
        System.out.println(cs.getString(2));  
    }

/** 
     *  
     *  @Discription 执行有参数，返回集合的存储过程 
     *  @return         void 
     *  @param conn 
     * @throws Exception  
     */

/**
* create or replace package someUtils 
as 
    type cur_ref is ref cursor; 
    procedure selectRows(cur_ref out someUtils.cur_ref); 
end someUtils; 
          
create or replace package body someUtils 
as  
  procedure selectRows(cur_ref out someUtils.cur_ref) 
    as 
      begin 
            open cur_ref for select * from mytable e; 
      end selectRows; 
end someUtils;
*/
public void callProcedureYYL(Connection conn) throws Exception  
    {  
        //执行调用的存储过程  
        cs = conn.prepareCall("{call someUtils.selectRows(?)}");  
        //设置返回参数  
        cs.registerOutParameter(1, OracleTypes.CURSOR);  
        //执行调用  
        cs.execute();  
        // 获取结果集 结果集是一个Object类型，需要进行强制转换 rs = (ResultSet)  
        rs = (ResultSet) cs.getObject(1);  
        //输出返回值  
        while(rs.next())  
        {  
            System.out.println(rs.getInt(1)+"\t"+rs.getString(2));  
        }  
    }

/** 
     *  
     *  @Discription 执行有参数，无返回值的存储过程 
     *  @return         void 
     *  @param conn 
     *  @throws Exception 
     */  
public void callProcedureY(Connection conn) throws Exception  
    {  
        //指定调用的存储过程  
        cs = conn.prepareCall("{call updateName(?,?)}");  
        cs.setInt(1, 7499);//设置存储过程对应的输入参数  
        cs.setString(2, "www");//对应下标从1 开始  
        //执行存储过程调用  
        cs.execute();  
    }  

/** 
     *  
     *  @Discription 执行有参数的函数 
     *  @return         void 
     *  @param conn 
     *  @throws Exception  
     */  
public void callProcedureFY(Connection conn) throws Exception  
    {  
        //指定调用的函数  
        cs = conn.prepareCall("{? = call useOther(?)}");  
        //配置OUT参数信息  
        cs.registerOutParameter(1, OracleTypes.CHAR);  
        //配置输入参数  
        cs.setInt(2, 1111);  
        //执行过程调用  
        cs.execute();  
        //输入返回值  
        System.out.println(cs.getString(1));  
    }  

public static void main(String[] args) {  
        CallProcedure cp = new CallProcedure();  
        try {  
            //开启事务管理  
            conn.setAutoCommit(true);
            //cp.CallProcedure2(conn);
            //cp.callProcedure(conn);  
            //cp.callProcedureY(conn);  
            //cp.callProcedureFY(conn);  
            cp.callProcedureYY(conn);  
            //cp.callProcedureYYL(conn);  
            conn.commit();  
        } catch (Exception e) {  
            e.printStackTrace();  
            try {  
                conn.rollback();  
            } catch (Exception e1) {  
                // TODO Auto-generated catch block  
                e1.printStackTrace();  
            }  
            // TODO: handle exception  
        }finally  
        {  
            DBUtils.closeConn(null, null, conn);  
        }  
    } 

}