- 浏览: 209783 次
- 性别:
- 来自: 上海
文章分类
- 全部博客 (196)
- Git (16)
- maven (8)
- Python (20)
- linux (14)
- java (30)
- spring (5)
- javascript (4)
- oracle (15)
- jquery (4)
- jbpm4.4 (4)
- ibatis (1)
- svn (1)
- sql server (1)
- sqoop (1)
- photoshop (2)
- 日常记录 (5)
- scala (1)
- IntelliJ IDEA (7)
- mysql (2)
- Hive (1)
- visual studio code (3)
- angularjs (5)
- nodejs (4)
- gradle (1)
- springboot (4)
- jakson (1)
- hibernate (2)
- 面试 (5)
- React (2)
- Kotlin (1)
- Netty (0)
- webstorm (2)
- spring cloud (4)
- redis (1)
- PowerDesigner (1)
- Vue (4)
- easyui (1)
- activiti (2)
- httpClient4.5 (2)
- logback (3)
- ngrinder (1)
- 分库分表 (1)
最新评论
-
严东军:
学习了,谢谢
摘录--DBMS_SCHEDULER -
zwllxs:
你知道你调的是谁的代码不?
jbpm4.4+ssh2 完整请假流程
第一部分: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;
一、使用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;
发表评论
-
简历11111111111
2013-03-12 22:19 673简历的顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶 -
oracle集合操作函数
2010-09-13 10:34 1063集合操作符专门用于合并多条select 语句的结果,包括:UN ... -
摘录--DBMS_SCHEDULER
2010-08-11 16:24 1841Oracle 10g新增了scheduler的功能,功能比jo ... -
oracle的case语句实例
2010-08-09 13:12 941SELECT SUM( CASE ... -
摘录:简单测试job的创建过程案例
2010-08-04 18:37 1154一:简单测试job的创建 ... -
摘录-详解 Oracle job 的使用与管理技巧
2010-08-04 18:35 1141详解 Oracle job 的使用与管理技巧 SVRMGR&g ... -
摘录--关于MV刷新的问题
2010-08-04 10:56 1806关于MV刷新的问题 最近在工作中遇到一个问题,是关 ... -
转:基于远程数据库表,建立本地数据库的物化视图
2010-05-03 14:49 2032基于远程数据库表,建立本地数据库的物化视图2008年12月28 ... -
如何用SQL返回两个日期之间的所有连续日期
2010-04-06 22:28 8985如何用SQL返回两个日期之间的所有连续日期 -------- ... -
qlsql 学习实例代码4
2010-04-03 19:57 1112---------------------------行触发器 ... -
qlsql 学习实例代码3
2010-03-31 22:26 1110------------------------------- ... -
qlsql 学习实例代码2
2010-03-31 15:09 1229------------------------------- ... -
qlsql 学习实例代码1
2010-03-30 23:11 1199准备工作: create table DEPT ( DEP ... -
PL SQL 学习笔记
2010-03-30 15:47 837PL SQL 学习笔记 见附件 作者:丁俊 PLSDE ...
相关推荐
在"Oracle PL/SQL实例精解 数据库建立代码"中,我们将深入探讨如何在Oracle数据库中使用PL/SQL来构建一个名为"student"的模式,这包括创建数据库对象如表、索引,以及填充样本数据。 首先,"student"模式可能包含一...
在"Oracle PL/SQL 实例精解(第4版涵盖Oracle 11g)+源码脚本"中,读者可以深入理解PL/SQL的各种概念和实践技巧。 本书详细讲解了Oracle 11g版本中的PL/SQL语言,这涵盖了从基础语法到高级特性的广泛范围。PL/SQL是...
在"Oracle PL/SQL实例精讲student数据库模式"中,我们将深入探讨如何利用PL/SQL来设计、管理和操作一个名为"student"的数据库模式。 "student"数据库模式可能包含了与学生相关的各种实体,如学生信息表(students)...
本书是一本逐步分解的,详尽的pl/sql编程教程,使用真实场景的试验、范例和练习来介绍读者所需的pl/sql编程技能,涵盖oracle 11g的最新特性。作者的写作手法源自于在哥伦比亚大学教授pl/sql编程技术的经验,深度...
这本书“Oracle PL/SQL实例编程”显然旨在深入讲解如何利用PL/SQL进行实际开发工作,通过实例来教授读者掌握这一技能。下面我们将深入探讨PL/SQL的关键知识点。 1. **基础语法**:PL/SQL是过程化SQL,它包含声明...
本资料集是"Oracle PL/SQL实例精解",提供了丰富的源代码示例,帮助读者深入理解和应用这一技术。 首先,我们来看End_of_Chapter_Exercises.zip,这可能包含了每个章节结束时的练习题或项目。这些练习通常设计得...
《Oracle PL/SQL专家指南-高级PL/SQL解决方案的设计与开发》是一本深入探讨Oracle数据库中的PL/SQL编程的专业书籍。PL/SQL是Oracle数据库特有的编程语言,它结合了SQL的查询能力与过程式编程语言的功能,使得数据库...
Oracle PL/SQL是一种强大的编程语言,它将SQL与过程编程语言的特性相结合,为数据库开发提供了丰富的功能。在Oracle数据库环境中,PL/SQL是开发高效、可靠和可维护的数据库应用程序的关键工具。以下是对"Oracle PL/...
西安电子科技大学出版社出版的《Oracle数据库SQL和PL/SQL实例教程》将通过丰富的实例帮助读者理解和掌握这些概念。读者将学习如何编写高效的SQL查询,以及如何利用PL/SQL进行更复杂的业务逻辑处理。通过实例练习,...
通过本书,读者不仅能够获得丰富的理论知识,还能通过大量的实例和练习来加深对PL/SQL的理解。无论你是初学者还是有经验的开发者,都能从中受益,提升在Oracle环境下的编程技能。 在阅读《Oracle PL/SQL by Example...
在"2174_MasteringPLSQL_CODE"这个压缩包文件中,你将找到一系列与Oracle PL/SQL相关的代码实例,涵盖了以下几个关键知识点: 1. **基础语法**:包括变量声明、常量定义、数据类型、条件语句(IF-THEN-ELSIF,CASE...
在"Oracle数据库 SQL 和 PL/SQL实例教程"中,你可以期待学习到以下几个核心知识点: 1. **SQL基础**:包括数据查询、数据插入、更新和删除(DML语句),以及数据库对象的创建和管理(DDL语句)。例如,如何使用...
通过阅读《Oracle PL/SQL》这本书,法语读者可以系统地学习到上述内容,并通过实例和练习提升自己的编程技能。这本书将帮助读者深入理解Oracle数据库的编程精髓,提升数据库管理与应用开发的能力。
《ORACLE PL/SQL 程序设计(第五版)》是Oracle数据库开发领域的一本经典教程,专注于讲解如何使用PL/SQL这一强大的过程化语言进行数据库编程。这本书旨在帮助读者掌握PL/SQL的基本概念、语法和高级特性,以便在实际...
在"Oracle PL/SQL实例精讲--使用表,课后答案"中,读者可以深入理解如何有效地使用PL/SQL与Oracle数据库中的表格进行交互。这份资源包含第五版的课程内容,提供了实际操作的例子和课后练习的答案,对于学习者来说是...
《Oracle PL/SQL 实例精解》是一本深入探讨Oracle数据库中的PL/SQL编程语言的书籍。PL/SQL,全称Procedural Language/Structured Query Language,是Oracle数据库特有的一种混合了SQL与过程化编程的语言,用于在...
Oracle PL/SQL 是Oracle数据库系统中的过程化语言,它结合了SQL查询功能和高级编程特性,使得开发者可以创建复杂的数据库应用程序。在Oracle 11g R2版本中,PL/SQL与SQL Developer一起使用,提供了丰富的开发环境来...
《Oracle PL/SQL程序设计(第5版)》基于Oracle数据库11g,从PL/SQL编程、PL/SQL程序结构、PL/SQL程序数据、PL/SQL中的SQL、PL/SQL应用构建、高级PL/SQL主题这6个方面详细系统地讨论了PL/SQL以及如何有效地使用它。...