`

oracle pl/sql实例练习

阅读更多
第一部分:oracle pl/sql实例练习(1)
一、使用scott/tiger用户下的emp表和dept表完成下列练习,表的结构说明如下
emp员工表(empno员工号/ename员工姓名/job工作/mgr上级编号/hiredate受雇日期/sal薪金/comm佣金/deptno部门编号)
dept部门表(deptno部门编号/dname部门名称/loc地点)
工资 = 薪金 + 佣金
也可以通过以下脚本测试:
create table DEPT
(
DEPTNO NUMBER(2) not null,
DNAME VARCHAR2(14),
LOC    VARCHAR2(13)
)
tablespace USERS;
alter table DEPT add constraint PK_DEPT primary key (DEPTNO);
insert into DEPT (DEPTNO, DNAME, LOC)
values (10, 'ACCOUNTING', 'NEW YORK');
insert into DEPT (DEPTNO, DNAME, LOC)
values (20, 'RESEARCH', 'DALLAS');
insert into DEPT (DEPTNO, DNAME, LOC)
values (30, 'SALES', 'CHICAGO');
insert into DEPT (DEPTNO, DNAME, LOC)
values (40, 'OPERATIONS', 'BOSTON');
commit;
create table EMP
(
EMPNO    NUMBER(4) not null,
ENAME    VARCHAR2(10),
JOB      VARCHAR2(9),
MGR      NUMBER(4),
HIREDATE DATE,
SAL      NUMBER(7,2),
COMM     NUMBER(7,2),
DEPTNO   NUMBER(2)
)
tablespace USERS;
alter table EMP add constraint PK_EMP primary key (EMPNO);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7369, 'SMITH', 'CLERK', 7902, to_date('17-12-1980', 'dd-mm-yyyy'), 800, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7499, 'ALLEN', 'SALESMAN', 7698, to_date('20-02-1981', 'dd-mm-yyyy'), 1600, 300, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7521, 'WARD', 'SALESMAN', 7698, to_date('22-02-1981', 'dd-mm-yyyy'), 1250, 500, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7566, 'JONES', 'MANAGER', 7839, to_date('02-04-1981', 'dd-mm-yyyy'), 2975, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7654, 'MARTIN', 'SALESMAN', 7698, to_date('28-09-1981', 'dd-mm-yyyy'), 1250, 1400, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7698, 'BLAKE', 'MANAGER', 7839, to_date('01-05-1981', 'dd-mm-yyyy'), 2850, null, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7782, 'CLARK', 'MANAGER', 7839, to_date('09-06-1981', 'dd-mm-yyyy'), 2450, null, 10);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7788, 'SCOTT', 'ANALYST', 7566, to_date('19-04-1987', 'dd-mm-yyyy'), 3000, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7839, 'KING', 'PRESIDENT', null, to_date('17-11-1981', 'dd-mm-yyyy'), 5000, null, 10);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7844, 'TURNER', 'SALESMAN', 7698, to_date('08-09-1981', 'dd-mm-yyyy'), 1500, 0, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7876, 'ADAMS', 'CLERK', 7788, to_date('23-05-1987', 'dd-mm-yyyy'), 1100, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7900, 'JAMES', 'CLERK', 7698, to_date('03-12-1981', 'dd-mm-yyyy'), 950, null, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7902, 'FORD', 'ANALYST', 7566, to_date('03-12-1981', 'dd-mm-yyyy'), 3000, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7934, 'MILLER', 'CLERK', 7782, to_date('23-01-1982', 'dd-mm-yyyy'), 1300, null, 10);
commit;

二、问题:
1.列出至少有一个员工的所有部门。
2.列出薪金比“SMITH”多的所有员工。
3.列出所有员工的姓名及其直接上级的姓名。
4.列出受雇日期早于其直接上级的所有员工。
5.列出最低薪金大于1500的各种工作。
6.列出在每个部门工作的员工数量、平均工资和平均服务期限。
答案:
1.列出至少有一个员工的所有部门。
select deptno from emp group by deptno having count(*)>1;
解析:该语句主要学习分组函数group by,以及对分组后过滤的条件函数having。需要注意的是使用了分组函数语句的select字段中不能包含group by后没有的字段,原因是如果显示非分组的字段就可能显示多条记录,就达不到按某字段分组的目的。Having只能用在分组函数group by后,相当于对分组后的记录做where条件过滤。
2.列出薪金比“SMITH”多的所有员工。
select * from emp where sal>(select sal from emp where ename='SMITH');
解析:该语句主要学习子查询,且子查询在where条件的后面。注意和3的区别。
3.列出所有员工的姓名及其直接上级的姓名。
select ename,(select ename from emp where empno=a.mgr) from emp a;
解析:该语句主要学习子查询,且子查询在select 和 from的中间,同时内部语句使用了外部语句的字段。注意和2的区别。
4.列出受雇日期早于其直接上级的所有员工。
select ename from emp a where hiredate>(select hiredate from emp where empno=a.mgr);
解析:该语句主要学习子查询,且子查询在where条件的后面。同时内部语句使用了外部语句的字段。注意和2的区别。注意和2的区别。
5.列出最低薪金大于1500的各种工作。
select job,min(sal) msal from emp group by job having min(sal)>1500;
解析:该语句主要学习分组函数,功能同1。
6.列出在每个部门工作的员工数量、平均工资和平均服务期限。
select deptno,count(*), trunc(avg(sal+nvl(comm,0))) avgsal, trunc(avg(sysdate-hiredate)) avgday from emp group by deptno;
解析:该语句主要学习分组函数,同时学习其相关的统计函数count, avg等。
三、以下是过程、函数的学习事例。所有的过程和函数都放到了package下面。
包声明:
create or replace package MyPack1 is
--过程模板
procedure P_Templet(i_myNumber In Number,o_myVarchar Out varchar2);
function f_Templet(i_a1 varchar2,i_a2 Number) return number;
--声明一个游标
type Ref_Cursor_Type is ref cursor;
--列出至少有一个员工的所有部门
procedure p_test1;
--列出薪金比“SMITH”多的所有员工
function f_test2(i_ename emp.ename%type) return number;
end MyPack1;
包实现:
create or replace package body MyPack1 is
--过程模板
    procedure P_Templet(i_myNumber In Number,
       o_myVarchar Out varchar2) is
    myDate      date;
    myNumber    Number(10);
    myVarchar   varchar2(50);
begin
       null;
       exception
       when others then
       dbms_output.put_line('程序出现了异常!'||Sqlcode||Sqlerrm);
end;
---------------------------------------------------
--函数模板
function f_Templet(i_a1 varchar2,i_a2 Number) return number is
    myDate      date;
    myNumber    Number(10);
    myVarchar   varchar2(50);
begin
       null;
       exception
       when others then
      dbms_output.put_line('程序出现了异常!'||Sqlcode||Sqlerrm);
       return myNumber;
end;
-----------------------------------------------------
--列出至少有一个员工的所有部门
--用一个sql语句实现:select count(*),deptno from emp group by deptno having count(*)>1;
--主要学习过程的写法,以及游标的用法。
procedure p_test1 is
   v_deptno         dept.deptno%type;
   v_deptname         dept.dname%type;
   v_count1    Number(10);
   C1               Ref_Cursor_Type;
begin
        open C1 for
          select distinct deptno,dname from dept;
         loop
        fetch C1
          into v_deptno,v_deptname;
           exit when C1%notfound;
             begin
             select count(*) into v_count1 from emp e where e.deptno=v_deptno;
             if(v_count1>0) then
               Dbms_Output.Put_Line(v_deptname);
             else null;
             end if;
             end;
        end loop;
       exception
      when others then
        null;
end;
-----------------------------------------------------
--列出薪金比“SMITH”多的所有员工
--用一个sql语句实现:select * from emp where sal>(select sal from emp where ename='SMITH');
-- 调用该函数的语句:select * from (select ename,mypack1.f_test2(ename) as count1 from(select --distinct t.ename from emp t) ) where count1>0
--主要学习函数的写法,学习外部sql如何调用该函数,因为这样调用对条件控制或分页等更灵活,同--时降低了函数的复杂性。
function f_test2(i_ename emp.ename%type) return number is
   v_sal    Number(10);
   v_sal2    Number(10);
   v_return    Number(10);
begin
       begin
            select sal into v_sal from emp where ename='SMITH';
            exception
                when others then
                v_sal :=0;
       end;
       begin
            select sal into v_sal2 from emp where ename=i_ename;
            exception
                when others then
                v_sal2 :=0;
       end;
       v_return := v_sal2-v_sal;
       return v_return;
end;
end MyPack1;
---------------------------------------------------------------
第二部分:oracle pl/sql实例练习(2)
--创建测试表
create table TESTTABLE
(
ID          NUMBER(4) not null,
CURRENTDATE DATE not null
)
--------------------------------------------------------------
实例1
--该实例要学习如何通过pl/sql批量插入数据,以及常量、For的用法。
Declare
maxrecords Constant Int:=100;
i Int:=1;
Begin
        For i In 1..maxrecords Loop
            Insert Into testtable(id,currentdate)
            Values(i,Sysdate);
            dbms_output.put_line('现在插入的内容是:'||i||'   '||Sysdate);
            Commit;         --这里切记要commit否则将不会将数据提交到表中
        End Loop;
        dbms_output.put_line(maxrecords||'条记录已经插入。');
End;
------------------------------------------------------------------
实例2
--这种写法是为了简化多个选择用if来做判断。
Declare
        v_test Int:=60;      
        Begin
             Case v_test
             When 90 Then
             dbms_output.put_line('v_test的值为:90!');
             When 80 Then
             dbms_output.put_line('v_test的值为:80!');
             Else
             dbms_output.put_line('v_test的值我不知道!');
             End Case;
        End;
      
------------------------------------------------------
实例3
--完整定义了一个记录类型的变量,简单应用。这里要学习如何使用Record类型变量。
Declare
        Type myrecord Is Record(
             r_Id Number(4),
             r_currentdate Date
             );
        v_myrecord myrecord;
        Begin
             Select * Into v_myrecord From testtable Where id=10;
             dbms_output.put_line('用记录类型的变量取出来的值为:'||
                                  v_myrecord.r_Id||' '||v_myrecord.r_currentdate);
        End;
------------------------------------------------------------
实例4
--学习使用%Rowtype定义变量
Declare
        v_myrow testtable%Rowtype;
        Begin
             Select * Into v_myrow From testtable Where id=20;
             dbms_output.put_line('用rowtype查询的结果是:'||v_myrow.id||v_myrow.currentdate);
        End;
---------------------------------------------------------------
实例5
--学习如何定义一维表变量,以及如何给一维表变量赋值。这种变量类似编程语言中的一维数组
Declare
       Type mytbtype1 Is Table Of Varchar2(4) Index By Binary_Integer;
       Type mytbtype2 Is Table Of testtable.id%Type Index By Binary_Integer;
       tb1 mytbtype1;
       tb2 mytbtype2;
       Begin
            tb1(1):='大学';
            tb1(2):='大专';
            dbms_output.put_line(tb1(1)||tb1(2));
            select id BULK COLLECT into tb2 from testtable where id<10 order by id;
            For i In 1..tb2.count Loop
              dbms_output.put_line(tb2(i));
            End Loop;
       End;
     
     
------------------------------------------------------------
实例6
--定义一个多维表变量,这就像一个二维数组
--当然这个二维的数组的下表就有些区别与我们在编程语言中熟悉的二维数组了
--可以理解为一维存储的列名,而另一维则是存储与一维列名相对应的数据
Declare
       --这里区别与一维表变量的定义
       Type multbtype Is Table Of testtable%Rowtype Index By Binary_Integer;
       multb multbtype;
       Begin
            Select * Into multb(1)
            From testtable
            Where id=88;
            dbms_output.put_line('multb(1).id='||
                                 multb(1).id||
                                 ' multb(1).currentedate'||
                                 multb(1).currentdate
                                 );
       End;
     
     
     
--------------------------------------------------------------------
实例7  
--这里主要学习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)));
            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));
            --exists存在的问题,不知到怎么使用
            --tb.Exists('太原市');
       End;
     
---------------------------------------------------------------------
实例8
--学习如何定义和使用游标,这里展示了%isopen,%found,%Rowcount
Declare
       Cursor mycursor Is
              Select * From testtable e
              Where e.id<100;
       currentrecord mycursor%Rowtype;
       Begin
            --打开游标
            If mycursor%Isopen Then
               dbms_output.put_line('该游标已经打开了,正在关闭!');
               Close mycursor;
            Else
               dbms_output.put_line('游标已经关闭');
            End If;
            --读取数据
            Open mycursor;--打开游标时最好做判断当前游标是否是关闭的,这里略写
            Loop
            Fetch mycursor Into currentrecord;
            Exit When mycursor%Notfound;
            Begin
                dbms_output.put_line('游标已经取到数据,查询结果是:');
                dbms_output.put_line(to_char(currentrecord.id||currentrecord.currentdate));
    End;
    End Loop;
    Close mycursor;--关闭游标时最好做判断当前游标是否是打开的,这里略写
            --读取记录总条数
            Open mycursor;
            Loop
                Fetch mycursor Into currentrecord;          
            Exit When mycursor%Notfound;
            End Loop;
            dbms_output.put_line('查结果总共有:'||mycursor%Rowcount);
            --关闭游标
            If mycursor%Isopen Then
             dbms_output.put_line('正在关闭游标。');                                                           
            Close mycursor;
            End If;
       End;
--------------------------------------------------------------------------
实例9
--学习存储过程如何带输入参数和输出参数
Create Or Replace Procedure myprocedure(
       i_id In testtable.id%Type,
       o_currentdate Out testtable.currentdate%Type) Is
       v_currentdate testtable.currentdate%Type;
       Begin
            Select currentdate Into v_currentdate From testtable t Where t.id=i_id;
            dbms_output.put_line('currentdate:'||v_currentdate);
            o_currentdate:=v_currentdate;
            Exception
            When Others Then
            dbms_output.put_line('程序出现了异常!'||Sqlcode||Sqlerrm);
       End;
分享到:
评论

相关推荐

    Oracle PL/SQL实例精解 数据库建立代码

    在"Oracle PL/SQL实例精解 数据库建立代码"中,我们将深入探讨如何在Oracle数据库中使用PL/SQL来构建一个名为"student"的模式,这包括创建数据库对象如表、索引,以及填充样本数据。 首先,"student"模式可能包含一...

    Oracle PL/SQL 实例精解(第4版涵盖Oracle 11g)+源码脚本

    在"Oracle PL/SQL 实例精解(第4版涵盖Oracle 11g)+源码脚本"中,读者可以深入理解PL/SQL的各种概念和实践技巧。 本书详细讲解了Oracle 11g版本中的PL/SQL语言,这涵盖了从基础语法到高级特性的广泛范围。PL/SQL是...

    oracle pl/sql实例精讲student数据库模式数据和表脚本

    在"Oracle PL/SQL实例精讲student数据库模式"中,我们将深入探讨如何利用PL/SQL来设计、管理和操作一个名为"student"的数据库模式。 "student"数据库模式可能包含了与学生相关的各种实体,如学生信息表(students)...

    Oracle PL/SQL实例编程(PL/SQL经典书籍)

    这本书“Oracle PL/SQL实例编程”显然旨在深入讲解如何利用PL/SQL进行实际开发工作,通过实例来教授读者掌握这一技能。下面我们将深入探讨PL/SQL的关键知识点。 1. **基础语法**:PL/SQL是过程化SQL,它包含声明...

    oracle pl sql 实例精解 源代码

    本资料集是"Oracle PL/SQL实例精解",提供了丰富的源代码示例,帮助读者深入理解和应用这一技术。 首先,我们来看End_of_Chapter_Exercises.zip,这可能包含了每个章节结束时的练习题或项目。这些练习通常设计得...

    Oracle PL/SQL专家指南-高级PL/SQL解决方案的设计与开发

    《Oracle PL/SQL专家指南-高级PL/SQL解决方案的设计与开发》是一本深入探讨Oracle数据库中的PL/SQL编程的专业书籍。PL/SQL是Oracle数据库特有的编程语言,它结合了SQL的查询能力与过程式编程语言的功能,使得数据库...

    Oracle PL/SQL学习官方教材

    Oracle PL/SQL是一种强大的编程语言,它将SQL与过程编程语言的特性相结合,为数据库开发提供了丰富的功能。在Oracle数据库环境中,PL/SQL是开发高效、可靠和可维护的数据库应用程序的关键工具。以下是对"Oracle PL/...

    Oracle数据库SQL和PL/SQL实例教程

    西安电子科技大学出版社出版的《Oracle数据库SQL和PL/SQL实例教程》将通过丰富的实例帮助读者理解和掌握这些概念。读者将学习如何编写高效的SQL查询,以及如何利用PL/SQL进行更复杂的业务逻辑处理。通过实例练习,...

    Oracle PL/SQL by Example(4th Edition)

    通过本书,读者不仅能够获得丰富的理论知识,还能通过大量的实例和练习来加深对PL/SQL的理解。无论你是初学者还是有经验的开发者,都能从中受益,提升在Oracle环境下的编程技能。 在阅读《Oracle PL/SQL by Example...

    《精通Oracle PL/SQL》源码

    在"2174_MasteringPLSQL_CODE"这个压缩包文件中,你将找到一系列与Oracle PL/SQL相关的代码实例,涵盖了以下几个关键知识点: 1. **基础语法**:包括变量声明、常量定义、数据类型、条件语句(IF-THEN-ELSIF,CASE...

    oracle数据库 sql 和pl/sql实例教程

    在"Oracle数据库 SQL 和 PL/SQL实例教程"中,你可以期待学习到以下几个核心知识点: 1. **SQL基础**:包括数据查询、数据插入、更新和删除(DML语句),以及数据库对象的创建和管理(DDL语句)。例如,如何使用...

    oracle_oracle_oraclepl/sql_

    通过阅读《Oracle PL/SQL》这本书,法语读者可以系统地学习到上述内容,并通过实例和练习提升自己的编程技能。这本书将帮助读者深入理解Oracle数据库的编程精髓,提升数据库管理与应用开发的能力。

    ORACLE PL/SQL 程序设计(第五版)

    《ORACLE PL/SQL 程序设计(第五版)》是Oracle数据库开发领域的一本经典教程,专注于讲解如何使用PL/SQL这一强大的过程化语言进行数据库编程。这本书旨在帮助读者掌握PL/SQL的基本概念、语法和高级特性,以便在实际...

    Oracle PL/SQL实例精讲--使用表,课后答案

    在"Oracle PL/SQL实例精讲--使用表,课后答案"中,读者可以深入理解如何有效地使用PL/SQL与Oracle数据库中的表格进行交互。这份资源包含第五版的课程内容,提供了实际操作的例子和课后练习的答案,对于学习者来说是...

    《Oracle PL/SQL 实例精解》说明

    《Oracle PL/SQL 实例精解》是一本深入探讨Oracle数据库中的PL/SQL编程语言的书籍。PL/SQL,全称Procedural Language/Structured Query Language,是Oracle数据库特有的一种混合了SQL与过程化编程的语言,用于在...

    oracle pl/sql 实例精解(中文原书第4版)

    本书是一本逐步分解的,详尽的pl/sql编程教程,使用真实场景的试验、范例和练习来介绍读者所需的pl/sql编程技能,涵盖oracle 11g的最新特性。作者的写作手法源自于在哥伦比亚大学教授pl/sql编程技术的经验,深度...

    oracle PL/SQL

    Oracle PL/SQL 是Oracle数据库系统中的过程化语言,它结合了SQL查询功能和高级编程特性,使得开发者可以创建复杂的数据库应用程序。在Oracle 11g R2版本中,PL/SQL与SQL Developer一起使用,提供了丰富的开发环境来...

    Oracle PL/SQL程序设计(第5版)(下册)第二部分

    《Oracle PL/SQL程序设计(第5版)》基于Oracle数据库11g,从PL/SQL编程、PL/SQL程序结构、PL/SQL程序数据、PL/SQL中的SQL、PL/SQL应用构建、高级PL/SQL主题这6个方面详细系统地讨论了PL/SQL以及如何有效地使用它。...

Global site tag (gtag.js) - Google Analytics