10年读pl/sql编程指南敲下了上面大部分例子二(备用)

--(4)在forall子句中使用bulk子句
         declare 
         type ename_table_type is table of emp.ename%type;
         type sal_table_type is table of emp.sal%type;
         ename_table ename_table_type;
         sal_table sal_table_type;
         sql_stat varchar2(100);
         begin
         ename_table:=ename_table_type('SCOTT','ins','SMITH');
         sql_stat:='update emp set sal=sal*1.1 where ename=:1 returning sal into :2';
         forall i in 1..ename_table.count 
         execute immediate sql_stat using ename_table(i) returning bulk collect into sal_table;
         for j in 1..ename_table.count loop
         dbms_output.put_line(ename_table(j)||' '||sal_table(j));
         end loop;
         end;
    --对象类型
         --1，建立使用不包含任何方法的对象类型
         --(1)建立对象类型
         create or replace type person_type1 as object(name varchar2(10),gender varchar2(2),birthdate date);   
         --(2)使用对象类型
         create table person_typ1 of person_type;
         --(3)使用对象类型的构造函数给表添加数据
         insert into person_typ1 values(person_type('张三','M','12-4月-2010'));
         select * from person_typ1;
         --(4)检索行对象数据
         declare
         person person_type;
         begin
         select value(p) into person from person_typ1 p where p.name='&name';
         dbms_output.put_line('性别：'||person.gender);
         end;
           --删除
         begin
         delete from person_typ1 where name='张三';
         end;
         --(5)建立列对象
         create table employee_tab1(
                eno number(6),person person_type,
                sal number(6,2),job varchar2(10)
         );
         --(6)为列对象插入数值
         insert into employee_tab1(eno,sal,job,person) values('1','8000','MGR',person_type('李四','F','4-5月-2010'));
         --(7)检索对象类型列的数据
         declare
         person person_type;
         salary employee_tab1.sal%type;
         begin
         select person,sal into person,salary from employee_tab1 where eno=&no;
         dbms_output.put_line('雇员名：'||person.name||',工资：'||salary);
         end;
         --2.建立和使用包含member方法的对象类型
         --(1)建立对象类型(也叫做对象类型规范)
         create or replace type person_type2 as object(
                name varchar2(10),gender varchar2(2),
                birthday date,address varchar2(100),
                member procedure change_address(new_addr varchar2),
                member function get_info return varchar2 
         );
         --(2)建立对象类型体
         create or replace type body person_type2 is 
         member procedure change_address(new_addr varchar2)
         is begin
         address:=new_addr;
         end;
         member function get_info return varchar2
         is v_info varchar2(100);
         begin
         v_info:='姓名：'||name||',出生日期：'||birthday;
         return v_info;
         end;
         end;
         create table employee_tab2(
                eno number(6),person person_type2,
                sal number(6,2),job varchar2(10)
         );
         insert into employee_tab2 values(1,person_type2('王五','M','9-8月-2010','杭州'),8000,'SALE');
         insert into employee_tab2 values(2,person_type2('孙六','M','9-8月-2010','杭州'),6000,'MGR');
         declare
         persona person_type2;
         begin
         select person into persona from employee_tab2 where eno=&no;
         persona.change_address('呼和浩特');
         update employee_tab2 set person=persona where eno=&no;
         end;
         --(3)实现对象类型体的继承
         --创建父类
         create or replace type person_type3 as object(
              name varchar2(10),gender varchar2(2),
              birthdate date,address varchar2(20),
              member function get_info return varchar2
         ) not final;
         --定义父类方法
         create or replace type body person_type3 as 
         member function get_info return varchar2
         is begin
         return '姓名：'||name||',家庭住址：'||address;
         end;
         end; 
         --创建子类
         create or replace type employee_type3 under person_type3(
                eno number(6),job varchar2(10),
                sal number(6,2),member function get_other return varchar2
         );
         --创建子类方法
         create or replace type body employee_type3 as 
         member function get_other return varchar2
         is begin
         return '雇员名称：'||name||',工资：'||sal;
         end;
         end;   
         --建立并操作对象表
         create table employee_tab3 of employee_type3;
         insert into employee_tab3 values('张三','男','8-5月-1886','南阳',1,'MGR',5600);
         insert into employee_tab3 values('李四','男','8-5月-1886','郑州',2,'MGR',6000);
         declare
         v_employee employee_type3;
         begin
         select value(a) into v_employee from employee_tab3 a where a.eno=&no;
         dbms_output.put_line(v_employee.get_info);
         dbms_output.put_line(v_employee.get_other);
         end;
    --维护对象类型
         --(1)显示对象类型信息
         select * from user_types;  
         desc person_type3;   
         --(2)增加或删除对象类型属性
         alter type person_type add attribute address varchar2(50) cascade;
         alter type person_type drop attribute address cascade;
         --(3)增加或删除对象类型的方法
         alter type person_type add member function get_info return varchar2 cascade;
         create or replace type body person_type as 
         member function get_info return varchar2
         is begin
          return '姓名：'||name||',性别：'||gender;
         end;
         end;
     --dbms_lob包
         --1.常量
         file_readonly CONSTANT BINARY_INTEGER:=0;
         lob_readonly CONSTANT BINARY_INTEGER:=0;
         lob_readwrite CONSTANT BINARY_INTEGER:=1;
         session CONSTANT PLS_INTEGER:=10;
         call CONSTANT PLS_INTEGER:=12;
         --2.APPEND(用于将两个lob类型的变量累加,适用于clob,blob)
         declare 
         dest_lob clob;
         src_lob clob;
         begin
         src_lob:='中国';
         dest_lob:='你好,';
         dbms_lob.append(dest_lob,src_lob);
         dbms_output.put_line(dest_lob);
         end;
         --3.过程close(用于关闭已经打开的lob,适用于clob/nclob,blob,bfile类型)
         dbms_lob.close
         
         
         
         
         
         
     --学海拾贝
         --查询数据库版本
         select * from v$version;
         --查询数据库所有的角色
         select * from dba_roles;
         --查询数据库所有的用户(包括用户密码，表空间)
         select * from dba_users;
         --查询用户的权限
         select * from dba_role_privs where grantee='HHH';
         --查询用户信息(默认表空间，状态等)
         select * from user_users;
         --查询用户下所有的object(包括表，索引，序列等)
         select * from user_objects;
         --创建表空间(一般表空间名和用户名都用大写的创建)
         CREATE TABLESPACE SJYD DATAFILE 'C:\SJYD_DATA.DBF' SIZE 5M DEFAULT STORAGE (INITIAL 50K NEXT 50K MAXEXTENTS UNLIMITED PCTINCREASE 1);
         --创建用户(设置默认的表空间)
         CREATE USER SJYD IDENTIFIED BY SJYD123 DEFAULT TABLESPACE SJYD TEMPORARY TABLESPACE TEMP PROFILE DEFAULT;
         --为用户设置权限
         GRANT "CONNECT" TO "SJYD";
         GRANT "EXP_FULL_DATABASE" TO "SJYD";
         GRANT "IMP_FULL_DATABASE" TO "SJYD";
         ALTER USER "SJYD" DEFAULT ROLE ALL;
         ALTER USER SJYD ACCOUNT UNLOCK;
         --删除表空间和用户
         DROP TABLESPACE SJYD;
         DROP USER SJYD CASCADE;
         --导出数据
         exp sjyd/sjyd123@oracle file='d:\sjyd.dmp' full=y;
         --导入数据
         imp sjyd/sjyd123@oracle file='d:\sjyd.dmp' fromuser=sjyd touser=hhh;
         imp sjyd/sjyd123@oracle file='d:\sjyd.dmp' full=y;
         --数据库进行闪回
         exec dbms_flashback.enable_at_time(to_timestamp('2005-1-24 23:26:5', 'yyyy-mm-dd hh24:mi:ss'));
         --单表进行闪回
         alter table tablename enable row movement;
         flashback table tablename to timestamp('2010-08-17 15:00:00');
         alter table tablename disable row movement;
         --查询表的创建时间
         select * from user_objects WHERE object_name='TABLE_NAME';
         select * from all_objects where object_name='ITABLE_NAME';
         --查询用户的默认表空间
         select username,default_tablespace from user_users where username='SCOTT';
         
         --对表的操作
         select * from dba_recyclebin a where a.original_name='tablename';--查询该表被操作的时间(删除操作)
         select * from recyclebin;--查看回收站，里面有被删除表的信息和删除时间。
         flashback table tablename to before drop;--将删除的表进行闪回。
         select * from t as of timestamp to_timestamp('2010-06-29 22:57:47', 'yyyy-mm-dd hh24:mi:ss');--查询该时间点的数据
                --建表同时建主外键
         create table t_sun(sno varchar2(5),id number,sname varchar2(10),
         constraint pk_sno primary key(sno),
         constraint fk_id foreign key(id) references t(id));
         --设置数据库的审计功能(audit,对表的修改进行跟踪记录)
         show parameter audit;--查看审计功能是否开启
         alter system set audit_sys_operations=true scope=spfile;--审计管理用户(以sysdba/sysoper角色)
         alter system set audit_trail=db,extended scope=spfile;--开启审计功能，并且将更改反映到实例文件
         startup force;--重启实例。
         show parameter audit;--查看是否开启
         audit all to emp;--设置对某个表对象进行审计。
         select * from dba_audit_trail;--查询审计记录。
         alter system set audit_trail=none;--关闭审计。
         
         --oracle全表扫描的四种类型 http://blog.csdn.net/tianlesoftware/archive/2010/08/31/5852106.aspx
         
         --判断是否是pl/sql进行的用户连接，建立触发器
         create or replace trigger check_client
         after logon on database
         begin
         for c in (select null from v$session where sid=userenv('sid') and program='plsqldev.exe') loop
         raise_application_error(-20001,'Deny PLSQL Developer connection.');
         end loop;
         end;


         --进行数据库备份与恢复(rman)
        
         
         --建立记录类型(用于单行多列)
         declare type emp_record_type is record(
                 name emp.ename%type;
                 sal emp.sal%type
         ); 
     --使用索引表类型(数组，用于多行单列，下标可以从负值开始，无长度限制)
         declare type ename_table_type is table of emp.ename%type index by binary_integer;
         ename_table ename_table_type;
         begin
         select ename into ename_table(1) from emp where empno=&no;
         dbms_output.put_line('雇员名：'||ename_table(1));
         end;--使用binary_integer类型
         
         declare type area_table_type is table of number index by varchar2(10);
         area_table area_table_type;
         begin
         area_table('北京'):=1;
         area_table('上海'):=2;
         area_table('广州'):=3;
         dbms_output.put_line('第一个元素：'||area_table.first);
         dbms_output.put_line('最后一个元素：'||area_table.last);
         end;--使用varchar2类型
     --嵌套表(下标从1开始，元素个数无限制,使用时必须先使用构造方法初始化嵌套表变量)
         declare type ename_table_type is table of emp.ename%type;
         ename_table ename_table_type;
         begin
         ename_table:=ename_table_type('mary','mary');
         select ename into ename_table(2) from emp where empno=&no;
         dbms_output.put_line('雇员名：'||ename_table(2));
         end;     
         --变长数组varray(下标从1开始，有限长度，必须要初始化)
         declare type ename_table_type is varray(20) of emp.ename%type;
         ename_table ename_table_type:=ename_table_type('mark');
         begin
         select ename into ename_table(1) from emp where empno=&no;
         dbms_output.put_line('雇员名：'||ename_table(1));
         end;
         --记录表(处理多行多列的数据)
         declare type emp_table_type is table of emp%rowtype index by binary_integer;
         emp_table emp_table_type;
         begin
         select * into emp_table(1) from emp where empno=&no;
         dbms_output.put_line('雇员名'||emp_table(1).ename);
         dbms_output.put_line('工资'||emp_table(1).sal);
         end;
         --使用多级嵌套表
         declare type al_table_type is table of int;
         type nal_table_type is table of al_table_type;
         nal_table nal_table_type:=nal_table_type(al_table_type(2,34),al_table_type(2,3));
         begin
         dbms_output.put_line('显示二维数组所有元素');
         for i in 1..nal_table.count loop
             for j in 1..nal_table(i).count loop
                 dbms_output.put_line('nal_table('||i||','||j||')='||nal_table(i)(j));
             end loop;
         end loop;
         end;