仓酷云

标题: MYSQL网页设计Result Sets from Stored Procedures I... [打印本页]

作者: 飘灵儿 时间: 2015-1-16 22:38
标题: MYSQL网页设计Result Sets from Stored Procedures I...
列举选择MySQL的理由的最困难的地方在于，如何对这些理由进行排序。MySQL学习教程这就如同我们经常争论的故事：先有鸡还是先有蛋?oracle

ResultSetsfromStoredProceduresInOracle
Afrequentlyaskedquestionis:

IdliketoknowwhetherORACLEsupportsprocedures(functions)which
returnsresultsets.

Theanswerismostdefinitelyyes.Inshort,itlllooklikethis:

createorreplacefunctionsp_ListEmpreturntypes.cursortype
as
l_cursortypes.cursorType;
begin
openl_cursorforselectename,empnofromemporderbyename;
returnl_cursor;
end;
/
With7.2onupofthedatabaseyouhavecursorvariables.Cursorvariablesarecursorsopenedbyapl/sqlroutineandfetchedfrombyanotherapplicationorpl/sqlroutine(in7.3pl/sqlroutinescanfetchfromcursorvariablesaswellasopenthem).Thecursorvariablesareopenedwiththeprivelegsoftheowneroftheprocedureandbehavejustliketheywerecompletelycontainedwithinthepl/sqlroutine.Itusestheinputstodecidewhatdatabaseitwillrunaqueryon.

Hereisanexample:

createorreplacepackagetypes
as
typecursorTypeisrefcursor;
end;
/createorreplacefunctionsp_ListEmpreturntypes.cursortype
as
l_cursortypes.cursorType;
begin
openl_cursorforselectename,empnofromemporderbyename;returnl_cursor;
end;
/
examplesforSQLPlus,Pro*C,Java/JDBC,ODBC,ADO/ASP,DBIPerlandOCIfollow:
REMSQL*Pluscommandstouseacursorvariablevariablecrefcursor
exec:c:=sp_ListEmp
printc

andthePro*Ctousethiswouldlooklike:
staticvoidprocess()
{
EXECSQLBEGINDECLARESECTION;
SQL_CURSORmy_cursor;
VARCHARename[40];
intempno;
EXECSQLENDDECLARESECTION;EXECSQLWHENEVERSQLERRORDOsqlerror_hard();EXECSQLALLOCATE:my_cursor;EXECSQLEXECUTEBEGIN
:my_cursor:=sp_listEmp;
END;END-EXEC;for(;;)
{
EXECSQLWHENEVERNOTFOUNDDObreak;
EXECSQLFETCH:my_cursorINTO:ename,empno;printf("%.*s,%d
",ename.len,ename.arr,empno);
}
EXECSQLCLOSE:my_cursor;
}
Andthejavatousethiscouldbe:

importjava.sql.*;
importjava.io.*;
importoracle.jdbc.driver.*;
classcurvar
{
publicstaticvoidmain(Stringargs[])
throwsSQLException,ClassNotFoundException
{
Stringdriver_class="oracle.jdbc.driver.OracleDriver";
Stringconnect_string="jdbc:oracle:thin:@slackdog:1521:oracle8";Stringquery="begin:1:=sp_listEmp;end;";
Connectionconn;Class.forName(driver_class);
conn=DriverManager.getConnection(connect_string,"scott","tiger");CallableStatementcstmt=conn.prepareCall(query);
cstmt.registerOutParameter(1,OracleTypes.CURSOR);
cstmt.execute();
ResultSetrset=(ResultSet)cstmt.getObject(1);while(rset.next())
System.out.println(rset.getString(1));
cstmt.close();
}
}

Thefollowingisthankstomarktoml@hotmail.com(marktomlinson)..

IfyouuseODBChereisaworkingexample,butitrequirestheuseofthe
8.0.5.2.0orlaterOracleODBCdriver,andan8.0.5server.

1)Createaformwith1Textcontrol(Text1)and1ListControl(List1)and
1Button(btnExecute).
2)TheonlycodethatyouneedisaClickmethodonyourbutton.HereistheCode.

