plsql_3

获取环境与会话

select SYS_CONTEXT('USERENV','SESSION_USER'),SYS-CONTEXT('USERENV',IP_ADDRESS) INTO USERNAME,IP_ADDRESS
FROM DUAL


更新查询返回的结果行

update employess SET ROW = emp_row

where ....


加 一个returning 返回结果


delcare

first employees.first_name%type;
last employees.last_name %type;

new_salary employees.salary%type;

begin
update employees

set salary = salary+(salary*0.3)
where employee_id =100;
returning fisrt_name,last_name,salary into fist,last,new_sarlary;

dbms_ouput_put.line


更新游标返回的行结果

使用where current of 更新循环当前数据


delcar


cursor emp_sal+cur is
select * from employess
where department_id =60
for update;
emp_sal_rec emp_sal_cur%rowtype;

begin

for emp_sal_rec in emp_sal_cur loop

dbms_output_put.line

update employees

set salary = salary+(salary*.025)
where current of emp_sal_cur;
end loop;

for emp_sal_rec in emp_sal_cur loop

dbms

end loop;
end;


删除 返回的结果行

create or replace procedure remove_job_history(dept_id in number) as
cursor job history_cur_is

select * from job_hisory ......for update

rec job_histroy_cur_is

begin

for rec in job_....... loop
delete from ....
where current of job_cur...



删除重复数据


delete from employees a where rowind>(

select min(rowid) from employees b where a.employee_id = b.employees_id);

)