ORACLE数据库的基本语法集锦

来源：互联网发布：电脑控制器软件编辑：程序博客网时间：2024/06/07 06:28

-- 表

create table test (names varchar2(12),

dates date,

num int,

dou double);

-- 视图

create or replace view vi_test as

select * from test;

-- 同义词

create or replace synonym aa

for dbusrcard001.aa;

-- 存储过程

create or replace produce dd(v_id inemployee.empoy_id%type)

begin

end

dd;

-- 函数

create or replace function ee(v_id inemployee%rowtype) return varchar(15)

var_test varchar2(15);

begin

return var_test;

exception when others then

end

-- 三种触发器的定义

create or replace trigger ff

alter delete

on test

for each row

declare

begin

delete from test;

ifsql%rowcount < 0 or sql%rowcount is null then

rais_replaction_err(-20004,"错误")

end if

end

create or replace trigger gg

alter insert

on test

for each row

declare

begin

if:old.names = :new.names then

raise_replaction_err(-2003,"编码重复");

end if

end

create or replace trigger hh

for update

on test

for each row

declare

begin

ifupdating then

if :old.names <> :new.names then

reaise_replaction_err(-2002,"关键字不能修改")

end if

endif

end

-- 定义游标

declare

cursor aa is

select names,num from test;

begin

for bb in aa

loop

if bb.names = "ORACLE" then

end if

end loop;

end

-- 速度优化，前一语句不后一语句的速度快几十倍

select names,dates

from test,b

where test.names = b.names(+) and

b.names is null and

b.dates > date('2003-01-01','yyyy-mm-dd')

select names,dates

from test

where names not in ( select names

from b

where dates >to_date('2003-01-01','yyyy-mm-dd'))

-- 查找重复记录

select names,num

from test

where rowid != (select max(rowid)

from test b

where b.names = test.names and

b.num = test.num)

-- 查找表TEST中时间最新的前10条记录

select * from (select * from test order bydates desc) where rownum < 11

-- 序列号的产生

create sequence row_id

minvalue 1

maxvalue 9999999999999999999999

start with 1

increment by 1

insert into testvalues(row_id.nextval,....)

存储过程

1.基本结构

CREATE OR REPLACE PROCEDURE 存储过程名字

(

参数1 IN NUMBER,

参数2 IN NUMBER

) IS

变量1 INTEGER:=0;

变量2 DATE;

BEGIN

END 存储过程名字

2.SELECT INTO STATEMENT

将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条

记录，否则抛出异常(如果没有记录抛出NO_DATA_FOUND)

例子：

BEGIN

SELECT col1,col2 into 变量1,变量2 FROMtypestruct where xxx;

EXCEPTION

WHEN NO_DATA_FOUND THEN

xxxx;

END;

...

3.IF 判断

IFV_TEST=1 THEN

BEGIN

do something

END;

ENDIF;

4.while 循环

WHILE V_TEST=1 LOOP

BEGIN

XXXX

END;

ENDLOOP;

5.变量赋值

V_TEST := 123;

6.用for in 使用cursor

...

CURSOR cur IS SELECT * FROM xxx;

BEGIN

FORcur_result in cur LOOP

BEGIN

V_SUM :=cur_result.列名1+cur_result.列名2

END;

ENDLOOP;

END;

7.带参数的cursor

CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID;

OPEN C_USER(变量值);

LOOP

FETCH C_USER INTO V_NAME;

EXITFETCH C_USER%NOTFOUND;

do something

ENDLOOP;

CLOSE C_USER;

存储过程的一些相关问题

　详细讲解有关Oracle存储过程的相关问题：

　　1.在Oracle数据库中，数据表别名是不能加as的，例如：

　　selecta.appname from appinfo a；—— 正确

　　selecta.appname from appinfo as a；—— 错误

　　注释：这可能是为了防止和Oracle数据库中的存储过程中的关键字as冲突的问题。

　　2.在存储过程中，select某一字段时，后面必须紧跟into，假如select整个记录，利用游标的话就另当别论了。

select af.keynode into kn from

APPFOUNDATION af where af.appid=aid

and af.foundationid=fid;-- 有into，正确编译

select af.keynode from APPFOUNDATION af

where af.appid=aid and af.foundationid=fid;

-- 没有into，编译报错，提示：Compilation

Error: PLS-00428: an INTO clause is

expected in this SELECT statement

　　3.在我们利用select……into……语法时，必须先确保数据库中有该条记录，否则会报出"no data found"异常。

　　可以在该语法之前，先利用selectcount（*） from 查看数据库中是否存在该记录，如果存在，再利用select……into……