PrivateSubbtnExecute_Click()
PL/SQLCode
===========

CREATEORREPLACEpackagereftestas
cursorc1isselectenamefromemp;
typeempCurisrefcursorreturnc1%ROWTYPE;
ProcedureGetEmpData(eninvarchar2,EmpCursorinoutempCur);
END;

CREATEORREPLACEpackagebodyreftestas
ProcedureGetEmpData
(eninvarchar2,EmpCursorinoutempCur)is
begin
openEmpCursorforselectenamefromempwhereenameLIKEen;
end;
end;

DimcnAsNewrdoConnection
DimqdAsrdoQuery
DimrsAsrdoResultset
DimclAsrdoColumn
StaticNumberAsIntegerList1.Clear
Number=0
cn.Connect="uid=scott;pwd=tiger;DSN=MSLANGORL;"
enabletheMSCursorlibrary
cn.CursorDriver=rdUseOdbc
Maketheconnection
cn.EstablishConnectionrdNoDriverPromptsSQL="{callRefTest.GetEmpData(?,?)}"Setqd=cn.CreateQuery("",sSQL)qd.rdoParameters(0).Type=rdTypeVARCHAR
qd(0).Direction=rdParamInputOutput
qd(0).Value=Text1.Text
qd.rdoParameters(1).Type=rdTypeVARCHARDynamicorKeysetismeaninglesshere
Setrs=qd.OpenResultset(rdOpenStatic)Do
Debug.Print
Debug.PrintDoUntilrs.EOF
ForEachclInrs.rdoColumns
IfIsNull(cl.Value)Then
List1.AddItem"(null)"
Debug.Print"";cl.Name;"NULL";Errortrapfor
nullfields
Else
List1.AddItemcl.Value
Debug.Print"";cl.Name;"";cl.Value;
EndIf
Next
Debug.Print
rs.MoveNext
Loop
LoopWhilers.MoreResults
cn.CloseEndSub

Andnow,forafullASPexample(thankstoJimHoienandJohnDurst)

