PL/SQLÓαê±äÁ¿µÄµ÷ÓÃÓë×÷ΪÊä³ö²ÎÊýµÄÓ¦ÓÃ
½ñÌìÓиöѧÉúÎÊÎÒµ÷ÓðüÀïÃæ¾ßÓÐÓαêÀàÐ͵ÄÊä³ö²ÎÊý£¬¿ÉÊÇÔÚÓÃPL/SQL³ÌÐòµ÷ÓõÄʱºò¾ÓÈ»±¨³ö²ÎÊý¸öÊý»òÀàÐÍ´íÎóµÄÒì³£ÐÅÏ¢£¬ÓÚÊÇ×Ô¼ºÒ²ÊÔÁËһϣ¬¹ûÈ»Êdzö´íÁË¡£È»ºó¾Í×Ô¼º×ÁÄ¥ÁËһϡ¢²é²é×ÊÁϺÍÎÊÎÊͬÊ£¬ÔÀ´ÎÊÌâÊǺܼòµ¥µÄ£¬ÏÈ¿´Ò»Ï³ÌÐòµÄ½á¹¹´úÂ룺
CREATE OR REPLACE PACKAGE Pkg_Student
IS
TYPE StudentType IS REF CURSOR RETURN TB_STUDENT%ROWTYPE;
PROCEDURE Pro_student
(
pi_ClassId IN TB_STUDENT.CLASSID%TYPE,
Po_student OUT StudentType
);
END Pkg_Student;
--ʵÏÖ°üÌå´úÂë
CREATE OR REPLACE PACKAGE BODY Pkg_Student
IS
PROCEDURE Pro_student
(
pi_ClassId IN TB_STUDENT.CLASSID%TYPE,
Po_student OUT StudentType
)
AS
BEGIN
IF pi_ClassId=1 THEN
OPEN Po_student FOR SELECT * from TB_STUDENT WHERE TB_STUDENT.CLASSID=1;
ELSIF pi_ClassId=2 THEN
OPEN Po_student FOR SELECT *from TB_STUDENT WHERE TB_STUDENT.CLASSID=2;
ELSE
OPEN Po_student FOR SELECT *from TB_STUDENT WHERE TB_STUDENT.CLASSID=3;
END IF;
END;
END Pkg_Student;
--Ö´Ðе÷ÓðüÀïÃæµÄ´æ´¢¹ý³Ì
DECLARE
TYPE StudentType IS REF CURSOR RETURN TB_STUDENT%ROWTYPE;
v_student StudentType;
v_ClassID TB_STUDENT.CLASSID%TYPE:=1;
BEGIN
Pkg_Student.Pro_student(v_ClassID,v_student);
END;
ÏñÉÏÃæÄÇÑùÖ´Ðоͻá³öÏÖ²ÎÊý¸öÊý»òÀàÐÍ´íÎó£»ÓÚÊÇ£¬¾ÀÕýÁËһϴúÂëÈçÏ£º
DECLARE
v_student Pkg_Student.StudentType;
v_ClassID TB_STUDENT.CLASSID%TYPE:=1;
BEGIN
Pkg_Student.Pro_student(v_ClassID,v_student);
END;
ÔÒòÈçÏ£º
1¡¢StudentType±¾ÉíÊÇÔÚ°üÀïÃæÉùÃ÷ºÍÓ¦Óã¬Æä×÷ÓÃÓòÒ²ÔÚ°üÄÚ²¿¶øÒÑ£»
2¡¢ÈôÍⲿ³ÌÐòÒªµ÷ÓðüÀïÃæ¾ßÓÐÓαêÀàÐͲÎÊýµÄ¹ý³Ì»òº¯Êýʱ£¬ÄÇô±ØÐëÔÚÍⲿÏÔʾµ÷Óðü
ÀïÃæµÄÓαêÉùÃ÷£¬¶ø²»ÊÇÔÚÍâÃæÖØÐ¶¨ÒåÒ»¸öÓαêÉùÃ÷ºÍÓαê±äÁ¿¡£¶øÇÒ×¢ÒâµÄÊÇÓαê±äÁ¿±¾ÉíÒ²ÊǶ¯Ì¬µÄ£¬ÊÇÒ»¸öÒýÓÃÀàÐÍ£¬²»ÒÀÀµÓÚ¹¤×÷Çø¡£
ºÃÁË£¬¶ÔÓÚÕâ¸öÎÊÌâµÄ·ÖÎö¾Íµ½Õâ±ßÁË.........Ï£Íû¶Ô±ðÈËÓаïÖú¡££¨°ïÖúËûÈ˾ÍÊǰïÖú×Ô¼º£©
Ïà¹ØÎĵµ£º
AcessÓëSQLµÄÇø±ð
ÒÔÏÂ總結ÁË×Ô¼ºÔÚ項Ä¿ÖÐËùÓöµ½µÄÓÐ關Acess與SQL²î異µÄһЩµØ·½£º
1£¬¶ÔÓÚÈÕÆÚ×Ö¶Î×Ö¶Î
¡¡¡¡access±íʾΪ:#1981-28-12#
¡¡¡¡SQLSERVER2000±íʾΪ:''1981-02-12''
¡¡¡¡2,SQLÓï¾äÇø±ð£¬_select,_updateÔÚ¶Ôµ¥±í²Ù×÷ʱ¶¼²î²»¶à£¬
¡¡¡¡µ«¶à±í²Ù×÷ʱupdateÓï¾ ......
SQL Server Out Put Excel File
ÔÚ SQL ServerÖÐ, µ¼³öEXCEL Îļþ, Óõ½ bcp.exe
bcp µ¼³öµÄ±¾ÖÊÊÇ´¿Îı¾Îĵµ,
ÈôÊý¾Ýº¬ÓÐÖÐÎÄ,Çëµ¼³öµ½ÖÐÎİæEXCEL,»òTXTÎĵµµÈ, ·ñÔòÂÒÂë....
ÓÃTXT ´ò¿ªÓ¢ÎİæEXCEL,Ò²¿ÉÒÔ,
µ¼³ö Êý¾Ýµ½C:\authors.xls, ÈôÎļþ´æÔÚÔòÖØÐ´Îļþ, ²»´æÔÚÔò´´½¨Îļþ
  ......
