数_据_库_题_目_Oracle

conn sys/sys as sysdba;
conn scott/tiger;
1.本机使用
  启动“Oracle”实例:OracleServiceORCL
  配置监听-->重新配置-->next-->否-->完成
2.网络连接
  还要启动监听器:OracleOraDb10g_home1TNSListener


1.Oracle10g安装(在自己的笔记本上安装)
2.配置监听器和服务名，并进行本机和它机的连接测试
3.创建表空间tbs1，数据文件为tbs1.dbf，自动增长
  create tablespace tbs1
  datafile 'E:\tbs1.dbf'
  size 2m
  autoextend on;

4.创建aptech用户名，密码aptech，默认表空间为tbs1
  create user aptech
  identified by aptech
  default tablespace tbs1

--创建表空间
create tablespace accp
datafile 'D:\'
size 10m;

--创建用户
create user liuxiaobin
identified by liuxiaobin
default tablespace accp;

alter user liuxiaobin default tablespace accp;

5.修改用户密码为accp
  alter user huzi identified by accp;

6.以sys或system用户身份给aptech用户分配connect及resource权限
grant connect to huzi; --授予连接权限
grant resource to huzi;--授予资源使用权限
grant create table to huzi;  --创建表的权限

7.赋予aptech用户查询scott的emp表的权限，并测试
  grant select on emp to aptech;

建表
create table User_Info(
u_Id char(5) not null,
u_Name varchar(10) not null,
u_Sex varchar(5) not null,
u_Address varchar(20) not null,
constraint PK_User_Info primary key(u_Id)
);
添加数据
insert into User_Info(u_Id,u_Name,u_Sex,u_Address) values ('006','xbliuc','男','湖南攸县');
删除数据
    delete from User_Info where u_Id = '006'; --删除一行数据
    truncate User_Info;  --清除表中所有数据
    drop table User_Info;  --删除表