<%@Language=VBScript%><%Thisdemonstrationdrawsheavilyfromtheinformationcontainedinthearticleathttp://govt.us.oracle.com/~tkyte/ResultSets/index.htmlwithspecialattentiontothedetailsprovidedbyMarkTomlinson.MakesureyouhavethecorrectOracleODBCdriversoitwillsupportRefCursors.ThisdemonstrationwasajointprojectbyJimHoien(jhoien@yahoo.com)andJohnDurst(jpdurst@yahoo.com)ThesearethestatementsusedontheOracleserver:/*******************************************************************************************//*CreatetheEMPdemotableandpopulate.(extractedfromOraclesprovideddemobld.sql)*//*******************************************************************************************/CREATETABLEEMP(EMPNONUMBER(4)NOTNULL,ENAMEVARCHAR2(10),JOBVARCHAR2(9),MGRNUMBER(4),HIREDATEDATE,SALNUMBER(7,2),COMMNUMBER(7,2),DEPTNONUMBER(2));INSERTINTOEMPVALUES(7369,SMITH,CLERK,7902,17-DEC-80,800,NULL,20);INSERTINTOEMPVALUES(7499,ALLEN,SALESMAN,7698,20-FEB-81,1600,300,30);INSERTINTOEMPVALUES(7521,WARD,SALESMAN,7698,22-FEB-81,1250,500,30);INSERTINTOEMPVALUES(7566,JONES,MANAGER,7839,2-APR-81,2975,NULL,20);INSERTINTOEMPVALUES(7654,MARTIN,SALESMAN,7698,28-SEP-81,1250,1400,30);INSERTINTOEMPVALUES(7698,BLAKE,MANAGER,7839,1-MAY-81,2850,NULL,30);INSERTINTOEMPVALUES(7782,CLARK,MANAGER,7839,9-JUN-81,2450,NULL,10);INSERTINTOEMPVALUES(7788,SCOTT,ANALYST,7566,09-DEC-82,3000,NULL,20);INSERTINTOEMPVALUES(7839,KING,PRESIDENT,NULL,17-NOV-81,5000,NULL,10);INSERTINTOEMPVALUES(7844,TURNER,SALESMAN,7698,8-SEP-81,1500,0,30);INSERTINTOEMPVALUES(7876,ADAMS,CLERK,7788,12-JAN-83,1100,NULL,20);INSERTINTOEMPVALUES(7900,JAMES,CLERK,7698,3-DEC-81,950,NULL,30);INSERTINTOEMPVALUES(7902,FORD,ANALYST,7566,3-DEC-81,3000,NULL,20);INSERTINTOEMPVALUES(7934,MILLER,CLERK,7782,23-JAN-82,1300,NULL,10);/*******************************************************************************************//*Createapackagedprocedurethatacceptsadepartmentnumberandreturnstheemployees.*//*[Note:Astandaloneprocedurewillnotwork,itmustbeapackagedprocedure!]*//*******************************************************************************************/CREATEORREPLACEPACKAGEDEPARTMENTASTYPECURSOR_TYPEISREFCURSOR;PROCEDUREGET_EMPS(I_DEPTNOINNUMBER,O_RESULT_SETOUTCURSOR_TYPE);END;/CREATEORREPLACEPACKAGEBODYDEPARTMENTASPROCEDUREGET_EMPS(I_DEPTNOINNUMBER,O_RESULT_SETOUTCURSOR_TYPE)ASBEGINOPENO_RESULT_SETFORSELECTEMPNO,ENAMEFROMEMPWHEREDEPTNO=I_DEPTNO;END;END;/%><HTML><HEAD><TITLE>OracleADOTest</TITLE></HEAD><BODY><H2>TestofADOandOracleStoredProceduresusingRefCursors</H2><%DimobjConnDimconnStringDimcmdStoredProcDimparam1DimtestDeptNotestDeptNo=10testDeptNo=20testDeptNo=30setobjConn=server.createobject("adodb.connection")SystemDSNconnectionReplacethevaluesbelowwithyourownconnString="DSN=<YourDSN>;UID=<YourUserName>;PWD=<YourPassword>"objConn.OpenconnStringSetcmdStoredProc=Server.CreateObject("ADODB.Command")SetcmdStoredProc.ActiveConnection=objConncmdStoredProc.CommandText="Department.Get_Emps"cmdStoredProc.CommandType=adCmdStoredProcSetparam1=cmdStoredProc.CreateParameter("Dept_ID",adInteger,adParamInput)cmdStoredProc.Parameters.Appendparam1param1.Value=testDeptNoSetrs=cmdStoredProc.ExecuteResponse.Write("<h3>EmployeesinDepartment#"&testDeptNo&"</h3>"&vbCrLf)Response.Write("<p>"&vbCrLf)Response.Write("<table>"&vbCrLf)Response.Write("<tr>"&vbCrLf)Response.Write("<th>Emp#</th>"&vbCrLf)Response.Write("<th>Name</th>"&vbCrLf)Response.Write("</tr>"&vbCrLf)While(Notrs.EOF)Response.Write("<tr>"&vbCrLf)Response.Write("<td>"&rs(0)&"</td>"&vbCrLf)Response.Write("<td>"&rs(1)&"</td>"&vbCrLf)Response.Write("</tr>"&vbCrLf)rs.MoveNextWendResponse.Write("</table>"&vbCrLf)rs.Clo搜索引擎优化bjConn.CloseSetrs=nothingSetparam1=nothingSetcmdStoredProc=nothingSetobjConn=nothing%></BODY></HTML>

AndthefollowingisthankstoBrettRosen:

