ORACLE PL/SQL ¼¯ºÏѧϰ±Ê¼Ç(¶þ)
Èý¡¢Ç¶Ì×±íµÄʹÓ÷½·¨
1¡¢½«Ç¶Ì×±í¶¨ÒåΪPL/SQLµÄ³ÌÐò¹¹Ôì¿é
TYPE type_name IS TABLE OF element_type[NOT NULL];
ÈçÏÂÀýËùʾ£º
DECLARE
-- Define a nested table of variable length strings.
TYPE card_table IS TABLE OF VARCHAR2(5 CHAR);
-- Declare and initialize a nested table with three rows.
cards CARD_TABLE := card_table(NULL,NULL,NULL);
BEGIN
-- Print title.
dbms_output.put_line(
'Nested table initialized as nulls.');
dbms_output.put_line(
'----------------------------------');
-- Loop through the three records.
FOR i IN 1..3 LOOP
-- Print the contents.
dbms_output.put ('Cards Varray ['||i||'] ');
dbms_output.put_line('['||cards(i)||']');
END LOOP;
-- Assign values to subscripted members of the varray.
cards(1) := 'Ace';
cards(2) := 'Two';
cards(3) := 'Three';
-- Print title.
dbms_output.put (CHR(10)); -- Visual line break.
dbms_output.put_line(
'Nested table initialized as Ace, Two and Three.');
dbms_output.put_line(
'-----------------------------------------------');
-- Loop through the three records to print the varray contents.
FOR i IN 1..3 LOOP
dbms_output.put_line('Cards ['||i||'] '
|| '['||cards(i)||']');
END LOOP;
END;
/
2¡¢½«Ç¶Ì×±íÀàÐͶ¨ÒåºÍÓÃ×÷PL/SQLµÄ¶ÔÏóÀàÐÍ
CREATE OR REPLACE TYPE type_name
AS TABLE OF element_type [NOT NULL];
ÈçÏÂÀýËùʾ£º
-- Define a varray of four rows of variable length strings.
CREATE OR REPLACE TYPE card_unit_varray
AS VARRAY(13) OF VARCHAR2(5 CHAR);
/
-- Define a varray of four rows of variable length strings.
CREATE OR REPLACE TYPE card_suit_varray
AS VARRAY(4) OF VARCHAR2(8 CHAR);
/
-- Define a table of variable length strings.
CREATE OR REPLACE TYPE card_deck_table
AS TABLE OF VARCHAR2(17 CHAR);
/
DECLARE
-- Define a counter to manage 1 to 52 cards in a deck.
counter INTEGER
Ïà¹ØÎĵµ£º
oracle±í¿Õ¼ä²Ù×÷Ïê½â
1
2
3×÷Õߣº À´Ô´£º ¸üÐÂÈÕÆÚ£º2006-01-04
5
6
7½¨Á¢±í¿Õ¼ä
8
9CREATE TABLESPACE data01
10DATAFILE '/ora ......
package¾ÍÊÇÓÃÀ´·â×°¹ý³Ì£¬º¯ÊýÓõġ£
Ò»¸öpackage¿ÉÒÔ´æ·Å¶à¸ö¹ý³Ì£¬º¯Êý¡£
µ÷ÓÃÊÇ£º
°üÃû.¹ý³ÌÃû1
°üÃû.¹ý³ÌÃû2
http://hank4ever.javaeye.com/blog/472336
°ü (package)
¡¡¡¡(1)°üµÄÒâÒå
¡¡¡¡pl/sqlΪÁËÂú×ã³ÌÐòÄ£¿é»¯µÄÐèÒª,³ýÁË¿é(block)ºÍ×Ó³ÌÐò½á¹¹Íâ,»¹ÒýÈëÁ˰üµÄ¹¹Ôì¡£
¡¡¡¡°üÊÇÒ»ÖÖÊý¾Ý¿â¶ÔÏó,½«Â ......
½çÃæ¿ª·¢ÈËÔ±±¨ÓкܶàÖØ¸´Êý¾ÝÔÚÓû§È¨ÏÞ±í¡£È»ºóÎÒɾ³ýÁ˱íÊý¾Ýdelete ·½Ê½£¬ÐÞ¸ÄÁ˶ÔÓ¦µÄ´æ´¢¹ý³Ìʹ֮²»Öظ´£¡
ºóÀ´·¢ÏÖ ÖØÐÂÀ»ØµÄÊý¾ÝûȨÏÞ¡£ Ö»ºÃÉÁ»Øµ½½ñÌìÁ賿ÁË£¡
SQL> ALTER TABLE BA.T_POWER_ADMIN ENABLE ROW MOVEMENT;
Table altered
SQL> flashback table ba.t_Power_Admin to tim ......
ÔÚPL/SQL³ÌÐòÉè¼ÆÖУ¬ÓÐÈýÖÖ¶¨Òå¼Ç¼ÀàÐ͵ķ½·¨£ºÒ»ÖÖÊÇʹÓÃ%ROWTYPEÊôÐÔ£»ÁíÒ»ÖÖÊÇÔÚPL/SQL³ÌÐòµÄÉùÃ÷²¿·ÖÏÔʾ¶¨Òå¼Ç¼ÀàÐÍ£»×îºóÒ»ÖÖ·½·¨Êǽ«¼Ç¼ÀàÐͶ¨ÒåΪÊý¾Ý¿â½á¹¹»ò¶ÔÏóÀàÐÍ¡£
ÎÒÏȼòµ¥µÄ½éÉÜÒ»¸öÏÂÃæÒªÓõ½µÄ±íµÄ½á¹¹£¨ºÚÌå±êÃ÷µÄ×Ö¶ÎΪÖ÷¼ü£©£º
INDIVIDUALS±í
INDIVIDUAL ID
FIRST NAME
MIDDLE_INITI ......