7.PLSQL

PLSQL的优点：1、允许包括异常处理和控制结构2、经常使用的查询可以存储成PLSQL块，由应用程序来调用。

declarec_rateincrease constant number(3,2):=1.2;/*（常量申明）*/v_title varchar2(30) NOT NULL;v_retail books.retail%type;/*（赋予和原有字段一样的类型）*/v_newretail number(5,2);beginselect title,retail,retail*c_rateincreaseinto v_title,v_retail,v_newretailfrom bookswhere isbn='2147426552'; /*(查询只能查询出一条记录)*/dbms_output.put_line('the new price for '||v_title||'is$'||v_newretail);/*（输出变量）*/end;PLSQL有三部分（申明、执行和异常）其中执行是必须的。

在SQL*PLUS中执行PLSQL需要先输入SET SERVEROUTPUT ON 运行代码，然后打一个/执行语句块。

标准命名方式：c_常量 v_变量 g_全局变量------------------------------------------------------------------- declarev_customer# number(4);v_firstname customers.firstname%type;beginselect customer#,firstnameinto v_customer#,v_firstnamefrom customerswhere customer#=1003;dbms_output.put_line( v_customer# );dbms_output.put_line( v_firstname );end;declarev_c number(2);v_f number(2);v_sum number(2);beginv_c:=3;v_f:=5;v_sum :=v_c+v_f;dbms_output.put_line(v_sum);end;begininsert intos035(sid,sname,age,tid)values('s10008','kkk',27,'t102');update s035 set sname='lll'where sid='s10007';commit;end;/*（修改能同时修改多条记录，查询输出只能是一条）*/beginupdate emp set sal=sal*decode(deptno,10,1.05,20,1.1,30,1.15,40,1.2 ,1);commit;end;--------------------------------------------------------------- declarev_sname s035.sname%type;beginv_sname:='joins';update s035 set sname=v_sname where sid='s10007';commit;end;---------------------------------------------------------------- declarev_sname s035.sname%type;beginselect snameinto v_snamefrom s035where sid='s10002';update s035 set sname=v_sname where sid='s10007';commit;end;/*PLSQL语句块中只能执行查询，插入、删去语言，不能执行创建表的语言*/--------------------------------------------------------------------------------------------------------------------------- declarev_sname s035.sname%type :='&name';/*（弹出对话框来接受输入的值）*/ beginupdate s035 set sname=v_sname where sid='s10007';commit;end;select * from s035;--------------------------------------------------------------- declarev_sname s035.sname%type :='&name';v_age number(2):=&age;beginupdate s035 set sname=v_sname where sid='s10007';update s035 set age=v_age where sid='s10007';commit;end;select * from s035;--------------------------------------------------------------------------------------------------------------------------- declarev_c number(2):=&long;v_k number(2):=&width;v_m number(4);beginv_m:=v_c*v_k;dbms_output.put_line('面积是'||v_m);end;控制语句/*if循环*/declarev_cj number(3):=&你的成绩是：;v_dj varchar2(2);beginif v_cj>90thenv_dj:='A';ELSIF V_CJ>80thenv_dj:='B';ELSIF V_CJ>70thenv_dj:='C';ELSIF V_CJ>60thenv_dj:='D';ELSEv_dj:='E';END IF;dbms_output.put_line('你的成绩等级是'||v_dj);end;-------------------------------------------------------------------------------------------------------------------------- /*case选择*/v_grade char(2):=upper('&grade:');( &grade:不能有空格)v_appraisal varchar2(20);beginv_appraisal:=case v_gradewhen'A'THEN'excellent'when'B'THEN'Very good'when'C'THEN'good'else'no such grade!'end;dbms_output.put_line('grade:'||v_grade||'appraisal:'||v_appraisal); end;------------------------------------------------------------declarev_cj number(3):=&你的成绩是：;/*(冒号要在汉语输入法下输入，在英语输入法下不接受)*/v_grade varchar2(2);v_appraisal varchar2(20);beginif v_cj>90thenv_grade:='A';ELSIF V_CJ>80thenv_grade:='B';ELSIF V_CJ>70thenv_grade:='C';ELSIF V_CJ>60thenv_grade:='D';ELSEv_grade:='E';END IF;v_appraisal:=case v_gradewhen'A'THEN'excellent'when'B'THEN'Very good'when'C'THEN'good'when'D'THEN'bad'else' very bad!'end;dbms_output.put_line('你的成绩评论是'||v_appraisal);end;--------------------------------------------------------------/*loop end loop循环*/v_num number(2):=0;beginloopv_num:=v_num+2;dbms_output.put_line('1-10偶数有：'||v_num);exit when v_num=10;end loop;end;(结果不包括初值，包括退出循环的值。

