Oracle代码大全.从入门到熟练

--|************************************************

create table tb_shopType
(
ID number(10) primary key,
typeName varchar2(10) not null
);


insert into tb_shopType values(1,'手机');
insert into tb_shopType values(2,'电脑');
insert into tb_shopType values(3,'MP3');


create table tb_shop
(
ID number(10) primary key,
shopId varchar2(20) unique not null,
shopName varchar2(20) not null,
price number(6,2) not null,
shopTypeId number(10) not null,
manufacturingDate date not null,
constraint ck_price check(price>0),
constraint fk_shopTypeId foreign key(shopTypeId)
references tb_shopType(ID)
);


insert into tb_shop values(1,'M0001','诺基亚 E71',1910.23,1,'04-4月-10');
insert into tb_shop values(2,'M0002','诺基亚 N89',2230.50,'01-4月-09');
insert into tb_shop values(3,'C0001','联想 Y460A-ITH',5549.50,2,'21-4月-10');
insert into tb_shop values(4,'C0002','华硕 F83E667Vf-SL',4999.00,'01-4月-09');
insert into tb_shop values(5,'MP0001','蓝魔T13FHD',599.00,3,'11-4月-09');
insert into tb_shop values(6,'MP0002','苹果iPod nano',1040.00,'01-4月-09');




alert table tb_shop add memo varchar2(200);


alert table tb_shop modify memo varchar2(50);


alert table tb_shop drop column memo;


truncate table tb_shop;


drop table tb_shop;


update tb_shop set price =price-100 where id=3


select * from tb_shop where manufacturingDate='11-4月-09';


delete from tb_shop where manufacturingDate=to_date('2009-4-11','yyyy-mm-dd');


alter user scott account unlock;


alter user scott identified by tiger;


alter user hr account unlock;


alter user hr identified by hr;


--查询工资最高的员工
select first_name,last_name,salary
from employees
where salary=(select max(salary) from employees);


select first_name,salary,department_id
from employees
where salary>all(select salary from employees where department_id=20);


select first_name,department_id from employees
where (salary,department_id) in (
select min(salary),department_id from employees
group by department_id
)order by department_id;


select employee_id,first_name,department_id
from employees a
where exists(
select * from employees b where b.manager_id=a.employee_id
)
order by department_id,employee_id;


create table emp(empId,ename,hireDate,deptId)
as
select a.employee_id,a.first_name||a.last_name,a.hire_date,a.department_id
from employees a
where a.department_id in (90,110);










insert into emp
select a.employee_id,a.department_id
from employees a where a.department_id=20;


delete emp where emp.deptid=
(select department_id from departments where department_name='Marketing');


update emp set(hiredate,deptid)=
(select hiredate,deptid from emp where emp.empid=206)
where emp.empid=100;


grant select on emp to scott;


grant update(empid,ename) on emp to scott;


revoke delete on emp from scott;


delete emp where empid=101;
savepoint p1;
insert into emp values(200,'孙悟空','12-2月-10',90);
select empid,ename from emp;
rollback to p1;
commit;

--************************************************************

set serveroutput on
set verify off


DECLARE
v_totalSal NUMBER(5);
v_deptno NUMBER(2);
BEGIN
select deptno into v_deptno from dept where dname=&dname;
select sum(sal) into v_totalSal from emp where deptno=v_deptno;
dbms_output.put_line('总工资为:'|| v_totalSal);
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('输入的部门编号不存在！');
END;
/


