摘录的一些知识点

ORACLE自学教程

--create tabletestone

( id number, --序号username varchar2(50),password varchar2(50),sjvarchar2(20));

--导入命令

load data infile'd:\whx\testoracle.txt' append into table testone fields terminated byX'09'(id,username,password,sj);

--oracle日志文件

Oracle日志文件管理与查看

　　--1.查询系统使用的是哪一组日志文件：

　　select * from v$log;

　　--2.查询正在使用的组所对应的日志文件：

　　select * from v$logfile;

　　--3.强制日志切换：

　　alter system switch logfile;

　　--4.查询历史日志：

　　select * from v$log_history;

　　--5.查询日志的归档模式：

　　select dbid,name,created,log_mode from v$database;

　　--6.查询归档日志的信息：

　　select recid,stamp,thread#,sequence#,name fromv$archived_log;

　　--7.增加与删除日志文件组

　　alter database add logfile group 1('/home1/oracle/oradata/ora8i/log1a.log'),'/home2/oracle/oradata/ora8i/log1b.log')size 100M;

　　alter database drop logfile group 1;

　　--8.增加与删除日志成员

　　alter database add logfile member'/home1/oracle/oradata/ora8i/log1a.log' to group1,'/home1/oracle/oradata/ora8i/log2a.log' to group 2;

　　alter database drop logfile member'/home1/oracle/oradata/ora8i/log1a.log' ;

　　--9.日志文件移动

　　alter database rename file'/home1/oracle/oradata/ora8i/log1a.log' to'/home2/oracle/oradata/ora8i/log1a.log';

　　--执行该命令之前必须保证该日志文件物理上已经移动到新目录

　　--10.清除日志文件

　　alter database clear logfile'/home1/oracle/oradata/ora8i/log1a.log';

　　--该命令用于不能用删除组及组成员命令删除日志时使用

--文本内容导入数据库

---将文本文件/(excel表中)的内容导入到oracle数据库中---可以利用PL/SQL developer

--首先查看oracle数据库的编码SQL> select * fromnls_database_parameters where parameter ='NLS_CHARACTERSET';

PARAMETERVALUE------------------------------ --------------------------------------------------------------------------------NLS_CHARACTERSETZHS16GBK

--这其来源于props$，这是表示数据库的字符集。--oracle客户端编码SQL> select * from nls_instance_parameters whereparameter='NLS_LANGUAGE';

PARAMETERVALUE--------------------------------------------------------------------------------------------------------------------------------------------NLS_LANGUAGEAMERICAN

--其来源于v$parameter，表示客户端的字符集的设置，可能是参数文件，环境变量或者是注册表会话字符集环境SQL>select *

from nls_session_parameters;--其来源于v$nls_parameters，表示会话自己的设置，可能是会话的环境变量或者是alter session完成，如果会话没有特殊的设置，将与nls_instance_parameters一致。--再来说一下怎么修改oracle的字符集：--目前我的数据库环境的字符集是AL32UTF8，那么把它改成ZHS16GBK --1.首先以sysdba的身份登录上去 conn /as sysdba SQL>conn sys/ych as

sysdba; --2.关闭数据库shutdown immediate; --3.以mount打来数据库，startup mount --4.设置session SQL>ALTER SYSTEM ENABLE RESTRICTED

SESSION;SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;--PL/SQL Developer与Oracle11g远程连接问题http://zhumeng8337797.blog.163.com/blog/static/10076891420111115104023136/

--Oracle11g编码问题

SQL> ALTER

SYSTEM SET AQ_TM_PROCESSES=0; --5.启动数据库SQL>alter

database open; --6.修改字符集SQL>ALTER DATABASE CHARACTER SET

ZHS16GBK;--这会可能会报错，提示我们的字符集：新字符集必须为旧字符集的超集，这时我们可以跳过超集的检查做更改：SQL>ALTER

DATABASE character set INTERNAL_USE ZHS16GBK;--这条语句就可以了，TERNAL_USE提供的帮助就会使oracle绕过了子集与超集的验证，这条语句和上面的语句内部操作时完全相同的。 --7.关闭，重新启动SQL>shutdown immediate;SQL>startup----------------------------------------------------------------------------------------------------/==============================================================//*DBMS name: MySQL 4.0 *//* Created on: 2007-6-28 15:25:46 *//==============================================================/

drop table ifexists admin;

drop table ifexists answer;

drop table ifexists subitem;

drop table ifexists item;

drop table ifexists question;

drop table ifexists user;

/==============================================================//*Table: admin *//==============================================================/

SQL>createsequence s_admin increment by 1;SQL>create table admin( id INT PRIMARY KEY ,adminid VARCHAR2(50) not null, adminpwd VARCHAR2(50)) ;

/==============================================================//*Table: question *//==============================================================/SQL>createsequence s_question increment by 1;SQL>create table question( qid int notnull, title VARCHAR2(50), content clob, itemid int, subid int, useridVARCHAR2(50), grade VARCHAR2(50), offerscore int, status int, questiontimedate, clickcount int, acceptflag int, commenflag int, primary key (qid)) ;

/==============================================================//*Table: answer *//==============================================================/SQL>createtable answer( aid int not null, quesans VARCHAR2(50), userid VARCHAR2(50),grade VARCHAR2(50), anstime date, status int, qid int, primary key (aid) ,foreign key (qid) references question(qid) on delete cascade ) ;

/==============================================================//*Table: item *//==============================================================/createtable item( itemid int not null, itemname VARCHAR2(50), itemcode int, primarykey (itemid)) ;

/==============================================================//*Table: subitem *//==============================================================/createtable subitem( subid int not null, subname varchar2(50), itemid int, subcodeint, primary key (subid) , foreign key (itemid) references item(itemid) ondelete cascade) ;

/==============================================================//*Table: user *//==============================================================/createtable table_user( id INT PRIMARY KEY , userid VARCHAR2(50) not null , userpwdVARCHAR2(50), userques VARCHAR2(50), userans VARCHAR2(50), usermailVARCHAR2(50), integral int, grade int, sex VARCHAR2(2), realname VARCHAR2(50));

----------------------------------------------------------------------------------------------------

SQL>setserveroutput on;SQL> create table t_student (id number(10,0) not null, namevarchar2(255) not null, age number(10,0), sex varchar2(255), good char(1),primary key (id, name));SQL> create table t_teacher (id number(10,0) notnull, birthDate date, gender varchar2(255), good number(1,0) not null, namevarchar2(255), title varchar2(255), primary key (id));SQL> delete fromteacher where id=2;SQL> update use setemail='xiaowang@163.com'whereusername='xiaowang';SQL> drop table userba2;SQL> create tablet1_student(id varchar2(255),name varchar2(255),age varchar2(255),good char(1));SQL> create table userba2(id number,username varchar2(20),passwordvarchar2(20));SQL> drop table t_group;sql> drop table t_user;

--1. 查询Oracle中所有用户信息SQL> select*from dba_users;

--2. 只查询用户和密码sql>select username,password from dba_users;

--3. 查询当前用户信息select*from dba_ustats;

--通过pl/sql中的“浏览器”也可以查看user信息

--查看当前用户的缺省表空间

　　SQL>select username,default_tablespace fromuser_users;

---查看当前用户的系统权限和表级权限

　　SQL>select * from user_sys_privs;

　　SQL>select * from user_tab_privs;

---查看用户下所有的表

　　SQL>select * from user_tables;

--1、用户

----查看当前用户的缺省表空间

　　SQL>select username,default_tablespace fromuser_users;

----查看当前用户的角色

　　SQL>select * from user_role_privs;

----查看当前用户的系统权限和表级权限

　　SQL>select * from user_sys_privs;

　　SQL>select * from user_tab_privs;

----显示当前会话所具有的权限

　　SQL>select * from session_privs;

----显示指定用户所具有的系统权限

　　SQL>select * from dba_sys_privs where grantee='GAME';

--2、表

----查看用户下所有的表

　　SQL>select * from user_tables;

----查看名称包含log字符的表

　　SQL>select object_name,object_id from user_objects

　　where instr(object_name,'LOG')>0;

----查看某表的创建时间

　　SQL>select object_name,created from user_objects whereobject_name=upper('&table_name');

----查看某表的大小

　　SQL>select sum(bytes)/(1024*1024) as"size(M)" from user_segments wheresegment_name=upper('&table_name');

----查看放在ORACLE的内存区里的表

　　SQL>select table_name,cache from user_tables whereinstr(cache,'Y')>0;

--3、索引

----查看索引个数和类别

　　SQL>select index_name,index_type,table_name fromuser_indexes order by table_name;

----查看索引被索引的字段

　　SQL>select * from user_ind_columns whereindex_name=upper('&index_name');

----查看索引的大小

　　SQL>select sum(bytes)/(1024*1024) as"size(M)" from user_segments

　　where segment_name=upper('&index_name');

--4、序列号

　　查看序列号，last_number是当前值

　　SQL>select * from user_sequences;

　　5、视图

----查看视图的名称

　　SQL>select view_name from user_views;

----查看创建视图的select语句

　　SQL>set view_name,text_length from user_views;

　　SQL>set long 2000; 说明：可以根据视图的text_length值设定set long 的大小

　　SQL>select text from user_views whereview_name=upper('&view_name');

--6、同义词

----查看同义词的名称

　　SQL>select * from user_synonyms;

--7、约束条件

----查看某表的约束条件

　　SQL>select constraint_name,constraint_type,search_condition, r_constraint_name

　　from user_constraints where table_name =upper('&table_name');

　　SQL>selectc.constraint_name,c.constraint_type,cc.column_name

　　from user_constraints c,user_cons_columns cc

　　where c.owner = upper('&table_owner') andc.table_name = upper('&table_name')

　　and c.owner = cc.owner and c.constraint_name =cc.constraint_name

