--定義變數，迴圈向test_tab寫資料

declare

v_count number(2) := 0;

begin

--execute immediate 'create table test_tab (id int)';

loop

insert into test_tab values (v_count);

v_count := v_count +1;

exit when (v_count>=10);

end loop;

commit;

end;

--使用for迴圈向test_tab寫資料

declare

v_count number(2) := 0;

begin

for cn in 10..20 loop

insert into test_tab values (cn);

end loop;

commit;

end;

--使用for迴圈從emp表中取資料

declare

v_count number(2) := 0;

begin

for item in (select * from emp) loop

dbms_output.put_line(item.ename||' '||item.empno||' '||SQL%ROWCOUNT||'-');

end loop;

end;

--While迴圈

declare

v_count number(2) := 0;

begin

while v_count<10 loop

dbms_output.put_line('v_count='||v_count);

v_count := v_count + 1;

end loop;

end;

--迴圈跳轉

declare

total_done varchar2(20) :='YES';

inner_done varchar2(20) :='YES';

v_counter int := 0;

BEGIN

<>

LOOP

v_counter := v_counter+1;

EXIT WHEN v_counter>10;

<>

LOOP

EXIT Outer_loop WHEN total_done = 'NO';

-- Leave both loops

EXIT WHEN inner_done = 'YES';

-- Leave inner loop only

END LOOP Inner_loop;

dbms_output.put_line('exit inner loop body');

END LOOP Outer_loop;

dbms_output.put_line('exit outer loop body');

END;

--定義記錄型別

declare

TYPE emp_record_type IS RECORD

(ename VARCHAR2(10),

job VARCHAR2(9),

sal NUMBER(7,2));

emp_record emp_record_type;

begin

select ename,job,sal into emp_record from emp where rownum=1;

dbms_output.put_line(emp_record.ename||' '||emp_record.job);

end;

--定義EMP表記錄型別

--定義EMP.ENAME欄位型別

declare

emp_record emp%rowtype;

emp_name emp.ename%type;

begin

select * into emp_record from emp where rownum=1;

dbms_output.put_line(emp_record.ename||' '||emp_record.job);

end;

--PL/SQL記憶體表

DECLARE

TYPE ename_table_type IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER;

TYPE hiredate_table_type IS TABLE OF DATE INDEX BY BINARY_INTEGER;

ename_table ename_table_type;

hiredate_table hiredate_table_type;

BEGIN

ename_table(1) := 'CAMERON';

ename_table(2) := 'KING';

hiredate_table(8) := SYSDATE + 7;

IF ename_table.EXISTS(1) THEN

dbms_output.put_line('First element is '||ename_table(2)||' hiredate='||hiredate_table(8));

end if;

END;

--定義遊標，使用LOOP迴圈讀取資料

declare

cursor emp_cur is select empno,ename from emp;

v_empno emp.empno%type;

v_ename emp.ename%type;

begin

if not emp_cur%ISOPEN then

open emp_cur;

end if;

loop

fetch emp_cur into v_empno,v_ename;

dbms_output.put_line('empno='||v_empno||',ename='||v_ename);

exit when emp_cur%NOTFOUND;

end loop;

end;

--定義遊標，遊標記錄型別，LOOP讀取資料

declare

cursor emp_cur is select empno,ename from emp;

rec_empno emp_cur%rowtype;

begin

if not emp_cur%ISOPEN then

open emp_cur;

end if;

loop

fetch emp_cur into rec_empno;

dbms_output.put_line('empno='||rec_empno.empno||',ename='||rec_empno.ename);

exit when emp_cur%NOTFOUND;

end loop;

end;

--使用FOR操作遊標資料

declare

cursor emp_cur is select empno,ename from emp;

begin

for rec_empno in emp_cur

loop

dbms_output.put_line('empno='||rec_empno.empno||',ename='||rec_empno.ename);

end loop;

end;

--使用sys_refcursor引用型別定義遊標

declare

my_cur sys_refcursor;

tname tab%rowtype;

begin

open my_cur for 'select * from tab';

loop

fetch my_cur into tname;

exit when my_cur%notfound;

dbms_output.put_line('TName = '||tname.tname);

end loop;

close my_cur;

exception

when NO_DATA_FOUND then

dbms_output.put_line('No data');

when OTHERS then

dbms_output.put_line('Other');

end;

--使用FOR操作隱式遊標

begin

for item in (select empno,ename from emp)

loop

dbms_output.put_line(item.empno||' '||item.ename);

end loop;

end;

--使用FOR操作遊標資料

declare

cursor c1 is select empno,ename from emp;

begin

for item in c1

loop

dbms_output.put_line(item.empno||' '||item.ename);

end loop;

end;

/

--定義引用遊標型別

declare

type emp_cur is ref cursor return emp%rowtype;

