PLSQL基本语法部分其他示例 (2013-11-19).docVIP

PL/SQL基本语法部分（补充示例） 注意： 以下例子请在xe库中的hr账号下实现 在SQL Plus中运行前，请先set serveroutput on(在SQL Developer中运行时也可以加上set serveroutput on) 表结构如下： create table s(sno char(10),sname char(10),sage int); create table c(cno char(10),cname char(10),teacher char(10)); create table sc(sno char(10),cno char(10),grade int); 加入数据脚本： Begin insert into s values(s1,zs,21); insert into s values(s2,l4,20); insert into c values(c1,db,wang); insert into c values(c2,c,zhang); insert into sc values(s1,c2,99); insert into sc values(s2,c1,65); end; 变量的定义例1 declare num1 number(3); begin select count(*) into num1 from sc where grade60; dbms_output.put_line(The result is: ||num1); end; 变量的定义例2 declare var1 sc.grade%type; begin select avg(grade) into var1 from sc; dbms_output.put_line(The result is: ||var1); end; 变量的定义例3 declare var1 s%rowtype; begin select * into var1 from s where sname=zs; dbms_output.put_line(The result is: ||var1.sno||var1.sname); end; 条件语句 declare avg_grade sc.grade%type; begin select avg(grade) into avg_grade from sc; if(avg_grade90) then dbms_output.put_line(90); else if (avg_grade80) then dbms_output.put_line(80-90); else dbms_output.put_line(80); end if; end if; end; / 注意elsif的写法 循环语句 declare i int:=1; mysql varchar(200); begin while i20 loop mysql:=grant select on s to str||to_char(i); dbms_output.put_line(mysql); -- execute immediate mysql; 执行这条sql i:=i+1; end loop; end; 用for循环来写： for i in 1..20 loop end loop; case 语句 --case语句 BEGIN case when to_number(to_char(sysdate,hh24))12 then dbms_output.put_line(早上); else dbms_output.put_line(下午); end case; END; / --case表达式 DECLARE degree CHAR(1); pinyu VARCHAR2(20); BEGIN Degree := input_degree; pinyu := CASE WHEN degree = A THEN Excellent WHEN degree = B THEN Very Good WHEN degree = C THEN Good WHEN degree = D THEN Fair WHEN degree = F THEN Poor ELSE No such grade END; dbms_output.put_line(piny