ORACLEPL/SQL»ù´¡
ORACLEPL/SQL»ù´¡--ÓαêµÄѧϰÀ´Ô´£º
Óαê×ÖÃæÀí½â¾ÍÊÇÓζ¯µÄ¹â±ê¡£
ÓÃÊý¾Ý¿âÓïÑÔÀ´ÃèÊö£ºÓαêÊÇÓ³ÉäÔÚ½á¹û¼¯ÖÐÒ»ÐÐÊý¾ÝÉϵÄλÖÃʵÌ壬ÓÐÁËÓαêÓû§¾Í¿ÉÒÔ·ÃÎʽá¹û¼¯ÖеÄÈÎÒâÒ»ÐÐÊý¾ÝÁË£¬½«Óαê·ÅÖõ½Ä³Ðк󣬼´¿É¶Ô¸ÃÐÐÊý¾Ý½øÐвÙ×÷£¬ÀýÈçÌáÈ¡µ±Ç°ÐеÄÊý¾ÝµÈµÈ¡£
ÓαêµÄ·ÖÀࣺ
ÏÔʽÓαêºÍÒþʽÓαê
ÏÔʾÓαêµÄʹÓÃÐèÒª4²½£º
1.ÉùÃ÷Óαê
CURSOR mycur(vartype number) is
select emp_no,emp_zc from cus_emp_basic
where com_no = vartype;
2.´ò¿ªÓαê
open mycur(000627) ×¢£º000627:²ÎÊý
3.¶ÁÈ¡Êý¾Ý
fetch mycur into varno,varprice;
4.¹Ø±ÕÓαê
close mycur;
ÓαêµÄÊôÐÔ
oracle ÓαêÓÐ4¸öÊôÐÔ£º %ISOPEN £¬ %FOUND £¬ %NOTFOUND£¬ %ROWCOUNT
%ISOPEN ÅжÏÓαêÊÇ·ñ±»´ò¿ª£¬Èç¹û´ò¿ª%ISOPEN µÈÓÚtrue,·ñÔòµÈÓÚfalse
%FOUND %NOTFOUND ÅжÏÓαêËùÔÚµÄÐÐÊÇ·ñÓÐЧ£¬Èç¹ûÓÐЧ£¬Ôò%FOUNDDµÈÓÚtrue£¬·ñÔòµÈÓÚfalse
%ROWCOUNT ·µ»Øµ±Ç°Î»ÖÃΪֹÓαê¶ÁÈ¡µÄ¼Ç¼ÐÐÊý¡£
ʾÀý£º
set serveroutput on;
declare
varno varchar2(20);
varprice varchar2(20);
CURSOR mycur(vartype number) is
select emp_no,emp_zc from cus_emp_basic
where com_no = vartype;
begin
if mycur%isopen = false then
open mycur(000627);
end if;
fetch mycur into varno,varprice;
while mycur%found
loop
dbms_output.put_line(varno||’,’||varprice);
if mycur%rowcount=2 then
exit;
end if;
fetch mycur into varno,varprice;
end loop;
close mycur;
end;
pl/sql ¼Ç¼ µÄ½á¹¹ºÍcÓïÑÔÖеĽṹÌåÀàËÆ£¬ÊÇÓÉÒ»×éÊý¾ÝÏî¹¹³ÉµÄÂß¼µ¥Ôª¡£
pl/sql ¼Ç¼²¢²»±£´æÔÙÊý¾Ý¿âÖУ¬ËüÓë±äÁ¿Ò»Ñù£¬±£´æÔÙÄÚ´æ¿Õ¼äÖУ¬ÔÚʹÓüǼʱºò£¬ÒªÊ×Ïȶ¨Òå¼Ç¼½á¹¹£¬È»ºóÉùÃ÷¼Ç¼±äÁ¿¡£¿ÉÒÔ°Ñpl/sql¼Ç¼¿´×÷ÊÇÒ»¸öÓû§×Ô¶¨ÒåµÄÊý¾ÝÀàÐÍ¡£
set serveroutput on;
declare
type person is record
(
empno cus_emp_basic.emp_no%type,
Ïà¹ØÎĵµ£º
ÔÚPostgreSQL8.1.3ÉÏ¿ÉÒÔwork
1. È¡Êý¾Ý¿â´óС
SELECT pg_size_pretty(pg_database_size('somedatabase')) As fulldbsize
2. È¡±í´óС
SELECT pg_size_pretty(pg_total_relation_size('someschema.sometable')) As fulltblsize, pg_size_pretty(pg_relation_size('someschema.sometable')) As justthetblsize
ÔÌù£º
......
Ò»¡¢ÕâÊÇÕâ¸öϵÁеÄ×îºóÒ»½ÚÁË£¬×Ô¶¨Ò帴ÔÓÊý¾ÝÀàÐÍÄ¿Ç°Ö»ÄÜͨ¹ýCLRÀ´ÊµÏÖ¡£ÎªÁËÔÚ SQL Server ÖÐÔËÐУ¬ÄúµÄ UDT ±ØÐëʵÏÖ UDT ¶¨ÒåÖеÄÒÔÏÂÒªÇó£º
1.¸Ã UDT ±ØÐëÖ¸¶¨ Microsoft.SqlServer.Server.SqlUserDefinedTypeAttribute¡£System.SerializableAttribute ¿ÉÑ¡Ó㬵«½¨ÒéʹÓá£
2.UDT ±ØÐëͨ¹ý´´½¨¹«¹²µÄ static£¨M ......
SQLÖеÄʱ¼äº¯Êý·Ç³£ÓÐÓã¬ÌرðÊÇÔÚÎÒÃǽøÐгõʼ¸³Öµ¡¢¸´ÔÓ²éѯµÄʱºò£¬¾ÍÏÔµÃÌر𷽱㡣
1¡¢»ñµÃϵͳµ±Ç°Ê±¼ä
select getdate()
2¡¢DateName ( datepart , date )·µ»Ø±íʾָ¶¨ÈÕÆÚµÄÖ¸¶¨ÈÕÆÚ²¿·ÖµÄ×Ö·û´®¡£
--½ñÌìÊÇ2009-2-24--ÐÇÆÚ¶þ
SELECT DATENAME(year, getdate()) AS 'Year&nbs ......
½ñÌìÔÚһ̨Ðé»úÉÏ°²×° sql server 2005£¬¾ÓÈ»±¨1´íÁË£¨ÎÞÓïÁË£¬×°Õâ¸ö¶«Î÷¶¼Åª²»ºÃ%>_<%£©¿ªÊ¼ÒÔΪÊÇÒòΪADÓòµÄÔÒò£¬ºóÀ´É¾µôÓòÓÖÖؽ¨Óò£¬È»ºóÔÙ×°£¬ÓÖ±¨2´í£¬ÓÚÊÇÎʱðÈËËÑË÷µÈµÈ£¬ÕÒµ½ÔÒò˵ҪÖØÐÂ×¢²áinstaller£¬×îÖÕ°²×°³É¹¦ÁË£¬º¦ÎÒ¼Ó°àŪµÄ£¬¸ßÐ˵ÄÊÇ°²×°ºÃÀ²£¬¿ÉÊÇÎÒûÓÐŪÃ÷°×µ½µ×ÊÇɶÔÒòµ¼ÖÂsql°²×°²»ÁËÄØ£ ......
SQL SERVER ºÍEXCELµÄÊý¾Ýµ¼Èëµ¼³ö
1¡¢ÔÚSQL SERVERÀï²éѯExcelÊý¾Ý:
-- ======================================================
SELECT *
from OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]
ÏÂÃæÊǸö²éÑ ......