摘錄的一些知識(shí)點(diǎn)

ORACLE自學(xué)教程

--create tabletestone

( id number, --序號(hào)username varchar2(50),password varchar2(50),sjvarchar2(20));

--導(dǎo)入命令

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

--oracle日志文件

Oracle日志文件管理與查看

　　--1.查詢系統(tǒng)使用的是哪一組日志文件：

　　select * from v$log;

　　--2.查詢正在使用的組所對(duì)應(yīng)的日志文件：

　　select * from v$logfile;

　　--3.強(qiáng)制日志切換：

　　alter system switch logfile;

　　--4.查詢歷史日志：

　　select * from v$log_history;

　　--5.查詢?nèi)罩镜臍w檔模式：

　　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.日志文件移動(dòng)

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

　　--執(zhí)行該命令之前必須保證該日志文件物理上已經(jīng)移動(dòng)到新目錄

　　--10.清除日志文件

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

　　--該命令用于不能用刪除組及組成員命令刪除日志時(shí)使用

--文本內(nèi)容導(dǎo)入數(shù)據(jù)庫(kù)

---將文本文件/(excel表中)的內(nèi)容導(dǎo)入到oracle數(shù)據(jù)庫(kù)中---可以利用PL/SQL developer

--首先查看oracle數(shù)據(jù)庫(kù)的編碼SQL> select * fromnls_database_parameters where parameter ='NLS_CHARACTERSET';

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

--這其來(lái)源于props$馏鹤，這是表示數(shù)據(jù)庫(kù)的字符集剿干。--oracle客戶端編碼SQL> select * from nls_instance_parameters whereparameter='NLS_LANGUAGE';

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

--其來(lái)源于v$parameter珊拼，表示客戶端的字符集的設(shè)置鹦牛，可能是參數(shù)文件，環(huán)境變量或者是注冊(cè)表會(huì)話字符集環(huán)境SQL>select *

from nls_session_parameters;--其來(lái)源于v$nls_parameters脊髓，表示會(huì)話自己的設(shè)置，可能是會(huì)話的環(huán)境變量或者是alter session完成栅受，如果會(huì)話沒(méi)有特殊的設(shè)置将硝，將與nls_instance_parameters一致。--再來(lái)說(shuō)一下怎么修改oracle的字符集：--目前我的數(shù)據(jù)庫(kù)環(huán)境的字符集是AL32UTF8屏镊，那么把它改成ZHS16GBK --1.首先以sysdba的身份登錄上去 conn /as sysdba SQL>conn sys/ych as

sysdba; --2.關(guān)閉數(shù)據(jù)庫(kù)shutdown immediate; --3.以mount打來(lái)數(shù)據(jù)庫(kù)依疼，startup mount --4.設(shè)置session SQL>ALTER SYSTEM ENABLE RESTRICTED

SESSION;SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;--PL/SQL Developer與Oracle11g遠(yuǎn)程連接問(wèn)題http://zhumeng8337797.blog.163.com/blog/static/10076891420111115104023136/

--Oracle11g編碼問(wèn)題

SQL> ALTER

SYSTEM SET AQ_TM_PROCESSES=0; --5.啟動(dòng)數(shù)據(jù)庫(kù)SQL>alter

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

ZHS16GBK;--這會(huì)可能會(huì)報(bào)錯(cuò)，提示我們的字符集：新字符集必須為舊字符集的超集而芥，這時(shí)我們可以跳過(guò)超集的檢查做更改：SQL>ALTER

DATABASE character set INTERNAL_USE ZHS16GBK;--這條語(yǔ)句就可以了律罢，TERNAL_USE提供的幫助就會(huì)使oracle繞過(guò)了子集與超集的驗(yàn)證，這條語(yǔ)句和上面的語(yǔ)句內(nèi)部操作時(shí)完全相同的棍丐。 --7.關(guān)閉误辑，重新啟動(dòng)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. 查詢當(dāng)前用戶信息select*from dba_ustats;

--通過(guò)pl/sql中的“瀏覽器”也可以查看user信息

--查看當(dāng)前用戶的缺省表空間

　　SQL>select username,default_tablespace fromuser_users;

---查看當(dāng)前用戶的系統(tǒng)權(quán)限和表級(jí)權(quán)限

　　SQL>select * from user_sys_privs;

　　SQL>select * from user_tab_privs;

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

　　SQL>select * from user_tables;

--1、用戶

----查看當(dāng)前用戶的缺省表空間

　　SQL>select username,default_tablespace fromuser_users;

----查看當(dāng)前用戶的角色

　　SQL>select * from user_role_privs;

----查看當(dāng)前用戶的系統(tǒng)權(quán)限和表級(jí)權(quán)限

　　SQL>select * from user_sys_privs;

　　SQL>select * from user_tab_privs;

----顯示當(dāng)前會(huì)話所具有的權(quán)限

　　SQL>select * from session_privs;

----顯示指定用戶所具有的系統(tǒng)權(quán)限

　　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;

----查看某表的創(chuàng)建時(shí)間

　　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的內(nèi)存區(qū)里的表

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

--3巾钉、索引

----查看索引個(gè)數(shù)和類別

　　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、序列號(hào)

　　查看序列號(hào)趋翻，last_number是當(dāng)前值

　　SQL>select * from user_sequences;

　　5睛琳、視圖

----查看視圖的名稱

　　SQL>select view_name from user_views;

----查看創(chuàng)建視圖的select語(yǔ)句

　　SQL>set view_name,text_length from user_views;

　　SQL>set long 2000; 說(shuō)明：可以根據(jù)視圖的text_length值設(shè)定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师骗、存儲(chǔ)函數(shù)和過(guò)程

----查看函數(shù)和過(guò)程以及包的狀態(tài)

　　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';

----查看函數(shù)和過(guò)程以及包的源代碼

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

---ORACLE中數(shù)據(jù)字典視圖分為3大類,用前綴區(qū)別，分別為：USER讨惩，ALL 和 DBA辟癌，許多數(shù)據(jù)字典視圖包含相似的信息。--USER_*:有關(guān)用戶所擁有的對(duì)象信息荐捻，即用戶自己創(chuàng)建的對(duì)象信息--ALL_*：有關(guān)用戶可以訪問(wèn)的對(duì)象的信息黍少，即用戶自己創(chuàng)建的對(duì)象的信息加上其他用戶創(chuàng)建的對(duì)象但該用戶有權(quán)訪問(wèn)的信息--DBA_*：有關(guān)整個(gè)數(shù)據(jù)庫(kù)中對(duì)象的信息--（這里的*可以為T(mén)ABLES， INDEXES处面， OBJECTS厂置， USERS等。

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

