declare sum_i int=0; begin for i in reverse 1..100 loop if mod(i,2)=0 then sum_i:=sum_i+i; end if; end loop; dbms_output.put_line('前100个自然数中偶数之和是：'||sum_i); end; /
SQL>create or replace procedure insert_dept( num_deptno in number, var_ename in varchar2, var_loc in varchar2) is begin insert into dept values(num_deptno,var_ename,var_loc); commit; end insert_dept; / 向存储过程传入参数可以有3种方式：
create [or replace] function fun_name[(parameter1[,parameter2]...) return data_type is [inner_variable] begin plsql_sentence; [exception] [dowith _ sentences;] end [fun_name];
create [or replace] trigger tri_name [before | after |instead of ] tri_event on table_name |view_name | user_name |db_name [for each row ] [when tri_conditon] begin plsql_sentence; end tri_name;
trigger：表示创建触发器的关键字； before | after | instead of : 表示“触发时机”的关键字。before表示在执行DML等操作之后触发， 这种方式能够防止某些错误操作发生而便于回滚或实现某些业务规则； after表示在DML等操作发生之后，这种方式便于记录该操作或做某些事后处理信息； instead of表示触发器为替代触发器； on ：表示操作的数据表、视图、用户模式和数据库等，对它们执行某种数据操作，将引起触发器的运行。 for each row：指定触发器为行级触发器，当DML语句对每一行数据进行操作时都会引起该触发器的运行。如果未指定该条件，则表示创建语句级触发器，这是无论数据操作影响多少行，触发器都只会执行一次。 tri_event：触发事件，比如常用的有：INSERT、UPDATE、DELETE、CREATE、ALTER、DROP等。 table_name | view_name | user_name | db_name：分别表示操作的数据表、视图、用户模式和数据库，对它们的某些操作将引起触发器的运行。 tri_condition：表示触发条件表达式，只有当该表达式的值为true时，遇到触发事件才会自动执行触发器，使其执行触发操作；
6.4 程序包
程序包是由PL/SQL程序元素和匿名PL/SQL块、命名PL/SQL块组成。
程序包通常由规范和包主体组成。
6.4.1 程序包的规范
程序包一定要在“包主体”之前被创建，语法如下： create [or replace] package pack_name is [declare_variable]; [declare_type]; [declare_cursor]; [declare_function]; [declare_procedure]; end [pack_name];
SQL>alter system set log_archive_dest1='location=D:\OracleFiles\archive1 optional'; SQL>alter system set log_archive_dest2='location=D:\OracleFiles\archive2 mandatory reopen=400';
●实例的所有UNDO表空间 SQL>select tablespace_name from dba_tablespaces where contents='UNDO';
●UNDO表空间的统计信息 可通过查询动态性能视图V$UNDOSTAT收集UNDO统计信息； SQL>select to_char(begin_time,'hh24:mi:ss') as 开始时间,to_char(end_time,'hh24:mi:ss') as 结束时间,undoblks as 回退块数 from v$undostat order by begin_time;
●首先确认表是否删除； SQL>select * from student5; ●查询数据字典视图RECYCLEBIN来了解该表是否在回收站中； SQL>select object_name,original_name from recyclebin where original_name='STUDENT5'; ●使用FLASHBACK TABLE语句恢复被删除的student5表； SQL>flashback table student5 to before drop; ●查看表是否已恢复； SQL>select * from student5;
#创建一个商品零售表，然后为该表按照销售日期所在的季度创建4个分区： create table ware_retail_part ( id integer primary key, retail_date date, ware_name varchar2(50) ) partition by range(retail_date) ( partition par_01 values less than(to_date('2011-04-01','yyyy-mm-dd')) tablespace TBSP_1, partition par_02 values less than(to_date('2011-07-01','yyyy-mm-dd')) tablespace TBSP_1, partition par_03 values less than(to_date('2011-10-01','yyyy-mm-dd')) tablespace TBSP_2, partition par_04 values less than(to_date('2012-01-01','yyyy-mm-dd')) tablespace TBSP_2 );
#插入记录 insert into ware_retail_part values(1,to_date('2011-01-20','yyyy-mm-dd'),'平板电脑'); insert into ware_retail_part values(2,to_date('2011-04-15','yyyy-mm-dd'),'s 智能手机'); insert into ware_retail_part values(3,to_date('2011-07-25','yyyy-mm-dd'),'sMP5');
#查询记录 SQL>select * from ware_retail_part partition(par_02); #查询数据表ware_retail_part中par_02分区的全部记录；
Range分区的字段可以是两个或者多个。
SQL>create table ware_retail_part2 ( id integer primary key, retail_date date, ware_name varchar2(50) ) partition by range(id,retail_date) ( partition par_01 values less than(10000,to_date('2011-12-01','yyyy-mm-dd')) tablespace TBSP_1, partition par_02 values less than(20000,to_date('2012-12-01','yyyy-mm-dd')) tablespace TBSP_1, partition par_03 values less than(maxvalue,maxvalue) tablespace TBSP_2 15 );
create table clients ( id integer primary key, name varchar2(50), province varchar2(20) ) partition by list(province) ( partition shandong values('shandong'), partition guangdong values('guangdong'), partition yunnan values('yunnan') );
11.2.4 组合分区
create table person2 ( id number primary key, name varchar2(20), sex varchar2(2) ) partition by range(id) subpartition by hash(name) subpartitions 2 store in(tbsp_1,tbsp_2) ( partition par1 values less than(5000), partition par2 values less than(10000), partition par3 values less than(maxvalue) );
create table saleRecord ( id number primary key, goodsname varchar2(50), saledate date, quantity number ) partition by range(saledate) interval (numtoyminterval(1,'year')) ( partition par_fist values less than (to_date('2012-01-01','yyyy-mm-dd')) );
函数numtoyminterval的功能是将数字转换成INTERVAL YEAR TO MONTH文字。
#2.创建一个存储学生成绩的分区表studentgrade，该表共有3个分区； create table studentgrade ( id number primary key, name varchar2(10), subject varchar2(10), grade number ) partition by range(grade) ( partition par_nopass values less than(60) tablespace ts_1, partition par_pass values less than(70) tablespace ts_2, partition par_good values less than(maxvalue) tablespace ts_3 );
#3.根据表分区创建本地索引分区，索引分区也有3个 create index grade_index on studentgrade(grade) local ( partition p1 tablespace ts_1, partition p2 tablespace ts_2, partition p3 tablespace ts_3 );
#4. 查询索引分区信息 select partition_name,tablespace_name from dba_ind_partitions where index_name='GRADE_INDEX'; 11.5.3 全局索引分区
全局索引分区就是没有与分区表相同分区键的分区索引。
当分区中出现许多事务并且要保证所有分区中的数据记录唯一时，采用全局索引分区。
11.6 管理索引分区
11.6.2 索引分区管理的实际操作
（1）删除索引分区
使用ALTER INDEX...DROP PARTITION语句；
SQL>alter index index_saleprice drop partition p2; #删除索引分区；
使用ALTER INDEX...REBUILD PARTITION语句重建索引分区；
（2）重命名索引分区
重命名索引分区的语法：alter index index_name rename partition partition_old_name to partition_new_name; ******************************************************************************************************************** 十二、用户管理与权限分配
授予对象权限给用户或角色也使用GRANT命令，语法如下：grant obj_privi | all column on schema.object to user | role | public [with grant option] | [with hierarchy option]
obj_privi：表示对象的权限，可以是ALTER、EXECUTE、SELECT、UPDATE和INSERT等； role：角色名； user：被授权的用户名； with admin option：表示被授权者可再将系统权限授予其他的用户； with hierarchy option：在对象的子对象（在视图上再建立视图）上授权给用户；
SQL>grant select,insert,delete,update on scott.emp to xifang;
12.3.5 回收对象权限
使用REVOKE命令，语法： revoke obj_privi | all on schema.object from user | role | public cascade constraints
SQL>select sum(gets) as 请求存取数, sum(getmisses) as 不命中数 from v$rowcache;
使用视图V$SESSTAT和V$STATNAME查询Orace收集对话信息使用的总内存的统计；
#显示当前分配给所有会话的内存数 SQL>select sum(value)|| '字节' as 当前分配给所有会话的内存数 from v$sesstat,v$statname where name='session uga memory' and v$sestat.statistic#=v$statnam.statistic#;
#显示曾经分配给所有会话的最大内存数 SQL>select sum(value)|| '字节' as 曾经分配给所有会话的最大内存数 from v$sesstat,v$statname where name='session uga memory max' and v$sestat.statistic#=v$statnam.statistic#;
create or replace procedure tun_table(table_deleted in varchar2) as #实现清空指定的表 cur_name integer; #定义内部变量，存储打开的游标 begin cur_name:=dbms_sql.open_cursor; #打开游标 dbms_sql.parse(cur_name,'truncate table'||table_deleted||'drop storage',dbms_sql.navite); #指定truncate语句清空指定的表 dbms_sql.close_cursor(cur_name); exception when others then dbms_sql.close_cursor(cur_name); #出现异常，关闭游标 raise; end trun_table;
