PL/SQLѧϰ±Ê¼ÇÎå
ÓαêÊÇ´ÓÊý¾Ý¿âÖÐÌáÈ¡³öÀ´µÄÊý¾Ý£¬ÒÔÁÙʱ±íµÄÐÎʽ´æ·ÅÔÚÄÚ´æÖУ¬ÔÚÓαêÖÐÓÐÒ»¸öÊý¾ÝÖ¸Õ룬ÔÚ³õʼ״̬ÏÂÖ¸ÏòÊ׼Ǽ£¬ ÀûÓÃfetchÓï¾äÒÆ¶¯¸ÃÖ¸Õ룬´Ó¶ø¶ÔÓαêÖеÄÊý¾Ý½øÐи÷ÖÖ²Ù×÷¡£
1.¶¨ÒåÓαê
cursor ÓαêÃû is selectÓï¾ä;
2.´ò¿ªÓαê
open ÓαêÃû;
3.ÌáÈ¡ÓαêÊý¾Ý
fetch ÓαêÃû into ±äÁ¿Ãû1, ±äÁ¿Ãû2, ....;
»ò
fetch ÓαêÃû into ¼Ç¼ÐͱäÁ¿Ãû;
4.¹Ø±ÕÓαê
close ÓαêÃû;
5.ÓαêµÄÊôÐÔ
%isopen
¸ÃÊôÐÔ±êʶÓαêÊÇ·ñ´ò¿ª£¬Èç¹ûûÓдò¿ªÓαê¾ÍʹÓÃfetchÓï¾ä½«Ìáʾ´íÎó¡£
%isfound
¸ÃÊôÐÔ±êʶһ¸öfetchÓï¾äÊÇ·ñÓÐÖµ£¬ÓÐÖµ·µ»Øtrue£¬·ñÔò·µ»Øfalse¡£
%notfound
¸ÃÊôÐÔÓë%isfoundÏà·´¡£
%rowcount
¸ÃÊôÐÔÓÃÓÚ»ñÈ¡ÓαêµÄÊý¾ÝÐÐÊý¡£
ʾÀý£º
set serveroutput on
declare
tempsal scott.emp.sal%type;
cursor mycursor is
select * from scott.emp
where sal>tempsal;
cursorrecord mycursor%rowtype;
begin
tempsal:=800;
open mycursor;
if mycursor%isopen then
fetch mycursor into cursorrecord;
dbms_output.put_line(to_char(cursorrecord.deptno));
dbms_output.put_line(to_char(mycursor%rowcount));
else
dbms_output.put_line('Óα껹δ´ò¿ª');
end if;
end;
Ïà¹ØÎĵµ£º
½ñÌìºÜÓôÃÆ,ʵÑéÊҵķþÎñÆ÷¶«Î÷¶¼Ã»ÓÐÁË,¶øÎÒµÄÊý¾Ý¿âÓÖÔÚÄÄÀï,ÎÒÓÖûÓб¸·Ý,°ÑÎÒÒÖÓôµÄÒªÃü,ÐÒ¿÷ÎÒÓõÄlinq to sql.×ÐϸÏëÏë,ËûµÄdbmlÎļþ»ù±¾°üº¬ÁËÊý¾Ý¿âµÄËùÓÐÄÚÈÝ,ËùÒÔ,Ò»¶¨Óа취´ÓdbmlÎļþÖд´½¨Êý¾Ý¿â!
Ñо¿ÁËÒ»¶ÎÊ ......
ALTER procedure [dbo].[sp_lock_check]
@spid1 int = NULL,
@spid2 int = NULL
as
set nocount on
if @spid1 is not NULL
begin
select ......
SQLÓÅ»¯µÄÔÔòÊÇ£º½«Ò»´Î²Ù×÷ÐèÒª¶ÁÈ¡µÄBLOCKÊý¼õµ½×îµÍ¡£
µ÷Õû²»Á¼SQLͨ³£¿ÉÒÔ´ÓÒÔϼ¸µãÇÐÈ룺
¼ì²é²»Á¼µÄSQL£¬¿¼ÂÇÆäд·¨ÊÇ·ñ»¹ÓпÉÓÅ»¯ÄÚÈÝ£»
¼ì²é×Ó²éѯ¿¼ÂÇSQL×Ó²éѯÊÇ·ñ¿ÉÒÔÓüòµ¥Á¬½ÓµÄ·½Ê½½øÐÐÖØÐÂÊéд£»
¼ì²éÓÅ»¯Ë÷ÒýµÄʹÓã»
¿¼ÂÇÊý¾Ý¿âµÄÓÅ»¯Æ÷;
²éѯµÄÒ»°ã¹æÔò
Ø ......
Select * from t_user_profile where convert ( varchar ( 21 ),regDate, 120 ) like ' 2008-05-07% ' ±íÃû³Æ£ºt_user_profile ÈÕÆÚ×Ö¶ÎÃû³Æ£ºregDate
Select * from t_user_profile where convert(varchar(21),regDate,120) like '2008-05-07%'< ......
ʲôÊÇPL/SQL
PL/SQL(Procedural Language/SQL,¹ý³Ì»¯SQLÓïÑÔ£©ÊÇOracle¹«Ë¾ÔÚ±ê×¼SQLÓïÑԵĻù´¡ÉÏ·¢Õ¹µÄÓïÑÔ£¬Ëü½«±äÁ¿¡¢¿ØÖƽṹ¡¢¹ý³ÌºÍº¯ÊýµÈ½á¹¹»¯³ÌÐòÉè¼ÆµÄÒªËØÒýÈëSQLÓïÑÔ£¬´Ó¶øÄܹ»±àÖÆ±È½Ï¸´ÔÓµÄSQL³ÌÐò£»ÀûÓÃPL/SQLÓïÑÔ±àдµÄ³ÌÐò³ÆÎªPL/SQL³ÌÐò¿é£¬ÆäÖ÷ÒªÌØµãÈçÏ£º
¾ßÓÐÄ£¿é»¯µÄ½á¹¹
ʹÓùý³Ì»¯ÓïÑÔ¿ØÖƽ ......