PLSQL开发笔记和小结
*****************************************PLSQL基本结构*****************************************基本数据类型变量
1. 基本数据类型Number 数字型Int 整数型Pls_integer 整数型,产生溢出时出现错误Binary_integer 整数型,表示带符号的整数Char 定长字符型,最大255个字符Varchar2 变长字符型,最大2000个字符Long 变长字符型,最长2GBDate 日期型Boolean 布尔型(TRUE、FALSE、NULL三者取一)在PL/SQL中使用的数据类型和Oracle数据库中使用的数据类型,有的含义是完全一致的,有的是有不同的含义的。
2. 基本数据类型变量的定义方法变量名 类型标识符 [not null]:=值;declareage number(3):=26; --长度为3,初始值为26begincommit;end;其中,定义常量的语法格式:常量名 constant 类型标识符 [not null]:=值;declarepi constant number(9):=3.1415926;--为pi的数字型常量,长度为9,初始值为3.1415926begincommit;end;
表达式
变量、常量经常需要组成各种表达式来进行运算,下面介绍在PL/SQL中常见表达式的运算规则。1. 数值表达式PL/SQL程序中的数值表达式是由数值型常数、变量、函数和算术运算符组成的,可以使用的算术运算符包括+(加法)、-(减法)、*(乘法)、/(除法
)和**(乘方)等。命令窗口中执行下列PL/SQL程序,该程序定义了名为result的整数型变量,计算的是10+3*4-20+5**2的值,理论结果应该是27。―――――――――――――――――――――――――――――――――――――set serveroutput onDeclareresult integer;beginresult:=10+3*4-20+5**2;dbms_output.put_line('运算结果是:'||to_char(result));end;―――――――――――――――――――――――――――――――――――――dbms_output.put_line函数输出只能是字符串,因此利用to_char函数将数值型结果转换为字符型。2. 字符表达式字符表达式由字符型常数、变量、函数和字符运算符组成,唯一可以使用的字符运算符就是连接运算符“||”。3. 关系表达式关系表达式由字符表达式或数值表达式与关系运算符组成,可以使用的关系运算符包括以下9种。< 小于> 大于= 等于(不是赋值运算符:=)like 类似于in 在……之中<= 小于等于>= 大于等于!= 不等于 或<>between 在……之间关系型表达式运算符两边的表达式的数据类型必须一致。4. 逻辑表达式逻辑表达式由逻辑常数、变量、函数和逻辑运算符组成,常见的逻辑运算符包括以下3种。NOT:逻辑非OR:逻辑或AND:逻辑与运算的优先次序为NOT、AND和OR。
PLSQL函数
PL/SQL程序中提供了很多函数供扩展功能,除了标准SQL语言的函数可以使用外,最常见的数据类型转换函数有以下3个。To_char:将其他类型数据转换为字符型。To_date:将其他类型数据转换为日期型。To_number:将其他类型数据转换为数值型。继续追加中..
系统输出打印
利用pl/sql在数据库服务器端打印一句话:set serveroutput on--设置数据库输出,默认为关闭,每次重新打开窗口需要重新设置。BEGINDBMS_OUTPUT.PUT_LINE('Hello PL/SQL');END;
pl/sql程序中对大小写不敏感(打印声明的变量)―――――――――――――――――――――――――――――――――――――set serveroutput onDECLAREv_char varchar2(20):='a';v_char1 varchar2(20):='b';BEGINDBMS_OUTPUT.PUT_LINE(v_char);DBMS_OUTPUT.PUT_LINE(v_char1);END;pl语句块是pl/sql里最小的编程块,其中可以再嵌套begin endbegindbms_output.put_line('Hello World');dbms_output.put_line('2*3='||(2*3));dbms_output.put_line('what''s');end;―――――――――――――――――――――――――――――――――――――
PL/SQL中的变量声明
所有变量必须在declare中声明,程序中不允许声明。没有初始化的变量默认值为null,屏幕上null是看不见的,命名习惯:PL/SQL中变量一般以v_开头(等同于存储过程中as和begin区域的变量定义习惯)。注意number也能存小数,最长38位,所以以后建议整数都用binary_integer存。long是字符类型,boolean类型不能打印。标准变量类型:数字,字符,时间,布尔。―――――――――――――――――――――――――――――――――――――declarev_number1 number;v_number2 number(3,2) ;v_number3 binary_integer :=1;v_name varchar2(20) :='kettas';v_date date :=sysdate;v_long long :='ni hao';v_b boolean := true;beginif (v_number1 is null) thendbms_output.put_line( 'hello');end if;dbms_output.put_line(v_number1);dbms_output.put_line(v_number2);dbms_output.put_line(v_number3);dbms_output.put_line(v_name);dbms_output.put_line(v_date);dbms_output.put_line(v_long);--dbms_output.put_line(v_b); --执行该句ORACLE提示“调用 'PUT_LINE' 时参数个数或类型错误”end;―――――――――――――――――――――――――――――――――――――
备注:关于声明number(4,3)中括号中的两个数字的意义,前面的数字叫精度,后面的叫刻度。刻度:当刻度为正数的时候,表示四舍五入到小数点后面的位数当刻度为负数的时候,表示四舍五入到小数点前面的位数精度:从数字的最前面不为零开始到刻度精确到的位置v_Number number(4,3):=123.123121、按刻度进行四舍五入得到123.1232、确定刻度精确到的位置123123处,精度为6位(.符号不算)2、根据精度进行判断6位(>4)精度上限值 --报错不能存储number(3,-3):=444451、根据刻度-3进行四舍五入得到440002、小数点向前移动3位44.此位置为刻度精确到的位置3、根据精度进行判断2位(<3)精度上限值 --不报错可存储结果为44000DECLAREv_Number number(4,3):=123.12312;--实际精度6位大于上限精度值4位,提示“ORA-06502: PL/SQL: 数字或值错误 : 数值精度太高”BEGINDBMS_OUTPUT.PUT_LINE(v_Number);END;DECLAREv_Number number(7,3):=4555; --实际精度7位等于上限精度值,可以存储BEGINDBMS_OUTPUT.PUT_LINE(v_Number);END;*****************************************变量赋值方式*****************************************oracle中变量赋值方式是值拷贝而非引用declarev_number1 number:=100;v_number2 number;beginv_number2:=v_number1;v_number1:=200;dbms_output.put_line(v_number1); --200dbms_output.put_line(v_number2); --100end;*****************************************PLSQL复合类型*****************************************记录类型recordrecord类型最常用,声明的时候可以加not null,但必须给初始值,如果record类型一致可以相互赋值,如果类型不同,里面的字段恰好相同,不能互相赋值。引用记录型变量的方法是“记录变量名.基本类型变量名”。―――――――――――――――――――――――――――――――――――――declaretype t_first is record(id number(3),name varchar2(20));v_first t_first;beginv_first.id:=1;v_first.name:='cheng';dbms_output.put_line(v_first.id);dbms_output.put_line(v_first.name);end;
record类型变量间赋值declaretype t_first is record(id number,name varchar2(20));v_first t_first;v_second t_first;beginv_first.id:=1;v_first.name:='susu';v_second:=v_first;--相互赋值v_first.id:=2;v_first.name:='kettas';dbms_output.put_line(v_first.id);dbms_output.put_line(v_first.name);dbms_output.put_line(v_second.id);dbms_output.put_line(v_second.name);end;―――――――――――――――――――――――――――――――――――――表类型变量table语法如下:type 表类型 is table of 类型 index by binary_integer;表变量名 表类型;类型可以是前面的类型定义,index by binary_integer子句代表以符号整数为索引,这样访问表类型变量中的数据方法就是“表变量名(索引符号整数)”。table类型,相当于java中的Map容器,就是一个可变长的数组,key(符号整数索引)必须是整数,可以是负数,value(类型)可以是标量,也可以是record类型。可以不按顺序赋值,但必须先赋值后使用。
1. 定义一维表类型变量―――――――――――――――――――――――――――――――――――――declaretype t_tb is table of varchar2(20) index by binary_integer;v_tb t_tb;beginv_tb(100):='hello';v_tb(98):='world';dbms_output.put_line(v_tb(100));dbms_output.put_line(v_tb(98));end;
类型为record的表类型变量declaretype t_rd is record(id number,name varchar2(20));type t_tb is table of t_rd index by binary_integer;v_tb2 t_tb;beginv_tb2(100).id:=1;v_tb2(100).name:='hello';--dbms_output.put_line(v_tb2(100).id);--dbms_output.put_line(v_tb2(100).name);dbms_output.put_line(v_tb2(100).id||' '||v_tb2(100).name);end;―――――――――――――――――――――――――――――――――――――
2. 定义多维表类型变量该程序定义了名为tabletype1的多维表类型,相当于多维数组,table1是多维表类型变量,将数据表tempuser.testtable中recordnumber为60的记录提取出来
存放在table1中并显示。―――――――――――――――――――――――――――――――――――――declaretype tabletype1 is table of testtable%rowtype index by binary_integer;table1 tabletype1;beginselect * into table1(60) from tempuser.testtable where recordnumber=60;dbms_output.put_line(table1(60).recordnumber||table1(60).currentdate);end;备注:在定义好的表类型变量里,可以使用count、delete、first、last、next、exists和prior等属性进行操作,使用方法为“表变量名.属性”,返回的是数字。set serveroutput ondeclaretype tabletype1 is table of varchar2(9) index by binary_integer;table1 tabletype1;begintable1(1):='成都市';table1(2):='北京市';table1(3):='青岛市';dbms_output.put_line('总记录数:'||to_char(table1.count));dbms_output.put_line('第一条记录:'||table1.first);dbms_output.put_line('最后条记录:'||table1.last);dbms_output.put_line('第二条的前一条记录:'||table1.prior(2));dbms_output.put_line('第二条的后一条记录:'||table1.next(2));end;―――――――――――――――――――――――――――――――――――――
*****************************************%type和%rowtype*****************************************使用%type定义变量,为了让PL/SQL中变量的类型和数据表中的字段的数据类型一致,Oracle 9i提供了%type定义方法。这样当数据表的字段类型修改后,PL/SQL程序中相应变量的类型也自动修改。―――――――――――――――――――――――――――――――――――――create table student(id number,name varchar2(20),age number(3,0));
insert into student(id,name,age) values(1,'susu',23);--查找一个字段的变量
declarev_name varchar2(20);v_name2 student.name%type;beginselect name into v_name2 from student where rownum=1;dbms_output.put_line(v_name2);end;
--查找多个字段的变量declarev_id student.id%type;v_name student.name%type;v_age student.age%type;beginselect id,name,age into v_id,v_name,v_age from student where rownum=1;dbms_output.put_line(v_id||' '||v_name||' '||v_age);end;
--查找一个类型的变量,推荐用*
declarev_student student%rowtype;beginselect * into v_student from student where rownum=1;dbms_output.put_line(v_student.id||' '||v_student.name||' '||v_student.age);end;
--也可以按字段查找,但是字段顺序必须一样,不推荐这样做declarev_student student%rowtype;beginselect id,name,age into v_student from student where rownum=1;dbms_output.put_line(v_student.id||' '||v_student.name||' '||v_student.age);end;
declarev_student student%rowtype;beginselect id,name,age into v_student.id,v_student.name,v_student.age from student where id=1;--select * into v_student.id,v_student.name,v_student.age from student where id=1;dbms_output.put_line();end;―――――――――――――――――――――――――――――――――――――备注:insert,update,delete,select都可以,create table,drop table不行。DPL,DML,和流程控制语句可以在pl/sql里用,但DDL语句不行。declarev_name student.name%type:='wang';begininsert into student(id,name,age) values(2,v_name,26);end;begininsert into student(id,name,age) values(5,'hehe',25);end;
declarev_name student.name%type:='hexian';beginupdate student set name=v_name where id=1;end;
beginupdate student set name='qinaide' where id=2;end;―――――――――――――――――――――――――――――――――――――*****************************************PLSQL变量的可见空间*****************************************变量的作用域和可见性,变量的作用域为变量申明开始到当前语句块结束。当外部过程和内嵌过程定义了相同名字的变量的时候,在内嵌过程中如果直接写这个变量名是没有办法访问外部过程的变量的,可以通过给外部过程定义一个名字<
*****************************************PLSQL流程控制*****************************************if判断declarev_b boolean:=true;begin if v_b thendbms_output.put_line('ok');end if;end;
if else判断declarev_b boolean:=true;beginif v_b thendbms_output.put_line('ok');elsedbms_output.put_line('false');end if;end;
if elsif else判断declarev_name varchar2(20):='cheng';beginif v_name='0701' thendbms_output.put_line('0701');elsif v_name='cheng' thendbms_output.put_line('cheng');elsedbms_output.put_line('false');end if;end;
loop循环,注意推出exit是推出循环,而不是推出整个代码块declarev_i binary_integer:=0;beginloopif v_i>10 thenexit;end if;v_i:=v_i+1;dbms_output.put_line('hehe');end loop;dbms_output.put_line('over');end;
loop简化写法declarev_i binary_integer :=0;beginloopexit when v_i>10;v_i :=v_i+1;dbms_output.put_line('hehe');end loop;dbms_output.put_line('over');end;
while循环declarev_i binary_integer:=0;beginwhile v_i<10 loopdbms_output.put_line('hello'||v_i );v_i:=v_i+1;end loop;dbms_output.put_line('over');end;
for循环,注意不需要声明变量beginfor v_i in 0..10 loopdbms_output.put_line('hello'||v_i);end loop;dbms_output.put_line('over');end;
*****************************************PLSQL异常处理*****************************************1、声明异常异常名 EXCEPTION;2、抛出异常RAISE 异常名3、处理异常抛出异常后的逻辑代码不会被继续执行
异常的定义使用―――――――――――――――――――――――――――――――――――――begindbms_output.put_line(1/0);exceptionwhen others thendbms_output.put_line('error');end;
declaree_myException exception;begindbms_output.put_line('hello');raise e_myException; --raise抛出异常,用此关键字,抛出后转到自定义的e_myException ,执行其里面的putline函数后,再跳到end处,结束PL/SQL块,raise接下面的2句不会继续执行。dbms_output.put_line('world');dbms_output.put_line(1/0);exceptionwhen e_myException thendbms_output.put_line(sqlcode); --当前会话执行状态,错误编码dbms_output.put_line(sqlerrm); --当前错误信息dbms_output.put_line('my error');when others thendbms_output.put_line('error');end;―――――――――――――――――――――――――――――――――――――*****************************************PLSQL游标和goto语句*****************************************备注:下面提到的游标为静态cursor,包括显示和隐式。游标,从declare、open、fetch、close是一个完整的生命旅程。当然了一个这样的游标是可以被多次open进行使用的,显式cursor是静态cursor,她的作用域是全局的,但也必须明白,静态cursor也只有pl/sql代码才可以使用它。静态游标变量是在定义时就必须指定SQL语句。
cursor 游标(结果集)用于提取多行数据,定义后不会有数据,使用后才有。一旦游标被打开,就无法再次打开(可以先关闭,再打开)。declarecursor c_student is select * from book;beginopen c_student;close c_student;end;
第二种游标的定义方式,用变量控制结果集的数量。declarev_id binary_integer;cursor c_student is select * from book where id>v_id;beginv_id:=10;open c_student;close c_student;end;
第三种游标的定义方式,带参数的游标,用的最多。declarecursor c_student(v_id binary_integer) is select * from book where id>v_id;beginopen c_student(10);close c_student;end;
游标的使用,一定别忘了关游标。declarev_student book%rowtype;cursor c_student(v_id binary_integer) is select * from book where id>v_id;beginopen 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。
下面是几个实例:create table BOOK(ID VARCHAR2(10) not null,BOOKNAME VARCHAR2(10) not null,PRICE VARCHAR2(10) not null,CID VARCHAR2(10) not null);
--insertcreate or replace procedure say_hello(i_name in varchar2,o_result_msg out varchar2)asv_price varchar2(100);e_myException exception;
begininsert into book(id,bookname,price) values (1,2,3);o_result_msg := 'success';exceptionwhen others thenrollback;o_result_msg := substr(sqlerrm, 1, 200);end;
--update or deletecreate or replace procedure say_hello(i_name in varchar2,o_result_msg out varchar2)asv_price varchar2(100);e_myException exception;
beginupdate book set price = '55' where bookname = i_name;delete from book where bookname = i_name;if sql%notfound thenraise e_myException;end if;/*if sql%rowcount = 0 then--写法2raise e_myException;end if;*/o_result_msg := 'success';exceptionwhen e_myException thenrollback;o_result_msg := 'update or delete dail';end;
--selectcreate or replace procedure say_hello(i_name in varchar2,o_result_msg out varchar2)asv_price varchar2(100);e_myException exception;
beginselect price into v_price from book where bookname = i_name;o_result_msg := 'success';exceptionwhen no_data_found thenrollback;o_result_msg := 'select into dail';end;loop方式遍历游标declarev_bookname varchar2(100);cursor c_book(i_id number) is select bookname from book where id = i_id;beginOpen c_book(i_id);LoopFetch c_book into v_bookname;exit when c_student%notfound;update book set price = '33' where bookname = v_bookname;End Loop;Close c_book;end;或declarev_bookname varchar2(100);cursor c_book(i_id number) is select bookname from book where id = i_id;beginOpen c_book(i_id);Fetch c_book into v_bookname;While c_book%FoundLoopupdate book set price = '33' where bookname = v_bookname;Fetch c_book into v_bookname;End Loop;Close c_book;end;
while循环遍历游标,注意,第一次游标刚打开就fetch,%found为null,进不去循环解决方法:while nvl(c_student%found,true) loopdeclarev_bookname varchar2(100);cursor c_book(i_id number) is select bookname from book where id = i_id;beginOpen c_book(i_id);while nvl(c_book%found,true) --或这种写法:while c_book%found is null or c_book%found loopFetch c_book into v_bookname;update book set price = '33' where bookname = v_bookname;End Loop;Close c_book;end;
for循环遍历,最简单,用的最多,不需要 声明v_student,Open和Close游标和fetch操作(不用打开游标和关闭游标,实现遍历游标最高效方式)declarecursor c_book(i_id number) is select bookname from book where id = i_id;beginfor cur in c_book(i_id) --直接将入参i_id传入cursor即可loopupdate book set price = '53' where bookname = cur.bookname;end loop;end;
goto例子,一般不推荐使用goto,会使程序结构变乱declarei number:=0;beginif i=0 thengoto hello;end if;<
存储过程入参,不论类型,缺省情况下值都为null,入参和出参不能有长度,其中关键字as可以替换成is,存储过程中变量声明在as和begin之间,同时,存储过程中可以再调用其它的存储过程,如果要保证存储过程之间的事务处理不受影响,可以定义为自治事务。create or replace procedure say_hello(v_name in varchar2,v_flag number,o_ret out number)asbeginif v_name is null and v_flag is null then --v_name和v_flag都等于nullo_ret := 10;elseo_ret := 100;end if;end;
对于入参为null情况下给予缺省值create or replace procedure say_hello(i_name in varchar2,i_flag number,o_ret out number)asv_name varchar2(100);beginif i_name is null thenv_name := '0';elsev_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值
带一个参数的存储过程输入参数in,输入参数不能进行:=赋值,但可以将它赋给as后面定义的变量;输入参数in,可以作为变量进行条件判断;默认不写就是in;存储过程没有重载,这个有参的say_hello会替代已经存在的无参say_hello。
create or replace procedure say_hello(v_name in varchar2)asbegin--v_name:='a'; --存储过程入参v_name不能做为赋值目标dbms_output.put_line('hello '||v_name);end;
存储过程输入参数作为变量进行条件判断create or replace procedure say_hello(i_opFlag in number)asv_name varchar2(100);beginif i_opFlag = 1 thenv_name :='0';elsev_name :='haha';end if;dbms_output.put_line('hello '||v_name);end;
利用存储过程中定义的变量对入参的空值处理:create or replace procedure say_hello(i_name in varchar2)asv_name varchar2(100);beginif i_name is null thenv_name :='0';elsev_name :=i_name;--将入赋值给定义变量end if;dbms_output.put_line('hello '||v_name);end;
多个参数的存储过程create or replace procedure say_hello(v_first_name in varchar2,v_last_name in varchar2)asbegindbms_output.put_line('hello '||v_first_name||'.'||v_last_name);end;
out输出参数,用于利用存储过程给一个或多个变量赋值,类似于返回值create or replace procedure say_hello(v_name in varchar2,v_content out varchar2)beginv_content:='hello'||v_name;end;
调用:declarev_con varchar2(200);v_in varchar2(20):='wang';beginsay_hello(v_in,v_con);dbms_output.put_line(v_con);end;
in out参数,既赋值又取值create or replace procedure say_hello(v_name in out varchar2)asbeginv_name:='hi '||v_name;end;
调用:declarev_inout varchar2(20):='wangsu';beginsay_hello(v_inout);dbms_output.put_line(v_inout);end;
对存储过程入参赋缺省值create or replace procedure say_hello(v_name varchar2 default 'susu',v_content varchar2 default 'hello')asbegindbms_output.put_line(v_name||' '||v_content);end;
调用:(用指明形参名的方式调用更好)beginsay_hello();end;或beginsay_hello('cheng');end;或beginsay_hello(v_name=>'cheng');end;
*****************************************PLSQL中的function*****************************************FUNCTION和PROCEDURE的区别1、函数有返回值,过程没有2、函数调用在一个表达式中,过程则是作为pl/sql程序的一个语句过程和函数都以编译后的形式存放在数据库中,函数可以没有参数也可以有多个参数并有一个返回值。过程有零个或多个参数,没有返回值。函数和过程都可以通过参数列表接收或返回零个或多个值,函数和过程的主要区别不在于返回值,而在于他们的调用方式,过程是作为一个独立执行语句调用的,函数以合法的表达式的方式调用create or replace function func(v_name in varchar2)return varchar2isbeginreturn(v_name||' hello');end;
调用:declarev_name varchar2(20);beginv_name:=func('cheng');dbms_output.put_line(v_name);end;
带out参数的函数create or replace function func(v_name in varchar2,v_content out varchar2)return varchar2isbeginv_content:=v_name||' hello';return v_content;end;
调用:declarev_name varchar2(20);v_name1 varchar2(20);beginv_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 varchar2isbeginv_name:=v_name||' hello';return 'cheng';end;
调用:declarev_inout varchar2(20):='world';v_ret varchar2(20);beginv_ret:=func(v_inout);--返回调用v_inout值(作为出参)dbms_output.put_line(v_ret);--打印func结果dbms_output.put_line(v_inout);--返回v_name结果end;
就爱阅读www.92to.com网友整理上传,为您提供最全的知识大全,期待您的分享,转载请注明出处。
来源: http://www.92to.com/bangong/2017/04-07/20089817.html