PLSQL加强版

--匿名块
declare 
 n varchar (20 char);
begin 
 select user_name into n from P_user where user_Id=1;
 dbms_output.put_line(n);
end;
----------------------------------------------------------------------------

--子程序
create or replace procedure p12
is
 n varchar (20 char);
begin 
 select user_name into n from P_user where user_Id=1;
 dbms_output.put_line(n);
end;
-----------------------------------------------------------------------------
--标量：标准的变量
declare 
 n varchar (20 char);
 a number;
begin 
 select user_name into n from P_user where user_Id=1;
 dbms_output.put_line(n);
 dbms_output.put_line(a);
end;
----------------------------------------------------------------------------
--单行多列 选表所有字段
declare
users p_user%rowtype;
begin
select*into users from p_user where user_id=1;
dbms_output.put_line(users.user_id);
dbms_output.put_line(users.user_name);
dbms_output.put_line(users.age);
end;
-----------------------------------------------------------------------------
--自定义PL/SQL纪录
declare
type user_type is record(id number,name p_user.user_name%type,age number);
users user_type;
begin
select*into users from p_user where user_id=2;
dbms_output.put_line(users.id);
dbms_output.put_line(users.name);
dbms_output.put_line(users.age);
end;
-------------------------------------------------------------------------------------
--在insert语句中使用PL/SQL纪录
declare
users p_user%rowtype;
begin
users.user_id:=9;
users.user_name:='demo';
users.age:=9;
insert into p_user values users;
commit;
end;
-----------------------------------------------------------------------
--在update数据中使用PL/SQL纪录
declare
users p_user%rowtype;
begin
users.user_id:=9;
users.user_name:='good boy';
users.age:=20;
update p_user set row =users where user_id=9;
commit;
end;
--------------------------------------------------------------------
--在PL/SQL中使用varray
declare
type names_type is varray(5) of varchar2(20);
names names_type;
begin
names:=names_type(null,null,null,'james');
select user_name into names(1)from p_user where user_id=1;
names(3):='just';
for i in 1..names.count loop
dbms_output.put_line(names(i));
end loop;
end;
--------------------------------------------------------------------
--在表列中使用varray
create or replace type names_type2 is varray(3)of varchar2(10);

create table p_room2(id number,names names_type2);
---------------------------------------------------------------------
--PL/SQL记录表
declare
type user_table_type is table of p_user%rowtype
index by pls_integer;
user_tables user_table_type;
begin
select*into user_tables(1)from p_user where user_id=1;
select*into user_tables(2)from p_user where user_id=2;
user_tables(3).user_id:=2;
user_tables(3).user_name:='power';
user_tables(3).age:=3;
for i in 1..user_tables.count loop
dbms_output.put_line(user_tables(i).user_id);
dbms_output.put_line(user_tables(i).user_name);
dbms_output.put_line(user_tables(i).age);
end loop;
end;
------------------------------------------------------------------------
--exists方法
declare
type id_name_type is table of varchar2(10);
names id_name_type:=id_name_type('123','bcd','fff');
num number;
begin
num:=1;--下标
if names.exists(num)then
dbms_output.put_line('names:'||names(num));
else
dbms_output.put_line('元素未初始化');
end if;
end;

------------------------------------------------------------------------
--limit方法
declare
type names_array_type is varray(20)of number(6);
names_array names_array_type;
begin
names_array:=names_array_type(2,3,4,5,6);
dbms_output.put_line(names_array.limit);
end;

------------------------------------------------------------------------
--count
declare
type names_array_type is table of varchar2(20);
names_array names_array_type;
begin
names_array:=names_array_type('ff',null,'uu','hh');
dbms_output.put_line(names_array.count);
end;

---------------------------------------------------------------------
--first last
declare
type names_type is table of varchar2(20) index by pls_integer;
names names_type;
begin
names(-7):='james';
names(-9):='works';
names(100):='sdfds';
dbms_output.put_line('第一个元素：'||names(names.first));
dbms_output.put_line('最后一个元素下标：'||names.last);

end;
-------------------------------------------------------------------------
--prior& next
declare
type names_type is table of varchar2(20) index by pls_integer;
names names_type;
begin
names(-7):='james';
names(-9):='works';
names(100):='sdfds';
names(50):='tables';
dbms_output.put_line('第一个元素的前一个下标：'||names.first||'值:'||names(names.first));
dbms_output.put_line('当前元素的后一个下标：'||names.next(names.first)||'值:'||names(names.next(names.first)));
dbms_output.put_line('下标为50的元素的前一个元素的下标：'||names.prior(50));
end;

---------------------------------------------------------------------------
--extend
declare
type names_type is table of varchar2(20);
names names_type:=names_type('aaaa','bbbbb','cccc');
begin
dbms_output.put_line(names.count);
names.extend(4);
dbms_output.put_line(names.count);
names.extend(3,2);

end;

-----------------------------------------------------------------------------
--trim

declare
type names_type is table of varchar2(20);
names names_type:=names_type('aa','bb','cc','dd','ee');
begin
dbms_output.put_line(names.count);
dbms_output.put_line(names(names.last));
names.trim;
dbms_output.put_line(names.count);
dbms_output.put_line(names(names.last));
names.trim(3);
dbms_output.put_line(names(names.last));
end;

-----------------------------------------------------------------------------
--delete

declare
type names_type is table of varchar2(20);
names names_type:=names_type('aa','bb','cc','dd','ee','ff','gg');
begin
dbms_output.put_line(names.count);
dbms_output.put_line(names(3));
names.delete(3);
dbms_output.put_line(names.count);
--dbms_output.put_line(names(3));
dbms_output.put_line(names.next(2));
names.delete(4,6);
dbms_output.put_line(names.count);
end;
------------------------------------------------------------------
--在select into 语句中使用bulk collect字句

declare
type users_table_type is table of p_user%rowtype index by binary_integer;
user_array users_table_type;
begin
select*bulk collect into user_array from p_user;
for i in 1..user_array.count loop
dbms_output.put_line('id:'||user_array(i).user_id);
dbms_output.put_line('name:'||user_array(i).user_name);

dbms_output.put_line('age:'||user_array(i).age);

dbms_output.put_line('___________________');
end loop;
end;

-------------------------------------------------------------------
--批量插
DECLARE
TYPE id_table_type IS TABLE OF NUMBER(6)
INDEX BY BINARY_INTEGER;
TYPE name_table_type IS TABLE OF VARCHAR2(12)
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..id_table.count loop
  INSERT INTO DEMO(ID,NAME) 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 names_table_type is table of varchar2(20) index by binary_integer;
names_table names_table_type;
begin
for i in 1..1000 loop
names_table(i):=i;
end loop;
forall i in 1..names_table.count
update demo set name=names_table(i) where id=names_table(i);
END;

---------------------------------------------------------------------
--批量删除

DECLARE
type names_table_type is table of varchar2(20) index by binary_integer;
names_table names_table_type;
begin
for i in 1..200 loop
names_table(i):=i;
end loop;
forall i in 1..names_table.count
delete from demo where id:=(names_table(i));
END;