all_users;SQL>select * from user_users;2.查看用戶系統(tǒng)權(quán)限：select * from

dba_sys_privs;select * from all_sys_privs;select * from user_sys_privs;3.查看用戶對(duì)象權(quán)限：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.查看當(dāng)前用戶的缺省表空間select username,default_tablespace fromuser_users;

7.查看某個(gè)角色的具體權(quán)限魂角，如grant connect,resource,create

session,create view to TEST;查看RESOURCE具有那些權(quán)限昵济，用SELECT * FROMDBA_SYS_PRIVS WHERE GRANTEE='RESOURCE';

例：限制user1用戶只允許100個(gè)并發(fā)連接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--執(zhí)行部分delete from mytest where name='王紅祥';end;

--最簡(jiǎn)單的快

---------分頁(yè)算法--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;

---看似相似的分頁(yè)語(yǔ)句，在響應(yīng)速度上其實(shí)有很大的差別野揪。來(lái)看一個(gè)測(cè)試過(guò)程访忿，首先穿件一個(gè)測(cè)試表。SQL>createtable test as select * from emp;

----反復(fù)插入數(shù)據(jù)SQL>insert into test select * from test;

---first_rows對(duì)分頁(yè)的影響----創(chuàng)建一個(gè)測(cè)試表斯稳，并作關(guān)聯(lián)查詢分頁(yè)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.用普通分頁(yè)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;--有定義和執(zhí)行部分的塊declare--定義變量v_ename varchar2(5);begin --執(zhí)行部分 select ename into v_ename from emp where empno=&aa;

--在控制臺(tái)顯示用戶名 dbms_output.put_line('用戶名:'||v_ename); end; --有定義和執(zhí)行部分的塊 --把用戶的工資也顯示出來(lái)declare--定義變量v_ename varchar2(5);v_sal

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

where empno=&aa; select sal into v_sal from emp where emp empno=&aa; --在控制臺(tái)顯示用戶名 dbms_output.put_line('用戶名:'||v_ename);

end; --有定義和執(zhí)行部分的塊 --把用戶的工資也顯示出來(lái)declare--定義變量v_ename varchar2(5);v_sal number(7,2);begin --執(zhí)行部分 select ename,sal into v_ename,v_sal from emp where

empno=&aa; --在控制臺(tái)顯示用戶名 dbms_output.put_line('用戶名:'||v_ename); dbms_output.put_line('薪金:'||v_sal); end; declare--定義變量v_ename

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

ename,sal into v_ename,v_sal from emp where empno=&aa; --在控制臺(tái)顯示用戶名 dbms_output.put_line('用戶名:'||v_ename||'薪金:'||v_sal); exception when no_data_found then

dbms_output.put_line('朋友你的編號(hào)輸入有誤海铆，請(qǐng)重新輸入'); end; --演示4 create procedure sp_pro4(spName varchar2,neSal number)

is begin --執(zhí)行部分---根據(jù)用戶名修改工資 update emp set

sal=newSal where ename spName; end; --函數(shù)案例 --輸入雇員的名字，返回該雇員的年薪 create function sp_fun2(spName varchar2)

return number is yearSal number(7,2); begin --執(zhí)行部分select sal12+nvl(comm,0)12into yearSal from emp where ename=spName; return yearSal; end; --(1)我們可以使用create package命令來(lái)創(chuàng)建 ----創(chuàng)建包 ---創(chuàng)建一個(gè)包sp_package ---聲明該抱有一個(gè)過(guò)程update_sal ---還聲明了該包有一個(gè)函數(shù)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實(shí)現(xiàn)包體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)如何調(diào)用包的過(guò)程或是函數(shù)--當(dāng)調(diào)用包的過(guò)程或是函數(shù)時(shí)挣惰，在過(guò)程和函數(shù)前需要帶上包名卧斟，如果要訪問(wèn)其它方案的包，還需要在包名前加上方案名.--如:sql>call sp_package.update_sal('SCOTT',1500);

--下面已輸入員工號(hào)憎茂，顯示員工姓名唆涝、工資、個(gè)人所得稅(稅率為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 --執(zhí)行部分 select ename,sal into v_ename,v_sal from emp where

empno=&em; ---計(jì)算所得稅 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 --執(zhí)行部分 select ename,sal into v_ename,v_sal

from emp where empno=&em; ---計(jì)算所得稅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 --執(zhí)行部分 select ename,sal into v_ename,v_sal from emp where

empno=&em; ---計(jì)算所得稅 v_tax_sal:=v_sal*c_tax_rate; --輸出 dbms_output.put_line('姓名是:'||v_ename||'工資'||v_sal||'所得稅'||v_tax_sal);end;

---pl/sql記錄實(shí)例declare--定義一個(gè)pl/sql記錄類型emp_record_type,類型包含三個(gè)數(shù)據(jù)name,salary,title;type emp_record_type is record(name

emp.ename%type,salary emp.sal%type,title emp.job%type);--定義了一個(gè)sp_record變量赏枚，這個(gè)變量的類型是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;--復(fù)合類型----pl/sql表的實(shí)例declare--定義了一個(gè)pl/sql表類型sp_table_type(是一個(gè)類型,不是一個(gè)變量)亡驰，該類型是用于存放emp.ename%type類型的數(shù)據(jù)--index by binary_integer;表示下表是整數(shù)type

sp_table_type is table of emp.ename%type index by binary_integer;--定義了一個(gè)sp_table變量,變量的類型是sp_table_type;pl/sql中定義時(shí)變量在前，類型在后面sp_table sp_table_type;beginselect ename

into sp_table(0) from emp where empno=778;dbms_output.putline('員工名:'||sp_table(0));end;--出錯(cuò)饿幅，下標(biāo)出現(xiàn)錯(cuò)誤declare--定義了一個(gè)pl/sql表類型sp_table_type(是一個(gè)類型,不是一個(gè)變量)凡辱，該類型是用于存放emp.ename%type類型的數(shù)據(jù)--index by binary_integer;表示下表是整數(shù)type

into sp_table(-1) from emp where empno=778;dbms_output.putline('員工名:'||sp_table(0));end;--出錯(cuò)栗恩，實(shí)際返回的行數(shù)超出了請(qǐng)求的行數(shù)declare--定義了一個(gè)pl/sql表類型sp_table_type(是一個(gè)類型,不是一個(gè)變量)透乾，該類型是用于存放emp.ename%type類型的數(shù)據(jù)--index by binary_integer;表示下表是整數(shù)type

into sp_table(0) from emp;dbms_output.putline('員工名:'||sp_table(0));end;--參照變量-ref cursor游標(biāo)變量--(1)請(qǐng)使用pl/sql編寫(xiě)一個(gè)塊,可以輸入部門(mén)號(hào),并顯示該部門(mén)所有員工的姓名和他的工資。declare--定義游標(biāo)類型sp_emp_cursortype

sp_emp_cursor is ref cursor;--定義一個(gè)游標(biāo)變量test_cursor sp_emp_cursor;v_ename

emp.ename%type;v_sal emp.sal%type;begin --執(zhí)行部分 --把test_cursor和一個(gè)select結(jié)合 open test_cursor for select ename,sal from emp where

deptno=&no;--循環(huán)取出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)的基礎(chǔ)上捧韵，如果某個(gè)員工的工資低于200元，就增加100元汉操。declare--定義游標(biāo)類型sp_emp_cursortype

