Stored procedures and stored functions

Creating a stored procedure

create or replace procedure sayhelloword
as

-- Explanation
begin
dbms_out_put_line("Hello world");

end;
/

Copy the code

Calling a stored procedure

1. exec sayhelloword();
2. begin sayhelloword(); sayhelloword(); The end;

Creating a storage function

create or replace function queryempincome(eno in  number)
return number
as
Define variables to hold employees' salaries and bonuses
psal emp.sal%type;

pcome emp.comm%type;

begin
  -- Get the employee's monthly salary and bonus
  select sal,comm into psal,pcomm from emp where empno=eno;
  
  -- Straight back to annual income
  return psal*12 + nvl(pcomm,0);
  
 end;

Copy the code

How do I access stored procedures and stored functions in a program in Java

// Database connection (JDBC)


package com.claa.javabasic.Connect;

import java.sql.*;

/ * * *@Author: claa
 * @Date: 2021/06/27 09:43
 * @Description: Oracle Jdbc connection */
public class JDBCUtilsO {
    private static String driver = "oracle.jdbc.OracleDriver";

    private static String url="JDBC: oracle: thin: @ 192.168.56.101:1521: wouldn";

    private static String user ="scott";

    private  static String password ="tiger";

    // Register the database driver
    static {
        try{
            Class.forName(driver);
        }catch (ClassNotFoundException e){
          throw newExceptionInInitializerError(e); }}// Get the database driver
    public  static Connection getConnection(a){
        try{
           return DriverManager.getConnection(url,user,password)
        }catch (SQLException e){
            e.printStackTrace();
        }
        return null;
    }


    // Release database resources
    public static void Release(Connection conn, Statement st, ResultSet rs) {
        try {
            if(rs! =null){ rs.close(); }}catch (SQLException e) {
            e.printStackTrace();
        }finally {
             drs = null;
        }

        try {
            if(st! =null){ st.close(); }}catch (SQLException e) {
            e.printStackTrace();
        }finally {
            st = null;
        }

        try {
            if(st! =null){ st.close(); }}catch (SQLException e) {
            e.printStackTrace();
        }finally {
            conn = null; }}}Copy the code

// Call stored procedures and functions
package com.claa.javabasic.Connect;

import oracle.jdbc.OracleTypes;
import org.junit.Test;

import java.sql.CallableStatement;
import java.sql.Connection;

/ * * *@Author: claa
 * @Date: 2021/06/27 * seated@Description: * /
public class TestProcedure {

    @Test
    public void testProcedure(a){
        // Stored procedures
        String sql = "{call queryempincome(? ,? ,? ,?) }";

        // Store the function
        //String sql = "{? =call queryempincome(? ,? ,? ,?) } ";

        Connection conn = null;

        CallableStatement call = null;
        try{
            // Get a connection
            conn = JDBCUtilsO.getConnection();

            // Create a Statement through the connection
            call = conn.prepareCall(sql);

            // For the in argument
            call.setInt(1.7839);

            // For the Out argument
            call.registerOutParameter(2, OracleTypes.VARCHAR);
            call.registerOutParameter(3,OracleTypes.NUMBER);
            call.registerOutParameter(4,OracleTypes.VARCHAR);

            // Perform the call
            call.execute();

            // Retrieve the result
            String name = call.getString(2);
            System.out.println(name);

        }catch(Exception e){
           e.printStackTrace();
        }finally {
            JDBCUtilsO.Release(conn,call,null); }}}Copy the code

If there is something wrong, we welcome to discuss it together. Finally, welcome to follow my wechat account “Tao Tao Zhi hai”. Your likes, favorites and forwarding are the biggest encouragement to me.