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
Ïà¹ØÎĵµ£º
Ϊ°üº¬ÁеĶÔÏó£¨ÈçÊÓͼ»ò±í£©µÄÿÁзµ»ØÒ»ÐС£ÏÂÃæÊǰüº¬ÁеĶÔÏóÀàÐ͵ÄÁÐ±í¡£
±íÖµ³ÌÐò¼¯º¯Êý (FT)
ÄÚÁª±íÖµ SQL º¯Êý (IF)
ÄÚ²¿±í (IT)
ϵͳ±í (S)
±íÖµ SQL º¯Êý (TF)
Óû§±í (U)
ÊÓͼ (V)
ÁÐÃû Êý¾ÝÀàÐÍ ËµÃ÷
object_id
int
´ËÁÐËùÊô¶ÔÏóµÄ ID¡£
name
sysname
ÁÐÃû¡£ÔÚ¶ÔÏóÖÐÊÇΨһµÄ¡£
column_id
in ......
sys.identity_columns
ÓÃ×÷±êʶÁеÄÿÁж¼ÔÚ±íÖÐÕ¼Ò»ÐС£
ÁÐÃû Êý¾ÝÀàÐÍ ËµÃ÷
<´Ó sys.columns ¼Ì³ÐµÄÁÐ>
ÓйشËÊÓͼËù¼Ì³ÐÁеÄÁÐ±í£¬Çë²ÎÔÄ sys.columns
seed_value
sql_variant
¸Ã±êʶÁеÄÖÖ×ÓÖµ¡£ÖÖ×ÓÖµµÄÊý¾ÝÀàÐÍÓëÁб¾ÉíµÄÊý¾ÝÀàÐÍÏàͬ¡£
increment_value
sql_variant
¸Ã±êʶÁеÄÔöÁ¿Öµ¡£ÖÖ×ÓÖµµÄÊ ......
QL·ÖÀࣺ
DDL—Êý¾Ý¶¨ÒåÓïÑÔ(CREATE£¬ALTER£¬DROP£¬DECLARE)
DML—Êý¾Ý²Ù×ÝÓïÑÔ(SELECT£¬DELETE£¬UPDATE£¬INSERT)
DCL—Êý¾Ý¿ØÖÆÓïÑÔ(GRANT£¬REVOKE£¬COMMIT£¬ROLLBACK)
Ê×ÏÈ,¼òÒª½éÉÜ»ù´¡Óï¾ä£º
1¡¢ËµÃ÷£º´´½¨Êý¾Ý¿â
CREATE DATABASE database-name
2¡¢ËµÃ÷£ºÉ¾³ýÊý¾Ý¿â
drop database dbname
3¡¢ ......
sqlºËÐÄÓï¾ä(·Ç³£ÊµÓõö¼¼ÇÉ)²åÈëÊý¾Ý
Ïò±íÖÐÌí¼ÓÒ»¸öмǼ£¬ÄãҪʹÓà sql INSERT Óï¾ä¡£ÕâÀïÓÐÒ»¸öÈçºÎʹÓÃÕâÖÖÓï¾äµÄÀý×Ó£º
INSERT mytable (mycolumn ......
½ñÌìÔÚ´¦ÀíÒ»¸öÓû§ÃûÊý¾Ý¿âʱ£¬·¢ÏÖÓÐЩ²»Õý³£µÄÊý¾Ý´æÔÚ£¬°´ÕÕÂß¼£¬Óû§ÃûÖ»ÄÜÊÇÊý×Ö£¬×Öĸ£¬Ï»®Ïߺʹ¿ÖÐÎÄÕâÑùµÄ×Ö·û×éºÏ´æÔÚ£¬²»Ó¦¸ÃÓÐÆäËû×éºÏ´æÔÚ£¬µ«ÊÇ·¢ÏÖÊý¾Ý¿âÖÐÓÉÓÚ¸÷ÖÖÀúÊ·ÔÒò£¬ÓÐЩ²»Õý³£µÄ´æÔÚ£¬ÈçºÎÕÒµ½ÕâЩÒì³£Êý¾Ý£¬ÔÚCSDNµÄ SQL Server °æÎÊÁËÕâÑùÁ½¸öÎÊÌ⣬ÈçÏ£º http://topic.csdn.net/u/20100111 ......