sp_emp_cursor is ref cursor;--定義一個(gè)游標(biāo)變量test_cursor sp_emp_cursor;v_ename

emp.ename%type;v_sal emp.sal%type;begin --執(zhí)行部分 --把test_cursor和一個(gè)select結(jié)合 open test_cursor for select ename,sal from emp where

deptno=&no;--循環(huán)取出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;

--編寫(xiě)一個(gè)過(guò)程磷瘤，可以輸入一個(gè)雇員名芒篷，如果該雇員的工資低于2000，就給該雇員工資增加10%

create or

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

emp.sal%type;begin --執(zhí)行 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; --編寫(xiě)一個(gè)過(guò)程采缚，可以輸入一個(gè)雇員名针炉，如果該雇員的補(bǔ)助不是0，就給該雇員補(bǔ)助在原來(lái)的基礎(chǔ)上增加100;如果補(bǔ)助為0就把補(bǔ)助設(shè)置為200;create or replace procedure

sp_pro7(spName varchar2) is --定義v_comm emp.comm%type;begin --執(zhí)行 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; --編寫(xiě)一個(gè)過(guò)程扳抽，可以輸入一個(gè)雇員編號(hào)篡帕，如果該雇員的職位是PRESIDENT，就給該他工資增加1000;如果該雇員是MANAGER,就給他的工資增加500;其他職位的雇員工資增加200;create or replace procedure sp_pro8(spNO number) is

--定義v_job emp.job%type;begin --執(zhí)行部分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)---循環(huán)語(yǔ)句loop,新建一張表create table

usersq(num1 number摔蓝，Name1 varchar2(40));--請(qǐng)編寫(xiě)一個(gè)過(guò)程赂苗，可輸入用戶名，并循環(huán)添加10個(gè)用戶到users表中贮尉，用戶編號(hào)從1開(kāi)始增加拌滋。create or replace procedure

sp_prol(spName varchar2) is--定義:=表示賦值v_num

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

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

* from usersq;---請(qǐng)編寫(xiě)一個(gè)過(guò)程，可輸入用戶名猜谚，并循環(huán)添加10個(gè)用戶到users表中败砂，用戶編號(hào)從1開(kāi)始增加。create or

replace procedure sp_pro2(spName varchar2) is--定義:=表示賦值v_num number:=11;beginwhile v_num<=20 loop --執(zhí)行部分 insert into usersq values(v_num,spName); --自增 v_num:=v_num+1; end loop; end;--for循環(huán)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是已經(jīng)定義好的標(biāo)號(hào)名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('循環(huán)結(jié)束'); 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('循環(huán)結(jié)束'); <> end;---新建表---book表create table book (bookIdnumber,bookName varchar2(50),publishHouse varchar2(50));

---編寫(xiě)過(guò)程---spBookId (in) number,--in代表往存儲(chǔ)儲(chǔ)過(guò)程輸入魏铅，默認(rèn)為in--out代表一個(gè)輸出參數(shù)create orreplace procedure sp_pro4(spBookId in number,spbookName invarchar2,sppulishHouse varchar2) isbegin insert into bookvalues(spBookId,spbookName,sppulishHouse); end;

public classTESTfenye;

--案例:編寫(xiě)一個(gè)過(guò)程昌犹，可以輸入雇員的編號(hào)，返回該雇員的姓名create or

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

ename into spName from emp where empno=spno; end;--案例擴(kuò)展:編寫(xiě)一個(gè)過(guò)程览芳，可以輸入雇員的編號(hào)斜姥，返回該雇員的姓名、工資沧竟、和崗位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.返回結(jié)果集的過(guò)程--1.創(chuàng)建包,在該包中定義了一個(gè)test_cursor類型create or replace package testpackage as type test_cursor

is ref cursor; end testpackage;--2.創(chuàng)建存儲(chǔ)過(guò)程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中調(diào)用該存儲(chǔ)過(guò)程

--oracle分頁(yè)--可以把下面sql語(yǔ)句當(dāng)做一個(gè)模板使用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;

--開(kāi)發(fā)一個(gè)包--使用上面的包c(diǎn)reate or replace package testpackage as

type test_cursor is ref cursor; end testpackage;--開(kāi)始編寫(xiě)分頁(yè)的過(guò)程create or replace procedure fenye(tablename in

varchar2,Psize in number,--3铸敏、4、5悟泵、一頁(yè)顯示記錄PNow in number,--1杈笔、2、3myrows out number,--總記錄數(shù)myPageCount out

number,--總頁(yè)數(shù)p_cursor out testpackage.test_cursor---返回的記錄) is--定義部分--定義sql語(yǔ)句字符串v_sql varchar2(1000);--v_begin

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

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

rn>='||v_begin;--把游標(biāo)和sql語(yǔ)句關(guān)聯(lián)open p_cursor

for v_sql;--關(guān)閉游標(biāo)--計(jì)算myrows和myPageCount--組織了一個(gè)sql語(yǔ)句v_sql:='select count(*) from'||tablename;--執(zhí)行sql語(yǔ)句糕非，并把返回值賦值給myrows;execute

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

myPageCount:=myrows/psize;else myPageCount:=myrows/psize+1;end if;--關(guān)閉游標(biāo)close p_cursor;end;--使用java測(cè)試

---問(wèn)題---排序需求create or replace procedure

fenye(tablename in varchar2,Psize in number,--3蒙具、4球榆、5、一頁(yè)顯示記錄PNow in number,--1禁筏、2持钉、3myrows out number,--總記錄數(shù)myPageCount out number,--總頁(yè)數(shù)p_cursor out

testpackage.test_cursor---返回的記錄) is--定義部分--定義sql語(yǔ)句字符串v_sql varchar2(1000);--v_begin

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

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

