Oracle_第四弹

来源：互联网发布：凡科建站源码下载编辑：程序博客网时间：2024/06/14 20:57

目录

点击下载1-4 word版本

pl/sql编程语言 2

简介 2

PL/SQL的语法 ☆☆☆ 2

PL/SQL的变量和常量的定义 2

逻辑语句 3

游标类似于集合 4

例外类似于异常 5

存储过程 5

存储函数 6

存储过程和存储函数的区别 6

触发器 6

java中操作oracle 8

测试链接 8

调用过程基本类型的out 8

调用过程游标类型的out 8

oracle_day4 PL/SQL语言，java应用

pl/sql编程语言

简介

pl/sql 语言是oracle对sql的过程化扩展，是把sql的语言的数据操纵能力与过程语言的数据处理能力结合了起来

--例：给所有职工涨10%工资

update emp set sal=sal*1.1

select * from emp

PL/SQL的语法 ☆☆☆

declare

声明部分 (变量定义，光标申请，例外说明)

begin

语句

exception

例外处理语句

end;

PL/SQL的变量和常量的定义
- 定义变量
  - 基本类型： char 、 varchar2 、 date 、 number 、 boolean 、long

应用例子：

declare --声明

var1 char(10):='lala'; --定义变量

BEGIN --相当于main方法

dbms_output.put_line(var1); --输出语句

end;

引用型变量（变量名表名.字段名%type）

概念：引用变量既是与字段的类型一样

应用例子:

declare

myname emp.ename%type:='拉姆'; --声明引用变量并赋值

BEGIN

dbms_output.put_line(myname);

end;

记录型变量（变量名表名%rowtype）

概念：记录型变量，记录了这个表的一行

应用例子:

declare

p emp%rowtype; --定义记录型变量表的一行

BEGIN

select * into p from emp t where t.empno=7369; --为记录型变量赋值

dbms_output.put_line(p.ename); --出入记录型变量的一个字段

end;

定义常量

v_age_c constant number(10) :=60; ---常量

逻辑语句
- 判断 IF
  - 语法1

if 条件 then

语句 1;

end if;

语法2.

if 条件 then

语句1;

else

语句2;

end if;

语法3.

if 条件 then

语句1;

ELSIF 条件 then

语句;

else

语句;

end if ;

例子：输入年龄返回状态

declare

v_age number(10);

begin

v_age :=11;

if v_age<18 then

dbms_output.put_line('未成年');

elsif v_age>=18 and v_age<65 then

dbms_output.put_line('赚钱中');

else

dbms_output.put_line('安享晚年');

end if;

end;

循环
- 语法1.

while total <= 25000

loop

语句

total:=total+1；

end loop;

语法2.

loop

exit when 跳出条件;

语句

end loop;

语法3.

fro i in 1..3

loop

语句;

end loop;

例1:输出0-100的数

declare

p number(10) := 0;

BEGIN

while p<=100

loop

dbms_output.put_line(p);

p:=p+1;

end loop;

end;

例2:输出0-100的数

declare

v_num number(10) :=0;

begin

loop

-- if v_num>100 then

-- exit;

-- end if;

exit when v_num>100; --条件退出

dbms_output.put_line(v_num);

v_num :=v_num +1;

end loop;

end;

例3:输出0-100的数

BEGIN

for i in 0..100

loop

dbms_output.put_line(i);

end loop;

end;

游标类似于集合
- 简介：
在java中有集合的概念，name在pl/sql中也会用到多条记录，这时我们就用游标
- 语法：

cursor 游标名 [(参数名参数类型,参数名参数类型)] is select 语句

例子1：用游标的方式输出员工的编号和姓名

DECLARE

cursor c1 is select * from emp; --创建无参数的游标

emprow emp%rowtype; --定义一个记录型变量用来取出游标中的每一条记录

BEGIN

open c1; --使用游标的时候需要打开游标之后要关闭游标

loop --循环

FETCH c1 into emprow; --逐条取出游标中的数据放到记录型变量中