¼ÇÒ»¸ö×ÔÈÏΪдµÃÓе㸴ÔÓµÄsqlÓï¾ä£¬º¬ÒåÊÇ¿ç3ÕűíµÄselect£º
select table_name,column_name,data_type,data_length,data_scale from user_tab_columns where (data_type != 'NUMBER' or data_length != 22 or data_scale != 6) and (table_name,column_name) in (select upper(a.table_name_eng), upper(b.column_name_ ......
²Ù×÷Êý¾Ý¿â¹ý³ÌÖУ¬Óöµ½Ðí¶àÎÊÌ⣬ºÜ¶à¶¼ÓëSQL CODEºÍSQL StateÏà¹Ø£¬ÏÖÔÚ°ÑÒ»¸öÍêÕûµÄSQLCODEºÍSQLState´íÎóÐÅÏ¢ºÍÏà¹Ø½âÊÍ×÷ÒÔÏÂ˵Ã÷£¬Ò»À´¿ÉÒÔ×Ô¼º²Î¿¼£¬¶ÔDB2´íÎó×ÔÐÐÕÒ³öÔÒò
£¨ÉùÃ÷£ºÕâÊÇËѼ¯ÍøÉϵÄ×ÊÁϵÃÀ´µÄ,Ïêϸ³ö´¦²»¼ÇµÃÁË£©
sqlcode sqlstate ˵Ã÷
000 00000 SQLÓï¾ä³É¹¦Íê³É
01xxx SQLÓï¾ä³É¹¦Íê³É£¬µ«ÊÇÓ ......
µ¼³ö.sqlÎļþ
1.½«Êý¾Ý¿âtransfer_server_dbµ¼³öµ½transfSRV.sqlÎļþÖУº
mysqldump -u root -p transfer_server_db > /home/eric/transfSRV.sql
2.½«Êý¾Ý¿âtransfer_server_dbÖеÄdevice_info_tableµ¼³öµ½table.sqlÎļþÖУº
mysqldump -u root -p transfer_server_db device_info_table > /home/eric/ta ......