rn>='||v_begin;--把游標(biāo)和sql語(yǔ)句關(guān)聯(lián)open p_cursor

for v_sql;--關(guān)閉游標(biāo)--計(jì)算myrows和myPageCount--組織了一個(gè)sql語(yǔ)句v_sql:='select count(*) from'||tablename;--執(zhí)行sql語(yǔ)句，并把返回值賦值給myrows;execute

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

myPageCount:=myrows/psize;else myPageCount:=myrows/psize+1;end if;--關(guān)閉游標(biāo)close p_cursor;end;

---案例:編寫(xiě)一個(gè)過(guò)程融师，可接受雇員的編號(hào)右钾，并顯示該雇員的名字--問(wèn)題是蚁吝，如果輸入的雇員的編號(hào)不存在旱爆，怎樣去處理。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 --定義一個(gè)例外myex exception;begin --更新用戶sal update emp

set sal=sal+1000 where empno=spNo; --sql%notfound這里表示沒(méi)有uodate成功 --raise myex觸發(fā)例外 if sql%notfound

then raise myex; end if; exception when myex then dbms_output.put_line('沒(méi)有任何用戶更新'); end;

---創(chuàng)建視圖窘茁，把emp表sal<1000的雇員映射到該視圖(view)--視圖創(chuàng)建以后怀伦，可以將視圖當(dāng)成一張普通的表create viewmyview as select * from emp where sal<1000;

--為了簡(jiǎn)化操作，用一個(gè)視圖解決山林，顯示雇員編號(hào)房待，姓名和部門(mén)名稱create view myview2 as selectemp.ename,emp.sal,dept.deptno from emp,dept where emp.deptno=dept.deptno;select* from myview2;

--視圖和視圖之間可以進(jìn)行聯(lián)合查詢-------------------------------student數(shù)據(jù)庫(kù)------------------------------------------

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,'課程設(shè)計(jì)好',123.52,00000001,'scott',sysdate,'scott',sysdate);SQL>insert

into course values(6300040,'課程設(shè)計(jì)好',121.32,00000002,'scott',sysdate,'scott',sysdate);SQL>insert

into course values(6300041,'課程設(shè)計(jì)好',173.52,00000003,'scott',sysdate,'scott',sysdate);SQL>insert

into course values(6300042,'課程設(shè)計(jì)好',123.52,00000004,'scott',sysdate,'scott',sysdate);SQL>insert

into course values(6300043,'課程設(shè)計(jì)好',123.52,00000005,'scott',sysdate,'scott',sysdate);SQL>insert

into course values(6300044,'課程設(shè)計(jì)好',121.32,00000006,'scott',sysdate,'scott',sysdate);SQL>insert

into course values(6300045,'課程設(shè)計(jì)好',173.52,00000007,'scott',sysdate,'scott',sysdate);SQL>insert

into course values(6300046,'課程設(shè)計(jì)好',123.52,00000008,'scott',sysdate,'scott',sysdate);SQL>insert

into course values(6300047,'課程設(shè)計(jì)好',123.52,00000009,'scott',sysdate,'scott',sysdate);SQL>insert

into course values(6300048,'課程設(shè)計(jì)好',121.32,00000010,'scott',sysdate,'scott',sysdate);SQL>insert

into course values(6300049,'課程設(shè)計(jì)好',173.52,00000011,'scott',sysdate,'scott',sysdate);SQL>insert

into course values(6300050,'課程設(shè)計(jì)好',123.52,00000012,'scott',sysdate,'scott',sysdate);SQL>insert

into course values(6300051,'課程設(shè)計(jì)好',123.52,00000013,'scott',sysdate,'scott',sysdate);SQL>insert

into course values(6300052,'課程設(shè)計(jì)好',121.32,00000014,'scott',sysdate,'scott',sysdate);SQL>insert

into course values(6300053,'課程設(shè)計(jì)好',173.52,00000015,'scott',sysdate,'scott',sysdate);SQL>insert

into course values(6300054,'課程設(shè)計(jì)好',123.52,00000016,'scott',sysdate,'scott',sysdate);

--------------創(chuàng)建一個(gè)過(guò)程create or replace procedure sp_pro01 isbegininsert into

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

insert into course values(6300040,'課程設(shè)計(jì)好',121.32,00000002,'scott',sysdate,'scott',sysdate);insert

into course values(6300041,'課程設(shè)計(jì)好',173.52,00000003,'scott',sysdate,'scott',sysdate);insert

into course values(6300042,'課程設(shè)計(jì)好',123.52,00000004,'scott',sysdate,'scott',sysdate);insert

into course values(6300043,'課程設(shè)計(jì)好',123.52,00000005,'scott',sysdate,'scott',sysdate);insert

into course values(6300044,'課程設(shè)計(jì)好',121.32,00000006,'scott',sysdate,'scott',sysdate);insert

into course values(6300045,'課程設(shè)計(jì)好',173.52,00000007,'scott',sysdate,'scott',sysdate);insert

into course values(6300046,'課程設(shè)計(jì)好',123.52,00000008,'scott',sysdate,'scott',sysdate);insert

into course values(6300047,'課程設(shè)計(jì)好',123.52,00000009,'scott',sysdate,'scott',sysdate);insert

into course values(6300048,'課程設(shè)計(jì)好',121.32,00000010,'scott',sysdate,'scott',sysdate);insert

into course values(6300049,'課程設(shè)計(jì)好',173.52,00000011,'scott',sysdate,'scott',sysdate);insert

into course values(6300050,'課程設(shè)計(jì)好',123.52,00000012,'scott',sysdate,'scott',sysdate);insert

into course values(6300051,'課程設(shè)計(jì)好',123.52,00000013,'scott',sysdate,'scott',sysdate);insert

into course values(6300052,'課程設(shè)計(jì)好',121.32,00000014,'scott',sysdate,'scott',sysdate);insert

into course values(6300053,'課程設(shè)計(jì)好',173.52,00000015,'scott',sysdate,'scott',sysdate);insert

into course values(6300054,'課程設(shè)計(jì)好',123.52,00000016,'scott',sysdate,'scott',sysdate);insert

into course values(6300055,'課程設(shè)計(jì)好',123.52,00000017,'scott',sysdate,'scott',sysdate);end;

--------------------執(zhí)行該過(guò)程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);--------------創(chuàng)建一個(gè)過(guò)程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;

--------------------執(zhí)行該過(guò)程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);

--------------創(chuàng)建一個(gè)過(guò)程create or replace procedure sp_pro03 isbegin insert into

