关于oracle存储过程的一些知识点

--清空数据库中所有表的数据
create or replace procedure truncateAllTables as
v_sql varchar2(2000);
CURSOR cur is
       select table_name from user_tables order by table_name;
begin
--dbms_output.put_line(‘1111‘);
for rows in cur loop
v_sql := ‘truncate table ‘ || rows.table_name;
--dbms_output.put_line(v_sql);
execute immediate v_sql;
end loop;
end;

exec truncateAllTables;

create or replace procedure proc_CpNumber(cpNumber out varchar2) is
begin
   ....
   -- 一些调试信息
   dbms_output.put_line(‘==0‘||cpNumber);
   dbms_output.put_line(‘==0‘||length(cpNumber));
   --赋值
   select ‘E‘||(to_number(substr(cpNumber,2,10))+1) into cpNumber from dual;
   ......
end;

--调用带有参数的存储过程
declare
cp_number varchar2(2000);
begin
proc_CpNumber(cp_number);
dbms_output.put_line(cp_number);
end;
/

public class OracleDBHelper {
    static final String DRIVER = "oracle.jdbc.driver.OracleDriver";
    //开发环境
    static final String URL = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
    
    static final String USER = "test123";
    static final String PWD = "test123";
    
    private OracleDBHelper() {
        
    }
    private static Connection con = null;

    public static Connection getConnection() {
        if(con == null) {
            try {
                Class.forName(DRIVER);
                con = DriverManager.getConnection(URL, USER, PWD);
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return con;
    }
    
    public static void main(String[] args) throws Exception {
        CallableStatement cStatement = OracleDBHelper.getConnection().prepareCall("{call proc_cpnumber(?)}");
        cStatement.setString(1, "aa");
        cStatement.registerOutParameter(1, java.sql.Types.VARCHAR);
        cStatement.execute();
        System.out.println(cStatement.getString(1));
        OracleDBHelper.getConnection().close();
    }
}

<select id="proc_cpnumber" statementType="CALLABLE" parameterType="java.util.HashMap" resultType="java.util.HashMap">   
<![CDATA[   
    {call proc_cpnumber(#{eNumber,mode=OUT,jdbcType=VARCHAR})}   
]]>   
</select>

    public synchronized String getNumber() {
        String eNumber = "";
        Map map=new HashMap();
        getSqlSession().selectOne("proc_cpnumber",map);
        eNumber=(String)map.get("eNumber");
        return eNumber;
    }