InoticedthatyoudidnthaveanOCIentryonhttp://osi.oracle.com/~tkyte/ResultSets/index.html.HereisOCIcodetodothis(Oracle81)ifyouwanttoincludeitonthatpage.Someerrorcheckingandcleanuphasbeenremoved,butthebelowshouldwork.(oncedbnamehasbeenreplacedappropriately)Brettintmain(intargc,char*argv[]){OCIError*pOciError;char*pConnectChar="dbname";char*pUsernameChar="scott";char*pPasswordChar="tiger";intanswer;OCIStmt*pOciStatement;char*sqlCharArray="BEGIN:success:=sp_ListEmp;END;";intid;charename[40];OCIEnv*g_pOciEnvironment=NULL;OCIServer*g_pOciServer=NULL;OCISession*g_pOciSession=NULL;OCISvcCtx*g_pOciServiceContext=NULL;sb2*pIndicator=0;sb2*pIndicator2=0;sb2*pIndicator3=0;OCIDefine*pOciDefine;OCIDefine*pOciDefine2;OCIBind*pBind;OCIStmt*cursor;answer=OCIInitialize(OCI_THREADED,NULL,NULL,NULL,NULL);answer=OCIEnvInit(&g_pOciEnvironment,OCI_DEFAULT,0,NULL);answer=OCIHandleAlloc(g_pOciEnvironment,(void**)&pOciError,OCI_HTYPE_ERROR,0,NULL);answer=OCIHandleAlloc(g_pOciEnvironment,(void**)&g_pOciSession,OCI_HTYPE_SESSION,0,NULL);answer=OCIHandleAlloc(g_pOciEnvironment,(void**)&g_pOciServer,OCI_HTYPE_SERVER,0,NULL);answer=OCIHandleAlloc(g_pOciEnvironment,(void**)&g_pOciServiceContext,OCI_HTYPE_SVCCTX,0,NULL);answer=OCIServerAttach(g_pOciServer,pOciError,(unsignedchar*)pConnectChar,strlen(pConnectChar),OCI_DEFAULT);answer=OCIAttrSet(g_pOciSession,OCI_HTYPE_SESSION,(unsignedchar*)pUsernameChar,strlen(pUsernameChar),OCI_ATTR_USERNAME,pOciError);answer=OCIAttrSet(g_pOciSession,OCI_HTYPE_SESSION,(unsignedchar*)pPasswordChar,strlen(pPasswordChar),OCI_ATTR_PASSWORD,pOciError);answer=OCIAttrSet(g_pOciServiceContext,OCI_HTYPE_SVCCTX,g_pOciServer,0,OCI_ATTR_SERVER,pOciError);answer=OCIAttrSet(g_pOciServiceContext,OCI_HTYPE_SVCCTX,g_pOciSession,0,OCI_ATTR_SESSION,pOciError);answer=OCISessionBegin(g_pOciServiceContext,pOciError,g_pOciSession,OCI_CRED_RDBMS,OCI_DEFAULT);answer=OCIHandleAlloc(g_pOciEnvironment,(void**)(&pOciStatement),OCI_HTYPE_STMT,0,NULL);answer=OCIStmtPrepare(pOciStatement,pOciError,(unsignedchar*)sqlCharArray,strlen(sqlCharArray),OCI_NTV_SYNTAX,OCI_DEFAULT);answer=OCIHandleAlloc(g_pOciEnvironment,(void**)(&cursor),OCI_HTYPE_STMT,0,NULL);answer=OCIBindByPos(pOciStatement,&pBind,pOciError,1,&cursor,0,SQLT_RSET,pIndicator2,0,NULL,0,0,OCI_DEFAULT);answer=OCIStmtExecute(g_pOciServiceContext,pOciStatement,pOciError,1,0,NULL,NULL,OCI_COMMIT_ON_SUCCESS);answer=OCIDefineByPos(cursor,&pOciDefine,pOciError,2,&id,sizeof(int),SQLT_INT,pIndicator,0,0,OCI_DEFAULT);answer=OCIDefineByPos(cursor,&pOciDefine2,pOciError,1,ename,40,SQLT_STR,pIndicator3,0,0,OCI_DEFAULT);if(answer==0)while((answer=OCIStmtFetch(cursor,pOciError,1,OCI_FETCH_NEXT,OCI_DEFAULT))==0){printf("fetchedid%dandname%s
",id,ename);}answer=OCIHandleFree(pOciError,OCI_HTYPE_ERROR);return0;}

AndthefollowingDBIperlexampleisthankstoq_richard_chen@yahoo.com(RichardChen):