exit when c1%notfound; --当游标中没有数据的时候退出循环

dbms_output.put_line(emprow.empno||'--'||emprow.ename); --输出

end loop; --结束循环

close c1; --关闭游标

end;

例子2：为部门为20的员工涨工资

DECLARE

cursor c2 (dno emp.deptno%type) is select empno from emp where deptno=dno; --创建带参数的游标

pno emp.empno%type;

BEGIN

open c2(20);

loop

fetch c2 into pno;

exit when c2%notfound;

update emp t set t.sal= t.sal-1000 where t.empno=pno;

end loop;

close c2;

end;

select * from emp

例外类似于异常
- 系统给定的异常

--no_data_found (没有找到数据)

--too_many_rows (select ... into 语句匹配多行)

--zero_divide (被零除)

--value_error (算数或转换错误)

--timeout_on_resource (在等待资源时发生错误)

--例子：写出被0除的异常

DECLARE

var number(10);

BEGIN

var:=1/0;

EXCEPTION

when zero_divide then dbms_output.put_line('被0除异常');

when value_error then dbms_output.put_line('算数或转换错误');

when others then dbms_output.put_line('其他错误');

end;

自定义异常异常可自定义，在声明中来定义异常

declare

exc_age exception; --自定义异常

v_age number(10);

BEGIN

v_age:=122;

--v_age :=&nage; ---弹框自定义在PL/SQL编辑器中好使

if v_age >120 then --如果年龄大于120就抛出异常

raise exc_age;

end if;

EXCEPTION --捕获异常

when exc_age then --对异常处理

dbms_output.put_line('抛出异常');

end;

存储过程
- 简介
是编辑好后，具有特定功能的，sql对象放在数据库中供客户端调用
- 语法
create [or replace] procedure 过程名(eno in number) is 定义变量;
- 例子1 给指定的员工加100工资并打印前后工资

create or replace procedure addSall(eno in number) is pemp emp%rowtype;

BEGIN

select * into pemp from emp where empno=eno;

UPDATE emp set sal=sal+100 where empno=eno;

dbms_output.put_line('涨工资前'||pemp.sal||'涨工资后'||(pemp.sal+100));

end ;

--在sql中调用方法1

BEGIN

addSall(7369);

end;

--在sql中调用方法2

call addSall(7369);

例子2 通过存储过程调用返回年薪

create or replace procedure get_year_sals1(eno in number,v_sals out number) ---数据类型不写长度

begin

select e.sal*12 + nvl(e.comm,0) into v_sals from emp e where e.empno=eno;

--dbms_output.put_line(v_sals);

end;

--调用out参数的存储过程

declare

v_sals number(10);

begin

get_year_sals1(7369,v_sals);

dbms_output.put_line(v_sals);

end;

存储函数
- 语法：
  create [or replace] function 存储函数的名(变量名 [in]/out 变量的数据类型) return 数据类型 as /is 变量声明

begin

---业务逻辑

return 返回值

end;

例子通过存储函数调用返回年薪

create or replace function get_year_sal_fun(eno number) return number

is v_sals number(10);

begin

select sal*12+nvl(comm,0) into v_sals from emp where empno=eno;

return v_sals;

end;

--调用函数的方法

declare

v_sals number(10);

begin

v_sals := get_year_sal_fun(7369); ---存储函数的返回值必须接收

dbms_output.put_line(v_sals);

end;

存储过程和存储函数的区别

存储过程没有返回值，存储函数有返回值，

存储过程不能再sql中语句中使用，存储函数因为有返回值所以可以再sql中当做一个值来使用，

存储过程和存储函数都可以通过 out来获取多个值。

触发器
- 简介
监控器如果数据发生变化可以调用预编译的sql
- 语法

create [or replace] trigger 触发器名称

before / after --之前或之后触发

insert / update /delete --监控什么动作 -- insert or update 监控多个动作

on 表 --监控哪个表

