PL/SQL详例和解释(五)
es;
type t_name_var is varray(25) of varchar2(30);
name_var t_name_var:=t_name_var();
counter number:=0;
begin
for r_name in c_name loop
counter:=counter+1;
name_var.extend;
name_var(counter):=r_name.name;
end loop;
for n in 1..counter loop
name_var.extend(1,n);--扩展一个，并添加第n个元素
end loop;
for n in 1..name_var.count loop
dbms_output.put_line('Name ('||n||') is '||name_var(n));
end loop;
end;
--多层数组实现二元次数组输出
declare
type t_var1_tab is varray(10) of number(5);
type t_var2_tab is varray(10) of t_var1_tab;
var1_tab t_var1_tab:=t_var1_tab(2,3,7,9);
var2_tab t_var2_tab:=t_var2_tab(var1_tab);
begin
var2_tab.extend;
var2_tab(2):=t_var1_tab(7,8,3,6);
for n in 1..2 loop
for m in 1..4 loop
dbms_output.put_line('varrary ('||n||')('||m||') is '||var2_tab(n)(m));
end loop;
end loop;
end;
--嵌套记录类型实现个人信息输出
declare
type name_type is record (first_name it_employees.first_name%type, last_name it_employees.last_name%type);
type info_p_type is record (name name_type, email it_employees.e_mail%type, phonenum number(20));
info_person info_p_type;
begin
select first_name, last_name, e_mail, phone_number into info_person.name.first_name, info_person.name.last_name, info_person.email, info_person.phonenum from it_employees where employee_id=&employee_id;
dbms_output.put_line('Name: '||info_person.name.first_name||'.'||info_person.name.last_name);
dbms_output.put_line('Email: '||info_person.email);
dbms_output.put_line('Name: '||info_person.phonenum);
exception
when no_data_found then dbms_output.put_line('Please input vaild employee id!');
end;
--动
态SQL execute immediate
declare
sql_stmt varchar2(300);
total_emp_num number;
v_emp_id it_employees.employee_id%type:=&employee_id;
v_salary number(10);
begin
sql_stmt:='select count(*) from it_employees';
execute IMMEDIATE sql_stmt into total_emp_num;
DBMS_OUTPUT.PUT_LINE('Total employee number is: '||total_emp_num);
sql_stmt:='declare v_date varchar2(30); begin v_date:=to_char(sysdate, ''DD-MON-YYYY''); dbms_output.put_line(''v_date is: ''||v_date); end;';--字符串中的字符串用''***''
execute IMMEDIATE sql_stmt;
sql_stmt:='select salary from it_employees where employee_id=:10001';
EXECUTE IMMEDIATE sql_stmt into v_salary using v_emp_id;
DBMS_OUTPUT.PUT_LINE('Salary is '||v_salary);
end;
--动态SQL OPEN-FOR根据部门号输出员工名字
declare
first_name varchar2(10);
last_name varchar2(10);
type emp_cur_type is ref cursor;
emp_cur emp_cur_type;--定义一个游标变量
dep_id number:=&department_id;
begin
open emp_cur for 'select first_name, last_name from it_employees where department_id=:101' using dep_id;
loop
fetch emp_cur into first_name, last_name;
exit when emp_cur%notfound;
DBMS_OUTPUT.PUT_LINE('Name is:'||first_name||'.'||last_name);
end loop;
close emp_cur;
exception
when no_data_found then dbms_output.put_line('INVALID DEPARTMENT ID!');
when others then
if emp_cur%isopen then close emp_cur;
end if;
end;
--For 和 Forall 效率对比（批量效果更佳）把批量数据插入到索引表中
create table cc_emp (emp_id number(10),emp_name varchar2(30));
select * from cc_emp;
truncate table cc_emp;
declare
type emp_id_type is table of cc_emp.emp_id%type index by pls_integer;
type emp_name_type is table of cc_emp.e