stud values(80605301,'朱','浩橋','朱','湖北','100001','02887720301','西華大學(xué)',sysdate,'scott',sysdate,'sys',sysdate); insert into

stud values(80605302,'李','龍','李','吉林','100002','02887720302','西華大學(xué)',sysdate,'scott',sysdate,'system',sysdate); insert into

stud values(80605303,'祝','智發(fā)','祝','四川','100003','02887720303','西華大學(xué)',sysdate,'scott',sysdate,'sys',sysdate); insert into

stud values(80605304,'李','玥','李','重慶','100004','02887720304','西華大學(xué)',sysdate,'scott',sysdate,'system',sysdate); insert into

stud values(80605305,'宋','雪剿','宋','浙江','100005','02887720305','西華大學(xué)',sysdate,'scott',sysdate,'sys',sysdate); insert into

stud values(80605310,'杜','憶東','杜','四川','100003','02887720306','西華大學(xué)',sysdate,'scott',sysdate,'system',sysdate); insert into

stud values(80605311,'張','航','張','四川','100003','02887720307','西華大學(xué)',sysdate,'scott',sysdate,'scott',sysdate); insert into

stud values(80605312,'莫','逸楊','莫','四川','100003','02887720308','西華大學(xué)',sysdate,'scott',sysdate,'system',sysdate); insert into

stud values(80605314,'戴','子儀','戴','湖北','100001','02887720309','西華大學(xué)',sysdate,'scott',sysdate,'scott',sysdate); insert into

stud values(80605315,'楊','勇','楊','吉林','100002','02887720310','西華大學(xué)',sysdate,'scott',sysdate,'system',sysdate); insert into

stud values(80605316,'郭','榮華','郭','四川','100003','02887720313','西華大學(xué)',sysdate,'scott',sysdate,'sys',sysdate); insert into

stud values(80605317,'楊','銀輝','楊','重慶','100004','02887720314','西華大學(xué)',sysdate,'scott',sysdate,'system',sysdate); insert into

stud values(80605318,'李','昆霖','李','浙江','100005','02887720315','西華大學(xué)',sysdate,'scott',sysdate,'scott',sysdate); insert into

stud values(80605319,'趙','虎','趙','四川','100003','02887720316','西華大學(xué)',sysdate,'scott',sysdate,'system',sysdate); insert into

stud values(80605320,'黃','友志','黃','四川','100003','02887720317','西華大學(xué)',sysdate,'scott',sysdate,'sys',sysdate); insert into

stud values(80605321,'陳','光春','陳','四川','100003','02887720318','西華大學(xué)',sysdate,'scott',sysdate,'system',sysdate);

end;

--------------------執(zhí)行該過(guò)程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);

--------------創(chuàng)建一個(gè)過(guò)程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;

--------------------執(zhí)行該過(guò)程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);--------------創(chuàng)建一個(gè)過(guò)程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,'嚴(yán)','常龍','嚴(yán)','上海','100004','02887720014','scott',sysdate,'sys',sysdate);end;

--------------------執(zhí)行該過(guò)程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章游標(biāo)簡(jiǎn)介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;-------創(chuàng)建一個(gè)基于表的游標(biāo)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;

--------------------------------時(shí)尚購(gòu)物網(wǎng)------------------------------------------drop database

testdbgocreate database testdb;gouse testdb;go---存儲(chǔ)過(guò)程create Sequencesequ_stationinfo_idincrement by 1start with 1nomaxvaluenocyclecache

-------------------創(chuàng)建一個(gè)序列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,--真實(shí)姓名passwd varchar2(30) not null,--密碼email varchar2(40) not null,--電子郵件phone varchar2(20) not null, --電話號(hào)碼address varchar2(30) not null, --用戶地址postcode char(6) not null,--郵編grade int default 5 --用戶的級(jí)別)

--管理員表admin

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

identity(1,1),--貨物idgoodsName varchar2(40) ,--名稱goodsIntro varchar2(500),--介紹goodsPrice float

,--價(jià)格goodsNum int ,--數(shù)量publisher varchar2(40),--發(fā)行商photo

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

--直接向數(shù)據(jù)庫(kù)添加貨物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','香港電影')

-----------------存儲(chǔ)過(guò)程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;------------------------執(zhí)行存儲(chǔ)過(guò)程sql>exec sp_pro18;

--向用戶表中初始化一個(gè)用戶，將來(lái)是通過(guò)注冊(cè)界面加入的

insert into

users values('shunping','韓順平','shunping','hanshunping@tsinghua.org.cn','010-88888888','星星小區(qū)哈哈樓嘻嘻單元123房間','123456',1);

-------------------創(chuàng)建一個(gè)序列create sequence seqmax1 increment by 1;-----------------創(chuàng)建一個(gè)存儲(chǔ)過(guò)程create or replace procedure sp_pro19 isbegin insert into

users values(seqmax1.nextval,'shunping','王紅祥','shunping','wang010366@vip.qq.com','028-87720557','星星小區(qū)哈哈樓嘻嘻單元123房間','123456',1); insert into users

values(seqmax1.nextval,'whx','whx','wxh','wang010366@vip.qq.com','028-87720033','星星小區(qū)哈哈樓嘻嘻單元123房間','123456',1)end;

---------執(zhí)行存儲(chǔ)過(guò)程sql>exec sp_pro19;sql>

--學(xué)生可能這樣設(shè)計(jì)訂單表(錯(cuò)誤)--create table orders(ordersId bigint

primary key identity(1,1),--訂單號(hào)userId bigint constraint fk_client_id

references users(userid),--哪個(gè)用戶訂的goodsId bigint constraint fk_shangpin_id

references goods(goodsId),--商品號(hào)nums int not null,--數(shù)量orderDate datetime default getdate(),--下訂單的時(shí)間payMode varchar2(20)check (payMode in('貨到付款','支付寶付款')) default '貨到付款',--付款的方式isPayed bit check ( isPayed in (0

,1)),--(0,表示還沒(méi)有付款 1:表示已經(jīng)付款了)totalPrice

float not null,--總價(jià)格)

--應(yīng)該這樣去設(shè)計(jì)訂單表create table orders( ordersId number(10) primary key,--訂單號(hào) userId number(10) constraint fk_client_id references

users(userid),--哪個(gè)用戶訂的 orderDate datetime default getdate(),--下訂單的時(shí)間 payMode varchar2(20) check (payMode in('貨到付款','支付寶付款')) default '貨到付款',--付款的方式 isPayed bit check ( isPayed in (0

,1)),--(0,表示還沒(méi)有付款 1:表示已經(jīng)付款了) totalPrice

float not null--總價(jià)格)