打印句块和退出句块对结果会产生影响)-------------------------------------------------------------- declarev_num number(2):=0;beginloopv_num:=v_num+2;exit when v_num>10;dbms_output.put_line(v_num);end loop;end;/*while，end loop 循环*/declarev_num number(2):=2;beginwhile v_num<11loopdbms_output.put_line(v_num);v_num:=v_num+2;end loop;end;/*用FOR循环输出1-50内的偶数*/beginfor i in1..50 loopif i mod2=0thendbms_output.put_line(i);end if;end loop;end;------------------------------------------------------------------------------------------------------------------------------ /*利用WHILE循环和FOR循环来输出数组*/declarev_h number(2):=1;beginwhile v_h<4loopfor v_l in1..3 loopdbms_output.put_line(v_h||' '||v_l);end loop; /*用FOR循环来控制列值*/v_h:=v_h+1;end loop; /*用WHILE循环来控制行值*/end;-------------------------------------------------------------------- /*利用IF的嵌套循环来计算商品的价格*/declarec_price constant number(5,2):=&输入价格; /*不能输入汉语状态下的冒号*/v_price number(5,2);v_month number(2):=&输入月份;v_type varchar2(2):=UPPER('&输入会员类型');beginif(v_month >=4and v_month<=6) or(v_month >=9and v_month<=11) then dbms_output.put_line('现在是旺季');if v_type ='A'THEN v_price:=c_price*0.6;elsif v_type='B'THEN v_price:=c_price*0.7;else v_price:=c_price;end if;ELSE dbms_output.put_line('现在是淡季');if v_type ='A'THEN v_price:=c_price*0.8;elsif v_type='B'THEN v_price:=c_price*0.9;else v_price:=c_price;end if;end if;dbms_output.put_line('你的商品打折后价格是：'||v_price);end;游标（申明，使用，关闭）/*游标的基本使用方法*/declarev_title books.title%type ;v_name %type;cursor bookpub_cursor isselect title,namefrom booksnatural JOIN publisherwhere category='COMPUTER'; /*游标的申明*/beginopen bookpub_cursor; 打开游标fetch bookpub_cursor into v_title,v_name; /*用FETCH INTO使用游标*/dbms_output.put_line('bookstitle '||v_title||' publishername'||v_name);close bookpub_cursor; /*关闭游标*/end;--------------------------------------------------------------------- /*利用LOOP循环和%notfound属性输出查询的全部记录*/declarev_title books.title%type ;v_name %type;cursor bookpub_cursor isselect title,namefrom booksnatural JOIN publisherwhere category='COMPUTER';beginopen bookpub_cursor;loopfetch bookpub_cursor into v_title,v_name;exit when bookpub_cursor%notfound;dbms_output.put_line('bookstitle '||v_title||' publishername'||v_name);end loop;close bookpub_cursor;end;---------------------------------------------------------------------/*用WHILE循环输出查询的记录*/declarev_title books.title%type ;v_name %type;cursor bookpub_cursor isselect title,namefrom booksnatural JOIN publisherwhere category='COMPUTER';beginopen bookpub_cursor;fetch bookpub_cursor into v_title,v_name; /*先执行一次FETCH,不然%found属性无法进行初始化*/while bookpub_cursor%found loopdbms_output.put_line('bookstitle: '||v_title||' publishername:'||v_name);fetch bookpub_cursor into v_title,v_name; /*（先输出第一条记录）不然就会丢失第一条记录 */end loop;close bookpub_cursor;end;------------------------------------------------------------------- /*利用游标输出FL州的客户号和姓名（FOR循环） */Declarev_customer# customers.customer#%type ;v_firstname customers.firstname%type;cursor customers_cursor isselect customer#,firstnamefrom customerswhere state='FL';r_customers customers%rowtype; /*r_customers是一个记录类型只能存一行的值customers%rowtype*/beginfor r_customers in customers_cursor loop/*（利用FOR循环的条件判定复合条件的记录，循环输出）*/dbms_output.put_line('customer#:'||r_customers.customer#||'firstname:'||r_customers.firstname);end loop;end;===========================================================/*利用FOR 循环来输出游标，计算总数*/declarev_totprofit number(5,2):=0;cursor profit_cursor isselect title, retail,cost/*不能查询字段retail-cost，不然后面的记录集不能设*/from books ; /*(申明一个游标)*/r_profit books%rowtype; /*（设一个记录集）*/beginfor r_profit in profit_cursor loopdbms_output.put_line('title:'||r_profit.title||'profit:'||(r_profit.r etail-r_profit.cost)); /*输出每本书的利润r_profit.retail-r_profit.cost要加括号*/v_totprofit:=v_totprofit+(r_profit.retail-r_profit.cost); /*计算书的总利润*/dbms_output.put_line('totleprofit:'||v_totprofit);end loop;end;--------------------------------------------------------------------------------------------------------------------------------/*利用ROWCOUNT来限制显示多少条记录*/declarev_title books.title%type ;v_name %type;cursor bookpub_cursor isselect title,namefrom booksnatural JOIN publisherwhere category='COMPUTER';beginopen bookpub_cursor;fetch bookpub_cursor into v_title,v_name;while bookpub_cursor%rowcount <3and bookpub_cursor%found loopdbms_output.put_line('bookstitle: '||v_title||' publishername: '||v_name||' '||bookpub_cursor%rowcount);fetch bookpub_cursor into v_title,v_name;end loop;dbms_output.put_line(bookpub_cursor%rowcount);/*输出显示的条数*/ close bookpub_cursor;end;declarev_sid s035.sid%type:='&sid';begindelete from s035 where sid=v_sid;dbms_output.put_line('成功删去记录');if sql%notfound thendbms_output.put_line('没有记录');end if ;commit;end;---------------------------------------------------------------------------------------------------------------------- declarev_tid t035.tid%type:='&tid';begindelete from s035 where tid=v_tid;if sql%notfound thendbms_output.put_line('该老师所带学生信息不存在');elsedbms_output.put_line('成功删去该老师所带学生记录');end if ;delete from t035 where tid=v_tid;if sql%notfound thendbms_output.put_line('该老师信息不存在');elsedbms_output.put_line('成功删去该老师记录');end if ;commit;end;异常/*三种已定义的异常使用*/declarev_title books.title%type;beginselect titleinto v_titlefrom books;exceptionwhen no_data_found thendbms_output.put_line('没有数据');when too_many_rows thendbms_output.put_line('数据太多超过一条');when zero_divide thendbms_output.put_line('不能除0');end;--------------------------------------------------------------- /*自定义异常*/declareid_already_in_use exception;pragma exception_init(id_already_in_use,-00001); /*绑定错误号*/ begininsert into s035(sid,sname)values('s101','aaa');insert into s035(sid,sname)values('s102','aaa');exceptionwhen id_already_in_use thendbms_output.put_line('主键重复');end;-----------------------------------------------------------/*用户自定义RAISE触发异常*/declarev_tid t035.tid%type:='&tid';no_student exception;begindelete from s035 where tid=v_tid;if sql%notfound thenraise no_student; /*当没有数据记录时认为是异常*/end if;exceptionwhen no_student thendbms_output.put_line('该老师没有带任何学生');when others thendbms_output.put_line('其他错误');end;------------------------------------------------------------------- declarev_tid t035.tid%type:='&tid';v_count number(2);no_student exception;beginselect count(*)into v_countfrom s035where tid=v_tid;if v_count=0thenraise no_student; /*当没有数据记录时认为是异常*/elsedbms_output.put_line('该老师带学生数：'||v_count);end if;exceptionwhen no_student thendbms_output.put_line('该老师没有带任何学生');when others thendbms_output.put_line('其他错误');end;----------------------------------------------------------------- 存储过程与函数过程和函数统称为PLSQL数据块子程序，函数与过程的唯一区别是函数总会有返回值，而过程不一定有返回值（函数）函数的参数传递有三种方法：（1）位置表示法：即放在相应的位置上（2）名称表示法b_category=>’COMPUTER’（3）混合表示法：混合表示法中位置表示的参数必须放在名称表示的前面没有输出变量的函数过程create or replace function get_name035/*(函数名) */(t1_id varchar2)/*（参数及其类型可以输出，默认输入，不标明类型字符数）*/return varchar2isResult varchar2(20);/*（返回值的类型及其字符数）*/beginselect snameinto resultfrom s035where sid =t1_id; /*（函数的过程）*//* 可以添加异常处理 exception*/return(Result);end;使用自定义函数get_name035select get_name035('s10002') from dual;有输出变量的函数过程 T1_agecreate or replace function get_s035(t1_sid varchar2,t1_age out number)/*(输出变量要用OUT标出)*/return varchar2isResult varchar2(20);beginselect sname,ageinto result,t1_agefrom s035where sid=t1_sid;return(Result);end ;必须使用PLSQL语句块来调用有输出变量的函数过程declarev_sid varchar2(6):='&sid';v_sname varchar(20);v_age number(20);beginv_sname:=get_s035(v_sid,v_age);dbms_output.put_line(v_sname||v_age);end;------------------------------------------------------------------ create or replace function get_avgprofit(b_category varchar2,b_count out number)return number isb_avg number(5,2);beginselect avg(retail-cost),count(*) into b_avg,b_countfrom bookswhere category=b_category;return(b_avg);exceptionwhen no_data_found thendbms_output.put_line('没有数据');when too_many_rows thendbms_output.put_line('数据太多使用游标');when others thendbms_output.put_line('其他错误');end get_avgprofit;/*调用get_avgprofit函数来计算图书利润及数量*/declarev_category books.category%type:='&图书种类：';b_count number(2) ;b_avg number(5,2);beginb_avg:=get_avgprofit('COMPUTER',b_count);dbms_output.put_line('COMPUTER种类的图书平均利润：'||b_avg||'总数量：'||b_count);end;（存储过程）定义带异常的存储过程create or replace procedure delbook035(t1_isbn in books.isbn%type) as no_result exception ;begindelete from books where isbn=t1_isbn;if sql%notfound thenraise no_result;elsedbms_output.put_line('图书编号为'||t1_isbn||'的记录已经删去');end if;exceptionwhen no_result thendbms_output.put_line('此编号没有记录无法删去');when others thendbms_output.put_line('其他异常');commitend ;调用存储过程：set serveroutput on;exec delbook(‘1001’);---------------------------------------------------------------------------------------------------------------------------- 有多条返回值的存储过程：create or replace procedure booksnum035(t1_num number,t1_name out varchar2,t1_contact out varchar2 ) isbeginselect name,contactinto t1_name,t1_contactfrom publisherwhere pubidin (select pubidfrom booksgroup by pubidhaving count(*)>t1_num);end booksnum035;调用存储过程有单条返回值的存储过程create or replace procedure proc_demo035(b_isbn varchar2default'1001', b_title out varchar2,b_profit out number) isbeginselect title,retail-costinto b_title,b_profitfrom bookswhere isbn=b_isbn;exceptionwhen no_data_found thendbms_output.put_line('没有数据');when others thendbms_output.put_line('其他异常');end proc_demo035;调用函数declarev_title books.title%type;v_profit number(5,2);beginproc_demo035('3437212690',v_title,v_profit);dbms_output.put_line('3437212690'||v_title||v_profit);proc_demo035(b_title=>v_title,b_profit=>v_profit);dbms_output.put_line('1059831198'||v_title||v_profit);end;--创建存储过程create or replace procedure sp_UpdName(t1_id in t1.id%type , t1_name in %type)asno_result exception;beginupdate t1 set name=t1_name where id = t1_id;if SQL%NOTFOUND thenraise no_result;end if;commit;dbms_output.put_line('修改成功！');exceptionwhen no_result thendbms_output.put_line('您修改的数据不存在！');when others thendbms_output.put_line('发生其他错误！');end;------------------------------------------------------------------------------------------ --调用存储过程set serveroutput on;exec sp_UpdName(1,'a111');。