2 PL/SQL的块结构和数据类型 块结构的特点 嵌套 begin ...... begin ...... exception ...... end; exception ...... end; 标识符： 不能超过30个字符 第一个字符必须为字母 其余字符可以是字母，数字，＄，_，或# 不区分大小写形式 如果用双引号括起来，则字符顺序可以任意排列，并区分大小写形式 无SQL保留字 数据类型 数字型： 整数，实数，以及指数 字符串： 用单引号括起来 若在字符串表示单引号，则使用两个单引号 字符串长度为零（两个单引号之间没有字符），则表示NULL 字符： 长度为1的字符串 数据定义 语法 标识符[常数] 数据类型[NOT NULL][:=PL/SQL表达式]; ':='表示给变量赋值 数据类型包括 数字型 number(7,2) 字符型 char(120) 日期型 date 布尔型 boolean（取值为true,false或null，不存贮在数据库中） 日期型 anniversary date:='05-JUL-95'; project_completion date; 布尔型 over_budget boolean not null:=false; available boolean; （初始值为NULL） %type类型匹配 books_printed number(6); books_sold book_printed%type; manager_name emp.ename%type; 变量赋值 变量名:=PL/SQL表达式 numvar:=5; boolvar:=true; datevar:='11-JUN-87'; 字符型、数字型表达式中的空值 null+<数字>=null（空值加数字仍是空值） null><数字>=null（空值与数字进行比较，结果仍是空值） null||'字符串'='字符串'（null即'） （空值与字符串进行连接运算，结果为原字符串） 变量作用范围 标识符在宣言它的块中有效 标识符如果不在子块中重新定义，则在PL/SQL块的所有子块中同样有效 重新定义后的标识符，作用范围仅在本子块中有效 例 declare e_mess char(80); begin /*子块1*/ declare v1 number(4); begin select empno into v1 from emp where job='president'; exception when too_many_rows then insert into job_errors values('more than one president'); end; /*子块2*/ declare v1 number(4); begin select empno into v1 from emp where job='manager'; exception when too_many_rows then insert into job_errors values('more than one manager'); end; exception when others then e_mess:=substr(sqlerrm,1,80); insert into general errors values(e_mess); end; --------- 22 SQL和PL/SQL 插入 declare my_sal number(7,2):=3040.55; my_ename char(25):='wanda'; my_hiredate date:='08-SEP-88'; begin insert into emp (empno,enmae,job,hiredate,sal,deptno) values(2741,my_ename,'cab driver',my_hiredate,my_sal,20); end; 删除 declare bad_child_type char(20):='naughty'; begin delete from santas_gift_list where kid_rating=bad_child_type; end; 事务处理 commit[WORK]; rollback[WORK]; （关键字WORK可选，但对命令执行无任何影响） savepoint 标记名;（保存当前点） 在事务中标记当前点 rollback [WORK] to [SAVEPOINT] 标记名;（回退到当前保存点） 取消savepoint命令之后的所有对数据库的修改 关键字WORK和SAVEPOINT为可选项，对命令执行无任何影响 函数 PL/SQL块中可以使用SQL命令的所有函数 insert into phonebook(lastname) value(upper(my_lastname)); select avg(sal) into avg_sal from emp; 对于非SQL命令，可使用大多数个体函数 不能使用聚组函数和参数个数不定的函数，如 x:=sqrt(y); lastname:=upper(lastname); age_diff:=months_between(birthday1,birthday2)/12; 赋值时的数据类型转换 4种赋值形式: 变量名:=表达式 insert into 基表名 values(表达式1,表达式2,...); update 基表名 set 列名=表达式; select 列名 into 变量名 from ...; 数据类型间能进行转换的有: char转成number number转成char char转成date date转成char 例 char_var:=nm_var; 数字型转换成字符型 date_var:='25-DEC-88'; 字符型转换成日期型 insert into 表名(num_col) values('604badnumber'); 错误，无法成功地转换数据类型 --------- 4 条件控制 例 declare num_jobs number(4); begin select count(*) into num_jobs from auditions where actorid=&&actor_id and called_back='yes'; if num_jobs>100 then update actor set actor_rating='word class' where actorid=&&actor_id; elsif num_job=75 then update actor set actor_rating='daytime soaps' where actorid=&&actor_id; else update actor set actor_rating='waiter' where actorid=&&actor_id; end if; end if; commit; end; -------- 5 循环 语法 loop ...... end loop; exit;（退出循环） exit [when];（退出循环，当满足WHEN时） 例1 declare ctr number(3):=0; begin loop insert into table1 values('tastes great'); insert into table2 values('less filling'); ctr:=ctr+1; exit when ctr=100; end loop; end; （注：如果ctr取为NULL，循环无法结束） 例2 FOR语法 for 变量<范围> loop ...... end loop; declare my_index char(20):='fettucini alfredo'; bowl char(20); begin for my_index in reverse 21..30 loop insert into temp(coll) values(my_index); /*循环次数从30到21*/ end loop; bowl:=my_index; end; 跟在in reverse后面的数字必须是从小到大的顺序，必须是整数，不能是变量或表达式 ---------- 6 游标 显式游标 打开游标 open <游标名> 例 open color_cur; 游标属性 %notfound %found %rowcount %isopen 例 fetch my_cur into my_var; while my_cur %found loop (处理数据) fetch my_cur into my_var; exit when my_cur %rowcount=10; end loop; %notfound属性 取值情况如下： fetch操作没有返回记录，则取值为true fetch操作返回一条记录，则取值为false 对游标无fetch操作时，取值为null <游标名> %notfound 例 if color_cur %notfound then... 注：如果没有fetch操作，则<游标名> %notfound将导致出错， 因为%notfound的初始值为NULL。 关闭游标 close <游标名> 例 close color_cur; 游标的FOR循环 语法 for <记录名> in <游标名> loop <一组命令> end loop; 其中： 索引是建立在每条记录的值之上的 记录名不必声明 每个值对应的是记录名，列名 初始化游标指打开游标 活动集合中的记录自动完成FETCH操作 退出循环，关闭游标 隐式游标 隐式游标是指SQL命令中用到的，没有明确定义的游标 insert,update,delete,select语句中不必明确定义游标 调用格式为SQL% 存贮有关最新一条SQL命令的处理信息 隐式游标的属性 隐式游标有四个属性 SQL%NOTFOUND SQL%FOUND SQL%ROWCOUNT：隐式游标包括的记录数 例： delete from baseball_team where batting_avg<100; if sql%rowcount>5 thn insert into temp values('your team needs help'); end if; SQL%ISOPEN：取值总为FALSE。SQL命令执行完毕，PL/SQL立即关闭隐式游标。 --------- 7 标号 GOTO语句 用法： goto you_are_here; 其中you_are_here是要跳转的语句标号 标号必须在同一组命令，或是同一块中使用 正确的使用 <>（标号） x:=x+1 if a>b then b:=b+c; goto dinner; end if; 错误的使用 goto jail; if a>b then b:=b+c; <>（标号） x:=x+1; end if; 标号：解决意义模糊 标号可用于定义列值的变量 <> declare deptno number:=20; begin update emp set sal=sal*1.1 where deptno=sample.deptno; commit; end sample; 如果不用标号和标号限制符，这条命令将修改每条记录。 ---------- 8 异常处理 预定义的异常情况 任何ORACLE错误都将自动产生一个异常信息 一些异常情况已命名，如： no_data_found 当SELECT语句无返回记录时产生 too_many_rows 没有定义游标，而SELECT语句返回多条记录时产生 whenever notfound 无对应的记录 用户定义的异常情况 由用户自己获取 在DECLARE部分定义： declare x number; something_isnt_right exception; 用户定义的异常情况遵循一般的作用范围规则 条件满足时，获取异常情况：raise something_isnt_right 注意：同样可以获取预定义的异常情况 exception_init语句 允许为ORACLE错误命名 调用格式： pragma exception_init(<表达式>,); 例 declare deadlock_detected exception; pragma exception_init(deadlock_detected,-60); raise语句 单独使用RAISE命令，可再一次获取当前的异常情况（就象异常情况被重复处理了一样）。 在异常处理中，此语句只能单独使用。 异常处理标识符 一组用于处理异常情况的语句： exception when <表达式> or [表达式...] then <一组语句> ... when others then--最后一个处理 <一组语句> end;既结束PL/SQL块部分，也结束异常处理部分 -------- 练习与答案 1: 接收contract_no和item_no值，在inventory表中查找，如果产品： 已发货，在arrival_date中赋值为今天后的7天 已订货，在arrival_date中赋值为今天后的一个月 既无订货又无发货，则在arrival_date中赋值为今天后的两个月， 并在order表中增加一条新的订单记录。 product_status的列值为'shipped'和'ordered' inventory: product_id number(6) product_description char(30) product_status char(20) std_shipping_qty number(3) contract_item: contract_no number(12) item_no number(6) arrival_date date order: order_id number(6) product_id number(6) qty number(3) 答案： declare i_product_id inventory.product_id%type; i_product_description inventory.product_description%type; i_product_status inventory.product_status%type; i_std_shipping_qty inventory.std_shipping_qty%type; begin select product_id,product_description,product_status,std_shipping_qty into i_product_id,i_product_description, i_product_status,i_std_shipping_qty from inventory where product_id=( select product_id from contract_item where contract_no=&&contractno and item_no=&&itemno); if i_product_status='shipped' then update contract_item set arrival_date=sysdate+7 where item_no=&&itemno and contract_no=&&contractno; elsif i_product_status='ordered' then update contract_item set arrival_date=add_months(sysdate,1) where item_no=&&itemno and contract_no=&&contractno; else update contract_item set arrival_date=add_months(sysdate,2) where item_no=&&itemno and contract_no=&&contractno; insert into orders values(100,i_product_id,i_std_shipping_qty); end if; end if; commit; end; 2: 1.找出指定部门中的所有雇员 2.用带'&'的变量提示用户输入部门编号 22.把雇员姓名及工资存入prnttable表中，基结构为： create table prnttable (seq number(7),line char(80)); 4.异常情况为，部门中奖金不为空值的雇员信息才能存入prnttable表中。 答案： declare cursor emp_cur is select ename,sal,comm from emp where deptno=&dno; emp_rec emp_cur%rowtype; null_commission exception; begin open emp_cur; fetch emp_cur into emp_rec; while (emp_cur%found) loop if emp_rec.comm is null then begin close emp_cur; raise null_commission; end; end if; fetch emp_cur into emp_rec; end loop; close emp_sur; exception when null_commission then open emp_cur; fetch emp_cur into emp_rec; while (emp_cur%found) loop if emp_rec.comm is not null then insert into temp values(emp_rec.sal,emp_rec.ename); end if; fetch emp_cur into emp_rec; end loop; close emp_cur; commit; end;
转载于:https://www.cnblogs.com/zwl12549/archive/2007/01/24/629232.html
相关资源：数据结构—成绩单生成器
