Oracle的基礎2

--定義

create or replace procedure my_procedure

as

begin

dbms_output.put_line('這是第一個存儲過程');

end my_procedure;

--調用

begin

my_procedure();

end;

--帶輸入參數的存儲過程（插入資料的存儲過程）

create or replace procedure my_procedure2

(

v_empno in number,

v_ename in varchar2,

v_job in varchar2,

v_deptno in number

)

as

begin

--執行一個插入語句

insert into emp (empno,ename,job,deptno) values (v_empno,v_ename,v_job,v_deptno);

end my_procedure2;

--調用

begin

my_procedure2(7944,'老梅','teacher',10);

end;

select * from emp;

--帶輸入輸出參數的存儲過程,根據輸出工資查詢出大于該工資的員勞工數

create or replace procedure my_procedure3

(

v_sal in number,

v_count out number

)

as

begin

select count(1) into v_count from emp where sal > v_sal;

end my_procedure3;

--調用

declare

v_count number;

v_sal number := 7000;

begin

my_procedure3(v_sal, v_count);

dbms_output.put_line('工資高于'||v_sal||'的人數為'||v_count);

end;

--cursor test is select * from emp

--分頁的存儲過程，輸入參數必須要： 目前頁（number）以及一頁顯示條數（number） 輸出參數：總頁數(number)和分頁資料

create or replace procedure page_handler

(

page_number in number,

page_size in number

)

as

--聲明變量

total_rows number;

total_page number;

copy_page_number number := page_number;

--聲明一個弱類型遊标類型

type page_cursor_type is ref cursor;

--聲明遊标變量

page_cursor page_cursor_type;

--定義一個記錄類型

type v_emp_type is record(

rn number,

empno emp.empno%type,

ename emp.ename%type,

job emp.job%type,

mgr emp.mgr%type,

hiredate emp.hiredate%type,

sal emp.sal%type,

comm emp.comm%type,

deptno emp.deptno%type

);

v_emp v_emp_type;

begin

--查詢資料總條數

select count(1) into total_rows from emp;

dbms_output.put_line('資料總條數：'||total_rows);

--通過資料總數和一頁顯示行數計算總頁數

--使用向上取整的函數 ceil

total_page := ceil(total_rows/page_size);

--對page_number進行合法校驗

--輸入參數的值時不能改變的

if copy_page_number < 1 then

copy_page_number := 1;

elsif copy_page_number > total_page then

copy_page_number := total_page;

end if;

dbms_output.put_line('目前頁碼：'||copy_page_number);

--進行分頁查詢,打開一個動态遊标

open page_cursor for 'select * from (select rownum rn, emp.* from emp where rownum <= '|| (copy_page_number*page_size) ||') e where e.rn > '|| (copy_page_number-1)*page_size ||'';

loop

fetch page_cursor into v_emp;

exit when page_cursor%notfound;

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

end loop;

close page_cursor;

end page_handler;

--子查詢

select * from (select rownum rn, emp.* from emp where rownum < 10) e where e.rn > 0;

--執行

begin

page_handler(-1, 5);

end;

select * from emp;

select ceil(5/3) from dual;

--包的使用,在包中定義一個存儲過程，輸入參數為薪資，輸出輸出為遊标變量

create or replace package my_package

as

--定義遊标、存儲過程、函數等

--定義一個遊标類型

type emp_cursor_type is ref cursor;

procedure getEmp(v_sal in number, emp_cursor out emp_cursor_type);

end my_package;

--定義包體部分

create or replace package body my_package

as

--實作存儲過程

procedure getEmp(v_sal in number, emp_cursor out emp_cursor_type)

as

begin

open emp_cursor for select * from emp where sal > v_sal;

end getEmp;

end my_package;

--調用程式包

declare

--聲明遊标變量

emp_cursor my_package.emp_cursor_type;

v_emp emp%rowtype;

begin

my_package.getEmp(9999, emp_cursor);

loop

fetch emp_cursor into v_emp;

exit when emp_cursor%notfound;

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

end loop;

end;

select * from emp;

create or replace procedure page_handler2

(

page_number in number,

page_size in number,

total_page out number,

data_list out my_package.emp_cursor_type

)

as

total_rows number;

copy_page_number number := page_number;

begin

--查詢總條數

select count(1) into total_rows from emp;

--計算總頁數

total_page := ceil(total_rows/page_size);

--校驗目前頁碼

if copy_page_number < 1 then

copy_page_number := 1;

elsif copy_page_number > total_page then

copy_page_number := total_page;