修改数据
    update User_Info set u_Name='junge',u_Address='海南岛' where u_Id='005';


  为每个函数写一条测试sql语句。函数如下：
  //把单词首字母大写
  Initcap():SELECT INITCAP('the soap') "Capitals" FROM DUAL;

  //大写转换成小写
  Lower():SELECT LOWER('MR. SCOTT MCMILLAN') "Lowercase"FROM DUAL;

  //小写转换成大写
  Upper():SELECT UPPER('Large') "Uppercase"FROM DUAL;

  //
  Ltrim():SELECT LTRIM('xyxXxyLAST WORD','xy') "LTRIM example"FROM DUAL;

  //
  Rtrim():SELECT RTRIM('BROWNINGyxXxy','xy') "RTRIM example"FROM DUAL;
     Translate(): SELECT TRANSLATE('2KRW229',
    '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
    '9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX') "License"
     FROM DUAL;

  //
  Replace():SELECT REPLACE('JACK and JUE','J','BL') "Changes"FROM DUAL;

  //
  Instr():SELECT INSTR('CORPORATE FLOOR','OR', 3, 2)"Instring" FROM DUAL;

  //截取字符   位置从1开始
  Substr():SELECT SUBSTR('ABCDEFG',3,4) FROM DUAL;

  //
  Concat(pr1, expr2):SELECT CONCAT(CONCAT(last_name, '''s job category is '),job_id) "Job"
   FROM employees
   WHERE employee_id = 152;

  //把数值型转换成字符型
  CHR():SELECT CHR(67)||CHR(65)||CHR(84) "Dog" FROM DUAL;

  //把字符型型转换成数值型
  ASCII():SELECT ASCII('Q') FROM DUAL;

  //从左边开始添加字符
  LPAD():SELECT LPAD('Page 1',15,'*.') "LPAD example"FROM DUAL;

  //从右边开始添加字符
  RPAD():SELECT RPAD('MORRISON',12,'ab') "RPAD example"FROM DUAL;

  //去掉两边的0
  TRIM():SELECT TRIM (0 FROM 0009872348900) "TRIM Example"FROM DUAL;

  //字符的长度
  LENGTH():SELECT LENGTH('CANDIDE') "Length in characters"FROM DUAL;

  //
  DECODE():SELECT product_id,
       DECODE (warehouse_id, 1, 'Southlake',
                             2, 'San Francisco',
                             3, 'New Jersey',
                             4, 'Seattle',
                                'Non-domestic')
       "Location of inventory" FROM inventories
       WHERE product_id < 1775;


  做题的格式如下：
  Initcap(char):将字符串中每个单词的第一个字母变成大写
  select initcap('hello world') from dual;  --测试语句
  =>> Hello World  --表示结果

要求：结果全部以UE编辑器编写并上传,注意格式工整

1.创建表空间accpspace，数据文件accp.dbf,自动增长。
  create tablespace ccpspace
  datafile 'E:\accp.dbf'
  size 2m
  autoextend on;

2.创建用户accp,分配默认表空间为accpspace。
  create user accp
  identified by aptech
  default tablespace accpspace

3.使用sys账号给accp用户授必要的权限。
  grant connect to accp;
  grant resource to accp;

4.通过accp用户连接到oracle数据库服务器，创建数据测试表
  student（id,name,sex）并插入测试数据。

5.在项目开发过程中连接Oracle数据库,对客户端和服务器各有什么要求?
  Oracle数据库服务器由哪几部分组成?
  答：客户端：启动Oracle实例
      服务器：启动监听器
      Oracle数据库  和 Oracle实例

以scott用户下emp和dept表为基础,完成以下操作:
--emp表结构如下
SQL> desc emp;
名称                                      是否为空? 类型
----------------------------------------- -------- -------
EMPNO   (编号)                             NOT NULL NUMBER(4)
ENAME   (姓名)                                   VARCHAR2(10)
JOB     (工作)                                    VARCHAR2(9)
MGR     (部门经理)                                  NUMBER(4)
HIREDATE(雇佣日期)                                      DATE
SAL     (工资)                                    NUMBER(7,2)
COMM    (佣金)                                    NUMBER(7,2)
DEPTNO  (部门编号)                                  NUMBER(2)

--dept表结构如下
SQL> desc dept;
名称                                      是否为空? 类型
----------------------------------------- -------- ----------------------------
DEPTNO  (编号)                                  NOT NULL NUMBER(2)
DNAME   (部门名称)                                    VARCHAR2(14)
LOC     (地址)                                        VARCHAR2(13)

1.选择部门30中的雇员
  select * from emp where DEPTNO = 30;

2.列出所有办事员的姓名、编号和部门
  select * from emp;

3.找出佣金(COMM)高于薪金SAL的雇员
   select * from emp where COMM > SAL;

4.找出没有佣金(COMM)的雇员的姓名、编号和部门
   select ENAME,EMPNO from emp where COMM = 0;

5.找出收取佣金的雇员编号,名字和工作类别.
  select ename,empno,job from emp where comm > 0;

6.找出部门10中所有经理和部门20中所有办事员的详细资料(采用集合查询)
  select * from emp where DEPTNO=10
  intersect
  select * from emp where DEPTNO=20;

7.显示工资高于2500并且岗位为'MANAGER'的雇员信息(采用集合查询)
  select * from emp where SAL>2500
  intersect
  select * from emp where JOB='MANAGER';

8.找出不收取佣金或收取的佣金低于100的雇员
   select * from emp COMM is null or COMM<100

9.找出各月最后一天受雇的所有雇员(使用日期函数)
  select * from emp where LAST_DAY(HIREDATE) = HIREDATE;

10.找出早于12年之前受雇的雇员(使用日期函数)
   select * from emp where EXTRACT(year from sysdate) - EXTRACT(year from HIREDATE) > 12;

11.显示只有首字母大写的所有雇员的姓名
   select ename from emp where ename=initcap(ename);

12.显示姓名正好为15个字符的雇员详细信息
   select * from emp where length('ename')=15;

13.显示姓名不带有"R"的雇员详细信息
   select *  from emp where instr(ename,'R')=0;

14.显示所有雇员的姓名的前三个字符
   select substr(ENAME,0,3) from emp;

15.显示所有雇员的姓名，用a替换所有"A"
   select translate(ename,'A','a') from emp;

16.显示雇员的详细资料,按姓名排序
   select * from emp order by  ename;

17.显示雇员姓名，根据其服务年限，将最老的雇员排在最前面
   select * from emp order by HIREDATE desc;

18.显示雇员姓名，工作和薪金，按工作的降序顺序排序，而工作按薪金排序
  select * from emp order by job desc,sal;

19.显示所有雇员的姓名和加入公司的年份和月份,按雇员受雇日所在月排序,
   并将最早年份的姓名排在最前面
   select ename,EXTRACT(year from HIREDATE) as year,EXTRACT(month from HIREDATE) as month
from emp order by year,month;

20.显示在一个月为30天的情况下所有雇员的日薪金,忽略余数
   select trunc((decode(comm,'',0,comm)+sal)/30) from emp;

21.找出在(任何年份的)2月受雇的所有雇员
   select * from emp where EXTRACT(month from HIREDATE) = 2;

22.对于每个雇员，显示其加入公司的天数(日期相减得天数)
   select ename,round(to_number(sysdate-HIREDATE)) as day from emp;

23.显示姓名字段的任何位置包含"A"的所有雇员的姓名
   select ename from emp where instr(ename,'A')>0;

24.显示员工的部门、姓名、工资和“新工资”,“新工资”的现实标准为：
   如果deptno=10,则“新工资”=sal*1.1,
   如果deptno=20,则“新工资”=sal*1.2,
   否则“新工资”=sal
   提示：使用decode()函数
   select ename,decode(deptno,10,sal*1.1,20,sal*1.2,sal) newgongzi from emp;

25.显示员工所有信息，如果COMM列为"null"则显示为"0"
   select nvl(ENAME,0) from emp;

26.显示员工的姓名(ename),工资(sal),佣金(comm)和总收入(总收入=sal+comm)
   select ename 姓名,sal 工资,comm 佣金,(sal+comm) 总收入 from emp;





create table UserInfo(
uId number(5) not null,
uName varchar2(10) not null,
uSex varchar2(10) not null,
uAddress varchar2(10) null
);
1.创建如下结构的书籍类别表booktype和book表，在两个表中插入测试数据。
名称                  是否为空? 类型
--------------------- -------- ---------------
TYPENO   (编号,主键)           NOT NULL VARCHAR2(6)
TNAME    (类别名)                      VARCHAR2(20)
TDES     (描述)                     VARCHAR2(100)

名称                  是否为空? 类型
--------------------- -------- -------------
ID       (序号)                     NUMBER(14)
BNO      (书编号)                  VARCHAR2(20)
BNAME    (书名)                    VARCHAR2(20)
BAUTHOR  (作者)                    VARCHAR2(20)
PDATE    (出版日期)                      DATE
BDATE    (购买日期)                      DATE
PRICE    (价格)                    NUMBER(6,2)
WCOUNT   (字数)                    NUMBER(10)
TYPENO   (类别编号)                VARCHAR2(6)

ch2.修改booktype表的结构，将类别说明字段tdes字符数扩大1倍。
  alter table booktype modify TDES VARCHAR2(200)

3.修改booktype表的结构，将类别说明字段tdes删除。
  alter table booktype drop column TDES

4.修改book表的结构，将序号字段id设为主键。
  alter table book add constraint PK_id primary key(id);

5.修改book表的结构，将类别编号字段typeno设为引用booktype表typeno字段的外键。
  alter book add constrant foreign key (typeno) references booktype(typeno);

6.要使其它用户（比如scott）也能访问这里创建的book表，如何做？
  grant all on book to scott;

7.写语句实现将scott的dept和emp表记录复制到当前用户的dept和emp表中。
  conn scott/tiger
grant select on dept to 当前用户
grant select on emp to 当前用户
以当前用户登录
create table dept as select * from scott.dept
create table emp as select * from scott.emp

8.显示符合以下条件的书籍的名称：
  a.)购买于2006年7月1日之前；
  select bname from book
  where extract(year from bdate <2006
   or (extract(year from bdate =2007 and extract(month from bdate) <7);

  b.)价格低于100元或超过300元；
    select bname from book where price < 100 or price > 300;
9.编写一条语句，按书籍的购买日期对结果进行排序，从购买日期最近的书籍开始。
  select * from book order by bdate desc;

10.对于EMP表,编写一个语句只有当最低工资少于1000，而且最高工资超
  过2500时,才显示部门编号以及部门支付的最低工资和最高工资。(分组查询)
  select deptno,max(sal),min(sal) from emp
group by deptno
having max(sal) > 2500 and min(sal) < 1000;

11.对于EMP表,编写一个语句显示各部门的每个工作类别支付的最高工资。
  select dept.dname ,max(sal) from emp
inner join dept
on dept.deptno = emp.deptno
group by dept.dname;

12.获得北京奥运会开幕式那天的下一个星期六是几号。
  select next_day('2008-08-08','Saturday') from dual;

13.计算EMP表中员工的年收入,包括月工资和提成(奖金).
  select empno,ename,((sal+decode(comm,'',0,comm))*12) as yearSal from emp;

14.任意给定一个字符串,其中包括了字符和数字,试图分别
   把其中的数字和文本字符分开,并得到它们(使用’translate‘函数)
   select translate('123abc','123abc','123') as num,
translate('123abc','123abc','abc') as word from dual;


15.请说说Oracle中的ROWID与ROWNUM的区别和作用?
   区别：ROWID:标识一行的唯一编号
         ROQNUM:表示查询出结果集的行编号
   作用：ROWID:用户创建索引和排序
         ROWNUM:用于实现分页查询

16.oracle数据库中,SQL命令分为哪几类,请分别写出他们中文全称及包含的详细命令
   DDL(数据定义语言): create,drop,alter,truncate
   DML(数据操作语言): select,insert,update,delete
   TCL(事务控制语言): commit,savepoint,rollback
   DCL(数据控制语言): constr,revoke


Rem ==============================================================================
    第三章上机作业
Rem ==============================================================================
EMPNO   (编号)                             NOT NULL NUMBER(4)
ENAME   (姓名)                                   VARCHAR2(10)
JOB     (工作)                                    VARCHAR2(9)
MGR     (部门经理)                                  NUMBER(4)
HIREDATE(雇佣日期)                                      DATE
SAL     (工资)                                    NUMBER(7,2)
COMM    (佣金)                                    NUMBER(7,2)
DEPTNO  (部门编号)                                  NUMBER(2)

--dept表结构如下
SQL> desc dept;
名称                                      是否为空? 类型
----------------------------------------- -------- ----------------------------
DEPTNO  (编号)                                  NOT NULL NUMBER(2)
DNAME   (部门名称)                                    VARCHAR2(14)
LOC     (地址)

1.创建一个包含1982年3月31日之后入职的所有雇员的视图.
  create view view_emp1
  select * from emp
  where HIREDATE >to_Date('1982-03-31','yy-mm-dd');

2.创建一个包含佣金高于其薪金的雇员的视图
  create view view_emp2 as select * from emp where COMM >SAL;

3.创建一个包含所有雇员的雇员编号、雇员名称、部门名称和薪金的视图。
  create view view_emp3 as
  select EMPNO,ENAME,SAL,DNAME from emp,dept
  where emp.DEPTNO = dept.DEPTNO;

4.创建一个包含所有没有佣金的雇员的视图.
  create view view_emp4 as select * from emp where COMM is null or COMM = 0;

5.创建一个包含各种工作的薪金总和的视图。
  create view view_emp5 as select sum(sal) as from emp;

6.创建序列SQ:开始值1、增量1、最大值100、最小值1,不循环、cache为30,创建后显示序列的初始值
  create sequence seq_emp1
  start with 10
  increment by 1
  maxvalue 100
  minvalue 1
  nocycle
  cache 30;

7.修改序列SQ:最大值300,循环
  alter sequence seq_emp1 maxvalue 300 cycle;

8.假定accp用户可以访问scott的emp表,请分别为scott.emp创建一个私有同义和共有同义词(注意授权)
              conn scott/tiger
              grant all on emp to accp;
              conn sys/sys as sysdba;
  私有同义词：create synonym accp1 for scott.emp;

              grant create create synonym,create public synonym to accp;
              conn accp/accp;
  公有同义词：create public synonym accp2 for scott.emp;

11.在emp表的empno字段上创建一个标准索引,在job字段上创建位图索引,在ename字段上创建函数索引,
   将ename转化为小写
   标准索引：create index ind_emp1 on emp(empno);
   位图索引: create bitmap index ind_emp2 on emp(ename);
   函数索引：create index ind_emp3 on emp(upper(ename));

12.显示所创建的全部视图、同义 ，序列和索引信息
   desc user_sequences;   -- 数据字典表
   select sequence_name from user_sequences;  --序列
   desc user_views;     --视图
   desc user_synonyms;  --同义词
   desc user_indexes;   --索引

13.删除您创建的任何视图的基表,然后尝试查询视图,结果是什么。
   基表不存在，视图无法查询

14.删除您创建的所有视图、同义 、序列和索引
   删除视图：  drop view view_emp;
   删除同义词：drop synonym emp;
   删除序列：  drop sequence syn_emp;
   删除索引：  drop index ind_emp;

15.视图有什么优点?
   1.保证数据的安全性
   2.隐藏数据的复杂性
   3.简化SQL命令
   4.隔离基表结构的变化
   5.通过重命名列，提供新的数据


Rem ==============================================================================
    第三章家庭作业
Rem ==============================================================================
1.如何从数据字典中获得序列的相关信息
  desc user_sequences;   -- 数据字典表
  select sequence_name from user_sequences;  --序列

2.公共同义词和私有同义词有什么区别
  区别：公有同义词能让所有数据库用户访问，
        私有同义词只能在其模式内访问，且不能与当前模式的对象重名

3.怎样利用视图进行DML操作，该DML操作应当遵循什么原则?(参考PPT)
  只能修改一个底层的基表
  如果修改违反了基表的约束条件，则无法更新视图
  如果视图包含连接操作符、DISTINCT 关键字、集合操作符、聚合函数或 GROUP BY 子句，则将无法更新视图
  如果视图包含伪列或表达式，则将无法更新视图

4.怎样使用CHECK OPTION 、WITH， READ ONLY子句,各有什么作用?
   create view view_emp as
   select * from emp where id = 1
   with check option  //检查约束
   with read only;    //设置是否为只读

5.请说明各种连接查询的特点
  内连接:结果集为所有符合条件的数据
左外连接:查询结果为左边表中的数据，与右边表不匹配的以空代替
右外连接:查询结果为右边表中的数据，与左边表不匹配的以空代替
交叉连接:A中的数据会与B中每一条数据进行匹配，如果没有匹配上就用空代替

6.Oracle的索引创建方式有哪几种?(参考PPT)
  create index index_name on tableName(column_name);--标准索引
create bitmap index index_name on tableName(column_name)--位图索引
create index index_name on tableName(coulumn_name1,column_name2);--组合索引
create index index_name on tableName(lower(column_name))--函数索引
create index index_name on tableName(column_name) reverse;--反向键索引

7.更多的索引意味着更高的性能吗,请简单说明理由？(查找相关资料回答)
  不是，因为服务器要对索引进行维护，不是,如果索引过多的话，数据量很大的话，使用索引反而会使查询时间更长

8.Oracle中锁的作用是什么,类型有哪两种,分别如何添加锁
  锁是数据库用来控制共享资源并发访问的机制
保护正在被修改的数据
分为表级锁、行级锁
insert  delete update 和 select ... for update Oracle自动应用行级锁；
添加表级锁:Lock table tableName in mode MODE;


Rem ==============================================================================
    第四章上机作业
Rem ==============================================================================


1.编写PL/SQL块，接收部门输入，然后使用CASE语句判断条件并更新 雇员工资
  如果部门为10，则为雇员增加10%的工资;
  如果部门为20，则为雇员增加8%的工资;
  如果部门为30，则为雇员增加5%的工资;
  输入其他数字则显示“部门不存在”;
  BEGIN
  CASE &部门
      WHEN '10' THEN DBMS_OUTPUT.PUT_LINE('雇员增加10%的工资');
                update emp set sal = sal+sal*0.1;
      WHEN '20' THEN DBMS_OUTPUT.PUT_LINE('雇员增加8%的工资');
                update emp set sal = sal+sal*0.08;
      WHEN '30' THEN DBMS_OUTPUT.PUT_LINE('雇员增加5%的工资');
            update emp set sal = sal+sal*0.05;
          ELSE DBMS_OUTPUT.PUT_LINE('部门不存在');
  END CASE;
  END;

2.使用PL/SQL实现将用户输入的数字反转后输出
  方法一
  declare
num number;
str varchar(20) default '';
begin
num := &输入的数字;
for i in 1..length(num)
loop
dbms_output.put_line(to_char(Substr(num,i,1)));
str := to_char(Substr(num,i,1)) + NvL(str,'');
end loop;
dbms_output.put_line(str);s
end;

  方法二
declare
num number;
begin
num := &输入的数字;
for i in reverse 1..length(num)
loop
dbms_output.put_line(Substr(num,i,1));
end loop;
end;

  方法三
declare
num number;
num1 number;
begin
num := &输入的数字;
for i in 1..length(num)
loop
num1 := num mod 10;
num := num/10;
dbms_output.put_line(floor(num1 ));
end loop;
end;


3.使用PL/SQL将100以内的素数输出(素数:只能被1和本身整除的数)
  declare
bol boolean;
begin
for i in 1..100 loop
bol := true;
for j in 2..i-1  loop

if i mod j = 0 then
bol := false;
end if;
end loop;
if bol then
dbms_output.put_line(i);
end if;
end loop;
end;

4.创建从1开始,增量为1的序列,创建一个名为Test(col1 number)的表,
  分别利用loop、while和for循环向Test表插入20条记录
  create sequence sql1
start with 1
increment by 1
maxvalue 100
cache 10;

create table myTable(col1 number);

begin
for i in 1..20 loop
insert into Test values(sql1.nextVal);
end loop;
end;

5.编写PL/SQL显示emp表指定部门的员工详细信息,要求ename的只有第一个字母大写,
  hiredate以‘yyyy-mm-dd’格式显示,如果没有指定的部门则引发自定义异常,
  显示"没有XX部门"
6.采用PL/SQL编写程序求1+2+3+...+100的和，并输出结果
   DECLARE
   a number :=0;
     b number :=0;
   BEGIN
    LOOP
    if a > 99 then
       exit;
    end if;
    a := a + 1;
        b := b + a;
    END LOOP;
      DBMS_OUTPUT.PUT_LINE('1加至100结果为：'||b);
   END;

Rem ==============================================================================
    第四章家庭作业
Rem ==============================================================================
1.PL/SQL有几部分组成，哪些是可选的哪些是必选的
  申明部分      declare      可选
  可执行部分    begin...end  必选
  一场处理部分  exception    可选
2.PL/SQL块中可以写什么类型的语句
DML：数据操作语言
TCL：事物控制语言
游标控制语言
函数  运算符

3.请描述三种循环的特点
  loop 无条件循环
for 明确次数的循环
while 根据某条件的循环

4.PL/SQL属性类型有哪两种,如何定义
%type %rowType
declare
v_name tableName.columnName%type;
v_name tableName%rowType;

Rem ==============================================================================
    第五章上机作业
Rem ==============================================================================
--emp表结构如下
SQL> desc emp;
名称                                      是否为空? 类型
----------------------------------------- -------- -------
EMPNO   (编号)                             NOT NULL NUMBER(4)
ENAME   (姓名)                                   VARCHAR2(10)
JOB     (工作)                                    VARCHAR2(9)
MGR     (部门经理)                                  NUMBER(4)
HIREDATE(雇佣日期)                                      DATE
SAL     (工资)                                    NUMBER(7,2)
COMM    (佣金)                                    NUMBER(7,2)
DEPTNO  (部门编号)                                  NUMBER(2)

--dept表结构如下
SQL> desc dept;
名称                                      是否为空? 类型
----------------------------------------- -------- ----------------------------
DEPTNO  (编号)                                  NOT NULL NUMBER(2)
DNAME   (部门名称)                                    VARCHAR2(14)
LOC     (地址)                                        VARCHAR2(13)
1.编写PL/SQL,根据传入的员工编号(empno)显示其部门信息,如果没有指定编号的员工、
  则提示“没有编号为XX的员工”


2.根据输入的工作(job)类别显示emp表所有员工编号、部门名称、工资信息
  declare
       cursor cur_emp(no emp.job%type) is select EMPNO,DNAME,sal from emp inner join dept on dept.deptno = emp.deptno
       where job = no;
       v_emp cur_emp%rowtype;--游标记录类型
       eno emp.empno%type;
       e_dname dept.dname%type;
       e_sal emp.sal%type;
  begin
  open cur_emp('&工作类别');
  loop
    fetch cur_emp into eno,e_dname,e_sal;
    exit when cur_emp%notfound;
    dbms_output.put_line(eno||'  '||e_dname||'  '||e_sal);
end loop;
close cur_emp;
  end;


3.编写PL/SQL,使用游标修改员工的工资(sal):
  如果sal小于2000则加100;
  如果sal小于1000则加300
  declare
      cursor cur_emp is select ename,sal from emp where  for update;
      v_emp cur_emp%rowtype;--游标记录类型
      e_name emp.ename%type;
      e_sal emp.sal%type;
  begin
      open cur_emp();
      loop
        fetch cur_emp into e_name,e_sal;
           exit when cur_emp%notfound;
        if(v_emp.sal<2000) then
             --修改当前行
           update emp set sal=sal+100 where current of cur_emp;
        elsif(v_emp.sal<1000) then
           update emp set sal=sal+300 where current of cur_emp;
        end if;
        dbms_output.put_line(e_name||'  '||e_sal);
   end loop;
   close cur_emp;
   end;

4.编写PL/SQL,使用REF游标显示指定部门员工的详细信息
5.使用PL/SQL执行动态SQL语句方式创建两个表,职员表(工号(empno),姓名(ename),
  工资(sal))和津贴表(工号(empno),津贴(comm))。
  然后用游标完成操作:取出职员表中工资大于1000元的记录,
  将其工资的30%作为津贴插入津贴表中.


Rem ==============================================================================
    第五章家庭作业
Rem ==============================================================================
1.游标的种类和各自的用途?
  隐式游标：执行DML语句中，自动创建隐式游标
  显示游标：用户处理返回多行的查询时
  ref游标：用于运行时才确定的动态sql查找的结果

2.游标属性有哪些并详细说明其作用?
  %found:影响行数时为真
  %notfound：没有影响行数时为真
  %rowcount：返回一行或多行的结果
  %isopen：是否打开

3.显示游标操作的步骤有哪些,分别用什么语句实现?
  申明  cursor cur_emp is select * from emp;
  打开  open cur_emp
  提取记录  fetch cur_emp into varl,var2
  关闭  close cur_emp

4.REF游标的特点是什么,强类型REF游标和弱类型REF游标有何区别?
  特点：能在处理请求时执行动态sql


--emp表结构如下
SQL> desc emp;
名称                                      是否为空? 类型
----------------------------------------- -------- -------
EMPNO   (编号)                             NOT NULL NUMBER(4)
ENAME   (姓名)                                   VARCHAR2(10)
JOB     (工作)                                    VARCHAR2(9)
MGR     (部门经理)                                  NUMBER(4)
HIREDATE(雇佣日期)                                      DATE
SAL     (工资)                                    NUMBER(7,2)
COMM    (佣金)                                    NUMBER(7,2)
DEPTNO  (部门编号)                                  NUMBER(2)

--dept表结构如下
SQL> desc dept;
名称                                      是否为空? 类型
----------------------------------------- -------- ----------------------------
DEPTNO  (编号)                                  NOT NULL NUMBER(2)
DNAME   (部门名称)                                    VARCHAR2(14)
LOC     (地址)                                        VARCHAR2(13)
1.编写PL/SQL函数GetSalary(),根据传入的empno获得员工的总工资,
  总工资=(工资)sal+comm(补贴),如果comm没有则固定加100


  create or replace function GetSalary(eno emp.EMPNO%type)
    return emp.SAL%type
  is
      gongzi emp.SAL%type;
  begin
         select (SAL+decode(comm,'',100,comm)) into gongzi from emp where EMPNO=eno;
      return gongzi;
  end;

  select GetSalary(7788) from dual;

2.编写过程UpdateSalary过程：循环将员工的sal增加10%,直到所有员工的平均工资达到3000,
  且最高工资不能超过6000。

   LOOP
    if a > 99 then
       exit;
    end if;
    a := a + 1;
        b := b + a;
=================================
  create or replace procedure PL
  as
     avg_sal emp.sal%type;
     max_sal emp.sal%type;
  BEGIN
     loop
         select max(sal) into max_sal from emp;
         select avg(sal) into avg_sal from emp;
        if(avg_sal > 3000 and max_sal>= 6000) then
           exit;
        else
             update emp set sal=sal+sal*0.1;
        end if;
     end loop;
  end;
  --执行
  exec pl;

3.编写过程,根据传入的部门编号统计该部门的总人数并返回(采用IN,OUT参数)
  create or replace procedure pl2(eno emp.empno%type,a out number)
  as
  begin
     select count(*) into a from emp where deptno=eno;
  end;

  --执行
  declare
   v_name number;
  BEGIN
     pl2(10,v_name);
     dbms_output.put_line(v_name);
  end;


4.程序包规范、包主体及调用练习。包名： emp_pack。
  1.规范中定义包含根据empno返回ename的函数‘findName’和
    根据empno删除记录的过程‘deleteEmp’
    create or replac package body pack1
    is
       function fun(eno number) return varcher2;
       procedure pl2(eno number,pname out varchar2);
    end pack1;

  2.包主体实现具体内容
    create or replac package body pack1
    is
    --GetSalary函数
       or replace function GetSalary(eno emp.EMPNO%type)
    return emp.SAL%type
  is
      gongzi emp.SAL%type;
  begin
         select (SAL+decode(comm,'',100,comm)) into gongzi from emp where EMPNO=eno;
      return gongzi;
  end;
   --pl存储过程
       or replace procedure PL
  as
     avg_sal emp.sal%type;
     max_sal emp.sal%type;
  BEGIN
     loop
         select max(sal) into max_sal from emp;
         select avg(sal) into avg_sal from emp;
        if(avg_sal > 3000 and max_sal>= 6000) then
           exit;
        else
             update emp set sal=sal+sal*0.1;
        end if;
     end loop;
  end;
    end pack1;

  3.分别调用过程和函数进行测试
  select pack,function(7788) from dual;


Rem ==============================================================================
    第六章家庭作业
Rem ==============================================================================
1.过程和函数有什么区别?
  过程作为 PL/SQL语句执行，函数作为表达式的一部分调用
  过程在规格说明中不包含RETURN子句，函数必须在规格说明中包含 RETURN 子句
  过程不返回任何值，函数必须返回单个值
  过程可以包含 RETURN 语句，但是与函数不同，它不能用于返回值
  函数必须包含至少一条 RETURN 语句

2.如何向其他用户授予过程和函数的执行权限
  grant execute on 过程名

3.子程序有哪些优点(查阅PPT)
  模块化 安全性 可维护性 可重用性


clear srceen         --清屏
rollback             --回滚
SET SERVEROUTPUT ON --打开服务端输出开关




oracle数据类型
段类型 中文说明 限制条件 其它说明
CHAR 固定长度字符串 最大长度2000 bytes  
VARCHAR2 可变长度的字符串 最大长度4000 bytes 可做索引的最大长度749
NCHAR 根据字符集而定的固定长度字符串 最大长度2000 bytes  
NVARCHAR2 根据字符集而定的可变长度字符串 最大长度4000 bytes  
DATE 日期（日-月-年） DD-MM-YY（HH-MI-SS） 经过严格测试，无千虫问题
LONG 超长字符串 最大长度2G（231-1） 足够存储大部头著作
RAW 固定长度的二进制数据 最大长度2000 bytes 可存放多媒体图象声音等
LONG RAW 可变长度的二进制数据 最大长度2G 同上
BLOB 二进制数据 最大长度4G  
CLOB 字符数据 最大长度4G  
NCLOB 根据字符集而定的字符数据 最大长度4G  
BFILE 存放在数据库外的二进制数据 最大长度4G  
ROWID 数据表中记录的唯一行号 10 bytes ********.****.****格式，*为0或1
NROWID 二进制数据表中记录的唯一行号 最大长度4000 bytes
NUMBER(P,S) 数字类型 P为整数位，S为小数位
DECIMAL(P,S) 数字类型 P为整数位，S为小数位
INTEGER 整数类型 小的整数
FLOAT 浮点数类型 NUMBER(38)，双精度
REAL 实数类型 NUMBER(63)，精度更高