　　order by cc.position;

--8、存储函数和过程

----查看函数和过程以及包的状态

　　SQL>select object_name,status from user_objects where

object_type='FUNCTION';　　SQL>select object_name,status fromuser_objects where object_type='PROCEDURE'; SQL>select object_name,statusfrom user_objects where object_type='PACKAGE';

----查看函数和过程以及包的源代码

　　SQL>select text from all_source where owner=user andname=upper('&plsql_name');

---ORACLE中数据字典视图分为3大类,用前缀区别，分别为：USER，ALL 和 DBA，许多数据字典视图包含相似的信息。--USER_*:有关用户所拥有的对象信息，即用户自己创建的对象信息--ALL_*：有关用户可以访问的对象的信息，即用户自己创建的对象的信息加上其他用户创建的对象但该用户有权访问的信息--DBA_*：有关整个数据库中对象的信息--（这里的*可以为TABLES， INDEXES， OBJECTS， USERS等。

--1.查看所有用户：SQL>select * from dba_user;SQL>select * from

all_users;SQL>select * from user_users;2.查看用户系统权限：select * from

dba_sys_privs;select * from all_sys_privs;select * from user_sys_privs;3.查看用户对象权限：select * from dba_tab_privs;select * from

all_tab_privs;select * from user_tab_privs;4.查看所有角色：select * from

dba_roles;5.查看用户所拥有的角色：select * from dba_role_privs;select *from user_role_privs;

6.查看当前用户的缺省表空间select username,default_tablespace fromuser_users;

7.查看某个角色的具体权限，如grant connect,resource,create

session,create view to TEST;查看RESOURCE具有那些权限，用SELECT * FROMDBA_SYS_PRIVS WHERE GRANTEE='RESOURCE';

例：限制user1用户只允许100个并发连接SQL> altersystem set resource_limit=true;

System altered

SQL> createprofile profile_user1 limit sessions_per_user 100;

Profile created

SQL> ALTERUSER user1 profile profile_user1;

User altered

------------------------------------------------------------------------------------------案例create or replace procedure sp_pro2 is begin--执行部分delete from mytest where name='王红祥';end;

--最简单的快

---------分页算法--1.0SQL>select * from (select rownum rn,t.* from empt) where rn>&minnum and rn<&maxmun;

--1.1SQL>select* from (select rownum rn,t.* from emp t rownum<=&maxnum) wherern>&minnum;

---看似相似的分页语句，在响应速度上其实有很大的差别。来看一个测试过程，首先穿件一个测试表。SQL>createtable test as select * from emp;

----反复插入数据SQL>insert into test select * from test;

---first_rows对分页的影响----创建一个测试表，并作关联查询分页SQL>createtable page_test as select rownum id,t.* from test t;

----1.先分析该表SQL>analyze table page_test compute statistics fortable for all columns;

----2.用普通分页SQL>select * from(select rownum rn,a.object_name from

page_test a,page_test b,page_test c where a.id=b.id and b.id=c.id and

rownum<=5) where

rn>0;-----------------------------------------------------------------------------------------------------------------------------begin

dbms_output.put_line('hello'); end;--有定义和执行部分的块declare--定义变量v_ename varchar2(5);begin --执行部分 select ename into v_ename from emp where empno=&aa;

--在控制台显示用户名 dbms_output.put_line('用户名:'||v_ename); end; --有定义和执行部分的块 --把用户的工资也显示出来declare--定义变量v_ename varchar2(5);v_sal

number(7,2);begin --执行部分 select ename into v_ename from emp

where empno=&aa; select sal into v_sal from emp where emp empno=&aa; --在控制台显示用户名 dbms_output.put_line('用户名:'||v_ename);

end; --有定义和执行部分的块 --把用户的工资也显示出来declare--定义变量v_ename varchar2(5);v_sal number(7,2);begin --执行部分 select ename,sal into v_ename,v_sal from emp where

empno=&aa; --在控制台显示用户名 dbms_output.put_line('用户名:'||v_ename); dbms_output.put_line('薪金:'||v_sal); end; declare--定义变量v_ename

varchar2(5);v_sal number(7,2);begin --执行部分 select

ename,sal into v_ename,v_sal from emp where empno=&aa; --在控制台显示用户名 dbms_output.put_line('用户名:'||v_ename||'薪金:'||v_sal); exception when no_data_found then

dbms_output.put_line('朋友你的编号输入有误，请重新输入'); end; --演示4 create procedure sp_pro4(spName varchar2,neSal number)

is begin --执行部分---根据用户名修改工资 update emp set

sal=newSal where ename spName; end; --函数案例 --输入雇员的名字，返回该雇员的年薪 create function sp_fun2(spName varchar2)

return number is yearSal number(7,2); begin --执行部分select sal12+nvl(comm,0)12into yearSal from emp where ename=spName; return yearSal; end; --(1)我们可以使用create package命令来创建 ----创建包 ---创建一个包sp_package ---声明该抱有一个过程update_sal ---还声明了该包有一个函数annual_incomecreate

package sp_package isprocedure update_sal(name varchar2,newsal number);function

annual_income(name varchar2) return number;end;--(2)建立包体可以使用create package body命令 ---给包sp_packeage实现包体create or

replace package body sp_package isprocedure update_sal(name varchar2,newsal

number) isbeginupdate emp set sal=newsal where ename=name;end;function annual_income(name

varchar2) return number is annual_salary number;beginselect sal*12+nvl(comm,0)

into annual_salary from where ename=name;return annual_salary;end;end; --(3)如何调用包的过程或是函数--当调用包的过程或是函数时，在过程和函数前需要带上包名，如果要访问其它方案的包，还需要在包名前加上方案名.--如:sql>call sp_package.update_sal('SCOTT',1500);

--下面已输入员工号，显示员工姓名、工资、个人所得税(税率为0.03)为例 --案例 declare c_tax_rate number(3,2):=0.03;

--用户名,工资，税收 v_ename

varchar2(5); v_sal number(7,2); v_tax_sal number(7,2); begin --执行部分 select ename,sal into v_ename,v_sal from emp where

empno=&em; ---计算所得税 v_tax_sal:=v_sal*c_tax_rate; --输出 dbms_output.put_line('姓名是:'||v_ename||'工资'||v_sal||'所得税'||v_tax_sal);

end; declare c_tax_rate number(3,2):=0.03; --用户名,工资，税收 v_ename emp.ename%type; v_sal number(7,2); v_tax_sal

number(7,2); begin --执行部分 select ename,sal into v_ename,v_sal

from emp where empno=&em; ---计算所得税v_tax_sal:=v_sal*c_tax_rate; --输出 dbms_output.put_line('姓名是:'||v_ename||'工资'||v_sal||'所得税'||v_tax_sal); end; declare c_tax_rate number(3,2):=0.03;

--用户名,工资，税收 v_ename

emp.ename%type; v_sal emp.sal%type; v_tax_sal number(7,2); begin --执行部分 select ename,sal into v_ename,v_sal from emp where

empno=&em; ---计算所得税 v_tax_sal:=v_sal*c_tax_rate; --输出 dbms_output.put_line('姓名是:'||v_ename||'工资'||v_sal||'所得税'||v_tax_sal);end;

---pl/sql记录实例declare--定义一个pl/sql记录类型emp_record_type,类型包含三个数据name,salary,title;type emp_record_type is record(name

emp.ename%type,salary emp.sal%type,title emp.job%type);--定义了一个sp_record变量，这个变量的类型是emp_record_typesp_record

emp_record_type;begin select ename,sal,job into sp_record from emp where

empno=7788; dbms_output.put_line('员工名'||emp_record.name);

end;--复合类型----pl/sql表的实例declare--定义了一个pl/sql表类型sp_table_type(是一个类型,不是一个变量)，该类型是用于存放emp.ename%type类型的数据--index by binary_integer;表示下表是整数type

sp_table_type is table of emp.ename%type index by binary_integer;--定义了一个sp_table变量,变量的类型是sp_table_type;pl/sql中定义时变量在前，类型在后面sp_table sp_table_type;beginselect ename

into sp_table(0) from emp where empno=778;dbms_output.putline('员工名:'||sp_table(0));end;--出错，下标出现错误declare--定义了一个pl/sql表类型sp_table_type(是一个类型,不是一个变量)，该类型是用于存放emp.ename%type类型的数据--index by binary_integer;表示下表是整数type

into sp_table(-1) from emp where empno=778;dbms_output.putline('员工名:'||sp_table(0));end;--出错，实际返回的行数超出了请求的行数declare--定义了一个pl/sql表类型sp_table_type(是一个类型,不是一个变量)，该类型是用于存放emp.ename%type类型的数据--index by binary_integer;表示下表是整数type

into sp_table(0) from emp;dbms_output.putline('员工名:'||sp_table(0));end;--参照变量-ref cursor游标变量--(1)请使用pl/sql编写一个块,可以输入部门号,并显示该部门所有员工的姓名和他的工资。declare--定义游标类型sp_emp_cursortype

sp_emp_cursor is ref cursor;--定义一个游标变量test_cursor sp_emp_cursor;v_ename

emp.ename%type;v_sal emp.sal%type;begin --执行部分 --把test_cursor和一个select结合 open test_cursor for select ename,sal from emp where

deptno=&no;--循环取出loop fetch test_cursor into

v_ename,v_sal; --判断退出条件，是否test_cursor是否为空 exit when test_cursor%notfound dbns_output.put_line('名字为'||v_ename||'工资'||v_sal); end

loop;end;--(2)在(1)的基础上，如果某个员工的工资低于200元，就增加100元。declare--定义游标类型sp_emp_cursortype

sp_emp_cursor is ref cursor;--定义一个游标变量test_cursor sp_emp_cursor;v_ename

