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

代码大全,Oracle代码大全.从入门到熟练"/>

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

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

创建表空间的语法是：

CREATE TABLESPACE tablespacename

DATAFILE ‘filename’ [SIZE integer [K|M]]

[AUTOEXTEND [OFF|ON]];

CREATE USER 命令的语法是：

CREATE USER MARTIN

IDENTIFIED BY martinpwd

DEFAULT TABLESPACE USERS

TEMPORARY TABLESPACE TEMP;

授权语法

GRANT CONNECT TO MARTIN;

GRANT RESOURCE TO MARTIN;

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

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,1,'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,2,'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,3,'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,last_name,salary,department_id

from employees

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

select first_name,last_name,salary,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,last_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.first_name||a.last_name,a.hire_date,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 empm%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_recordm);

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_empm,v_emp.deptno

from emp where emp.empno=v_emp.empno;

dbms_output.put_line('雇员编号:'||v_emp.empno);

dbms_output.put_line('雇员更新前奖金:"|| nvl(v_empm,0));

select avg(sal) into v_dept_avgSal from emp

where deptno=v_emp.deptno;

if v_empm is null then

update emp set comm=v_dept_avgSal*0.1

where empno=v_emp.empno;

else

if v_emp.sal 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_empm from emp where empno=v_emp.empno;

dbms_output.put_line('雇员更新后奖金：'|| v_empm);

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,500,40);

insert into tb_stock values(3,'电脑',50,15,100,20);

