JAVA in a stored procedure call methods (cited)

2011-10-17  来源:本站原创  分类:Database  人气:105 

A: Java how to call a stored procedure:
A: No, with output parameters
With no output parameters --------------- ------------------------------ ----
create procedure getsum
@ N int = 0 <- here as a parameter ->
as
declare @ sum int <- define the variable ->
declare @ i int
set @ sum = 0
set @ i = 0
while @ i <= @ n begin
set @ sum = @ sum + @ i
set @ i = @ i +1
end
print 'the sum is' + ltrim (rtrim (str (@ sum)))

-------------- In SQL execution :--------------------
exec getsum 100

------------ Call :--------------------- in JAVA
JAVA JAVA program can call but can not go to show the results of the stored procedure because of the above stored procedure parameter type int passed way in (by value) method
import java.sql .*;
public class ProcedureTest
{
public static void main (String args []) throws Exception
{
/ / Load the driver
DriverManager.registerDriver (new sun.jdbc.odbc.JdbcOdbcDriver ());
/ / Get connection
Connection conn = DriverManager.getConnection ("jdbc: odbc: mydata", "sa ","");

/ / Create the stored procedure object
CallableStatement c = conn.prepareCall ("{call getsum (?)}");

/ / To the stored procedure parameter settings
c.setInt (1,100); / / the first parameter set to 100

/ / Execute a stored procedure
c.execute ();
conn.close ();
}
}

B: with output parameters
1: return int
------------------------- ---------------- With output parameters
alter procedure getsum
@ N int = 0,
@ Result int output
as
declare @ sum int
declare @ i int
set @ sum = 0
set @ i = 0
while @ i <= @ n begin
set @ sum = @ sum + @ i
set @ i = @ i +1
end
set @ result = @ sum
------------------- ------------ Executed in Query Analyzer
declare @ myResult int
exec getsum 100, @ myResult output
print @ myResult

------------ --------------------- Call in JAVA
import java.sql .*;
public class ProcedureTest
{
public static void main (String args []) throws Exception
{
/ / Load the driver
DriverManager.registerDriver (new sun.jdbc.odbc.JdbcOdbcDriver ());
/ / Get connection
Connection conn = DriverManager.getConnection ("jdbc: odbc: mydata", "sa ","");

/ / Create the stored procedure object
CallableStatement c = conn.prepareCall ("{call getsum (?,?)}");

/ / Stored procedure to set the first parameter value
c.setInt (1,100);

/ / Register the second stored procedure parameters
c.registerOutParameter (2, java.sql.Types.INTEGER);

/ / Execute a stored procedure
c.execute ();

/ / Get the stored procedure's output parameter values
System.out.println (c.getInt (2));
conn.close ();
}
}
2: Return varchar
---------------- ---------------- Stored procedure with cursor
--- In a stored procedure with cursor using the cursor stop traversal orderid
create procedure CursorIntoProcedure
@ Pname varchar (8000) output
as
- Define a cursor
declare cur cursor for select orderid from orders
- Define a cursor variable to receive the value of
declare @ v varchar (5)
- Open the cursor
open cur
set @ pname =''-- to @ pname initial
- Extract the value of the cursor
fetch next from cur into @ v
while @ @ fetch_status = 0
begin

set @ pname = @ pname +';'+v
fetch next from cur into @ v
end
print @ pname
- Close the cursor
close cur
- Destruction of the cursor
deallocate cur

Execute a stored procedure -------------- ------------
exec CursorIntoProcedure''

Call ------------------ -------------- JAVA
import java.sql .*;
public class ProcedureTest
{
public static void main (String args []) throws Exception
{
/ / Load the driver
DriverManager.registerDriver (new sun.jdbc.odbc.JdbcOdbcDriver ());
/ / Get connection
Connection conn = DriverManager.getConnection ("jdbc: odbc: mydata", "sa ","");
CallableStatement c = conn.prepareCall ("{call CursorIntoProcedure (?)}");

c.registerOutParameter (1, java.sql.Types.VARCHAR);

c.execute ();

System.out.println (c.getString (1));
conn.close ();
}
}
C: delete the data storage process
------------------ Stored Procedure --------------------------

