1、ORACLEORADATAORCLuserdata05.dbf SIZE 50M;(9)ALTER TABLESPACE EXAMPLEORACLEORADATAORCLexample05.dbf SIZE 20M;(10) ALTER DATABASE DATAFILE D:ORACLEORADATAORCLuserdata05.dbf AUTOEXTEND ON NEXT 5M MAXSIZE 100M; (14)ORACLEORADATAORCLredo05a.log,D:ORACLEORADATAORCLredo05b.log)SIZE 5M;(15)ALTER DATABASE AD
2、D LOGFILE MEMBER ORACLEORADATAORCLredo05c.log TO GROUP 5;(16)SHUTDOWN IMMEDIATESTARTUP MOUNT第6章 数据库对象的创建与管理2实训题(2)Create table exer_class(CNO number(2) primary key,CNAME varchar2(20),NUM number(3)Create table exer_student(SNO number(4) primary key,SNAME varchar2(10) unique,SAGE number,SEX char(2),CN
3、O number(2)(3)Alter table exer_student add constraint ck_sage check (sage0 and sagev_avgsal then end if; cursor c_emp is select e.employee_id eid,e.last_name ename, e.department_id edid,m.employee_id mid,m.last_name mname from employees e join employees m on e.manager_id=m.employee_id; v_emp c_emp%r
4、owtype; open c_emp; loop fetch c_emp into v_emp; exit when c_emp%notfound; dbms_output.put_line(v_emp.eid|v_emp.ename| v_emp.edid|v_emp.mid|v_emp.mname); close c_emp; v_emp employees%rowtype; select * into v_emp from employees where last_name=Smith; dbms_output.put_line(v_emp.employee_id| v_emp.firs
5、t_name| v_emp.salary|exception when no_data_found then insert into employees(employee_id,last_name,salary,email,hire_date, job_id,department_id) values(2010,7500,smith, to_date(2000-10-5,yyyy-mm-dd),AD_VP,50); when too_many_rows then for v_emp in(select * from employees where last_name=)loop第10章 PL/
6、SQL程序设计(1)创建一个存储过程,以员工号为参数,输出该员工的工资。create or replace procedure pro_showsal( p_empno employees.employee_id%type)as v_sal employees.salary%type; select salary into v_sal from employees where employee_id=p_empno; dbms_output.put_line(v_sal); when no_data_found then dbms_output.put_line(there is not su
7、ch an employees); pro_showsal(100);(2)创建一个存储过程,以员工号为参数,修改该员工的工资。若该员工属于10号部门,则工资增加140元;若属于20号部门,则工资增加200元;若属于30号部门,则工资增加250元;若属于其他部门,则工资增长300元。create or replace procedure pro_updatesal( v_deptno employees.department_id%type; v_inc number; select department_id into v_deptno from employees case v_deptn
8、o when 10 then v_inc:=140; when 20 then v_inc:=200; when 30 then v_inc:=250; else v_inc:=300; end case; update employees set salary=salary+v_inc(5)创建一个包,包中包含一个函数和一个过程。函数以部门号为参数,返回该部门员工的最高工资;过程以部门号为参数,输出该部门中工资最高的员工名、员工号。create or replace package pkg_emp function func_ret_maxsal(p_deptno number) retur
9、n number; procedure pro_showemp(p_deptno number);create or replace package body pkg_emp function func_ret_maxsal(p_deptno number) return number as v_maxsal number; begin select max(salary) into v_maxsal from employees where department_id=p_deptno; return v_maxsal; end; procedure pro_showemp(p_deptno
10、 number) cursor c_emp is select * from employees where department_id=p_deptno and salary=func_ret_maxsal(p_deptno); v_emp.salary);(6)创建一个包,包中包含一个过程和一个游标。游标返回所有员工的信息;存储过程实现每次输出游标中的5条记录。create or replace package pkg_showemp procedure show_fiveemp;create or replace package body pkg_showemp procedure sh
11、ow_fiveemp if not c_emp%isopen then for i in 1.20 loop if c_emp%notfound then exit; v_emp.first_name); pkg_showemp.show_fiveemp;(7)在employees表上创建一个触发器,保证每天8:0017:00之外的时间禁止对该表进行DML操作。create or replace trigger trg_empbefore insert or update or delete on employeesif to_char(sysdate,HH24:MI)not between
12、08:00 and 17: thenraise_application_error(-20000,此时间内,不允许修改EMPLOYEES表end if;(8)在employees表上创建一个触发器,当插入、删除或修改员工信息时,统计各个部门的人数及平均工资,并输出。create or replace trigger trg_8after insert or update or deleteon employees cursor c_dept is select department_id,avg(salary) avgsal,count(*) num from employees group
13、by department_id; for v in c_dept loop dbms_output.put_line(v.department_id| v.avgsal|v.num);第13章 安全管理 CREATE USER usera_exer IDENTIFIED BY usera DEFAULT TABLESPACE USERS QUOTA 10M ON USERS ACCOUNT LOCK;CREATE USER userb_exer IDENTIFIED BY userb;GRANT CREATE SESSION TO usera_exer WITH ADMIN OPTION;G
14、RANT SELECT ,UPDATE ON ehr.employees TO usera_exer WITH GRANT OPTION;ALTER USER usera_exer ACCOUNT UNLOCK;CONNECT usera_erer/useraSELECT * FROM ehr.employees;UPDATE ehr.employees SET salary=salary+100 ;GRANT SELECT ,UPDATE ON ehr.employees TO userb_exer;REVOKE CREATE SESSION FROM usera_exer;GRANT CR
15、EATE SESSION TO usera_exer;REVOKE SELECT,UPDATE ON ehr.employees FROM usera_exer;GRANT SELECT ,UPDATE ON ehr.employees TO usera_exer;CREATE ROLE rolea;CREATE ROLE roleb;GRANT CREATE TABLE TO rolea;GRANT INSERT,UPDATE ON ehr.employees TO rolea;GRANT CONNECT ,RESOURCE TO roleb;GRANT rolea,roleb TO use
16、ra_exer;CREATE PROFILE pwdfileLIMIT CONNECT_TIME 30 IDLE_TIME 10 FAILED_LOGIN_ATTEMPTS 4 PASSWORD_LIFE_TIME 20 PASSWORD_LOCK_TIME 10ALTER USER usera_exer PROFILE pwdfile;第14章 备份与恢复(1)对human_resource数据库进行冷备份。(2)对human_resource数据库进行一次完全的热备份。(3)备份human_resource数据库的控制文件。(4)假定human_resource数据库丢失了数据文件user
17、s01.dbf,使用数据库热备份对数据库进行恢复,并验证恢复是否成功。(8)使用expdp命令导出human_resource数据库的ehr模式下的所有数据库对象。第15章 备份与恢复(4)假设2013-3-12日在数据库中执行了下列操作。略略略(课本可看)(P254)(5)利用闪回查询,查询15:40:10时exercise中的数据(6)利用闪回版本查询,查询15:35:1015:42:10之间sno=100的记录版本信息(7)利用闪回表技术,将exercise表恢复到删除操作进行之前的状态(8)执行“DROP TABLE exercise”语句 然后利用闪回删除技术恢复exercise表(
18、9)将数据库中的闪回日志保留时间设置为3天(4320分钟)(10)利用闪回数据库技术,将数据库恢复到创造表之前的状态set time oncreate table flash_table( id NUMBER PRIMARY KEY, name CHAR(20) );insert into flash_table values(100,jackcommit;insert into flash_table values(200,kinginsert into flash_table values(300,johnselect * from flash_table;select current_scn from v$database;update flash_table set name=wang where id=100;delete from flash_table where id=300;alter table flash_table ENABLE ROW MOVEMENT;flashback
copyright@ 2008-2022 冰豆网网站版权所有
经营许可证编号:鄂ICP备2022015515号-1