http://gzf_PC:1158/em
數據庫配置文件已經安裝到 D:\WorkC,同時其他選定的安裝組件也已經安裝到 D:\WorkC\oracle。
iSQL*Plus URL 為:
http://gzf_PC:5560/isqlplus
iSQL*Plus DBA URL 為:
http://gzf_PC:5560/isqlplus/dba
-- 創建表
drop table test;
create table test(
Id int,
UName varchar2(20),
constraint ucon unique(Id)
);
-- 創建的同時 指定主鍵值；
create table cdpt(
id number(6),
name varchar2(30),
constraint pk_id primary key(id)
);
insert into test values(1,'郭澤峰');
select * from test;
-- 復制表和數據(不復制表約束)
create table testback as select * from test;
-- 復制表結構
drop table testback;
create table testback as select * from test where 1=2;
-- 創建索引
create index i_1 on test(uname asc);
-- 給表或視圖取一個別名
create synonym testother for test;
-- 添加列
alter table test add (other int)
-- 刪除列
alter table test modify (other varchar2(50))
alter table test drop (other)
-- 修改表名
rename test11 to test
-- 添加外鍵約束
Alter table emp add constraint S_F Foreign key (deptno) references dept(deptno);
--
/*
5 .對已經存在的表添加約束
ALTER TABLE <table_name> ADD CONSTRAINT <constraint_name> 約束類型 (針對的字段名);
示例：
Alter table emp add constraint S_F Foreign key (deptno) references dept(deptno);

6 .對表里的約束禁用;
ALTER TABLE <table_name> DISABLE CONSTRAINT <constraint_name>;

7 .對表里的約束重新啟用;
ALTER TABLE <table_name> ENABLE CONSTRAINT <constraint_name>;

8 .刪除表中約束
ALTER TABLE <table_name> DROP CONSTRAINT <constraint_name>;
示例:
ALTER TABLE emp drop CONSTRAINT <Primary key>;

*/
-- 刪除
drop table aa
drop index aa
drop synonym aa -- 刪除別名
-- 刪除
SELECT [DISTINCT] <column1 [as new name] ,columns2,...>
FROM <table1>
[WHERE <條件>]
[GROUP BY <column_list>]
[HAVING <條件>]
[ORDER BY <column_list> [ASC|DESC]]
--
select * from emp where not deptno=10;
select * from emp where empno in(7788,7369,7499);
select * from emp where sal between 1000 and 3000;
select * from emp where ename like '_C%';
--
select * from emp where sal=(select max(sal) from emp);
--找出比deptno=30的員工最低工資高的其他部門的員工
select * from emp where sal>ANY(select sal from emp where deptno=30) and deptno<>30;
-- 找出比deptno=30的員工最高工資高的其他部門的員工
select * from emp where sal>ALL(select sal from emp where deptno=30) and deptno<>30;
-- 兩個表鏈接
select empno,ename,dname from emp,dept where emp.deptno=dept.deptno;
--
select * from emp where rownum<=10;--查詢前10行記錄
--注意ROWNUM只能為1 因此不能寫 select * from emp where rownum between 20 and 30;
--
select * from emp where rownum<=3 and empno not in (select empno from emp where rownum<=3);
---結果可以返回整個數據的3-6行;
-- oracle分頁查詢：效率最低
select * from (select t.*,row_number() over(order by Id desc) rk from test t) where rk<5 and rk>2;
--另一種分頁效率稍微高[必須先小于后大于]
select * from(select t.*,rownum rn from(select * from test order by Id asc) t where rownum<7) where rn>5;
-- 效率比較低
SELECT b.* FROM (SELECT A.*, ROWNUM RN FROM (SELECT * FROM test order by Id) A ) b WHERE b.RN BETWEEN 4 AND 5
--

SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM test) A
WHERE ROWNUM <= 5
)
WHERE RN >= 3
-- 創建視圖
drop view testView
create view testView
as
select Id from test
-- 創建存儲過程 (修改 or replace)
create procedure guo(
a int,
)
as
begin
select a;
end

/*
Create 創建
Sequence 序列 seqEmop 序列名稱
Increment by 步長
Stat with 1 開始值
Maxvalue 最大值
Minvalue 最小值
*/
-- 創建序列：

Create sequence seq_cdpt
Increment by 1
Start with 1
Maxvalue 999999
Minvalue 1
Nocycle
nocache;

