PL/SQL编程基础

最新推荐文章于 2024-07-05 10:44:41 发布

-FIGHTING-

最新推荐文章于 2024-07-05 10:44:41 发布

阅读量1.4k

点赞数

分类专栏： Oracle

本文链接：https://blog.csdn.net/u012701023/article/details/46406213

版权

Oracle 专栏收录该内容

16 篇文章 0 订阅

订阅专栏

PL/SQL编程基础

实践目的

　　(1) 掌握PL/SQL块结构、PL/SQL的基本语法、PL/SQL的控制结构。

　　(2) 掌握PL/SQL块中使用复合数据类型和游标的方法。

实践内容

任务中涉及的数据表是scott用户给出的表。建议将下面所做的练习内容保存起来，以便在实践7中在此基础上进行修改，注意比较未命名的PL/SQL与命名的PL/SQL的差别。

（1）输出1-1000能被3和5整除的数

（2）求1-100奇数之和

（3）输出10号部门的员工号，员工名和公资。

（4）EMP表中职工号7788的职工，如果工资小于3000那么把工资更改为3000：

（5）查询工资前3名员工的姓名、工资信息

（6）根据emp表新建一个emp0表，并向表里添加10条数据empno列的值在最大员工号基础上每次加5，ename为’Unknown’

实验步骤：

（1）输出1-1000能被3和5整除的数

SQL> conn scott/tiger;

已连接。

SQL> set serveroutput on;

SQL> declare

2 a int:=1;

3 begin for a in 1..1000

4 loop

5 if mod(a,3)=0 and mod(a,5)=0

6 then

7 dbms_output.put_line('a:'||a);

8 end if;

9 end loop;

10 end;

11 /

a:15

a:30

a:45

a:60

a:75

a:90

a:105

a:120

a:135

a:150

a:165

a:180

a:195

a:210

a:225

a:240

a:255

a:270

a:285

a:300

a:315

a:330

a:345

a:360

a:375

a:390

a:405

a:420

a:435

a:450

a:465

a:480

a:495

a:510

a:525

a:540

a:555

a:570

a:585

a:600

a:615

a:630

a:645

a:660

a:675

a:690

a:705

a:720

a:735

a:750

a:765

a:780

a:795

a:810

a:825

a:840

a:855

a:870

a:885

a:900

a:915

a:930

a:945

a:960

a:975

a:990

PL/SQL 过程已成功完成。

（2）求1-100奇数之和

SQL> declare

2 a int:=0;

3 sm int:=0;

4 begin

5 for i in 1..100

6 loop

7 a:=i;

8 if mod(a,2)=1

9 then

10 sm:=sm+a;

11 end if;

12 end loop;

13 dbms_output.put_line('sum:'||sm);

14 end;

15 /

sum:2500

PL/SQL 过程已成功完成。

（3）输出10号部门的员工号，员工名和公资。

第一种方法：（SQL语句）

SQL> select empno,ename,sal from emp where deptno='10';

EMPNO ENAME SAL

---------- ---------- ----------

7782 CLARK 2450

7839 KING 5000

7934 MILLER 1300

第二种方法：（plsql）

（4） EMP表中职工号7788的职工，如果工资小于3000那么把工资更改为3000：

第一种方法：

SQL> DECLARE

2 v_empno emp.empno%TYPE :=&empno;

3 v_sal emp.sal%TYPE;

4 BEGIN

5 SELECT sal INTO v_sal FROM emp WHERE empno=v_empno;

6 IF v_sal<=3000 THEN

7 UPDATE emp SET sal=3000 WHERE empno=v_empno;

8 DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'员工工资已更新!');

9 ELSE

10 DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'员工工资已经超过规定值!');

11 END IF;

12 EXCEPTION

13 WHEN NO_DATA_FOUND THEN

14 DBMS_OUTPUT.PUT_LINE('数据库中没有编码为'||v_empno||'的员工');

15 WHEN TOO_MANY_ROWS THEN

16 DBMS_OUTPUT.PUT_LINE('程序运行错误!请使用游标');

17 WHEN OTHERS THEN

18 DBMS_OUTPUT.PUT_LINE('发生其它错误!');

19 END;

21 /

输入 empno 的值: 7788

原值 2: v_empno emp.empno%TYPE :=&empno;

新值 2: v_empno emp.empno%TYPE :=7788;

PL/SQL 过程已成功完成。

第二种方法：

PL/SQL 过程已成功完成。

SQL> DECLARE

2 v_empno emp.empno%TYPE :=&empno;

3 no_result EXCEPTION;

4 BEGIN

5 UPDATE emp SET sal=3000 WHERE sal<3000 and empno=v_empno;

6 IF SQL%NOTFOUND THEN

7 RAISE no_result;

8 END IF;

9 EXCEPTION

10 WHEN no_result THEN

11 DBMS_OUTPUT.PUT_LINE('你的数据更新语句失败了!');