drop table students basic information table
students basic information table create table
(
StuID int primary key,
StuName varchar (10),
StuAddress varchar (20)
)
insert into students' basic information table values ​​(1, 'San Mao', 'wuhan')
insert into students' basic information table values ​​(2, 'San Mao', 'wuhan')
table create table student achievement
(
StuID int,
Chinese int,
PyhSics int
foreign key (StuID) references students basic information table (StuID)
on delete cascade
on update cascade
)
insert into student achievement table values ​​(1,99,100)
insert into student achievement table values ​​(2,99,100)

- Create a stored procedure
create procedure delePro
@ StuID int
as
delete from students basic information table where StuID = @ StuID
- Has been created
exec delePro 1 - execute a stored procedure
- Create a stored procedure
create procedure selePro
as
select * from students basic information table
- Has been created
exec selePro - execute a stored procedure
------------------ ---------------- Call in JAVA
import java.sql .*;
public class ProcedureTest
{
public static void main (String args []) throws Exception
{
/ / Load the driver
DriverManager.registerDriver (new sun.jdbc.odbc.JdbcOdbcDriver ());
/ / Get connection
Connection conn = DriverManager.getConnection ("jdbc: odbc: mydata", "sa ","");

/ / Create the stored procedure object
CallableStatement c = conn.prepareCall ("{call delePro (?)}");

c.setInt (1,1);

c.execute ();

c = conn.prepareCall ("{call selePro}");
ResultSet rs = c.executeQuery ();

while (rs.next ())
{
String Stu = rs.getString ("StuID");
String name = rs.getString ("StuName");
String add = rs.getString ("StuAddress");

System.out.println ("Student ID:" + "" + "Name:" + "" + "address");
System.out.println (Stu + "" + name + "" + add);
}
c.close ();
}
}
D: stored procedures to modify data
--------------------- --------------------- Create a stored procedure
create procedure ModPro
@ StuID int,
@ StuName varchar (10)
as
update the students the basic information table set StuName = @ StuName where StuID = @ StuID

------------- ------------------------- Execute a stored procedure
exec ModPro 2, 'four hair'
-------------------- --------------- JAVA stored procedure call
import java.sql .*;
public class ProcedureTest
{
public static void main (String args []) throws Exception
{
/ / Load the driver
DriverManager.registerDriver (new sun.jdbc.odbc.JdbcOdbcDriver ());
/ / Get connection
Connection conn = DriverManager.getConnection ("jdbc: odbc: mydata", "sa ","");

/ / Create the stored procedure object
CallableStatement c = conn.prepareCall ("{call ModPro (?,?)}");

c.setInt (1,2);
c.setString (2, "beauty");

c.execute ();

c = conn.prepareCall ("{call selePro}");
ResultSet rs = c.executeQuery ();

while (rs.next ())
{
String Stu = rs.getString ("StuID");
String name = rs.getString ("StuName");
String add = rs.getString ("StuAddress");

System.out.println ("Student ID:" + "" + "Name:" + "" + "address");
System.out.println (Stu + "" + name + "" + add);
}
c.close ();
}
}
E: query data stored procedure (fuzzy queries)
--------------------- ----------------- Stored Procedures
create procedure FindCusts
@ Cust varchar (10)
as
select customerid from orders where customerid
like'%'+cust + '%'
--------------- --------------------------- Implementation
execute FindCusts 'alfki'
------------- -------------------------- Call in JAVA
import java.sql .*;
public class ProcedureTest
{
public static void main (String args []) throws Exception
{
/ / Load the driver
DriverManager.registerDriver (new sun.jdbc.odbc.JdbcOdbcDriver ());
/ / Get connection
Connection conn = DriverManager.getConnection ("jdbc: odbc: mydata", "sa ","");

/ / Create the stored procedure object
CallableStatement c = conn.prepareCall ("{call FindCusts (?)}");
c.setString (1, "Tom");

ResultSet rs = c.executeQuery ();

while (rs.next ())
{
String cust = rs.getString ("customerid");
System.out.println (cust);
}
c.close ();
}
}
F: increase the data storage process