HelloTom,Iwaslookingforsuchcompilationoftipsonthetopic.IdidnotfindthesectionaboutdoingitusingthepopularperlDBI.AftersomefiddlingIgetitworkingtheretoo.HereisacompleteworkingexamplefollowingyourmodelusingperlDBI.Ithinkitisagoodideathatyouincludethisinyourhowtosothatmorepeoplewillbenefitfromit.ThanksRichardChen$catdemo.pl#!/usr/local/bin/perl-wusestrict;useDBI;useDBD::Oracleqw(:ora_types);my$dbh=DBI->connect(dbi:Oracle:,scott,tiger)ordie$DBI::errstr;my$sth1=$dbh->prepare(q{createorreplacepackagetypesastypecursorTypeisrefcursor;end;});$sth1->execute;>>$sth1=$dbh->prepare(q{createorreplacefunctionsp_ListEmpreturntypes.cursorTypeasl_cursortypes.cursorType;beginopenl_cursorforselectename,empnofromemporderbyename;returnl_cursor;end;});$sth1->execute;$sth1=$dbh->prepare(q{BEGIN:cursor:=sp_ListEmp;END;});my$sth2;$sth1->bind_param_inout(":cursor",$sth2,0,{ora_type=>ORA_RSET});$sth1->execute();while(my@row=$sth2->fetchrow_array){printjoin("|",@row),"
";}

MFC+ODBCVERSION(examplecheckedbyMarcinBuchwald)marcin.buchwald@gazeta.plOracleserversidecodeisjustlikeintheVBexample
CDatabasem_DB;BOOLok=m_DB.OpenEx(_T("DSN=orcl;UID=velvet"),CDatabase::useCursorLib);COraSetset(&m_DB);set.m_Value=Text1.Text;set.Open();while(!set.IsEOF()){//setmemberscontainvaluesofsinglerow//useithereset.MoveNext();}set.Close();whereCOraSet::COraSet(CDatabase*pdb):CRecordset(pdb){m_nParams=1;m_nFields=;m_nDefaultType=snapshot;}CStringCOraSet::GetDefaultSQL(){return_T("{callRefTest.GetEmpData(?,?)}");}
不可否认，MySQL也是一个很好的关系型数据库，或许在技术上它与其他领先的关系数据库相差并不大，或不具有劣势。但是，对于一些企业环境来说，MySQL显然不具有优势。

作者: 若天明 时间: 2015-1-19 20:28
你可以简单地认为适合的就是好，不适合就是不好。

作者: 谁可相欹 时间: 2015-1-28 10:20
如果，某一版本可以提供强大的并发响应，但是没有Oracle的相应版本稳定，或者价格较贵，那么，它就是不适合的。

作者: 莫相离 时间: 2015-2-5 14:58
还不是性能有问题！否则面向对象的数据库早就实现了！建议使用CLR的地方一般是和应用的复杂程度或操作系统环境有很高的耦合度的场景。如你想构建复杂的算法，并且用到了大量的指针和高级数据模型。

作者: 小女巫 时间: 2015-2-12 09:38
对递归类的树遍历很有帮助。个人感觉这个真是太棒了！阅读清晰，非常有时代感。

作者: 只想知道 时间: 2015-3-3 02:24
习惯敲命令行的朋友可能会爽一些。但是功能有限。适合机器跑不动SQLServerManagementStudio的朋友使用。

作者: 若相依 时间: 2015-3-11 08:47
我是新手，正在学习数据库和操作系统，深感理论的泛广，唯有一步一步来，但是又感觉时间不够，收集了很多资料却总是没能认真的看完，希望有一个讨论板块，大家共同解决，共同分享，共同努力

作者: 活着的死人 时间: 2015-3-18 00:46
分区表是个亮点！从分区表也能看出微软要做大作强SQLServer的信心。资料很多，这里不详细说。但是重点了解的是：现在的SQLServer2005的表，都是默认为分区表的。因为它要支持滑动窗口的这个特性。这种特性对历史数据和实时数据的处理是很有帮助的。

作者: 乐观 时间: 2015-3-25 09:18
备份方面可能还是一个老大难的问题。不能单独备份几个表总是感觉不爽。灵活备份的问题不知道什么时候才能解决。

欢迎光临仓酷云 (http://ckuyun.com/)