emp.ename%type;v_sal emp.sal%type;begin --执行部分 --把test_cursor和一个select结合 open test_cursor for select ename,sal from emp where

deptno=&no;--循环取出loop fetch test_cursor into

v_ename,v_sal; --判断工资高低，决定是否更新 if v_sal<200

then v_sal=v_sal+100 where deptno=no; --判断退出条件，是否test_cursor是否为空 exit when test_cursor%notfound dbns_output.put_line('名字为'||v_ename||'工资'||v_sal); endloop;end;

--编写一个过程，可以输入一个雇员名，如果该雇员的工资低于2000，就给该雇员工资增加10%

create or

replace procedure sp_pro6(spName varchar2) is --定义v_sal

emp.sal%type;begin --执行 select sal into v_sal from emp where

ename=spName; --判断 if v_sal<2000 then update emp set

sal=sal+sal*0.1 where ename=spName; end if; end; --编写一个过程，可以输入一个雇员名，如果该雇员的补助不是0，就给该雇员补助在原来的基础上增加100;如果补助为0就把补助设置为200;create or replace procedure

sp_pro7(spName varchar2) is --定义v_comm emp.comm%type;begin --执行 select comm into v_comm from emp where ename=spName; --判断 if v_sal<>0 then update emp set comm=comm+100

where ename=spName; else update emp set comm=comm+200 where ename=spName; end

if; end; --编写一个过程，可以输入一个雇员编号，如果该雇员的职位是PRESIDENT，就给该他工资增加1000;如果该雇员是MANAGER,就给他的工资增加500;其他职位的雇员工资增加200;create or replace procedure sp_pro8(spNO number) is

--定义v_job emp.job%type;begin --执行部分select job into v_job from emp where empno=spNO; if v_job='PERSIDENT'then update emp set sal=sal+1000 where empno=spNO; elsif v_job='MANAGER' thenupdate emp set sal=sal+500 where empno=spNO; else update emp set sal=sal+100where empno=spNO; end if;end;

sql>exec

sp_pro8(7983)---循环语句loop,新建一张表create table

usersq(num1 number，Name1 varchar2(40));--请编写一个过程，可输入用户名，并循环添加10个用户到users表中，用户编号从1开始增加。create or replace procedure

sp_prol(spName varchar2) is--定义:=表示赋值v_num

number:=1;begin loop insert into usersq values(v_num,spName); --判断是否要退出循环 exit when v_num=10; --自增 v_num:=v_num+1;

end loop; end;sql>exec sp_pro1('你好');sql>select

* from usersq;---请编写一个过程，可输入用户名，并循环添加10个用户到users表中，用户编号从1开始增加。create or

replace procedure sp_pro2(spName varchar2) is--定义:=表示赋值v_num number:=11;beginwhile v_num<=20 loop --执行部分 insert into usersq values(v_num,spName); --自增 v_num:=v_num+1; end loop; end;--for循环create or replace procedure sp_pro3(spName varchar2) is--定义:=表示赋值v_num number:=11;begin for i in reverse1..10 loopinsert into usersq values(i,spName);end loop;end;

sql>exec

sp_pro3('小王');

goto table,其中table是已经定义好的标号名declarei

int:=1;begin loop dbms_output.put_line('输出i='||i); if i=10

then goto end_loop; end if; i:=i+1; end loop; <>

dbms_output.put_line('循环结束'); end;

sql>setserveroutput on;

declarei

int:=1;begin loop dbms_output.put_line('输出i='||i); if i=10

then goto end_loop; end if; i:=i+1; end loop; dbms_output.put_line('循环结束'); <> end;---新建表---book表create table book (bookIdnumber,bookName varchar2(50),publishHouse varchar2(50));

---编写过程---spBookId (in) number,--in代表往存储储过程输入，默认为in--out代表一个输出参数create orreplace procedure sp_pro4(spBookId in number,spbookName invarchar2,sppulishHouse varchar2) isbegin insert into bookvalues(spBookId,spbookName,sppulishHouse); end;

public classTESTfenye;

--案例:编写一个过程，可以输入雇员的编号，返回该雇员的姓名create or

replace procedure sp_pro8(spno in number,spName out varchar2) isbegin select

ename into spName from emp where empno=spno; end;--案例扩展:编写一个过程，可以输入雇员的编号，返回该雇员的姓名、工资、和岗位create orreplace procedure sp_pro9(spno in number,spName out varchar2,spSal out number,spJobout varchar2) isbegin select ename,sal,job into spName,spSal,spJob from empwhere empno=spno; end;

---3.返回结果集的过程--1.创建包,在该包中定义了一个test_cursor类型create or replace package testpackage as type test_cursor

is ref cursor; end testpackage;--2.创建存储过程create or

replace procedure sp_pro10(spNo in number,p_cursor out testpackage.test_cursor)

isbegin open p_cursor for select * from emp where deptno=spNo; end; --3.如何在JAVA中调用该存储过程

--oracle分页--可以把下面sql语句当做一个模板使用selectt1.*,rownum rn from (select * from emp) t1;

selectt1.*,rownum rn from (select * from emp) t1 where rownum<=10;

select * from(select t1.*,rownum rn from (select * from emp) t1 where rownum<=10) wherern>=6;

--开发一个包--使用上面的包create or replace package testpackage as

type test_cursor is ref cursor; end testpackage;--开始编写分页的过程create or replace procedure fenye(tablename in

varchar2,Psize in number,--3、4、5、一页显示记录PNow in number,--1、2、3myrows out number,--总记录数myPageCount out

number,--总页数p_cursor out testpackage.test_cursor---返回的记录) is--定义部分--定义sql语句字符串v_sql varchar2(1000);--v_begin

number:=(PNow-1)*psize+1;v_end number:=PNow*psize;begin--执行部分v_sql:='select * from (select t1.*,rownum rn from (select

* from '||tablename ||') t1 where rownum<='||v_end||') where

rn>='||v_begin;--把游标和sql语句关联open p_cursor

for v_sql;--关闭游标--计算myrows和myPageCount--组织了一个sql语句v_sql:='select count(*) from'||tablename;--执行sql语句，并把返回值赋值给myrows;execute

immediate v_sql into myrows;--计算myPageCountif mod(myrows,psize)=0 then

myPageCount:=myrows/psize;else myPageCount:=myrows/psize+1;end if;--关闭游标close p_cursor;end;--使用java测试

---问题---排序需求create or replace procedure

fenye(tablename in varchar2,Psize in number,--3、4、5、一页显示记录PNow in number,--1、2、3myrows out number,--总记录数myPageCount out number,--总页数p_cursor out

testpackage.test_cursor---返回的记录) is--定义部分--定义sql语句字符串v_sql varchar2(1000);--v_begin

number:=(PNow-1)*psize+1;v_end number:=PNow*psize;begin--执行部分v_sql:='select * from (select t1.*,rownum rn from (select

* from '||tablename ||'order by sal) t1 where rownum<='||v_end||') where

rn>='||v_begin;--把游标和sql语句关联open p_cursor

for v_sql;--关闭游标--计算myrows和myPageCount--组织了一个sql语句v_sql:='select count(*) from'||tablename;--执行sql语句，并把返回值赋值给myrows;execute

immediate v_sql into myrows;--计算myPageCountif mod(myrows,psize)=0 then

myPageCount:=myrows/psize;else myPageCount:=myrows/psize+1;end if;--关闭游标close p_cursor;end;

---案例:编写一个过程，可接受雇员的编号，并显示该雇员的名字--问题是，如果输入的雇员的编号不存在，怎样去处理。declarev_ename emp.ename%type;begin--定义select ename into v_ename from emp where empno=&no;dbms_output.put_line('姓名为:'||v_ename);end;

declarev_ename

emp.ename%type;--定义beginselect ename into v_ename from emp

where empno=&no;dbms_output.put_line('姓名为:'||v_ename);exception

when no_data_found then dbms_output.put_line('不存在');end;

---自定义例外create or replace procedure ex_test(spNo number)is begin

--更新用户sal update emp set sal=sal+1000 whereempno=spNo; end;

--如何自定义例外create or replace procedure ex_test(spNo number)is --定义一个例外myex exception;begin --更新用户sal update emp

set sal=sal+1000 where empno=spNo; --sql%notfound这里表示没有uodate成功 --raise myex触发例外 if sql%notfound

then raise myex; end if; exception when myex then dbms_output.put_line('没有任何用户更新'); end;

---创建视图，把emp表sal<1000的雇员映射到该视图(view)--视图创建以后，可以将视图当成一张普通的表create viewmyview as select * from emp where sal<1000;

--为了简化操作，用一个视图解决，显示雇员编号，姓名和部门名称create view myview2 as selectemp.ename,emp.sal,dept.deptno from emp,dept where emp.deptno=dept.deptno;select* from myview2;

--视图和视图之间可以进行联合查询-------------------------------student数据库------------------------------------------

SQL> create

table course( course_no number(8,0) not null,description varchar2(50),cost

number(9,2),prerequisite number(8,0),create_by varchar2(30) not

null,create_date date,modify_by varchar2(30) not null,modified_date

date);SQL>insert into course values(6300039,'课程设计好',123.52,00000001,'scott',sysdate,'scott',sysdate);SQL>insert

into course values(6300040,'课程设计好',121.32,00000002,'scott',sysdate,'scott',sysdate);SQL>insert

into course values(6300041,'课程设计好',173.52,00000003,'scott',sysdate,'scott',sysdate);SQL>insert

into course values(6300042,'课程设计好',123.52,00000004,'scott',sysdate,'scott',sysdate);SQL>insert

into course values(6300043,'课程设计好',123.52,00000005,'scott',sysdate,'scott',sysdate);SQL>insert

into course values(6300044,'课程设计好',121.32,00000006,'scott',sysdate,'scott',sysdate);SQL>insert