--訂單細(xì)節(jié)表create table orderDetail( ordesIid bigint constraint

fk_order_id references orders(ordersId),--訂單號(hào)(并是一個(gè)外鍵) 指向orders表的主鍵 goodsId bigint

constraint fk_shangpin_id references goods(goodsId),--商品號(hào)(并是一個(gè)外鍵) 指向goods表的主鍵 nums int not null--數(shù)量)

--顯示一下各個(gè)表的初始化信息select * from users;select * fromgoods;select * from orders

select * fromorderDetail

-------Hibernate

HelloWorld---------------------------建立新java 項(xiàng)目,名為hibernate_0100_HelloWorld--學(xué)習(xí)建User-library-hibernate,并加入相應(yīng)的jar包--項(xiàng)目右鍵-buildpath-configure build path-add

library—--選擇User-library,在其中新建 libraray,命名為 hibernate--在該library中加入hibernate所需jar包--hibernate

core--/required--slf-nop jar--引入mysql的JDBC驅(qū)動(dòng)包--在mysql中建立對(duì)應(yīng)的數(shù)據(jù)庫(kù)以及表--create

database hibernate驼抹；--use hibernate桑孩；--create table Student (id int primary key,

namevarchar(20), age int)；--建立hibernate 配置文件hibernate.cfg.xml--從參考文檔中copy--修改對(duì)應(yīng)的數(shù)據(jù)庫(kù)連接--注釋掉暫時(shí)用不上的內(nèi)容--建立Student 類--建立Student 映射文件 Student.hbm.xml--參考文檔--將映射文件加入到hibernate.cfg.xml中--參考文檔--寫(xiě)測(cè)試類Main,在Main中對(duì)Student對(duì)象進(jìn)行直接的存儲(chǔ)測(cè)試 --參考文擋--FAQ：--要調(diào)用 new

Configuration().configure().buildSessionFactory(),而不是-- 要省略 configure框冀，否則會(huì)出 hibernate

dialect must be set 的異常--Note:--請(qǐng)務(wù)必建立自己動(dòng)手査文擋的能力--重要的是:--要建立自己動(dòng)手查一手文檔的信心--還有建立自己動(dòng)手查一手文檔的習(xí)慣流椒！--主動(dòng)學(xué)習(xí)，砍棄被動(dòng)接受灌輸?shù)牧?xí)慣明也！--建立能力--錯(cuò)誤讀完整--讀—昔誤的關(guān)鍵行--排除法--比較法--google

-----------------建立 Annotation 版本的HelloWorld---------------------創(chuàng)建teacher 表宣虾，sql>create

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

varchar2(10));--創(chuàng)建Teacher 類--在hibernate lib 中加入annotation的jar包--hibernate annotaion jar--ejb3

persistence jar--hibernate common-annotations.jar--注意文襠中沒(méi)有提到hibernate-common-annotations.jar 文件--參考Annotaion文檔建立對(duì)應(yīng)的注解--在hibernate.cfg.xml中建立映射

class:.../〉--參考文襠進(jìn)行測(cè)試（注意文襠中缺少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;--編寫(xiě)一個(gè)select語(yǔ)句，以便于使用user_source視圖來(lái)顯示discount過(guò)程的源代碼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;

--測(cè)試id_is_good函數(shù)的一個(gè)方法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;------------------------------------------------------------------------------第一步創(chuàng)建包頭SQL>create or replace package school_api as procedure

discount_cost; function new_instructor_id return instructor.instructor_id%type;

end school_api;---第二步實(shí)現(xiàn)包體--------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--第一步創(chuàng)建包頭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;---第二部創(chuàng)建包體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--第一步創(chuàng)建包頭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;--第二部創(chuàng)建包體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;

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);---------第二步插入數(shù)據(jù)温数，建立過(guò)程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','一汽','長(zhǎng)春'); insert into J values('J3','彈簧廠','天津'); insert into J values('J4','造船廠','天津'); insert into J values('J5','機(jī)車廠','唐山'); insert into J values('J6','無(wú)線電廠','常州'); insert into J values('J7','半導(dǎo)體廠','南京');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','螺絲刀','藍(lán)',14); insert into P values('P4','螺絲刀','紅',14); insert into P values('P5','凸輪','藍(lán)',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;

---------------------------------------第五步绣硝，完成作業(yè)--1.找出所有供應(yīng)商的姓名和所在城市SQL>select

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

PNAME,COLOR,WEIGHT from P;--3.找出使用供應(yīng)商S1所供應(yīng)零件的工程號(hào)碼SQL>select

distinct JNO from SPJ where SNO='S1' order by JNO;--4.找出工程項(xiàng)目J2使用的各種名稱及其數(shù)量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.找出上海廠商供應(yīng)的所有零件號(hào)碼SQL>select distinct SPJ.PNO from SPJ

where SPJ.SNO in (select S.SNO from S where S.CITY='上海');--6.找出使用上海產(chǎn)的零件的工程名稱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.找出沒(méi)有使用天津廠的零件的工程號(hào)碼SQL>select distinct SPJ.JNO from SPJ

where SPJ.SNO in(select S.SNO from S where S.CITY!='天津')---8.把全部紅色零件的顏色改成藍(lán)色SQL>update P

set P.COLOR='藍(lán)'where P.COLOR='紅';--9.由S5供應(yīng)J4的零件P6改成S3供應(yīng)鹉胖，請(qǐng)做必要的修改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.從供應(yīng)商關(guān)系中刪除S2的記錄，并從供應(yīng)情況關(guān)系中刪除對(duì)應(yīng)的記錄SQL>deletefrom SPJ where SPJ.SNO='S2';SQL>delete from S where S.SNO='S2';

---請(qǐng)將(S2,J6,P4,200)插入供應(yīng)情況關(guān)系SQL>insertinto SPJ values('S2','J6','P4',200);

---標(biāo)準(zhǔn)答案--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='藍(lán)' 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 尚未在此會(huì)話中定義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-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------第四次作業(yè)---創(chuàng)建兩張表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 );

----------------------------------------------------------------------------------------------------------------------------------創(chuàng)建用戶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;--創(chuàng)建過(guò)程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.用戶王明對(duì)兩張表有select權(quán)力SQL>grant select on

employer,department to wangming;--b.用戶李勇對(duì)兩張表有insert和delete權(quán)力SQL>grant insert,delete on

employer,department to liyong;--c.每個(gè)職工對(duì)自己的記錄有select權(quán)力SQL>grant select on employer where empname=user to

