--创建表retired_emps

drop table retired_emps;

create table retired_emps (

empno number(4),

ename varchar2(10),

job varchar2(9),

mgr number(4),

hiredate date,

leavedate date,

sal number(7,2),

comm number(7,2),

deptno number(2)

);

--使用记录插入表

declare

v_employee_number number := 124;

--以原对象为记录

v_emp_rec employees%rowtype;

begin

select * into v_emp_rec from employees

where employee_id = v_employee_number;

--insert比较复杂

insert into retired_emps(empno,ename,job,mgr,hiredate,leavedate,sal,comm,deptno)

values (v_emp_rec.employee_id,v_emp_rec.last_name,

v_emp_rec.job_id,v_emp_rec.manager_id,

v_emp_rec.hire_date,sysdate,

v_emp_rec.salary,v_emp_rec.commission_pct,

v_emp_rec.department_id);

commit;

end;

--改进insert语句

declare

v_employee_number number := 124;

--以目标对象，定义记录

v_emp_rec retired_emps%rowtye;

begin

--增加select复杂度

select employee_id,last_name,job_id,manager_id,

hire_date,hire_date,salary,commission_pct,

department_id

into v_emp_rec from employees

where employee_id = v_employee_number;

--减少insert复杂度

insert into retired_emps values v_emp_rec;

commit;

end;

--三种创建记录的方式

create table cust_sales_roundup (

customer_id number(5),

customer_name varchar2(200),

total_sales number(15,2)

);

declare

--直接用对象加%rowtype

cust_sales_roundup_rec cust_sales_roundup%rowtype;

--游标记录

cursor cust_sales_crc is select * from cust_sales_roundup;

--自定义记录

type customer_sales_rectype is record

( customer_id number(5),

customer_name customer.name%type,

total_sales number(15,2)

);

preferred_cust_rec customer_sales_rectype;

begin

--可以复制

cust_sales_roundup_rec := cust_sales_rec;

perferred_cust_rec := cust_sales_rec;

end;

---------关联数组的使用

declare

--标志就是index by pls_integer

type list_of_names_t is table of scott.emp.ename%type index by pls_integer;

happyfamily list_of_names_t;

l_row PLS_INTEGER;

begin

happyfamily(202011200) := 'Eli';

happyfamily(-512839) := 'Steven';

happyfamily(-909912) := 'Chris';

happyfamily(88) := 'Veva';

l_row := happyfamily.first;

--无论怎么存放，他会按顺序输出

while (l_row is not null)

loop

dbms_output.put_line(l_row || '-->' ||happyfamily(l_row));

l_row := happyfamily.next(l_row);

end loop;

l_row := 88;

--可以是用很多数据结构函数

if happyfamily.exists(l_row) then

dbms_output.put_line('It is here!-->' || happyfamily(l_row));

else

dbms_output.put_line('It is not here!--> '|| happyfamily(l_row));

end if;

end;

-------------

declare

type emp_table_type is table of scott.emp%rowtype index by pls_integer;

my_emp_table emp_table_type;

max_count number(4) := 7934;

begin

--节点为记录的关联数组

for i in 7934 .. max_count loop

select * into my_emp_table(i) from scott.emp where scott.emp.empno = i;

end loop;

--输出first、last

for i in my_emp_table.first .. my_emp_table.last loop

dbms_output.put_line(my_emp_table(i).ename);

end loop;

end;

---------------------------------------------------关联数组使用string下标

declare

--创建下标类型varchar2(64)
subtype location_t is varchar2(64);

--申明关联数组
type population_type is table of number index by location_t;
--关联数组实例
l_country_population population_type;
l_count pls_integer;
l_location location_t;
begin

--赋值
l_country_population('Greenland') := 100000;
l_country_population('USA') := 300000000;
l_country_population('Iceland') := 750000;
l_country_population('Austrlia') := 230000000;
l_country_population('usa') := 40000000;
--显示数量
l_count := l_country_population.count;
dbms_output.put_line('count = ' || l_count);
--显示第一个元素，注意排列顺序
l_location := l_country_population.first;
dbms_output.put_line('first row = ' || l_location);
dbms_output.put_line('first value = ' || l_country_population(l_location));
--显示最后一个元素
l_location := l_country_population.last;
dbms_output.put_line('last row = ' || l_location);
dbms_output.put_line('last value = ' || l_country_population(l_location));

end;

--显示执行时间

set timing on

--打开output输出内容
set serveroutput on

--打开当前时间
set time on

--设置页面大小
set pagesize 100

--设置每行大小
set linesize 100

--

declare

--已不到位的定义关联数组
type population_type is table of number index by varchar2(64);
--创建实例
city_population population_type;
i varchar2(64);
begin

--初始化
city_population('Smallville') := 2000;
city_population('Midland') := 750000;
city_population('Megalopolis') := 1000000;
--更改值
city_population('Smallville') := 2001;
--获取第一个值
i := city_population.first;
--遍历关联数组
while i is not null
    loop
      dbms_output.put_line('Population of ' || i || ' is ' || city_population(i));
      i := city_population.next(i);
    end loop;
end;

---数字下标的关联数组
declare
subtype name_t is varchar(32);
type lookup_t is table of name_t index by pls_integer;

name_tab      lookup_t;
name          name_t := 'Frank';
l_index       pls_integer;
l_found       boolean := false;
begin

--初始化关联数组
name_tab(0) := 'Wilson';
name_tab(1) := 'Richard';
name_tab(2) := 'Michael';
name_tab(3) := 'Frank';
name_tab(4) := 'Chris';
name_tab(5) := 'Walnut';
name_tab(6) := 'Andy';
name_tab(7) := 'Patrick';

l_index := name_tab.first;

--遍历关联数组，查找结果
loop
    if l_index is null then
      exit;
    end if;
    if name = name_tab(l_index) then
      l_found := true;
      exit;
    end if;
    l_index := name_tab.next(l_index);
end loop;
--显示查找结果
if l_found then
    dbms_output.put_line('I found it : ' || l_index || '-->' || name);
else
    dbms_output.put_line('I cannot find it . sorry !');
end if;

end;

-----申明一个下标是string的关联数组，查找对象
declare
subtype name_t is varchar(32);
subtype value_t is varchar(32);
type lookup_t is table of value_t index by name_t;

name_tab      lookup_t;
name          name_t := 'Frank';
begin
name_tab('Wilson') := 'su';
name_tab('Richard') := 'zhang';
name_tab('Michael') := 'liu';
name_tab('Frank') := 'zhou';
name_tab('Chris') := 'peng';
name_tab('Walnut') := 'gao';
name_tab('Andy') := 'miao';
name_tab('Patrick') := 'song';

--直接使用exists函数查找
if name_tab.exists(name) then
    dbms_output.put_line('I found it : ' || name || '-->' || name_tab(name));
else
    dbms_output.put_line('I cannot find it . sorry !');
end if;

end;

ITPub博客

PL/SQL使用记录

最新文章