------------ -------------------- Stored procedure
create procedure InsertPro
@ StuID int,
@ StuName varchar (10),
@ StuAddress varchar (20)
as
insert into students' basic information table values ​​(@ StuID, @ StuName, @ StuAddress)

----------- --------------- Call a stored procedure
exec InsertPro 5, '555 ', '555'
----------- ------------- Implementation in JAVA
import java.sql .*;
public class ProcedureTest
{
public static void main (String args []) throws Exception
{
/ / Load the driver
DriverManager.registerDriver (new sun.jdbc.odbc.JdbcOdbcDriver ());
/ / Get connection
Connection conn = DriverManager.getConnection ("jdbc: odbc: mydata", "sa ","");

/ / Create the stored procedure object
CallableStatement c = conn.prepareCall ("{call InsertPro (?,?,?)}");
c.setInt (1,6);
c.setString (2, "Liu");
c.setString (3, "wuhan");

c.execute ();

c = conn.prepareCall ("{call selePro}");
ResultSet rs = c.executeQuery ();

while (rs.next ())
{
String stuid = rs.getString ("StuID");
String name = rs.getString ("StuName");
String address = rs.getString ("StuAddress");
System.out.println (stuid + "" + name + "" + address);
}
c.close ();
}
}

G: In JAVA to create stored procedures and call directly in JAVA
import java.sql .*;
public class ProcedureTest
{
public static void main (String args []) throws Exception
{
/ / Load the driver
DriverManager.registerDriver (new sun.jdbc.odbc.JdbcOdbcDriver ());
/ / Get connection
Connection conn = DriverManager.getConnection ("jdbc: odbc: mydata", "sa ","");

Statement stmt = conn.createStatement ();
/ / Create the stored procedure in JAVA
stmt.executeUpdate ("create procedure OOP as select * from student report cards");

CallableStatement c = conn.prepareCall ("{call OOP}");

ResultSet rs = c.executeQuery ();
while (rs.next ())
{
String chinese = rs.getString ("Chinese");

System.out.println (chinese);
}
conn.close ();

}
}