　　4.请注意，在存储过程中，别名不能和字段名称相同，否则虽然编译可以通过，但在运行的阶段会报错。

select keynode into kn from APPFOUNDATION

where appid=aid and foundationid=fid;-- 正确运行

select af.keynode into kn fromAPPFOUNDATION af

where af.appid=appid andaf.foundationid=foundationid;

-- 运行阶段报错，提示

ORA-01422:exact fetch returns more

than requested number of rows

　　5.在存储过程中，关于出现null的问题

　　假如有一个表X，定义如下：

create table X(

id varchar2(50) primary key not null,

vcount number(8) not null,

bid varchar2(50) not null -- 外键

);

　　假如在存储过程中，使用如下语句：

　　select sum（vcount） into fcount from X wherebid='xxxxxx'；如果X表中不存在bid="xxxxxx"的记录，则fcount=null（即使fcount定义时设置了默认值，例如：fcount number（8）：=0依然无效，fcount还是会变成null），这样以后使用fcount时就可能会出现问题，所以在这里我们最好先判断一下：

if fcount is null then

fcount:=0;

end if;

　　6.Hibernate调用Oracle的存储过程

this.pnumberManager.getHibernateTemplate().execute(

new HibernateCallback() ...{

public Object doInHibernate(Sessionsession)

throws HibernateException, SQLException...{

CallableStatement cs = session

.connection()

.prepareCall("{callmodifyapppnumber_remain(?)}");

cs.setString(1, foundationid);

cs.execute();

return null;

}

});

*数据定义语句(只操作表的结构)

--创建表

--创建部门表

create table department_13(department_idnumber(6), department_name varchar(25),

manager_idnumber(6), location_id number(4));

--使用子查询创建表

create table department_13_temp as selectdepartment_id, department_name from department_13;

--修改表

--增加字段

alter table department_13_tempadd(manager_id number(6));

--删除字段

alter table department_13_temp drop columnmanager_id;

--修改字段名称

alter table 表名 rename column 原列名 to 新列名;

--修改字段类型

alter table department_13_tempmodify(manager_id varchar(6));

--修改字段大小

alter table department_13_tempmodify(manager_id number(4));

--删除表

drop table department_13_temp;

--数据字典表

select table_name from dba_tables;

select table_name from user_tables;

--察看用户拥有的数据库对象类型

select distinct object_type fromuser_objects;

--约束

--创建非空约束(同时也是列级约束)

create table department_13(department_idnumber(6) constraint dept_13_id notnull, department_name

varchar(25),

manager_idnumber(6), location_id number(4));

--创建唯一性约束(同时也是表级约束)

create table department_13(department_idnumber(6), department_name varchar(25),

manager_idnumber(6), location_id number(4),

constraintdep_id_13_uni unique(department_id));

--创建主键约束

create table department_13(department_idnumber(6), department_name varchar(25),

manager_idnumber(6), location_id number(4),

constraintdep_id_13_pri primary key(department_id));

--创建外键约束

create table employee_13(employee_idnumber(6), employee_name varchar(25), email varchar(28), hire_date

date,

job_id varchar(20),salary number(8,2),commission_pct number(2,2),

manager_id number(6),department_id number(6),

constraintemp_13_foreign foreignkey(department_id)

referencesdepartment_13(department_id));

--check约束

create table employee_13_temp(employee_idnumber(6), employee_name varchar(25), email varchar(28),

hire_date date,

job_id varchar(20),salary number(8,2),commission_pct number(2,2),

manager_id number(6),department_id number(6),

constraint emp_sal_mincheck(salary > 8888));

--增加约束

alter table employee_13_temp add constraintemp_13_pri primary key(employee_id);

alter table employee_13_temp modify(salarynot null);

--删除约束

alter table employee_13_temp drop constaintemp_13_pri;

--删除被外键参照的主键约束

alter table department_13 drop primary keycascade;

--手工创建索引

create Index emp_13_sal onemployee_13(salary);

--删除索引

drop index emp_13_sal;

--创建序列

create sequence hospital_id

minvalue 1

maxvalue 999999999999

start with 11

increment by 1

cache 10;

--创建视图

create or replace view emp_13_11 as selectemployee_id, employee_name, salary, job_id from employee_13

where department_id = 11;

create or replace view emp_13_dept asselect d.department_name, d.manager_id, e.employee_name, e.salary

from employee_13 e, department_13 d

where e.department_id = d.department_id and e.department_id = 11;

create or replace view emp_13_dept_temp asselect d.department_name, d.manager_id, e.employee_name,

e.salary from employee_13 e, department_13d

where e.department_id = d.department_id;

--删除视图

