1、begin -执行部分 return 返回值;End;7. 什么是角色,如何创建一个角色并把角色赋予指定账户?1)角色是具有名称的一组相关权限的组合。 2)创建角色有验证方式和非验证方式两种。例如:-创建public_role和private_role角色 Create role public_role not identified;Create role private_role identified by private; -给public_role角色授予某些系统权限和对象权限,如select,update Grant select,update on scott.emp to publ
2、ic_role; 3)把角色赋予指定账户的sql语句和直接把某一权限赋予指定账户相似。 例如: -把角色public_role授予给用户wangcai Grant public_role to wangcai with admin option;编程题:1. 编写PL程序块循环给scott.emp表中所有职工增加工资,每次增加100元,直到平均工资高于5000或任一职工的工资超过6000元为止,最后显示出给所有员工一共增加了多少工资。DECLARE -声明变量 avg_sal emp.sal%TYPE; max_sal emp.sal%TYPE; v_count number:=0;BEGIN
3、 -循环遍历 loop SELECT AVG(sal) INTO avg_sal FROM emp; SELECT MAX(sal) INTO max_sal FROM emp; -退出条件判断 EXIT WHEN avg_sal5000 OR max_sal6000; UPDATE emp SET sal=sal+100; v_count:=v_count+SQL%ROWCOUNT; END LOOP; -打印结果 dbms_output.put_line(职工工资增加了¥|v_count*100);END;2. 编写一个PL块,定义一个静态游标存储scott.emp表中所有雇员的员empn
4、o、ename和sal,并使用循环打印出所有数据。declare cursor cur_emp is select * from emp; -创建一个游标变量 v_erow emp%rowtype; -存储结果集每行变量名begin open cur_emp; -打开游标 loop -开始遍历游标 fetch cur_emp into v_erow; exit when cur_emp%notfound; -打印员工信息员工编号:|v_erow.empno|员工姓名:|v_erow.ename|员工工资:|v_erow.sal); end loop;end;3. 编写一个存储过程upsal接收
5、一个员工号,如果该员工职位是MANAGER,并且在DALLAS工作,那么就给他薪金加15%;如果该员工职位是CLERK,并且在NEW YORK工作,那么就给他薪金扣除5%;其他情况不作处理。-创建存储过程CREATE OR REPLACE PROCEDURE upsal(v_empno in emp.empno%TYPE)AS v_job emp.job%TYPE; v_loc dept.loc%TYPE; - 查询数据 SELECT job,loc INTO v_job,v_loc FROM emp,dept WHERE empno = v_empno AND emp.deptno=dept
6、.deptno; - 显示数据 IF v_job=MANAGER AND v_loc=DALLAS THEN UPDATE emp SET sal=sal*1.15 WHERE empno=v_empno; ELSIF v_job=CLERKNEW YORK THEN UPDATE emp SET sal=sal*0.95 WHERE empno=v_empno; ELSE NULL; END IF; - 异常处理 EXCEPTION WHEN OTHERS THEN数据没更新!);-调用存储过程DECLARE v_empno emp.empno%TYPE; v_empno:=&请输入职工号;
7、 upsal(v_empno);4. 编写一个存储过程comSalary,分别统计出scott.emp表中所有部门的平均工资,并且只显示出工资超过3500的部门。并编写PL块调用此存储过程。CREATE OR REPLACE PROCEDURE comSalary CURSOR cur IS SELECT deptno,AVG(sal) FROM emp GROUP BY deptno; line cur%ROWTYPE; OPEN cur; LOOP -开始遍历游标 FETCH cur INTO line; EXIT WHEN cur%NOTFOUND; SELECT AVG(sal) IN
8、TO avg_sal FROM emp WHERE deptno=line.deptno; IF avg_sal3500 THEN -判断 dbms_output.put_line(line.deptno); CLOSE cur; -关闭游标BEGIN comSalary;一、填空题1.每个Oracle数据库都由3种类型的文件组成:数据文件、日志文件和 控制文件。2.执行立即关闭的命令是_haregnoracle_。3.改变数据库状态的语句是alterdatabase。4.用于创建表空间的语句是createtablespace,5.修改表空间的语句是altertablespace。6.向用户授
9、权的命令为grantconnect,resourcetocdpfzx。7.创建用户的语句是createuserCDPFZXidentifiedbyCDPFZX。8.修改角色的语句是alterrole。9.在CREATETABLE语句中,定义主键的关键字是_PRIMARYKEY。10.在ALTERTABLE语句中,修改列名的关键字是_ALTER_。11.在SELECT语句中,设置查询条件的关键字是_where_。12.在SELECT语句中,实现模糊查询的功能的关键字是_like_。13.declarations关键字标志着PL/SQL程序中声明段的开始,在声明段中可以声明变量、常量和游标等对象。
10、14.PL/SQL的异常处理代码在异常处理块中实现。三、简答题1、简述用户和角色的关系。Oracle使用角色的方法来限定各种用户的权力,在系统中可以有许多用户,各种用户有各种不同的角色,拥有不同的权力。实践表明与其很明确的赋一组权限给一个角色,不如赋给一个角色,然后把这个角色赋给一组用户。这样就可以实现动态的权限管理,当这组用户的权限必须改变时,只需改变角色的权限就可以了。2、简述表与视图的区别与联系 区别:1、视图是已经编译好的sql语句,是基于SQL语句的结果集的可视化的表。而表不是2、视图没有实际的物理记录。而表有。3、表是内容,视图是窗口4、表只用物理空间而视图不占用物理空间,视图只是
11、逻辑概念的存在,表可以及时四对它进行修改,但视图只能有创建的语句来修改5、表是内模式,视图是外模式6、视图是查看数据表的一种方法,可以查询数据表中某些字段构成的数据,只是一些SQL语句的集合。从安全的角度说,视图可以不给用户接触数据表,从而不知道表结构。7、表属于全局模式中的表,是实表;视图属于局部模式的表,是虚表。8、视图的建立和删除只影响视图本身,不影响对应的基本表。联系:视图(view)是在基本表之上建立的表,它的结构(即所定义的列)和内容(即所有数据行)都来自基本表,它依据基本表存在而存在。一个视图可以对应一个基本表,也可以对应多个基本表。视图是基本表的抽象和在逻辑意义上建立的新关系四
12、、应用题1创建、修改表空间的定义,以及编辑和维护表空间。(1)创建临时表空间tmptbs,文件大小为20M,可以重用,指定其最小区间为16M。CREATETEMPORARYTABLESPACEtmptbsTEMPFILE/oracle/oradata/db/tmptbs.dbfSIZE20Mautoextendonnext50mminsize16mextentmanagementlocal;(2)在临时表空间tmptbs中添加临时文件,tmptbs01.dbf,文件大小为20MB。ALTERADDDATAFILE/oracle/oradata/db/tmptbs01.dbf20M;(3)将表空
13、间tmptbs设置为脱机状态。OFFLINE;(4)将表空间tmptbs设置为只读表空间。READONLY;(5)删除表空间tmptbs,在删除表空间的同时,删除包含的段和数据文件。DROPINCLUDINGCONTENTSANDDATAFILES;2创建和管理用户的基本属性,并对用户授予相应的角色和权限。(1)创建用户名为USERMAN,密码为USERPASS的用户。createUSERMANUSERPASSdefault(2)设置用户USERMAN的密码立即过期。Alterexpired(3)锁定用户USERMAN。lockaccount(4)对于用户USERMAN授予SYSDBA权限。G
14、RANTSYSDBA(5)对用户USERMAN授予表USERS的SELECT、INSERT、UPDATE、DELETE权限。SELECT,INSERT,DELETE,UPDATEONUSERuserman五、选做题编写PL/SQL程序,使用LOOPEXITEND语句计算1100之间所有偶数之和。declarev_countnumber:=1;v_sum0;beginforin1.100loopifmod(v_count,2)0then+v_count;endif;loop;dbms_output.put_line(v_sum);试卷二二、填空题(每空2分,共40分)1、假设已在某远程客户端完成
15、网络服务名配置,服务名为aptech,请写出用户MARTIN(用户口令martinpass)连接到服务器的命令:_Connectmartin/martinpassaptech _;2、SYS用户以管理员身份登录后,要授予用户MARTIN可以对SCOTT用户的EMP表进行查询的权限,请写出授权命令:(假设MARTIN用户已存在)_ Grantselectscott.empmartin _;3、创建表employee的副本,但不包含表中的记录:TABLEemployee_copyAS_ Select*fromemployeewhere1=2_;4、查询itemfile表中itemrate列的信息,
16、要求将数值转换为字符串,并使用当前货币符号作为前缀:SELECT_ To_char _(itemrate,C99999)FROMitemfile;5、查itemfile表中itemdesc、re_level列的信息,要求re_level为NULL时显示为0itemdesc,_ NVL _(re_level,0)6、完成以下PL/SQL块,功能是:显示2到50的25个偶数。BEGINFOR_ even_number _IN_1.25_LOOPDBMS_OUTPUT.PUT_LINE(even_number*2);ENDEND;7、完成以下PL/SQL块,功能是:接受职员编号并检索职员姓名。将职员
17、姓名存储在变量empname中,如果代码引发VALUE_ERROR异常,则向用户显示错误消息。DELCAREempnameemployee.ename%TYPE;enoemployee.empno%TYPE;BEGINeno:=&employee_number;_ SELECTenameINTOWHEREempno=eno_;DBMS_OUTPUT.PUT_LINE(职员姓名:|empname);_ EXCEPTION _WHENVALUE_ERRORTHENDBMS_OUTPUT.PUT_LINE(要存储在变量中的值过大)8、完成以下PL/SQL块,功能是:使用游标,显示所有单价低于250元
18、的玩具的单价。DECLAREmy_toy_pricetoys.toyprice%TYPE;CURSORtoy_curIStoypricetoystoypricesales_rec.asalesDBMS_OUTPUT.PUT_LINE(产品:|sales_rec.pid|需提高销售额ELSE_ sales_rec.tsalessales_rec.asales _已达到销售额销售业绩出色IF;10、完成以下PL/SQL块,功能是:创建一个交换两数的过程。ORREPLACEPROCEDUREswap(p1OUTNUMBER,p2_ INOUT _NUMBER)v_tempNUMBER;p1;p1p2
19、;v_temp;11、完成以下PL/SQL块,功能是:创建一个函数dept_name,其功能是接受职员编号后返回职员所在部门名称。(注:部门名称在dept表中,而职员信息在emp表中,职员所在部门号的列名为deptno)FUNCTIONdept_name(emp_noRETURNVARCHAR2ASdept_noNUMBER(2); resultdept.dname%TYPE; _ SELECTdeptnoempempno=emp_no;_dnameresultdeptdept_no;_ RETURNresult;_EXCEPTIONOTHERSNULL;12、要执行pack_me包中的ord
20、er_proc过程(有一个输入参数),假设参数值为002,可以输入以下命令:EXECUTE_ pack_ma.order_proc(002)_13、完成以下PL/SQL块的功能是:创建一个触发器biu_job_emp,无论用户插入记录,还是修改EMP表的job列,都将用户指定的job列的值转换成大写。TRIGGERbiu_job_emp_BEFOREINSERTUPDATEOFjobemp _FOREACHROW _BEGIN :NEW.job=_ UPPER(:NEW.job)_;试卷三复习题一、填空题:1. OracleEnterpriseManager是一个基于B/S的框架系统。2Ora
21、cle数据库的存储结构分为物理结构和逻辑结构。3在游标或者游标变量打开后还没有进行第一次提取时,found属性为null。在oracle中已commit或rollback作为上一个事务的结束标志及下一个新事物开始的标志。DML表示数据操作语言,主要的DML有SELECT,INSERT,UPDATE,DELETE.6索引的主要目标是提高访问的速度。7在Oracledatabase10g系统中,可以使用DBCA工具和CREATEDATEBASE命令两种方式创建数据库。命令Startupnomount只能启动数据库实例,但不能装载和打开数据库。9在输入URL进入OracleEnterpriceManagerDatabaseControl的时候,必须指出ip地址和端口号10在登录Oracle
copyright@ 2008-2022 冰豆网网站版权所有
经营许可证编号:鄂ICP备2022015515号-1