PL/SQL¿ª·¢
bulk collectÊÇ¿ÉÒÔ¿´×öÊÇÒ»ÖÖÅú»ñÈ¡µÄ·½Ê½£¬ÔÚÎÒÃǵÄplsqlµÄ´úÂë¶ÎÀï¾³£×÷ΪintoµÄÀ©Õ¹À´Ê¹Ó᣶ÔÓÚselect id into v from .... ÊÇÒ»¸ö³£ÓõÄÓ÷¨¡£²»¹ýÕâÀïÖ»ÄÜÊÇ·µ»Øµ¥Ìõ¼Ç¼µÄʱºò£¬²ÅÄÜʹÓã¬Èç¹ûÊÇÓжàÌõ¼Ç¼ÎÒÃǾͲ»ÄÜÓÃÕâÑùµÄ·½Ê½£¬¶øÊÇʹÓÃfetchºÍÑ»·µÄ·½Ê½£¬²»½öʹÓÃÂé·³£¬¶øÇÒÐÔÄÜÒ²µ×Ï£¬ÕâʱÎÒÃǵÄbulk collect¡Öصdz¡ÁË£¬½â¾öÎÒÃǵÄÎÊÌâ¡£
ͨ¹ýbulk collect¿ÉÒÔ°ÑÎÒÃǵIJéѯ½á¹ûÒ»´ÎÐԵؼÓÔص½ÎÒÃǵÄǶÈë±íÖС£ÕâÑùÎÒÃDz»ÐèÒªºÜÂé·³µÄÓÃÓαêµÄÑ»·Ò»ÌõÒ»ÌõµÄÈ¥fetchß·£¬¿ÉÏë¶øÖª£¬ÕâÑù²»½ö²Ù×÷·½±ã£¬Ò²¿ÉÒÔ»ñµÃÏ൱²»´íµÄ³ÌÐòÐÔÄÜ¡£ ÎÒÃÇ¿ÉÒÔÔÚselect into, fetch into, returning intoµÄ¾ä×ÓÖÐʹÓá£ÏÂÃæÎÒÃÇͨ¹ý¶ÔÒÔÉϸ÷¸öÀý×ÓÀ´½øÐÐÑÝʾÀ´¿´¿´ËûÃǵÄÓ÷¨
ÏÈÀ´¿´¿´ÎÒÃǵIJâÊÔ±íºÍÊý¾Ý
SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
ZC_CODE VARCHAR2(20)
MONEY NUMBER(35)
MONEY_2 NUMBER(35)
SQL> col zc_code format a20;
SQL> col money format 999999.99;
SQL> col money_2 format 9999.99;
SQL>select * from test;
ZC_CODE MONEY MONEY_2
-------------------- ---------- --------
201 18600.00
20101 9600.00
2010101 3300.00
2010102 3200.00
2010103 3100.00
20102 9000.00
2010201 2000.00
2010202 7000.00
8 rows selected.
¶¨ÒåÎÒÃǵÄǶÌ×±íµÄÀàÐÍ£¬ ¶¨ÒåÔÚÒ»¸öpackageÀÕâ¸öpackageʹÎÒÃǵÄÊý¾ÝÀàÐͼ¯ºÏ
SQL> create or replace package ALL_DATA_TYPE is
2 type T_TESTROW is table of test1.test%rowtype index by binary_integer;
3 type T_TESTZCCODE is table of test1.test.zc_code%type index by binary_integer;
4 end;
5 /
ÏÈÀ´¸öselect intoµÄÀý×Ó
SQL> create or replace function test_bulkcollect return ALL_DATA_TYPE.T_TESTROW is
2 v_rtn ALL_DATA_TYPE.T_TESTROW;
3 begin
4 select * bulk collect into v_rtn from test;
5 return v_rtn;
6 end;
7 /
ͳһµÄ²âÊÔ·½·¨£¬ ÎÒÃÇ·Ö±ðÓÃÕâ¸ö²âÊÔ´úÂëÀ´ÑÝʾÒÔÉÏselect into£¬fetch intoºÍr
Ïà¹ØÎĵµ£º
º¯Êý£º
XMLPARSE£º½âÎö×Ö·û»òÕß´ó¶ÔÏó¶þ½øÖÆÊý¾Ý£¬²úÉúXML
XMLSERIALIZE£º½«XMLֵת»»Îª×Ö·û»òÕß´ó¶ÔÏó¶þ½øÖÆÊý¾Ý
XMLVALIDATE£º¸ù¾ÝXML schemaУÑéXMLÖµµÄÓÐЧÐÔ
XMLEXISTS£º¼ì²âXQueryÊÇ·ñ·µ»Ø½á¹û£¨ÈçÓÉÒ»¸ö»ò¶à¸öÏîËù×é³ÉµÄÐòÁУ©
XMLQUERY£ºÖ´ÐÐÒ»¸öXQuery²¢ÒÔÒ»¸öÐòÁзµ»Ø½á¹û
XMLTABLE£ºÖ´ÐÐÒ»¸öXQuery£¬ÒÔ¹ØÏ ......
1.²»ÓÃÍ˳öSQL2000°²×°³ÌÐò,Ö±½ÓÇл»µ½×ÀÃæ.
¡¡¡¡2.´ò¿ª×¢²á±í±à¼Æ÷£¨ÔÚ“ÔËÐДÖÐÇÃÈë“regedit”Ö®ºó»Ø³µ¼´¿É£©£¬¶¨Î»µ½×¢²á±íµÄHKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager½Úµã.
¡¡¡¡3.µ¥»÷“Session Manager”Õâ¸ö½Úµã£¬ ......
/*µ±Ö´ÐÐSQLʱ·¢ÏÖ²»ÄÜÁ¬½ÓÊý¾Ý¿â£¬ÏÈÖØÁ¬Ò»´Î*/
import java.io.FileInputStream;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
......
SUBSTRING
·µ»Ø×Ö·û¡¢binary¡¢text »ò image ±í´ïʽµÄÒ»²¿·Ö¡£ÓйؿÉÓë¸Ãº¯ÊýÒ»ÆðʹÓõÄÓÐЧ Microsoft® SQL Server ......
--´´½¨Óαê
DECLARE Cursor1 CURSOR FOR
SELECT NODE_NO,NODE_VALUE,SUM(RESALE_SUM-RESALERETU_SUM+SALE_SUM-SALERETU_SUM) sale_sum,
SUM(STORE_SUM) sum_store,SUM(STORE_SUM) store_sum,
sum(RESALE_PROFIT-RESALERETU_PROFIT+SALE_PROFIT-SALERETU_PROFIT-ADJUST_PROFIT) sale_profit
from ......