12 WHEN OTHERS THEN

13 DBMS_OUTPUT.PUT_LINE('发生其它错误!');

14 END;

15 /

输入 empno 的值: 7788

原值 2: v_empno emp.empno%TYPE :=&empno;

新值 2: v_empno emp.empno%TYPE :=7788;

PL/SQL 过程已成功完成。

（5）查询工资前3名员工的姓名、工资信息

第一种方法：利用SQL方法实现语句：

SQL> select ename,sal from emp where

2 empno in (select empno from (select empno,sal from emp order by sal desc

3 where rownum<4);

ENAME SAL

---------- ----------

SCOTT 3000

KING 5000

FORD 3000

第二种方法：用plsql：

SQL> declare

2 v_name varchar2(10);

3 v_sal number(5);

4 cursor emp_cursor is select ename,sal from emp order by sal desc;

5 begin

6 open emp_cursor;

7 for i in 1..3

8 loop

9 fetch emp_cursor into v_name,v_sal;

10 dbms_output.put_line(v_name||','||v_sal);

11 end loop;

12 close emp_cursor;

13 end;

14 /

PL/SQL 过程已成功完成。

（6）根据emp表新建一个emp0表，并向表里添加10条数据empno列的值在最大员工号基础上每次加5，ename为’Unknown’

SQL> declare

2 var emp.empno%type;

3 begin

4 select empno into var from(select emp.empno,rownum as rno

5 from emp order by emp.empno desc ) where rno<2;

6 for i in 1..10

7 loop

8 var:=var+5;

9 insert into emp0 values (var,'Unknow','IT','8888',to_date('2013-11-22','yyyy-

10 mm-dd'),9000,10,20);

11 end loop;

12 Commit;

13 end;

14 /

PL/SQL 过程已成功完成。

SQL> select * from emp0;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- -------------- ---------- ---------- ----------

7939 Unknow IT 8888 22-11月-13 9000 10 20

7944 Unknow IT 8888 22-11月-13 9000 10 20

7949 Unknow IT 8888 22-11月-13 9000 10 20

7954 Unknow IT 8888 22-11月-13 9000 10 20

7959 Unknow IT 8888 22-11月-13 9000 10 20

7964 Unknow IT 8888 22-11月-13 9000 10 20

7969 Unknow IT 8888 22-11月-13 9000 10 20

7974 Unknow IT 8888 22-11月-13 9000 10 20

7979 Unknow IT 8888 22-11月-13 9000 10 20

7984 Unknow IT 8888 22-11月-13 9000 10 20

已选择10行。

实验小结：

1. PL/SQL程序由三个块组成，即声明部分、执行部分、异常处理部分。PL/SQL块可以分为三类：1. 无名块：动态构造，只能执行一次。2. 子程序：存储在数据库中的存储过程、函数及包等。当在数据库上建立好后可以在其它程序中调用它们。3. 触发器：当数据库发生操作时，会触发一些事件，从而自动执行相应的程序。

2. PL/SQL是ORACLE系统的核心语言，现在ORACLE的许多部件都是由PL/SQL写成。在PL/SQL中可以使用的SQL语句有：INSERT，UPDATE，DELETE，SELECT INTO，COMMIT，ROLLBACK，SAVEPOINT。

3. pl/sql（procedural language/sql）是Oracle在标准的sql语言上的扩展，pl/sql不仅允许嵌入Sql语言，还可以定义变量和常量，允许使用条件语句和循环语句，允许使用例外处理各种错误，这样使得它的功能变得更加强大。

4. 块（block）是pl/sql的基本程序单元，编写pl/sql程序实际上就是编写pl/sql块。要完成相对简单的应用功能，可能只需要编写一个pl/sql快；但是如果想要实现复杂的功能，可能需要在一个pl/sql块中嵌套其他的pl/sql快。

5. 在 PL/SQL 程序中，对于处理多行记录的事务经常使用游标来实现。显式游标主要是用于对查询语句的处理，尤其是在查询结果为多条记录的情况下；而对于非查询语句，如修改、删除操作，则由ORACLE 系统自动地为这些操作设置游标并创建其工作区，这些由系统隐含创建的游标称为隐式游标，隐式游标的名字为SQL，这是由ORACLE 系统定义的。对于隐式游标的操作，如定义、打开、取值及关闭操作，都由ORACLE 系统自动地完成，无需用户进行处理。用户只能通过隐式游标的相关属性，来完成相应的操作。在隐式游标的工作区中，所存放的数据是与用户自定义的显示游标无关的、最新处理的一条SQL 语句所包含的数据。

6. 定义一个变量，其数据类型与已经定义的某个数据变量的类型相同，或者与数据库表的某个列的数据类型相同，这时可以使用%TYPE。使用%TYPE特性的优点在于： 1.所引用的数据库列的数据类型可以不必知道；2.所引用的数据库列的数据类型可以实时改变。