public;--d.用戶劉星對(duì)職工表有select權(quán)力够傍，對(duì)工資字段具有更新權(quán)力SQL>grant

select,update(empsalary) on employer to liuxing; --e.用戶張新具有修改這兩張表的結(jié)構(gòu)的權(quán)力SQL>grant alter on

employer,department to zhangxin;--f.用戶周平具有對(duì)兩張表所有權(quán)利(讀甫菠、插、改王带、刪數(shù)據(jù)),并具有給其他用戶授權(quán)的權(quán)力SQL>grant all

privileges on employer,department to zhouping with grant option;--g.用戶楊蘭具有從每個(gè)部門(mén)職工中select最高工資淑蔚，最低工資，平均工資的權(quán)力,他不能查看每個(gè)人的工資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;

---撤銷個(gè)用戶所授予的權(quán)力--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;

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------標(biāo)準(zhǔn)答案 --a 愕撰、用戶王明對(duì)兩個(gè)表有SELECT 權(quán)力刹衫。 SQL>GRANT SELECT ON 職工,部門(mén) TO 王明 --b 醋寝、用戶李勇對(duì)兩個(gè)表有INSERT 和DELETE 權(quán)力。 SQL>GRANT INSERT,DELETE ON 職工,部門(mén) TO 李勇 --c 带迟、每個(gè)職工只對(duì)自己的記錄有SELECT 權(quán)力音羞。 SQL>GRANT

SELECT ON 職工 WHEN USER()=NAME TO ALL; --d 、用戶劉星對(duì)職工表有SELECT 權(quán)力仓犬，對(duì)工資字段具有更新權(quán)力嗅绰。 SQL>GRANT

SELECT,UPDATE(工資) ON 職工 TO 劉星 --e 、用戶張新具有修改這兩個(gè)表的結(jié)構(gòu)的權(quán)力搀继。 SQL>GRANT

ALTER TABLE ON 職工,部門(mén) TO 張新; --f 窘面、用戶周平具有對(duì)兩個(gè)表所有權(quán)力（讀，插叽躯，改财边，刪數(shù)據(jù)），并具有給其他用戶授權(quán)的權(quán)力点骑。 SQL>GRANT

ALL PRIVILIGES ON 職工,部門(mén) TO 周平 WITH GRANT OPTION; --g酣难、用戶楊蘭具有從每個(gè)部門(mén)職工中SELECT 最高工資、最低工資黑滴、平均工資的權(quán)力憨募，他不能查看每個(gè)人的工資。 SQL>CREATE VIEW 部門(mén)工資 AS SELECT 部門(mén).名稱,MAX(工資),MIN(工資),AVG(工資) FROM 職工,部門(mén) WHERE 職工.部門(mén)號(hào)=部門(mén).部門(mén)號(hào) GROUP BY 職工.部門(mén)號(hào) GRANT SELECT ON 部門(mén)工資 TO 楊蘭; --9 ．把習(xí)題8 中（1）---（7）的每一種情況袁辈，撤銷各用戶所授予的權(quán)力 --1

SQL>REVOKE SELECT ON 職工菜谣，部門(mén) FROM 王明;--2

SQL>REVOKE INSERT , DELETE ON 職工，部門(mén) FROM 李勇;--3SQL>REOVKE SELECT ON 職工 WHEN USER ( ) =NAME FROM ALl; --4SQL>REVOKE SELECT ,

UPDATE ON 職工 FROM 劉星;

--5SQL>REVOKE ALTER TABLE ON 職工,部門(mén) FROM 張新; --6 SQL>REVOKE ALL PRIVILIGES ON 職工,部門(mén) FROM 周平; --7

SQL>REVOKE SELECT ON 部門(mén)工資 FROM 楊蘭; SQL>DROP

VIEW 部門(mén)工資;

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觸發(fā)器-----定義觸發(fā)器SQL>create trigger <觸發(fā)器名> {before|after} <觸發(fā)事件> on <表名> for each {row|statement} [when <觸發(fā)條件>] <觸發(fā)動(dòng)作體>

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次作業(yè)--假設(shè)有下面的關(guān)系模式：

-- 科室（科名吵瞻，科地址葛菇，科電話）-- 病房（病房號(hào)，床位號(hào)橡羞，科室名）

-- 醫(yī)生（工作證號(hào)眯停，姓名，職稱卿泽，科室名莺债，年齡）

-- 病人（病歷號(hào)，姓名签夭，性別齐邦，診斷，主管醫(yī)生第租，病房號(hào)）

--其中措拇，一個(gè)科室有多個(gè)病房、多個(gè)醫(yī)生慎宾，一個(gè)病房只能屬于一個(gè)科室丐吓，一個(gè)醫(yī)生只屬于一個(gè)科室浅悉，--但可負(fù)責(zé)多個(gè)病人的診治，一個(gè)病人的主管醫(yī)生只有一個(gè)券犁。使用SQL語(yǔ)言定義上面的關(guān)系模式术健，--要求在模式中使用完整性約束命名子名完成以下完整性約束命名條件的定義：

--1）定義每個(gè)模式的主碼；

--2）定義相關(guān)關(guān)系模式的實(shí)體完整性和參照完整性粘衬；

--3）定義病人的性別只能是”男”或”女”荞估；

--4）定義醫(yī)生的年齡只能在20到80歲之間；

--5）對(duì)病人表定義創(chuàng)建一個(gè)after行級(jí)觸發(fā)器稚新，當(dāng)進(jìn)行插入操作時(shí)未指定主管醫(yī)生時(shí)勘伺，自動(dòng)改為王醫(yī)生。

-- 科室（科名枷莉，科地址娇昙，科電話）SQL>create table 科室( 科名 varchar2(20) primary key, 科地址 varchar2(20) not null unique, 科電話 varchar2(20) not null unique );-- 病房（病房號(hào)尺迂，床位號(hào)笤妙，科室名）SQL>create table 病房( 病房號(hào) varchar2(20) primary key, 床位號(hào) numeric(2) not null unique, 科室名 varchar2(20) not null unique, constraint c11 foreign

key(科室名) references 科室(科名) );-- 醫(yī)生（工作證號(hào)，姓名噪裕，職稱蹲盘，科室名，年齡）SQL>create

