oracle ±Ê¼Ç VI Ö®Óαê (CURSOR)
Óαê(CURSOR),ºÜÖØÒª
Óαê:ÓÃÓÚ´¦Àí¶àÐмǼµÄÊÂÎñ
ÓαêÊÇÒ»¸öÖ¸ÏòÉÏÏÂÎĵľä±ú(handle)»òÖ¸Õë,¼òµ¥Ëµ£¬Óαê¾ÍÊÇÒ»¸öÖ¸Õë
1 ´¦ÀíÏÔʽÓαê
ÏÔʽÓα괦ÀíÐè 4¸ö PL/SQL ²½Öè,ÏÔʾÓαêÖ÷ÒªÓÃÓÚ´¦Àí²éѯÓï¾ä
(1) ¶¨ÒåÓαê
¸ñʽ: CURSOR cursor_name [(partment[,parameter]...)] IS select_statement;
¶¨ÒåµÄÓα겻ÄÜÓÐ INTO ×Ó¾ä
(2) ´ò¿ªÓαê
OPEN cursor_name[...];
PL/SQL ³ÌÐò²»ÄÜÓà OPEN Óï¾äÖØ¸´´ò¿ªÒ»¸öÓαê
(3)ÌáÈ¡ÓαêÊý¾Ý
FETCH cursor_name INTO {variable_list | record_variable};
(4) ¹Ø±ÕÓαê
CLOSE cursor_name;
Àý 1 ²éѯǰ 10 ÃûÔ±¹¤µÄÐÅÏ¢
declare
--¶¨ÒåÓαê
cursor c_cursor is select last_name,salary from employees where rownum < 11 order by salary;
v_name employees.last_name%type;
V_sal employees.salary%type;
begin
--´ò¿ªÓαê
open c_cursor;
-- ÌáÈ¡ÓαêÊý¾Ý
fetch c_cursor into v_name,v_sal;
while c_cursor %found loop
dbms_output.put_line(v_name || ':' || v_sal);
fetch c_cursor into v_name,v_sal;
end loop;
--¹Ø±ÕÓαê
close c_cursor;
end;
----------------------------------
Á·Ï°: ÊäÈ벿ÃźŠdep_id,²éѯ¸Ã²¿Ãŵį½¾ù¹¤×Ê : avg_sal,Ô±¹¤¹¤×ÊΪ salary
Èô salary < avg_sal - 500 ¹¤×ÊÕÇ 500
Èô avg_sal - 500 <= salary < avg_sal + 500 ¹¤×ÊÕÇ 300
Èô
Ïà¹ØÎĵµ£º
1. ´´½¨ÊÓͼ£º
CREATE OR REPLACE VIEW SM_V_UNIT_AUTH AS
SELECT T2.UNIT_ID,
T2.SUPER_UNIT_ID,
T1.AUTH_ID,
T1.AUTH_NAME,
T1.A ......
Union£¬¶ÔÁ½¸ö½á¹û¼¯½øÐв¢¼¯²Ù×÷£¬²»°üÀ¨Öظ´ÐУ¬Í¬Ê±½øÐÐĬÈϹæÔòµÄÅÅÐò£»
Union All£¬¶ÔÁ½¸ö½á¹û¼¯½øÐв¢¼¯²Ù×÷£¬°üÀ¨Öظ´ÐУ¬²»½øÐÐÅÅÐò£»
Intersect£¬¶ÔÁ½¸ö½á¹û¼¯½øÐн»¼¯²Ù×÷£¬²»°üÀ¨Öظ´ÐУ¬Í¬Ê±½øÐÐĬÈϹæÔòµÄÅÅÐò£»
Minus£¬¶ÔÁ½¸ö½á¹û¼¯½øÐвî²Ù×÷£¬²»°üÀ¨Öظ´ÐУ¬Í¬Ê±½øÐÐĬÈϹæÔòµÄÅÅÐò¡£
¿ÉÒÔÔÚ×î ......
1¡¢select TIMESTAMP'2009-11-03 09:26:50.124' from dual --ĬÈϺÁÃë±£´æÁùλ
2¡¢´ËÓï¾äÖ»ÄÜÔÚ·þÎñÆ÷¶ËÔËÐУ¬Èç¹ûÖ´ÐÐÒÔϲÙ×÷£¬ÔòÔÚ²åÈëoracleʱ£¬¿ÉÒÔÖ±½Óдnls_date_formatÉ趨µÄ¸ñʽ£¬µ«ÊÇÖ»ÄÜÕë¶ÔÒ»´Î»æ»£¬Èç¹û¹Øµô´°¿ÚÔòʧЧ¡£
alter session set nls_d ......
OracleÖÐUSERENVºÍSYS_CONTEXTÓÃÀ´·µ»Øµ±Ç°sessionµÄÐÅÏ¢£¬ÆäÖУ¬userenvÊÇΪÁ˱£³ÖÏòϼæÈݵÄÒÅÁôº¯Êý£¬ÍƼöʹÓÃsys_contextº¯Êýµ÷ÓÃuserenvÃüÃû¿Õ¼äÀ´»ñÈ¡Ïà¹ØÐÅÏ¢¡£
1¡¢ USERENV(OPTION)
¡¡¡¡·µ»Øµ±Ç°µÄ»á»°ÐÅÏ¢.
¡¡¡¡OPTION='ISDBA'Èôµ±Ç°ÊÇDBA½ÇÉ«,ÔòΪTRUE,·ñÔòFALSE.
¡¡¡¡OPTION='LANGUAGE'·µ»ØÊý¾Ý¿âµÄ ......
1.ASCII
·µ»ØÓëÖ¸¶¨µÄ×Ö·û¶ÔÓ¦µÄÊ®½øÖÆÊý;
SQL> select ascii(’A’) A,ascii(’a’) a,ascii(’0’) zero,ascii(’ ’) space from dual;
A A ZERO SPACE
--------- --------- --------- ---------
65 97 48 32
2.CHR
¸ø³öÕ ......