my_cur emp_cur;

my_emp emp%rowtype;

begin

if not my_cur%ISOPEN then

open my_cur for select * from emp;

end if;

loop

fetch my_cur into my_emp;

exit when my_cur%notfound;

dbms_output.put_line(my_emp.empno||' '||my_emp.ename);

end loop;

close my_cur;

end;

--Exception處理

declare

deptno number(2);

dname varchar2(14);

loc varchar2(14);

begin

select deptno,dname,loc into deptno,dname,loc from dept where deptno=10;

insert into dept values (deptno,dname,loc);

exception

when DUP_VAL_ON_INDEX THEN

rollback;

dbms_output.put_line('Rollback transaction......');

end;

--PL/SQL變數定義

declare

c_val constant number(2) := 10;

c_val2 int;

c_val3 c_val2%type;

begin

--c_val := 11;

dbms_output.put_line('-----------');

end;

--TURE/NULL/FALSE判斷真假

declare

b_val1 boolean := true;

b_val2 boolean := null;

begin

if b_val1 and b_val2 then

dbms_output.put_line('true');

else

dbms_output.put_line('false');

end if;

end;

--迴圈標誌及跳轉

declare

total_done varchar2(20) :='YES';

inner_done varchar2(20) :='YES';

v_counter int := 0;

BEGIN

<>

LOOP

v_counter := v_counter+1;

EXIT WHEN v_counter>10;

<>

LOOP

EXIT Outer_loop WHEN total_done = 'YES';

-- Leave both loops

EXIT WHEN inner_done = 'YES';

-- Leave inner loop only

END LOOP Inner_loop;

END LOOP Outer_loop;

END;

--定義Record型別

declare

TYPE emp_record_type IS RECORD

(ename VARCHAR2(10),

job VARCHAR2(9),

sal NUMBER(7,2));

emp_record emp_record_type;

begin

dbms_output.put_line('record type');

end;

--PL/SQL記憶體表

DECLARE

TYPE e_table_type IS TABLE OF emp.Ename%Type

INDEX BY BINARY_INTEGER;

e_tab e_table_type;

BEGIN

e_tab(1) := 'SMITH';

UPDATE emp

SET sal = 1.1 * sal

WHERE Ename = e_tab(1);

COMMIT;

END;

/

--遊標定義

DECLARE

CURSOR emp_cursor

(p_deptno NUMBER, p_job VARCHAR2) IS

SELECT empno, ename

FROM emp

WHERE deptno = p_deptno

AND job = p_job;

v_empno number(20);

v_ename varchar2(30);

BEGIN

OPEN emp_cursor(10, 'CLERK');

loop

fetch emp_cursor into v_empno,v_ename;

exit when emp_cursor%notfound;

dbms_output.put_line('empno='||v_empno||',ename='||v_ename);

end loop;

END;

/

--FOR操作遊標

DECLARE

CURSOR sal_cursor IS

SELECT sal

FROM emp

WHERE deptno = 30

FOR UPDATE OF sal NOWAIT;

BEGIN

FOR emp_record IN sal_cursor LOOP

UPDATE emp

SET sal = emp_record.sal * 1.10

WHERE CURRENT OF sal_cursor;

END LOOP;

COMMIT;

END;

--非預定義異常

DECLARE

e_emps_remaining EXCEPTION;

PRAGMA EXCEPTION_INIT (

e_emps_remaining, -2292);

v_deptno dept.deptno%TYPE := &p_deptno;

BEGIN

DELETE FROM dept

WHERE deptno = v_deptno;

COMMIT;

EXCEPTION

WHEN e_emps_remaining THEN

DBMS_OUTPUT.PUT_LINE ('Cannot remove dept ' ||

TO_CHAR(v_deptno) || '. Employees exist. ');

END;

--使用者定義異常

DECLARE

e_invalid_dept EXCEPTION;

BEGIN

UPDATE dept

SET dname = 'TestDept'

WHERE deptno = &v_deptno;

IF SQL%NOTFOUND THEN

RAISE e_invalid_dept;

END IF;

COMMIT;

EXCEPTION

WHEN e_invalid_dept THEN

DBMS_OUTPUT.PUT_LINE('Invalid dept number.');

DBMS_OUTPUT.PUT_LINE('SQLCODE='||SQLCODE||' SQLERRM='||SQLERRM);

END;

呼叫DBMS_STANDARD(ORACLE提供的包)包所定義的RAISE_APPLICATION_ERROR過程，可以重新定義異常錯誤訊息，它為應用程式提供了一種與ORACLE互動的方法：

建立錯誤程式碼表：

CREATE TABLE errlog(

Errcode NUMBER,

Errtext CHAR(40));

建立函式：

CREATE OR REPLACE FUNCTION get_salary (p_deptno NUMBER)

