plsql小记

1.
natural join
2.
using
3.
left[outer]join 左连接用于返回满足连接条件的数据，以及不满足连接的左边表的其他数据。
4.
right[outer]join 右连接用于返回满足连接条件的数据，以及不满足连接的右边表的其他数据。
5.
full[outer]join 完全连接用于返回连接条件的数据，以及不满足连接条件的左边表和右边表。

6.
create or replace trigger tr_sec_emp
 before insert or update or delete on emp
begin
     if to_char(sysdate,'DY','nls_date_language=AMERICAN')
        in ('SAT','SUN') then
        raise_application_error(-20001,'不能在休息日改变雇员信息');
     end if;
end tr_sec_emp;

7.
create or replace trigger tr_sec_emp
 before insert or update or delete on emp
begin
     if to_char(sysdate,'DY','nls_date_language=AMERICAN')
        in ('SAT','SUN') then
        case
            when inserting then
                 raise_application_error(-20001,'不能在休息日执行INSERT');
            when updating then
                 raise_application_error(-20001,'不能在休息日执行update');
            when deleting then
                 raise_application_error(-20001,'不能在休息日执行detele');
         end case;
     end if;
end tr_sec_emp;

8.plsql记录
为了处理单行单列的数据，可以使用标量变量；为了处理单行多列的数据，可以使用plsql记录；
为了处理单列多行数据，应该使用pl/sql集合。plsql集合包括plsql表，嵌套表和varray三种类型。
--自定义plsql记录

declare
       type emp_record_type is record(
       name emp.ename%type,
       salary emp.sal%type,
       dno emp.deptno%type
     );

 emp_record emp_record_type;
 ...

--使用%rowtype属性定义记录变量 
dept_record dept%rowtype;
emp_record emp%rowtype;


--select into 语句中使用pl/sql记录
declare
type emp_record_type is record(
name emp.ename%type,
salary emp.sal%type,
title emp.job%type);
emp_record emp_record_type;
begin
select ename,sal,job into emp_record from emp where empno=&no;
dbms_output.put_line(emp_record.name);
dbms_output.put_line(emp_record.salary);
dbms_output.put_line(emp_record.title);
end;

--在insert values子句中使用记录变量
declare
dept_record dept%rowtype;
begin
dept_record.deptno :=50;
dept_record.dname :='administrator';
dept_record.loc :='beijing';
insert into dept values dept_record;
end;


--在insert values子句中使用记录成员
declare
       dept_record dept%rowtype;
begin
 dept_record.deptno :=60;
 dept_record.dname :='sales';
 insert into dept(deptno,dname) values
 (dept_record.deptno,dept_record.dname);
 end;

 --在update set子句中使用记录变量

declare
  dept_record dept%rowtype;
begin
  dept_record.deptno := 30;
  dept_record.dname  := 'sales';
  dept_record.loc    := 'shanghai';
  update dept set row = dept_record where deptno = 30;
end;

--在update set子句中使用记录成员

declare 
dept_record dept%rowtype;
begin
dept_record.loc :='guangzhou';
update dept set loc = dept_record.loc where deptno =10;
commit;
end;

9.plsql集合


--使用binary_integer或者pls_integer定义plsql表下标
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;
/


--使用varchar2定义plsql表下标
declare
type area_table_type is table of number index by varchar2(10);
area_table area_table_type;
begin
area_table('Japan') :=1;
area_table('china') :=2;
area_table('america') :=3;
area_table('England') :=4;
area_table('portugal') :=4;
dbms_output.put_line('第一个元素:'||area_table.first);
dbms_output.put_line('最后一个元素:'||area_table.last);
end;


--在plsql块中使用嵌套表
declare
type ename_table_type is table of emp.ename%type;
ename_table ename_table_type;
begin
ename_table := ename_table_type('mary','mary','mary');
dbms_output.put_line('雇员名:'||ename_table(2));
select ename into ename_table(2) from emp where empno =&no;
dbms_output.put_line('雇员名:'||ename_table(2));
end;
/


--在表列中使用嵌套表
create or replace type phone_type is table of varchar2(20);
/

create table person(
id number(4),name varchar2(10),sal number(6,2),phone phone_type)nested table phone store as phone_table;


--操纵嵌套表列 为嵌套表列插入数据。
begin
insert into person values(1,'scott',800,phone_type('0471-3456788','13804111111'));
commit;
end;


--操纵嵌套表，检索嵌套表列的数据
declare
phone_table phone_type;
begin
select phone into phone_table from person where name ='scott';
for i in 1..phone_table.count loop
dbms_output.put_line('号码:'||i||':'||phone_table(i));
end loop;
end;
/

--更新嵌套表列数据
declare
phone_table phone_type := phone_type('0471-3456788','1255555','985456','4414654');
begin
update person set phone = phone_table where id =1;
commit;
end;

