视频学习Oracle记录
========================================
六、 PL/SQL编程——博大而精深
========================================
pl/sql是在Oracle上的sql语言上的扩展，pl/sql不仅允许嵌入sql语言，还可以定义变量常量，允许使用条件语句和循环语句，允许使用例外处理各种错误。
例子：PL/SQL
建表：create table mytest(name varchar2(30),passwd varchar2(30));
创建过程：
create or repalce procedure sp_pro1 is --sp_pro1过程名如果存在，replace 替换
begin --执行部分
insert into mytest values('ll','m1234');
end;
/
show error;--可以查看出现的错误
调用过程
a、exec 过程名(arg1,arg2,...)
b、call 过程名(arg1,arg2,...)
PL/SQL编写规范
注释：单行：-- 多行：/*....*/
PL/SQL 块--PL/SQL的基本单元
块有三部分构成：定义、执行、例外
declare
/*定义部分(可选)----定义常量、变量、游标 、例外、复杂数据类型*/
begin
/*执行部分(必要)----要执行的PL/SQL语句和SQL语句*/
exception
/*例外处理部分(可选)----处理运行的各种错误*/
end;
例子：只包含执行部分的PL/SQL
set serveroutput on --打开输出选项
begin
dbms_output.put_line('hello');--dbms_output是Oracle提供的包，put_line()为函数
end;
例子：包含定义部分和执行部分的PL/SQL
declare
v_ename varchar2(5);--定义字符串变量
begin
select ename into v_ename from emp where empno=&no;--&取地址符，会提示从命令行输入
dbms_output.put_line('雇员名：'||v_ename);--控制台显示用户名
end;
例子：包含定义部分和执行部分以及例外处理部分的PL/SQL
declare
v_ename varchar2(5);--定义字符串变量
begin
select ename into v_ename from emp where empno=&no;--&取地址符，会提示从命令行输入
dbms_output.put_line('雇员名：'||v_ename);--控制台显示用户名
exception
when no_data_found then
dbms_output.put_line('没有发现数据！');
end;
例子：PL/SQLc传参的存储过程(前面几个例子是块，下面这个例子为存储过程)
create procedure sp_pro3(spName varchar2, newSal number) is
begin
/*执行部分(略)*/
end;
Java调用存储过程
import java.sql.*;
/**
* Created by floyd on 2014/6/28.
*/
public class OraclecomJava {
public static void main(String[] arg){
try {
//1.加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//2.得到连接
Connection ct = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521databasename:t_table","scott","tiger");
//3.创建一个CallableStatement
CallableStatement cs = ct.prepareCall("{call sp_pro(?,?)}");
//4.给？赋值
cs.setString(1,"name");
cs.setInt(2,10);
//5.执行
cs.execute();
//关闭
cs.close();
ct.close();
} catch (Exception e){
e.printStackTrace();
}
}
}
函数的案例
1、函数创建
--输入雇员的姓名，返回该雇员的年薪
create function sp_fun(spName varchar2) return number is yearSal number(7,2);--函数头
begin
/*执行部分*/
select sal*12+nvl(comm,0)* 12 into yearSal from emp where ename=spName;
return yearSal;
end;
2、函数调用
命令行执行：
sql> var income number;
sql> call sp_fun('SMITH') into: income;
sql> print income
Java程序中调用
select sp_fun('SMITH') from emp
rs.getInt(1)得到返回结果
包的使用
---创建一个包，声明该包有一个过程和一个函数
create package sp_package is
procedure update_sal(name varchar2, new sal number);
function sp_fun(name varchar2) return number;
end;
实现包中的过程和函数,即包体：
create package body sp_package is
procedure update_sal(name varchar2, new sal number)is
begin
update emp set sal=newsal where ename=name
end;
function sp_fun(name varchar2) return number is ysal number;
begin
select sal*12+nvl(comm,0)* 12 into yearSal from emp where ename=name;
return ysal;
end;
end;
触发器
变量
标量scalar
定义一个边长字符串
v_ename varchar2(10)
v_sal number(6,2)
赋值号：v_sal number(6,2):=5.4
v_date date
定义布尔变量 v_valid boolean not null default false;
例子：输入员工号显示雇员姓名、工资、个人所得税
--税率为0.03
declare
c_tax_rate number(3,2) := 0.03;
--v_ename varchar2(5);
v_ename emp.ename%type;--使用%type来定义v_ename变量
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*v_tax_rate;
--输出
dbms_output.put_line('姓名：'||v_ename||'工资'||v_sal||'个人所得税' || v_tax_sal)
end;
复合变量（相当于高级语言中的结构体）composite
---PL/SQL记录实例
declare
--定义一个PL/SQL记录类型 emp_record_type,类型包含三个数据
type emp_record_type is record(name emp.ename%type,salary emp.sal%type,tile emp.job%type);
--定义了一个变量sp_record，类型为emp_record_type
sp_record emp_record_type;
begin
select ename,sal,job into sp_record from emp where empno=&no;
dbms_output.put_line('姓名：'||sp_record.name);
end;
复合类型（相当于高级语言中的数组）composite
---PL/SQL记录实例
declare
--定义一个PL/SQL表类型 sp_table_type
type sp_table_type is table of emp.ename%type index by binary_integer;
--定义了一个变量sp_table，类型为sp_table_type
sp_table sp_table_type;
begin
select ename into sp_table(0) from emp where empno=&no;
dbms_output.put_line('姓名：'||sp_table(0));
end;
---使用PL/SQL写一个块
declare
--定义游标类型 sp_emp_cursor
test_cursor sp_emp_cursor;
--定义变量
v_ename emp.ename%type
v_sal emp.sal%type;
begin
---执行
--把 test_cursor 和 select 结合
open test_cursor for select ename,sal from emp where deptno=&no;
loop
fetch test_cursor into v_ename,v_sal;
--判断是否test_cursor为空
exit when test_cursor%notfund;
dbms_output.put_line('姓名：'||v_ename||'工资'||v_sal);
end loop;
end;
/