PL/SQL详例和解释(八)
e, i_id in number,i_f_name in varchar2, i_l_name in varchar2, i_salary in number)
return self as result,
member procedure get_emp_info(out_id out number, out_name out varchar, out_salary out number),
static procedure get_cur_date,
member function show_emp_salary(i_id in number)return number
);
create or replace type body employ_info_type as
constructor function employ_info_type(self in out nocopy employ_info_type, i_id in number)
return self as result
is
begin
self.emp_id:=i_id;
select first_name,last_name, salary into self.emp_first_name, self.emp_last_name, self.emp_salary from it_employees where employee_id=self.emp_id;
return;
exception
when no_data_found then dbms_output.put_line('No related records!');
end;
constructor function employ_info_type(self in out nocopy employ_info_type, i_id in number, i_f_name in varchar2, i_l_name in varchar2, i_salary in number)
return self as result
is
begin
self.emp_id:=i_id;
self.emp_first_name:=i_f_name;
self.emp_last_name:=i_l_name;
self.emp_salary:=i_salary;
return;
end;
member procedure get_emp_info(out_id out number, out_name out varchar, out_salary out number)
is
begin
out_id:=self.emp_id;
out_name:=self.emp_first_name||'.'||self.emp_last_name;
out_salary:=self.emp_salary;
DBMS_OUTPUT.PUT_LINE('Employee ID: '||out_id);
DBMS_OUTPUT.PUT_LINE('Employee Name: '||out_name);
DBMS_OUTPUT.PUT_LINE('Employee Salary: '||out_salary);
end;
static procedure get_cur_date
is
cur_date varchar2(30);
begin
cur_date:=to_char(sysdate,'HH24:MI');
DBMS_OUTPUT.PUT_LINE('Curre
nt Time is: '||cur_date);
end;
member function show_emp_salary(i_id in number)
return number
is
v_salary number(10);
begin
select salary into v_salary from it_employees where employee_id=i_id;
return v_salary;
end;
end;
declare--执行调用
employ_info employ_info_type;
id number(10);
name varchar2(30);
salary number(10);
begin
employ_info:=employ_info_type(10003);
employ_info.get_emp_info(id,name,salary);
employ_info_type.get_cur_date;
salary:=employ_info.show_emp_salary(id);
DBMS_OUTPUT.PUT_LINE('Salary: '||salary);
end;
--运用UTL_FILE来写log
CREATE OR REPLACE DIRECTORY D_OUTPUT AS 'C:\Users\ziwzhang\Desktop\temp\';---这三句话必须由dba来执行
GRANT READ, WRITE ON DIRECTORY D_OUTPUT TO system;
GRANT EXECUTE ON utl_file TO system;
create or replace procedure emp_number(log_directory in varchar2, log_name in varchar2)
as
file_handler UTL_FILE.FILE_TYPE;
emp_counter number;
begin
select count(*) into emp_counter from it_employees;
file_handler:=UTL_FILE.FOPEN(log_directory,log_name,'A');
UTL_FILE.NEW_LINE(file_handler);
UTL_FILE.PUT_LINE(file_handler,'----------USER LOG-----------');
UTL_FILE.NEW_LINE(file_handler);
UTL_FILE.PUT_LINE(file_handler,'on '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
UTL_FILE.NEW_LINE(file_handler);
UTL_FILE.PUT_LINE(file_handler,'The number of employee is: '||emp_counter);
UTL_FILE.NEW_LINE(file_handler);
UTL_FILE.PUT_LINE(file_handler,'-----------END LOG-----------');
UTL_FILE.NEW_LINE(file_handler);
UTL_FILE.FCLOSE(file_handler);
exception
when UTL_FILE.INVALID_FILENAME THEN
DBMS_OUTPUT.PUT_LINE('FILE IS INVALID!')