ORACLE_NOTES

----------------------------------------------------
sql
----------------------------------------------------
website:http://sqlzoo.net/
◎-DDL
CREATE TABLESPACE/DATABASE/ROLE/PROFILE/USER
CREATE TABLE/VIEW/INDEX
CREATE PROCEDURE/FUNCTION/TRIGGER/PACKAGE(包头，包体)
DROP TABLE tablename
执行以上语句都是一个transaction

◎SELECT * FROM tab1 WHERE (...);/JOIN + SUBQUERY
DELETE ...
UPDATE ...
INSERT
◎groupby 后面可以添上having，因为聚集的使用

◎For each region show the region and number of countries:
select count(*) from cia group by region

For each region show the region and number of countries with populations of at least 10 million.
 select count(name),region from cia where population>= 10000000 group by region

Which country has a population that is more than Kenya but less than Canada?
 select * from cia where population>(select population from cia where name ='Kenya') and population<(select population from cia where name ='Canada')
Which countries have a GDP greater than any European country? [Give the name only.]
 select * from cia where gdp> (select max(gdp) from cia where region like 'Europe')
 select * from cia where gdp> all(select gdp from cia where region like 'Europe')
Find the region for which all countries have a population of 0
 select distinct region from cia x where (select sum(population) from cia y where x.region=y.region)=0
select sum(population),region from cia group by region having sum(population)=0

Some countries have populations more than three times that of any of their nieghbours (in the same region). Give the countries and regions.
 select region from cia x where population> (select max(3*population) from cia y where x.region=y.region and x.name<>y.name )
◎
procedure
function
trigger
(declare)
 cursor c_stu IS
 query;
var:
type;
...
begin
 open c_stu ;
 loop
  fetch c_stu into v1,v2,...
  exit when c_stu%notfound;
  ....
 end loop;
 close c_stu;
exception
 when ???
 ..
 others
 ....

end;

create or replace procedure proc()
as
begin
 type;
 var;
end proc;
----
create or replace function func() return type
is
 type;
 var;
begin
 ...
 return v;
exception
 ...
end func;
----
create or replace trigger trg
before update or delete or insert or tb_name
for eachrow
declare
 type;
 var;
begin

end trg;
----------
create or replace package pkg
is|as
 procedure proc1(...);
 procedure proc2(...);
 function func1(...);
 ...
end pkg;

create or replace package body pkg
is|as
 --define procdure
 procudre proc1()
 is|as
 begin

 end proc1;
 function func1(...) return type
 is|as
 begin
 ...
 end func1;
begin
 var:=...; 
end pkg;
◎pro*c所指定的sys_include是proc会去查找的路径
cpp_suffix=cc or cxx or cpp
-code=cpp
-parse=none or partial
◎EXEC SQL BEGIN DECLARE SECTION

 
◎show errors procedure myproc可以显示当前编译的错误，该语句存在数据库中
◎
◎
◎
◎
◎
◎
◎
◎
◎
◎
◎
◎
◎
◎
◎
◎
◎
◎
◎
◎
◎
◎
◎
◎
◎
◎
◎
◎
◎
◎
◎
◎
◎
◎
◎
----------------------------------------------------
pl/sql
----------------------------------------------------

----------------------------------------------------
pro*c
----------------------------------------------------