《Oracle PL/SQL开发指南》学习笔记31——源码调试——函数和过程（第七部分，PL/SQL中调用Java代码 ）

It’s possible to write the programming logic for your stored functions and procedures in Java libraries.

Then, you write a PL/SQL wrapper that accesses the library.

没有注释的源代码，不容易看明白。

所以，让我们平时养成写好注释的习惯吧。

1. 

SQL> ed
已写入 file afiedt.buf

  1  CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED TwoSignersJava AS
  2  // Required class libraries.
  3  import java.sql.*;
  4  import oracle.jdbc.driver.*;
  5  // Define class.
  6  public class TwoSignersJava {
  7  // Connect and verify new insert would be a duplicate.
  8  public static int contactTrigger(Integer memberID)
  9  throws SQLException {
 10  Boolean duplicateFound = false; // Control default value.
 11  // Create a Java 5 and Oracle 11g connection forward.
 12  Connection conn =
 13  DriverManager.getConnection("jdbc:default:connection:");
 14  // Create a prepared statement that accepts binding a number.
 15  PreparedStatement ps =
 16  conn.prepareStatement("SELECT null " +
 17  " FROM contact c JOIN member m " +
 18  " ON c.member_id = m.member_id " +
 19  " WHERE c.member_id = ? " +
 20  " HAVING COUNT(*) > 1");
 21  // Bind the local variable to the statement placeholder.
 22  ps.setInt(1, memberID);
 23  // Execute query and check if there is a second value.
 24  ResultSet rs = ps.executeQuery();
 25  if (rs.next())
 26  duplicateFound = true; // Control override value.
 27  // Clean up resources.
 28  rs.close();
 29  ps.close();
 30  conn.close();
 31  /* Return 1 (true) when two signers and 0 when they don't. */
 32  if (duplicateFound) return 1;
 33* else return 0; }}
 34  /

Java 已创建。

 

2.

 

SQL> /* Formatted on 2018/12/9 20:27:47 (QP5 v5.256.13226.35538) */
SQL> CREATE OR REPLACE FUNCTION two_signers (pv_member_id NUMBER)
  2     RETURN NUMBER
  3  IS
  4     LANGUAGE JAVA
  5     NAME 'TwoSignersJava.contactTrigger(java.lang.Integer) return int' ;
  6  /

函数已创建。

3.

SQL> /* Formatted on 2018/12/9 20:26:31 (QP5 v5.256.13226.35538) */
SQL> SELECT CASE
  2            WHEN two_signers (member_id) = 0 THEN 'Only one signer.'
  3            ELSE 'Already two signers.'
  4         END
  5            AS "Available for Assignment"
  6  FROM contact c JOIN member m USING (member_id)
  7  WHERE c.last_name = 'Sweeney'
  8  OFFSET 1 ROWS FETCH FIRST 1 ROWS ONLY;

Available for Assign
--------------------
Already two signers.