-- 修改序列
Alter sequence seqEmp maxvalue 5;
-- 查詢序列
Select seq_cdpt.nextval from dual;
Select seq_cdpt.currval from dual;

-- 使用序列
insert into test(Id) values(444);
insert into test(Id) values(seq_cdpt.nextVal);
commit;-- 必須有commit;
select * from user_indexes where table='test' ;

-- 查看數據及log
select name,log_mode from v$database;
-- 查看實例名字
select instance_name,status from v$instance;
--
select tablespace_name,status,extent_management from dba_tablespaces;
--查看控制文件位置
select name from v$controlfile;
-- 查看數據文件的位置和大小
select name,bytes from v$datafile;
--
SELECT VIEW_NAME FROM USER_VIEWS;
--
SELECT * FROM all_views t where view_name='testview'
--與
select object_name from user_objects where object_type='VIEW'
-- 查看視圖內容
select text from user_views where view_name=upper('testview');
-- 變量聲明：
declare
guo nvarchar2(50);
begin
guo:='234';
dbms_output.put_line(guo);
end;
-- 查看oracle安裝平臺：
select * from v$version;
-- 查看控制文件：
select name from v$controlfile;
-- 查看日志文件
select member from v$logfile;
-- 查看數據庫對象：
select owner, object_type, status, count(*) count# from all_objects group by owner, object_type, status;
-- 查看數據庫版本：
Select version FROM Product_component_version Where SUBSTR(PRODUCT,1,6)='Oracle';
-- 查看數據庫日期和歸檔方式
Select Created, Log_Mode, Log_Mode From V$Database;
-- 創建用戶：
CREATE USER <user_name> [profile "DEFAULT"]
identified by "<password>" [default tablespace "USERS"]
-- 刪除用戶：
DROP USER <user_name> CASCADE
-- 創建角色：
CREATE ROLE <role_name>
identified by "<password>"
-- 刪除角色
DROP ROLE <role_name>
--
select * from user_tables
--
--修改密碼
alter user gzf identified by gzf;
-- 查詢查看當前用戶擁有的角色權限信息
select * from role_sys_privs;
-- 查詢當前用戶
select * from user_users;
-- 查看當前用戶的角色信息
select * from user_role_privs;
-- 刪除權限：
revoke resource from gzf;
--授予用戶權限：
grant connect,resource to gzf;
grant select on scott.Test to gzf;
--刪除權限：
revoke select on scott.Test from gzf;
--查詢當前時間
select sysdate from dual
/*
boolean:true,false,null;用number(1)來標示；
*/
-- 變量聲明：如果錯誤的話，將用exception輸出；
declare guo int:=123123123;
res varchar2(50);
begin
--輸出：
guo:=2*2+3/0;
-- set serveroutput on;
res:=To_Char(guo);
dbms_output.put_line(guo);
exception
when others then
dbms_output.put_line('error');
-- commit;
end;
-- 類型在轉換
select to_char(12) from dual;
select to_date('2005-01-01 13:14:20','yyyy-MM-dd HH24:mi:ss') from dual;
select to_date('2005-01-03','yyyy-dd-MM') from dual;
select to_number('123') as cc from dual;
-- 打印變量 -- number也存儲小數，所以整形，建議用binary_integer;
declare a number:=123.12;
b nvarchar2(10);
c boolean:=true;
begin
b:=to_char(a);
dbms_output.put_line(b);
exception
-- 啥意思？
when others then
dbms_output.put_line('error');
end;
--
--number:如果第二個參數是負數的話，說明四舍五入整數位，123.123 （5,-2）結果：100
declare a number(5,-5):=123.23;
begin
dbms_output.put_line(a);
end;
--多變量聲明
declare a int;b int;
begin
a:=123;b:=234;
dbms_output.put_line(a);
end;
-- record類型：v與v1值可以互換；
declare
type guo_type is record(
u_Id int,
u_Name varchar2(50)
);
v guo_type;
v1 guo_type;
begin
v.u_Id:=123;
v.u_Name:='sdfsdf';
dbms_output.put_line(v.u_Name);
end;
-- table類型：相當于數組，可以存儲record類型
declare
type ta is table of varchar2(50) index by binary_integer;
va ta;
begin
va(100):='gzf0';
va(101):='gzf1';
va(102):='gzf2';
dbms_output.put_line('索引為100的數據記錄va(100)：'||va(100));
dbms_output.put_line('第一條記記錄va.first：'||va(va.first));
dbms_output.put_line('最后一條記錄va.last：'||va(va.last));
dbms_output.put_line('第二條記錄的前一條記錄va.prior：'||va(va.prior(101)));-- 101是索引
dbms_output.put_line('第二條記錄的后一條記錄'||va(va.next(101)));
dbms_output.put_line('總記錄va.count：'||to_char(va.count));
end;
-- 存儲record類型：
declare
type re is record(id varchar2(50));
type ta is table of re index by binary_integer;
va ta;
begin
va(100).id:='gzf123';
dbms_output.put_line(va(100).id);
end;
-- 定義多維度表變量:好像只能填充一條數據：
declare
type tt is table of test%rowtype index by binary_integer;
t tt;
begin
select * BULK COLLECT into t from test where id>0;
for aa in 1..t.count loop
dbms_output.put_line(t(aa).uname);
end loop;
end;
select * from test;
-- 設置類型為表中某個字段類型,這樣一旦表類型變化，變量類型也跟著變化；
declare
guo test.ID%type;
begin
guo:=123;
dbms_output.put_line(guo);
end;
-- 行類型：
declare
guo test%rowtype;
begin
select * into guo from test where id=1;
dbms_output.put_line(guo.UNAME||'sfsdf');
end;
--控制語句：if elsif else end if;
declare
a int:=25;
begin
if a>0 and a<10 then
dbms_output.put_line('1');
elsif a>20 and a<40 then
dbms_output.put_line('2');
else
dbms_output.put_line('3');
end if;
end;
--loop: if then exit;也可以，exit when ;也可以
declare
guo int:=0;
begin
loop
guo:=guo+1;
dbms_output.put_line(guo);
if guo=10 then
exit;
end if;
end loop;
end;
-- 或
declare
guo int:=0;
begin
loop
guo:=guo+1;
dbms_output.put_line(guo);
exit when guo=5;
end loop;
end;
-- while循環
declare
guo int:=0;
begin
while guo<10 loop
guo:=guo+1;
dbms_output.put_line(guo);
end loop;
end;
-- for循環:
declare
guo int:=0;
begin
for a in 1..10 loop
guo:=guo+1;
dbms_output.put_line(guo);
end loop;
end;
--異常
begin
dbms_output.put_line(1/0);
exception
when others then
dbms_output.put_line('error');
end;
-- 游標
declare
t test%rowtype;
cursor c is select * from test where id>0;
begin
open c;
loop
fetch c into t;
dbms_output.put_line(t.UName);
exit when c%notfound;
end loop;
close c;
end;
-- 存儲過程：out在類型前面；---像varchar2千萬不要加長度否則會報錯...好變態啊...
create or replace procedure guo11(
iid int,
uname out varchar2
) is
begin
uname:='123rrrr';
end guo11;
--
create or replace procedure guo12(
iid int
)
is
begin
dbms_output.put_line(iid);
end guo12;
--
create or replace procedure guo13
is
begin
dbms_output.put_line(123);
end;
--創建包：--------一些函數或存儲過程的包頭；

