实验5 SQL - PL编程基础

实验5

PL/SQL编程基础

【实验目的与要求】

? ?

掌握PL/SQL基本语法

掌握PL/SQL流程控制方法及相关语句的编写

【实验内容与步骤】 5.0.实验准备工作

1. 测试用表的创建与数据添加

(1)．创建测试表

Create Table TESTTABLE(

RECORDNUMBER number(4) Not Null, CURRENTDATE Date Not Null )

Tablespace \USER\ ;--这里的表空间其实可以省去，这样它就会在当前用户的表空间中创建一个表

(2)．使用for语句在测试表中加入测试数据 Declare

maxrecords Constant Int:=20; i Int:=1; Begin

For i In 1..maxrecords Loop

Insert Into scott. TESTTABLE (recordnumber,currentdate) --scott为模式名，应根据实际改动 Values(i,Sysdate);

dbms_output.put_line('现在输入的内容是：'||i||' '||Sysdate); Commit; --这里要commit否则将不会将数据提交到表中 End Loop;

dbms_output.put_line('记录已经按照计划全部插入，请查看！'); End;

// 注：scott为登录用户名，需根据情况改动.

第 1 页 共 24 页

(3)．查询表中数据，给出查询结果截图：

5.1.最简单的PL/SQL程序

1. 输出\

/*************************************** 第一个例子:输出\

***************************************/

set serverout on --设置SQL*Plus将服务器所返回的写出来

begin --块开始

DBMS_OUTPUT.put_line('Hello,World');

--在控制台输出信息，类似C语言的Printf或者java语言中的System.out.print end; --块结束

运行结果为：

2. 接收数据并输出

/***************************************

在此基础上，完成Hello,某某，某某从客户端得到 ***************************************/

第 2 页 共 24 页

declare

v_name varchar2(20); begin --块开始

v_name:='& v_name '; --与“客户端”交互，类似C语言的scanf语句 DBMS_OUTPUT.put_line('测试结果为：Hello,'||v_name); end; --块结束

运行结果为：

5.2.简单变量的使用

1．变量的声明与引用

set serveroutput on;

-- PL/SQL变量之 简单类型 declare

v_dept_id number(5) := 1111; v_age binary_integer := 12; v_dept_name varchar2(20) := '人事部'; v_rate constant number(4,2) := 22.12; v_valid boolean not null := TRUE; v_hire_date date not null := sysdate + 7; begin

v_dept_id := 2222;

dbms_output.put_line(v_dept_id); end; /

运行结果为：

第 3 页 共 24 页

2．Into子句赋值的使用：

declare

v_deptno number(2); v_loc varchar2(15); begin

select deptno,loc

into v_deptno,v_loc from dept

where dname='SALES'; --这要求结果有且仅有一条记录 DBMS_OUTPUT.PUT_LINE (V_deptno ||' and '||v_loc); --输出 end;

运行结果为：

第 4 页 共 24 页

实验练习：

编写一PL/SQL程序，实现依次从客户端(键盘)接收各字段的值，并放于变量中，输完一条记录的所有字段值后，将值写到数据库表Emp中。

请给出程序源码：

给出测试结果： 3．变量作用域：

阅读以下程序，熟悉变量的斌值方法，理解变量的作用，给出运行结果。 --嵌套块中的变量赋值和作用域 DECLARE v_weight NUMBER(3) := 100; v_message VARCHAR2(255) := 'Outer Value'; BEGIN

DECLARE

v_weight NUMBER(3) := 1;

v_message VARCHAR2(255) :='Inner value'; BEGIN

v_weight := v_weight + 1; v_message := 'Put'|| v_message;

DBMS_OUTPUT.PUT_LINE('inner block ' || to_char(v_weight) ||

v_message);

END;

第 5 页 共 24 页

v_weight := v_weight + 1;

v_message := 'Put' || v_message;

DBMS_OUTPUT.PUT_LINE('outter block ' || to_char(v_weight) || v_message); END;

运行结果为：

5.3.使用%type和%rowtype变量

1．使用%type变量

阅读以下程序，理解其功能，给出运行结果。 （1）DROP TABLE EMPL;

create table EMPL (

EMPLOYEE_ID NUMBER(5) not null, LAST_NAME VARCHAR2(20) not null, HIRE_DATE DATE,

SALARY NUMBER(8,2), JOB_ID NUMBER(5), DEPARTMENT_ID NUMBER(5) );