into course values(6300045,'课程设计好',173.52,00000007,'scott',sysdate,'scott',sysdate);SQL>insert

into course values(6300046,'课程设计好',123.52,00000008,'scott',sysdate,'scott',sysdate);SQL>insert

into course values(6300047,'课程设计好',123.52,00000009,'scott',sysdate,'scott',sysdate);SQL>insert

into course values(6300048,'课程设计好',121.32,00000010,'scott',sysdate,'scott',sysdate);SQL>insert

into course values(6300049,'课程设计好',173.52,00000011,'scott',sysdate,'scott',sysdate);SQL>insert

into course values(6300050,'课程设计好',123.52,00000012,'scott',sysdate,'scott',sysdate);SQL>insert

into course values(6300051,'课程设计好',123.52,00000013,'scott',sysdate,'scott',sysdate);SQL>insert

into course values(6300052,'课程设计好',121.32,00000014,'scott',sysdate,'scott',sysdate);SQL>insert

into course values(6300053,'课程设计好',173.52,00000015,'scott',sysdate,'scott',sysdate);SQL>insert

into course values(6300054,'课程设计好',123.52,00000016,'scott',sysdate,'scott',sysdate);

--------------创建一个过程create or replace procedure sp_pro01 isbegininsert into

course values(6300039,'西欧字幕越',123.54,00000001,user,sysdate,user,sysdate);

insert into course values(6300040,'课程设计好',121.32,00000002,'scott',sysdate,'scott',sysdate);insert

into course values(6300041,'课程设计好',173.52,00000003,'scott',sysdate,'scott',sysdate);insert

into course values(6300042,'课程设计好',123.52,00000004,'scott',sysdate,'scott',sysdate);insert

into course values(6300043,'课程设计好',123.52,00000005,'scott',sysdate,'scott',sysdate);insert

into course values(6300044,'课程设计好',121.32,00000006,'scott',sysdate,'scott',sysdate);insert

into course values(6300045,'课程设计好',173.52,00000007,'scott',sysdate,'scott',sysdate);insert

into course values(6300046,'课程设计好',123.52,00000008,'scott',sysdate,'scott',sysdate);insert

into course values(6300047,'课程设计好',123.52,00000009,'scott',sysdate,'scott',sysdate);insert

into course values(6300048,'课程设计好',121.32,00000010,'scott',sysdate,'scott',sysdate);insert

into course values(6300049,'课程设计好',173.52,00000011,'scott',sysdate,'scott',sysdate);insert

into course values(6300050,'课程设计好',123.52,00000012,'scott',sysdate,'scott',sysdate);insert

into course values(6300051,'课程设计好',123.52,00000013,'scott',sysdate,'scott',sysdate);insert

into course values(6300052,'课程设计好',121.32,00000014,'scott',sysdate,'scott',sysdate);insert

into course values(6300053,'课程设计好',173.52,00000015,'scott',sysdate,'scott',sysdate);insert

into course values(6300054,'课程设计好',123.52,00000016,'scott',sysdate,'scott',sysdate);insert

into course values(6300055,'课程设计好',123.52,00000017,'scott',sysdate,'scott',sysdate);end;

--------------------执行该过程SQL>exec sp_pro01;

-----------------------------------------------------------------------------------------

SQL>create

table section( section_id number(8,0) primary key not null, course_no

number(8,0) not null, section_no number(3) not null, start_date_time date,

locate varchar2(50), instructor_id number(8,0) references

instructor(instructor_id) not null, capacity number(3,0), create_by

varchar2(30) not null, created_date date not null, modified_by varchar2(30) not

null, modified_date date not null);--------------创建一个过程create or

replace procedure sp_pro02 isbegin insert into section

values(191,6300040,1,sysdate,'北京',150,100,'scott',sysdate,'scott',sysdate);

insert into section values(192,6300041,2,sysdate,'北京',190,100,'scott',sysdate,'scott',sysdate); insert into

section values(193,6300042,3,sysdate,'天津',100,100,'scott',sysdate,'scott',sysdate);

insert into section values(194,6300043,4,sysdate,'上海',170,100,'scott',sysdate,'scott',sysdate); insert into

section values(195,6300044,5,sysdate,'广州',130,100,'scott',sysdate,'scott',sysdate);

insert into section values(196,6300045,6,sysdate,'武汉',160,100,'scott',sysdate,'scott',sysdate); insert into

section values(197,6300046,7,sysdate,'成都',110,100,'scott',sysdate,'scott',sysdate);

insert into section values(198,6300047,8,sysdate,'重庆',134,100,'scott',sysdate,'scott',sysdate);end;

--------------------执行该过程SQL>exec sp_pro02;

-----------------------------------------------------------------------------------------SQL>createtable stud( student_id number(8,0) primary key, salutation varchar2(5),first_name varchar2(25), last_Name varchar2(25) not null, street_addressvarchar2(50) not null, zip varchar2(10) not null, phone char(11),employervarchar2(50),registration_date date not null,create_by varchar2(30) notnull,created_date date not null,modified_by varchar2(30) not null,modified_datedate not null);

--------------创建一个过程create or replace procedure sp_pro03 isbegin insert into

stud values(80605301,'朱','浩桥','朱','湖北','100001','02887720301','西华大学',sysdate,'scott',sysdate,'sys',sysdate); insert into

stud values(80605302,'李','龙','李','吉林','100002','02887720302','西华大学',sysdate,'scott',sysdate,'system',sysdate); insert into

stud values(80605303,'祝','智发','祝','四川','100003','02887720303','西华大学',sysdate,'scott',sysdate,'sys',sysdate); insert into

stud values(80605304,'李','玥','李','重庆','100004','02887720304','西华大学',sysdate,'scott',sysdate,'system',sysdate); insert into

stud values(80605305,'宋','雪剿','宋','浙江','100005','02887720305','西华大学',sysdate,'scott',sysdate,'sys',sysdate); insert into

stud values(80605310,'杜','忆东','杜','四川','100003','02887720306','西华大学',sysdate,'scott',sysdate,'system',sysdate); insert into

stud values(80605311,'张','航','张','四川','100003','02887720307','西华大学',sysdate,'scott',sysdate,'scott',sysdate); insert into

stud values(80605312,'莫','逸杨','莫','四川','100003','02887720308','西华大学',sysdate,'scott',sysdate,'system',sysdate); insert into

stud values(80605314,'戴','子仪','戴','湖北','100001','02887720309','西华大学',sysdate,'scott',sysdate,'scott',sysdate); insert into

stud values(80605315,'杨','勇','杨','吉林','100002','02887720310','西华大学',sysdate,'scott',sysdate,'system',sysdate); insert into

stud values(80605316,'郭','荣华','郭','四川','100003','02887720313','西华大学',sysdate,'scott',sysdate,'sys',sysdate); insert into

stud values(80605317,'杨','银辉','杨','重庆','100004','02887720314','西华大学',sysdate,'scott',sysdate,'system',sysdate); insert into

stud values(80605318,'李','昆霖','李','浙江','100005','02887720315','西华大学',sysdate,'scott',sysdate,'scott',sysdate); insert into

stud values(80605319,'赵','虎','赵','四川','100003','02887720316','西华大学',sysdate,'scott',sysdate,'system',sysdate); insert into

stud values(80605320,'黄','友志','黄','四川','100003','02887720317','西华大学',sysdate,'scott',sysdate,'sys',sysdate); insert into

stud values(80605321,'陈','光春','陈','四川','100003','02887720318','西华大学',sysdate,'scott',sysdate,'system',sysdate);

end;

--------------------执行该过程SQL>exec sp_pro03;

create tableenrollment(student_id number(8,0) references stud(student_id) notnull,section_id number(8,0) references section(section_id) not null,enroll_datedate not null,final_date number(3,0) not null,create_byvarchar2(30),create_date date not null,modified_by varchar2(30) notnull,modified_date date not null);-----------------------------------------------------------------------------------------------------------------------------SQL>create table enrollment(student_id number(8,0) referencesstud(student_id) not null,section_id number(8,0) references section(section_id)not null,enroll_date date not null,final_grade number(3,0),create_byvarchar2(30) not null,created_date date not null,modified_by varchar2(30) notnull,modified_date date not null);

--------------创建一个过程create or replace procedure sp_pro04 isbegin insert intoenrollment values(80605301,191,sysdate,98,'scott',sysdate,'sys',sysdate);insert into enrollmentvalues(80605302,192,sysdate,78,'scott',sysdate,'sys',sysdate); insert intoenrollment values(80605303,193,sysdate,68,'scott',sysdate,'sys',sysdate); insertinto enrollment values(80605304,194,sysdate,90,'scott',sysdate,'sys',sysdate);insert into enrollmentvalues(80605305,195,sysdate,77,'scott',sysdate,'sys',sysdate); insert intoenrollment values(80605310,196,sysdate,76,'scott',sysdate,'system',sysdate); insertinto enrollment values(80605311,197,sysdate,98,'scott',sysdate,'sys',sysdate);insert into enrollmentvalues(80605312,198,sysdate,78,'scott',sysdate,'scott',sysdate);end;

--------------------执行该过程SQL>exec sp_pro04;

-----------------------------------------------------------------------------------------------------------------------------

SQL>create

table instructor(instructor_id number(8,0) primary key not null,salutation

varchar2(5),first_name varchar2(25),last_Name varchar2(25) not null,street_address

varchar2(50) not null,zip varchar2(10) not null,phone char(11),create_by

varchar2(30) not null,created_date date not null,modified_by varchar2(30) not

null,modified_date date not null);--------------创建一个过程create or

replace procedure sp_pro05 isbegin insert into instructor values(150,'石','红','石','四川','100001','02887720011','scott',sysdate,'sys',sysdate);

insert into instructor values(190,'高','勤','高','北京','100002','02887720012','scott',sysdate,'sys',sysdate);

