【Oracle學習筆記】

標籤：val   遊標   主鍵   into   procedure   in out   str   rom   get   

內容主要包括：

(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學習筆記】