n n n DECLARE BEGIN EXCEPTION END 13
n n n 匿名块的基本结构: [DECLARE] BEGIN [EXCEPTION] END;
【例13 -1】PL/SQL程序块。 DECLARE name VARCHAR 2(10):=‘wxh’; BEG N DBMS_OUTPUT.PUT_L INE(name); END; / 【例13· 2】PL/SQL程序块。 DECLARE name CHAR(10)j Sex CHAR(2); BEGIN SELECT sname,SSex INTO name,sex FROM Stud WHERE sno=98001; __将检索的值存储到变量中 DBMS_OUTPUT.PUT_LINE(name); END; SQL>SET SERVEROUTPUT ON n
n 【例13.3】 声明常量、变量。 nl CONSTANTINT: =100: C CHAR(4); d DATE; n 2 NUMBER(5)DEFAULT 100; name VARCHAR 2(10):=’TOM’; istrue BOOLEAN DEFAULT TRUE;
n n n n 【例13 -4】 输出所有雇员的平均 资。 SQL>SET SERVEROUTPUT ON SQL>VAR avgsal NUMBER; SQL>BEGIN . SELECT AVG(sal)INTO:avgsal FROM emp; _一只是将查询值赋给了avgsal,不会显示select语句的结果 DBMS_OUTPUT.PUT_LINE(:avgsal); END; { 2001.92308 PL/SQL过程已成功完成。 SQL>PRINT avgsal AVGSAL 2001.92308
n 【例13.5】 输出 7934雇员的姓名、岗位。 DECLARE . . my_name emp.ename%TYPE; 一声明%TYPE类型的变量 my_job emp.Joh%TYPE; BEG N SELECT ename,Job INTO my_na~ne,my_job FROM emp WHERE empno=7934; DBMS—Ou. TPu. T.PUT_LINE(my name l l。 ’l lmy_job); END; } M LLER CLERK PL/SQL过程已成功完成。
n 【例13.6】 删除 9988雇员。 BEGIN DELETE FROM emp WHERE empno=9988; COMMIT; END; / PL/SQL过程已成功完成。
n n n n n 【例13.7】 输出 7934雇员的姓名、岗位。 DECLARE empl emp%ROWTYPE; BEG N SELECT‘INTO empl FROM emp WHERE empno=7934; DBMS_OUTPUT.PUT_LINE(empl.ename I I…’s Job is’I l empl.Job); END: | MILLER’s JOb i. S CLERK PL/SQL过程己成功完成。
n n n n 【例13.8】 IF.THEN—ELSE结构。 DECLARE e_sal emp.sal%TYPE; BEGIN SELECT sal INTO e_sal FROM emp WHERE empno: 7 9 3 4; IF e_sal<2000 THEN UPDATE emp SET comm=c. Olitm+sal‘ 1.2 WHERE ELSIF e_sal>: 2000 AND e_sal<2500 THEN UPDATE emp SET comm:coilqm+sal‘ 1.1 WHERE ELSE DBMS_OUTPUT.PUT—LINE(·不提高补助金·); END IF: END: empno: 7934;
【例13.9】 CASE语句。 DE,CLARE e_deptno emp。deptno%TYPE歹 d_dname dept.dname%TYPE; e_ename emp.ename%TYPE 7 BEG N SELECT deptno l ename INTO e_deptno l e_ename FROM emp WHERE empno=7 9 3 4; SELECT dname INTO d_dname FROM dept WHERE deptno=e_deptno; CASE WHEN d dname=。ACCOUNTING-THEN d_dname:=-财务部-歹 WHEN d_dname=。RE SEARCH’THEN d_dname:=’开发部,歹 WHEN d_dname=。SALES-THEN amdname:=t销售部·歹 WHEN amdname=’OPERATI ONS’THEN d_dname:=-项目部-歹 ELSE d_dname:=-没有这样的部门-; END CASE歹 DBMS_OUTPUT。PUT_L INE(d-dname); END歹 } j 财务部 PL/SQL过程已成功完成。 n
n n n n 求“ 1+2+…+100”的值来说明三种类型循环的应用。 【例13 -101 简单循环。 DECLARE V—Count NT:=1: V—sum NT:=0; BEG N L 00 P V—sum:=V—sum+V—count; V—count:=v—count+1; EXIT WHEN v count>100; __结束循环 END LOOP: DBMS_OUTPUT.PUT_LINE(。1+2+…+100: END: | 1+2+…+100=5050 PL/SQL过程已成功完成。
n n n 【例13 -III FOR循环。 DECLARE v_sum INT:=0; BEG N FOR i IN 1..100 LOOP v sum:=v_sum+i; END LOOPj DBMS_OUTPUT.PUT_LINE(’ 1+2+…+100= END: { 1+2+…+100=5050 PL/SQL过程已成功完成。
n n n n 【例13 -121 WHILE循环。 DECLARE v_count NUMBER:=1 j v_sum NUMBER:=0 j BEG N WHILE v_count<=100 LOOP V—sum:=V~sum+V—count; END LOOP; DBMS_OUTPUT.PUT_LINE(’ 1+2+…+100: END; } 1+2+…+100=5050 PL/SQL过程已成功完成。 v_sum); v—sum)j
n n n GOTO label—name j <<1 abel—name>> 例如: DECLARE id NUMBER:=1: BEGIN L 00 P DBMS一OUTPU T. PUT_L NE(‘循环次数—’|| id); id:=id+1; IF id=10’t. HEN GOTO a: END F: END LOOP; <<a>> :DBMS—OUq,PUqt.PUT—LINE(·跳出循环·); END;
n n n n n 游标的属性反映了游标的状态,下面假定stud表中只 有两行记录,执行以下PL/SQL块: DECLARE CURSOR c—stud IS SELECT sname FROM stud; name stud. sname%TYPE: BEGIN --loc 1 OPEN c_stud: --loc 2 FETCH c—stud INTO name;
--loc 3 FETCH c_stud INTO name; --loc 4 FETCH c_stud INTO name; --loc 5 CLOSE c_stud; --loc 6 END;
1.创建过程 语法如下: CREATE[OR REPLACE]PROCEDURE过程名 [参数 1 {IN | OUT | IN OUT)类型, 参数 2{IN I OUT l IN OUT}类型, 参数N{IN|OUT|IN OUT}类型] {IS | AS } 过程体 n
n Desc view_stu;
n n n (1)在SQL*Plus中直接用EXECUTE命令调 用: SQL>EXECUTE proc_name(parl, par 2…); (2)PL/SQL程序块调用: BEGIN proc_name(parl,par 2…); END;
下面是一个完整的创建、查看、调用过程的一个实例。 n 【例13. 18 完成以下操作。 n (1)编写过程,显示指定雇员所在的部门名称和所在城市。 SQL>CONN scott/tiger SQL>CREATE OR REPLACE PROCEDURE deptmesg(p_ename emp. ename %TYPE) AS p_dname dept. dname%TYPE; p_loc dept. loc%TYPE; BEG N SELECT dname, loc INTO p. dname, p_loc FROM emp. dept WHERE emp. deptno=deptno AND emp. ename=p. ename; DBMS_OUTPUT. PUT_LINE(p_dname||’ ’|| ploc); END; } n 过程已创建。 n
(2)查看以上过程。User_objects和user_sourece s. QL>SELECT obj ect_name FROM user_obj ects WHERE obj ect_type=’PROCEDURE’; OBJECT_NAME DEPTMESG SQL>SELECT text FROM user_source WHERE name=’DEPTMESG’; TEXT PROCEDURE deptmesg(p_ename emp.ename%TYPE)AS p_dname dept.dname%TYPE; p_loc dept.10 c%TYPE; BEG N SELECT dname.10 c INTO p_dname,p_loc FROM emp l dept WHERE emp.dep七no:dep七.deptno AND emp.ename=p_ename; DBMS_OUTPUT.PUT—LINE(p_dname I I’ ’J l p_loc); END; n
n n n n (3)调用以上过程。 在PL/SQL程序中调用: BEGIN Dept. Mesg('SMITH'); END; / RESEARCH DALLAS PL/SQL过程已成功完成。
n 【例13 -191 使用不同参数模式的示例。 CREATE OR REPLACE PROCEDURE proce_test(p_in IN VARCHAR 2, P_out OUT VARCHAR 2, p_inout IN OUT VARCHAR 2) AS varl VARCHAR 2(20); BEGIN
varl: =p_in; p_out:=varl | Ip_inout; END; { 过程已创建。
调用以上过程,体会参数的三种模式。 SUL>DECLARE v_out VARCHAR 2; v_inout VARCHAR 2; BEGIN v_inout: =‘ff’; proce_test(’dd’,v_out,v_inout); DBMS_OUTPUT. PUT_LINE(v_out); END; } ddff PL/SQL过程已成功完成。
n n n n 下面是一个完整的创建、调用函数的一个实例。 【例13.20] 完成以下操作。 (1)编写一个函数以显示该雇员在此组织中的 作天数。 CREATE OR REPLACE FUNCTION hire_day(no emp.empno%TYPE) RETURN NUMBER AS vhiredate emp.hiredate%TYPE; vday NUMBER; BEG N SELECT hiredate INTO vhiredate FROM emp WHERE empno=no; vday:=CEIL(SYSDATE—vhiredate); 一CEIL返回最小整数值 RETURN vday; END: { 函数已创建。
(2)调用以上函数。 DECLARE nl number; BEGIN nl:=hire_day(7900); DBMS_OUTPUT. PUT_LINE(nll I‘days。); END: / 9866 days PL/SQL过程已成功完成。 n
n 以下是创建、查看、调用包的一个实例。 【例13.21] 使用学生一选课数据库的表stud。 (1)创建包。 . /*包头*/ CREATE OR REPLACE PACKAGE stu_package AS PROCEDURE addstud(p_sno stud.sno%TYPE, p_sname stud.sname%TYPE, p_s. Sex stud.ssex%TYPE, p_sage stud.sage%TYPE, p_sdept stud.sdept%TYPE); PROCEDURE removestud(p_sno stud.sno%TYPE)j FUNCTION get_name(p_sno stud.sno%TYPE)RETURN VARCHAR 2; END; / 程序包已创建。
/*包体*/ CREATE OR REPLACE PACKAGE BODY stu__package AS PROCEDURe addstud(p_sno stud.sno%TYPE, 一给表添加记录的过 程 p_sname s tud.sname%TYPE, p_ssex stud.ssex%TYPE, p_sage stud.sage%TYPE, p_sdept stud.sdept%TYPE) IS BEG N INSERT INTO stud VALUES(p_sno,p_sname,p_s sex,p_sage, p_dept); COMMIT; END addstud;
PROCEDURE removestud(p_sno stud.sno%type)IS 一按学号删除学生记录的过程 BEG N DELETE FROM stud WHERE sno=p_sno; COMMIT; END removestud; . FUNCTION get_name(p_sno stud.sno%type)RETURN VARCHAR 2 一一按学号查询学生姓名的函数 AS name stud.sname%TYPE; BEG N SELECT sname INTO name FROM stud WHERE sno:p_sno; RETURN name; END get_name; END stu_package£ } 程序包主体已创建。
(2)查看以上包的信息。 s. QL>s. ELEc. T obj ect_name FROM user_obj ects WHERE obj ect_type=’PACKAGE’; OBJECT—NAME STU—PACKAGE SQL>SELECT text FROM user_source WHERE name=’STU—PACKAGE’; 将显示包头、包体的源代码。
(3)调用包。 SQL>EXECUTE stu_package.addstud(· 23458 t,·王晓t,·女·,20, -计算机-); PL/SQL过程已成功完成。 SQL>DECLARE strl VARCHAR(30); BEG N strl:=stu_package.get_name(‘ 23458’); DBMS_OUTPUT.PUT_LINE(。23458:’l I’ ’I l strl); END: / 23458:王晓 PL/SQL过程已成功完成。
13. 5. 1创建触发器 下面通过一个实例体会触发器的作用及点火的过程。 【例13.22] 创建触发器。 CREATE 0 R REPLACE TRIGGER stud_count AFTER DELETE ON stud DECLARE v_count INTEGER; BEG N SELECT COUNT(‘)INTO v_count FROM stud; DBMs—Ou. Tpu. T.PUT_LINE(。Student table now have,ll v_count ll’student.’); END; | 触发器已创建。
- Slides: 86