Võ Văn Hải's blog

Chỉ có một điều tuyệt đối đó là mọi thứ đều tương đối…

Làm việc với Stored Procedure

Trong bài viết này tôi sẽ giới thiệu với các bạn cách chúng ta thao tác với các StoredProcedure trong java với đối tượng CallableStatement.

CallableStatement là đối tượng kế thừa từ đối tượng PreparedStatement, như vậy có các tính chất của 1 PreparedStatement (tức có các tính chất của Statement(public interface CallableStatement extends PreparedStatement). Chúng ta tạo 1 CallableStatement bằng cú pháp sau:

CallableStatement prepareCall(String sql) throws SQLException hoặc

CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency) throws SQLException.

Sau đó chúng ta triệu gọi các stored procedure bằng các cú pháp sau:

Đối với procedure không có tham số: {call procedure_name}

Đối với procedure có tham số: {call procedure_name(?,?,…)}

Đối với procedure có giá trị trả về (function): {?=call procedure_name(?,?,…)}

Ví dụ: CallableStatement cstmt = con.prepareCall(“{call procedureName(?, ?)}”);

trong đó các dấu ? thay thế cho các tham số kiểu IN, OUT, INOUT.

Đối với tham số kiểu IN, ta sử dụng phương thức setXXX của đối tượng PrepareStatement để chuyển tham số vào. ví dụ: cstmt.setString(1, “c%”);

Đối với tham số kiểu OUT, ta phải đăng ký đầu ra trước khi thực thi, ví dụ:
stmt.registerOutParameter(1, java.sql.Types.INTEGER);

Đối với tham số dạng INOUT, ta kết hợp gồm IN và OUT.

================================================

Dể dễ hiểu, tôi sẽ viết 1 ví dụ thực thi các kiểu StoredProcedure cho các bạn.

Trước hết ta tạo 1 database có tên QLSV trong đó có 1 bảng có tên Lophoc . SQL script cho database như sau:

