1 / 99

第14章 PL/SQL 语言基础

第14章 PL/SQL 语言基础. 一、例子 DECLARE N CHAR(8) ; SAL NUMBER(5) ; I NUMBER(2) ; BEGIN SELECT SALARY INTO SAL FROM EMPLOYEES WHERE EMPLOYEE_ID='201' ; IF SAL <10000 THEN DBMS_OUTPUT.PUT_LINE(TO_CHAR(SAL)||' 太少') ; ELSE

nika
Download Presentation

第14章 PL/SQL 语言基础

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 第14章 PL/SQL语言基础 一、例子 DECLARE N CHAR(8); SAL NUMBER(5); I NUMBER(2); BEGIN SELECT SALARY INTO SAL FROM EMPLOYEES WHERE EMPLOYEE_ID='201'; IF SAL <10000 THEN DBMS_OUTPUT.PUT_LINE(TO_CHAR(SAL)||' 太少'); ELSE DBMS_OUTPUT.PUT_LINE(TO_CHAR(SAL)||' 还可以'); END IF; END;

  2. 二、PL/SQL简述 1. PL/SQL块及程序 PL/SQL是一种过程化编程语言。用PL/SQL定 义块可将多个SQL编制成程序（程序过程、函数 触发器等）。程序是由若干PL/SQL块组成。 2. PL/SQL块结构与用途 PL/SQL块由三部分组成： 定义部分：定义所处理的变量、常量、游标等。 可执行部分：SQL语句及控制结构的PL/SQL语句 异常处理部分：对执行过程中的错误进行处理。 块可以嵌套。

  3. 块结构： [DECLARE 说明部分 ] BEGIN 语句(SQL或PL/SQL语句） [EXCEPTION 错误处理程序 ] END 3. PL/SQL程序分类 由基本PL/SQL块组成PL/SQL程序。程序有： 无名块：它嵌入在某个应用中的PL/SQL块。 存储过程或函数：命名了的PL/SQL块，可以带

  4. 参数，并重复调用，是数据库对象。 包：是命名PL/SQL块，由一组相关的过程、函 数和标识符组成。是数据库对象。 触发器:与表相关联的存储过程。一表最多12个。 4. PL/SQL程序运行环境 可以有多个：SQL Plus Worksheet,SQL PLUS 等。 在SQL PLUS环境中，第一句是DECLARE或 BEGIN就识别为是PL/SQL程序。它只能以“/”表 示程序结束。

  5. 三、PL/SQL基础 • 标识符 标识符是用户定义的符号串，用来命名变量、常 量、过程等。 标识符以字母开头，后跟数字(0-9)或特殊字符$、 #和_。长度不超过30，不能是PL/SQL的保留字， 不能有空格。 例1：合法标识符：A34、BB#，D_123DF 不合法标识符：5A$、#ABC、END。 2. 变量及变量说明 变量是表示要处理数据项的名称。变量名用标识 符来表示。 变量在使用之前必须用DECLARE进行说明。

  6. 变量说明： 变量名 [CONSTANT] 类型 [NOT NULL][:=值][DEFAULT SQL表达式]； 例1: DECLARE 　　 aa char(5) not null:=‘TEST’; 　　 bb number(3) default 5; 　　 cc char(4):='THIS'; dd constant char(4) :=‘SWJ’; --不能赋值 　　begin DBMS_OUTPUT.PUT_LINE(aa||' '||to_char(bb)||' '||cc||' '||DD); end; 说明：非空时要有初始值,NOT NULL在前，缺省 值在后；每个变量说明占一行，且以分号“；”结 束。变量说明要在DECLARE以后，BEGIN之前。

  7. 变量或常量类型 数值型：NUMBER(p,s),INTEGER,FLOAT,DEC 字符型：CHAR(n),VARCHAR2(n),RAW(n) 日期型：DATE 布尔型：BOOLEAN(TRUE或FALSE) 大数据类型：BFILE,BLOB,CLOB,NCLOB 4. 数据类型转换 显示转换: TO_CHAR(),TO_NUMBER(), RAWTOHEX(), ,HEXTORAW(),ROWIDTOCHAR(),TO_DATE()

  8. 5. 函数

  9. 6. 表达式 表达式是由变量、常量、列名、函数和运算符 结合的有意义式子。 数值表达式：数值运算符、变量、常量、函数等 数值运算符：+、-、＊、/、**、() 例2: DECLARE A INT :=4; B FLOAT:=4.324; X FLOAT; BEGIN X:= SIN(3)*10+2**8-A*10*(10-B**2); DBMS_OUTPUT.PUT_LINE(TO_CHAR(X)); END;

  10. 字符表达式： 字符运算符: ||(合并) 关系表达式:关系表达式的结果是TRUE和FALSE 关系运算符: <> != < > <= >= = LIKE IN BETWEEN…AND… 　　<表达式 >　<关系运算符> <表达式> 例3: ABC > ‘123’ 123<>345 ‘THIS’ LIKE ‘T%’ X BETWEEN 10 AND 20 ‘A’ IN (‘SS’,’DD’,’AD’) FALSE SIN(X)+10 > A**2+B

  11. 逻辑表达式 逻辑运算符：NOT AND OR 　<关系表达式> <逻辑表达式> <关系表达式> 例4: NOT (A >10 AND B<10) A+10>100 AND CCC LIKE ‘C%’ 例5: DECLARE A INT :=4; B FLOAT:=4.324; X FLOAT; C BOOLEAN; BEGIN X:= SIN(3)*10+2**8-A*10*(10-B**2); C:='A' IN ('SS','DD','AD'); IF C OR A<B THEN DBMS_OUTPUT.PUT_LINE(TO_CHAR(X)); END IF; END;

  12. 7. 变量赋值 变量名：=与变量同类型的表达式； 例6: DECLARE C1 CHAR(34) ; --赋给C1的字符串不能大于34 　　　N1 NUMBER(3); N2 NUMBER(2); B1 BOOLEAN; BEGIN N1:=10; N2:=SIN(N1)**2+20.30; C1:=TO_CHAR(N2)||’ TEST’; B1:= C1 LIKE ‘%T’; END;

  13. 8. 注释行 单行注释：在一行的任何位置以“--字符串” 多行注释：/* 注释内容 */ 例7:DECLARE 　　 C1 CHAR(34); --字符变量，长度不超过34 N1 NUMBER(3); --数字型变量 　　BEGIN /* 赋值语句范例： 　　　　 变量类型要与表达式类型一致 */ 　　　N1:=10; 　　　C1:=TO_CHAR(N1)||' TEST'; 　　END;

  14. 9. PL/SQL中的语句 PL/SQL块中每一行一条语句，并且必须以分号 “；”结束。 变量或常量说明语句、 变量赋值语句、CASE语 句和IF语句、 各种循环语句、数据处理语句SIDU、 事务处理语句、 DBMS_OUTPUT.PUT_LINE(字 符串)、 游标语句等。 COMMIT、ROLLBACK EXECUTE IMMEDIATE 动态串 SELECT…INTO 变量名表|记录名FROM… 每个块由若干语句组成。若干块组成程序。

  15. 四、PL/SQL控制结构 三种程序结构：顺序、选择和循环 • 顺序结构 顺序结构是指执行过程按所写程序的顺序执行。 例1： DECLARE VV CHAR(20); SAL NUMBER(4); BEGIN SELECT LAST_NAME INTO VV FROM EMPLOYEES WHERE EMPLOYEE_ID=201; DBMS_OUTPUT.PUT_LINE(VV); SAL:=10; END;

  16. 例2：DECLARE JID VARCHAR2(10):='AD_VP'; JTIT CHAR(30); BEGIN SELECT JOB_TITLE INTO JTIT FROM JOBS WHERE JOB_ID=JID; DBMS_OUTPUT.PUT_LINE(JTIT); END; 说明：JID的类型与JOB_ID的类型要完全一致， 且长度要一样。改为JID CHAR(10)将出错。 可改为：JID JOBS.JOB_ID%TYPE:=‘AD_VP’;

  17. 条件 语句1 语句2 IF 条件 THEN 语句序列； ENDIF; “条件”为逻辑表达式或关系表达式，“条件”为TRUE时执行“语句序列”，为FALSE时执行END IF后面的语句。 2. IF选择结构 IF 条件 THEN 语句序列1 ELSE 语句序列2 END IF;

  18. IF 条件1 THEN 语句序列1 ELSIF 条件2 THEN 语句序列2 ELSE 语句序列3 END IF; 例1: DECLARE N1 NUMBER:=11; N2 NUMBER; BEGIN IF N1>10 THEN N2:=N1+10; DBMS_OUTPUT.PUT_LINE(TO_CHAR(N2)); END IF; END;

  19. 例2：嵌套选择结构 DECLARE N1 NUMBER:=11; N2 NUMBER DEFAULT 10; BEGIN IF N1>10 THEN IF N2<N1 THEN N2:=N1+10; DBMS_OUTPUT.PUT_LINE(TO_CHAR(N2)); END IF; END IF; END; 说明：嵌套必须是完全嵌套,可以是任何选择结构 IF_END IF、IF_ELSE_END IF 等。

  20. 例3：IF…THEN…ELSE结构 DECLARE N1 NUMBER:=9; N2 NUMBER:=19; BEGIN IF N1>10 THEN N2:=N2+10; ELSE N2:=N1*N1; END IF; DBMS_OUTPUT.PUT_LINE(TO_CHAR(n2)); END;

  21. 例4: IF…THEN…ELSIF…END IF结构 DECLARE N1 NUMBER:=10; N2 NUMBER:=20; CC CHAR(20); C2 DATE:=TO_DATE('1999-1-1','YYYY-MM-DD'); BEGIN DBMS_OUTPUT.PUT_LINE(TO_CHAR(C2)); IF N1>10 THEN N2:=N1+10; ELSIF N1 BETWEEN 7 AND 12 THEN N2:=SQRT(N1)*2; ELSIF C2>=DATE'1998-1-1' THEN CC:=TO_CHAR(SYSDATE); ELSE CC:='NO RESULT'; END IF; DBMS_OUTPUT.PUT_LINE(CC||' '||TO_CHAR(N2)); END;

  22. 说明: 1. 当“变量”的值与某个表达式值相同时，将执行相应的语句序列 2. 当变量与所有表达式不等时执行序列N+1 3.语句序列1到语句序列N+1中只能执行一个语句序列。 3. CASE选择结构 CASE 变量 WHEN 表达式1 THEN 语句序列1; WHEN 表达式2 THEN 语句序列2; …… WHEN 表达式N THEN 语句序列N; [ELSE 语句序列N+1;] END CASE

  23. 例1：DECLARE C1 CHAR(1):='F'; N1 NUMBER:=2; N2 NUMBER:=35; NC NUMBER; BEGIN NC:=ASCII(C1); CASE NC WHEN N2*N2 THEN N1:=N2*N2; DBMS_OUTPUT.PUT_LINE('N1='||TO_CHAR(N1)); WHEN N2+N2 THEN N1:=N2+N2; DBMS_OUTPUT.PUT_LINE('N1='||TO_CHAR(N1)); ELSE DBMS_OUTPUT.PUT_LINE('N1='||TO_CHAR(N1)); DBMS_OUTPUT.PUT_LINE('NC='||TO_CHAR(NC)); END CASE; END;

  24. 条件 语句序列 4. 循环结构 循环结构是指按照指定的逻辑条件循环执行一组 命令。有三种循环：LOOP-EXIT-END;LOOP- EXIT-WHEN-END;WHILE-LOOP-END和FOR- IN-LOOP-END。 当条件为真时，执行语句序列，直到条件为假。 条件是任何合法的逻辑表达式或关系表达式。

  25. 例1：计算1+2+3…+100 DECLARE I INT :=1; S INT:=0; BEGIN LOOP S:=S+I; IF I=100 THEN EXIT; END IF; I:=I+1; END LOOP; DBMS_OUTPUT. PUT_LINE(TO_CHAR(S)); END; 4.1 LOOP-EXIT-END循环 LOOP 语句序列 [EXIT] END LOOP 说明：执行EXIT时从循环 中退出，在条件语句中执 行EXIT。该循环中，必须 有EXIT,否则就会成为“死 循环”。EXIT只能在循环。

  26. 4.2 LOOP-EXIT WHEN-END循环 LOOP 语句序列 EXIT WITH 条件 --等价IF 条件 THEN EXIT，可用在其它循环 END LOOP 例2：计算10！ DECLARE I INT :=1; S INT:=1; BEGIN LOOP S:=S*I; EXIT WHEN I=10; I:=I+1; END LOOP; DBMS_OUTPUT.PUT_LINE(TO_CHAR(S)); END;

  27. 4. 3 WHILE-LOOP-END循环WHILE 条件 LOOP 每次循环前计算条 语句序列； 件，为TRUE，执 END LOOP； 行语句，否则不。 例3：计算s=1*2+2*3+…+N*(N+1),当N=50的值。 DECLARE I INT :=1; S INT:=0; BEGIN WHILEI<=50LOOP S:=S+I*(I+1); I:=I+1; END LOOP; DBMS_OUTPUT.PUT_LINE(TO_CHAR(S)); END;

  28. 例4：在WHILE循环中使用EXIT或EXIT WHEN 计算S=1*2*3+2*3*4+…+N*(N+1)*(N+2);当N=40 DECLARE I INT :=1; S INT:=0; BEGIN WHILE TRUE LOOP S:=S+I*(I+1)*(I+2); EXIT WHEN I=40; I:=I+1; END LOOP; DBMS_OUTPUT.PUT_LINE(TO_CHAR(S)); END; 说明：EXIT和EXIT WHEN可用在任何循环内， 且只能在循环内。

  29. 4.4 FOR-IN-LOOP-END循环 FOR 循环变量 IN [REVERSE]下界..上界 LOOP 语句序列； END LOOP； 说明： • 循环变量被隐式说明为BINARY-INTEGER，也可显式说明。 • 步长1或-1(REVERSE)，循环次数:上界-下界+1; • EXIT或EXIT WHEN可用在FOR循环中； • 已知循环次数时，可用FOR循环，也可用其它 • 末知循环次数不能用FOR循环 • 上界或下界可以为表达式。

  30. 例5：显示20到50的平方根的值及它们的和。 DECLARE S FLOAT :=0; BEGIN FOR I IN 20..50 LOOP DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQRT(I),'9.9999')); S:=S+SQRT(I); END LOOP; DBMS_OUTPUT.PUT_LINE('S= '||TO_CHAR(S)); END; 说明：循环变量I不用说明。

  31. 例6: 步长-1。加REVERSE。 说明：加REVERSE，DECLARE S FLOAT :=0; BEGIN FOR I IN REVERSE 20..50 LOOP DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQRT(I),'9.9999')); S:=S+SQRT(I); END LOOP; DBMS_OUTPUT.PUT_LINE('S= '||TO_CHAR(S)); END; 上界仍要大于下界。

  32. 例7:上下界为表达式的FOR循环 DECLARE S FLOAT :=0; N INT :=10; BEGIN FOR I IN REVERSE2*N-1..N*N LOOP DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQRT(I),'9.9999')); S:=S+SQRT(I); END LOOP; DBMS_OUTPUT.PUT_LINE('S= '||TO_CHAR(S)); END;

  33. 例8: 生成字符串SWJ001-SWJ120 declare n number(1); sn varchar2(8); sn1 char(3); begin for n in 1..120 loop if n<10 then sn1:=ltrim(to_char(n,'9')); sn:=rtrim('swj00'||sn1); else if n<100 then sn1:=ltrim(to_char(n,'99')); sn:=rtrim('swj0'||sn1); else sn1:=ltrim(to_char(n,'999')); sn:=rtrim('swj'||sn1); end if ; end if; DBMS_OUTPUT.PUT_LINE(SN); end loop; End;

  34. 4. 5 循环嵌套 上面的任何循环都可以互相嵌套，且可以多重嵌 套，但必须是完全嵌套。如： LOOP … FOR … LOOP … --可有其它循环(多重循环) END LOOP; … WHILE … LOOP … --可有其它循环(多重循环) END LOOP; END LOOP;

  35. 例9: 两重循环，计算S=1!+2!+…+10! DECLARE S FLOAT :=0; N INT :=10; K INT ; BEGIN FOR I IN 1..N LOOP -- 计算阶乘和 K:=1; FOR J IN 1..I LOOP --计算K! K:=K*J; END LOOP; S:=S+K; DBMS_OUTPUT.PUT_LINE(TO_CHAR(K)); END LOOP; DBMS_OUTPUT.PUT_LINE('S= '||TO_CHAR(S)); END;

  36. 4.6 循环标签 循环标签是以用来标示循环,放在循环前。其格式： <<标签名>> DECLARE S FLOAT :=0; N INT :=10; K INT ; BEGIN <<FOR_LOOP1>> FOR I IN 1..N LOOP -- 计算阶乘和 K:=1; <<FOR_LOOP2>> FOR J IN 1..I LOOP --计算K! K:=K*J; END LOOP FOR_LOOP2; S:=S+K; DBMS_OUTPUT.PUT_LINE(TO_CHAR(K)); END LOOP FOR_LOOP1; DBMS_OUTPUT.PUT_LINE('S= '||TO_CHAR(S)); END;

  37. 循环标签一是提高程序的可读性，二是可以从内循环标签一是提高程序的可读性，二是可以从内 循环跳到外层。 <<outer>> LOOP ... LOOP ... EXIT outer WHEN ... – 退出两重循环。 END LOOP; ... END LOOP outer; 注意：只能从内循环跳到外循环，反之不行。

  38. 五、%TYPE类型 PL/SQL变量用来存储表中的数据，但二者要有相 同类型。要保证变量与列名类型总是一致，就要 用%TYPE类型。 变量名 表名.列名%TYPE; 例1：DECLARE J_T JOBS.JOB_TITLE%TYPE; BEGIN SELECT JOB_TITLE INTO J_T FROM JOBS WHERE JOB_ID='AD_VP'; DBMS_OUTPUT.PUT_LINE(J_T); END;

  39. 六、%ROWTYPE类型 用%ROWTYPE说明变量为一个记录类型。 变量名 表名%ROWTYPE; 引用变量时：变量名.列名 例1：DECLARE EMP EMPLOYEES %ROWTYPE; BEGIN SELECT * INTO EMP FROM EMPLOYEES WHERE EMPLOYEE_ID='201'; DBMS_OUTPUT.PUT_LINE (EMP.LAST_NAME||' '||EMP.FIRST_NAME); END;

  40. 七、游标(CURSOR) 游标是将从表中选择的一组记录，放置在内存的 临时表中。游标是数据类型，要先说明后使用。 • 说明游标 CURSOR 游标名 IS SELECT语句； 例1: DECLARE MIN_S JOBS.MIN_SALARY%TYPE; CURSOR MS IS SELECT * FROM JOBS WHERE MIN_SALARY>=MIN_S; BEGIN NULL; --空语句 END;

  41. 2. 打开游标 OPEN 游标名； 注意：打开之前，游标中的变量必须有值；打开 游标后,查询记录放在内存,指针指向第一个记录。 例2：DECLARE MIN_S JOBS.MIN_SALARY%TYPE; CURSOR MS IS SELECT * FROM JOBS WHERE MIN_SALARY>=MIN_S; BEGIN MIN_S:=1000; --先给变量赋值 OPEN MS; -- 才能打开游标 END;

  42. 3. 提取游标数据 FETCH 游标名 INTO 变量1,变量2,…; 注意: 变量要与说明游标时SELECT后的列名在类 型和个数上完成一样。不能只提取部分。 FETCH MS INTO J1，J2，M1，M2； 4. 关闭游标 CLOSE 游标； CLOSE MS； 关闭后的游标不能再使用。 使用游标的过程：说明游标、变量赋值、打开游 标、处理、关闭游标。

  43. 例3：提出数据 DECLARE J1 JOBS.JOB_ID%TYPE; --变量名与列名类型保持一致 J2 JOBS.JOB_TITLE%TYPE; M1 JOBS.MIN_SALARY%TYPE; M2 JOBS.MAX_SALARY%TYPE; MIN_S JOBS.MIN_SALARY%TYPE; CURSOR MS IS SELECT * FROM JOBS --说明游标 WHERE MIN_SALARY>=MIN_S; BEGIN MIN_S:=1000; --先给变量赋值 OPEN MS; -- 才能打开游标 FETCH MS INTO J1,J2,M1,M2; --第一条记录 DBMS_OUTPUT.PUT_LINE(J1||' '||J2||' '||TO_CHAR(m1+m2)); FETCH MS INTO J1,J2,M1,M2; --第二条记录 DBMS_OUTPUT.PUT_LINE(J1||' '||J2||' '||TO_CHAR(m1+m2)); FETCH MS INTO J1,J2,M1,M2; --第三条记录 DBMS_OUTPUT.PUT_LINE(J1||' '||J2||' '||TO_CHAR(m1+m2)); END;

  44. 例4: 复杂游标（分组） DECLARE DEP EMPLOYEES.DEPARTMENT_ID%TYPE; COU INT; CURSOR DEPT IS SELECT DEPARTMENT_ID,COUNT(*) FROM EMPLOYEES GROUP BY DEPARTMENT_ID; BEGIN OPEN DEPT; -- 打开游标 FETCH DEPT INTO DEP,COU; --第一条记录 DBMS_OUTPUT.PUT_LINE(DEP||'的人数是： '||TO_CHAR(COU)); FETCH DEPT INTO DEP,COU; --第二条记录 DBMS_OUTPUT.PUT_LINE(DEP||'的人数是： '||TO_CHAR(COU)); CLOSE DEPT; --关闭游标 END;

  45. 5. 游标属性 通过游标属性来知道游标的状态。 A: %FOUND 如果前一个FETCH返回一行，%FOUND返回 TRUE；否则返回FALSE。在未打开游标之前设 置%FOUND，返回错误ORA_1001。用法： 游标名%FOUND • %NOTFOUND 与%FOUND相反，常用于退出循环提出。 C. %ISOPEN 判定游标是否打开。打开为TRUE,否则为FALSE.

  46. %ROWCOUNT 返回从游标中已提取的行数。

  47. 例5：declare emp employees %rowtype; cursor sal is select * into emp from employees where salary>=10000; --说明游标 begin open sal; -- 打开游标 loop fetch sal into emp; if sal%found then -- 提出成功 dbms_output.put_line (emp.last_name||' 工资:'||to_char(emp.salary)); else -- 不成功 exit; end if; end loop; end;

  48. 例6：测试游标属性 declare emp employees %rowtype; cursor sal is select * into emp from employees where department_id=100; begin if not sal%isopen then --游标未打开 open sal; end if; dbms_output.put_line('提取行数：'||to_char(sal%rowcount)); loop fetch sal into emp; dbms_output.put_line(emp.last_name||'行:'||to_char(sal%rowcount)); exit when sal%notfound or sal%rowcount>=5; end loop; end;

  49. 例7: 有表DATA_FIEL(n1,n2,n3,exnum)和表TEMP DECLARE num1 data_table.n1%TYPE; num2 data_table.n2%TYPE; num3 data_table.n3%TYPE; result temp.col1%TYPE; CURSOR c1 IS SELECT n1, n2, n3 FROM data_table WHERE exnum = 1; BEGIN OPEN c1; LOOP FETCH c1 INTO num1, num2, num3; EXIT WHEN c1%NOTFOUND; result := num2/(num1 + num3); INSERT INTO temp VALUES (result, NULL, NULL); END LOOP; CLOSE c1; COMMIT; END;

  50. 八、参数化游标和隐含游标 • 参数化游标 CRUSOR 游标名(变量1 类型1,变量2 类型2,…) IS SELECT语句 说明：SELECT语句的条件中使用变量,变量类型 可以为%TYPE。 打开参数化游标： OPEN 游标名(实参1,实参2,…); 说明：在打开时所有实参要有具体值，且在数量 和类型上与游标说明时一致。 实参可以是表达式。

More Related