PL/SQL详例和解释(七)
d=emp_id;
return v_salary;
exception
when no_data_found then
dbms_output.put_line('Invaild employee id!');
end salary_fuc;
declare
emp_id it_employees.employee_id%type;
begin
emp_id:=&employee_id;
dbms_output.put_line('the salary of employee '||emp_id||' is: '||salary_fuc(emp_id));
end;
--创建包
create or replace package emp_pac
is
v_current_date varchar2(30);
procedure name_procedure (loc_id in number, v_first_name out varchar2, v_last_name out varchar2);
function salary_fuc (emp_id in number) return number;
end emp_pac;
create or replace package body emp_pac
is
procedure name_procedure (loc_id in number, v_first_name out varchar2, v_last_name out varchar2)
as
cursor name_cur is select e.first_name, e.last_name from it_employees e, departments d where e.department_id=d.department_id and d.location_id=loc_id;
begin
for i in name_cur loop
v_first_name:=i.first_name;
v_last_name:=i.last_name;
DBMS_OUTPUT.PUT_LINE('Name is: '||v_first_name||'.'||v_last_name);
end loop;
exception
when others then
dbms_output.put_line('Error!');
end name_procedure;
function salary_fuc (emp_id in number) return number
is
v_salary number(10);
begin
select salary into v_salary from it_employees where employee_id=emp_id;
return v_salary;
exception
when no_data_found then
dbms_output.put_line('Invaild employee id!');
end salary_fuc;
begin--包中变量
v_current_date:=to_char(sysdate,'DD-MON-YYYY HH24:MI');
end emp_pac;
declare
loc_id departments.location_id%type;
v_first_name it_employees.first_name%type;
v_last_name it_employees.last_name%type;
begin
loc_id:=&Location_id;
emp_pac.name_procedure(loc_id,v_first_name,v_last_name);
DBMS_OUTPUT.PUT_LINE('Current Date is: '||emp_pac.v_current_date);
end;
--利用user_objects查看数据字典
select object_type, object_name, status from user_objects where object_type in ('FUNCTION','PROCEDURE','PACKAGE','OBJECT_BODY') order by object_type;
--创建一个对象类型的集合展示员工信息
create or replace type emp_info_type as object (emp_id number(10),emp_name varchar2(30),emp_email varchar2(30),emp_salary number(10),emp_dep_id number(10));
declare
type emp_tab_type is table of emp_info_type index by binary_integer;
emp_tab emp_tab_type;
begin
select emp_info_type(employee_id, first_name||'.'||last_name, e_mail, salary, department_id) bulk collect into emp_tab from it_employees where salary>
=15000;
for i in 1..emp_tab.count
loop
dbms_output.put_line('Employee ID is: '||emp_tab(i).emp_id);
dbms_output.put_line('Employee Name is: '||emp_tab(i).emp_name);
dbms_output.put_line('Employee Email is: '||emp_tab(i).emp_email);
dbms_output.put_line('Employee Salary is: '||emp_tab(i).emp_salary);
dbms_output.put_line('Employee Department ID: '||emp_tab(i).emp_dep_id);
dbms_output.put_line('--------------------------------------');
end loop;
end;
--对象类型的方法
create or replace type employ_info_type as object
(
emp_id number(10),
emp_first_name varchar2(20),
emp_last_name varchar2(20),
emp_salary number(10),
constructor function employ_info_type(self in out nocopy employ_info_type, i_id in number)
return self as result,
constructor function employ_info_type(self in out nocopy employ_info_typ