[for each row [when(条件)]] -----行级触发器用处如果要使用 :old :new 需要打开 for each row

declare --声明

begin

业务

end;

例子1：对emp 表插入数据时打印一句话 "新员工入职了"

create or replace trigger add_emp

after

insert

on emp

---for each row

declare

begin

dbms_output.put_line('新员工入职了');

end;

--测试例子1

insert into emp (empno,ename) values(9527,'00发');

commit;

例子2：不能给员工降工资

create or replace trigger update_emp

before update of sal on emp

for each row

begin

if :old.sal >= :new.sal then

--错误提示框第一个参数 -20001 --- -29999

raise_application_error(-20001,'不能给员工降工资');

end if;

end;

--测试例子2

update emp set sal=sal-1 where empno=7369;

commit;

例子3：实现主键序列

create table stu(

stuid number(10) primary key,

stuname varchar2(20)

)

create sequence seq_person; --创建序列

---语法

create or replace trigger add_stu

before

insert

on stu

for each row

declare

v_id number(10);

begin

select seq_person.nextval into v_id from dual;

:new.stuid:=v_id;

end;

insert into stu (stuname) values('张飞');

insert into stu values(1000,'张飞3');

commit;

select * from stu;

java中操作oracle

测试链接

@Test

publicvoid testJdbc() {

Stringdriver="oracle.jdbc.driver.OracleDriver";

Stringurl="jdbc:oracle:thin:@192.168.29.59:1521:orcl";

Stringuser="scott";

Stringpassword="tiger";

try {

Class.forName(driver);

Connectioncon=DriverManager.getConnection(url,user, password);

PreparedStatementstatement = con.prepareStatement("select * from emp where deptno=?");

statement.setLong(1,10);

ResultSetquery = statement.executeQuery();

while (query.next()) {

System.out.println(query.getLong("empno"));

}

} catch (Exception e) {

e.printStackTrace();

}

调用过程基本类型的out

@Test

publicvoid Jdbc1() {

Stringdriver="oracle.jdbc.driver.OracleDriver";

Stringurl="jdbc:oracle:thin:@192.168.29.59:1521:orcl";

Stringuser="scott";

Stringpassword="tiger";

try {

Class.forName(driver);

Connectioncon=DriverManager.getConnection(url,user, password);

CallableStatementcallst=con.prepareCall("{call get_year_sals1(?,?)}");

callst.setInt(1, 7369);

callst.registerOutParameter(2, OracleTypes.NUMBER);

callst.execute();

System.out.println(callst.getInt(2));//getint(第几个参数)

} catch (Exception e) {

e.printStackTrace();

}

调用过程游标类型的out

@Test

publicvoid Jdbc2() {

Stringdriver="oracle.jdbc.driver.OracleDriver";

Stringurl="jdbc:oracle:thin:@192.168.29.59:1521:orcl";

Stringuser="scott";

Stringpassword="tiger";

try {

Class.forName(driver);

Connectioncon=DriverManager.getConnection(url,user, password);

CallableStatementcallst=con.prepareCall("{call get_emp_by_deptno(?,?)}");

callst.setInt(1, 20);

callst.registerOutParameter(2, OracleTypes.CURSOR);

callst.execute();

ResultSetrs=((OracleCallableStatement)callst).getCursor(2);

while (rs.next()) {

System.out.println(rs.getObject(1)+","+rs.getObject(2));

}

} catch (Exception e) {

e.printStackTrace();

}

阅读全文

0 0

Oracle_第四弹

oracle_day4 PL/SQL语言，java应用

pl/sql编程语言

简介

PL/SQL的语法 ☆☆☆

PL/SQL的变量和常量的定义

逻辑语句

游标 类似于集合

例外 类似于异常

存储过程

存储函数

存储过程和存储函数的区别

触发器

java中操作oracle

测试链接

调用过程 基本类型的out

调用过程 游标类型的out

游标类似于集合

例外类似于异常

调用过程基本类型的out

调用过程游标类型的out