set serveroutput on;

-- PL/SQL变量之%TYPE类型 declare

v_last_name emp.last_name%TYPE; v_hire_date emp.hire_date%TYPE; v_balance number(6,2) ; v_min_balance v_balance%type; begin

dbms_output.put_line('定义%type类型的变量'); end; /

运行结果为：

第 6 页 共 24 页

（2）declare

v_deptno dept.deptno%type; v_loc dept.loc%type; begin

select deptno,loc

into v_deptno,v_loc from dept

where dname='SALES';

DBMS_OUTPUT.PUT_LINE (V_deptno ||' and '||v_loc); end; /

运行结果为：

2．使用%Rowtype变量

阅读以下程序，理解其功能，给出运行结果。

（1） DROP TABLE EMPL;

第 7 页 共 24 页

create table EMPL (

EMPLOYEE_ID NUMBER(5) not null, LAST_NAME VARCHAR2(20) not null, HIRE_DATE DATE,

SALARY NUMBER(8,2), JOB_ID NUMBER(5), DEPARTMENT_ID NUMBER(5) );

set serveroutput on;

-- PL/SQL变量之%ROWTYPE类型 declare

v_empl emp%rowtype; begin

dbms_output.put_line('定义%rowtype类型的变量'); end; /

运行结果为：

（2） --准备工作

drop table stock;

--创建表

create table stock (

symbol varchar2(50), price number(6,2) );

--添加数据

insert into stock values('IBM',188.88);

insert into stock values('ORCL',100.88);

第 8 页 共 24 页

--程序块 DECLARE

stock_info1 stock%ROWTYPE; stock_info2 stock%ROWTYPE; stock_info3 stock%ROWTYPE; BEGIN

--对应纪录直接取得相应字段的：Populate the specific fields in a record SELECT symbol,price

INTO stock_info1.symbol,stock_info1.price FROM stock

WHERE symbol ='ORCL';

--对应纪录直接 放入纪录中

SELECT * INTO stock_info2 FROM stock WHERE symbol ='ORCL';

stock_info3 := stock_info2; --记录间可以相互赋值

stock_info3.symbol:='ORACLE'; --对纪录的各字段赋值。