table 醫(yī)生( 工作證號(hào) 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) );-- 病人（病歷號(hào)膳音，姓名召衔，性別，診斷祭陷，主管醫(yī)生苍凛，病房號(hào)）SQL>create table 病人( 病歷號(hào) varchar2(20) primary key, 姓名 varchar2(20) not null, 性別 char(2) default

'男' check (性別 in ('男','女')), 診斷 varchar2(255)

not null, 主管醫(yī)生 varchar2(10) not null, constraint c31

foreign key(主管醫(yī)生) references 醫(yī)生(姓名), 病房號(hào) varchar2(20)

not null, constraint c41 foreign key(病房號(hào)) references 病房(病房號(hào)) );

--5）對(duì)病人表定義創(chuàng)建一個(gè)after行級(jí)觸發(fā)器，當(dāng)進(jìn)行插入操作時(shí)未指定主管醫(yī)生時(shí)兵志，自動(dòng)改為王醫(yī)生醇蝴。SQL>create or

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

begin if new.主管醫(yī)生 is null then new.主管醫(yī)生='王醫(yī)生'; end if; end;

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者

人面猴
序言：七十年代末秧倾，一起剝皮案震驚了整個(gè)濱河市魔种，隨后出現(xiàn)的幾起案子，更是在濱河造成了極大的恐慌惫周，老刑警劉巖按价，帶你破解...
沈念sama閱讀 206,126評(píng)論 6贊 481
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件惭适，死亡現(xiàn)場(chǎng)離奇詭異，居然都是意外死亡楼镐，警方通過(guò)查閱死者的電腦和手機(jī)癞志，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 88,254評(píng)論 2贊 382
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進(jìn)店門(mén)，熙熙樓的掌柜王于貴愁眉苦臉地迎上來(lái)框产，“玉大人凄杯，你說(shuō)我怎么就攤上這事师溅。” “怎么了盾舌？”我有些...
開(kāi)封第一講書(shū)人閱讀 152,445評(píng)論 0贊 341
道士緝兇錄：失蹤的賣(mài)姜人
文/不壞的土叔我叫張陵墓臭，是天一觀的道長(zhǎng)。經(jīng)常有香客問(wèn)我妖谴，道長(zhǎng)窿锉，這世上最難降的妖魔是什么？我笑而不...
開(kāi)封第一講書(shū)人閱讀 55,185評(píng)論 1贊 278
?港島之戀（遺憾婚禮）
正文為了忘掉前任膝舅，我火速辦了婚禮嗡载，結(jié)果婚禮上，老公的妹妹穿的比我還像新娘仍稀。我一直安慰自己洼滚，他們只是感情好，可當(dāng)我...
茶點(diǎn)故事閱讀 64,178評(píng)論 5贊 371
惡毒庶女頂嫁案：這布局不是一般人想出來(lái)的
文/花漫我一把揭開(kāi)白布技潘。她就那樣靜靜地躺著遥巴，像睡著了一般。火紅的嫁衣襯著肌膚如雪享幽。梳的紋絲不亂的頭發(fā)上铲掐，一...
開(kāi)封第一講書(shū)人閱讀 48,970評(píng)論 1贊 284
城市分裂傳說(shuō)
那天，我揣著相機(jī)與錄音值桩，去河邊找鬼摆霉。笑死，一個(gè)胖子當(dāng)著我的面吹牛奔坟，可吹牛的內(nèi)容都是我干的携栋。我是一名探鬼主播，決...
沈念sama閱讀 38,276評(píng)論 3贊 399
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開(kāi)眼咳秉，長(zhǎng)吁一口氣：“原來(lái)是場(chǎng)噩夢(mèng)啊……” “哼婉支！你這毒婦竟也來(lái)了？” 一聲冷哼從身側(cè)響起滴某，我...
開(kāi)封第一講書(shū)人閱讀 36,927評(píng)論 0贊 259
萬(wàn)榮殺人案實(shí)錄
序言：老撾萬(wàn)榮一對(duì)情侶失蹤磅摹，失蹤者是張志新（化名）和其女友劉穎，沒(méi)想到半個(gè)月后霎奢，有當(dāng)?shù)厝嗽跇?shù)林里發(fā)現(xiàn)了一具尸體户誓，經(jīng)...
沈念sama閱讀 43,400評(píng)論 1贊 300
?護(hù)林員之死
正文獨(dú)居荒郊野嶺守林人離奇死亡，尸身上長(zhǎng)有42處帶血的膿包…… 初始之章·張勛以下內(nèi)容為張勛視角年9月15日...
茶點(diǎn)故事閱讀 35,883評(píng)論 2贊 323
?白月光啟示錄
正文我和宋清朗相戀三年幕侠，在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了帝美。大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片。...
茶點(diǎn)故事閱讀 37,997評(píng)論 1贊 333
活死人
序言：一個(gè)原本活蹦亂跳的男人離奇死亡晤硕，死狀恐怖悼潭，靈堂內(nèi)的尸體忽然破棺而出庇忌，到底是詐尸還是另有隱情，我是刑警寧澤舰褪，帶...
沈念sama閱讀 33,646評(píng)論 4贊 322
?日本核電站爆炸內(nèi)幕
正文年R本政府宣布皆疹，位于F島的核電站，受9級(jí)特大地震影響占拍，放射性物質(zhì)發(fā)生泄漏略就。R本人自食惡果不足惜，卻給世界環(huán)境...
茶點(diǎn)故事閱讀 39,213評(píng)論 3贊 307
男人毒藥：我在死后第九天來(lái)索命
文/蒙蒙一晃酒、第九天我趴在偏房一處隱蔽的房頂上張望表牢。院中可真熱鬧，春花似錦贝次、人聲如沸崔兴。這莊子的主人今日做“春日...
開(kāi)封第一講書(shū)人閱讀 30,204評(píng)論 0贊 19
一樁弒父案蛔翅，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽(yáng)敲茄。三九已至，卻和暖如春搁宾，著一層夾襖步出監(jiān)牢的瞬間折汞，已是汗流浹背。一陣腳步聲響...
開(kāi)封第一講書(shū)人閱讀 31,423評(píng)論 1贊 260
情欲美人皮
我被黑心中介騙來(lái)泰國(guó)打工盖腿，沒(méi)想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留，地道東北人损同。一個(gè)月前我還...
沈念sama閱讀 45,423評(píng)論 2贊 352
代替公主和親
正文我出身青樓翩腐，卻偏偏與公主長(zhǎng)得像，于是被迫代替她去往敵國(guó)和親膏燃。傳聞我的和親對(duì)象是個(gè)殘疾皇子茂卦，可洞房花燭夜當(dāng)晚...
茶點(diǎn)故事閱讀 42,722評(píng)論 2贊 345

摘錄的一些知識(shí)點(diǎn)

推薦閱讀更多精彩內(nèi)容