insert into instructor values(170,'周','玲','周','四川','100001','02887720013','scott',sysdate,'sys',sysdate);

insert into instructor values(100,'朱','雯','朱','上海','100004','02887720014','scott',sysdate,'sys',sysdate);

insert into instructor values(130,'程','娜','程','四川','100001','02887720011','scott',sysdate,'sys',sysdate);

insert into instructor values(110,'廖','燕','廖','北京','100002','02887720012','scott',sysdate,'sys',sysdate);

insert into instructor values(160,'周','琼','周','四川','100001','02887720013','scott',sysdate,'sys',sysdate);

insert into instructor values(134,'严','常龙','严','上海','100004','02887720014','scott',sysdate,'sys',sysdate);end;

--------------------执行该过程SQL>exec sp_pro05;

-----------------------------------------------------------------------------------------------------------------------------

SQL>createtable zipcode(zip varchar2(10) not null,city varchar2(25) not null,statevarchar2(2),create_by varchar2(30) not null,created_date date notnull,modified_by varchar2(30) not null,modified_date date not null);

SQL>createtable grade_type(grade_type_code char(2),description varchar2(50),create_byvarchar2(30) not null,created_date date not null,modified_by varchar2(30) notnull,modified_date date not null);

SQL>createtable grade_type_wight(section_id char(2) not null,grade_type_code char(2) notnull,number_per_section number(3) not null,percent_of_final_grade number(3) notnull,drop_lowest char(1) not null,create_by varchar2(30) not null,created_datedate not null,modified_by varchar2(30) not null,modified_date date not null);

SQL>createtable grade(student_id number(8,0) references stud(student_id) notnull,section_id number(8,0) references section(section_id) notnull,grade_type_code char(2),grade_code_occurence number(38) notnull,numeric_grade number(3) not null,comments varchar2(2000),create_by varchar2(30)not null,created_date date not null,modified_by varchar2(30) notnull,modified_date date not null);

SQL>create

table gradte_conversion(letter_grade varchar2(2) not null,grade_point

number(3,2) not null,max_grade number(3) not null,min_grade number(3) not

null,create_by varchar2(30) not null,created_date date not null,modified_by

varchar2(30) not null,modified_date date not

null);------------------------------------------------------------------------------------------------------第11章游标简介declarev_first_name stud.first_name%type; v_last_name stud.last_name%type;begin selectfirst_name,last_name into v_first_name,v_last_name from stud where student=123;dbms_output.put_line('student name:'||v_first_name||''||v_last_name);exceptionwhen no_data_found then dbms_output.put_line('there is no student with studentID 123');end;

----------记录类型declare vr_student stud%rowtype;-------创建一个基于表的游标begin select * into vr_stud from stud wherestudent_id=156; dbms_output.put_line(vr_student.first_name||''||vr_student.last_name||'hasan ID of 156'); exception when no_data_found thenraise_application_erroe(-2001,'the student'||'is not in the database'); end;

---===================================================================================================---============================第1章 PL/SQL概念declaredeclaration statementsbegin Exectable statementsexception Exception-handlingstatementsend;

---1.1declarev_first_name varchar2(35); v_last_name varchar2(35); c_counter constantnumber:=0;---1.2declare v_first_name varchar2(35); v_last_name varchar2(35);c_counter constant number:=0;begin select first_name,last_name intov_first_name,v_last_name from student where student_id=123;dbms_output.put_line('student name:'||v_first_name||''||v_last_name);end;

---1.3

declarev_first_name varchar2(35); v_last_name varchar2(35); c_counter constantnumber:=0;begin select first_name,last_name into v_first_name,v_last_name fromstudent where student_id=123; dbms_output.put_line('studentname:'||v_first_name||''||v_last_name);exception when no_data_found thendbms_output.put_line('there is not student with'||'student id equals 123');end;

--------------------------------时尚购物网------------------------------------------drop database

testdbgocreate database testdb;gouse testdb;go---存储过程create Sequencesequ_stationinfo_idincrement by 1start with 1nomaxvaluenocyclecache

-------------------创建一个序列create sequence seqmax increment by 1;-------------------普通用户的表create table users(userid number(10) primary key,--用户idusername varchar2(30) not null unique,--用户名truename varchar2(30) not null,--真实姓名passwd varchar2(30) not null,--密码email varchar2(40) not null,--电子邮件phone varchar2(20) not null, --电话号码address varchar2(30) not null, --用户地址postcode char(6) not null,--邮编grade int default 5 --用户的级别)

--管理员表admin

--货物表create table goods (goodsId number(10) primary key

identity(1,1),--货物idgoodsName varchar2(40) ,--名称goodsIntro varchar2(500),--介绍goodsPrice float

,--价格goodsNum int ,--数量publisher varchar2(40),--发行商photo

varchar2(40),--照片type varchar2(10)--类型)

--直接向数据库添加货物insert into goods values(seqmax.nextval, '黑白森林','这是一部好片', 59, 1, '香港嘉禾出品','01.jpg','香港电影')insert into

goods values(seqmax.nextval, '金鸡II','这是一部好片', 45, 1, '香港嘉禾出品','02.jpg','香港电影')insert into

goods values(seqmax.nextval, '靓女菜馆', '这是一部好片',99, 1, '香港嘉禾出品','03.jpg','香港电影')insert into

goods values(seqmax.nextval, '布衣神相','这是一部好片', 10, 1, '香港嘉禾出品','04.jpg','香港电影')insert into

goods values(seqmax.nextval, '洛神', '这是一部好片',68, 1, '香港嘉禾出品','05.jpg','香港电影')insert into

goods values(seqmax.nextval, '黑白森林', '这是一部好片',56, 1, '香港嘉禾出品','01.jpg','香港电影')insert into

goods values(seqmax.nextval, '金鸡II', '这是一部好片',55, 1, '香港嘉禾出品','02.jpg','香港电影')

-----------------存储过程create or replace procedure sp_pro18 isbegin insert into

goods values(seqmax.nextval, '黑白森林','这是一部好片', 59, 1, '香港嘉禾出品','01.jpg','香港电影');insert into

goods values(seqmax.nextval, '金鸡II','这是一部好片', 45, 1, '香港嘉禾出品','02.jpg','香港电影');insert into

goods values(seqmax.nextval, '靓女菜馆', '这是一部好片',99, 1, '香港嘉禾出品','03.jpg','香港电影');insert into

goods values(seqmax.nextval, '布衣神相','这是一部好片', 10, 1, '香港嘉禾出品','04.jpg','香港电影');insert into

goods values(seqmax.nextval, '洛神', '这是一部好片',68, 1, '香港嘉禾出品','05.jpg','香港电影');insert into

goods values(seqmax.nextval, '黑白森林', '这是一部好片',56, 1, '香港嘉禾出品','01.jpg','香港电影');insert into

goods values(seqmax.nextval, '金鸡II', '这是一部好片',55, 1, '香港嘉禾出品','02.jpg','香港电影');

end;------------------------执行存储过程sql>exec sp_pro18;

--向用户表中初始化一个用户，将来是通过注册界面加入的

insert into

users values('shunping','韩顺平','shunping','hanshunping@tsinghua.org.cn','010-88888888','星星小区哈哈楼嘻嘻单元123房间','123456',1);

-------------------创建一个序列create sequence seqmax1 increment by 1;-----------------创建一个存储过程create or replace procedure sp_pro19 isbegin insert into

users values(seqmax1.nextval,'shunping','王红祥','shunping','wang010366@vip.qq.com','028-87720557','星星小区哈哈楼嘻嘻单元123房间','123456',1); insert into users

values(seqmax1.nextval,'whx','whx','wxh','wang010366@vip.qq.com','028-87720033','星星小区哈哈楼嘻嘻单元123房间','123456',1)end;

---------执行存储过程sql>exec sp_pro19;sql>

--学生可能这样设计订单表(错误)--create table orders(ordersId bigint

primary key identity(1,1),--订单号userId bigint constraint fk_client_id

references users(userid),--哪个用户订的goodsId bigint constraint fk_shangpin_id

references goods(goodsId),--商品号nums int not null,--数量orderDate datetime default getdate(),--下订单的时间payMode varchar2(20)check (payMode in('货到付款','支付宝付款')) default '货到付款',--付款的方式isPayed bit check ( isPayed in (0

,1)),--(0,表示还没有付款 1:表示已经付款了)totalPrice

float not null,--总价格)

--应该这样去设计订单表create table orders( ordersId number(10) primary key,--订单号 userId number(10) constraint fk_client_id references

users(userid),--哪个用户订的 orderDate datetime default getdate(),--下订单的时间 payMode varchar2(20) check (payMode in('货到付款','支付宝付款')) default '货到付款',--付款的方式 isPayed bit check ( isPayed in (0

,1)),--(0,表示还没有付款 1:表示已经付款了) totalPrice

float not null--总价格)

--订单细节表create table orderDetail( ordesIid bigint constraint

fk_order_id references orders(ordersId),--订单号(并是一个外键) 指向orders表的主键 goodsId bigint

constraint fk_shangpin_id references goods(goodsId),--商品号(并是一个外键) 指向goods表的主键 nums int not null--数量)

--显示一下各个表的初始化信息select * from users;select * fromgoods;select * from orders

select * fromorderDetail

-------Hibernate

HelloWorld---------------------------建立新java 项目,名为hibernate_0100_HelloWorld--学习建User-library-hibernate,并加入相应的jar包--项目右键-buildpath-configure build path-add

library—--选择User-library,在其中新建 libraray,命名为 hibernate--在该library中加入hibernate所需jar包--hibernate

core--/required--slf-nop jar--引入mysql的JDBC驱动包--在mysql中建立对应的数据库以及表--create