--創建包頭

create or replace package package_demo is

function Getage(birthst varchar,birthend varchar) return integer;

function Getsalary(VFpsncode varchar) return number;

end package_demo;

--創建包體

create or replace package body package_demo is

function Getage(birthst varchar,birthend varchar) return integer --得到年齡函數

is

V_birth integer;

ToDateEnd Date;

Toyear number(4);

Tomonth number(4);

Fromyear number(4);

Frommonth number(4);

begin

if (birthend='') or (birthend is null) then

select sysdate into ToDateEnd from dual; --得到系統時間

end if;

Toyear := to_number(to_char(ToDateEnd,'YYYY')); --得到最后年月

Tomonth := to_number(to_char(ToDateEnd,'MM'));

Fromyear := to_number(substr(birthst,1,4));--計算的年月

Frommonth := to_number(substr(birthst,6,2));

if Tomonth-Frommonth>0 then V_birth:=Toyear-fromyear;

else V_birth:=Toyear-fromyear-1;

end if;

return(V_birth);

end Getage;

function getSalary(VFpsncode varchar) return number--返回工資情況
is
V_psnSalary number(8,2);
begin
Select 2 into V_psnSalary from dual;

return(V_psnSalary);

end getSalary;

end package_demo;

-- 調用包體：
select package_demo.getsalary('C001') from dual;
-- 存儲過程返回結果集；********************************************************存儲過程**************************
----------------------聲明一個Package--------------
CREATE OR REPLACE PACKAGE pkg_test
AS
TYPE myrctype IS REF CURSOR;
PROCEDURE get_r(p_id NUMBER,p_rc OUT myrctype); --Package中聲明名為get 的Procedure(只有接口沒內容)
END pkg_test;
---聲明包體
CREATE OR REPLACE PACKAGE BODY pkg_test
AS
PROCEDURE get_r(p_id NUMBER,p_rc OUT myrctype)
IS
sqlstr VARCHAR2 (500);
BEGIN
IF p_id = 0 THEN
OPEN p_rc FOR
SELECT * FROM test;
ELSE
sqlstr :=
'select *
from test where id=:w_id'; --w_id是個參數，
--以下 p_rc是個REF CURSOR游標類型，而且是OUT型參數，即可返回一個記錄集了。USING p_id就是替換上面SQL中:w_id值拉:)
OPEN p_rc FOR sqlstr USING p_id;　
END IF;
END get;
END pkg_test;
--簡單存儲過程。。。。。。。。。。。。。。
CREATE OR REPLACE PROCEDURE getcity (
citycode IN int,
ref_cursor OUT sys_refcursor /* 這個sys_refcursor類型在SYS.STANDARD包中 */
)
AS
BEGIN
OPEN ref_cursor FOR
SELECT *
FROM test
WHERE id != citycode;
END;

