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"));

}

}

}