第一篇：mysql 5.0存儲過程學習總結
mysql 5.0存儲過程學習總結
一.創建存儲過程 1.基本語法：
create procedure sp_name()begin end 2.參數傳遞
二.調用存儲過程
1.基本語法：call sp_name()注意：存儲過程名稱后面必須加括號，哪怕該存儲過程沒有參數傳遞
三.刪除存儲過程 1.基本語法：
drop procedure sp_name// 2.注意事項
(1)不能在一個存儲過程中刪除另一個存儲過程，只能調用另一個存儲過程
四.區塊，條件，循環 1.區塊定義，常用 begin end;也可以給區塊起別名，如： lable:begin end lable;可以用leave lable;跳出區塊，執行區塊以后的代碼
2.條件語句 3.循環語句 ：while循環 loop循環 repeat until循環 repeat until循環
五.其他常用命令
1.show procedure status 顯示數據庫中所有存儲的存儲過程基本信息，包括所屬數據庫，存儲過程名稱，創建時間等 2.show create procedure sp_name 顯示某一個存儲過程的詳細信息
mysql存儲過程基本函數
一.字符串類
CHARSET(str)//返回字串字符集 CONCAT(string2 [,...])//連接字串
INSTR(string ,substring)//返回substring首次在string中出現的位置,不存在返回0 LCASE(string2)//轉換成小寫
LEFT(string2 ,length)//從string2中的左邊起取length個字符 LENGTH(string)//string長度
一起推論壇
一起推論壇www.tmdps.cn
第二篇：課題：SQLServer存儲過程返回值總結
SQLServer存儲過程返回值總結
1.存儲過程沒有返回值的情況(即存儲過程語句中沒有return之類的語句)
用方法 int count = ExecuteNonQuery(..)執行存儲過程其返回值只有兩種情況
(1)假如通過查詢分析器執行該存儲過程，在顯示欄中假如有影響的行數，則影響幾行count就是幾
(2)假如通過查詢分析器執行該存儲過程，在顯示欄中假如顯示'命令已成功完成。'則count =-1;在顯示欄中假如有查詢結果，則count =-1
總結：A.ExecuteNonQuery()該方法只返回影響的行數，假如沒有影響行數，則該方法的返回值只能是-1，不會為0。
B.不論ExecuteNonQuery()方法是按照CommandType.StoredProcedure或者CommandType.Text執行，其效果和A一樣。
2.獲得存儲過程的返回值--通過查詢分析器獲得
(1)不帶任何參數的存儲過程(存儲過程語句中含有return)
---創建存儲過程
CREATE PROCEDURE testReturn
AS
return 145
GO
---執行存儲過程
DECLARE @RC int
exec @RC=testReturn
select @RC
---說明
查詢結果為145
(2)帶輸入參數的存儲過程(存儲過程語句中含有return)
---創建存儲過程
create procedure sp_add_table1
@in_name varchar(100), @in_addr varchar(100), @in_tel varchar(100)
as
if(@in_name = '' or @in_name is null)
return 1
else
begin
insert into table1(name,addr,tel)values(@in_name,@in_addr,@in_tel)
return 0
end
---執行存儲過程
<1>執行下列，返回1
declare @count int exec @count = sp_add_table1 '','中三路','123456' select @count
<2>執行下列，返回0
declare @count int exec @count = sp_add_table1 '','中三路','123456' select @count
---說明
查詢結果不是0就是1
(3)帶輸出參數的存儲過程(存儲過程中可以有return可以沒有return)
例子A：
---創建存儲過程
create procedure sp_output
@output int output
as
set @output = 121
return 1
---執行存儲過程
<1>執行下列，返回121
declare @out int
exec sp_output @out output
select @out
<2>執行下列，返回1
declare @out int
declare @count int exec @count = sp_output @out output
select @count
---說明
有return，只要查詢輸出參數，則查詢結果為輸出參數在存儲過程中最后變成的值；只要不查詢輸出參數，則查詢結果為return返回的值
例子B：
---創建存儲過程
create procedure sp_output
@output int output
as
set @output = 121
---執行存儲過程
<1>執行下列，返回121
declare @out int
exec sp_output @out output
select @out
<2>執行下列，返回0
declare @out int
declare @count int
exec @count = sp_output @out output
select @count
---說明
沒有return，只要查詢輸出參數，則查詢結果為輸出參數在存儲過程中最后變成的值；只要不查詢輸出參數，則查詢結果為0
總結：
(1)存儲過程共分為3類：
A.返回記錄集的存儲過程---------------------------其執行結果是一個記錄集，例如：從數據庫中檢索出符合某一個或幾個條件的記錄
B.返回數值的存儲過程(也可以稱為標量存儲過程)-----其執行完以后返回一個值，例如：在數據庫中執行一個有返回值的函數或命令
C.行為存儲過程----用來實現數據庫的某個功能，而沒有返回值，例如：在數據庫中的更新和刪除操作
(2)含有return的存儲過程其返回值為return返回的那個值
(3)沒有return的存儲過程，不論執行結果有無記錄集，其返回值是0
(4)帶輸出參數的存儲過程：假如有return則返回return返回的那個值，假如要select輸出參數，則出現輸出參數的值，于有無return無關
--------------------3.獲得存儲過程的返回值--通過程序獲得
---------------------------SqlParameter[] cmdParms = {..,new SqlParameter(“@return”,SqlDbType.Int)};cmdParms[cmdParms.Length1].Direction = ParameterDirection.Output或者 cmdParms[cmdParms.Length1].Value;
分類: asp.net
第三篇：存儲過程的優缺點個人總結
優點
1.在生產環境下，可以通過直接修改存儲過程的方式修改業務邏輯（或bug），而不用重啟服務器。但這一點便利被許多人濫用了。有人直接就在正式服務器上修改存儲過程，而沒有經過完整的測試，后果非常嚴重。
2.執行速度快。存儲過程經過編譯之后會比單獨一條一條執行要快。但這個效率真是沒太大影響。如果是要做大數據量的導入、同步，我們可以用其它手段。
3.減少網絡傳輸。存儲過程直接就在數據庫服務器上跑，所有的數據訪問都在服務器內部進行，不需要傳輸數據到其它終端。但我們的應付服務器通常與數據庫是在同一內網，大數據的訪問的瓶頸會是硬盤的速度，而不是網速。
4.能夠解決presentation與數據之間的差異，說得文藝青年點就是解決OO模型與二維數據持久化之間的阻抗。領域模型和數據模型的設計可能不是同一個人（一個是SA，另一個是DBA），兩者的分歧可能會很大——這不奇怪，一個是以OO的思想來設計，一個是結構化的數據來設計，大家互不妥協——你說為了軟件的彈性必須這么設計，他說為了效率必須那樣設計，為了抹平鴻溝，就用存儲過程來做數據存儲的邏輯映射（把屬性映射到字段）。好吧，臺下已經有同學在叨咕ORM了。
5.方便DBA優化。所有的SQL集中在一個地方，DBA會很高興。這一點算是ORM的軟肋。不過按照CQRS框架的思想，查詢是用存儲過程還是ORM，還真不是問題——DBA對數據庫的優化，ORM一樣會受益。況且放在ORM中還能用二級緩存，有些時候效率還會更高。
缺點
1.SQL本身是一種結構化查詢語言，加上了一些控制（賦值、循環和異常處理等），但不是OO的，本質上還是過程化的，面對復雜的業務邏輯，過程化的處理會很吃力。這一點算致命傷。
2.不便于調試。基本上沒有較好的調試器，很多時候是用print來調試，但用這種方法調試長達數百行的存儲過程簡直是噩夢。好吧，這一點不算啥，C#/java一樣能寫出噩夢般的代碼。
3.沒辦法應用緩存。雖然有全局臨時表之類的方法可以做緩存，但同樣加重了數據庫的負擔。如果緩存并發嚴重，經常要加鎖，那效率實在堪憂。
4.無法適應數據庫的切割（水平或垂直切割）。數據庫切割之后，存儲過程并不清楚數據存儲在哪個數據庫中。
5.精通SQL的新手越來越少——不要笑，這是真的，我面試過N多新人，都不知道如何創建全局臨時表、不知道having、不知道聚集索引和非聚集索引，更別提游標和提交叉表查詢了。好吧，這個缺點算是湊數用的，作為屌絲程序員，我們的口號是：沒有不會的，只有不用的。除了少數有語言潔癖的人，我相信精通SQL只是時間問題。
總結
存儲過程最大的優點是部署的方便性——可以在生產環境下直接修改——雖然濫用的后果很嚴重。
存儲過程最大的缺點是SQL語言本身的局限性——我們不應該用存儲過程處理復雜的業務邏輯——讓SQL回歸它“結構化查詢語言”的功用吧。
第四篇：用java調用oracle存儲過程總結
用java調用oracle存儲過程總結
分類： PL/SQL系列 2009-09-24 15:08 253人閱讀 評論(0)收藏 舉報
聲明：
以下的例子不一定正確，只是為了演示大概的流程。
一：無返回值的存儲過程 存儲過程為：
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;
然后呢，在java里調用時就用下面的代碼： 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(driver);
conn = DriverManager.getConnection(strUrl, “ hyq ”, “ hyq ”);
CallableStatement proc = null;
proc = conn.prepareCall(“{ call HYQ.TESTA(?,?)}”);
proc.setString(1, “100”);
proc.setString(2, “TestOne”);
proc.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){
}
}
} }
二：有返回值的存儲過程（非列表）
當然了，這就先要求要建張表TESTTB,里面兩個字段（I_ID，I_NAME）。
存儲過程為： 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里調用時就用下面的代碼： 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:hyq”;
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.TESTB(?,?)}”);
proc.setString(1, “100”);
proc.registerOutParameter(2, Types.VARCHAR);
proc.execute();
String testPrint = proc.getString(2);
System.out.println(“=testPrint=is=”+testPrint);
}
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){
}
}
} }
}
注意，這里的proc.getString(2)中的數值2并非任意的，而是和存儲過程中的out列對應的，如果out是在第一個位置，那就是proc.getString(1)，如果是第三個位置，就是proc.getString(3)，當然也可以同時有多個返回值，那就是再多加幾個out參數了。
三：返回列表
由于oracle存儲過程沒有返回值，它的所有返回值都是通過out參數來替代的，列表同樣也不例外，但由于是集合，所以不能用一般的參數，必須要用pagkage了.所以要分兩部分，1，建一個程序包。如下：
CREATE OR REPLACE PACKAGE TESTPACKAGE AS
TYPE Test_CURSOR IS REF CURSOR;end TESTPACKAGE;
2，建立存儲過程，存儲過程為：
CREATE OR REPLACE PROCEDURE TESTC(p_CURSOR out TESTPACKAGE.Test_CURSOR)IS BEGIN
OPEN p_CURSOR FOR SELECT * FROM HYQ.TESTTB;END TESTC;
可以看到，它是把游標（可以理解為一個指針），作為一個out 參數來返回值的。在java里調用時就用下面的代碼： 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:hyq”;
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.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);
proc.execute();
rs =(ResultSet)proc.getObject(1);
while(rs.next())
{
System.out.println(“
}
}
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){
}
}
} }
四。Hibernate調用存儲過程
Connection con = session.connect();
CallableStatement proc = null;
con = connectionPool.getConnection();
proc = con.prepareCall(“{ call set_death_age(?, ?)}”);proc.setString(1, XXX);
proc.setInt(2, XXx);...proc.execute();
session.close();
在Hibernate中調用存儲過程的示范代碼--
如果底層數據庫（如Oracle）支持存儲過程，也可以通過存儲過程來執行批量更新。存儲過程直接在數據庫中運行，速度更加快。在Oracle數據庫中可以定義一個名為batchUpdateStudent()的存儲過程，代碼如下：
create or replace procedure batchUpdateStudent(p_age in number)as begin update STUDENT set AGE=AGE+1 where AGE>p_age;end;以上存儲過程有一個參數p_age，代表學生的年齡，應用程序可按照以下方式調用存儲過程： tx = session.beginTransaction();Connection con=session.connection();String procedure = “{call batchUpdateStudent(?)}”;CallableStatement cstmt = con.prepareCall(procedure);cstmt.setInt(1,0);//把年齡參數設為0 cstmt.executeUpdate();tx.commit();在以上代碼中，我用的是Hibernate的 Transaction接口來聲明事務，而不是采用JDBC API來聲明事務。
存儲過程中有一個參數p_age，代表客戶的年齡，應用程序可按照以下方式調用存儲過程：
代碼內容
tx = session.beginTransaction();Connection con=session.connection();
String procedure = “{call batchUpdateCustomer(?)}”;
CallableStatement cstmt = con.prepareCall(procedure);
cstmt.setInt(1,0);//把年齡參數設為0
cstmt.executeUpdate();
tx.commit();
CREATE procedure selectAllUsers DYNAMIC RESULT SETS 1 BEGIN
DECLARE temp_cursor1 CURSOR WITH RETURN TO CLIENT FOR
SELECT * FROM test;
OPEN temp_cursor1;END;
映射文件中關于存儲過程內容如下
............
{ ? = call selectAllUsers()}
{ ? = call selectAllUsers()} 也可以寫成{ call selectAllUsers()}，如果有參數就寫成
{ ? = call selectAllUsers(?,?,?)}
代碼中對query設置相應位置上的值就OK Java調用關鍵代碼如下
Session session = HibernateUtil.currentSession();
Query query = session.getNamedQuery(“selectAllUsers”);
List list = query.list();
System.out.println(list);
要求你的存儲過程必須能返回記錄集，否則要出錯
如果你的存儲過程是完成非查詢任務就應該在配置文件用以下三個標簽
setFirstResult(int)和setMaxResults(int)方法來分頁
第五篇：教學：數據庫-存儲過程資料
教學三：存儲過程
一、教學目的
（1）掌握T-SQL流控制語句；（2）掌握創建存儲過程的方法；（3）掌握存儲過程的執行方法；（4）掌握存儲過程的管理和維護。
二、教學內容
1、創建簡單存儲過程
（1）創建一個名為stu_pr的存儲過程，該存儲過程能查詢出051班學生的所有資料，包括學生的基本信息、學生的選課信息（含未選課同學的信息）。要求在創建存儲過程前請判斷該存儲過程是否已創建，若已創建則先刪除，并給出“已刪除！”信息，否則就給出“不存在，可創建！”的信息。
if exists(select name from sysobjects where name='stu_pr'and type='p')begin print '已刪除！' drop procedure stu_pr end else print '不存在，可創建！' go create procedure stu_pr as select * from Student_20103322 left outer join SC_20103322
on(Student_20103322.Sno=SC_20103322.Sno)left outer join Course_20103322 on(Course_20103322.Cno=SC_20103322.Cno)where classno='051' 1
exec stu_pr
2、創建帶參數的存儲過程
（1）創建一個名為stu_proc1的存儲過程，查詢某系、某姓名的學生的學號、姓名、年齡，選修課程名、成績。系名和姓名在調用該存儲過程時輸入，其默認值分別為“%”與“林%”。執行該存儲過程，用多種參數加以測試。
if exists(select name from sysobjects where name='stu_proc1' and type='p')begin
print '已刪除!' drop procedure stu_proc1 end else
print '不存在,可創建!' go create procedure stu_proc1 @Sdept char(8)='%',@Sname varchar(8)='林%' as select Sdept,Student_20103322.Sno,Sname,DATEDIFF(YEAR,Birth,GETDATE())age,Cname,Grade from Student_20103322,SC_20103322,Course_20103322 where Student_20103322.Sno=SC_20103322.Sno and Course_20103322.Cno=SC_20103322.Cno and Sdept like @Sdept and Sname like @Sname
execute stu_proc1 '計算機系','林紅' 3
execute stu_proc1 '信息安全','胡光璟'
（2）創建一個名為Student_sc的存儲過程，可查詢出某段學號的同學的學號、姓名、總成績。（學號起始號與終止號在調用時輸入，可設默認值）。執行該存儲過程。if exists(select name from sysobjects where name='Student_sc'and type='p')begin print '已刪除！' drop procedure student_sc end else print '不存在，可創建！' go create procedure Student_sc @Sno1 char(8),@Sno2 char(8)as select Student_20103322.Sno,Sname,SUM(Grade)總成績 from Student_20103322,SC_20103322,Course_20103322 where Student_20103322.Sno=SC_20103322.Sno and Course_20103322.Cno=SC_20103322.Cno and Student_20103322.Sno>=@Sno1 and Student_20103322.Sno<=@Sno2 group by Student_20103322.Sno,Sname
execute Student_sc '20110000','20110003' 5
3、創建帶輸出參數的存儲過程
（1）創建一個名為Course_sum的存儲過程，可查詢某門課程考試的總成績。總成績可以輸出，以便進一步調用。
if exists(select name from sysobjects where name='Course_sum'and type='p')begin print '已刪除！' drop procedure Course_sum end else print '不存在，可創建！' go create procedure Course_sum @Cname varchar(20),@sum int output as select @sum=sum(Grade)from SC_20103322,Course_20103322 where Course_20103322.Cno=SC_20103322.Cno and Cname=@Cname
group by SC_20103322.Cno,Cname
declare @ping int exec Course_sum '高數',@ping output print '高數的考試總成績為：'+cast(@ping as varchar(20))
（2）創建一執行該存儲過程的批處理，要求當總成績小于100時，顯示信息為：“XX課程的總成績為：XX,其總分未達100分”。超過100時，顯示信息為：“XX課程的總成績為：XX”。
declare @sum int
declare @Cname varchar(20)Exec Course_sum @cname,@sum out begin
if @sum <100 print cast(@cname as varchar)+'課程的總成績為:'+cast(@sum as varchar)+ ',其總分未達分'
else
print cast(@cname as varchar)+'課程的總成績為:'+cast(@sum as varchar)end
declare @sum int
declare @Cname varchar(20)set @Cname='高數' Exec Course_sum @cname,@sum out begin
if @sum <100 print cast(@cname as varchar)+'課程的總成績為:'+cast(@sum as varchar)+ ',其總分未達分'
else
print cast(@cname as varchar)+'課程的總成績為:'+cast(@sum as varchar)end
4、創建帶重編譯及加密選項的存儲過程
創建一個名為update_sc、并帶重編譯及加密選項的存儲過程，可更新指定學號、指定課程號的學生的課程成績。（學號、課程號由調用時輸入）
if exists(select name from sysobjects where name='update_sc'and type='p')begin print '已刪除！' drop procedure update_sc end else print '不存在，可創建！' go create procedure update_sc
@sno char(8),@cno char(3),@grade tinyint with RECOMPILE , ENCRYPTION as update SC_20103322 set Grade=@grade
where Sno=@sno and Cno=@cno
declare @sno char(8),@cno char(3),@grade tinyint set @sno='20103322' set @cno='003' set @grade='100' exec update_sc @sno,@cno,@grade begin print cast(@sno as varchar)+'的'+cast(@cno as varchar)+'課程成績為：'+cast(@grade as varchar)end
5、使用T-SQL語句管理和維護存儲過程
（1）使用sp_helptext查看存儲過程Student_sc的定義腳本
exec sp_helptext student_sc
（2）使用select語句查看Student_sc存儲過程的定義腳本（提示：通過查詢表sysobjects和表syscomments）
select *
from sysobjects,syscomments where name = 'Student_sc'
（3）將存儲過程stu_pr改為查詢學號為2011001的學生的詳細資料。
alter procedure stu_pr as select * from Student_20103322 left outer join SC_20103322 on(Student_20103322.Sno=SC_20103322.Sno)left outer join Course_20103322 on(Course_20103322.Cno=SC_20103322.Cno)where Student_20103322.Sno='2011001'
（4）刪除存儲過程stu_pr。
drop procedure stu_pr
6、使用SQL Server Management Studio管理存儲過程
（1）在SQL Server Management Studio中重新創建剛刪除的存儲過程stu_pr create procedure stu_pr as begin select * from Student_20103322 left outer join SC_20103322 on(Student_20103322.Sno=SC_20103322.Sno)left outer join Course_20103322 on(Course_20103322.Cno=SC_20103322.Cno)where Student_20103322.Sno='2011001' end
（2）查看存儲過程stu_pr，并將該過程修改為查詢051班女生的所有資料。
ALTER procedure [dbo].[stu_pr] as begin select * from Student_20103322 left outer join SC_20103322 on(Student_20103322.Sno=SC_20103322.Sno)left outer join Course_20103322 on(Course_20103322.Cno=SC_20103322.Cno)where Student_20103322.Sno='2011001' and Sex='女' end
（3）刪除存儲過程stu_pr
【完】
點擊咨詢 