end if;

--分頁查詢

open data_list for 'select * from (select rownum rn, emp.* from emp where rownum<='||(copy_page_number*page_size)||') e where e.rn>'||(copy_page_number-1)*page_size||'';

end page_handler2;

--調用

declare

total_page number;

data_list my_package.emp_cursor_type;

--定義一個記錄類型

type v_emp_type is record(

rn number,

empno emp.empno%type,

ename emp.ename%type,

job emp.job%type,

mgr emp.mgr%type,

hiredate emp.hiredate%type,

sal emp.sal%type,

comm emp.comm%type,

deptno emp.deptno%type

);

v_emp v_emp_type;

begin

page_handler2(4,5,total_page,data_list);

dbms_output.put_line('總頁數：'||total_page);

loop

fetch data_list into v_emp;

exit when data_list%notfound;

dbms_output.put_line('員工姓名：'||v_emp.ename||'，員工職位：'||v_emp.job);

end loop;

end;

call my_procedure();

select * from emp;

drop table emp_history;

create table emp_history as select * from emp where 1=2;

--删除員工資訊時将删除的員工儲存到曆史記錄表中

create or replace trigger del_emp_tr

after delete on emp for each row

begin

--觸發器有兩個僞屬性，:old :new

insert into emp_history values (:old.empno,:old.ename,:old.job,:old.mgr,:old.hiredate,:old.sal,:old.comm,:old.deptno);

end;

--執行删除觸發觸發器

delete from emp where empno=7944;

--oracle沒有主鍵自增，可以使用序列代替

create sequence emp_seq

increment by 1

start with 1

nomaxvalue

nocycle

nocache;

create or replace trigger add_emp_tr

before insert on emp for each row

begin

select emp_seq.nextval into :new.empno from dual;

end;

insert into emp (ename,job) values ('admin', 'DBA');

--建立一個索引

create index ename_index on emp (ename);

--删除

drop index ename_index;

--賦予使用者建立視圖的權限

grant create view to meijiajie;

--建立一個視圖

create or replace view vw_emp as select ename,job from emp;

select * from vw_emp;

create user meijiajie2 identified by meijiajie2;

grant create session to meijiajie2;

grant select on meijiajie.vw_emp to meijiajie2;

public class TestProcedure {

private Connection conn;

@Before

public void beforeMethod() throws ClassNotFoundException, SQLException {

//加載驅動

Class.forName("oracle.jdbc.driver.OracleDriver");

//擷取連接配接

//oracle的連接配接(瘦連接配接)：jdbc:oracle:thin:@192.168.10.254:1521:orcl

conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.10.254:1521:orcl", "meijiajie", "meijiajie");

}

@After

public void afterMethod() throws SQLException {

//關閉連接配接

if (conn != null) {

conn.close();

}

}

@Test

public void test1() throws SQLException {

//擷取語句執行對象

CallableStatement cs = conn.prepareCall("call my_procedure()");

//執行存儲過程

cs.execute();

//關閉資源

cs.close();

}

@Test

public void test2() throws SQLException {

CallableStatement cs = conn.prepareCall("call my_procedure2(?,?,?,?)");

//設定參數

cs.setInt(1, 2);

cs.setString(2, "金三胖");

cs.setString(3, "高麗棒子");

cs.setInt(4, 10);

//執行

cs.executeUpdate();

//關閉資源

cs.close();

}

@Test

public void test3() throws SQLException {

CallableStatement cs = conn.prepareCall("call my_procedure3(?,?)");

cs.setInt(1, 9999);

//如何去傳入一個輸出參數，

//輸出參數需要注冊

cs.registerOutParameter(2, Types.INTEGER);

//執行

cs.execute();

//擷取輸出參數

int count = cs.getInt(2);

System.out.println(count);

}

@Test

public void test4() throws SQLException {

CallableStatement cs = conn.prepareCall("call page_handler2(?,?,?,?)");

//設定參數

cs.setInt(1, 4);

cs.setInt(2, 5);

cs.registerOutParameter(3, Types.INTEGER);

//Oracle的遊标類型需要使用OracleTypes中的常量

cs.registerOutParameter(4, OracleTypes.CURSOR);

//執行

cs.execute();

//擷取輸出結果

int totalPage = cs.getInt(3);

System.out.println("總頁數：" + totalPage);

//擷取傳回的遊标

ResultSet rs = (ResultSet) cs.getObject(4);

while (rs.next()) {

System.out.println(rs.getString("ename") + "\t" + rs.getString("job"));

}

}

}