oracle学习笔记（六）-- 相关语法

下面将详细介绍SQL语句的相关语法：

1.     方法的创建（function）

CREATE [OR REPLACE] FUNCTION  function_name    [ (parameter [,parameter]) ]

    RETURN return_datatype

IS | AS

    [declaration_section]

BEGIN

    executable_section

[EXCEPTION

    exception_section]

END [function_name];

实例:

 CREATE OR REPLACE Function FindCourse   ( name_in IN varchar2 )   RETURN number

IS

cnumber number;

    cursor c1 is select course_number from courses_tbl where course_name = name_in;

BEGIN

open c1;

fetch c1 into cnumber;

if c1%notfound then

                cnumber := 9999;

end if;

close c1;

RETURN cnumber;

EXCEPTION

WHEN OTHERS THEN

   raise_application_error(-20001,'An error was encountered-'||SQLCODE||' -ERROR- '||SQLERRM);

END;

2.     过程创建（Procedure）

CREATE [OR REPLACE] PROCEDURE procedure_name    [ (parameter [,parameter]) ]

IS

    [declaration_section]

BEGIN

    executable_section

[EXCEPTION

    exception_section]

END [procedure_name];

实例:

CREATE OR REPLACE Procedure UpdateCourse   ( name_in IN varchar2 )

IS

    cnumber number;

    cursor c1 is select course_number from courses_tbl where course_name = name_in;

BEGIN

open c1;

fetch c1 into cnumber;

if c1%notfound then

                cnumber := 9999;

end if;

insert into student_courses ( course_name,course_number)values(name_in,                cnumber );

commit;

close c1;

EXCEPTION

WHEN OTHERS THEN

      raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);

END;

3.     触发器（Triggers）

Ø  Insert Triggers:

     BEFORE INSERT Trigger

AFTER INSERT Trigger

Ø  Update Triggers:

BEFORE UPDATE Trigger

AFTER UPDATE Trigger

Ø  Delete Triggers:

BEFORE DELETE Trigger

AFTER DELETE Trigger

Ø  Drop Triggers:

Drop a Trigger

Ø  Disable/Enable Triggers:

Disable a Trigger

Disable all Triggers on a table

Enable a Trigger

Enable all Triggers on a table

4.     Exception Handing

Named System Exceptions

Named Programmer-Defined Exceptions

WHEN OTHERS Clause

SQLCODE Function

SQLERRM Function

Oracle Error Messages

5.     Join

Ø  Inner Join

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers, orders WHERE suppliers.supplier_id = orders.supplier_id;

Search result :

supplier_id	name	order_date
10000	IBM	2003/05/12
10001	Hewlett Packard	2003/05/13

Ø  Out Join

select suppliers.supplier_id, suppliers.supplier_name, orders.order_date from suppliers, orders where suppliers.supplier_id = orders.supplier_id(+);

Search result :

supplier_id	supplier_name	order_date
10000	IBM	2003/05/12
10001	Hewlett Packard	2003/05/13
10002	Microsoft	<null>
10003	NVIDIA	<null>

6.     子查询（Subqueries）

Ø  WHERE clause

select * from all_tables tabs where tabs.table_name in (select cols.table_name   from all_tab_columns cols where cols.column_name = 'SUPPLIER_ID');

Ø  FROM clause

select suppliers.name, subquery1.total_amt from suppliers,

(select supplier_id, Sum(orders.amount) as total_amt   from orders   group by supplier_id)

subquery1,where subquery1.supplier_id = suppliers.supplier_id;

Ø  SELECT clause

select tbls.owner, tbls.table_name,

(select count(column_name) as total_columns   from all_tab_columns cols   where cols.owner = tbls.owner   and cols.table_name = tbls.table_name) subquery2

from all_tables tbls;

7.     联合查询

Ø  Union :集合的并，不包括重复行

select field1, field2, . field_n from tables UNION select field1, field2, . field_n from tables;

Ø  union all：集合的并，包括重复行

select field1, field2, .field_n from tables UNION ALL  select field1, field2, field_n from tables;

Ø  intersect ：集合的交，不包括重复行

select field1, field2, field_n from tables INTERSECT select field1, field2, field_n from tables;

Ø  minus：集合的差，不包括重复行

select field1, field2, field_n from tables MINUS select field1, field2, field_n from tables;

 

实例:

Id	Name	score
1	Aaron	78
2	Bill	76
3	Cindy	89
4	Damon	90
5	Ella	73
6	Frado	61
7	Gill	99
8	Hellen	56
9	Ivan	93
10	Jay	90

select * from student where id < 4 union select * from student where id > 2 and id < 6 的查询结果：

Id	Name	score
1	Aaron	78
2	Bill	76
3	Cindy	89
4	Damon	90
5	Ella	73

select * from student where id < 4 union All select * from student where id > 2 and id < 6的查询结果：

Id	Name	score
1	Aaron	78
2	Bill	76
3	Cindy	89
3	Cindy	89
4	Damon	90
5	Ella	73

select * from student where id < 4 intersect select * from student where id > 2 and id < 6的查询结果：

Id	Name	score
3	Cindy	89

select * from student where id < 4 minus select * from student where id > 2 and id < 6的查询结果：

Id	Name	score
1	Aaron	78
2	Bill	76
4	Damon	90
5	Ella	73