【Oracle學習筆記】,oracle學習筆記
內容主要包含:
(1)三種迴圈及其簡化
(2)遊標的使用
(3)異常處理
(4)預存程序
(5)儲存函數
(6)觸發器
(7)其他pl/sql操作
---------------loop迴圈定義變數--------------------
declare
cursor c1 is select * from emp;
#
rec emp%rowtype;
#
i numeber:=1;
#
v_count number;
begin
#
select count(*) into v_count from emp;
#(1)
open c1;
loop
fetch c1 into rec.EMPNO,rec.JOB;
insert into emp1(EMPNO,JOB) values(rec.EMPNO,rec.JOB)
i:=i+1;
exit when i>v_count;
end loop;
close c1;
end;
---------------loop迴圈--------------------
declare
cursor c1 is select * from emp;
#
rec emp%rowtype;
begin
#(1)
open c1;
loop
fetch c1 into rec.EMPNO,rec.JOB;
exit when c1%notfound;
insert into emp1(EMPNO,JOB) values(rec.EMPNO,rec.JOB)
end loop;
close c1;
end;
---------------loop迴圈簡化--------------------
declare
cursor c1 is select * from emp;
#
rec c1%rowtype;
begin
#(1)
open c1;
loop
fetch c1 into rec;
exit when c1%notfound;
insert into emp1 values rec
end loop;
close c1;
end;
---------------while迴圈--------------------
declare
cursor c1 is select * from emp;
#
rec emp%rowtype;
begin
open c1;
fetch c1 into rec.EMPNO,rec.JOB;
while c1%found loop
insert into emp1(EMPNO,JOB) values(rec.EMPNO,rec.JOB)
fetch c1 into rec.EMPNO,rec.JOB;
end loop;
close c1;
end;
---------------for迴圈,最簡潔,不易產生死迴圈,自己關閉--------------------
declare
#記錄集
cursor c1 is select * from emp;
#一行記錄
rec emp%rowtype;
begin
#for預設開啟遊標
for rec in c1 loop
insert into emp1(EMPNO,JOB) values(rec.EMPNO,rec.JOB)
end loop;
end;
---------------for迴圈,簡化--------------------
declare
#記錄集
cursor c1 is select * from emp;
begin
#for預設開啟遊標
#for隱含聲明rec,所以不用聲明了
for rec in c1 loop
insert into emp1(EMPNO,JOB) values(rec.EMPNO,rec.JOB)
end loop;
end;
---------------for迴圈,再簡化--------------------
declare
begin
#for預設開啟遊標
#for隱含聲明rec,所以不用聲明了
for rec in (select * from emp) loop
insert into emp1 values rec
end loop;
end;
---------------帶參數的遊標--------------------
declare
cursor c1(p_deptno number) is select * from emp;
#
rec emp%rowtype;
begin
#(1)
open c1(10);
loop
fetch c1 into rec.EMPNO,rec.JOB;
exit when c1%notfound;
insert into emp1(EMPNO,JOB) values(rec.EMPNO,rec.JOB)
end loop;
close c1;
end;
---------------列印--------------------
dbms_output.put_line(v_val);
---------------例外--------------------
Oracle預定義例外
Oracle錯誤
使用者定義的例外
---------------使用例外----------------
定義例外
捕獲例外
處理例外
EXCEPTION
WHEN TOO_MANY_ROWS THEN
NULL;
WHEN NO_DATA_FOUND THEN
NULL;
END;
---------------添加主鍵----------------
alter table
add constraint EMP_PK primary key;
---------------自訂例外--------------
DECLARE
MY_EXCEPTION EXCEPTION
PRAGMA EXCEPTION_INIT(MY_EXCEPTION,-00001);
BEGIN
INSERT INTO department() values();
EXCEPTION
WHEN MY_EXCEPTION THEN
#DBMS_OUTPUT.PUT_LINE('ERROR MESSAGES');
RAISE_APPLICATION_ERROR(-1111111,'STH');
END;
---------------定義變數--------------
v_1 data:=sysdata
---------------捕獲例外--------------
raise el;
DECLARE
v_ddrq data:=sysdata;
v_fhrq data:=sysdata-1;
MY_EXCEPTION EXCEPTION
BEGIN
INSERT INTO item(id,ddrq,fhrq) values(v_ddrq,v_fhrq);
IF v_ddrq>v_fhrq THEN
RAISE MY_EXCEPTION;
END IF;
EXCEPTION
WHEN MY_EXCEPTION THEN
#DBMS_OUTPUT.PUT_LINE('ERROR MESSAGES');
ROLLBACK;
RAISE_APPLICATION_ERROR(-1111111,'STH');
END;
---------------procedure--------------
CREATE or replace PROCEDURE get_sal(p_empno number)
is
v_sal emp.sal%type;
BEGIN
SELECT sal INTO v_sal FROM emp WHERE empno=p_empno;
DBMS_OUTPUT.PUTLINE(v_sal);
EDN;
sqlplus調用(每個語言有各自的調用方法)
execute get_sal(7269)
pl/sql調用
declare
begin
get_sal(7369);
end;
-----------過程與函數的區別------------
函數只有一個輸出
過程有多個
-----------過程,輸出參數------------
CREATE or replace PROCEDURE get_info
(p_empno number,
p_ename out varchar2,
p_deptno out number,
p_sal out emp.sal%type)
is
v_deptno emp.depyno%type;
v_ename emp.ename%type;
v_sal emp.sal%type;
BEGIN
SELECT sal,ename,deptno
INTO v_sal,v_ename,v_deptno
FROM emp
WHERE empno=p_empno;
p_ename:=v_ename;
p_deptno:=v_deptno;
p_sal:=v_sal;
EDN;
調用pl/sql
variable v_ename varchar2(20)
variable v_sal int;
variable v_deptno
execure get_info(7360,:v_ename,:v_deptno,:v_sal)
print v_sal; ...
-----------過程,混合參數------------
CREATE or replace PROCEDURE chang_no
(p_no in out varchar2)
is
BEGIN
p_no:=substr(p_no,1,3||'0'||substr(p_no,4));
EDN;
調用pl/sql
variable v_no varchar2(20)
declare
begin
:v_no:='1234567890';
execure get_no(:v_no)
print v_no;
-----------參數調用------------
順序
名字:形參 實參
variable v_name varchar2(20)
variable v_emptno
(p_name=>:v_name,p_empno=>:v_empno)
-----------儲存函數(可以在sql、pl/sql語句中調用,不能作為語句的一部分)------------
CREATE OR REPLACE FUNCTION add_sal(p_empno number)
RETURN number
IS
v_sal emp.sal%type;
BEGIN
select sal into v_sal from emp
where empno=p_empno;
RETURN v_sal*1.1;
END;
pl/sql調用
declare
v_sal:=emp.sal%type;
begin
v_sal:=add_sal(7369);
dbms_output.put_line(v_sal);
end;
sql調用
select ename,sal,add_sal(empno) from emp;
-----------觸發器------------
事前校正
事後審計
語句級
行級
DML
-----------語句級觸發器-----------
CREATE OR REPLACE TRIGGER trig1
BEFORE INSERT or UPDATE or DELETE ON emp
DECLARE
v_string varchar(20):="you can't option data";
BEGIN
IF(TO_CHAR(SYSYDATE,'DY') IN ('SAT','SUN')) OR (TO_CHAR(SYSDATE,'HH24:MI'))
NOT BETWEEN '08:00' AND '18:00')THEN
RAISE_APPLICATION_ERROE(-20500,v_error);
END IF;
END;
-----------行級觸發器-----------
CREATE OR REPLACE TRIGGER trig1
BEFORE INSERT or UPDATE or DELETE ON emp
FOR EACH ROW
WHEN (OLD.depto=10)
DECLARE
v_string varchar(20):="you can't option data";
BEGIN
IF(TO_CHAR(SYSYDATE,'DY') IN ('SAT','SUN')) OR (TO_CHAR(SYSDATE,'HH24:MI'))
NOT BETWEEN '08:00' AND '18:00')THEN
RAISE_APPLICATION_ERROE(-20500,v_error);
END IF;
END;
Oracle學習筆記txt全集下載
Oracle學習筆記 txt全集小說附件已上傳到百度網盤,點擊免費下載:
應該是全本了
Oracle學習筆記txt全集下載
Oracle學習筆記 txt全集小說附件已上傳到百度網盤,點擊免費下載:
應該是全本了