OracleÓÃÓαê·Ö½âºÅÂë´ÎÊý
drop table tb_wjf_xh_dg100_50_tmp4 purge;
create table tb_wjf_xh_dg100_50_tmp4
(
servnumber varchar(11)
)
;
declare
vv_cusor_servnumber varchar2(32);
vv_cusor_lost_cnt integer;
vv_servnumber_tmp varchar2(32);
vv_lost_cnt_tmp integer;
cursor v_cursor is
select servnumber,lost_cnt from tb_wjf_xh_dg100_50_tmp3;
begin
open v_cursor;
loop
fetch v_cursor into vv_cusor_servnumber, vv_cusor_lost_cnt;
exit when v_cursor%notfound;
vv_servnumber_tmp := vv_cusor_servnumber;
vv_lost_cnt_tmp := vv_cusor_lost_cnt;
while(vv_lost_cnt_tmp > 0) loop
insert into tb_wjf_xh_dg100_50_tmp4 values(vv_servnumber_tmp);
commit;
vv_lost_cnt_tmp := vv_lost_cnt_tmp-1;
end loop;
end loop;
close v_cursor;
end;
/
Ïà¹ØÎĵµ£º
ȨÏÞ(Privilege)ÊÇÖ¸Ö´ÐÐÌØ¶¨ÀàÐÍSQLÃüÁî»ò·ÃÎÊÆäËû·½°¸¶ÔÏóµÄȨÀû,ȨÏÞ°üÀ¨ÏµÍ³È¨Ï޺ͶÔÏóȨÏÞÁ½ÖÖÀàÐÍ.ϵͳȨÏÞ(System Privilege)ÊÇÖ¸Ö´ÐÐÌØ¶¨ÀàÐÍSQLÃüÁîµÄȨÀû,ËüÓÃÓÚ¿ØÖÆÓû§¿ÉÒÔÖ´ÐеÄÒ»¸ö»òÒ»×éÊý¾Ý¿â²Ù×÷.³£ÓõÄϵͳȨÏÞ:
CREATE SESSION Á¬½Óµ½Êý¾Ý¿â
CREATE TABLE ½¨±í
CREATE VIEW ½¨Á¢ÊÓͼ
CREATE PUBLI ......
step1
ÔÚÁª»úʱ×ö±¸·Ý(»ùÓÚ»Ö¸´Ä¿Â¼µÄ±¸·Ý£¬×öÁË¿ØÖÆÎļþµÄ×Ô¶¯±¸·Ý)£¬°üÀ¨ËùÓÐÊý¾ÝÎļþ¼°¹éµµµÄÈÕÖ¾Îļþ£º
rman>run{
backup format 'c:\bak\test_full_%u' database;
sql 'alter system archive log current';
backup format 'c:\bak\test_log_%u' archivelog all delete input;
}
step2
sql>insert into l ......
1. use database as archive log mode and set archive dest location
alter system set LOG_ARCHIVE_DEST_1 = 'LOCATION=/u04/arch/orcl' scope=spfile;
2. configuration retention days/size/distination
ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=43200; --30 days
ALTER SYSTEM SET db_recovery_file_dest_ ......
°üÓɰü¹æ·¶ºÍ°üÌåÁ½²¿·Ö×é³É¡£
1¡¢°ü¹æ·¶£¨Package Specification£©
°ü¹æ·¶£¬Ò²½Ð×ö°üÍ·£¬°üº¬ÁËÓйذüµÄÄÚÈݵÄÐÅÏ¢¡£µ«ÊÇ£¬Ëü²»°üº¬Èκιý³ÌµÄ´úÂë¡£
´´½¨°üÍ·µÄÓï·¨Ò»°ãÈçÏÂ
CREATE [OR REPLACE] PACKAGE package_name {IS | AS}
Procedure_name | function_name | variable_declaration | type_def ......
1¡¢´¥·¢Æ÷µÄ¸ÅÄî
´¥·¢Æ÷Ò²ÊÇÒ»ÖÖ´øÃûµÄPL/SQL¿é¡£´¥·¢Æ÷ÀàËÆÓÚ¹ý³ÌºÍº¯Êý£¬ÒòΪËüÃǶ¼ÊÇÓµÓÐÉùÃ÷¡¢Ö´ÐкÍÒì³£´¦Àí¹ý³ÌµÄ´øÃûPL/SQL¿é¡£Óë°üÀàËÆ£¬´¥·¢Æ÷±ØÐë´æ´¢ÔÚÊý¾Ý¿âÖв¢ÇÒ²»Äܱ»¿é½øÐб¾µØ»¯ÉùÃ÷¡£
¶ÔÓÚ´¥·¢Æ÷¶øÑÔ£¬µ±´¥·¢Ê¼þ·¢ÉúµÄʱºò¾Í»áÏÔʽµØÖ´Ðиô¥·¢Æ÷£¬²¢ÇÒ´¥·¢Æ÷²»½ÓÊܲÎÊý¡£
´´½¨´¥·¢Æ÷µÄÓï·¨È ......