RETURN NUMBER AS

V_sal NUMBER;

BEGIN

IF p_deptno IS NULL THEN

RAISE_APPLICATION_ERROR(-20991,'Department number is null');

ELSIF p_deptno<0 THEN

RAISE_APPLICATION_ERROR(-20992,'Invalide department number');

ELSE

SELECT SUM(sal) INTO v_sal FROM EMP WHERE deptno=p_deptno;

RETURN V_sal;

END IF;

END;

呼叫函式：

DECLARE

V_salary NUMBER(7,2);

V_sqlcode NUMBER;

V_sqlerr VARCHAR2(512);

Null_deptno EXCEPTION;

Invalid_deptno EXCEPTION;

PRAGMA EXCEPTION_INIT(null_deptno,-20991);

PRAGMA EXCEPTION_INIT(invalid_deptno, -20992);

BEGIN

V_salary :=get_salary(10);

DBMS_OUTPUT.PUT_LINE('10 department salary is: '||TO_CHAR(V_salary));

BEGIN

V_salary :=get_salary(-10);

EXCEPTION

WHEN invalid_deptno THEN

V_sqlcode :=SQLCODE;

V_sqlerr :=SQLERRM;

INSERT INTO errlog(errcode, errtext) VALUES(v_sqlcode, v_sqlerr);

COMMIT;

END inner1;

V_salary :=get_salary(20);

DBMS_OUTPUT.PUT_LINE('20department salary is: '||TO_CHAR(V_salary));

BEGIN

V_salary :=get_salary(NULL);

END inner2;

V_salary :=get_salary(30);

DBMS_OUTPUT.PUT_LINE('30department salary is: '||TO_CHAR(V_salary));

EXCEPTION

WHEN null_deptno THEN

V_sqlcode :=SQLCODE;

V_sqlerr :=SQLERRM;

INSERT INTO errlog(errcode, errtext) VALUES(v_sqlcode, v_sqlerr);

COMMIT;

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('Other errores!');

END outer;

create or replace PROCEDURE Get_emp_rec (Emp_number IN Emp.empno%TYPE,

Emp_ret OUT emp%ROWTYPE) IS

BEGIN

SELECT * INTO Emp_ret

FROM emp WHERE Empno = Emp_number;

END;

declare

emp_number int := 7900;

emp_ret emp%rowtype;

begin

get_emp_rec(emp_number,emp_ret);

dbms_output.put_line(emp_ret.ename);

end;

--觸發器

create table emp_his as select * from emp;

CREATE OR REPLACE TRIGGER del_emp

BEFORE DELETE OR UPDATE ON scott.emp FOR EACH ROW

BEGIN

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;

--檢視觸發器

CREATE OR REPLACE VIEW emp_view AS

SELECT deptno, count(*) total_employeer, sum(sal) total_salary

FROM emp GROUP BY deptno;

DELETE FROM emp_view WHERE deptno=10;

CREATE OR REPLACE TRIGGER emp_view_delete

INSTEAD OF DELETE ON emp_view FOR EACH ROW

BEGIN

DELETE FROM emp WHERE deptno= :old.deptno;

END emp_view_delete;

--登入觸發器

create table logtable (username varchar2(50),logindate date);

CREATE OR REPLACE TRIGGER LOGIN_HIS

AFTER LOGON ON DATABASE

BEGIN

INSERT INTO LOGTABLE

VALUES(USER,SYSDATE);

END;

--包定義

CREATE OR REPLACE package DXJF_MGR

AS

time1 number(10) := 0;

type cursorref_t is ref cursor;

type r_rate_t is record

(

no NUMBER(10),

rate NUMBER(10),

starttime date,

endtime date,

timespanid NUMBER(10)

);

type array_rate_t IS VARRAY(100) OF r_rate_t;

function f_is2day(dt date,duration number) return number;

procedure p_test(p_result in number :=0);

END DXJF_MGR;

/

CREATE OR REPLACE package body DXJF_MGR

AS

/*******************************************************************************

程式型別 :函式判斷通話時間否跨越2天

*******************************************************************************/

FUNCTION f_is2day(dt date,duration number)

return number

is

v_date1 varchar2(10);

v_date2 varchar2(10);

v_date3 date;

v_date4 date;

i_day number(10);

begin

select (dt+duration/24/60/60) into v_date3 from dual;

select trunc(dt+1,'J') into v_date4 from dual;

if v_date3<=v_date4 then

return 0;

else

return 1;

end if;

end;

/*******************************************************************************

程式型別 :測試功能

*******************************************************************************/

PROCEDURE p_test(p_result in number :=0) IS

BEGIN

dbms_output.put_line('p_test');

END p_test;

END DXJF_MGR;

/

pl/sql 練習

相關文章