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;