DECLARE
v_empno number(4);
v_ename varchar(10);
v_hiredate date;
BEGIN
v_empno:=&empno;
vselect ename,hiredate into v_ename,v_hiredate from emp where empno=v_empno;
dbms_output.put_line('姓名:'|| v_ename);
dbms_output.put_line('出生年月:'|| to_char(v_hiredate,'yyyy"年"mm"月"dd"日"');
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('输入的员工编号不存在！');
END;
/


DECLARE
v_pi CONSTANT NUMBER(6,5):=3.14;
v_r number(1):=2;
v_area number(6,2);
BEGIN
v_area:=v_pi*v_r;
DBMS_OUTPUT.PUT_LINE('圆周率:'|| v_pi);
DBMS_OUTPUT.PUT_LINE('半径:' || v_r);
DBMS_OUTPUT.PUT_LINE('面积:' || v_area);
END;
/


DECLARE
v_sal number(7,2);
v_comm number(7,2);
v_totalSal number(7,2);
BEGIN
select sal,comm into v_sal,v_comm
from emp where empno=&empno;


v_comm:=NVL(v_comm,0);


v_totalSal:=v_sal+v_comm;


DBMS_OUTPUT.PUT_LINE('基本工资:'|| v_sal);
DBMS_OUTPUT.PUT_LINE('补助:'|| v_comm);
DBMS_OUTPUT.PUT_LINE('总工资:'|| v_totalSal);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('请输入正确的雇员信息!');
END;
/




DECLARE
v_ename emp.ename%type;
v_sal emp.sal%type;
c_tax_rate constant number(3,2):=0.02;
v_tax_sal v_sal%type;
BEGIN
select ename,sal into v_ename,v_sal from emp where empno=&empno;
v_tax_sal:=v_sal*c_tax_rate;
DBMS_OUTPUT.PUT_LINE('雇员名:'|| v_ename);
DBMS_OUTPUT.PUT_LINE('雇员工资:'|| v_sal);
DBMS_OUTPUT.PUT_LINE('雇员所得税:'|| v_tax_sal);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('请输入正确的雇员信息!');
END;
/


DECLARE
v_emp_record detpt%rowtype;
BEGIN
select * frm v_emp_record from dept where deptno=&deptno;
DBMS_OUTPUT.PUT_LINE('部门编号:'|| v_emp_record.deptno);
DBMS_OUTPUT.PUT_LINE('部门名称:'|| v_emp_record.dname);
DBMS_OUTPUT.PUT_LINE('部门地区:'|| v_emp_record.loc);
END;
/


DECLARE
TYPE EMP_RECORD_TYPE IS RECORD
(
ename emp.ename%type,
sal emp.sal%type,
comm emp.comm%type,
total_sal sal%type
)
v_emp_record EMP_RECORD_TYPE;
BEGIN
select ename,sal,NVL(comm,0),sal+NVL(comm,0) into v_emp_record
from emp where empno=7521;
DBMS_OUTPUT.PUT_LINE('雇员名:'|| v_emp_record.ename);
DBMS_OUTPUT.PUT_LINE('工资:'|| v_emp_record.sal);
DBMS_OUTPUT.PUT_LINE('资金:'|| v_emp_record.comm);
DBMS_OUTPUT.PUT_LINE('总工资:'|| v_emp_record.total_sal);
END;
/


DECLARE
TYPE EMP_TABLE_TYPE IS TABLE OF NUMBER(4)
INDEX BY BINARY_INTEGER;


TYPE EMP_TABLE_TYPE_ENAMES IS TABLE OF emp.ename%type
INDEX BY BINARY_INTEGER;


v_emp_empnos EMP_TABLE_TYPE_EMPNOS;
v_emp_enames EMP_TABLE_TYPE_ENAMES;
BEGIN
v_emp_empnos(0):=7369;
v_emp_empnos(1):=7521;
v_emp_empnos(2):=7566;


select ename into v_emp_ename(0)
from emp where empno=v_emp_empnos(0);


select ename into v_emp_ename(1)
from emp where empno=v_emp_empnos(1);


select ename into v_emp_ename(2)
from emp where empno=v_emp_empnos(2);


dbms_output.put_line('雇员编号:'||v_emp_empnos(0)||' 雇员名:'||v_emp_enames(0));
dbms_output.put_line('雇员编号:'||v_emp_empnos(1)||' 雇员名:'||v_emp_enames(1));
dbms_output.put_line('雇员编号:'||v_emp_empnos(2)||' 雇员名:'||v_emp_enames(2));
END;
/


DECLARE
TYPE DEPT_TABLE_TYPE IS TABLE OF dept%ROWTYPE
INDEX BY BINARY_INTEGER;
v_dept_table DEPT_TABLE_TYPE;
BEGIN
select deptno,dname into v_dept_table(0).deptno,v_dept_table(0).dname
from dept where deptno=10;
select deptno,dname into v_dept_table(1).deptno,v_dept_table(1).dname
from dept where deptno=20;
select deptno,dname into v_dept_table(2).deptno,v_dept_table(2).dname
from dept where deptno=30;


dbms_output.put_line('部门编号 部门名称');


dbms_output.put_line(v_dept_table(0).deptno ||' '||v_dept_table(0).dname);
dbms_output.put_line(v_dept_table(1).deptno ||' '||v_dept_table(1).dname);
dbms_output.put_line(v_dept_table(2).deptno ||' '||v_dept_table(2).dname);
END;
/
declare
type dept_varray_type is varray(3) of varchar2(10);
v_dept_names_varray dept_varray_type:=dept_varray_type(null,null,null);
begin
v_dept_names_varray(1):='ACCOUNTING';
v_dept_names_varray(2):='RESEARCH';
v_dept_names_varray(3):='SALES';
dbms_output.put_line('===部门名称===');
dbms_output.put_line(v_dept_names_varray(1));
dbms_output.put_line(v_dept_names_varray(2));
dbms_output.put_line(v_dept_names_varray(3));
end;
/


declare
v_emp emp%rowtype;
v_dept_avgSal number(7,2);
begin
v_emp.empno:=&empno;


select sal,comm,deptno into v_emp.sal,v_emp.comm,v_emp.deptno
from emp where emp.empno=v_emp.empno;


dbms_output.put_line('雇员编号:'||v_emp.empno);
dbms_output.put_line('雇员更新前奖金:"|| nvl(v_emp.comm,0));


select avg(sal) into v_dept_avgSal from emp
where deptno=v_emp.deptno;

if v_emp.comm is null then
update emp set comm=v_dept_avgSal*0.1
where empno=v_emp.empno;
else
if v_emp.sal<v_dept_avgSal then
update emp set comm=comm+v_dept_avgSal*0.1
where empno=v_emp.empno;
else
update emp set comm=comm+v_emp.sal*0.1
where empno=v_emp.empno;
end if;
end if;
select comm into v_emp.comm from emp where empno=v_emp.empno;
dbms_output.put_line('雇员更新后奖金：'|| v_emp.comm);
excption
when no_data_found then
dbms_output.put_line('该雇员不存在');
end;
/


declare
v_deptno number(2):=&deptno;
begin
case v_deptno
when 10 then
update emp set comm=
case when comm is null then 100 else comm*1.1 end
where deptno=v_deptno;
when 20 then
update emp set comm=
case when comm is null then 200 else comm*1.2 end
where deptno=v_deptno;
when 30 then
update emp set comm=
case when comm is null then 300 else comm*1.3 end
where deptno=v_deptno;
else
dbms_output.put_line('不存在该部门!');
end case;
end;
/


declare
v_empno number(4):=&empno;
v_sal number(7,2);
begin
select sal into v_sal from emp
where empno=v_empno;
case
when v_sal<2000 then
dbms_output.put_line('一级工资');
when v_sal>=2000 then
dbms_output.put_line('二级工资');
when v_sal>=3000 then
dbms_output.put_line('三级工资');
when v_sal>=4000 then
dbms_output.put_line('四级工资');
else
dbms_output.put_line('五级工资');
end case;
exception
when no_data_found then
dbms_output.put_line('请输入正确的雇员编号!');
end;
/


create table rnd_temp_table
(
ID NUMBER(4) primary key,
value varchar2(10) not null
)


declare
type rnd_varray_type is varray(4) of varchar2(10);
r_rnd_varray run_varray_type=rnd_varray_type('DALLAS','CHICAGO','BOSTON','NEWYORK');
v_loop number(2):=1;
v_index number(1);
begin
loop
if v_loop=6 then
exit;
end if
v_index:=floor(dbms_random.value(1,5));
insert into rnd_temp_table values(v_loop,v_rnd_varray(v_index));
v_loop:=v_loop+1;
end loop;
end;
/


create table tb_stock(
ID number(2) primary key,
shopName varchar2(10),
stock number(5),
day_sales_volume number(5),
max_stock number(5),
min_stock number(2),
);
insert into tb_stock values(1,'彩电',100,10,500,50);
insert into tb_stock values(2,'空调',200,20,40);
insert into tb_stock values(3,'电脑',50,15,20);
insert into tb_stock values(4,'手机',300,600,10);


declare
v_stock tb_stock%rowtype;
v_n number(4):=0;
begin
v_stock.ID:=&ID;


select stock,day_sales_volume,min_stock
into v_stock.stock,v_stock.day_sales_volume,v_stock.min_stock
from tb_stock
where ID=v_stock.ID;


while v_stock.stock>v_stock.min_stock loop
v_stock.stock:=v_stock.stock-v_stock.day_sales_volume;
v_n:=v_n+1;
end loop;


dbms_output.put_line('商品编号:'|| v_stock.ID);
dbms_output.pub_line('采购期限:'|| v_n);
exception
when no_data_found then
dbms_output.put_line('请输入正确的商品编号!');
end;
/


declare
type dept_table_type is table of dept%rowtype
index by binary_integer;
v_dept_table dept_table_type;
begin
select deptno,v_dept_table(0).dname
from dept where deptno=10;


select deptno,v_dept_table(1).dname
from dept where deptno=20;


select deptno,v_dept_table(2).dname
from dept where deptno=30;


dbms_output.put_line('部门编号 部门名称');
for i in 0..v_dept_table.COUNT-1 loop
dbms_output.put_line(v_dept_table(i).deptno || ' ' || v_dept_table(i).dname);
end loop;


end;
/


declare
v_sal emp.sal%type;
v_name emp.ename%type;
begin
select sal,ename into v_sal,v_ename
from emp where empno=&empno;


if v_sal<3000 then
update emp set comm=sal*0.1 where ename=v_ename;
else
null;
end if;
end;
/


declare
v_dept_row dept%rowtype;
begin
select * into v_dept_row from dept;
insert into dept values(10,'PRODUCE','CHINA');
exception
when too_many_rows then
dbms_output.put_line('返回了多行,请使用游标来处理多行记录的集合');
when dup_val_on_index then
dbms_output.put_line('主键不能重复!');
end;
/


declare
ept_no_emp exception;
begin
update emp set comm=
case when comm is null then 50 else comm*1.0 end
where empno=&empno;




if sql%notfound then
raise ept_no_emp;
else
dbms_output.put_line('该雇员的奖金已经更新！');
end if;
exception
when ept_no_emp then
dbms_output.put_line('该雇员不存在!');
end;
/


DECLARE
TYPE CREATE_TABLE_RECORD IS RECORD
(
field_name varchar2(15),
field_type varchar2(15),
field_explain varchar2(15)
);
TYPE DYNAMIC_SQL_TABLE IS TABLE OF CREATE_TABLE_RECORD
INDEX BY BINARY_INTEGER;
v_dynamic_sql_table DYNAMIC_SQL_TABLE;


v_create_table_name VARCHAR2(20);
v_dynamic_ddl_sql VARCHAR2(500):='';
v_dynamic_dcl_sql VARCHAR2(500):='';
v_grant_user VARCHAR2(10);
v_grant_authority VARCHAR2(10);


BEGIN
v_create_table_name:='STVD';
v_grant_user:='hr';
v_grant_authority:='select';
v_dynamic_sql_table(0).field_name:='sid';
v_dynamic_sql_table(0).field_type:='varchar2(10)';
v_dynamic_sql_table(0).field_explain:='primary key';
v_dynamic_sql_table(1).field_name:='sname';
v_dynamic_sql_table(1).field_type:='varchar2(10)';
v_dynamic_sql_table(1).field_explain:='not null';
v_dynamic_sql_table(2).field_name:='sclass';
v_dynamic_sql_table(2).field_type:='varchar2(10)';
v_dynamic_sql_table(2).field_explain:='not null';

v_dynamic_ddl_sql:='create table '|| v_create_table_name ||chr(13)||'('||chr(13);


for i in 0..v_dynamic_sql_table.COUNT-1 loop
v_dynamic_ddl_sql:=v_dynamic_ddl_sql ||
v_dynamic_sql_table(i).field_name ||' '||
v_dynamic_sql_table(i).field_type ||' '||
v_dynamic_sql_table(i).field_explain ||','|| chr(13);
end loop;


v_dynamic_ddl_sql:=substr(v_dynamic_ddl_sql,length(v_dynamic_ddl_sql)-2);


v_dynamic_ddl_sql:=v_dynamic_ddl_sql||chr(13)||')';
v_dynamic_dcl_sql:='grant '|| v_grant_authority||' on '||v_create_table_name||' to '||v_grant_user;
execute immediate v_dynamic_ddl_sql;
execute immediate v_dynamic_dcl_sql;
END;
/


DECLARE
v_dynamic_sql VARCHAR2(100);
BEGIN
v_dynamic_sql:='UPDATE emp SET sal=sal*(1+:percent/100.0) where deptno=:deptno';
execute immediate v_dynamic_sql using &percent,&deptno;
END;
/


declare
v_dynamic_sql varchar2(100);
v_sal number(7,2);
v_empno number(4):=&empno;
v_percent number(2):=&percent;
begin
select sal into v_sal from emp where empno=v_empno;
dbms_output.put_line('更新前工资：'||v_sal);
v_dynamic_sql:='UPDATE emp SET sal=sal*(1+:percent/100.0)
where empno=:empno returning sal into :sal';
execute immediate v_dynamic_sql using v_percent,v_empno RETURNING INTO v_sal;
dbms_output.put_line('增长率:'||v_percent||'%');
dbms_output.put_line('新工资:'|| v_sal);
end ;
/


declare
v_dynamic_sql varchar2(100);
emp_record emp%ROWTYPE;
begin
v_dynamic_sql:='select * from emp where empno=:empno';
execute immediate v_dynamic_sql into emp_record using &empno;
dbms_output.put_line('雇员'|| emp_record.ename || '的工资是：'||emp_record.sal);


end;
/


DECLARE
TYPE EMP_ENAME_TABLE IS TABLE OF emp.ename%TYPE
INDEX BY BINARY_INTEGER;
v_emp_ename_table EMP_ENAME_TABLE;
v_dynamic_sql VARCHAR2(100);
begin
v_dynamic_sql:='select ename from emp where deptno=:deptno';
execute immediate v_dynamic_sql
BULK COLLECT INTO v_emp_ename_table USING &deptno;
for i in 1..v_emp_ename_table.COUNT LOOP
dbms_output.put_line(v_emp_ename_table(i));
end loop;
end;
/

--***********************************************************************

create table tb_test(A varchar2(10),B varchar2(10));
insert into tb_test values('aa','bb');
insert into tb_test values('aa','cc');
insert into tb_test values('bb','cc');
insert into tb_test values('aa','cc');
select * from tb_test


create or replace procedure proc_del_dup_rec
as
begin
delete tb_test a where a.ROWID=(
select max(rowid) from tb_test b
where
a.a=b.a and a.b=b.b
);
end;
/


create or replace procedure proc_transit_station(
v_start_station tb_station.station_name%type,
v_end_station tb_station.station_name%type,
v_line_name tb_station.line_name%type:='536'
)
as
v_start_forder tb_station.forder%type;
v_end_forder tb_station.forder%type;
v_station_line varchar2(100);


type station_name_table_type is table of tb_station.station_name%type;


v_station_name_table station_name_table_type;
begin
select forder into v_start_forder from tb_station where line_name=v_line_name
and station_name=v_start_station;
select forder into v_end_forder from tb_station where line_name=v_line_name
and station_name =v_end_station;
if v_start_forder<=v_end_forder then
select station_name bulk collect into v_station_name_table
from tb_station
where line_name=v_line_name
and forder>=v_start_forder and
forder<=v_end_forder;
else
select station_name bulk collect into v_station_name_table
from tb_station
where line_name=v_line_name
and forder<=v_start_forder and
forder>=v_end_forder order by forder desc;
end if;
dbms_output.put_line(v_line_name || '公交车从【'|| v_start_station || '->' || v_end_station || '】站的公交线路:');


for i in v_station_name_table.FIRST..v_station_name_table.LAST LOOP
v_station_line:=v_station_line || v_station_name_table(i)||'->';
end loop;
v_station_line:=Substr(v_station_line,length(v_station_line)-2);

dbms_output.put_line(v_station_line);


exception
when no_data_found then
dbms_output.put_line('请输入正确的公交车次及公交线路!');
end;
/


call proc_transit_station('常青路','武胜路','536');
call proc_transit_station('常青路','武胜路');


create or replace procedure proc_query_emp
(
param_empno number,
param_ename out varchar2,
param_salary out number
)
as
begin
select ename,sal into param_ename,param_salary from emp
where empno=param_empno;
exception
when no_data_found then
raise_application_error(-20001,'该雇员不存在!');
end;
/


DECLARE
v_empno emp.empno%type:=7788;
v_ename emp.ename%type;
v_sal emp.sal%type;
BEGIN
proc_query_emp(v_empno,v_ename,v_sal);
dbms_output.put_line(v_ename||' '||v_sal);
END;


create or replace procedure proc_compute
(
param_num1 in out number,
param_num2 in out number
)
as
v1 number,
v2 number
begin
v1:=param_num1/param_num2;
v2:=mod(param_num1,param_num2);
param_num1:=v1;
param_num2:=v2;
end;
/


declare
v_num1 number(2):=10;
v_num2 number(2):=3;
begin
proc_compute(v_num1,v_num2);
dbms_output.put_line(v_num1);
dbms_output.put_line(v_num2);
end;
/


create or replace procedure proc_add_dept
(
param_deptno number,
param_dname varchar2,
param_loc varchar2:=null
)
as
begin
insert into dept values(param_deptno,param_dname,param_loc);
exception
when dup_val_on_index then
raise_application_error(-20000,'部门编号不能重复');
end;
/


call proc_add_dept(60,'MANAGER','BEIJING');
call proc_add_dept(70,'PRODUCT');


call proc_add_dept(param_deptno=>80,param_dname=>'PURCHASE',param_loc=>'WUHAN');


call proc_add_dept(90,'ADMIN',param_loc=>'WUHAN');


create or replace function fun_get_user
return varchar2
as
v_user varchar2(100);
begin
select username into v_user from user_users;
return v_user;
end;
/


declare
v_user varchar2(100);
begin
v_user:=fun_get_user;
dbms_output.put_line('当前的用户是:'|| v_user);
end;
/


create or replace fun_get_sal(param_ename varchar2)
return number
as
v_sal emp.sal%type;
begin
select sal into v_sal from emp
where
upper(param_ename)=upper(ename);
exception
when no_data_found then
raise_application_error(-20000,'该雇员不存在');
end;
/


declare
v_ename emp.ename%type:='&v_ename';
begin
dbms_output.put_line(fun_get_sal(v_ename));
end;
/


create or replace function fun_get_emp_info
(
param_ename varchar2,
param_dname out varchar2
)
return varchar2
as
v_ejob emp.job%type;
begin
select a.job,b.dname into v_ejob,param_dname
from emp a,dept b
wher a.deptno=b.deptno and
upper(a.ename)=upper(param_ename);
return v_ejob;
exception
when no_data_found then
raise_application_error(-20000,'该雇员不存在!');
end;
/


delcare
v_ename varchar2(20):='&v_ename';
v_dname varchar2(20);
v_ejob varchar2(20);
begin
v_ejob:=fun_get_emp_info(v_ename,v_dname);
dbms_output.put_line('雇员名称'|| v_ename);
dbms_output.put_line('部门名称'|| v_dname);
dbms_output.put_line('雇员岗位'|| v_ejob);
end;
/


create or replace function fun_compute
param_num1 number,
param_num2 in out number
)
return number;
as
v1 number
begin
v1:=param_num1/param_num2;
param_num2:=mod(param_num1,param_num2);
return v1;
end;
/


declare
v_num1 number(2):=10;
v_num2 number(2):=3;
v_result number(2);
begin
v_result:=fun_compute(v_num1,v_num2);
dbms_output.put_line(v_num2);
dbms_output.put_line(v_result);
end;
/


select text from user_source where name=upper('fun_compute');


col object_name formart a20
select object_name,created,status from user_objects
where object_type in ('PROCEDURE','FUNCTION');


select name,type from User_dependences a
where a.referenced_name='EMP';


alter table emp modify ename varchar2(30);
select object_name,status from user_objects a,User_dependencies b
where b.name=a.object_name and
a.object_type in ('PROCEDURE','FUNCTION') and b.referenced_name='EMP';


alter prodecure proc_query_emp compile;


create or replace package emp_package
as
g_deptno number(3):=30;
procedure pro_add_employee(
param_empno number,
param_ename varchar2,
param_sal number,
param_deptno number:=g_deptno
);
function fun_get_sal(param_empno number) return number;
end emp_package;
/


create or replace package body emp_package
as
function fun_validate_deptno(param_deptno number)
return boolean
as
v_temp number;
begin
select 1 into v_temp from dept
where deptno=param_deptno;
return true;
exception
when no_data_found then
return false;
end;


procedure pro_add_employee
(
param_empno number,
param_deptno number:=g_deptno
)
as
if fun_validate_deptno(param_deptno) then
insert into emp(empno,deptno)
values(param_empno,param_ename,param_sal,param_deptno);
else
raise_application_error(-20001,'不存在部门');
end if;
exception
when dup_val_on_index then
raise_application_error(-20002,'该雇员编号已经传值!');
end;


function fun_get_sal(param_empno number) return number
as
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno=param_empno;
return v_sal;
exception
when no_data_found then
raise_application_error(-20003,'该雇员不存在!');
end;
end emp_packgae;
/


select text from user_source
where name='EMP_PACKAGE' AND TYPE='PACKAGE';

---************************************************

DECLAREcursor emp_cursor ISSelect ename,sal from emp where deptno=30;v_ename emp.ename%type;v_sal emp.sal%type;BEGINOPETN emp_cursor;LOOPFETCH emp_cursor INTO v_ename,v_sal;EXIT WHEN emp_cursor%notfound;dbms_output.put_line(v_ename || ' '|| v_sal);END LOOP;CLOSE emp_cursor;END;/DECLARECURSOR emp_cursor ISSELECT ename,sal FROM emp WHERE deptno=10;TYPE EMP_RECORD IS RECORD(ename emp.ename%type,sal emp.sal%type);TYPE ENAME_TABLE_TYPE IS TABLE OF EMP_RECORD;v_ename_table ENAME_TABLE_TYPE;BEGINOPEN emp_cursor;FETCH emp_cursor BULK COLLECT INTO v_ename_table;CLOSE emp_cursor;FOR i in v_ename_table.FIRST..v_ename_table.LAST LOOPdbms_output.put_line(v_ename_table(i).ename || ' '||v_ename_table(i).sal);END LOOP;END;/DECLARECURSOR emp_cursor ISSELECT ename FROM emp WHERE deptno=10;TYPE ENAME_TABLE_TYPE IS TABLE OF VARCHAR2(10);v_ename_table ENAME_TABLE_TYPE;BEGINIF NOT emp_cursor%ISOPEN THENOPEN emp_cursor;END IF:FETCH emp_cursor BULK COLLECT INTO v_ename_tablel;DBMS_OUTPUT.PUT_LINE('提取的总计行数：'|| emp_cursor%ROWCOUNT);CLOSE emp_cursor;END:/DELCARECURSOR emp_cursor IS SELECT ename,sal FROM emp WHERE deptno=10;emp_record emp_cursor%ROWTYPE;BEGINOPEN emp_cursor;LOOPFETCH emp_cursor INTO emp_record;EXIT WHEN emp_cursor%NOTFOUND;DBMS_OUTPUT.PUT_LINE(emp_record.ename || ' ' || emp_record.sal);END LOOP;CLOSE emp_cursor;END;/DECLARECURSOR emp_cursor(param_dept NUMBER) ISSELECT ename,sal FROM emp WHERE deptno=param_dept;emp_record emp_cursor%ROWTYPE;BEGINOPEN emp_cursor(10);LOOPFETCH emp_cursor INTO emp_record;EXIT WHEN emp_cursor%NOTFOUND;DBMS_OUTPUT.PUT_LINE(emp_record.ename||' '|| emp_record.sal);END LOOP;CLOSE emp_cursor;END;/DECLARECURSOR emp_cursor ISSELECT ename,sal FROM emp FOR UPDATE;v_emp_row emp_cursor%ROWTYPE;v_update_emp_count NUMBER(2):=0;BEGINOPEN emp_cursor;LOOPFETCH emp_cursor INTO v_emp_row;EXIT WHEN emp_cursor%NOTFOUND;IF v_emp_row.sal<2000 THENUPDATE emp SET sal=sal+100 WHERE CURRENT OF emp_cursor;v_update_emp_count:=v_update_emp_count+1;END IF;END LOOP;DBMS_OUTPUT.PUT_LINE('共有' || v_upate_emp_count || '名雇员被更新了!');CLOSE emp_cursor;END;/DECLARECURSOR emp_cursor ISSELECT deptno FROM emp FOR UPDATE;v_emp_row emp_cursor%ROWTYPE;v_update_emp_count NUMBER(2):=0;BEGINOPEN emp_cursor;LOOPFETCH emp_cursor INTO v_emp_row;EXIT WHEN emp_cursor%NOTFOUND;IF v_emp_row.deptno=30 THENDELETE emp WHERE CURRENT OF emp_cursor;v_update_emp_count:=v_update_emp_count+1;END IF;END LOOP;DBMS_OUTPUT.PUT_LINE('共有' || v_upate_emp_count || '名雇员被删除了!');CLOSE emp_cursor;END;/DECLARECURSOR emp_cursor ISSELECT ename,sal FROM emp FOR UPDATE NOWART;v_emp_row emp_cursor%ROWTYPE;v_update_emp_count NUMBER(2):=0;BEGINOPEN emp_cursor;LOOPFETCH emp_cursor INTO v_emp_row;EXIT WHEN emp_cursor%NOTFOUND;IF v_emp_row.sal<2000 THENUPDATE emp SET sal=sal+100 WHERE CURRENT OF emp_cursor;v_update_emp_count:=v_update_emp_count+1;END IF;END LOOP;DBMS_OUTPUT.PUT_LINE('共有' || v_upate_emp_count || '名雇员被更新了!');CLOSE emp_cursor;END;/DECLARECURSOR emp_cursor IS SELECT ename,sal FROM emp;BEGINFOR emp_row IN emp_cursor LOOPDBMS_OUTPUT.PUT_LINE('第'|| emp_cursor%ROWCOUNT || '个雇员:' || emp_row.ename);END LOOP;END;/BEGINFOR emp_row IN (SELECT ename,sal FROM emp) LOOPDBMS_OUTPUT.PUT_LINE(emp_row.ename);END LOOP;END;/DECLARETYPE EMP_CURSOR_TYPE IS REF CURSOR;emp_cursor EMP_CURSOR_TYPE;emp_row emp%ROWTYPE;BEGINOPEN emp_cursor FORSELECT * FROM emp WHERE deptno=30;LOOPFETCH emp_cursocursor%NOTFOUND;DBMS_OUTPUT.PUT_LINE('第'||emp_cursor%ROWCOUNT ||'个雇员:'||emp_row_ename);END LOOP;CLOSE emp_cursor;END;/CREATE OR REPLACE PROCEDURE proc_getEmpsByDeptno(param_deptno NUMBER,param_resultset OUT SYS_REFCURSOR)ASBEGINOPEN param_resultset FORSELECT ename,sal FROM emp WHERE deptno_param_deptno;END;/DECLARETYPE EMP_RECORD_TYPE IS RECORD(ename varchar2(10),sal number(7,2));v_emp_rows SYS_REFCURSOR;v_deptno NUMBER(2):=30;v_emp_row EMP_RECORD_TYPE;BEGINproc_getEmpsByDeptno(v_deptno,v_emp_rows);LOOPFETCH v_emp_rows into v_emp_row;EXIT WHEN v_emp_rows%NOTFOUND;DBMS_OUTPUT.PUT_LINE('第'||v_emp_roow%ROWCOUNT||'个雇员 名称：'||v_emp_row.ename || ' 工资:'|| v_emp_row.sal);END LOOP；CLOSE v_emp_rows;END;/create or replace function fun_getEmpsByHireDateYear(param_HireDateYear NUMBER)return sys_refcursorasparam_resultset SYS_REFCURSOR;beginopen param_resultset forselect ename,sal from emp where extract(year from hiredate)=param_HireDateYear;return param_resultset;end;/declaretype emp_record_type is record(ename varchar2(10),2));v_emp_rows SYS_REFCURSOR;v_hireDateYear NUMBER(4):=1981;v_emp_row EMP_RECORD_TYPE;beginv_emp_rows:=fun_getEmpsByHireDateYear(v_hireDateYear);loopfetch v_emp_rows intoo v_emp_rowexit when v_emp_rows%notfound; DBMS_OUTPUT.PUT_LINE('第'||v_emp_roow%ROWCOUNT||'个雇员 名称：'||v_emp_row.ename || ' 工资:'|| v_emp_row.sal);END LOOP；CLOSE v_emp_rows;END;/declarev_empno number(4):=7700;beginupdate emp set empno=v_empno where empno=v_empno;if sql%found thendbms_output.put_line('存在该雇员');elsedbms_output.put_line('不存在该雇员');end if;end;/declarev_deptno number(2):=20;v_rows_count number;beginupdate emp set sal=sal+100 where deptno=v_deptno;v_row_count:=sql%rowcount;if v_rows_count=0 thendbms_output.put_line('没有雇员被更新!');elsedbms_output.put_line('共有'||v_rows_count || '个雇员被更新了！');end if;end;/create or replace trigger tr_sec_empbeforeinsert or update or deleteon empbeginif to_char(sysdate,'Dy') in ('星期六','星期日') then raise_application_error(-20000,'不能在休息日改变雇员信息');end if;end;/delete emp where empno= 7788;create or replace trigger tr_sec_empbeforeinsert or update or deletebegin if to_char(sysdate,'星期日') thencasewhen updating thenraise_application_error(-20001,'不能在休息日更新雇员信息');when deleting thenraise_application_error(-20002,'不能在休息日删除雇员信息');when inserting thenraise_application_error(-20003,'不能在休息日插入雇员信息');end case;end if;end;/create table audit_table(ID number primary key,tb_name varchar2(20) not null,ins number not null,upd number not null,del number not null,starttime date,endtime date);create sequenceincrement by 1start with 1maxvalue 9999999cache 10cycle;create or replace trigger tr_sec_empafterinsert or update or deleteon empdeclarev_temp number;beginselect count(*) into v_temp from audit_tablewhere tb_name='EMP';if v_temp=0 theninsert into audit_table values(seq_audit.nextnval,'EMP',SYSDATE,null);end if;casewhen inserting thenupdate audit_table set ins=ins+1,endtime=sysdatewhere tb_name='EMP';when updating thenupdate audit_table set upd=upd+1,endtime=sysdatewhere tb_name='EMP';when deleting thenupdate audit_table set del=del+1,endtime=sysdatewhere tb_name='EMP';end caseend;/create or replace tigger tr_emp_salbefore update of sal on empfor each rowbeginif :NEW.sal<:OLD.sal thenraise_application_error(-20000,'新工资不能小于原有工资‘）；end if;end;/create table audit_sal_change(ID number primary key,ename varchar2(20) not null,oldsal number(7,newsal number(7,auditTime date);create or replace trigger tr_sal_changeafter update oof sal on empfor each rowbegininsert into audit_sal_change values(seq_audit_sal_change.NEXTVAL,:OLD.ename,:OLD.sal,:NEW.sal,sysdate);end;/create or replace trigger tr_sal_changeafter update of sal on empfor each rowwhen (OLD.job='MANAGER')begininsert into audit_sal_change values(seq_audit_sal_change.NEXTVAL,sysdate);end;/create or replace view view_dept_emp asselect a.deptno,a.dname,b.empno,b.enamefrom dept a,emp bwhere a.deptno=b.deptno;insert into view_dept_emp values(30,'2012','JACK');create or replace trigger tr_instead_of_dept_empinstead of insert on view_dept_empfor each rowdeclarev_temp number;beginselect count(*) into v_temp from dept where deptno=:NEW.deptno;IF v_temp=0 theninsert into dept(deptno,dname) values(:new.deptno,:new.dename);END IF;select count(*) into v_temp from emp where empno=:NEW.empno;IF v_temp=0 theninsert into emp(empno,ename) values(:new.empno,:new.ename);END IF;end;/conn sys/tiger as sysdbacreate table event_table(event varchar2(30),event_time date)create or replace trigger tr_startupafter startup on databasebegininsert into event_table values(ora_sysevent,SYSDATE);end;/create or replace trigger tr_shutdownbefore shutdow on databasebegininsert into event_table values(ora_sysevent,sysdate);end;/create table log_table(username varchar2(30),logon_time date,logonff_time date,Ip varchar2(20));create or replace trigger tr_logonafter logon on databasewhen (ora_login_user not in ('SYS','SYSMAN'))begininsert into log_table(username,logon_time,ip)values(ora_login_user,ora_client_ip_address);end;/create or replace trigger tr_loginffbefore logoff on databasewhen (ora_login_user not in ('SYS',logoff_time,ora_client_ip_address);end;/create table event_ddl(event varchar2(20),username varchar2(10),owner varchar2(10),objname varchar2(20),objtype varchar2(10),ddl_time date);create or replace trigger tr_ddlafter ddl on scott.schemabegininsert into event_ddl values(ora_sysevent,ora_login_user,ora_dict_obj_owner,ora_dict_obj_name,ora_dict_obj_type,SYSDATE);end;/end;/)

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!