CREATE TABLE [dbo].[Lophoc](
[classID] [nvarchar](15) COLLATE Vietnamese_CI_AS NOT NULL,
[className] [nvarchar](100) COLLATE Vietnamese_CI_AS NOT NULL,
[studentsNum] [int] NOT NULL,
[deptID] [nvarchar](15) COLLATE Vietnamese_CI_AS NULL,
CONSTRAINT [PK_tblClass] PRIMARY KEY CLUSTERED
(
[classID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Sau đó chúng ta tạo các procedure để thử.  Script sau đây sẽ tạo các stored procedure cho chúng ta:

–Tạo proc cho tham số IN
create proc exInparams
@clsID varchar(15)
AS
select *
from tblClass
where tblClass.classID like @clsID
GO
–Thực thi
exec exInparams ‘c%’
–********************************************
-Tạo proc cho tham số output
create proc exOutParams
@cls varchar(15),@num int OUTPUT
as
select @num=count(classID)
from tblClass
where classID like @cls
go
–Thực thi
declare @s int
exec exOutParams ‘c%’,@s OUTPUT
print @s
–********************************************
-Tạo proc cho tham số INOUT
create proc exINOUTParams
@num int OUT
as
select @num=count(classID)
from tblClass
where studentsNum>@num
go
select * from tblClass
–Thực thi
declare @s int
set @s=100
exec exINOUTParams @s OUTPUT
print @s
–********************************************
–Hàm Function
–Hàm lấy số SV của 1 lớp khi biết mã số lớp
create FUNCTION dbo.getNoStudents (@classID varchar(15))
RETURNS int
AS
BEGIN
Declare @c  int
set @c=(select studentsNum from tblClass where classID = @classID)
return @c
END
go
–Thực thi
declare @x int
set @x=dbo.getNoStudents(‘cdth4c’)
print(@x)
–********************************************

Bạn tạo DB rồi chạy các hàm để thử trên hệ quản trị CSDL, chẳng hạn MS SQLserver.

bạn tạo lớp sau để thử các loại:


package vovanhai.wordpress.com;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.ResultSet;

import session6_JDBC.ConnectDBFactory;


//thử tham số đầu vào

public class TestCallable {

private Connection con;

void TestINPro() throws Exception{

con=ConnectDBFactory.CreateMsSqlServerConnection(“QLSV”);

String sql=“{call exInparams(?)}”;

CallableStatement cstmt=con.prepareCall(sql);

cstmt.setString(1, “c%”);

ResultSet rs=cstmt.executeQuery();

while(rs.next()) {

System.out.println(rs.getString(“className”));

}

con.close();

}
//thử tham số đầu ra
void TestOUTPro() throws Exception{

con=

ConnectDBFactory.CreateMsSqlServerConnection(“QLSV”);

String sql=“{call exOutParams(?,?)}”;

CallableStatement cstmt=con.prepareCall(sql);

cstmt.setString(1, “d%”);

//đăng ký tham số đầu ra
cstmt.registerOutParameter(2, java.sql.Types.INTEGER);

cstmt.execute();

//Lấy giá trị trả về

int s=cstmt.getInt(2);

System.out.println(s);

con.close();

}
//Thử tham số vừa vào vừa ra
void TestINOUTPro() throws Exception{

con=ConnectDBFactory.CreateMsSqlServerConnection(“QLSV”);

String sql=“{call exINOUTParams(?)}”;

CallableStatement cstmt=con.prepareCall(sql);

cstmt.setInt(1, 200);

//Đăng ký tham số đầu ra

cstmt.registerOutParameter(1, java.sql.Types.INTEGER);

cstmt.execute();

//lấy giá trị trả về

int s=cstmt.getInt(1);

System.out.println(s);

con.close();

}
//Thử việc triệu gọi 1 function có giá trị trả về
public void TestFunctionCall() throws Exception {

ConnectDBFactory.CreateMsSqlServerConnection(“QLSV”);

CallableStatement cst=con.prepareCall(“{?=call getNoStudents(?)}”);

cst.registerOutParameter(1,java.sql.Types.INTEGER);

cst.setString(2,“CD”);

cst.execute();

int ret=cst.getInt(1);

System.out.println (“Số sinh viên: “+ret);

}

}

OK. Chúc các bạn thành công! Nếu hữu ích, thanks 1 tiếng nhé!

34 Responses to “Làm việc với Stored Procedure”

  1. Bao Long said

    Xin chân thành cảm ơn thầy rất nhiều!

  2. Hoang Nam said

    cám ơn thầy rất nhiều, em đang rất cần bài viết trên ^.^!

  3. Phạm Thị Mai Thu said

    Em cám ơn thầy rất nhiều. Bai viết này rất dễ hiểu và có ích đối với em.

  4. sundud said

    Cho em hỏi làm sao để insert một chuỗi tiếng việt có dấu vào mysql thông qua store procedure được gọi từ java? Vì em chỉ làm insert được tiếng việt vào mysql thông qua các câu lệnh trong mysql query browser thôi, còn khi cho gọi từ java thì lại không được. Mong thầy chỉ giúp. Em cám ơn.

  5. Minh dung hoi said

    Cho em hỏi làm sao để insert một chuỗi tiếng việt có dấu vào SQL SERVER 2000 thông qua store procedure được gọi từ form VB.NET Vì em chỉ làm insert được tiếng việt vào SQL thông qua các câu lệnh trong SQL thôi bang cách insert values (N’Nguyễn Văn Tèo’)nhưng thay Nguyễn Văn Tèo bằng 1 biến chứa giá trị tiếng việt truyền từ form VB.NET thì lại không được. Mong thầy chỉ giúp. Em cám ơn.

  6. vovanhai said

    Khi tôi làm thấy bình thường mà. Bạn dùng VB.Net phiên bản nào? Khi bạn code, design bạn có lưu form/code của bạn ở dạng unicode không?

  7. tuyethai said

    Em chao Thay,
    Em cung su dung nhung doan code nhu thay vua viet nhung khong hieu sao khong ra duoc. Rat mong thay giup em.
    Em dung 1 sp voi noi dung nhu sau:

    CREATE PROCEDURE [dbo].[sp_dsNhanVien] @viTri int, @soMauTin int output AS
    declare contro scroll cursor for
    select maNV, ho, ten, ngaySinh, diaChi, luong, ghiChu
    from NhanVien
    open contro
    if(@viTri = 1)
    begin
    fetch first from contro
    end
    set @soMauTin = @@cursor_rows
    close contro
    deallocate contro
    GO

    Sp nay em goi trong Analyzer thi nhan duoc output nhung khi viet code java (dung netbean 6.5) thi khong nhan duoc gia tri bien output luon bang 0.

    try {
    Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
    con = DriverManager.getConnection(“jdbc:odbc:QuanLyVatTu”, “sa”, “”);
    } catch (SQLException ex) {
    Logger.getLogger(NhanVien.class.getName()).log(Level.SEVERE, null, ex);
    } catch (ClassNotFoundException ex) {
    Logger.getLogger(NhanVien.class.getName()).log(Level.SEVERE, null, ex);
    }

    try {
    // TODO add your handling code here:
    viTri = 1;
    st = “{call sp_dsNhanVien(?, ?)}”;
    cs = con.prepareCall(st);
    cs.setInt(1, viTri);
    cs.registerOutParameter(2, java.sql.Types.INTEGER);

    rs = cs.executeQuery();
    soMauTin = cs.getInt(2); // soMauTin: la output va em luon nhan duoc la 0
    int j = 0;
    } catch (SQLException ex) {
    Logger.getLogger(NhanVien.class.getName()).log(Level.SEVERE, null, ex);
    }

    Xin Thay giup em.
    Em cam on Thay nhieu!

  8. xin hỏi thầy java có gọi trực tiếp store procedure của sql server k ah? said

    xin hỏi thầy java có gọi trực tiếp store procedure của sql server k ah? Nếu được thì phải làm thế nào?
    Mong thầy bày cách giúp e! E xin cảm ơn!

  9. vovanhai said

    Em xem trong phần các bài viết về JDBC trong blog này, có chỉ cách!

  10. xin hỏi thầy ah! said

    thưa thầy! thư viện session6_JDBC lấy ở đâu?

  11. Thầy cho e hỏi said

    Lớp này thầy xây dựng như thế nào ah?
    “session6_JDBC.ConnectDBFactory”

  12. vovanhai said

    Tìm các bài trước đó có.

  13. Thầy cho em hỏi. said

    em dùng store proc để chèn dữ liệu vào db. nhưng không chèn được tiếng việt có dấu. nó bị {tiếng->ti?ng}. mong thầy giúp đỡ. Cảm ơn!.

  14. Võ Văn Hải said

    thêm N’ vào các chuỗi.

  15. Huỳnh Văn Lộc said

    Thầy ơi cho em hỏi là khi em nhận 1 Resultset trả về từ 1 Procedure (param kiểu OUT) thì nó có thể Scroll tới lui được không Thầy…em dùng CSDL Oracle.

    CallableStatement cs = conn.prepareCall(“{call GET_LIST_DOMAIN_PROC(?)}”,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
    cs.registerOutParameter(1,OracleTypes.CURSOR );
    cs.execute();
    ResultSet rs = (ResultSet)cs.getObject(1);

    khi em gọi rs.last() hay rs.beforeFirst()… thì nó phát sinh lỗi

  16. Võ Văn Hải said

    Tôi thử rồi. Chạy tốt. Nhưng bạn dùng Driver gì?

  17. huynhdam39 said

    xin cám on tác giả

  18. Bich thao said

    thay oi cho em hoi thay co biet cach nao de load tat ca cac database trong sql server 2005 len combobox trong C# ko thay . Neu thay biet thi giup em voi nha .
    mail cua em la : thao1412003@yahoo.com
    cam on thay nhiu lam ^ ^

  19. Bich thao said

    em con mot van de nua la neu tren form minh thuc thi store trong sql rui va muon no hien thi ra textbox .vi khi thuc thi store no se cho ra mot cau lenh thuc thi khac dang print trong sql em muon no hien thi phan print do ra ben ngoai form de dung chuoi print do tiep tuc thuc thi cau lenh sql. neu thay biet thi gui mail cho em voi cam on thay nhiu lam ^^

  20. Võ Văn Hải said

    thực thi câu truy vấn: sp_databases.

  21. Bich thao said

    cam on thay em da load duoc cac table rui ^ ^

  22. Hoàng phúc said

    Em chào thầy, thưa thầy em đang làm bài tập mô phỏng ứng dụng của DNS bằng java mà em đang bị rối trí về vấn đề nên thực hiện thế nào, mong thầy cho em lời khuyên ạ. Em chân thành cảm ơn thầy.

  23. em chào thầy, cho em hỏi chút, ví dụ như khi tạo bảng em đặt tên cột là tiếng việt thì khi viết trong stored produces phải viết như thế nào ạ.
    ví dụ em có bảng tên là danhsach với các cột là số thứ tự, tên học sinh, quê quán. em viết:
    ALTER PROCEDURE dbo.themquanly
    (
    @[số thứ tự] int,
    @[tên học sinh] nvarchar(100),
    @[quê quán] nvarchar(100)
    )
    AS
    INSERT INTO [danhsach]
    (
    [số thứ tự],
    [tên học sinh],
    [quê quán]
    )
    VALUES
    (
    @[số thứ tự],
    @[tên học sinh],
    @[quê quán]
    )
    RETURN
    nhưng khi lưu thì báo:

    incorrect systax near ‘nvarchar’
    must declare the scalar variable “@”

    thầy xem giúp em với
    email em là : adamkho42@gmail.com

  24. Anh Tuân said

    Em chào thầy.
    em muốn load danh sách “logins name” ra java. Câu lệnh “exec sp_helplogins” thì chỉ load được trong stored procedure. Em muốn đưa ra java thì phải làm sao thầy? Mong thầy giúp đở.
    mail của em: voanhtuan1990@gmail.com

  25. Hoàng Anh said

    Em chỉ mới bắt đầu học java thôi nên không hiểu lắm… Mong Thầy chỉ rõ hơn ạ. Phần trên Thầy hướng dẫn là phần mình khai báo pocedure trong java dúng không ạ? Vậy sau khi khai báo xong rồi mình sẽ gọi thực thi nó như thế nào? Mong Thầy chỉ giúp, cám ơn Thầy nhiều nhiều.

  26. Hoàng Anh said

    Procedure em muốn thực thi là 1 câu lệnh insert data vào table Thầy ạ, không phải dạng truy vấn như ở trên. Vậy thì cách khai báo và gọi hàm thực thi có khác gì nhau không Thầy?

  27. Hoàng Anh said

    Sorry Thầy nha… e đã làm được rồi hifhif, Thầy không cần bận tâm 2 câu hỏi phía trên của e nữa nha. Cám ơn Thầy

  28. Võ Văn Hải said

    “em muốn load danh sách “logins name” ra java. Câu lệnh “exec sp_helplogins” thì chỉ load được trong stored procedure. Em muốn đưa ra java thì phải làm sao thầy? Mong thầy giúp đở.”
    Để có thể chạy được sp bạn phải log với quyền admin. CÒn việc thực thi thì như thực thi 1 stored proc bình thường bằng CallableStatement.

  29. Anh Tuân said

    Cảm ơn thầy nhiều. Em đã load được rồi.
    Trong 1 datadases ta thường tạo 1 user cho 1 login name. Vậy muốn lấy user đó để in ra thì phải làm sao thầy?

  30. Cường said

    Em có một câu hỏi rất mong nhận được sự trả lời của thầy và mọi người:
    Vấn đề là em đang dùng sql sever 2005.Khi tạo mới 1 stored ( thủ tục nội tại) rồi sau đó tôi lưu lại bằng Save ( ko phải save as) thì nó chỉ dẫn lưu ở thư mục projects.Lưu vào đó xong đến khi chọn phần mở rộng Stored trong SQL sever thì ko thấy stored mới tạo.
    Mong nhận đc câu hỏi từ mọi người.
    Xin cảm ơn !

  31. Cường said

    Tiện thể Thầy và mọi người cho e hỏi 1 điều mà e băn khoăn từ lâu về cơ sở dữ liệu:
    trong cơ sở dữ liệu thì các bảng hay được liên kết với nhau.1 ví dụ đơn giản của e là: e có 2 bảng : HOCSINH và bảng LOP:
    HOCSINH :idhocsinh(khoa chinh), ten,quequan,idlop
    LOP: idlop(khoa chinh),tenlop
    2 bảng này liên kết với nhau theo 1-n
    e có một thắc mắc là trong phần mềm quản lý học sinh khi ta thêm mới dữ liệu vào một bảng HOCSINH chẳng hạn thì nhất thiết phải thêm bắt buộc trường idlop ạ, dù biết rằng có thể chọn combobox để liệt kê những idlop lên bảng HOCSINH. Liệu có cách mà để ta thêm mới một học sinh thay vì thêm mã lớp ( idlop) thì ta thêm là tên lớp (tenlop) cho dễ dàng trong công việc thêm mới học sinh ko ạ !
    Xin chân thành cảm ơn Thầy và mọi người !
    Kính mong nhận được câu trả lời sớm nhất từ Thầy và mọi người

  32. giangvt06@gmail.com said

    Exception in thread “main” java.lang.Exception: Erro :Khong the ket noi ?The TCP/IP connection to the host PC2012021609CQN/SQLEXPRESS, port 1433 has failed. Error: “null. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.”. – Select * From users
    at giangvt06.MySQLConnec.executeQuery(MySQLConnec.java:79)
    at giangvt06.Giangvt06.main(Giangvt06.java:22)
    Java Result: 1

    Thầy sem hộ em lỗi này là lỗi gì. em anlabe tcp/ip voi config lai filewar roi ma van ko được.

  33. Võ Văn Hải said

    “…Liệu có cách mà để ta thêm mới một học sinh thay vì thêm mã lớp ( idlop) thì ta thêm là tên lớp (tenlop) cho dễ …”
    Việc này bạn chỉ cần tạo lớp lớp học với phương thức toString chỉ hiển thị tên lớp sau đó trong combobox bạn thêm object lophoc thay vì chuỗi String idlop.

  34. Thuy said

    Thầy cho hỏi update giá trị null cho khóa ngoại trong C# như thế nào? nếu thực thi thủ tục proc thì nó không cho thực thi tuy nhiên trong sql mình vẫn query được với giá trị null

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: