PLSQL编程

一、块部分
1、创建一个表格
create table wxy (name varchar2(30),passwd varchar2(30));
2、创建一个过程
create or replace procedure sp_prol is
begin
insert into mytest values('kkkk','m123');
end;

replace:表示如果有sp_pro1.就替换
3、如何查看错误信息
show error;
4、如何调用过程
exec 过程名（参数值1，参数值2....）;
call 过程名（参数值1，参数值2....）;

5、块（编程）主要用于编写四种（过程、函数、触发器、包）
6、编写规范
1、注释
单行注释：--
select * from emp where empno=7788;--取得员工信息
多行注释
/*.....*/
2、标识符号的命名规范
当定义变量时：建议用v_作为前缀 v_sal
当定义常量时：建议使用c_作为前缀 c_rate
当定义游标时：建议用_cursor作为后缀 emp_cursor
当定义例外时: 建议用e_作为前缀 e_error
7、pl/sql块介绍
块（block）是PL/SQL的基本程序单元，编写PL/SQL程序实际上就是编写PL/SQL
块，要完成相对简单的应用功能，可能只需要编写一个简单的块
块结构示意图
PL/SQL块由三个部分定义：定义部分、执行部分、例外处理部分；
如下所示
declear
/*定义部分----定义常量、变量、游标、例外、复杂数据类型*/
begin
/*执行部分----要执行的PL/SQL语句和sql语句*/
exception
/*例外处理部分----处理运行的各种错误*/
end;

set serveroutput on --打开输出选项命令
set serveroutput off--关闭输出选项命令

dbms_output(属于包).put_line（函数）

实例一:最简单的块

begin
dbms_output.put_line('hello.world');
end;

实例二：包含定义部分和执行部分的PL/SQL块

--把用户的工资也显示？
declear
v_ename varchar2(5);--定义变量
v_sal number(7,2);
begin
select ename,sal into v_ename,v_sal from emp where empno=&no;
--在控制台显示用户名
dbms_output.put_line('雇员名：'||v_ename||'工资为'||v_sal)；
end;

实例三;包含定义部分、执行部分、例外处理部分

--定义部分
declear
v_ename varchar2(5);--变量定义
begin
--执行部分
select ename into v_ename from emp where empno=&no;
--异常处理部分
exception
when no_data_found then
dbms_output.put_line('没有数据');
end;

二、过程

案例1：
create or repleace procedure sp_pro3(spName varchar2,newSal number) is

begin
--执行部分根据用户名称修改工资
update emp set sal=newSal where ename=spName;
end;

--调用该过程
call sp_pro3('scott'，4000);

存储过程如何在java中调用

import java.sql.*;
public class TestOraclePro{
public static void main(String [] args){

try{
//加载驱动
Class.forName("oracle.jdbc.driver.OraclaDriver");

//得到连接
Connection ct=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl","wxy","root");

//创建CallableStatement对象

CallableStatement cs=ct.preparCall("{call sp_pro3(?,?)}");

//给？赋值
cs.setString(1,"SCOTT");
cs.setInt(2,1000);

//执行
cs.execute();

//关闭资源
cs.close();
ct.close();
}catch(Exception e){
e.printStackTrace();
}

}
}

问题：如何使用过程返回值

