Oracle Advanced Programming, oraclesql Advanced Programming

Oracle Advanced Programming, oraclesql Advanced Programming

Oracle's advanced programming mainly involves the use of the ten objects in the database. Here, I will share with you some of my notes on these skills. Hope to help you!

Before reading this article, you can also refer to my previous database introduction:

Basic Oracle DDL operations
Basic Oracle Data Types
Oracle database backup and recovery

Next we will introduce Oracle's advanced programming.

1. The stored procedure returns a single value parameter.
 
* Call + package name + stored procedure name (incoming and outgoing values)
String str = "{call smsbusiness. deleteZhZMember (?,?,?)} ";


* Establish a connection
Connection conn = null;
Conn = DriverManager. getConnection ();

* Use the java. SQL. * Class
CallableStatement cs = conn. prepareCall (str );

* Input parameters
Cs. setInt (1, id );
Cs. setInt (2,-2 );

* Set the out value.
Cs. registerOutputParameter (3, Types. NUMBERIC );

* Execution
Cs. excuse ();

2. The stored procedure returns multi-value parameters.

* Package Creation
CREATE OR REPLACE PACKAGE TESTPACKAGE
TYPE Test_CURSOR is ref cursor;
End testpackage;
 
* Creation of a cursor
Create or replace procedure testc (p_CURSOR out TESTPACKAGE. Test_CURSOR) IS
BEGIN
OPEN p_CURSOR for select * FROM grade;
End testc;


* Java call
CallableStatement cs = conn. prepareCall (sqlStr );
ResultSet rs = (ResultSet) cs. getObject (2 );

3. Example of stored procedure without return values

 CREATE OR REPLACE PROCEDURE TESTA(PARA1 IN VARCHAR2,PARA2 IN VARCHAR2) AS    BEGIN      INSERT INTO HYQ.B_ID (I_ID,I_NAME) VALUES (PARA1, PARA2);    END TESTA;

Package com. hyq. src; import java. SQL. *; import java. SQL. resultSet; public class TestProcedureOne {public TestProcedureOne () {} public static void main (String [] args) {String driver = "oracle. jdbc. driver. oracleDriver "; String strUrl =" jdbc: oracle: thin: @ 127.0.0.1: 1521: hyq "; Statement stmt = null; ResultSet rs = null; Connection conn = null; callableStatement cstmt = null; try {Class. forName (dr Iver); conn = DriverManager. getConnection (strUrl, "zl", "123456"); CallableStatement proc = null; // create the object proc = conn. prepareCall ("{call HYQ. TESTA (?,?)} "); // Set the stored procedure call as a keyword. proc. setString (1, "100"); // set the first input parameter proc. setString (2, "TestOne"); // set the second input parameter proc.exe cute (); // execute} catch (SQLException ex2) {ex2.printStackTrace ();} catch (Exception ex2) {ex2.printStackTrace ();} finally {try {if (rs! = Null) {rs. close (); if (stmt! = Null) {stmt. close ();} if (conn! = Null) {conn. close () ;}} catch (SQLException ex1 ){}}}

4. Basic return value stored procedure example

 CREATE OR REPLACE PROCEDURE TESTB(PARA1 IN VARCHAR2,PARA2 OUT VARCHAR2) AS   BEGIN       SELECT INTO PARA2 FROM TESTTB WHERE I_ID= PARA1;   END TESTB;

// Java code: package com. hyq. src; public class TestProcedureTWO {public TestProcedureTWO () {} public static void main (String [] args) {String driver = "oracle. jdbc. driver. oracleDriver "; String strUrl =" jdbc: oracle: thin: @ 127.0.0.1: 1521: zl "; Statement stmt = null; ResultSet rs = null; Connection conn = null; try {Class. forName (driver); conn = DriverManager. getConnection (strUrl, "hyq", "hyq"); Ca LlableStatement proc = null; proc = conn. prepareCall ("{call zl. TESTB (?,?)} "); // Set the Stored Procedure proc. setString (1, "100"); // set the first parameter input parameter proc. registerOutParameter (2, Types. VARCHAR); // The second output parameter is proc.exe cute () of the VARCHAR type; // run String testPrint = proc. getString (2); // obtain the output parameter System. out. println ("= testPrint = is =" + testPrint);} catch (Exception e) {} finally {}}}

5. Stored Procedure of set return values

// Database: create or replace package testpackage as type Test_CURSOR is ref cursor; end TESTPACKAGE; create or replace procedure testc (p_CURSOR out TESTPACKAGE. test_CURSOR) is begin open p_CURSOR for select * from hyq. TESTTB; end testc;

// Java code: package com. hyq. src; import java. SQL. *; import java. io. outputStream; import java. io. writer; import java. SQL. preparedStatement; import java. SQL. resultSet; import oracle. jdbc. driver. *; public class TestProcedureTHREE {public TestProcedureTHREE () {} public static void main (String [] args) {String driver = "oracle. jdbc. driver. oracleDriver "; String strUrl =" jdbc: oracle: thin: @ 127.0.0.1: 1521: Zl "; Statement stmt = null; ResultSet rs = null; Connection conn = null; try {Class. forName (driver); conn = DriverManager. getConnection (strUrl, "hyq", "hyq"); CallableStatement proc = null; proc = conn. prepareCall ("{call hyq. testc (?)} "); // Proc under the hyq package. registerOutParameter (1, oracle. jdbc. oracleTypes. CURSOR); // set the output parameter to a CURSOR. the first parameter is the cursor type proc.exe cute (); // execute rs = (ResultSet) proc. getObject (1); // get the first parameter as a cursor and convert it to the ResultSet type while (rs. next () // obtain the data {System. out. println (rs. getString (1) + rs. getString (2) ;}} catch (Exception ex2) {ex2.printStackTrace () ;}finally {}}}

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.