DBMS_OUTPUT.PUT_LINE('stock_info1 :' || stock_info1.symbol || '

'||stock_info1.price);

DBMS_OUTPUT.PUT_LINE('stock_info2 :' || stock_info2.symbol || '

'||stock_info2.price);

DBMS_OUTPUT.PUT_LINE('stock_info3 :' || stock_info3.symbol || '

'||stock_info3.price);

END;

运行结果为：

思考：

如下所示：改变表结构。

drop table stock; create table stock (

第 9 页 共 24 页

symbol varchar2(50), price number(6,2), owner varchar2(10)

);

对以上程序运行有影响吗？为什么？

会有影响，由于表的重建会导致程序找不到相关数据。

5.4. 使用复合变量

1.记录类型

阅读以下程序，理解其功能，给出运行结果。

（1）set serveroutput on;

-- PL/SQL变量之RECORD类型1 declare

type named_record_type is record ( last_name varchar2(20), first_name varchar2(20), birthday date, age number(3) );

v_record_1 named_record_type; v_record_2 named_record_type; begin

v_record_1.last_name := '张'; v_record_1.first_name := '三丰';

v_record_1.birthday := '2-2月-1788'; v_record_1.age := 432;

dbms_output.put_line('定义record类型的变量'); end; /

运行结果为：

（2）--使用记录类型2：

set serveroutput on; DECLARE

TYPE remp IS RECORD( v_empno number(10), v_ename VARCHAR2 (20),

第 10 页 共 24 页

v_salary NUMBER (10,2) );

rsm_emp remp;

BEGIN

SELECT empno,ename,sal INTO rsm_emp.v_empno,rsm_emp.v_ename,rsm_emp.v_salary FROM emp

WHERE sal =(SELECT MAX(sal) FROM emp); --求出工资最高的雇员

DBMS_OUTPUT.PUT_LINE ('Highest Paid Employee is '||rsm_emp.v_ename); DBMS_OUTPUT.PUT_LINE ('Id is '||rsm_emp.v_empno ||'Salary ' ||

to_char(rsm_emp.v_salary,'999,999.99'));

END;

运行结果为：

（3）--使用记录类型3：

set serveroutput on; DECLARE

TYPE remp IS RECORD( v_empno number(10), v_ename VARCHAR2 (20), v_salary NUMBER (10,2) );

rsm_emp remp; BEGIN

SELECT empno,ename,sal INTO rsm_emp FROM emp

WHERE sal =(SELECT MAX(sal) FROM emp); --求出工资最高的雇员

DBMS_OUTPUT.PUT_LINE ('Highest Paid Employee is '||rsm_emp.v_ename); DBMS_OUTPUT.PUT_LINE ('Id is '||rsm_emp.v_empno ||'Salary ' ||

to_char(rsm_emp.v_salary,'999,999.99'));

END;

运行结果为：

第 11 页 共 24 页

2.记录表类型

阅读以下程序，理解其功能，给出运行结果。

--一维表类型(相当于基本数据类型元素组成的一维数组) declare

type tabletype1 is table of varchar2(4) index by binary_integer;

--type tabletype2 is table of employees.last_name%type index by binary_integer; table1 tabletype1; --table2 tabletype2; begin

table1(1):='大学'; table1(2):='大专'; --table2(1):=88; --table2(2):=55;

dbms_output.put_line(table1(1)||table1(2)); --dbms_output.put_line(table1(2)||table2(2)); end; /

运行结果为：

/*

在定义好的表类型变量里，可以使用count、delete、first、last、next、exists和prior

等属性进行操作，使用方法为“表变量名.属性”，返回的是数字。

*/

--使用表类型变量属性

SET SERVEROUTPUT ON; Declare type tabletype1 is table of varchar2(9) index by binary_integer; table1 tabletype1; i binary_integer :=11; begin table1(1):='成都市'; table1(5):='北京市'; table1(3):='青岛市';

第 12 页 共 24 页

table1(11):='青岛市'; dbms_output.put_line('总记录数：'||to_char(table1.count)); dbms_output.put_line('第一条记录：'||table1.first); dbms_output.put_line('最后条记录：'||table1.last); dbms_output.put_line('第3条的前一条记录：'||table1.prior(3)); dbms_output.put_line('第5条的后一条记录：'||table1.next(5)); if(table1.exists(i)) then dbms_output.put_line('该条记录值为：' || table1(i)); else dbms_output.put_line('该条记录不存在！'); end if; table1.delete(11); --table1.delete(12); dbms_output.put_line('总记录数：'||to_char(table1.count)); dbms_output.put_line('第一条记录：'||table1.first); dbms_output.put_line('最后条记录：'||table1.last); dbms_output.put_line('第3条的前一条记录：'||table1.prior(3)); dbms_output.put_line('第5条的后一条记录：'||table1.next(5)); end; /

运行结果为：

5.5. 复合变量的使用

1. 完整定义一个记录类型的变量，了解其简单应用。

阅读以下程序，理解其功能，给出运行结果。 Declare

Type myrecord Is Record( r_recordnumber Number(4), r_currentdate Date );

v_myrecord myrecord; Begin

第 13 页 共 24 页

Select *

Into v_myrecord From testtable

Where recordnumber=80;

dbms_output.put_line('用记录类型的变量取出来的值为：'|| v_myrecord.r_recordnumber|| v_myrecord.r_currentdate); End;

给出运行结果：

2. 简单的%Rowtype定义变量的实例

阅读以下程序，理解其功能，给出运行结果。

Declare

v_myrow testtable%Rowtype; Begin

Select *

Into v_myrow From testtable

Where recordnumber=90;

dbms_output.put_line('用rowtype查询的结果是：

'||v_myrow.recordnumber||v_myrow.currentdate);

--dbms_output.put_line('用rowtype查询的结果是：

'||v_myrow.recordnumber||v_myrow.currentdate||myrow.Rowid);

--上句操作说明了行类型的变量中不会把oracle表中的rowid的值带进来 End;

给出运行结果：

3．定义与使用一维表变量

阅读以下程序，理解其功能，给出运行结果。

--维表变量，这种变量看起来有些类似于C语言中的一维数组 Declare

Type mytbtype1 Is Table Of Varchar2(4) Index By Binary_Integer;

Type mytbtype2 Is Table Of testtable.recordnumber%Type Index By Binary_Integer; tb1 mytbtype1; tb2 mytbtype2; Begin

tb1(1):='大学'; tb1(2):='大专'; tb2(1):=90; tb2(2):=70;

dbms_output.put_line(tb1(1)||tb2(1)); dbms_output.put_line(tb1(2)||tb2(2));

第 14 页 共 24 页

End;

给出运行结果：

4．定义与使用多维表变量

阅读以下程序，理解其功能，给出运行结果。

定义一个多维表变量，这就像一个二维数组，当然这个二维的数组的下标就有些区别于在编程语言中熟悉的二维数组，可以理解为一维存储的列名，而另一维则是存储与一维列名相对应的数据。

(1) 多维表变量简单应用

Declare

--这里区别于一维表变量的定义

Type multbtype Is Table Of testtable%Rowtype Index By Binary_Integer; multb multbtype; Begin

Select * Into multb(12) From testtable

Where recordnumber=88;

dbms_output.put_line('multb(12).recordnumber='|| multb(12).recordnumber|| ' multb(12).currentedate'|| multb(12).currentdate ); End;

给出运行结果：

(2) oracle中的‘数组’与其他编程语言中的数组的区别：

Declare

Type mytabletype Is Table Of Varchar2(9) Index By Binary_Integer; tb mytabletype; Begin

tb(1):='成都市'; tb(2):='太原市'; tb(3):='北京市';

dbms_output.put_line('记录总数：'||to_char(tb.Count));

dbms_output.put_line('第一条记录为：'||tb.First||'其值为：'||tb(tb.First)); dbms_output.put_line('最后条记录为：'||tb.Last||'其值为：'||tb(tb.Last)); dbms_output.put_line('第二条的前一条记录为：'||tb.Prior(2)||'其值为：'||tb(tb.Prior(2)));

第 15 页 共 24 页

dbms_output.put_line('第二条的后一条记录为：'||tb.Next(2)||'其值为：'||tb(tb.Next(2)));

dbms_output.put_line('第二条记录为：'||tb(2)); tb.Delete(2);

dbms_output.put_line('删除第二条记录后的第二条记录为:'||tb(3)); End;

给出运行结果：

5.6. 条件判断语句的使用

阅读并理解以下程序，调试并给出运行结果。 1．If语句：

drop table employee;

create table employee( empno number(4), name varchar2(30), salary number(8,2) );

insert into employee values(7900,'黄蓉',3000); commit;

给出运行结果：

2．Case语句：

为了简化多个选择用if来做判断，这种case区别于编程语言中的case语句多用于多种情况的判断。

第 16 页 共 24 页

给出运行结果：

5.7. 循环语句的使用

阅读并理解以下程序，调试并给出运行结果。 0．建立一个临时表

--建立一个临时表，用于测试 DROP TABLE temp_table; CREATE TABLE temp_table ( num_col NUMBER, char_col VARCHAR2(60) );

1．LOOP语句 --LOOP语句 DECLARE

v_Counter NUMBER := 1; BEGIN LOOP

-- 以loop counter向temp_table Insert纪录 -- .

INSERT INTO temp_table

VALUES (v_Counter, 'Loop index'); v_Counter := v_Counter + 1;

-- 循环终止条件 - when the loop counter > 50 -- 跳出循环.

IF v_Counter > 10 THEN EXIT; END IF; END LOOP; END;

给出运行结果：

第 17 页 共 24 页

2．FOR LOOP语句

--FOR LOOP语句 BEGIN

FOR v_Counter IN 1..10 LOOP

dbms_output.put_line('v_Counter is ' || v_Counter); END LOOP;

--DBMS_OUTPUT.PUT_LINE(v_Counter); --在for循环外不可以访问循环变量 END;

给出运行结果：

3．while LOOP语句

--while LOOP语句 DECLARE

v_Counter NUMBER := 1; BEGIN

--v_Counter <= 10--循环. WHILE v_Counter <= 50 LOOP

dbms_output.put_line('v_Counter is ' || v_Counter); v_Counter := v_Counter + 1; END LOOP; END;

给出运行结果：

第 18 页 共 24 页

4．while语句

--WHILE语句 DECLARE

v_Counter NUMBER := 1; BEGIN

WHILE v_Counter <= 10 LOOP

-- 以loop counter向temp_table Insert纪录 -- .

INSERT INTO temp_table

VALUES (v_Counter, 'Loop index');

v_Counter := v_Counter + 1;

-- 循环终止条件 - when the loop counter > 50 -- 跳出循环.

END LOOP; END;

给出运行结果：

第 19 页 共 24 页

5.8. 综合练习

以下实验中用到的Exam_EMP表结构与内容与Scott模式下的表Emp相同，请用Create ?As命令创建并导入数据。而后，参照前面实验，完成以下实验练习。

1． 计算Exam_EMP表中COMM最高与最低的差值，COMM值为空时按0计算。

写出程序源码：

Declare

var1 number;

var2 number;

val_comm number;

begin

select max(nvl(comm,0)) into var1 from emp;

select min(nvl(comm,0)) into var2 from emp;

val_comm:=var1-var2;

dbms_output.put_line(val_comm); end;

给出运行测试结果：

2． 编写程序，根据表Exam_EMP中deptno字段的值，为姓名为‘JONES’的雇员修改工

资；若部门号为10，则工资加100；部门号为20，加200；其他部门加400。 写出程序源码：

set serveroutput on declare

v_deptno emp.deptno%type; addsal emp.sal%type; sal number;

begin

第 20 页 共 24 页

select deptno into v_deptno from emp where ename='SCOTT'; if

v_deptno='10' then

addsal:=100; elsif

v_deptno='20' then addsal:=300; else

addsal:=400; end if;

update emp set sal=sal+addsal where

ename='SCOTT';

dbms_output.put_line(sal); end;

给出运行测试结果：

3． 编写程序，计算显示部门人数最多的部门号、人数、工资总和，以及部门人数最少的部

门号、人数、工资总和。 写出程序源码：

给出运行测试结果：

4． 计算Exam_EMP中所有雇员的所得税总和。假设所得税为累进税率，所得税算法为：

工资收入为0－2000为免税；收入2000－3000者，超过2000的部分税率10％；3000－4000者超过3000部分按20％税率计算；4000－5000者超过4000部分按30％税率计算；5000以上收入，超过5000部分按40％税率计算。（请查阅累进税率的概念） 写出程序源码： Declare

sum_xx number:=0; xx number;

begin

select sum((sal-2000)*0.1) into xx from emp where sal>2000 and sal<=3000; sum_xx:=sum_xx+xx;

select sum((sal-3000)*0.2+100) into xx from emp where sal>3000 and sal<=4000;

第 21 页 共 24 页

sum_xx:=sum_xx+xx;

select sum((sal-4000)*0.3+300) into xx from emp where sal>4000 and sal<=5000; sum_xx:=sum_xx+xx;

select sum((sal-5000)*0.4+600) into xx from emp where sal>5000; sum_xx:=sum_xx+xx;

dbms_output.put_line(sum_xx); end;

给出运行测试结果：

5． 假设有个表如Exam_EMP，未建立主键，含有多条记录重复（列值完全相同），试编制

一个PL/SQL，将多余的重复记录删除。 写出程序源码： variable al number; begin :al:=1000; end; /

begin

dbms_output.put_line(:al); end; /

给出运行测试结果：

参照前面实验和教材，完成以下实验作业，给出源程序和测试结果。

6． 编写一个PL/SQL程序块，对名字以\或\开始的所有雇员按他们的基本薪水的10%

第 22 页 共 24 页

加薪。

写出程序源码：

declare

cursor

csr_AddSal

is

select * from emp where ENAME LIKE 'A%' OR ENAME LIKE 'S%' for update

OF SAL;

r_AddSal csr_AddSal%rowtype;

saleInfo emp.SAL%TYPE;

begin

for r_AddSal in csr_AddSal loop

dbms_output.put_line(r_AddSal.ENAME||'原来的工

资:'||r_AddSal.SAL);

saleInfo:=r_AddSal.SAL*1.1;

UPDATE emp SET SAL=saleInfo WHERE CURRENT OF csr_AddSal; dbms_output.put_line(r_AddSal.ENAME||'现在的工资:'||r_AddSal.SAL*1.1); end loop; end;

给出运行测试结果：

第 23 页 共 24 页

第 24 页 共 24 页

本文来源：https://www.bwwdw.com/article/o0vw.html