-- 調用存儲過程
declare
t test%rowtype;
cur sys_refcursor;
begin
getcity(1,cur);
-- open cur; --這里游標已經打開，所以不用繼續打開
loop
fetch cur into t;
dbms_output.put_line(t.UNAME);
exit when cur%notfound;
end loop;
close cur;
end;



-- 存儲過程：參數可以缺省;
/*
create procedure guo(
iid int,
uname varchar,
)
--執行存儲過程（默認缺省）
begin
guo();
end
制定一個參數：
begin
guo(uname=>'cheng');
end
*/
begin
guo11();
end;
-- 執行：
declare
uname varchar2(20);
iid int:=123;
begin
guo11(iid,uname);
dbms_output.put_line(uname);
end;
--執行
-- 授予權限...
GRANT SELECT ANY TABLE TO scott WITH ADMIN OPTION;
-- 函數：創建函數：
create or replace function fun(vaName in varchar2)
return varchar2
is
begin
return '111'||vaName;
end;

-- 調用函數：
declare
myresult varchar2(20);
begin
myresult:=fun('hhhh');
dbms_output.put_line(myresult);
end;
-- 打印帶out參數的結果：
create or replace function fun1(vaName in varchar2,vaout out varchar2)
return varchar2
is
begin
vaout:='gzf000';
return '111'||vaName;
end;
-- 查詢結果：
declare
one varchar2(20);
two varchar2(20);
begin
one:=fun1('fff',two);
dbms_output.put_line(one);
dbms_output.put_line(two);
end;
-- 創建自增列...
Oracle沒有自增字段這樣的功能，但是通過觸發器(trigger)和序列(sequence)可以實現。

先建一個測試表了：

create table userlogin
(

id number(6) not null,

name varchar2(30) not null primary key

)
tablespace users
/
第一步：創建SEQUENCE
create sequence userlogin_seq increment by 1 start with 1 minvalue 1 maxvalue 9999999999999 nocache order;
第二步：創建一個基于該表的before insert 觸發器，在觸發器中使用剛創建的SEQUENCE
create or replace trigger userlogin_trigger
before insert on userlogin
for each row
begin
select userlogin_seq.nextval into:new.id from sys.dual ;
end;

-- 創建觸發器：
例1: 建立一個觸發器, 當職工表 emp 表被刪除一條記錄時，把被刪除記錄寫到職工表刪除日志表中去。