三、函数
函数用于返回特定的数据，当建立函数时，在函数的头部必须包含return 字句，而在函数体内必须包含return语句返回的数据，我们
可以使用create function 来建立函数；
案例一;输入雇员的姓名，返回该雇员的年薪
create function sp_fun1(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;
--在sqlplus中的函数调用
sql>var income number
sql>call sp_fun1('SCOTT') into :income;
sql>print income

同样我们可以在java程序中调用该函数
select sp_fun1('SCOTT') from dual;//这样可以通过rs.getInt(1)的返回的结果

四、包
包用于在逻辑上组合过程和函数，它由规范和包体两部分组成
1、我们可以用create package 命令来创建包;
案例一;
create package sp_package is
--声明了过程 update_sal
procedure update_sal(name varchar2,newSal number);
--声明了函数 annual_income
function annual_income(name varchar2) return number;
end;

包的规范只包含了过程和函数的说明，但是没有过程和函数的实现代码，包体用于实现包规范中的过程和函数
2、建立包体可以使用create package body 命令
--给sp_package创建包体
create package body sp_package is
procedure update_sal(name varchar2,newSal number) is
begin
update emp set sal=newSal where ename=name;
end;
function annual_income(name varchar2)
return number is annual_salary number;
begin
select sal*12+nvl(comm,0) into annual_salary from emp where ename=name;
return annual_salary;
end;
end;

包的调用
sql>exec sp_package.update_sal('SCOTT',120);

标量（scalar）-常量类型
v_ename varchar2(10)
v_sal number(6,2)
v_sal2 number(6,2):=5.4
v_hiredate date;
v_valid  boolean not null default false;

案例;
--下面已输入员工号，显示雇员姓名、工资、个人所得税
--（税率为0.03）为例，说明变量的使用，看看如何编写
declear
c_tax_rate number(3,2):=0.03;
v_ename varchar2 emp.ename%type；
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;
dbms_output.put_line('姓名是：'||v_ename||'工资'||v_sal||'税率'||v_tax_sal);
end;

复合类型-PL/SQL记录
案例：
declare
type emp_record_type is record(
name emp.ename%type,
salary emp.sal%type,
title 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=7788;
dbms_output.put_line('姓名：'||sp_record.name);
end;
复合类型-PL/SQL表
相当于高级语言中的数组，但是需要注意的是在高级语言中数组的下标
不能为负数，而pl/sql是可以为负数的，并且表元素的下标没有限制；
案例：

declare
--index by binary_integer 表示下标是整数
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=7788;
dbms_output.put_line('员工名：'||sp_table(0));
end;

参照变量：参照变量是指用于存放数值指针的变量，通过使用参照变量可以使得应用程序共享相同的对象，从而降低占用的空间
在编写PL/SQL程序时，可以使用游标变量（ref cursor）和对象类型变量（ref obj_type）两种参照变量类型
游标变量
使用游标时，当定义游标时不需要指定相应的select语句，但是当使用游标时（open时）需要指定select语句，这样一个游标
与一个select语句结合了；
案例：使用PL/SQL编写一个快，可以输入部门号，并显示该部门所有的员工名称
declare
--定义游标类型
type sp_emp_cursor is ref 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%notfound;
dbms_output.put_line('名字'||v_ename);
end loop;
--关闭游标
close test_cursor;
end;

控制结构：
三种分支语句 if--then, if--then--else, if--then--elsif--else
案例：
create or replace procedure sp_pro3(name varchar2)is
v_sal number;
begin
select sal into v_sal from emp where ename=name;
if v_sal=0 then
update emp set sal=v_sal+100 where ename=name;
else
update emp set sal=v_sal-100 where ename=name;
end if;
end;

循环结构:loop,while ，for
案例一：
create or replace procedure sp_pro3(name varchar2)is
v_num number:=1;
begin
loop
insert into user values(v_num,name);
exit when v_num=10;
v_num:=v_num+1;
end loop;
end;
案例二：
create or replace procedure sp_pro3(name varchar2)is
v_num number:=1;
begin
while v_num<=20 loop
insert into user values(v_num,name);
v_num:=v_num+1;
end loop;
end;
顺序控制语句：goto, null(不会执行任何操作)

有返回值的存储过程

由于oracle存储过程没有返回值，它的所有返回值都是通过out参数来替代的；
如果返回的是集合，就不能用一般的参数，必须要用package了，
1、建立一个包，如下：
create or replace package testPackage as type test_cursor is ref cursor;
2、创建过程
create or replace procedure sp_pro(pno in number,p_cursor out testPackage.test_cursor)is
begin
open p_cursor for select * from emp where empno=pno;
end;
3、在java中如何调用
·加载驱动
·得到连接（Connection ct）
·创建callableStatement对象
CallableStatement cs=ct.prepareCall("{call sp_pro(?,?)}");
·给？赋值
cs.setInt(1,10);
cs.registerOutParameter(2,oracle,jdbc.OracleTypes.CURSOR);
·执行
cs.execute();
·得到结果集
ResultSet rs=(ResultSet)cs.getObject(2);
while(rs.next){
System.out.println(rs.getInt(1),rs.getString(2));
}

PL/SQL分页技术：

案例：
--创建一个包，该包中定义类型test_cursor游标
create or replace package testpackage as
type test_cursor is ref cursor;
end testpackage;

--编写分页过程
create or replace procedure fenye(
tableName in varchar2,--表名称
pageSize in number,--每页显示的记录数
pageNow in number,--当前页数
countRows out number,--返回的总记录数
countPages out number,--返回的总页数
p_cursor out tespackage.test_cursor--返回的记录集，类型为游标
)is
--定义部分
--定义sql语句 字符串
v_sql varchar2(2000);
--定义两个整数
v_begin number:=(pageNow-1)*pageSize+1;
v_end number:=pageNow*pageSize;
begin
v_sql:='select * from (select t1.*,rownum rn from (select * from '||tableName||')t1 where rownum<='||v_end||')where rn>='||v_begin;
--打开游标，和sql语句关联起来
open p_cursor for v_sql;
--计算总记录数和总页数
v_sql:='select count(*) from '||tableName;
--执行sql语句，并把返回的值赋给countRows
execute immediate v_sql into countRows;
--计算总页数
if mod(countRows,pageSize)=0 then
countPage:=countRows/pageSize;
else
countPage:=countRows/pageSize+1;
end if;
--关闭游标
close p_cursor;
end;

例外处理：case_not_found cursor_already_open dup_val_on_index(索引唯一)
invalid_cursor invalid_number too_many_rows zero_divide value_error
预定义例外 case_not_found
在开发pl/sql块中编写case语句时，如果在when子句中乜有包含必须的条件分支，就会触发case_not_found的例外：
案例：
create or replace procedure sp_pro6(spno number)is
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno=spno;
case
when v_sal<1000 then
update emp set sal=sal+100 where empno=spno;
when v_sal<2000 then
update emp set sal=sal+200 where empno=spno;
end case;
exception
when case_not_found then
dbms_output.put_line('case语句没有与'||v_sal||'想匹配的值');
end;

自定义例外：
案例;
create or replace procedure ex_test(spno number) is
--定义一个例外
myex exception;
begin
update emp set sal=sal+1000 where empno=spno;
--sql%notfound这是表示没有update
--raise myex;触发myex
if sql%notfound then
raise myex;
end if;
exception
when myex then
dbms_output.put_line('没有更新任何用户');
end;