drop view emp_13_11;

--查找出薪水最高的三个员工的信息(Top-N分析法):使用到了行内视图

select rownum, employee_name, salary from(select employee_name, salary from employee_13 order by

salary desc) where rownum <=3;

select * from (select employee_name, salaryfrom employee_13 order by salary desc) where rownum <=3;

--创建一个同义词

create synonym ct fromSystem.emp_13_dept_temp;

--删除同义词

drop synonym ct

*数据操作语句(操作表的数据)

--Insert语句

insert into department_13 values(13,'测试部',120,119);

insert into department_13 values(28,null,null,113);

insert into department_13values(&department_id, '&department_name',&manager_id,&location_id);

--Update语句

update employee_13 set salary=66566 whereemployee_id = 3;

--merge语句(数据合并语句)

merge into depat_13_temp a

using department_13 b

on(a.department_id = b.department_id)

when matched then

update set

a.department_name = b.department_name,

a.manager_id = b.manager_id,

a.location_id = b.location_id

when not matched then

insert(a.department_id, a.department_name, a.manager_id, a.location_id)

values(b.department_id, b.department_name, b.manager_id, b.location_id);

--提交事务

update department_13 set manager_id = 120where department_id = 14;

commit;

--察看自动提交环境变量

show autocommit;

--打开自动提交

set autocommit on;

--savepoint

update department_13 set manager_id=130where department_id > 14;

savepoint undo1;

delete from department_13 wheredepartment_id > 14;

savepoint undo2;

rollback to undo1;

--SELECT语句

--带算书表达式的select语句

select employee_id, employee_name, salary,salary*12 from employee_13;

--带连接表达式的select语句

select employee_name|| '的年薪是:'|| salary*12 ||'美元'from employee_13;

--对空值的引用

select employee_name, salary,salary*(1+commission_pct) "奖金" from employee_13;

--字段别名

select employee_name "姓名", salary "薪水", salary*(1+commission_pct)"奖金" fromemployee_13;

--去掉重复值

select distinct salary from employee_13;

--带条件的查询

select employee_id, employee_name, salaryfrom employee_13 where department_id = 10;

--得到当前日期格式字符串

select * from v$nls_parameters;

--得到系统当前日期

select sysdate from dual;

--比较操作符

--between..and

select employee_name, job_id, hire_datefrom employee_13 where salary between 4000 and 7000;

--in

select employee_name, job_id, hire_datefrom employee_13 where salary in(6111,4111,7222);

--like

select employee_name, job_id, hire_datefrom employee_13 where employee_name like '李%';

--is null

select employee_name, job_id, hire_datefrom employee_13 where commission_pct is null;

--比较操作的逻辑运算符

-AND

select employee_name, job_id, hire_datefrom employee_13 where salary between 4000 and 7000 and job_id

= '软件架构师';

-- and .. or