CREATE TABLE emp_his AS SELECT * FROM EMP WHERE 1=2;
CREATE OR REPLACE TRIGGER tr_del_emp
BEFORE DELETE --指定觸發時機為刪除操作前觸發
ON scott.emp
FOR EACH ROW --說明創建的是行級觸發器
BEGIN
--將修改前數據插入到日志記錄表 del_emp ,以供監督使用。
INSERT INTO emp_his(deptno , empno, ename , job ,mgr , sal , comm , hiredate )
VALUES( :old.deptno, :old.empno, :old.ename , :old.job,:old.mgr, :old.sal, :old.comm, :old.hiredate );
END;
DELETE emp WHERE empno=7788;
DROP TABLE emp_his;
DROP TRIGGER del_emp;

例2：限制對Departments表修改（包括INSERT,DELETE,UPDATE）的時間范圍，即不允許在非工作時間修改departments表。

CREATE OR REPLACE TRIGGER tr_dept_time
BEFORE INSERT OR DELETE OR UPDATE
ON departments
BEGIN
IF (TO_CHAR(sysdate,'DAY') IN ('星期六', '星期日')) OR (TO_CHAR(sysdate, 'HH24:MI') NOT BETWEEN '08:30' AND '18:00') THEN
RAISE_APPLICATION_ERROR(-20001, '不是上班時間，不能修改departments表');
END IF;
END;

例3：限定只對部門號為80的記錄進行行觸發器操作。

CREATE OR REPLACE TRIGGER tr_emp_sal_comm
BEFORE UPDATE OF salary, commission_pct
OR DELETE
ON HR.employees
FOR EACH ROW
WHEN (old.department_id = 80)
BEGIN
CASE
WHEN UPDATING ('salary') THEN
IF :NEW.salary < :old.salary THEN

RAISE_APPLICATION_ERROR(-20001, '部門80的人員的工資不能降');
END IF;
WHEN UPDATING ('commission_pct') THEN

IF :NEW.commission_pct < :old.commission_pct THEN
RAISE_APPLICATION_ERROR(-20002, '部門80的人員的獎金不能降');
END IF;
WHEN DELETING THEN
RAISE_APPLICATION_ERROR(-20003, '不能刪除部門80的人員記錄');
END CASE;
END;

/*
實例：
UPDATE employees SET salary = 8000 WHERE employee_id = 177;
DELETE FROM employees WHERE employee_id in (177,170);
*/

例4：利用行觸發器實現級聯更新。在修改了主表regions中的region_id之后（AFTER），級聯的、
自動的更新子表countries表中原來在該地區的國家的region_id。


CREATE OR REPLACE TRIGGER tr_reg_cou
AFTER update OF region_id
ON regions
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('舊的region_id值是'||:old.region_id
||'、新的region_id值是'||:new.region_id);
UPDATE countries SET region_id = :new.region_id
WHERE region_id = :old.region_id;
END;
例5：在觸發器中調用過程。

CREATE OR REPLACE PROCEDURE add_job_history
( p_emp_id job_history.employee_id%type
, p_start_date job_history.start_date%type
, p_end_date job_history.end_date%type
, p_job_id job_history.job_id%type
, p_department_id job_history.department_id%type
)
IS
BEGIN
INSERT INTO job_history (employee_id, start_date, end_date,
job_id, department_id)
VALUES(p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id);
END add_job_history;

--創建觸發器調用存儲過程...
CREATE OR REPLACE TRIGGER update_job_history
AFTER UPDATE OF job_id, department_id ON employees
FOR EACH ROW
BEGIN
add_job_history(:old.employee_id, :old.hire_date, sysdate,
:old.job_id, :old.department_id);
END;

8.2.3 創建替代(INSTEAD OF)觸發器

創建觸發器的一般語法是:

CREATE [OR REPLACE] TRIGGER trigger_name
INSTEAD OF
{INSERT | DELETE | UPDATE [OF column [, column …]]}
[OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...]
ON [schema.] view_name --只能定義在視圖上
[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]
[FOR EACH ROW ] --因為INSTEAD OF觸發器只能在行級上觸發,所以沒有必要指定
[WHEN condition]
PL/SQL_block | CALL procedure_name;
--- 變量還可以既取值又賦值...
/*
create or replace function fun1(vaName in out varchar2)
*/
-- 存儲過程可以參數缺省值：為參數指定默認參數...
-- 表復制：

-- 子查詢...
--select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b
-- 外聯
-- select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
--
-- delete from info where not exists(select * from infobz where info.infid=infobz.infid )

Oracle匯總筆記