1、基础语法
http://692088846.iteye.com/blog/2017137 （%type、%rowtype、if\if else\if elseif else、while、do..while、游标、异常、函数、过程）
1.1 声明变量赋值并输出
set serveroutput on –设置数据库输出，默认为关闭，每次重新打开窗口需要重新设置。 Declare result integer;
—声明变量【变量名 变量类型】 begin result:=10+3*4-20+5**2;
–给变量赋值【:=】 dbms_output.put_line(‘运算结果是：’||to_char(result)); end; ————————————————————————————————–
dbms_output.put_line函数输出只能是字符串，因此利用to_char函数将数值型结果转换为字符型。 运算的优先次序为NOT、AND和OR。 To_char：将其他类型数据转换为字符型。 To_date：将其他类型数据转换为日期型。 To_number：将其他类型数据转换为数值型。
1.2 %type、%rowtype、select…into
— %type 变量的类型和数据表中的字段的数据类型一致 — %rowtype 变量的类型和数据表中的一行记录数据类型一致
— select … into 变量 表中查询数据并赋值，可以一次性给多个变量赋值
declare v_object CUSTOMER%Rowtype; begin
select * into v_object from customer where rownum=1; dbms_output.put_line(v_object.Enterprise_Code ||’,’||v_object.CreateDate); end;
1.3 只可以增删改
— insert，update，delete，select都可以，create table，drop table不行。DPL，DML，和流程控制语句可以在pl/sql里用但DDL语句不行，表定义语言不可以在plsql中改变 declare v_name student.name%type:=’wang’; –声明变量并赋值 begin insert into student(id,name,age) values(2,v_name,26);
–插入数据 end; ——————————————– declare v_name student.name%type:=’hexian’;
–更新数据 begin update student set name=v_name where id=1; end; begin update student set name=’qinaide’ where id=2; end; ————————————————- ————–
2、 PLSQL流程控制
–if判断—
declare v_b boolean:=true; — := 是赋值 begin if v_b then dbms_output.put_line(‘ok’); end if; end;
–if else判断—
declare v_b boolean:=true; begin if v_b then dbms_output.put_line(‘ok’); else dbms_output.put_line(‘false’); end if; end;
–if elsif else判断–
declare v_name varchar2(20):=’cheng’; begin if v_name=’0701′ then dbms_output.put_line(‘0701′); elsif v_name=’cheng’ then dbms_output.put_line(‘cheng’); else dbms_output.put_line(‘false’); end if; end;
–loop循环，注意推出exit是退出循环，而不是推出整个代码块
declare v_i binary_integer :=0; begin loop exit when v_i>10; v_i :=v_i+1; dbms_output.put_line(‘hehe’); end loop; dbms_output.put_line(‘over’); end;
— while循环
declare v_i binary_integer:=0; begin while v_i<10 loop dbms_output.put_line(‘hello’||v_i ); v_i:=v_i+1; end loop; dbms_output.put_line(‘over’); end;
— for循环，注意不需要声明变量
begin for v_i in 0..10 loop dbms_output.put_line(‘hello’||v_i); end loop; dbms_output.put_line(‘over’); end;
3、PLSQL异常处理
1、声明异常 异常名 EXCEPTION； 2、抛出异常 RAISE 异常名 3、处理异常 抛出异常后的逻辑代码不会被继续执行 异常的定义使用 ――――――――――――――――――――――――――――――――――――― begin dbms_output.put_line(1/0); –1/0出现错误，抛出异常，输出error exception when others then dbms_output.put_line(‘error’); end; —————————————————————————– declare e_myException exception; –声明异常 begin dbms_output.put_line(‘hello’); –raise抛出异常,用此关键字，抛出后转到自定义的e_myException，执行其 –里面的putline函数后，再跳到end处，结束PL/SQL块，raise接下面的2句不会继续执行。 raise e_myException; dbms_output.put_line(‘world’); dbms_output.put_line(1/0); exception when e_myException then –执行异常 dbms_output.put_line(sqlcode); –当前会话执行状态，错误编码 dbms_output.put_line(sqlerrm); –当前错误信息 dbms_output.put_line(‘my error’); when others then dbms_output.put_line(‘error’); end;
4、 PLSQL游标
4.1 游标定义
备注：把游标看作一个集合 游标，从declare、open、fetch、close是一个完整的生命旅程。cursor 游标(结果集)用于提取多行数据，定义后不会有数据，使用后才有。一旦游标被打开，就无法再次打开(可以先关闭，再打开)。
--游标的定义
declare
v_title books.title%type; --声明为和数据库中数据相同的类型
v_retail books.retail%type;
cursor book_cursor is --把游标看作一个集合
select title, retail form books natural join orderitems where order=1012;
Begin
open book_cursor --打开游标
loop
fetch books_cursor into v_title, v_ratial; --通过循坏提取数据
exit when book_cursor%notfound --如果没有数据就退出循坏
dbms_output.line('book title:' || v_title || ',price:' || v_retail);
end loop;
close book_cursor; --关闭游标
end;
--第二种游标的定义方式，用变量控制结果集的数量。
declare
v_id binary_integer;
cursor c_student is select * from book where id > v_id;
begin
v_id:=10;
open c_student;
close c_student;
end;
--第三种游标的定义方式，带参数的游标，用的最多。
declare
cursor c_student(v_id binary_integer) is select * from book where id>v_id;
begin
open c_student(10);
close c_student;
end;
View Code
4.2 游标的使用
游标的使用，一定别忘了关游标。 declare v_student book%rowtype;
cursor c_student(v_id binary_integer)
is select * from book where id>v_id; begin
open c_student(10);
fetch c_student
into v_student;
close c_student; dbms_output.put_line(v_student.name); end;
如何遍历游标fetch 游标的属性 %found，%notfound,%isopen,%rowcount。 %found:若前面的fetch语句返回一行数据，则%found返回true，如果对未打开的游标使用则报ORA-1001异常。 %notfound，与%found行为相反。 %isopen，判断游标是否打开。 %rowcount:当前游标的指针位移量，到目前位置游标所检索的数据行的个数，若未打开就引用，返回ORA-1001。 注： no_data_found和%notfound的用法是有区别的，小结如下 1）SELECT . . . INTO 语句触发 no_data_found； 2）当一个显式光标(静态和动态)的 where 子句未找到时触发 %notfound； 3）当UPDATE或DELETE 语句的where 子句未找到时触发 sql%notfound； 4）在光标的提取(Fetch)循环中要用 %notfound 或%found 来确定循环的退出条件，不要用no_data_found。
4.3 游标实例：
1、loop方式遍历游标
declare
v_bookname varchar2(100);
--带参数的游标，把游标当做一个集合（只有bookname的集合）
cursor c_book(i_id number) is select bookname from book where id = i_id;
begin
Open c_book(i_id); --打开游标
Loop
Fetch c_book into v_bookname; --获取数据
exit when c_student%notfound;
update book set price = '33' where bookname = v_bookname; --如果该id对应的bookname没值则更新
End Loop;
Close c_book; --关闭游标
end;
或
declare
v_bookname varchar2(100);
cursor c_book(i_id number) is select bookname from book where id = i_id;
begin
Open c_book(i_id);
Fetch c_book into v_bookname;
While c_book%Found
Loop
update book set price = '33' where bookname = v_bookname;
Fetch c_book into v_bookname;
End Loop;
Close c_book;
end;
2、while循环遍历游标，注意，第一次游标刚打开就fetch，%found为null，进不去循环
解决方法：while nvl(c_student%found,true) loop
declare
v_bookname varchar2(100);
cursor c_book(i_id number) is select bookname from book where id = i_id;
begin
Open c_book(i_id);
while nvl(c_book%found,true) --或这种写法：while c_book%found is null or c_book%found loop
Fetch c_book into v_bookname;
update book set price = '33' where bookname = v_bookname;
End Loop;
Close c_book;
end;
3、 for循环遍历
- 最简单，用的最多，不需要 声明v_student,Open和Close游标和fetch操作(不用打开游标和关闭游标，实现遍历游标最高效方式)
declare
cursor c_book(i_id number) is select bookname from book where id = i_id;
begin
for cur in c_book(i_id) --直接将入参i_id传入cursor即可
loop
update book set price = '53' where bookname = cur.bookname;
end loop;
end;
遍历游标
5、Oracle存储过程
存储过程—就像数据库中运行方法（函数），和C#方法一样，由存储过程名/存储过程参数组成，可以有返回结果。
优点：
– 执行速度更快（在数据库中保存的存储过程语句都是编译过的）
– 允许模块化程序设计（类似方法的复用）
– 提高系统安全性（防止sql注入）
– 减少网络流通量（只要传输存储过程的名称）
一般，以sp_、xp_开头的都是系统存储过程,用户自定义存储过程usp_
总结：输入参数 in或缺省、输出参数out，不带数据长度； 还有in out类型；
对于in类型参数：不能赋值，只能用他给变量赋值，是只读的； 如果后面加了default则在调用用可以不用传值；
对于out类型参数：调用时不用赋值，在过程中会被赋值；
对于in out 类型参数：既可以赋值，也可以改变值；
对过程的调用没有返回值，不能用变量去接收，它没有返回值；
过程是一个完整的sql，函数是一个合法的表达式
IN,OUT,IN OUT是形参的模式。若省略，则为IN模式。
IN模式的形参只能将实参传递给形参，进入函数内部，但只能读不能写，函数返回时实参的值不变。
OUT模式的形参会忽略调用时的实参值（或说该形参的初始值总是NULL），但在函数内部可以被读或写，函数返回时形参的值会赋予给实参。
IN OUT具有前两种模式的特性，即调用时，实参的值总是传递给形参，结束时，形参的值传递给实参。
调用时，对于IN模式的实参可以是常量或变量，但对于OUT和IN OUT模式的实参必须是变量。
5.1 存储过程案例
create table BOOK
(
ID VARCHAR2(10) not null,
BOOKNAME VARCHAR2(10) not null,
PRICE VARCHAR2(10) not null,
CID VARCHAR2(10) not null
);
-- %rowcount是SQL的属性表示影响了多少条记录
1、insert-------------------------------------------------------------------------------------------------
create or replace procedure say_hello(
i_name in varchar2, --输入参数，不带类型长度
o_result_msg out varchar2 --输出参数，不带类型长度
)
as --在as和begin之间定义变量，需要声明类型长度
v_price varchar2(100);
e_myException exception;
begin
insert into book(id,bookname,price) values (1,2,3); --这里会报错，下面的不再继续执行
o_result_msg := 'success';
exception --报错误之后，执行错误的地方
when others then
rollback;
o_result_msg := substr(sqlcode, 1, 200); --返回错误码
end;
--调用过程--------------------------------------------------
declare
i_name varchar2(100) := 'dd';
o_result_msg varchar2(100);
begin
say_hello(i_name,o_result_msg); --注意存储过程没有返回值
dbms_output.put_line(o_result_msg);
end;
2、update/delete------------------------------------------------------------------------------------
create or replace procedure say_hello(
i_name in varchar2,
o_result_msg out varchar2
)
as
v_price varchar2(100);
e_myException exception;
begin
update book set price = '55' where bookname = i_name;
delete from book where bookname = i_name;
if sql%notfound then
raise e_myException;
end if;
o_result_msg := 'success';
exception
when e_myException then
rollback;
o_result_msg := 'update or delete dail';
end;
3、select-------------------------------------------------------------------------------------------------------------------
create or replace procedure say_hello(
i_name in varchar2,
o_result_msg out varchar2
)
as
v_price varchar2(100);
e_myException exception;
begin
select price into v_price from book where bookname = i_name;
o_result_msg := 'success';
exception
when no_data_found then
rollback;
o_result_msg := 'select into dail';
end;
存储过程增删改、调用 存储过程入参，不论类型，
缺省情况下值都为null,
入参和出参不能有长度,其中关键字
as可以替换成is,存储过程中
变量声明在as和begin之间，同时，存储过程中可以再调用其它的存储过程，如果要保证存储过程之间的事务处理不受影响，可以定义为自治事务。 create or replace procedure say_hello( i_name
in varchar2,
–入参 ，入参和出参数据不能有长度。对于入参为null情况下给予缺省值 i_flag number,
— 缺省时为in o_ret
out number ) as v_name varchar2(100);
–定义变量在as和begin之间，需要长度 begin if i_name is null then v_name := ‘0’; else v_name := i_name; end if; insert into phone(..,wname..,) values(..,v_name,..); end; 或直接在insert语句中调用nvl函数赋缺省值 insert into phone(..,wname..,) values(..,nvl(v_name,’ ‘),..); —-如果将’ ‘写成”,则insert进来的v_name值还是为”等价于null值
5.2 带一个参数的存储过程
输入参数in，
输入参数不能进行 :=赋值，但可以将它赋给as后面定义的变量【输入参数不能在存储过程中被赋值，它是只读的；但它的值可以赋给别的变量】； 输入参数in，可以作为变量进行
条件判断、is null null值判断；
默认不写就是in；
存储过程没有重载，这个有参的say_hello会替代已经存在的无参say_hello。 create or replace procedure say_hello(v_name in varchar2) as begin
–v_name:=’a’; –存储过程入参v_name不能做为赋值目标 dbms_output.put_line(‘hello ‘||v_name); end; 存储过程输入参数作为变量进行条件判断 create or replace procedure say_hello( i_opFlag in number ) as v_name varchar2(100); begin
if i_opFlag = 1 then v_name :=’0′; else v_name :=’haha’; end if; dbms_output.put_line(‘hello ‘||v_name); end; 利用存储过程中定义的变量对入参的空值处理： create or replace procedure say_hello( i_name in varchar2 ) as v_name varchar2(100); begin
if i_name is null then v_name :=’0′; else
v_name :=i_name;–将入赋值给定义变量 end if; dbms_output.put_line(‘hello ‘||v_name); end;
5.3 多个参数的存储过程
out输出参数，用于利用存储过程给一个或多个变量赋值，类似于返回值 create or replace procedure say_hello( v_name in varchar2, v_content
out varchar2 ) begin v_content:=’hello’||v_name; end; 调用： declare v_con varchar2(200); –声明变量时带长度 v_in varchar2(20):=’wang’; –赋值 begin
say_hello(v_in,v_con); — 存储过程没有返回值，这里不用接受 dbms_output.put_line(v_con); end;
5.4 in out参数，既赋值又取值
create or replace procedure say_hello(v_name
in out varchar2) as begin v_name:=’hi ‘||v_name; end; 调用： declare v_inout varchar2(20):=’wangsu’; begin say_hello(v_inout); dbms_output.put_line(
v_inout);
–直接输入参数 end;
5.5 对存储过程入参赋缺省值
create or replace procedure say_hello( v_name varchar2
default ‘susu’, v_content varchar2
default ‘hello’ ) as begin dbms_output.put_line(v_name||’ ‘||v_content); end; 调用：(用指明形参名的方式调用更好) begin say_hello(); end; 或 begin say_hello(‘cheng’); end; 或 begin say_hello(v_name=>’cheng’); end;
6、 PLSQL中的function
FUNCTION和PROCEDURE的区别 1、函数有返回值，过程没有，
调用过程不能用变量去接受 2、
函数调用在一个表达式中，
过程则是作为pl/sql程序的一个语句 过程和函数都以编译后的形式存放在数据库中，函数可以没有参数也可以有多个参数并有一个返回值。过程 有零个或多个参数，没有返回值。函数和过程都可以通过参数列表接收或返回零个或多个值，函数和过程的 主要区别不在于返回值，而在于他们的调用方式，
过程是作为一个独立执行语句调用的，
函数以合法的表达式的方式调用
--1、创建函数：获取某部门的工资总和
create or replace function get_salary(
dept_no number,
emp_count out number)
return number
is
v_sum number;
begin
select sum(salary), count(*) into v_sum, emp_count from emp wher emp.id=dept_no;
return v_sum;
exception
when no_data_found then
dbms_output.put_line('你需要的数据不存在！');
when others then
dbms_output.put_line(sqlcode || '----' || sqlerrm);
end get_salary;
--2、函数的调用
--2.1第一种：位置表示法
declare
v_num number;
v_sum number;
begin
v_sum := get_salary(10, v_num);
dbms_output.put_line('部门号为：10的工资总和：'|| v_sum || ', 人数为：'||v_num);
end;
--2.2 名称表示法
declare
v_num number;
v_sum number;
begin
v_sum := get_salary(emp_count => v_num, dept_no => 10);
dbms_output.put_line('部门号为：10的工资总和：'||v_sum ||',人数为:'||v_num);
end;
函数的声明以及调用
带out参数的函数： create or replace function func( v_name in varchar2, v_content out varchar2 ) return varchar2 is begin v_content:=v_name||’ hello’; return v_content; end; 调用： declare v_name varchar2(20); v_name1 varchar2(20); begin v_name1:=func(‘susu’,v_name);–返回v_name值 dbms_output.put_line(v_name1);–打印func结果 dbms_output.put_line(v_name);–打印v_name结果 end; 带in out 参数的函数 create or replace function func( v_name in out varchar2) return varchar2 is begin v_name:=v_name||’ hello’; return ‘cheng’; end; 调用： declare v_inout varchar2(20):=’world’; v_ret varchar2(20); begin v_ret:=func(v_inout);–返回调用v_inout值(作为出参) dbms_output.put_line(v_ret);–打印func结果 dbms_output.put_line(v_inout);–返回v_name结果 end;
7、包
--包头(定义了一个函数和一个过程)
Create or replace package pkg_huxiaohong_product
IS
procedure update_sal(e_name varchar2, newsal number);
function ann_income(e_name varchar2) return number;
END;
--包体（实现上面的函数和过程）
create or replace package body pkg_huxiaohong_product
IS
--过程，没有返回值
proceducre update_sal(e_name varchar2, newsal number)
IS
Begin
update emp1 set sal = newsal where ename = e_name;
End;
--函数，有返回值
function ann_income(e_name varchar2) return number
IS
annsal number;
Begin
select sal * 12 + nvl(common,0) into annsal form emp1
where ename = e_name;
return annsal;
End;
End;
--调用包
--共有元素调用：包名，元素名
--过程调用：
sql>exec pkg_huxiaohong_product.update_sal('scott', 1200);
--函数调用：
declare v_annsal number(7,2);
Begin
v_annsal:=pkg_huxiaohong_product.ann_income('scott');
dbms_output.put_line('年薪为：'|| v_annsal);
End;
-- 删除包
drop package[body[user.] pkg_huxiaohong_product
创建使用包
7.1 记录
--2、声明记录类型
declare
type emp_record is record( --声明一个记录类型
v_name emp.ename%type, --动态获取变量的类型
);
v_emp_record emp_record; --定义一个记录类型的成员变量
begin
select ename into v_emp_record from emp ;
dbms_output.put_line('姓名是'||v_name);
end;
声明记录类型