PL/SQLÓû§Ö¸ÄÏÓë²Î¿¼ PL/SQLÓ¦ÓóÌÐòÐÔÄܵ÷ÓÅ£¨×ª£©
µÚÊ®¶þÕ PL/SQLÓ¦ÓóÌÐòÐÔÄܵ÷ÓÅ
1¡¢PL/SQLÐÔÄÜÎÊÌâµÄÔµÓÉ
Ó¦»ùÓÚPL/SQLµÄÓ¦ÓóÌÐòÊ©ÐÐЧÂʵÍÏÂʱ£¬Í¨³£ÊÇÒòΪ²»ºÃµÄSQL»°Óï¡¢±à³Ì²½Ö裬¶ÔPL/SQL»ù´¡ÕÆÎÕÔã¸â»òÊÇÂÒÓù²ÏíÄÚ´æ´¢Æ÷´Ù³ÉµÄ¡£
•PL/SQLÖв»ºÃµÄSQL»°Óï
PL/SQL±à³Ì¿´ÉÏÈ¥Ïà¶ÔÕսϼòµ¥£¬ÓÉÓÚËüÃǵĸ´ÔÓÄÚÈݶ¼ÑÚ²ØÔÚSQL»°ÓïÖУ¬SQL»°Óï¾³£·Öµ£´óÁ¿µÄ¹¤×÷¡£ÕâÄËÊÇΪºÎ²»ºÃµÄSQL»°ÓïÊÇÊ©ÐÐЧÂʵÍϵÄÖØÒªÔµ¹ÊÁË¡£ÈçÈôÒ»¸ö³ÌÐòÖаüÔкܶ಻ºÃµÄSQL»°ÓÄÇô£¬ÎÞÂÛÊÇPL/SQL»°ÓïдµÄÓкÎÆäÃÀ¶¼ÊÇÓÚÊÂÎÞ²¹µÄ¡£
ÈçÆäSQL»°Óï¼õµÍÁËÎÒÃǵijÌÐòËٶȵĻ°£¬½«Òª°´µ×ÏÂÁбíÖеIJ½Öè·ÖÎöÒ»ÏÂ×ÓËüÃǵÄÖ´Ðмƻ®ºÍÐÔÄÜ£¬Æäºó´Óбà×ëSQL»°Óï¡£±ÈÈ磬²éѯÓÅ»¯Æ÷µÄ½Òʾ¾Í¿ÉÄÜ»áÅųýµôÎÊÌ⣬ÈçûÓбØÒªµÄÈ«±íɨÃè¡£
Ò».EXPLAIN PLAN»°Óï
¶þ.Ê©ÓÃTKPROFµÄSQL TraceЧÄÜ
Èý.Oracle TraceЧÄÜ
•Ôã¸âµÄ±à³ÌÏ°Æø
Õý³££¬Ôã¸âµÄ±à³ÌÏ°ÆøÒ²»á¸ø³ÌÐò´ø»Ø¸ºÃæÓ°Ïì¡£ÕâÖÖÇé¿öÏ£¬¼´Ê¹ÊÇÓÐÐĵõijÌÐòԱд³öµÄ´úÂëÒ²Ò²Ðí·Á°ÐÔÄÜ·¢»Ó¡£
ÖÁÓÚ¸ø¶¨µÄÒ»ÏîÈÎÎñ£¬ÎÞÂÛÊÇËùÑ¡µÄ³ÌÐòÓïÑÔÓкεÈÊʺϣ¬±à×ëÆ·ÖʽϲîµÄ×Ó³ÌÐò(±ÈÈ磬һ¸öºÜÂýµÄ·ÖÃűðÀà»ò¼ìË÷º¯Êý)»òÐí»ÙµôÕû¸öÐÔÄÜ¡£¼ÙÉèÓÐÒ»¸ö¼±Ðè±»Ó¦ÓóÌÐòƵ·±µ÷ÓõIJéѯº¯Êý£¬ÈçÆäÕâ¸öº¯Êý²»ÊÇÓ¦ÓùþÏ£»ò¶þ·Ö·¨£¬¶øÊÇÖ±½ÓÔËÓÃÏßÐÔ²éÑ°£¬¾Í»á´ó´óÓ°ÏìЧÂÊ¡£Ôã¸âµÄ³ÌÐòÖ¸µÄÊÇÄÇЩ´øÓдÓδÓйýÔËÓùýµÄ±äÁ¿µÄ£¬´«ËÍûÓбØÒªµÄ²ÎÊýµÄ£¬°Ñ³õʼ»¯»ò¼ÆËã·Åµ½Óò»×ŵÄÑ»·ÖÐÊ©ÐеijÌÐòÖ®Àà¡£
•ÄÚÖú¯ÊýµÄ·´¸´
PL/SQLÌṩÁ˺öà¸ß¶ÈÓÅ»¯¹ýµÄº¯Êý£¬ÈçREPLACE¡¢TRANSLATE¡¢SUBSTR¡¢INSTR¡¢RPADºÍLTRIMµÈ¡£²»ÓÃÊÖ¹¤±à׫ÎÒÃÇ×Ô¸÷¶ùµÄ°æ±¾£¬ÓÉÓÚÄÚÖú¯ÊýÒѾÊǺܸßЧÂʵÄÁË¡£¼´»òÄÚÖú¯ÊýµÄЧÄÜÔ¶Ô¶³¬¹ýÎÒÃǵÄؽÐ裬Ҳ²»ÓÃÊÖ¹¤¶ÒÏÖËüÃÇЧÄܵÄ×Ó¼¯¡£
•µÍЧµÄÁ÷³ÌͳÖÆ»°Óï
ÔÚ¼ÆËãÂß¼±í´ïʽֵµÄʱ·Ö£¬PL/SQLÔËÓöÌ·µÄ¼ÆËãģʽ¡£ÇÒ²»Ëµ£¬ÍòÒ»½á¹û¿ÉÒÔ±»È·¶¨ÏÂÀ´£¬PL/SQL¾Í»áÖÕÖ¹Óàϵıí´ïʽ¼ÆËã¡£±ÈÈ磬ϲ¿µÄOR±í´ïʽ£¬Ó¦sal±È1500СµÄʱ·Ö£¬²Ù×÷·û×ó±ßµÄÖµÄËÊÇTRUE£¬Ö®ËùÒÔPL/SQL¾Í²»»áÔÙ¼ÆËã²Ù×÷·ûÓÒ²à±í´ïʽµÄÖµ£º
IF (sal < 1500) OR (comm IS NULL) THEN
...
END IF;
ÏÖÏ£¬¿¼ÂÇϲ¿µÄAND±í´ïʽ£º
IF credit_ok(cust_id) AND (loan < 5000) THEN
...
END IF;
ÔÚÉÏÃæµÄº¯ÊýÖУ¬²¼¶ûº¯Êýcredit_okÀÏÊDZ»µ÷Óᣵ«ÊÇ£¬ÈçÆäÎÒÃÇÏòµ×ÏÂÕâôÍË»»Á½¸ö±í´ïʽµÄλÖãº
IF (loan < 5000) AND credit_ok(cust_id) THEN
...
E
Ïà¹ØÎĵµ£º
create database test1
use test1
create table admin
(
id int primary key ,
name varchar(50),
pwd varchar(50),
)
insert into admin values(1,'aa','aa')
alter table admin add tel varchar(50) ......
×î½üÓÐÅóÓÑÓöµ½Ê¡ÊеÄÎÊÌ⣬ÏëÏë×Ô¼º½ñºóÒ²ÓпÉÄÜ»áÓöµ½£¬ËùÒÔ¾Í×Ô¼ºÔÚÍøÉÏдд£¬ËÑËÑ£¬Ö÷ÒªÊǶÔ×Ô¼º½ñºóÓвο¼
--´´½¨Êý¾Ý¿â
create database NationalAll
Go
--ʹÓÃNationalAllÊý¾Ý¿â
use NationalAll
Go
--´´½¨Ê¡¼¶±í
Create Table Province
(
ProID int primary key not null,
ProName nvarchar(50) n ......
ÏîÄ¿ÖÕÓÚ½áÊøÁË£¬×ܽáµÄʱºòµ½ÁË... hehe :)
ÔÚÏîÄ¿ÖÐÎÒÃÇÓöµ½Á˺ܶàµÄÎÊÌ⣬±ê×¼SQLʹÓþÍÊÇÆäÖÐÒ»¸ö¡£ ÒòΪÎÒÃÇÔÚ×öBI packageµÄʱºò£¬Ò»¿ªÊ¼¶¼ÊÇ»ùÓÚMS SQL À´×öµÄ£¬ËùÒÔUniverseµÄÉè¼ÆÉÏҲûÓÐÌ«¶àµÄ¿¼ÂÇ¡£ µ±ºóÀ´ÀÏ´ó¸æËßÎÒÅ ......
--Excelµ¼Èësql´æ´¢¹ý³Ì£¨¼òµ¥£©
Create procedure import
@tablename varchar(100),
@filepath varchar(100)
as
EXEC( 'INSERT INTO ' + @tablename +
' SELECT * from
OpenDataSource( ''Microsoft.Jet.OLEDB.4.0'',''Data Source="' + @filepath + '";
......
SQL²Ù×÷È«¼¯
ÏÂÁÐÓï¾ä²¿·ÖÊÇMssqlÓï¾ä£¬²»¿ÉÒÔÔÚaccessÖÐʹÓá£
SQL·ÖÀࣺ
DDL—Êý¾Ý¶¨ÒåÓïÑÔ(CREATE£¬ALTER£¬DROP£¬DECLARE)
DML—Êý¾Ý²Ù×ÝÓïÑÔ(SELECT£¬DELETE£¬UPDATE£¬INSERT)
DCL—Êý¾Ý¿ØÖÆÓïÑÔ(GRANT£¬REVOKE£¬COMMIT£¬ROLLBACK)
Ê×ÏÈ,¼òÒª½éÉÜ»ù´¡Óï¾ä£º
1¡¢ËµÃ÷£º´´½¨Êý¾Ý¿â
CREATE ......