mysql存儲過程拼sql

㈠ mysql存儲過程動態拼接sql時能用函數嗎

能在詳細點嗎，沒看明白，拼接過程中可以用mysql以外的函數，拼接成後 只能用mysql函數了

㈡ mysql存儲過程能不能直接執行拼接的sql語句

當然可以，就是在mysql存儲過程中使用動態sql，就可以拼接sql，然後執行了。

給你復制一段，如果不滿意，自己搜索 mysql存儲過程動態sql就可以了

;
CREATEPROCEDURESearchByDoctor(
INDoctorIdVARCHAR(50),
INdeptIdVARCHAR(50),
INbeginDateVARCHAR(20),
INendDateVARCHAR(20),
INStandDeptIdVARCHAR(50),
INOperationFlagVARCHAR(50),
INSsczflIdVARCHAR(50),
OUTOperNumINT,
OUTAvgDangerIndexDOUBLE,
OUTOperGrCaseINT
)
BEGIN
	DECLAREcal1VARCHAR(800);
DECLAREcal2VARCHAR(800);

	SETcal1="SELECTCOUNT(1),AVG(DANGER_INDEX)INTO@para1,@para2FROMyw_ssxxbWHERE1=1";
SETcal2="SELECTCOUNT(1)INTO@para3FROMgr_grbwWHEREOPE_RELIDIN(SELECTRELIDFROMyw_ssxxbWHERE1=1";
#拼接醫生id
SETcal1=CONCAT(cal1,"","ANDOPEDOC_ID=","'",DoctorId,"'");
SETcal2=CONCAT(cal2,"","ANDOPEDOC_ID=","'",DoctorId,"'");

#拼接科室id
	IFdeptId<>''THEN
	SETcal1=CONCAT(cal1,"ANDDEPT_ID=","'",deptId,"'");
ENDIF;
#拼接開始結束日期
IFbeginDate<>''ANDendDate<>''THEN
SETcal1=CONCAT(cal1,"","ANDOPER_ATBETWEEN","'",beginDate,"'","AND","'",endDate,"'");
ENDIF;

#拼接標准科室
IFStandDeptId<>''THEN
SETcal1=CONCAT(cal1,"","ANDDEPT_IDIN(_DEPT_ID=","'",StandDeptId,"'",")");
ENDIF;
#拼接數據來源
IFOperationFlag<>''THEN
SETcal1=CONCAT(cal1,"","ANDOPEPARTKINDIDIN(SELECTIDFROMzh_ssczflWHEREFLAG=","'",OperationFlag,"'",")");
ENDIF;
#拼接手術操作類別
IFSsczflId<>''THEN
SETcal1=CONCAT(cal1,"","ANDOPEPARTKINDID=","'",SsczflId,"'");
ENDIF;
SETcal2=CONCAT(cal2,")");
	SET@sql1=cal1;
SET@sql2=cal2;
	PREPAREstmt1FROM@sql1;
	EXECUTEstmt1;
	DEALLOCATEPREPAREstmt1;
PREPAREstmt2FROM@sql2;
	EXECUTEstmt2;
	DEALLOCATEPREPAREstmt2;
SETOperNum=@para1;
SETAvgDangerIndex=@para2;
SETOperGrCase=@para3;
END;

㈢ mysql存儲過程一個參數有多個逗號分割

這個應該可以使用字元串拼接，你是不是這樣寫存儲過程： create procere p_proc(var_in int) begin delete from [tableName] where [colName] in (var_in); end 然後又這樣調用： call p_proc(1, 2, 3); 這樣肯定不行，那麼你可以這樣：

㈣ mysql使用存儲過程將多條（4條以上）SQL使用 union all 合並

BEGIN
declaresel_sqlVARCHAR(100);
declareg_sqlVARCHAR(100);
declareu_sqlVARCHAR(100);
declaret_nameVARCHAR(100);
DECLAREdoneINTDEFAULTFALSE;
declaremonth__monthwhereflag=1;
=true;
setsel_sql='(selectsum(fee+fee2+fee3)Asfee,monthfromfee_detail_';/*左括弧*/
setg_sql='groupbymonth)';/*右括弧*/
setu_sql='unionall';/*加空格、上同*/
OPENmonth_cursor;
my_loop:loop
FETCHmonth_cursorintot_name;
ifdonethen
leavemy_loop;
endif;
ifs_sqlisnullthen
set@s_sql=CONCAT(sel_sql,t_name,g_sql);/*第一個SQL*/
else
set@s_sql=CONCAT(s_sql,u_sql,sel_sql,t_name,g_sql);/*開始Union後續SQL*/
endif;
endloop;
/*最後執行一次拼接後的SQL語句*/
PREPAREpre_s_sqlfrom@s_sql;
EXECUTEpre_s_sql;
deallocatepreparepre_s_sql;
closemonth_cursor;
END

思路修正，貌似還有不少語法問題，自行修復。

㈤ mysql存儲過程求大神(拼接字元串)

DROPPROCEDUREIFEXISTS`p_create_view`

CREATEPROCEDUREp_create_view()
BEGIN
DECLAREtableNameVARCHAR(100);
DECLAREstopFlagINT;
DECLAREsql1VARCHAR(1000);
DECLAREcursor_nameCURSORFORSELECTTABLE_NAMEFROMinformation_schema.tablesWHERETABLE_NAMELIKE'test_2014%';
'02000'SETstopFlag=1;
SET@sql1='createviewtestasselectid,namefrom';
OPENcursor_name;
REPEAT
FETCHcursor_nameINTOtableName;
SELECTCONCAT(@sql1,tableName,'UNIONALLSELECTid,nameFROM')INTO@sql1FROMDUAL;
UNTILstopFlagENDREPEAT;
CLOSEcursor_name;
SELECTSUBSTR(@sql1,1,LENGTH(@sql1)-31)INTO@sql1FROMDUAL;
PREPAREstepFROM@sql1;
EXECUTEstep;
END;

㈥ mysql存儲過程中調用Prepare返回值

事實上MySQL還不支持從動態sql中返回結果，既不能用PrepareStatement來聲明游標，游標只能用嵌入式SQL來聲明。
所以你要實現這個功能就要考慮其它的辦法，給你一個建議就是創建一個臨時表（Create temporary TableName）,將你查詢出的結果存放的這個臨時表裡，然後你可以對這張臨時表進行操作。但是也很復雜的！！！~

㈦ mysql存儲過程能不能直接執行拼接的sql語句

prepare statement_name from 'sql_text'
execute statement_name using [ variable1,variable2]
deallocate prepare statement_name

㈧ laravel 怎麼調用mysql存儲過程

laravel並沒有提供直接調用存儲過程的實現,只能通過拼sql的方式調用存儲過程,比如:

DB::statement('call存儲過程名稱(參數)');

或者

DB::select('call存儲過程名稱(參數)');

㈨ mysql 存儲過程 資料庫表名欄位作為參數傳遞給存儲過程的方法

直接將資料庫表名欄位作為字元類型傳到存儲過程中，存儲過程拼接sql語句，再用exec執行。例如：
CREATE PROCEDURE [proc_test]
(@tablename varchar(100))
WITH
EXECUTE AS CALLER
AS
declare @sql varchar(1000);
set @sql='select * from '+@tablename
exec(@sql)
GO

再直接執行 exec proc_test '學生表'