database hibernate；--use hibernate；--create table Student (id int primary key,

namevarchar(20), age int)；--建立hibernate 配置文件hibernate.cfg.xml--从参考文档中copy--修改对应的数据库连接--注释掉暂时用不上的内容--建立Student 类--建立Student 映射文件 Student.hbm.xml--参考文档--将映射文件加入到hibernate.cfg.xml中--参考文档--写测试类Main,在Main中对Student对象进行直接的存储测试 --参考文挡--FAQ：--要调用 new

Configuration().configure().buildSessionFactory(),而不是-- 要省略 configure，否则会出 hibernate

dialect must be set 的异常--Note:--请务必建立自己动手査文挡的能力--重要的是:--要建立自己动手查一手文档的信心--还有建立自己动手查一手文档的习惯！--主动学习，砍弃被动接受灌输的习惯！--建立能力--错误读完整--读—昔误的关键行--排除法--比较法--google

-----------------建立 Annotation 版本的HelloWorld---------------------创建teacher 表，sql>create

table teacher (id number(3) primary key,name varchar2(20),title

varchar2(10));--创建Teacher 类--在hibernate lib 中加入annotation的jar包--hibernate annotaion jar--ejb3

persistence jar--hibernate common-annotations.jar--注意文裆中没有提到hibernate-common-annotations.jar 文件--参考Annotaion文档建立对应的注解--在hibernate.cfg.xml中建立映射

class:.../〉--参考文裆进行测试（注意文裆中缺少configure()的小bug) --FAQ: @不给提示--配置eclipse属性信息content assist-activation--加上@--------------------------------------------------------------------------------------------------------------------------------------------------第19章--ch19_01.sqlSQL>create or replace

procedure Discount as cursor c_group_discount is select distinct

s.course_no,c.description from section s,enrollment e,course c where

s.section_id=e.section_id and c.course_no=s.course_no group by

s.course_no,c.description,e.section_id,s.section_id having count(*)>=8;

begin for r_group_discount in c_group_discount loop update course set

cost=cost*.95 where course_no=r_group_discount.course_no;

