PL/SQLÖÐRef CursorµÄÓ¦ÓÃ
PL/SQL×÷Ϊoracle¶ÔÓÚ´«Í³SQLÓï·¨µÄÀ©Õ¹,ÔÚ¿çϵͳÊÂÎñ´¦ÀíÒÔ¼°¶ÔÊý¾Ý¿â´óÅúÁ¿Êý¾Ý´¦Àí·½Ãæ×÷ÓÃÏÔÖø;¶øµ±Ç°Ö÷Á÷µÄMVC¿ò¼Ü¶ÔDAO²ãµÄ·â×°¶¼ÊÇÒ»ÌõSQLÓï¾ä·µ»ØÒ»¸ö¼Ç¼¼¯;µ±Êý¾ÝµÄ¹ØÏµ±È½Ï¸´ÔÓ»òÊý¾ÝÁ¿±È½Ï´óµÄʱºò,ÓëÊý¾Ý¿âµÄ½»»¥¹ýÓÚÆµ·±½«Ôö´óÄÚÍøµÄÊý¾Ý´«ÊäÁ¿ÓëÊý¾Ý¿âµÄIO.Õë¶Ô´ËÀิÔÓµÄÊý¾Ý´¦Àí,¿ÉÒÔ²ÉÓÃPL/SQL³ÌÐò°üÀ´ÊµÏÖÆäÒµÎñÂß¼.
ÄÜÔÚ×Ó³ÌÐòÖ®¼ä¹²ÏíÊý¾ÝµÄref cursorÔÚÕâ·½ÃæºÝ´óÓÅÊÆ,ÏÂÃæÊÇÎÒ×Ô¼ºÐ´µÄÒ»¸öÀý×Ó:
³ÌÐò°üÍ·:
Create or replace Package WesleyPackage
as
Type Test_CURSOR IS REF CURSOR;
Procedure SelectByProcedure( QueryRecordNumStatment1 in varchar ,QueryTotalRecordInfoStatment2 in varchar , QueryPageInfoStatment3 in varchar,CurrentPage4 in number , ShowRange5 in number , RecordNum6 out number ,TotalInfoRS7 out WesleyPackage.Test_CURSOR, PageInfoRS8 out WesleyPackage.Test_CURSOR );
End WesleyPackage;
³ÌÐò°üÌå:
--´øÓÐout²ÎÊýµÄ´æ´¢¹ý³Ì±ØÐëͨ¹ýjava jdbcµÄCallableStatementµ÷Ó÷½Ê½´òÓ¡Êä³ö½á¹û
--´æ´¢¹ý³ÌµÄ²ÎÊý²»ÄÜΪ ibatisÖÐµÄ po¶ÔÏó
Set serveroutput on;
Create or replace Package Body WesleyPackage as
Procedure SelectByProcedure( QueryRecordNumStatment1 in varchar
,QueryTotalRecordInfoStatment2 in varchar
, QueryPageInfoStatment3 in varchar,CurrentPage4 in number
, ShowRange5 in number , RecordNum6 out number
,TotalInfoRS7 out WesleyPackage.Test_CURSOR
, PageInfoRS8 out WesleyPackage.Test_CURSOR ) as --×¢ÒâûÓÐCreate
beginRowNum number;--µ±Ç°Ò³µÄÊ×Ìõ¼Ç¼µÄÐòºÅ
endRowNum number; --µ±Ç°Ò³µÄÄ©Ìõ¼Ç¼µÄÐòºÅ
showNum number; --¸ù¾Ý²éѯµÃµ½µÄ×ÜÌõÊýºÍÏÔʾ·¶Î§µÃµ½µÄÒ»¸ö½ÏСֵ
showRecordPerPage number:= 30;--ÿҳÏÔʾµÄÌõÊý
pageSQL varchar2(300);
Begin
execute immediate to_char(QueryRecordNumStatment1) i
Ïà¹ØÎĵµ£º
·µ»Ø´ú±íÖ¸¶¨ÈÕÆÚµÄÖ¸¶¨ÈÕÆÚ²¿·ÖµÄÕûÊý¡£
Óï·¨
DATEPART ( datepart ,date )
²ÎÊý
datepart
ÊÇÖ¸¶¨Ó¦·µ»ØµÄÈÕÆÚ²¿·ÖµÄ²ÎÊý¡£Ï±íÁгöÁË Microsoft® SQL Server™ ʶ±ðµÄÈÕÆÚ²¿·ÖºÍËõд¡£
ÈÕÆÚ²¿·ÖËõд
year
yy, yyyy
quarter
qq, q
month
mm, m
dayofyear
dy, y
day
dd, d
week
wk, ww
weekda ......
ת×Ô£ºhttp://topic.csdn.net/u/20100109/09/70f82fb7-eabc-4140-9573-5f8395e0f62e.html
²éѯËÙ¶ÈÂýµÄÔÒòºÜ¶à£¬³£¼ûÈçϼ¸ÖÖ£º
¡¡¡¡1¡¢Ã»ÓÐË÷Òý»òÕßûÓÐÓõ½Ë÷Òý(ÕâÊDzéѯÂý×î³£¼ûµÄÎÊÌ⣬ÊdzÌÐòÉè¼ÆµÄȱÏÝ)
¡¡¡¡2¡¢I/OÍÌÍÂÁ¿Ð¡£¬ÐγÉÁËÆ¿¾±Ð§Ó¦¡£
¡¡¡¡3¡¢Ã»Óд´½¨¼ÆËãÁе¼Ö²éѯ²»ÓÅ»¯¡£
¡¡¡¡4¡¢ÄÚ´æ²»×ã
¡¡¡ ......
½ñÌìÔÚ´¦ÀíÒ»¸öÓû§ÃûÊý¾Ý¿âʱ£¬·¢ÏÖÓÐЩ²»Õý³£µÄÊý¾Ý´æÔÚ£¬°´ÕÕÂß¼£¬Óû§ÃûÖ»ÄÜÊÇÊý×Ö£¬×Öĸ£¬Ï»®Ïߺʹ¿ÖÐÎÄÕâÑùµÄ×Ö·û×éºÏ´æÔÚ£¬²»Ó¦¸ÃÓÐÆäËû×éºÏ´æÔÚ£¬µ«ÊÇ·¢ÏÖÊý¾Ý¿âÖÐÓÉÓÚ¸÷ÖÖÀúÊ·ÔÒò£¬ÓÐЩ²»Õý³£µÄ´æÔÚ£¬ÈçºÎÕÒµ½ÕâЩÒì³£Êý¾Ý£¬ÔÚCSDNµÄ SQL Server °æÎÊÁËÕâÑùÁ½¸öÎÊÌ⣬ÈçÏ£º http://topic.csdn.net/u/20100111 ......
CREATE PROCEDURE dbo.OutputData
@tablename varchar(200)
AS
declare @column varchar(1000)
declare @ ......