insert into tb_stock values(4,'手机',300,15,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,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('部门编号 部门名称');

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,0,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','bb');

insert into tb_test values('aa','cc');

insert into tb_test values('bb','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,0,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,created,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_ename varchar2,

param_sal number,

param_deptno number:=g_deptno

)

as

if fun_validate_deptno(param_deptno) then

insert into emp(empno,ename,sal,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';

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

DECLARE

cursor emp_cursor IS

Select ename,sal from emp where deptno=30;

v_ename emp.ename%type;

v_sal emp.sal%type;

BEGIN

OPETN emp_cursor;

LOOP

FETCH 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;

/

DECLARE

CURSOR emp_cursor IS

SELECT 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;

BEGIN

OPEN 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 LOOP

dbms_output.put_line(v_ename_table(i).ename || ' '||v_ename_table(i).sal);

END LOOP;

END;

/

DECLARE

CURSOR emp_cursor IS

SELECT ename FROM emp WHERE deptno=10;

TYPE ENAME_TABLE_TYPE IS TABLE OF VARCHAR2(10);

v_ename_table ENAME_TABLE_TYPE;

BEGIN

IF NOT emp_cursor%ISOPEN THEN

OPEN emp_cursor;

END IF:

FETCH emp_cursor BULK COLLECT INTO v_ename_tablel;

DBMS_OUTPUT.PUT_LINE('提取的总计行数：'|| emp_cursor%ROWCOUNT);

CLOSE emp_cursor;

END:

/

DELCARE

CURSOR emp_cursor IS SELECT ename,sal FROM emp WHERE deptno=10;

emp_record emp_cursor%ROWTYPE;

BEGIN

OPEN emp_cursor;

LOOP

FETCH 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;

/

DECLARE

CURSOR emp_cursor(param_dept NUMBER) IS

SELECT ename,sal FROM emp WHERE deptno=param_dept;

emp_record emp_cursor%ROWTYPE;

BEGIN

OPEN emp_cursor(10);

LOOP

FETCH 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;

/

DECLARE

CURSOR emp_cursor IS

SELECT ename,sal FROM emp FOR UPDATE;

v_emp_row emp_cursor%ROWTYPE;

v_update_emp_count NUMBER(2):=0;

BEGIN

OPEN emp_cursor;

LOOP

FETCH emp_cursor INTO v_emp_row;

EXIT WHEN emp_cursor%NOTFOUND;

IF v_emp_row.sal<2000 THEN

UPDATE 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;

/

DECLARE

CURSOR emp_cursor IS

SELECT deptno FROM emp FOR UPDATE;

v_emp_row emp_cursor%ROWTYPE;

v_update_emp_count NUMBER(2):=0;

BEGIN

OPEN emp_cursor;

LOOP

FETCH emp_cursor INTO v_emp_row;

EXIT WHEN emp_cursor%NOTFOUND;

IF v_emp_row.deptno=30 THEN

DELETE 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;

/

DECLARE

CURSOR emp_cursor IS

SELECT ename,sal FROM emp FOR UPDATE NOWART;

v_emp_row emp_cursor%ROWTYPE;

v_update_emp_count NUMBER(2):=0;

BEGIN

OPEN emp_cursor;

LOOP

FETCH emp_cursor INTO v_emp_row;

EXIT WHEN emp_cursor%NOTFOUND;

IF v_emp_row.sal<2000 THEN

UPDATE 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;

/

DECLARE

CURSOR emp_cursor IS SELECT ename,sal FROM emp;

BEGIN

FOR emp_row IN emp_cursor LOOP

DBMS_OUTPUT.PUT_LINE('第'|| emp_cursor%ROWCOUNT || '个雇员:' || emp_row.ename);

END LOOP;

END;

/

BEGIN

FOR emp_row IN (SELECT ename,sal FROM emp) LOOP

DBMS_OUTPUT.PUT_LINE(emp_row.ename);

END LOOP;

END;

/

DECLARE

TYPE EMP_CURSOR_TYPE IS REF CURSOR;

emp_cursor EMP_CURSOR_TYPE;

emp_row emp%ROWTYPE;

BEGIN

OPEN emp_cursor FOR

SELECT * FROM emp WHERE deptno=30;

LOOP

FETCH 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

)AS

BEGIN

OPEN param_resultset FOR

SELECT ename,sal FROM emp WHERE deptno_param_deptno;

END;

/

DECLARE

TYPE 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;

BEGIN

proc_getEmpsByDeptno(v_deptno,v_emp_rows);

LOOP

FETCH 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_refcursor

as

param_resultset SYS_REFCURSOR;

begin

open param_resultset for

select ename,sal from emp where extract(year from hiredate)=param_HireDateYear;

return param_resultset;

end;

/

declare

type emp_record_type is record

(

ename varchar2(10),

sal number(7,2)

);

v_emp_rows SYS_REFCURSOR;

v_hireDateYear NUMBER(4):=1981;

v_emp_row EMP_RECORD_TYPE;

begin

v_emp_rows:=fun_getEmpsByHireDateYear(v_hireDateYear);

loop

fetch v_emp_rows intoo 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;

/

declare

v_empno number(4):=7700;

begin

update emp set empno=v_empno where empno=v_empno;

if sql%found then

dbms_output.put_line('存在该雇员');

else

dbms_output.put_line('不存在该雇员');

end if;

end;

/

declare

v_deptno number(2):=20;

v_rows_count number;

begin

update emp set sal=sal+100 where deptno=v_deptno;

v_row_count:=sql%rowcount;

if v_rows_count=0 then

dbms_output.put_line('没有雇员被更新!');

else

dbms_output.put_line('共有'||v_rows_count || '个雇员被更新了!');

end if;

end;

/

create or replace trigger tr_sec_emp

before

insert or update or delete

on emp

begin

if to_char(sysdate,'Dy') in ('星期六','星期日') then

raise_application_error(-20000,'不能在休息日改变雇员信息');

end if;

end;

/

delete emp where empno= 7788;

create or replace trigger tr_sec_emp

before

insert or update or delete

begin

if to_char(sysdate,'Dy') in ('星期六','星期日') then

case

when updating then

raise_application_error(-20001,'不能在休息日更新雇员信息');

when deleting then

raise_application_error(-20002,'不能在休息日删除雇员信息');

when inserting then

raise_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 sequence

increment by 1

start with 1

maxvalue 9999999

cache 10

cycle;

create or replace trigger tr_sec_emp

after

insert or update or delete

on emp

declare

v_temp number;

begin

select count(*) into v_temp from audit_table

where tb_name='EMP';

if v_temp=0 then

insert into audit_table values(seq_audit.nextnval,'EMP',0,0,0,SYSDATE,null);

end if;

case

when inserting then

update audit_table set ins=ins+1,endtime=sysdate

where tb_name='EMP';

when updating then

update audit_table set upd=upd+1,endtime=sysdate

where tb_name='EMP';

when deleting then

update audit_table set del=del+1,endtime=sysdate

where tb_name='EMP';

end case

end;

/

create or replace tigger tr_emp_sal

before update of sal on emp

for each row

begin

if :NEW.sal<:old.sal then>

raise_application_error(-20000,'新工资不能小于原有工资‘);

end if;

end;

/

create table audit_sal_change(

ID number primary key,

ename varchar2(20) not null,

oldsal number(7,2) not null,

newsal number(7,2) not null,

auditTime date

);

create or replace trigger tr_sal_change

after update oof sal on emp

for each row

begin

insert into audit_sal_change values(seq_audit_sal_change.NEXTVAL,:OLD.ename,:OLD.sal,:NEW.sal,sysdate);

end;

/

create or replace trigger tr_sal_change

after update of sal on emp

for each row

when (OLD.job='MANAGER')

begin

insert into audit_sal_change values(seq_audit_sal_change.NEXTVAL,:OLD.ename,:OLD.sal,:NEW.sal,sysdate);

end;

/

create or replace view view_dept_emp as

select a.deptno,a.dname,b.empno,b.ename

from dept a,emp b

where a.deptno=b.deptno;

insert into view_dept_emp values(30,'ADMIN','2012','JACK');

create or replace trigger tr_instead_of_dept_emp

instead of insert on view_dept_emp

for each row

declare

v_temp number;

begin

select count(*) into v_temp from dept where deptno=:NEW.deptno;

IF v_temp=0 then

insert 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 then

insert into emp(empno,ename) values(:new.empno,:new.ename);

END IF;

end;

/

conn sys/tiger as sysdba

create table event_table

(

event varchar2(30),

event_time date

)

create or replace trigger tr_startup

after startup on database

begin

insert into event_table values(ora_sysevent,SYSDATE);

end;

/

create or replace trigger tr_shutdown

before shutdow on database

begin

insert 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_logon

after logon on database

when (ora_login_user not in ('SYS','SYSMAN'))

begin

insert into log_table(username,logon_time,ip)

values(ora_login_user,SYSDATE,ora_client_ip_address);

end;

/

create or replace trigger tr_loginff

before logoff on database

when (ora_login_user not in ('SYS','SYSMAN'))

begin

insert into log_table(username,logoff_time,ip)

values(ora_login_user,SYSDATE,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_ddl

after ddl on scott.schema

begin

insert into event_ddl values(

ora_sysevent,

ora_login_user,

ora_dict_obj_owner,

ora_dict_obj_name,

ora_dict_obj_type,

SYSDATE

);

end;

/

end;

/

)