相关文章
  • JAVA in a stored procedure call methods (cited) 2011-10-17

    A: Java how to call a stored procedure: A: No, with output parameters With no output parameters --------------- ------------------------------ ---- create procedure getsum @ N int = 0 <- here as a parameter -> as declare @ sum int <- define the v

  • JAVA calling DB2 stored procedure 2008-05-28

    Search java call db2 (version 8.2) Stored Procedures and few articles to look at, so I decided to write point, after the novice to use, I hope that my article be useful to them and they can find my articles, Amen 1. JAVA call db2 stored procedure mos

  • JAVA calling Oracle stored procedure (to) 2010-12-10

    In large database systems, there are two very important functions, that is, stored procedures and triggers. Whether in the database system stored procedure or trigger, is through SQL statements and control flow statements to complete the set. In cont

  • Optimization of SQL Server stored procedure seven methods [reprint] 2010-12-13

    Optimization of SQL Server stored procedure seven methods Optimized stored procedures there are many ways, the most commonly used are described below 7. 1. Using SET NOCOUNT ON option We use the SELECT statement, in addition to returns the correspond

  • The most complete java call oracle stored procedure. Function 2010-11-24

    java call oracle stored procedures, functions, under the following situations: 1.java call stored procedure with parameters 2.java call a stored procedure without parameters 3.java call the function with parameter 4.java function call without paramet

  • JAVA calling Oracle stored procedure (transfer) 2010-12-10

    In large database systems, there are two very important functions, that is, stored procedures and triggers. Whether it is in the database system stored procedures or triggers, through SQL statements and control flow statements to complete the set. Re

  • java call oracle stored procedure return set 2011-06-08

    java call oracle stored procedure return set 1, pl / sql stored procedures CREATE OR REPLACE PROCEDURE retCursor(ret_cursor OUT SYS_REFCURSOR) IS ret_cursor_value SYS_REFCURSOR; BEGIN OPEN ret_cursor_value FOR SELECT * FROM emp; ret_cursor := ret_cur

  • java call oracle stored procedure returns multiple records (result set) 2010-10-25

    Cursor, type, nested table, storage bag, stored procedures -- Define TYPE CREATE OR REPLACE TYPE OrderAttrType AS OBJECT ( CharacterId VARCHAR2(60), CharacterName VARCHAR2(60), CharacterValue VARCHAR2(4000), CharacteValueName VARCHAR2(4000), OldChara

  • [Transfer] Java calling SQL stored procedure input and output parameters 2010-11-09

    1 Use a stored procedure without parameters Using the JDBC driver calls the stored procedure without parameters, you must use the call SQL escape sequence. Call with no arguments The syntax of the escape sequence is as follows: {Call procedure-name}

  • JAVA and ORACLE stored procedure 2010-07-19

    In large database systems, there are two very important functions, that is, stored procedures and triggers. Whether in the database system stored procedure or trigger, is through SQL statements and control flow statements to complete the set. In cont

  • java call oracle stored procedure sample summary 2010-09-18

    oracle java stored procedure is called an example of a conclusion: no return value stored procedure Stored procedure is: create or replace procedure adddept (colno number, colname varchar2, colc varchar2) as begin insert into table values (colno, col

  • Java call Oracle stored procedure 2010-12-03

    1, the stored procedure is stored in the database and perform the procedure on the database side, it has two types. A similar SELECT query to retrieve the data, the data can be retrieved in the form of a data set returned to the customer. Another sim

  • java call the stored procedure returns a result set (cursor) 2011-03-25

    New Stored Procedure create or replace procedure getcur(p_rc out sys_refcursor) is begin open p_rc for 'select * from room'; end getcur; java call Class.forName("oracle.jdbc.driver.OracleDriver"); Connection conn = DriverManager.getConnection( &

  • Java class TestStoreProcedure.java test the stored procedure 2011-06-01

    Use the following Java classes TestStoreProcedure.java test stored procedures. package test; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import oracle.jdbc.OracleTypes; /

  • java page SqlServer stored procedure call, return multiple result sets 2011-06-17

    Stored procedure: USE [Db_8za8za_2] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Description: <Description,, Universal paging stored procedure > -- ==========================================

  • [Transfer] SQL server database stored procedure testing methods 2011-06-01

    Many Windows-based systems using SQL Server as a background component. The application under test (AUT: Application Under Test) or the system under test (SUT: System Under Test) to regularly access the database through stored procedures. For these sc

  • Java stored procedure is called the 2 methods 2010-04-23

    Using java call: class.forName (\ "sun.jdbc.odbc.JdbcOdbcDriver \"); / / load the driver Connection con = DriverManager.getConnection (\ "Jdbc: Odbc: test \", \ "sa \", \ "\"); / / get connection String call = \ &qu

  • java stored procedure error return values to take the trap of 2009-08-25

    Today found a java did not get a sqlserver stored procedure return value question, thus we usually consider themselves a very good writing are suspect, in the end Which way is correct? Or a start we are wrong? Or do we find time to consider the probl

  • ORACLE JAVA stored procedure call. The function returns the result set example 2010-07-14

    - Function create or replace function get_info (e_name varchar2, job in out varchar2) return varchar2 is Result varchar2 (100); begin select e_name ||'-'|| job into Result from dual; job: = '1 '; return (Result); end get_info; - Call DECLARE job varc

  • oracle java stored procedure call 2010-12-17

    In large database systems, there are two very important functions, that is, stored procedures and triggers. Whether in the database system stored procedure or trigger, is through SQL statements and control flow statements to complete the set. In cont