dbms_output.put_line('A_5% discount has been given

to'||r_group_discount.course_no||''||r_group_discount.description); end loop;

end;SQL>execute Discount;--编写一个select语句，以便于使用user_source视图来显示discount过程的源代码SQL>column text format a770 selectto_char(line,99)||'>',text from user_source wherename='DISCOUNT';--ch19_02.sqlSQL>create or replace procedurefind_name(i_student_id in number,o_first_name out varchar2,o_last_name outvarchar2) as begin select first_name,last_name into o_first_name,o_last_namefrom stud where student_id=i_student_id; exception when others thendbms_output.put_line('error in finding student_id'||i_student_id); endfind_name;SQL>declare v_local_first_name stud.first_name%type;v_local_last_name stud.last_name%type; beginfind_name(80605304,v_local_first_name,v_local_last_name);dbms_output.put_line('student 80605304is:'||v_local_first_name||''||v_local_last_name); end;

--ch20_01.sqlSQL>createor replace function show_description(i_course_no course.course_no%type) returnvarchar2 as v_description varchar2(50); begin select description intov_description from course where course_no=i_course_no; return v_description;exception when no_data_found then return('the course is not in the database');when others then return('error in running show_description'); end;

--ch20_01b.sqlSQL>createor replace function id_is_good(i_student_id in number) return boolean asv_id_cnt number; begin select count(*) into v_id_cnt from stud wherestudent_id=i_student_id; return 1=v_id_cnt; exception when others then returnfalse; end id_is_good;

set serveroutputon;SQL>declare v_description varchar2(50); beginv_description:=show_description(&sv_number);dbms_output.put_line(v_description); end;

--测试id_is_good函数的一个方法SQL>declare

v_id number; begin v_id:=&id; if id_is_good(v_id) then

dbms_output.put_line('student id:'||v_id||'is a valid'); else

dbms_output.put_line('student id:'||v_id||'is not valid'); end if;

end;--ch20_01c.sql version 1.0SQL>create or replace function

new_instructor_id return instructor.instructor_id%type as v_new_instid

instructor.instructor_id%type; begin select instructor_id_SEQ.nextval into

v_new_instid from dual where v_new_instid; exception when others then declare

v_sqlerrm varchar2(250):=substr(SQLERRM,1,250); begin

raise_application_error(-20003,'error in instructor_id:'||v_sqlerrm); end; end

new_instructor_id;---------------------------------------------------------------------------------------SQL>declare

cons_zip constant zipcode.zip%type:='&sv_zipcode'; e_zipcode_is_not_valid

exception; begin if zipcode_does_not_exist(cons_zip) then raise

e_zipcode_is_not_valid; else null; end if; exception when e_zipcode_is_not_valid

then raise_application_error(-20003,'could not find zipcode'||cons_zip||'.');

end;---=============================================================================================---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------第21章包------------------------------------------------------------------------------------------------------------------------------------------------------------ch21_1.sqlSQL>create

or replace package manage_students as procedure find_name(i_student_id in

stud.student_id%type,o_first_name out stud.first_name%type,o_last_name out

stud.last_name%type); function id_is_good(i_student_id in stud.student_id%type)

return boolean; end manage_students;-----ch21_2.sqlSQL>declare v_first_name

stud.first_name%type; v_last_name stud.last_name%type; begin

manage_students.find_name(125,v_first_name,v_last_name); dbms_output.put_line(v_first_name||''||v_last_name);

end;-------ch21_4a.sqlSQL>create or replace package body manage_students as

procedure find_name(i_student_id in stud.student_id%type, o_first_name out

stud.first_name%type, o_last_name out stud.last_name%type) is v_student_id

stud.student_id%type; begin select first_name,last_name into

o_first_name,o_last_name from stud where student_id=i_student_id; exception

when others then dbms_output.put_line('error in finding

student_id:'||v_student_id); end find_name; function id_is_good(i_student_id in

stud.student_id%type) return boolean is v_id_cnt number; begin select count(*)

into v_id_cnt from stud where stud.student_id=i_student_id; return 1=v_id_cnt;

exception when others then return false; end id_is_good; end manage_students;------------------------------------------------------------------------------第一步创建包头SQL>create or replace package school_api as procedure

discount_cost; function new_instructor_id return instructor.instructor_id%type;

end school_api;---第二步实现包体--------ch21_5a.sqlSQL>create orreplace package body school_api as procedure discount_cost is cursorc_group_discount is select distinct s.course_no,c.description from sections,enrollment e,course c where s.section_id=e.section_id andc.course_no=s.course_no group by s.course_no,c.description,e.section_id,s.section_idhaving count(*)>=8; begin for r_group_discount in c_group_discount loopupdate course set cost=cost*.95 where course_no=r_group_discount.course_no;dbms_output.put_line('A_5% discount has been given to' ||r_group_discount.course_no||''||r_group_discount.description); end loop; end discount_cost; functionnew_instructor_id return instructor.instructor_id%type is v_new_instidinstructor.instructor_id%type; begin select instructor_id_SEQ.nextval intov_new_instid from dual; return v_new_instid; exception when others then declarev_sqlerrm varchar2(250):=substr(SQLERRM,1,250); beginraise_application_error(-20003,'error in instructor_id:'||v_sqlerrm); end; endnew_instructor_id; end school_api;--------------ch21_6a.SQLSQL>setserveroutput on;SQL>declare v_first_name stud.first_name%type; v_last_namestud.last_name%type; begin if manage_students.id_is_good(&&v_id); thenmanage_students.find_name(&&v_id,v_first_name,v_last_name); dbms_output.put_line(v_first_name||''||v_last_name);else dbms_output.put_line('Student ID:'||&&v_id||'is not in thedatabase'); end if; end;

---ch21_7a.sqlSQL>declare

v_instructor_id instructor.instructor_id%type; begin school_api.discount_cost;

v_instructor_id:=school_api.new_instructor_id; dbms_output.put_line('the new id

is :'||v_instructor_id); end;--——ch21_9a.sql--第一步创建包头SQL>create or

replace package course_pkg as type course_rec_typ is record(first_name

stud.first_name%type,last_name stud.last_name%type,course_no

course.course_no%type,description course.description%type,section_no

section.section_no%type); type course_cur is ref cursor return course_rec_typ;

procedure get_course_list(p_student_id number,p_instructor_id

number,course_list_cv in out course_cur); end course_pkg;---第二部创建包体SQL>create or replace package body course_pkg as

procedure get_course_list(p_student number,p_instructor number,course_lsit_cv

in out course_cur) is begin if p_student_id is null and p_instructor_id is null

then open course_list_cv for select 'please choose a

student-'first_name,'instructor combination' last_name,null course_no,null

description,null section_no from dual; elsif p_student_id is null then open

course_list_cv for select s.first_name first_name,s.last_name

last_name,c.course_no course_no,c.description description,se.section_no

section_no from instructor i,stud s,section se,course c,enrollment e where

i.instructor_id=p_instructor_id and i.instructor_id=se.instructor_id and

se.course_no=c.course_no and e.student_id=s.student_id and e.section_id=se.section_id

order by c.course_no,se.course_no; elsif p_instructor_id is null then open

course_list_cv for select i.last_name last_name,c.course_no

course_no,c.description description,se.section_no section_no from instructor

i,stud s,section se,course c,enrollment e where s.student_id=p_student_id and

i.instructor_id=se.instructor_id and se.course_no=c.course_no and

e.student_id=s.student_id and e.section_id=se.section_id order by

c.course_no,se.section_no; end if; end get_course_lsit; end course_pkg; ---ch21_10a.sql--第一步创建包头SQL>create or replace package student_info_pkg as type

student_details is ref cursor; procedure get_student_info(p_student_id

number,p_choice number,details_cv in out stud_details); end student_info_pkg;--第二部创建包体SQL>create or replace package body student_info_pkg asprocedure get_student_info(p_student_id number,p_choice number,detail_cv in outstud_details) is begin if p_choice=1 then open details_cv for selects.first_name first_name,s.last_name last_name,s.street_address address,z.citycity,z.state state,z.zip zip from stud s,zipcode z wheres.student_id=p_student_id and z.zip=s.zip; elsif p_choice=2 then opendetails_cv for select from stud s,section se,course c,enrollment e wherese.course_no=c.course_no and e.student_id=s.student_id ande.section_id=se.section_id and se.section_id in(select e.section_id from studs,enrollment e where s.student_id=p_student_id and s.student_id=e.student_id)order by c.course_no; elsif p_choice=3 then open details_cv for selecti.first_name first_name,i.last_name last_name,c.course_nocourse_no,c.description description,se.section_no section_no from instructori,stud s,section se,course c,enrollment e where s.student_id=p_student_id andi.instructor_id=se.instructor_id and se.course_no=c.course_no and e.student_id=s.student_idand e.section_id=se.section_id order by c.course_no,se.section_no; end if; endget_student_info; end student_info_pkg;

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------数据库作业---------第一步创建四张表SQL>create table J(JNO varchar2(20)

primary key,JNAME varchar2(20) not null,CITY varchar2(20) not

null);SQL>create table S(SNO varchar2(20) primary key,SNAME varchar2(20) not

null,STATUS numeric(3,0) not null,CITY varchar2(20) not null);SQL>create

table P(PNO varchar2(20) primary key,PNAME varchar2(20) not null,COLOR char(2)

not null,WEIGHT numeric(3,0) not null);SQL>create table SPJ(SNO varchar2(20)

references S(SNO),PNO varchar2(20) references P(PNO),JNO varchar2(20)

references J(JNO),QTY numeric(3,0) not null);---------第二步插入数据，建立过程create or replace procedure sp_pro20

isbegin insert into S values('S1','精益',20,'天津'); insert into S values('S2','盛锡',10,'北京'); insert into S values('S3','东方红',30,'北京'); insert into S values('S4','丰泰盛',20,'天津'); insert into S values('S5','为民',30,'上海');end;

create or

replace procedure sp_pro_21 isbegin insert into J values('J1','三建','北京'); insert into J values('J2','一汽','长春'); insert into J values('J3','弹簧厂','天津'); insert into J values('J4','造船厂','天津'); insert into J values('J5','机车厂','唐山'); insert into J values('J6','无线电厂','常州'); insert into J values('J7','半导体厂','南京');end;

create or

replace procedure sp_pro22 isbegin insert into P values('P1','螺母','红',12); insert into P values('P2','螺栓','绿',17); insert into P values('P3','螺丝刀','蓝',14); insert into P values('P4','螺丝刀','红',14); insert into P values('P5','凸轮','蓝',40); insert into P values('P6','齿轮','红',30);end;

create or

replace procedure sp_pro23 isbegin insert into SPJ values('S1','P1','J1',200);

insert into SPJ values('S1','P1','J3',100); insert into SPJ

values('S1','P1','J4',700); insert into SPJ values('S1','P2','J2',100); insert

into SPJ values('S1','P3','J1',400); insert into SPJ

values('S2','P3','J2',200); insert into SPJ values('S2','P3','J4',500); insert

into SPJ values('S2','P3','J5',400); insert into SPJ

values('S2','P5','J1',400); insert into SPJ values('S2','P5','J2',100); insert

into SPJ values('S3','P1','J1',200); insert into SPJ

values('S3','P3','J1',200); insert into SPJ values('S4','P5','J1',100); insert

into SPJ values('S4','P6','J3',300); insert into SPJ

values('S4','P6','J4',200); insert into SPJ values('S5','P2','J4',100); insert

into SPJ values('S5','P3','J1',200); insert into SPJ

values('S5','P6','J2',200); insert into SPJ

values('S5','P6','J4',500);end;------------------------------------------------------------------------------------------------------第三步exec sp_pro20;exec sp_pro21;exec sp_pro_22;exec

sp_pro23;-------------------------------------------------------------第四步SQL>select * from S;SQL>select * fromJ;SQL>select * from P;SQL>select * from SPJ;

---------------------------------------第五步，完成作业--1.找出所有供应商的姓名和所在城市SQL>select

SNAME,CITY from S;--2.找出所有零件的名称、颜色、重量SQL>select

PNAME,COLOR,WEIGHT from P;--3.找出使用供应商S1所供应零件的工程号码SQL>select

distinct JNO from SPJ where SNO='S1' order by JNO;--4.找出工程项目J2使用的各种名称及其数量SQL>selectPNAME,WEIGHT from P where P.PNO in(select PNO from SPJ whereJNO='J2');SQL>select P.PNAME,count(*) from P where P.PNO in(select PNO fromSPJ where JNO='J2');

--5.找出上海厂商供应的所有零件号码SQL>select distinct SPJ.PNO from SPJ

where SPJ.SNO in (select S.SNO from S where S.CITY='上海');--6.找出使用上海产的零件的工程名称SQL>select

J.JNAME from J where J.JNO in (select distinct SPJ.JNO from SPJ where SPJ.SNO

in (select S.SNO from S where S.CITY='上海'));---7.找出没有使用天津厂的零件的工程号码SQL>select distinct SPJ.JNO from SPJ

where SPJ.SNO in(select S.SNO from S where S.CITY!='天津')---8.把全部红色零件的颜色改成蓝色SQL>update P

set P.COLOR='蓝'where P.COLOR='红';--9.由S5供应J4的零件P6改成S3供应，请做必要的修改SQL>selectSPJ.JNO from SPJ where SPJ.SNO='S5';SQL>select * from (select SPJ.JNO fromSPJ where SPJ.SNO='S5') where JNO='J4';SQL>select SPJ.PNO,SPJ.SNO from SPJwhere SPJ.JNO in(select * from (select SPJ.JNO from SPJ where SPJ.SNO='S5')where JNO='J4') and SPJ.PNO='P6';SQL>select SPJ.SNO from SPJ group bySPJ.SNO;SQL>select SPJ.SNO from SPJ group by SPJ.SNO havingSPJ.SNO='S5';SQL>select SPJ.JNO from SPJ where SPJ.SNO=(select SPJ.SNO fromSPJ group by SPJ.SNO having SPJ.SNO='S5');

---答案SQL>select * from SPJ where SPJ.PNO='P6';SQL>select

* from (select * from SPJ where SPJ.PNO='P6') where JNO='J4';SQL>select *

from (select * from (select * from SPJ where SPJ.PNO='P6') where JNO='J4')

where SNO='S5';SQL>update (select * from (select * from (select * from SPJ

where SPJ.PNO='P6') where JNO='J4') where SNO='S5') set SNO='S3';----或者SQL>update spj set sno='S3' where sno='S5' AND

JNO='J4' AND PNO='P6'; ---10.从供应商关系中删除S2的记录，并从供应情况关系中删除对应的记录SQL>deletefrom SPJ where SPJ.SNO='S2';SQL>delete from S where S.SNO='S2';

---请将(S2,J6,P4,200)插入供应情况关系SQL>insertinto SPJ values('S2','J6','P4',200);

---标准答案--1select sname,city from s;--2select pname,color,weight

from p;--3select jno from spj where sno='s1';--4select p.pname,spj.qty from

spj,p where jno='j2' and p.pno=spj.pno; --5select pno from spj where sno

in(select sno from s where city='上海');--6select

jname from j where jno in (select jno from spj where sno in(select sno from s

where city='上海')); --7select jno from j where jno not in

(select jno from spj where city='天津');--8select *

from p;update p set color='蓝' where color='红';select * from p;--9update spj set sno='s3' where sno='s5'

and pno='p6' and jno='j4';--10select * from spj;delete from s where

sno='s2';delete from spj where sno='s2';select * from

spj;----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ORA-08002:序列 INSTRUCTOR_ID_SEQ.CURRVAL 尚未在此会话中定义ORA-8002 WhenSelecting CURRVAL From Sequence [ID 1019173.102]--------------------------------------------------------------------------------Modified 09-SEP-2010 Type PROBLEM Status PUBLISHED Problem Description:--------------------You have created the following sequence: CREATE SEQUENCEsome_sequence START WITH 1000;Later, in your code, you try to access thecurrent value of the sequence using the CURRVAL pseudo column: SELECTsome_sequence.CURRVAL from dual;You get the following error: ORA-08002:sequence SOME_SEQUENCE.CURRVAL is not yet defined in this session

SolutionDescription: ---------------------The NEXTVAL function acts as a sequenceinitializer. This can be misleading since in our example when we create thesequence we START WITH 1000. This does not however initialize the sequence. Thefirst call to NEXTVAL initializes the sequence to the START WITH value. (Note thatit does NOT increment the value.)

See the outputbelow for a complete example: SQL> CREATE SEQUENCE some_sequence 2 STARTWITH 1000; Sequence created. /* Use CURRVAL here before NEXTVAL and the erroris thrown */ SQL> SELECT some_sequence.CURRVAL "Value" 2 FROMDUAL; FROM DUAL * ERROR at line 2: ORA-08002: sequence SOME_SEQUENCE.CURRVAL isnot yet defined in this session /* Now call NEXTVAL and initialize the sequence.. */ SQL> SELECT some_sequence.NEXTVAL "Value" 2 FROM DUAL; Value--------- 1000 /* Now we have access to the current value CURRVAL */ SQL>SELECT some_sequence.CURRVAL "Value" 2 FROM DUAL; Value ---------1000 /* Now that the sequence has been initialized with the first call toNEXTVAL, the 2nd call to NEXTVAL increments as it should.. */ SQL> SELECTsome_sequence.NEXTVAL "Value" 2 FROM DUAL; Value --------- 1001SQL> SELECT some_sequence.CURRVAL "Value" 2 FROM DUAL; Value--------- 1001

SolutionExplanation: ---------------------

Before you canaccess CURRVAL for a sequence, you must first initialize the sequence withNEXTVAL.

References:-----------Oracle Server SQL Reference, Pseudocolumns CURRVAL and NEXTVAL.

Search Words:

-------------ORA-8002-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------第四次作业---创建两张表SQL>create table employer( empno

varchar2(20) primary key, empname varchar2(20) not null, empage numeric(3,0)

not null check(empage>=16 and empage <=100), empjob varchar2(20) not

null, empsalary numeric(9,2) not null check(empsalary>=0), empdeptno

varchar2(20) not null, foreign key(empdeptno) references department(deptno)

);SQL>create table department( deptno varchar2(20) primary key, deptname

varchar2(20) not null unique, deptmanager varchar2(20) not null, deptaddress

varchar2(20) not null, deptphone varchar2(11) not null unique );

----------------------------------------------------------------------------------------------------------------------------------创建用户SQL>create user wangming with

resource,connect;SQL>create user liyong with resource,connect;SQL>create

user liuxing with resource,connect;SQL>create user zhangxin with

resource,connect;SQL>create user zhouping with

resource,connect;SQL>create user yanglan with resource,connect;--创建过程SQL>create or replace procedure sp_pro21 is begin

create user wangming with resource,connect; create user liyong with

resource,connect; create user liuxing with resource,connect; create user

zhangxin with resource,connect; create user zhouping with resource,connect;

create user yanglan with resource,connect;

end;-------------------------------------------------------------------------------------------------a.用户王明对两张表有select权力SQL>grant select on

employer,department to wangming;--b.用户李勇对两张表有insert和delete权力SQL>grant insert,delete on

employer,department to liyong;--c.每个职工对自己的记录有select权力SQL>grant select on employer where empname=user to

public;--d.用户刘星对职工表有select权力，对工资字段具有更新权力SQL>grant

select,update(empsalary) on employer to liuxing; --e.用户张新具有修改这两张表的结构的权力SQL>grant alter on

employer,department to zhangxin;--f.用户周平具有对两张表所有权利(读、插、改、删数据),并具有给其他用户授权的权力SQL>grant all

privileges on employer,department to zhouping with grant option;--g.用户杨兰具有从每个部门职工中select最高工资，最低工资，平均工资的权力,他不能查看每个人的工资SQL>create view deptsalary as selectdepartment.deptname,max(empsalary),min(empsalary),avg(empsalary) fromemployer,department where employer.empdeptno=department.deptno group byemployer.empdeptno;SQL>grant select on deptsalary to yanglan;

---撤销个用户所授予的权力--aSQL>revoke select on employer,department fromwangming;--bSQL>revoke insert,delete on employer,department fromliyong;--cSQL>revoke select on employer where empname=user frompublic;--dSQL>revoke select,update(empsalary) on employer fromliuxing;--eSQL>revoke all privileges on employer,department from zhoupingcascade;--fSQL>revoke select on deptsalary from yanglan;SQL>drop viewdeptsalary;

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------标准答案 --a 、用户王明对两个表有SELECT 权力。 SQL>GRANT SELECT ON 职工,部门 TO 王明 --b 、用户李勇对两个表有INSERT 和DELETE 权力。 SQL>GRANT INSERT,DELETE ON 职工,部门 TO 李勇 --c 、每个职工只对自己的记录有SELECT 权力。 SQL>GRANT

SELECT ON 职工 WHEN USER()=NAME TO ALL; --d 、用户刘星对职工表有SELECT 权力，对工资字段具有更新权力。 SQL>GRANT

SELECT,UPDATE(工资) ON 职工 TO 刘星 --e 、用户张新具有修改这两个表的结构的权力。 SQL>GRANT

ALTER TABLE ON 职工,部门 TO 张新; --f 、用户周平具有对两个表所有权力（读，插，改，删数据），并具有给其他用户授权的权力。 SQL>GRANT

ALL PRIVILIGES ON 职工,部门 TO 周平 WITH GRANT OPTION; --g、用户杨兰具有从每个部门职工中SELECT 最高工资、最低工资、平均工资的权力，他不能查看每个人的工资。 SQL>CREATE VIEW 部门工资 AS SELECT 部门.名称,MAX(工资),MIN(工资),AVG(工资) FROM 职工,部门 WHERE 职工.部门号=部门.部门号 GROUP BY 职工.部门号 GRANT SELECT ON 部门工资 TO 杨兰; --9 ．把习题8 中（1）---（7）的每一种情况，撤销各用户所授予的权力 --1

SQL>REVOKE SELECT ON 职工，部门 FROM 王明;--2

SQL>REVOKE INSERT , DELETE ON 职工，部门 FROM 李勇;--3SQL>REOVKE SELECT ON 职工 WHEN USER ( ) =NAME FROM ALl; --4SQL>REVOKE SELECT ,

UPDATE ON 职工 FROM 刘星;

--5SQL>REVOKE ALTER TABLE ON 职工,部门 FROM 张新; --6 SQL>REVOKE ALL PRIVILIGES ON 职工,部门 FROM 周平; --7

SQL>REVOKE SELECT ON 部门工资 FROM 杨兰; SQL>DROP

VIEW 部门工资;

numeric(6) constraint c1 check (sno between 90000 and 99999), sname

varchar2(20) constraint c2 not null, sage numeric(3) constraint c3

check(sage<30), ssex char(2) constraint c4 check(ssex in ('男','女')), constraint studkey primary key

(sno) );---例11SQL>create table teach( eno numeric(4)priamry key, ename varchar2(20), job varchar2(8), sal numeric(7,2), deductnumeric(7,2), deptno numeric(2), constraint empkey foreign key(deptno)references dept(deptno), constraint c1 check(sal+deduct>=30000));---SQL>alter table stu drop constraint c1;SQL>alter table stu addconstraint c1 check(sno between 900000 and 999999999);SQL>alter table studrop constraint c3;SQL alter table stu add constraint c3 check(sage<40);

---------5.6触发器-----定义触发器SQL>create trigger <触发器名> {before|after} <触发事件> on <表名> for each {row|statement} [when <触发条件>] <触发动作体>

SQL>create or replace trigger insert_or_uodate_sal before insert or update

on teach for each row as begin if (new.job='教授')and(new.sal<4000) then new.sal:=4000; end if; end;SQL>create or replacetrigger insert_sal after insert teach for each row as begin insert into sal_logvalues(new.eno,new.sal,current_user,sysdate); end;

SQL>create or

replace trigger uodate_sal after uodate on tach for each row as begin if

new.sal<>old.sal) then insert into sal_log values(new.eno,new.sal,current_user,sysdate);