--在plsql块中使用varray
declare
type job_array_type is varray(20) of emp.job%type;
job_array job_array_type :=job_array_type('clerk','clerk');
begin
select job into job_array(1) from emp where lower(ename) = lower('&ename');
dbms_output.put_line('岗位:'||job_array(1));
end;
/

--在表列中使用varray
create type phone_array is varray(20) of varchar2(20);
/
create table worker(
id number(4),name varchar2(10),sal number(6,2),phone phone_array);


--plsql记录表
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);
  dbms_output.put_line('岗位:'||emp_table(1).job);
   dbms_output.put_line('上岗日期:'||emp_table(1).hiredate);
 end;


--exists

declare
 type id_table_type is table of number(6);
 id_table id_table_type := id_table_type(1,2,3);
 no int;
 begin
 no:=&i;
 if id_table.exists(no) then
 dbms_output.put_line('元素值:'||id_table(no));
 else
 dbms_output.put_line('元素未初始化');
 end if;
 end;


 --count
declare
  type id_array_type is varray(20) of number(6);
  id_array id_array_type := id_array_type(1, null, 2, 5);
begin
  dbms_output.put_line('id_array元素的总数:' || id_array.count);
end;


--limit
declare
  type id_array_type is varray(20) of number(6);
  id_array id_array_type := id_array_type(1, null, 2, 5);
begin
  dbms_output.put_line('id_array元素的总数:' || id_array.count);
  dbms_output.put_line('id_array元素的最大个数:' || id_array.limit);
end;


10.
批量帮定(bulk bind) 用于在执行sql操作时传递所有pl/sql集合元素的数据，通过在select和dml语句上使用批量绑定，可以极大地加快批量数据的出来那个


--使用传统循环插入
declare
  type id_table_type is table of number(6) index by binary_integer;
  type name_table_type is table of varchar2(10) index by binary_integer;
  id_table   id_table_type;
  name_table name_table_type;
  start_time number(10);
  end_time   number(10);
begin
  for i in 1 .. 100000 loop
    id_table(i) := i;
    name_table(i) := 'name' || to_char(i);
  end loop;
  start_time := dbms_utility.get_time;
  for i in 1 .. 100000 loop
    insert into demo values (id_table(i), name_table(i));
  end loop;
  end_time := dbms_utility.get_time;
  dbms_output.put_line('总计时间(秒):' ||
                       to_char((end_time - start_time) / 100));
end;

--使用批量绑定
declare
  type id_table_type is table of number(6) index by binary_integer;
  type name_table_type is table of varchar2(10) index by binary_integer;
  id_table   id_table_type;
  name_table name_table_type;
  start_time number(10);
  end_time   number(10);
begin
  for i in 1 .. 100000 loop
    id_table(i) := i;
    name_table(i) := 'name' || to_char(i);
  end loop;
  start_time := dbms_utility.get_time;
  forall i in 1 .. id_table.count
    insert into demo values (id_table(i), name_table(i));
  end_time := dbms_utility.get_time;
  dbms_output.put_line('总计时间(秒)' ||
                       to_char((end_time - start_time) / 100));
end;


bulk collect 子句用于将批量数据存放到plsql集合，该子句只适用于select into 语句，fetch into 语句和dml语句．


--在select into 语句中使用bulk collect 子句
declare
  type emp_table_type is table of emp%rowtype index by binary_integer;
  emp_table emp_table_type;
begin
  select * bulk collect into emp_table from emp where deptno = &no;
  for i in 1 .. emp_table.count loop
    dbms_output.put_line('姓名:' || emp_table(i)
                         .ename || ',岗位:' || emp_table(i)
                         .job || ',工资' || emp_table(i).sal);
  end loop;
end;


--dml返回子句中使用bulk collect 子句
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;
begin
  update emp set sal = sal * 1.1 where deptno = &no returning ename, sal bulk collect into ename_table,sal_table;

  for i in 1..ename_table.count loop 
  dbms_output.put_line('姓名:' ||ename_table(i)||',新工资:'||sal_table(i));
end loop;
end;

--使用forall语句 插入批量数据
declare
  type id_table_type is table of demo.id%type index by binary_integer;
  type name_table_type is table of demo.name%type index by binary_integer;
  id_table   id_table_type;
  name_table name_table_type;
  start_time number(10);
  end_time   number(10);
begin
  for i in 1 .. 20000000 loop
    id_table(i) := i;
    name_table(i) := 'Name' || to_char(i);
  end loop;
  start_time := dbms_utility.get_time;

  forall i in 1 .. id_table.count
    insert into demo values (id_table(i), name_table(i));
  end_time := dbms_utility.get_time;
  dbms_output.put_line('总计时间(秒):' ||
                       to_char((end_time - start_time) / 100));
end;

11.
delete:不会释放表所占用的空间
truncate:会释放表所占用的空间
truncate table emp