--(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;
评论
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
查看更多评论
添加红包