end if;

end;----------------------------------------------------------------------------------------------------------------------------------------------------------------------第5次作业--假设有下面的关系模式：

-- 科室（科名，科地址，科电话）-- 病房（病房号，床位号，科室名）

-- 医生（工作证号，姓名，职称，科室名，年龄）

-- 病人（病历号，姓名，性别，诊断，主管医生，病房号）

--其中，一个科室有多个病房、多个医生，一个病房只能属于一个科室，一个医生只属于一个科室，--但可负责多个病人的诊治，一个病人的主管医生只有一个。使用SQL语言定义上面的关系模式，--要求在模式中使用完整性约束命名子名完成以下完整性约束命名条件的定义：

--1）定义每个模式的主码；

--2）定义相关关系模式的实体完整性和参照完整性；

--3）定义病人的性别只能是”男”或”女”；

--4）定义医生的年龄只能在20到80岁之间；

--5）对病人表定义创建一个after行级触发器，当进行插入操作时未指定主管医生时，自动改为王医生。

-- 科室（科名，科地址，科电话）SQL>create table 科室( 科名 varchar2(20) primary key, 科地址 varchar2(20) not null unique, 科电话 varchar2(20) not null unique );-- 病房（病房号，床位号，科室名）SQL>create table 病房( 病房号 varchar2(20) primary key, 床位号 numeric(2) not null unique, 科室名 varchar2(20) not null unique, constraint c11 foreign

key(科室名) references 科室(科名) );-- 医生（工作证号，姓名，职称，科室名，年龄）SQL>create

table 医生( 工作证号 varchar2(20)

primary key, 姓名 varchar2(10) not null, 职称 varchar2(10) not null, 科室名 varchar2(20)

not null, constraint c21 foreign key(科室名) references 科室(科名), 年龄 numeric(3)

check (年龄 between 20 and 80) );-- 病人（病历号，姓名，性别，诊断，主管医生，病房号）SQL>create table 病人( 病历号 varchar2(20) primary key, 姓名 varchar2(20) not null, 性别 char(2) default

'男' check (性别 in ('男','女')), 诊断 varchar2(255)

not null, 主管医生 varchar2(10) not null, constraint c31

foreign key(主管医生) references 医生(姓名), 病房号 varchar2(20)

not null, constraint c41 foreign key(病房号) references 病房(病房号) );

--5）对病人表定义创建一个after行级触发器，当进行插入操作时未指定主管医生时，自动改为王医生。SQL>create or

replace trigger after_insert after insert on 病人 for each row as

begin if new.主管医生 is null then new.主管医生='王医生'; end if; end;

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 203,271评论 5赞 476
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 85,275评论 2赞 380
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 150,151评论 0赞 336
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 54,550评论 1赞 273
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 63,553评论 5赞 365
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,559评论 1赞 281
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 37,924评论 3赞 395
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,580评论 0赞 257
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 40,826评论 1赞 297
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,578评论 2赞 320
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,661评论 1赞 329
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,363评论 4赞 318
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 38,940评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,926评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,156评论 1赞 259
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 42,872评论 2赞 349
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,391评论 2赞 342

摘录的一些知识点

推荐阅读更多精彩内容