PL/SQL(一)

1 过程，函数，触发器 用PL/sql编写，这些都是在Oracle中

PL/sql是很强大的过程化语言，可以被Java调用

提升应用程序的性能：

如果用传统的操作数据库java 程序发送sql，数据库编译

，编译的过程要消耗时间，而过程，函数不必经过编译这一过程

1 优化sql 2 还不行就用过程，函数

缺点：

移植性不好

存储过程：

添加普通过程

create procedure myprocedure is 

begin

  insert into emp values(23,'myname'......);

end;/ --  后面机上/让Oracle去创建

如果有了存储过程的话，要替换这个myprocedure

create or replace procedure myprocedure is begin ...end;

查看错误信息 show errow;

调用过程

第一种exec  过程名[(参数1,参数2....)];

第二种 call  过程名[(参数1,参数2....)];

exec myprocedure;

块： 过程，函数，触发器，包

编写规范

注释

单行注释 --

多行     /*.......*/

符号命名

变量 v_xxx

常量 c_xxx

游标 xxx_cursor

例外 e_xxx (e_error)

块：三部分：定义部分，执行部分，例外部分

declear  定义部分  可选

begin

执行部分

dbms_output.put_line('test');

exception 例外部分 可选

end;

dbms_output 是Oracle提供的包，

set serveroutput off; -- off/on 打开关闭输入

declare 

v_ename varchar2(20)

bigin 

select ename into v_ename from emp

where empno = &aaa;

dbms_output_put_line('员工名：'||v_ename);

end 

执行后会弹出一个输入aaa的框，然后输出。

declare 

v_ename varchar2(20);

v_sal number(7,2);

bigin 

select ename,sal into v_ename,v_sal from emp

where empno = &aaa;

dbms_output_put_line('员工名：'||v_ename||'薪水'||val);

exception

when no_data_found then

dbms_output_put_line('输入有误');

end 

传递参数 myprocedure(name varchar2,sal number) 参数只需要声明什么类型就行了

create procedure myprocedure(newname varchar2,newsal number) is

begin

update emp set sal = newsal where ename = newname;  

end;

exec  myprocedure('SCOTT',8888)

java 调用过程

CallableStatement  cs = conn.propareCall("{call muprocedure(?,?)}");

cs.setString(1,"SMITH");

cs.setInt(2,100000);

cs.execute();

函数

函数是用于返回特定的数据，只返回一个值，在函数头部必须包含return

子句， 函数体内必须包含return语句返回的数据

通过名字得到员工年薪

create function myfun(name varchar2)

return number is

yearnumber number(7.2);

begin 

select (sal+comm)*12 into yearsal from emp where ename= name; 

return yearsal;

end;

--调用

var income number

call myfun('SCOTT') into:income;

print income

---------------

java调用函数

select mypro('SCOTT') from dual

然后rs.getInt(1); 

包

包是用于逻辑上组合过程和函数的，由包规范和包体组成

create package mypackage is

    procudure mypro(name varchar2,sal number);

    function myfun(name varchar2) return number;

end;

然后创建包体，给把mypackage 实现包体

create [or replace] package body mypackage is

procedure mypro(newname varchar2,newsal number) is 

begin 

update emp set sal = newsal where ename = newname;  

end;

create function myfun(name varchar2)

return number is

yearnumber number(7.2);

begin 

select (sal+comm)*12 into yearsal from emp where ename= name; 

return yearsal;

end;

end;

调用包的过程或者函数

exec mypackage.mypro('SCOTT',8989);

触发器

隐含执行的存储过程，必须指定触发事件和触发的操作。

常用触发事件insert，update，delete。

变量类型

1 标量类型  scalar

2 复合类型  composite

3 参照类型  reference

4 lob    large object

标量scalar是一种常用的类型

定义常量和变量的语法 identifier [constant] datatype [not null] [:=| default expr]

identifier:名称

constant  ：指定常量，需要指定它的初始值，且其值是不能改变的

datatype ：数据类型

not null:指定变量不能为null

:=  给变量或者是常量指定初始值

default 用于指定初始值

expr：指定初始值的PL/sql 表达式，可以为文本值，其他变量，函数等。

标量定义(存放单个变量)

1 定义变长字符串  v_ename varchar2(20)

2 定义一个小数    v_sal number(6,2);

3 定义一个小数并初始化  v_sal number(6,2):=8000

4 定义date   v_birth  date;

5 定义布尔变量       v_valid   boolean not null default false;

输入员工号 显示员工姓名 工资 个人所得税(税率0.03)

declare 

c_tax_rate number(3,2):=0.03

v_ename varchar2(20);

v_sal number(7,2);

v_tax_sal number(7,2);

begin

select ename,sal into v_ename,v_sal from emp where empno = &no;

-- 计算所得税

v_tax_sal:= v_sal * c_tax_rate;

--  输入 省略

end

  如果要查的员工名字长度大于20 就会报错，如何办？  %type  

此时可以该该 v_ename emp.ename%type; 这种类型和表的字段类型一致

这样就不会出溢出问题

declare 

c_tax_rate number(3,2):=0.03

v_ename emp.ename%type;

v_sal emp.sal%type;

v_tax_sal number(7,2);

begin

select ename,sal into v_ename,v_sal from emp where empno = &no;

-- 计算所得税

v_tax_sal:= v_sal * c_tax_rate;

--  输入 省略

end

复合变量  composite(用于存放多个值)

分为pl/sql记录 ,pl/sql表,(嵌套表，varray(动态表))

记录类似于高级语言的结构体(如类似于类)

declare type -- 定义一个名为my_c_t (存放多个数据)

my_c_t  is record(name emp.ename%type,salary emp.sal%type,...)

mytest my_c_t  

begin

select ename,sal,... into my_c_t from emp where empno = 888;

-- dbms_output_put_line('name:'||mytest.ename);

end;

pl/sql表相当于高级语言的数组，下表可以为负数,如所有的ename

declare  type

my_t_t is talbe of emp.ename%type index by binary_integer

test my_t_t

begin

select ename into test(-1) from emp where empno = 888;

-- dbms_output_put_line('name:'||test(-1));-- 放在哪个位置就再哪个位置取出

end;

但是如果select如果返回多行呢？ 报错了！ 如何办？ 用参照变量

参照变量 游标变量(ref cursor用的最多)和对象类型变量(ref obj cursor)

定义游标变量的时候不指定select，打开的时候需要指定

输入部门号，显示该部门的员工名和工资

1

declare type

my_s_t is ref cursor ;

beign end;

2

declare type

my_s_t is ref cursor ;

test my_s_t;

beign end;

3

declare type

my_s_t is ref cursor ;

test my_s_t;

beign 

open test for select ename,sal from emp where deptno = &no;

end;

4 这个时候需要定义两个变量

declare type

my_s_t is ref cursor ;

v_ename emp.ename%type;

v_sal emp.sal%type;

test my_s_t;

beign 

open test for select ename,sal from emp where deptno = &no;

loop

fetch test into v_enam,v_sal;

dbms_output_put_line('name:'v_name||'sal:'||v_sal);

end loop;

end;

5 指定何时退出

declare type

my_s_t is ref cursor ;

v_ename emp.ename%type;

v_sal emp.sal%type;

test my_s_t;

beign 

open test for select ename,sal from emp where deptno = &no;

exit when test%notfound;

loop

fetch test into v_enam,v_sal;

dbms_output_put_line('name:'||v_name||'sal:'||v_sal);

end loop;

end;