PLSQL学习笔记1.docx
《PLSQL学习笔记1.docx》由会员分享，可在线阅读，更多相关《PLSQL学习笔记1.docx（61页珍藏版）》请在冰豆网上搜索。
PLSQL学习笔记1
startd:
\a.sql
@d:
\a.sql
edit[d:
\a.sql]
spoold:
\a.sql将屏上内容输出到指定文件
spooloff
setlinesize320
setpagesize100
setautoprinton
setserveroutputon
variablev1refcursor
expimp备份恢
只读事务
settransactionreadonly
用处，设置只读事务后，其他用户提交的事务在这里不可见，用处就是用于统计，但又不想取得统计时发生的事务提交
用户管理:
登陆：
sqlplusscott/tiger
sqlplussystem/rootassysdba
systemsyssysdbascott;;sys超级管理员，具有角色dba;system是系统管理员，角色dbaoper,比sys低一级，没有createdatabase权限
创建用户createuserjixiufidentifiedbyjixiuf_passwd;
createuseruserNameidentifiedbyyourPasswroddefaulttablespaceuserstemporarytablespacetempquota50Monusersquota400Kontemp;
createuseruserNameidentifiedbyyourPasswroddefaulttablespacets1temporarytablespacets2unlimitedonts1;
切换用户connsystem/root;disconnect
显示当前用户showuser
更改密码passworduserName
删除用户:
dropuserjixiuf[cascade]，如果jixiuf用户已经创建过一些表，加cascade级联删除
权限分系统权限和对象权限，系统权限是用户对数据库的控制权，对象权限是用户对其它用户所拥有数据对象的操作权限
对象权限：
如select,updatedelete,createindex
系统权限：
如createsession即连接到数据库
grant[系统特权名][角色]to[用户名列表][public][withadminoption]
grantconnecttojixiuf;角色connect赋予jixiuf此用户可以连接到数据库connect,resource,dba三个重要角色,拥有resource可以在表空间建表，grantresourcetojixiuf
授权：
grantselectontableNametojixiuf[withgrantoption];具有了select*fromuserName.tableName
grantselect,update,deleteonuserName.tableNametojixiuf;
grantallonuserName.tabletojixiuf;增删改查权
如果是对象权限可以加withgrantoption，
若是系统权限则带withadminoption如:
grantconnnecttojixiufwithadminoption;
收回权限：
revokeselectonempfromjixiuf;
如若加了withgrantoption则revoke级联收回其他人的权限，withadminoption好像不收回
建立角色：
createroler1[notidentified];常用
createroler2[identifiedbypassword];
角色授权
系统权限
grantcreatesessiontor1[withadminoption];
grantconectontor1;把connect角色的权限copy一份给r1;select*fromROLE_ROLE_PRIVS;
对象权限
GRANTSELECTONSCOTT.EMPTOR1;
数据字典：
SELECT*FROMDICTWHERETABLE_NAMELIKE'%ROLE%';
使用profile管理用户口令，profile是口令限制，资源限制的命令集合，当建立数据库时，oracle会自动建立名称为default的profile,当建立用户没有指定profile，则默认用此项分配给用户
（1）帐户锁定：
指定用户登陆时最多可以输入口令的次数，指定锁定时间，用dba身份执行此命令
createprofileprofile_name_lock_userlimitfailed_login_attempts3password_lock_time2;
最多尝试3次，3次登陆不成功则不能继续登陆，不成功后允许下次登陆时间为2天后
alteruserjixiufprofileprofile_name_lock_user;
createuserjixiufidentifiedbyjixiudfprofileprofile_name_lock_user;
解锁：
alteruserjixiufacoountunlock;
定期修改密码
createprofilechange_passwordlimitpassword_life_time10password_grace_time2
一个密码用10天后必须修改，宽限期2天，这两天会提示用户修改密码
口令历史：
用户不能使用以前用过的密码
createprofilepassword_historylimitpassword_life_time10password_grace_time2password_reuse_time15;15天后可以重用以前的密码
删除profile
dropprofilepassword_history；对用户作的限制作废
connsystem/root
shutdown;
ORA-01031:
insufficientprivileges
connsystem/rootassysdba只有作为sysdba登陆时才有startupshutdown权限
shutdown;关闭数据库
startup;启动数据库
---------------------------------备份与恢复-----------------------------------------------------
备份与恢复（导入导出）
导出:
三类,导出表，导出方案（一个用户对应一个方案），导出数据库用exp命令
在导入和导出的时候要用到C:
\oracle\product\10.1.0\Db_1\BIN\exp.exe
exphelp=y有帮助提示
（1）导出表（也可以直接输入exp命令，以交互式进行备份）
1导出自已的表：
expuserid=scott/tiger@orcltables=（tableName1,tableName2）file=d:
\tableName.dmp;
2导出别人的表
expuserid=scott/tiger@orcltables=（userName.tableName1,userName.tableName2）file=d:
\tableName.dmp;
3导出表结构，（加一个rows=n）nmeansno
expuserid=scott/tiger@orcltables=（userName.tableName1,userName.tableName2）file=d:
\a.dmprows=n
4直接导出方式:
比常规导出速度快（加一个direct=y,专门用于导出大表）
expuserid=scott/tiger@orcltables=（userName.tableName1,userName.tableName2）file=d:
\a.dmpdirect=y
（2）导出方案
1导出自己方案
expuserid=scott/tiger@orclowner=scottfile=d:
\scott.dmp
2导出其他的人方案
expuserid=system/root@orclowner=（system,scott）file=d:
\scott.dmp
（3）导出数据库（须具有dba权限，或者exp_full_database权限full=yinctype=complete增量备份（第一次complete）
expuserid=system/root@orclfull=yinctype=completefile=d:
\scott.dmp
导入imp（选项：
useridtablesfromusertouserfile=d:
\a.dmpfull=yinctype=complete增量备份rows=n不导入数据ignore=y若表存在则只导数据）
1导入表
（1）导入自已的表
impuserid=scott/tiger@orclfile=d:
\scott.dmptables=emp;
impuserid=system/root@orclfile=d:
\d.dmpfromuser=scotttouser=jixiuftables=emp;
把scott.emp导入到用户jixiuf名下（前提是emp没有外键关联到其他表，否则，因为它关联的表并不在jixiuf中，1法实现主外键关联）
（2）只导入表的结构
impuserid=scott/tiger@orcltables=（emp）file=d:
\scott.dmprows=n
（3）导入数据：
impuserid=scott/tiger@orcltables=（emp）file=d:
\scott.dmpignore=y
2导入方案
（1）导入自身方案
impuserid=scott/tigerfile=d:
\scott.dmp
（2）导入他人方案
impuserid=system/rootfromuser=scotttouser=jixiuffile=d:
\scott.dmp
（3）导入数据库
impuserid=system/rootfull=yfile=d:
\scott.dmp
注意导入的数据可能会与已有的数据重复（如果原来的数据没丢失，却运行了导入一次命令则可能数据重复，慎!
!
!
）
-------------------------数据字典-----------------------------------------------------
user_xxx,all_xxx,dba_xxx如user_tablesdba_roles
dba_users,dba_sys_privsdba_tab_privsdba_col_privsdba_role_privs
selectusername,user_id,passwordfromdba_users;查用户的信息
select*fromdba_role_privswheregrantee='JIXIUF';查jixiuf所具有的role
select*fromdba_roles查oracle具有的role
查一个角色具有的权限（系统权限，对象权限）
descdba_sys_privs
select*fromdba_sys_privswheregrantee='CONNECT';或者select*fromrole_sys_privswhererole='CONNECT'后者以assysdba连接，才可以显示全，？
？
？
select*fromdba_tab_privswheregrantee='RESOURCE';
数据字典的数据字典dict
select*fromdictwherecommontslike'%TABLES%'
SELECT*FROMGLOBAL_NAME;查询当前使用的数据库orcl
----------------表空间-----------------------------------
段区块
createtablespacetsName1datafile'd:
\a.dbf'size20muniformsize128k大小20M区的大小128k
createtablet（idint）tablespacetsName1;
select*fromall_tableswheretablespace_name='TSNAME1';
表空间状态,onlineoffline联机（可读写），脱机（不可读写，系统维护）只读表空间
altertablespacetsName1offline
altertablespacetsName1readonly;
altertablespacetsName1readwrite;
删除表空间
droptablespacetsname1[includingcontents[anddatafiles]]
扩展表空间
1增加数据件
altertablespacetsname1adddatafile'd:
\b.dbf'size10M
2增加datafile的大小
altertablespacetsname1'd:
\b.dbf'resize30M（?
?
?
?
?
）
3设置file自动增长
altertablespacetsname1'd:
\a.dbf'autoextendonnext10mmaxsize500m
移动datafile（磁盘损坏，但datafile区域未坏，可移而用之）
1selecttablespace_namefromdba_data_fileswherefile_name='D:
\A.DBF';
tableSpaceName1
2altertablespacetableSpaceName1offline
3hostmoved:
\a.dbfc:
\a.dbf
4altertablespacetableSpaceName1renamedatafile'd:
\a.dbf'to'c:
\a.dbf'
taltertablespacetableSpaceName1online
相应数据字典：
dba_tablespacesdba_data_files
索引index------------------------------------------------------------------------------------
1键压缩index
--因job列有很多重复信息（即很多人的job是同一类型的），于是普通的索引就会导致job重复生成索引
为此可以压缩（job，name）以节省空间，即同一个job只建一个（无重复现象），而后即的name共享前缀项job，整个（job，name）索引可以节省compress表示压缩，而1表示压缩（job,name）第一项，即job项
createindexidxemponemp（job,ename）compress1;
2分区索引（索引存储在不同的分区）
据表是否分区，分为
2.1本地索引（本地前缀索引，本地无前缀索引）
2.2全局索引（基于整个表建索引）
簇cluster--------------------------------------------------------------------
有公共列的两个或多个表的集合（存储两个表的重复列）减少io节省空间，插入数据慢
簇表中的数据存储在公共数据块中（如有主外键关系的表）
簇键：
簇中的唯一标识符，用于获取行
先建簇，后建组成簇的表
--公共字段可以不只一个
createclusterclass_cluster（classNonumber）tablespaceusers;
为簇建索引
createindexcluster_index_classonclusterclass_cluster;
--表示classes表的classNo_字段存储到class_cluster中
createtableclasses（classNo_number,classNamevarchar2（22））clusterclass_cluster（classNo_）;
createtablestudent（studentNamevarchar2（22）,studentNonumber,classNonumber）clusterclass_cluster（classNo）;
以上两个表的classNoclassNo_其实都是class_cluster的
---------------------------------------------------------------------------------------------
添加字段
altertablestudentadd（desc_varchar2（20））;
修改字段长度:
altertablestudentmodify（description_varchar2（300））;
删除一个字段
altertablestudentdropcolumncol_name;
修改表名;
renamestudnttostu;
修改日期格式
altersessionsetnls_date_format='yyyy-mm-dd';
添加空值
insertintostudvalues（1,null）;
更新
updatestudentsetsex='nu',name=''wherexh='';
删除
deletefromstudent
droptablestudent
truncatetablestudent,不写日志
altertableempaddconstraintpk_p1primarykey（id）;
altertableempdropconstraintpk_p1;
回滚
savepointa;
deletefromstudnet;
rollbacktoa;
字符合并两个竖线
select'姓名：
'||namefromemp;
字符函数
lower（）upper（）substr（str,pos,len）replace（str,oldStr,newStr）
null-->default如果comm为null则以0为默认值
selectnvl（comm,0）fromemp;
日期函数：
selectcurrent_date,sysdatefromdual;
select*fromempwheresysdate>add_months（hiredate,8）查八个月以前的员工入职的
selectsysdate-hiredateas入职天数fromemp;
当月最后一天
selecthiredate,last_day（hiredate）fromemp;
selectto_char（hiredate,'yyyy-mm-ddhh24:
mi:
ss'）fromemp;
updateempsethiredate=to_date（'1988-09-09','yyyy-mm-dd'）;
当前使用的数据库名：
selectsys_context（'USERENV','db_name'）FROMDUAL;
当前使用的语言
selectsys_context（'USERENV','language'）fromdual;
selectsys_context（'USERENV','session_user'）fromdual;
selectsys_context（'USERENV','current_schema'）fromdual;
-----------------------------------------------------------------------------------------
------------------------------ps/sql-----------------------------------------------
procedure---------------------------------------------------------------------------
可以用desc查一个procedure
descsp_pro1;
可以在procedure中使用return，结束此procedure
user_source表中有更详细的信息
selecttextfromuser_sourcewherename='SP_PRO1';
pl/sql以块为单位
-----------------------------
--注意，procedure的名称是sp_pro1如果有参数，则声明如同
--createorreplaceproceduresp_pro1（namevarchar2）is
--无参数时加上括号好像编译不通过
createorreplaceproceduresp_pro1is
--此处不需要declare关键字
v_var_namevarchar2（255）;
begin
insertintoscott.tvalues
（1）;
end;
--注意end后的分号
/
--输入斜杠完成
--调用callsp_pro1（）;或者execsp
升级会员 