select employee_name, salary from employee_13 where (job_name = '软件工程师' or job_name = '软件架构师

') and salary > 4000;

--排序显示

--单字段排序

select employee_name, salary fromemployee_13 order by salary desc;

--组合字段排序(主排序字段相同时，按照辅助排序字段排序)

select employee_name, salary, hire_datefrom employee_13 order by salary desc,hire_date desc;

/**

*SQL函数

--单行函数

--字符函数

--大小写转换函数

select employee_id, salary from employee_13where lower(employee_name) = 'draglong';

select employee_id, salary from employee_13where upper(employee_name) = 'DRAGLONG';

select employee_id, salary from employee_13where Initcap(employee_name) like 'D%';

--字符处理函数

select replace('db2', 'oracle') from dual;

select employee_name, concat(employee_name,job_name) name, length(employee_name) len, instr

(employee_name,'g') ins

from employee_13 where substr(employee_name,1,5) = 'dragl';

--日期函数

select employee_name, job_name,(sysdate-hire_date)/7 weeks from employee_13;

select hire_date,months_between(sysdate,hire_date) week, add_months(hire_date,6) week2, next_day

(sysdate,'星期六') nextday,

last_day(hire_date) fromemployee_13;

--round函数

select employee_name, hire_date,round(hire_date,'MONTH') from employee_13;

--trunc函数

select trunc(sysdate,'D'),trunc(sysdate,'MM'),trunc(sysdate,'MONTH'), trunc(sysdate,'DD') from dual;

--转换函数

--日期转换为字符TO_CHAR(字段名,'格式字符串')

select employee_name, to_char(hire_date,'MM/YY') from employee_13;

select employee_name, to_char(hire_date,'YEAR"年"MM"月"DD"日"') from employee_13;

select employee_name,to_char(hire_date,'"北京时间"YYYY"年"MONDD"日"HH24"时"MI"分"SS"秒"') FROM

employee_13;

select employee_name,to_char(hire_date,'YYYYspth"年"MONDD"日"HH24"时"MI"分"SS"秒"') FROM employee_13;

--数字转换为字符TO_CHAR(字段名,'格式字符串')

select employee_name,to_char(salary,'$99,999.99') from employee_13;

select employee_name, to_char(salary,'L99,999.99')from employee_13;

select employee_name,to_char(salary,'$00,000.00') from employee_13;

--字符型日期转换为日期型日期to_date('日期字段值','格式字符串')

insert into employee_13values(213,null,null,to_date('2007年04月28

日','YYYY"年"MM"月"DD"日"'),null,null,null,null,null);

--第五类函数

/**

*为空处理函数

--NVL函数

select employee_name "姓名", salary "薪水",salary*(1+nvl(commission_pct,0)) "奖金" from employee_13;

--NVL2

select employee_name "姓名", salary "薪水", nvl2(commission_pct,'架构师','工程师') "级别" from

employee_13;

--NULLIF

select employee_name,length(employee_name)a, job_name, length(job_name) b, nullif(length

(employee_name),length(job_name)) resultfrom employee_13;

--COALESCE(取得列表中的第一个非空值)

select employee_name,coalesce(commission_pct,salary) from employee_13;

/**

*CASE语句

select employee_name, job_name, salary,

case job_name when '软件工程师' then 0.40*salary

when '软件架构师' then 0.30*salary

when '系统架构师' then 0.20*salary

else salary end "加薪幅度"

from employee_13;

/**

*DECODE语句

select employee_name, salary, job_name,

decode(job_name, '软件工程师',0.40*salary,

'软件架构师',0.30*salary,

'系统架构师',0.20*salary,

salary) "工资涨幅"

from employee_13;

/**

* 分组函数(多行函数)

select employee_name,salary,avg(salary),count(salary),max(salary),min(salary),sum(salary) from

employee_13;

--Group by语句

select department_id, avg(salary) fromemployee_13 group by department_id;

/**

*多表连接和子查询

--等值连接

select d.department_name, e.employee_name, d.department_id, e.salary

from employee_13 e, department_13 d

where e.department_id = d.department_id

and d.department_name = '开发部';

--非等值连接

select d.department_name, e.employee_name, d.department_id, e.salary

from employee_13 e, department_13 d

where e.department_id = d.department_id

and e.salary between 4000 and 7000;

--左外连接

select d.department_name, e.employee_name, d.department_id, e.salary

from employee_13 e, department_13 d

where e.department_id(+) = d.department_id;

--右外连接

select d.department_name, e.employee_name, d.department_id, e.salary

from employee_13 e, department_13 d

where e.department_id = d.department_id(+);

--自连接

select worker.employee_name || ' work for '|| manager.employee_name from employee_13 worker,

employee_13 manager

where worker.manager_id = manager.employee_id;

--sql 1999

--cross join

select employee_name, department_name fromemployee_13 cross join department_13;

--natural join

select department_name from department_13natural join employee_13;

--join...using(使用指定的字段进行等值连接)

select department_name from department_13join employee_13 using(department_id);

--join..on(不同名称的字段上的等值连接)

select department_name from department_13 djoin employee_13 e on(d.department_id = e.employee_id);

--左外连接

select d.department_name, e.employee_name, d.department_id, e.salary

from employee_13 e right outer joindepartment_13 d

on d.department_id = e.department_id;

--全连接

select e.employee_name, e.department_id,d.department_name from employee_13 e full outer join

department_13 d

on(d.department_id= e.department_id);

/**

*子查询

select employee_name, salary fromemployee_13 where salary>(

select salary fromemployee_13 where employee_name = '高伟祥') order by salary;

--单行比较操作

select employee_name, job_name, salary fromemployee_13 where job_name =(select job_name from

employee_13 where employee_id = 2);

select employee_name, job_name, salary fromemployee_13 where salary =(select min(salary) from

employee_13);

select department_id, min(salary) from employee_13group by department_id having min(salary) > (select

min(salary) from employee_13 wheredepartment_id = 10);

--多行比较操作

select employee_id, employee_name,job_name, salary from employee_13 where department_id in(

select department_id fromemployee_13 where job_name = '软件工程师') AND

job_name <>'软件工程师';

select employee_name, salary, job_name fromemployee_13 where salary < ANY(

select salary fromemployee_13 where job_name = '软件工程师') and

job_name <>'软件工程师';

select employee_name, salary, job_name fromemployee_13 where salary < ALL(

select salary fromemployee_13 where job_name = '软件工程师') and

job_name <>'软件工程师';

select employee_name,job_name,salary fromemployee_13 where department_id in (select department_id from

employee_13 where job_name='软件工程师' and job_name<>"软件工程师');

select replace('Oracle Sql','Oracle','DB2')from dual;

/**

*PL/SQL开发

--变量定义

set serveroutput on

declare

idnumber(6,4):=0;

hire_date date:=sysdate+7;

v_tax_rate constant number(3,2):=8.25;

v_validboolean not null:=true;

begin

dbms_output.put_line('编号是: ' ||id);

dbms_output.put_line('入职时间: ' ||hire_date);

dbms_output.put_line('税率: ' ||v_tax_rate);

ifv_valid then

dbms_output.put_line('这是真的');

else

dbms_output.put_line('这是假的');

endif;

end;

--表类型复合变量的定义

set serveroutput on

declare

type name_table_type is table of varchar(26) index by binary_integer;

t_name name_table_type;

begin

t_name(1):='陈龙';

t_name(2):='李林波';

t_name(3):='阿猫';

dbms_output.put_line('第一个数据为: '||t_name(1));

dbms_output.put_line('第二个数据为: '||t_name(2));

dbms_output.put_line('第三个数据为: '||t_name(3));

end;

--记录类型变量的定义

set serveroutput on;

declare

type dept_record_type is record(

department_id number(6),

department_name varchar(20),

manager_id number(6),

location_id number(6));

d_dept dept_record_type;

begin

d_dept.department_id :=111;

d_dept.department_name := '开发部';

d_dept.manager_id := 110;

d_dept.location_id := 119;

dbms_output.put_line(d_dept.department_id);

dbms_output.put_line(d_dept.department_name );

dbms_output.put_line(d_dept.manager_id);

dbms_output.put_line(d_dept.location_id);

end;

//查找数据库中的记录存放在记录类型变量中

set serveroutput on;

declare

type dept_record_type is record(

department_id number(6),

department_name varchar(20),

manager_id number(6),

location_id number(6));

d_dept dept_record_type;

begin

select department_id, department_name, manager_id, location_id into

d_dept.department_id, d_dept.department_name, d_dept.manager_id,d_dept.location_id from

department_13 where department_id = 11;

dbms_output.put_line(d_dept.department_id);

dbms_output.put_line(d_dept.department_name );

dbms_output.put_line(d_dept.manager_id);

dbms_output.put_line(d_dept.location_id);

end;

--使用%type属性定义变量

set serveroutput on

declare

e_name employee_13.employee_name%type;

e_sal number(8,2);

e_min_sal e_sal%type:= 1888;

begin

e_name:='jack';

e_sal := 1899;

e_min_sal:=e_sal/3;

dbms_output.put_line(e_name);

dbms_output.put_line(e_sal);

dbms_output.put_line(e_min_sal);

end;

//查找数据库中的记录存放在%type属性定义的变量中

set serveroutput on

declare

e_name employee_13.employee_name%type;

e_sal number(8,2);

e_min_sal e_sal%type:= 1888;

begin

select employee_name, salary, salary/3 into e_name,e_sal,e_min_sal fromemployee_13 where employee_id

= 2;

dbms_output.put_line(e_name);

dbms_output.put_line(e_sal);

dbms_output.put_line(e_min_sal);

end;

--%rowtype属性定义的变量

set serveroutput on

declare

r_dept department_13%rowtype;

begin

r_dept.department_id := 115;

r_dept.department_name := 'temp';

r_dept.manager_id := 111;

r_dept.location_id:=112;

dbms_output.put_line(r_dept.department_id);

dbms_output.put_line(r_dept.department_name);

dbms_output.put_line(r_dept.manager_id);

dbms_output.put_line(r_dept.location_id);

end;

--pl/sql程序块

set serveroutput on

DECLARE

v_weight NUMBER(3) := 100;

v_message VARCHAR2(255) :='Outer Value';

BEGIN

DECLARE

v_weight NUMBER(3) := 1;

v_message VARCHAR2(255) := 'Innervalue';

BEGIN

v_weight := v_weight + 1;

v_message := 'Put' || v_message;

dbms_output.put_line(v_weight);

dbms_output.put_line(v_message);

END;

v_weight := v_weight + 1;

v_message := 'Put'|| v_message;

dbms_output.put_line(v_weight);

dbms_output.put_line(v_message);

END;

--PL/SQL中的select语句

set serveroutput on

declare

e_name employee_13.employee_name%type;

e_sal number(8,2);

e_min_sal e_sal%type:= 1888;

begin

select employee_name, salary, salary/3 into e_name,e_sal,e_min_sal fromemployee_13;

dbms_output.put_line(e_name);

dbms_output.put_line(e_sal);

dbms_output.put_line(e_min_sal);

end;

--修改当前会话的语言环境

alter session set nls_language=american;

--pl/sql中的insert ,update, delete

begin

insert into department_13 values(321,'test',111,null);

update department_13 set manager_id = 112 where department_id =12;

delete department_13 where department_id = 15;

commit;

end;

--pl/sql中的条件分支语句

--if...then

set serveroutput on

declare

v_sal number;

begin

select salary into v_sal from employee_13 where employee_id = 2;

IFv_sal < 3000 THEN

dbms_output.put_line('薪水较低');

ELSIF v_sal < 10000 THEN

dbms_output.put_line('中等薪水');

ELSE

dbms_output.put_line('薪水很高');

ENDIF;

end;

set serveroutput on

declare

v_sal number;

begin

select salary into v_sal from employee_13 where employee_id = 2;

IFv_sal < 3000 THEN

update employee_13 set job_name='软件工程师';

commit;

ENDIF;

IFv_sal >6000 THEN

update employee_13 set job_name='软件架构师';

commit;

ENDIF;

end;

--简单循环

declare

v_number number:=1;

begin

loop

insert into test_table values('姓名'||v_number,v_number*10);

v_number:= v_number+1;

exit when v_number>10;

endloop;

end;

--for循环

set serveroutput on

declare

type t_number is table of number index by binary_integer;

v_1t_number;

v_total number;

begin

forv_count IN 1..10 LOOP

v_1(v_count):=v_count;

dbms_output.put_line(v_1(v_count));

endloop;

v_total := v_1.COUNT;

dbms_output.put_line(v_total);

end;

--for循环和if的结合使用

begin

forv_1 in 1..10 loop

insert into test_table values('陈龙'||v_1, v_1*10);

ifv_1 =5 then

exit;

end if;

endloop;

end;

--while循环

declare

v_1number:=1;

begin

while v_1 <=10 loop

insert into test_table values('draglong'||v_1, v_1+10);

v_1:= v_1+1;

endloop;

end;

--游标

--使用游标取得记录

set serveroutput on

declare

e_name employee_13.employee_name%type;

e_sal employee_13.salary%type;

e_job employee_13.job_name%type;

cursorcl is

select employee_name, salary, job_name from employee_13 where job_name='超人';

begin

open cl;

if cl%isopen then

loop

fetch cl into e_name, e_sal,e_job;

dbms_output.put_line(e_name);

dbms_output.put_line(e_sal);

dbms_output.put_line(e_job);

exit when cl%notfound;

end loop;

dbms_output.put_line('取得的游标记录数为: '||cl%rowcount);

close cl;

end if;

end;

--游标的复合类型变量的使用

set serveroutput on

declare

cursor cl is

select employee_name, salary, job_name from employee_13;

emp_record cl%rowtype;

begin

open cl;

loop

fetch cl into emp_record;

dbms_output.put_line(emp_record.employee_name);

dbms_output.put_line(emp_record.salary);

dbms_output.put_line(emp_record.job_name);

exit when cl%notfound;

end loop;

dbms_output.put_line('取得的游标记录数为: '||cl%rowcount);

close cl;

end;

--for循环取得游标记录

set serveroutput on

declare

v_number number;

cursor cl is

select employee_name, salary, job_name from employee_13;

begin

forv1 in cl loop

dbms_output.put_line(v1.employee_name);

dbms_output.put_line(v1.salary);

dbms_output.put_line(v1.job_name);

v_number:=v1.COUNT;

endloop;

--dbms_output.put_line('取得的游标记录数为: '||v_number);

end;

/**

*异常处理部分

--预定义异常

set serveroutput on

declare

e_name employee_13.employee_name%type;

e_sal number(8,2);

e_min_sal e_sal%type:= 1888;

begin

select employee_name, salary, salary/3 into e_name,e_sal,e_min_sal fromemployee_13 where salary >

2000;

dbms_output.put_line(e_name);

dbms_output.put_line(e_sal);

dbms_output.put_line(e_min_sal);

exception

when NO_DATA_FOUND THEN

dbms_output.put_line('没有符合条件的数据');

when TOO_MANY_ROWS THEN

dbms_output.put_line('数据库中存在多条记录，不符合查找的要求');

when others then

dbms_output.put_line('其他错误');

end;

--非预定义异常

set serveroutput on

declare

EXEXCEPTION;

pragma EXCEPTION_INIT(EX, -01400);

begin

insert into department_13(department_id)values(null);

exception

whenEX then

dbms_output.put_line('ora-1400 occurred 必须插入有效的部门编号');

end;

set serveroutput on

declare

EXEXCEPTION;

pragma EXCEPTION_INIT(EX, -2292);

begin

delete from department_13 where department_id = 10;

exception

when EX then

dbms_output.put_line('ora-2292 occurred 该条记录已经被其他字表参照');

end;

--用户自定义异常

set serveroutput on

declare

e_employee EXCEPTION;

v1number;

begin

select count(*) into v1 from employee_13 where department_id = 12;

dbms_output.put_line(v1);

ifv1 > 0 then

raise e_employee;

else

delete from department_13 where department_id = 12;

endif;

exception

when e_employee then

dbms_output.put_line('部门不能删除，因为部门存在员工');

end;

--创建错误日志表

create table log_table_error(codenumber(30), message varchar(200), info varchar(200));

--when others子句

declare

v_ErrorCode number;

v_ErrorMessage varchar(200);

v_CurrentUser varchar(8);

v_Information varchar(100);

v_name varchar(30);

begin

select employee_name into v_name from employee_13;

exception

when others then

v_ErrorCode := SQLCODE;

v_ErrorMessage:= SQLERRM;

v_CurrentUser:=USER;

v_information:='Error encountered on ' || to_char(sysdate) || 'bydatabase user ' || v_CurrentUser;

insert into log_table_error values(v_ErrorCode, v_ErrorMessage,v_Information);

end;

/**

*存储过程

--创建修改数据的存储过程

CREATE OR REPLACE PROCEDUREUpdateEmployeeSalary(

v_emp_id IN NUMBER, v_new_salary IN NUMBER)

BEGIN

UPDATE employee_13

SET salary=v_new_salary

WHERE employee_id = v_emp_id;

COMMIT;

END;

--创建追加数据的存储过程

CREATE OR REPLACE PROCEDURE AddDepartment(

dept_id department_13.department_id%type,

dept_name department_13.department_name%type,

manag_id department_13.manager_id%type,

location_id department_13.location_id%type)

BEGIN

INSERT INTO department_13 VALUES(dept_id, dept_name, manag_id,location_id);

COMMIT;

END;

--存储过程调用存储过程

CREATE OR REPLACE PROCEDUREprocess_updateSalary(v_emp_id INNUMBER, v_new_salary IN NUMBER)

BEGIN

UpdateEmployeeSalary(v_emp_id, v_new_salary);

END;

--创建函数

CREATE OR REPLACE FUNCTION tax_rate(v_valueIN NUMBER) RETURN NUMBER

BEGIN

IFv_value >1600 THEN

RETURN(v_value*0.11);

ELSE

RETURN(0);

ENDIF;

END tax_rate;

CREATE OR REPLACE FUNCTION rick_tax(r_testIN NUMBER)

return NUMBER

temp NUMBER;

begin

select salary into temp from employee_13_rick where employee_id =r_test;

IF temp > 1600 THEN

RETURN (temp*0.11);

ELSE

RETURN (0);

END IF;

END rick_tax;

CREATE OR REPLACE FUNCTION Select_salary(idIN NUMBER) RETURN NUMBER

v_salary NUMBER;

cursor cl is

SELECT SALARY FROM employee_13 WHEREemployee_id=id;

BEGIN

open cl;

loop

fetch cl into v_salary;

exit when cl%notfound;

endloop;

close cl;

return(v_salary*0.11);

END Select_salary;

--创建包

--创建包头

CREATE OR REPLACE PACKAGE dml_dept

PROCEDURE insert_dept(p_id number, p_name varchar, m_id number, l_idnumber);

PROCEDURE delete_dept(p_id number);

PROCEDURE update_dept(p_id number, m_id number);

FUNCTION select_manager(p_idnumber) RETURN NUMBER;

END dml_dept;

--创建包体

CREATE OR REPLACE PACKAGE BODY dml_dept

--插入数据的存储过程

PROCEDURE insert_dept(p_id number,p_name varchar,m_id number,l_idnumber)

v_1 number;

BEGIN

select count(*) into v_1 from department_13 where department_id = p_id;

if v_1 > 0 then

dbms_output.put_line('这个部门已经存在,不需要加入');

else

insert into department_13 values(p_id,p_name, m_id, l_id);

end if;

end;

--删除数据的存储过程

procedure delete_dept(p_id number)

v1 number;

begin

select count(*) into v1 from department_13 where department_id = p_id;

if v1 > 0 then

delete department_13 where department_id = p_id;

end if;

end;

--修改数据的存储过程

procedure update_dept(p_id number, m_id number)

v1number;

begin

update department_13 set manager_id = m_id where department_id = p_id;

end;

--查找数据的函数

function select_manager(p_id number) returnnumber

v1number;

begin

select department_id into v1 from department_13 where manager_id = p_id;

dbms_output.put_line('部门编号是：'|| v1);

return(v1);

end;

end dml_dept;

--在Oracle中建一个编号会自动增加的字段,以利于查询

　　1、建立序列：

　　CREATESEQUENCE checkup_no_seq

　　NOCYCLE

　　MAXVALUE9999999999

　　START WITH2;

　　2、建立触发器：

　　CREATE ORREPLACE TRIGGER set_checkup_no

　　BEFOREINSERT ON checkup_history

　　FOR EACH ROW

　　DECLARE

　　next_checkup_noNUMBER;

　　BEGIN

　　--Get thenext checkup number from the sequence

　　SELECTcheckup_no_seq.NEXTVAL

　　INTOnext_checkup_no

　　FROM dual;

　　--use thesequence number as the primary key

　　--for therecord being inserted

　　:new.checkup_no:= next_checkup_no;

　　END;

(1) 查询所有的记录  select * from scott.emp
(2) 查询所有记录的某些字段  select empno,ename,job from scott.emp
(3) 查询某些字段的不同记录  select distinct job from scott.emp

(4) 单条件的查询 select empno,ename,job from scott.emp where job='manager'

    其中等号可以换成其他运算符:

    != 不等于 select empno,ename,job from scott.emp where job!='manager'

    ^= 不等于 select empno,ename,job from scott.emp where job^='manager'

    <>不等于  select empno,ename,job from scott.emp where job<>'manager'

    <小于  select sal from scott.emp where sal<1000

    >大于 select sal from scott.emp where sal>1000

    <=小于等于 select sal from scott.emp where sal<=1000

    >=大于等于  select sal from scott.emp where sal>=1000

    in 在列表  select sal from scott.emp where sal in(1000,2000)

    not in 不在列表 select sal from scott.emp where sal not in(1000,2000)

    between...and 介于..与..间 select sal from scott.emp where sal  between 1000
    and 2000

    not between...and 不介于..与..之间  select sal from scott.emp where sal not
    between 1000 and 2000

    like 模式匹配  select ename from scott.emp where ename like 'M%'
    (%表示任意长度的长度串)

                  select ename from scott.emp where ename like 'M_'
                  (_表示一个任意的字符)

   is null 是否为空  select ename from scott.emp where ename is null

   is not null 不为空 select ename from scott.emp where ename is not null

    or(或)  select ename from scott.emp where ename='joke' or ename='jacky'

    and(与) select ename from scott.emp where ename='and' or ename='jacky'

    not(非) select ename from scott.emp where not ename='and' or ename='jacky'

(5)字段运算(+ - * /) select ename, sal,mgr,sal+mgr from scott.emp

(6)字段重命名 select ename 姓名,empno 工号, job 工作 from scott.emp

(7)无条件多表查询 select scott.ename,scott.job dept.dname,dept.loc from scott,dept;

等值多表查询 select scott.ename,scott.job dept.dname,dept.loc from scott,dept where scott.ename=dept.dname;

非等值多表查询 select scott.ename,scott.job dept.dname,dept.loc from scott,dept where scott.ename!=dept.dname and scott.job!='工人';

(8)嵌套查询(IN) select empno,ename,sal,job from scott.emp where sal in(select sal from scott.emp where ename='ward');

嵌套查询(ANY) select empno,ename,sal,job from scott.emp where sal > any(select sal from scott.emp where ename='ward');

嵌套查询(SOME) select emp.empno,emp.ename,emp.job,emp.sal from scott.emp where sal=some(select sal from scott.emp where job='manage');

嵌套查询(Exists) select emp.empno,emp.ename,emp.job,emp.sal from scott.emp,scott.dept where exists(select * from scott.emp where
scott.emp.deptno=scott.dept.deptno);

嵌套查询(Union)并集  (select deptno from scott.emp) union (select deptno from
scott.dept)

嵌套查询(intersect)交集  (select deptno from scott.emp) intersect (select deptno from scott.dept)

嵌套查询(minus)补集  (select deptno from scott.emp) minus (select deptno from scott.dept)

RowNUM 行号运用

(1) select RowNUM,ename from scott.emp;

(2) select ename,sal,from scott.emp where (RowNUM>3 and RowNUM<7) order by sal;

这些是基本的吧，似乎跟MS-SQL相差不大，个人感觉，Oracle重在管理与配置部分吧.先学到这

0 0