oracle客户端配置(oracle客户端配置tns)

　　1、Oracle默认账户、密码 sys change_on_install system manger scott tiger

　　2、Oracle服务说明 OracleServiceORCL：数据库的服务，如果创建两个数据库就会有两个服务，ORCL是数据库名。 OracleOraDb10g_home1TNSListener：监听服务，jdbc和plsql远程连接服务，端口号为：1521 OracleOraDb10g_home1iSQL*Plus：Web管理服务，例如：https://127.0.0.1:5560/isqlplus，端口号为：5560 注意事项：如果OracleOraDb10g_home1TNSListener启动出错，找到Net Configuration Assistant重新配置监听即可。 默认启动：OracleServiceORCL 和 OracleOraDb10g_home1TNSListener 就足够了。

　　3、加锁、解锁用户(管理员 "sys" "system" 命令) 加锁：alter user 用户 account lock; 解锁：alter user 用户 account unlock; 4、登录命令、切换用户命令 1. connect 用户/密码 2. conn 用户/密码 ========不想被别人看到明文密码情况下======== 3. SQL> conn 请输入用户名: scott 输入口令: *****

　　4. SQL> conn 请输入用户名: scott 输入口令: *****

　　5、查询当前登录用户 show user;

　　6、查询、设置“行宽”和“显示条数” 查询行宽：show linesize; //默认80 设置行宽：set linesize 120; 查询显示条数：show pagesize; //默认14 设置显示条数：set pagesize 100; 1.字符串指定列宽：column 列名 format a号; 例如：column ENAME format a10; 2.数字指定列宽：column 列名 format 9999; 说明：一个9代表一个数字。 1 2的缩写格式： col 列名 for 指令; ===================================================================== 例如我添加的内容如下： -- 指定行宽 set linesize 150 -- 指定每页显示条数 set pagesize 100 -- 指定oracle默认显示时间 alter session set nls_date_format='yyyy-MM-dd'; ===================================================================== 永久保存设置配置：找到oracle的安装目录，例如：D:Softwareoracleproduct10.2.0db_1sqlplusadminglogin.sql 设置写到最下面即可。 //测试是否是jdbc语句

　　1、jdbc语句必须需要分号，否则会换行让你继续输入。

　　2、使用/来测试，如果是jdbc输入完毕，再输入/如果成功执行上句的话，证明是jdbc语句。 //查询当前用户所有表 select * from tab; //查看表结构 desc 表名; //打开查询耗时 set timing on; //关闭查询耗时 set timing off; //sqlplus(黑窗口)清屏，sqlplusw中无效 Windows：host cls Linux中：host clear plsql中：clear //多行输错,修改命令 edit 或者 ed ，修改完输入 / ，文件中不要有问号 //处理null值函数（vnl函数） nvl(字段,值) 例如：select nvl(comm,0) from emp; nvl2(字段,不为空返回值,为空返回值) 例如：select nvl2(NULL,2,3) //返回 3 //别名大小写 使用别名，如果不加 "" 默认大写。 加了 "" 就会按 "" 里显示。 as xx; --转为：XX as "xx"; --等于：xx 注意：当别名含有 "空格"、 "字符串" 时一定要加 "" 号; //取消重复行关键字：（distinct） select distinct 重复字段 from 表名; 注意：如果查询多列，distinct作用于多列，多列有重复才算重复。 //拼接字符串 --查询格式：ENAME的工资是：SAL select ename || '的工资是：' || sal from emp; //拼接字符串(（oncat）函数 select concat('我叫 ','小童鞋_成er') from dual; 拼接多个：select concat(concat('我叫 ','小童鞋_成er'),' 啊!') from dual; //oracle的虚表，oracle查询必须指定form关键字 select 'Hello ' || 'World' from dual; dual是一个虚拟表： 比如 得到当前时间：select sysdate from dual; 计算数值： select 3 + 2 from dual; //关于（like）查询特殊字符用法： ID NAME -- ---------- 1 dd% 2 %xx 1、我们进行查询，select * from 表名 where 字段 like '%%'; 那么就是查询所有了。 //定义转移字符：select * from 表名 where 字段 like '%%' escape ''; --把%转义，escpae指定哪个是转义符。 比如：select * from 表名 where 字段 like 'x%%' escape 'x'; //把x转义 2、查询第三个字母为I的： select * from 表名 where 字段 like '__I'; //查询~到~范围（between） 比如查询工资1500~6000之间的。 select * from 表名 where (工资字段 between 1500 and 6000); //查询id为1 2 3 4的用户 （in查询） 第一种写法：select * from 表名 where ID字段 = 1 or ID字段 = 2 or ID字段 = 3 or ID字段 = 4; 批处理写法：select * from 表名 where ID字段 in(1,2,3,4); 注意： select * from 表名 where 字段 in(xx,xx,null); //没有影响 select * from 表名 where 字段 not in(xx,xx,null); //如果not in 含有null，则不返回任何结果。 //字符串转为日期（to_date） to_date('2014-10-02','yyyy-MM-dd'); //年-月-日 to_date('2014-10-02 12:00:02','yyyy-MM-dd HH24:mi:ss'); //年-月-日-时-分-秒 //日期转为指定格式（to_char） to_char(日期字段,'yyyy-MM-dd'); //年-月-日 to_char(日期字段,'yyyy-MM-dd HH24:mi:ss'); //年-月-日-时-分-秒 //查询Oracle指定的格式（v$nls_parameters） select * from v$nls_parameters; //只能查看，不能修改 //修改当前会话的格式 默认时间格式：DD-MON-YY alter session set nls_date_format='yyyy-MM-dd'; //只对当前会话有效 //排序 （order by） 升序： select * from 表名 order by 要排序字段 asc; //asc默认也可不写，从高到底 降序： select * from 表名 order by 要排序字段 desc; //降序，从低到高 NULL值排序始终在下面： select * from 表名 order by 含有NULL字段 desc nulls last; NULL值排序始终在上面： select * from 表名 order by 含有NULL字段 desc nulls first; select * from 表名 order by nvl(含有NULL字段,-1) asc; 根据字段位置排序： select id,name from xx order by 2 desc; //2是name的位置 //分组 分组函数： max(字段); //最大值 min(字段); //最小值 avg(字段); //平均值 count(字段); //总条数 sum(字段); //总和 分组方法（group by）： select max(sal),deptno from emp group by deptno; //查询每个部门工资最高的人，如果group by中没有这个字段，就不能显示这个字段 对两个字段进行排序： select max(sal),deptno,job from emp group by deptno,job; //根据“部门(deptno)”和“工作(job)”进行排序。 对分组条件进行筛选（group by 字段 having 条件），同时进行排序（order by）： select deptno,count(*) from emp group by deptno having count(*)>4 order by deptno desc; 注意： group by 和 having 都不可以使用“别名”; order by 可以使用“别名”; 某些情况下，优先使用where，而不使用having。 例子,查询“部门”，“部门人数”，取消10号部门信息： 效率高：select deptno,count(*) from emp where deptno <> 10 group by deptno; 效率低：select deptno,count(*) from emp group by deptno having deptno <> 10; //字符函数 LOWER(String) //将字段转换为“小写”; select LOWER('ABC') from dual; -- 结果：abc UPPER(String) //将字段转换为“大写”; select LOWER('abc') from dual; -- 结果：ABC INITCAP(String) //每个单词首字母转为“大写”; select INITCAP('hello word.day') from dual; -- 结果：Hello Word.Day -- 首写字母为大写 select initcap(ename) from emp; //拼接字符串 CONCAT('a','b'); //跟||一样 //截取函数： SUBSTR(String,index,index) //要截取的字符串，从第几个开始，此位置往后截取几个 (从1下标开始) select SUBSTR('abc',1,2) from dual; -- 结果：ab USBSTR(String,index) //如果不指定，就从1位置，截取到字符串结束 select SUBSTR('abc',2) from dual; -- 结果：bc //字段长度 LENGTH(String) //返回字段长度 select LENGTH('abc') from dual; -- 结果：3 //查找字符串位置 INSTR(String,String) //返回字符串位置下标 select INSTR('abcdefg','c') from dual; -- 结果：3 //补齐函数LPAD(String,indexOf,char)和RPAD(String,indexOf,char) LPAD： select lpad('abc',5,'*') from dual; -- 结果：**abc RPAD： select rpad('abc',5,'*') from dual; -- 结果：abc** //可以匿名，比如： select ename as 姓名,substr(ename,1,1)|| replace(rpad(' ',length(ename)-1,'*'),' ','') ||substr(ename,length(ename)) as 匿名 from emp where ename = 'SCOTT'; 姓名 匿名 --------------- SCOTT S***T //去掉前后空格TRIM(' ' from String) TRIM(String) //去掉前后空格 TRIM('a' from 'abc') //去掉a，注意：只能为前、后替换 //替换函数replace(String,String,String) select * from replace('abc','a','1') from dual; -- 结果：1bc //数学函数： MOD(number,nunber) //求余数 ROUND(number) //四舍五入 ROUND(number,number); //截取小数点第几位，比如：12.91 结果:12 没有小数； 如果是-1，比如：12.22,-1 结果：10 TRUNC(number) 跟ROUND() 函数一样，只是不四舍五入 TRUNC(number,number) //操作时间 sysdate-1 ：减一天 sysdate-1/24 ：减一天1小时 sysdate-1/24/24 ：减一天1小时1分钟 last_day(date) : 获取当月最后一天 比如：select to_char(last_day(to_date('11','mm')),'yyyy-mm-dd') from dual; //返回：2014-11-30 add_months(date,number) ：当前日期减一天 或 加一天 比如：select to_char(add_months(last_day(sysdate),-1)+1,'yyyy-mm-dd') from dual; //返回当月的1号 months_between ：计算两个时间相差“月份” 比如；select months_between(sysdate,to_date('2014-12-19','yyyy-mm-dd')) from dual; //返回-1 round(date,'xx')：///对日期四舍五入，比如：round(to_date('2014-10-10 22:00:00','yyyy-mm-dd hh24:mi:ss'),'dd') 返回：2014-10-11 00:00:00 trunct(date,'xx')：///对日期进行截断，比如：trunc(to_date('2014-10-10 22:00:00','yyyy-mm-dd hh24:mi:ss'),'month')) 返回：2014-10-01 00:00:00 上面两个函数指定xx：yyyy month dd hh24 mi ss //根据“星期几”获取下个“星期几”的日期 next_day(sysdate,'星期三'); //to_char(number,'xx') 转换数字 to_char(sal,'L9999') : 转换为本地数字格式，9999表示显示位数 $999,9999.00 ： 0000 ：0补齐 //字符串转数字 to_number('3'); //判断,类似switch case case CASE 字段 WHEN xx -- 如果是 xx值 THEN xx -- 替换成 xx值 ELSE --否则 xx --返回 xx值 END ------------------------------------------------ CASE 字段 WHEN xx -- 如果是 xx值 THEN xx -- 替换成 xx值 WHEN xx -- 如果是 xx值 THEN xx -- 替换成 xx值 ELSE --否则 xx --返回 xx值 END //判读,类似if else case when xx=xx then Yes -- 是 else No -- 不是 end ------------------------------------------------ //判断（oracle独有） decode函数 select decode(ename,'是SCOTT','Yes','不是SCOTT','No','都不是') from emp where ename = upper('scott'); //集合操作 union ：取并集，比如：A集合有1 3，B集合有1 4；并集结果：1 3 4 select * from emp where ename in('SCOTT','CLARK') union select * from emp where ename in('SMITH','ALLEN','SCOTT'); union all ：取合集，比如：A集合有1 3，B集合有 1 4；合集结果：1 1 3 4 select * from emp where ename in('SCOTT','CLARK') union all select * from emp where ename in('SMITH','ALLEN','SCOTT'); intersect ：取交集，比如：A集合有1 3，B集合有1 4；交集结果：1 select * from emp where ename in('SCOTT','CLARK') intersect select * from emp where ename in('SMITH','ALLEN','SCOTT'); minus ：取差集，比如：A集合有1 3 ，B集合有1 4；差集结果：3 例如：select ename from emp where ename in('SCOTT','ALLEN') minus select ename from emp where ename in('SCOTT','ALLEN','SMITH'); 结果：“无结果” 例如：select ename from emp where ename in('SCOTT','ALLEN','SMITH') minus select ename from emp where ename in('SCOTT','ALLEN'); 结果：SMITH 总结：以“第一个”集合为中心，取两结果的相差 /* 注意事项： 1、如果是两个查询设计分组； 2、order by必须在最后一个集合，group by无限制 3、集合数量必须一样，类型必须一样，其它字段名字不同可以。 */ //左连接、右连接 -- 左连接： /*Oracle专用：*/ select d.deptno as "部门编号",d.dname as "部门名称",count(e.deptno) as "总人数" from emp e,dept d where d.deptno=e.deptno(+) group by d.deptno,d.dname order by count(e.deptno); /*通用：*/ SELECT d.deptno AS "部门编号",d.dname AS "部门名称",COUNT(e.deptno) AS "部门总人数" FROM emp e LEFT JOIN dept d ON e.deptno=d.deptno GROUP BY d.deptno,d.dname ORDER BY 部门总人数 -- 右连接： /*Oracle专用：*/ select d.deptno as "部门编号",d.dname as "部门名称",count(e.deptno) as "总人数" from emp e,dept d where d.deptno=e.deptno(+) group by d.deptno,d.dname order by count(e.deptno); /*通用：*/ SELECT d.deptno AS "部门编号",d.dname AS "部门名称",COUNT(e.deptno) AS "部门总人数" FROM emp e RIGHT JOIN dept d ON e.deptno=d.deptno GROUP BY d.deptno,d.dname ORDER BY 部门总人数 //单表“自连接” /*方法1：*/ select a.ename,b.ename from emp a, emp b where a.mgr=b.empno; /*内链接通用：*/ SELECT a.ename,b.ename FROM emp a INNER JOIN emp b ON a.mgr=b.empno; //inner 可以省略不写 //满外连接 /*mysql不能用：*/ SELECT e.ename,e.deptno,d.dname,d.deptno FROM emp e FULL JOIN dept d ON e.deptno = d.deptno; //返回笛卡尔集 SELECT e.ename,e.deptno,d.dname,d.deptno FROM emp e CROSS JOIN dept d; ==============================================Oracle分页============================================================== //第一页 select * from (select rownum rn,a.* from (select * from emp) a where rownum <=10) where rn >=1; //第二页 select * from (select rownum rn,a.* from (select * from emp) a where rownum <=20) where rn >=11; ==============================================DML操作============================================================== //临时表创建 全部字段：create table 新表名 as select * from 表名; 部分字段：create table 新表名 as select 字段1,字段2,字段3 from 表名; //制定列插入数据 insert into 表名(字段1,字段2) select 字段1,字段2 from 表名; //删除表结构，不经过回收站 drop table 表名 purge; //关闭Oracle反馈提示 set feedback off; //delete和truncate删除的区别： delete table 表名：可恢复，删除速度做了优化，快。 truncate table 表名：不可恢复，删除速度慢。 ================================保存屏幕到文本================================= spool D:1.txt; //保存路径 select * from emp; spool off; //关闭保存 ================================保存点========================================= savepoint 保存点名称; //建立保存点 rollback to 保存点名称; //回滚指定名称的保存点 rollback; //回滚全部 ================================数据闪回======================================= //启用表闪回 alter table 表名 enable row movement; //闪回到指定时间 flashback table 表名 to timestamp to_timestamp('2014-12-03 15:04:40','yyyy-mm-dd HH24:mi:ss'); /*注意：delete可以恢复，truncate不可恢复。*/ ====================|============数据类型===================|================== | 数据类型 | 描述 | |----------------|----------------------| | varchar2(size) | 可变长字符数据。 | |----------------|----------------------| | char(size) | 定长字符数据。 | |----------------|----------------------| |number(si~[.xx])| 可变长数值数据。 | |----------------|----------------------| | date | 日期型数据。| |----------------|----------------------| | long |可变长字符数据,最大2G | |----------------|----------------------| | clob | 字符数据，最大4G。 | |----------------|----------------------| | blob | 二进制数据，最大4G。| |----------------|----------------------| | bfile |存储外置文件二进制,4G | ----------------------------------------- ================================数据库创建、约束================================= create table stu( id number constraint PK_STU_ID //PK_STU_ID 是约束的名字 primary key, //设置主键 name varchar2(20) check( length(name) > 2), //长度要大于2个字符 gender char(2) check( gender in('男','女') ), //值只能为“男”或“女” address varchar2(30) not null, //不能为NULL birthady date default sysdate //默认当前时间 ); -- 约束： 1、主键约束：constraint PK_STU_ID primary key //唯一，不能为NULL，不可重复 2、不能为NULL约束：constraint NOTNULL_STU_NAME not null //不能为NULL 3、只能是唯一约束：constraint UNIQUE_STU_XX unique //唯一约束，例如身份证一对一 4、数字约束：constraint CHECK_STU_AGE check( age > 0 and age <= 100) //年龄值必须是大于0,小于100 5、规定约束：constraint CHECK_STU_SEX check( sex in('男','女') ) //必须是“男”或“女” 6、外键约束：constraint FK_STU_DEPTNO references 外键表(外键表字段) //外键约束 //查看约束字典 select * from user_constraints; //查询指定表约束 select * from user_constraints where table_name='表名'; ===========================表字段操作============================= //增加列 alter table 表名 add(列名 xx); //修改列 alter table 表名 modify(age number check(age>2)); //修改列名 alter table 表名 rename column 旧列名 to 新列名; //删除列 alter table 表名 drop column 列名; //给表修改名字 rename 旧表名 to 新表名; //给列字段添加备注 comment on column 表名.列名 is '这是备注信息'; /*mysql*/ alter table emp modify 表名 列类型 comment '这是备注信息'; //给表增加注释 comment on table 表名 is '这是给表增加注释'; ===========================视图============================= //赋予创建视图的权限 grant create view to 用户名; //查询VIEW select * from tab where tabtype='VIEW'; /** * 视图的作用 **/ 1、屏蔽掉DML(增、删、改)操作，利用别名。 2、视图不可以提高查询性能。 3、简化复杂的查询。 4、限制数据的访问。 //创建视图语法，默认如果不指定 就可以对视图进行DML操作的 create view 视图名称 as 查询sql ; //创建只读视图 create view 视图名称 as 查询sql with read only ; ========================================================================================= 注意：mysql、sql server都不支持with read only，使用下列可以达到效果 create view my_view as select empno,ename,deptno from emp where deptno = 10 union select 0,'0',0 from emp where 1=0 ; ========================================================================================= //替换现有视图 create or replace view 旧视图名称 as .... ; //只允许选择条件插入视图 create view 视图名称 as select empno,ename,sal,deptno from emp where deptno = 10 with check option -- 只能插入 deptno=10 的 ; /* where deptno in(10,20) with check option -- 只能插入 10 和 20 的数据 */ ===========================序列============================= //创建一个序列 create sequence 序列名; //修改一个完整序列 alter sequence myxl increment by 1 //每次递增数(可不写) minvalue 1 //最小值为1(可不写) start with 2 //值从哪个数开始(可不写) maxvalue 10 //最大值(可不写) //nomaxvalue 没有最大值 nocache //不使用缓存,(可不写，默认大小20) nocycle; //不循环(可不写),循环是cycle //查询当前序列值 select 序列名.currval from dual; //增加一个序列值 select 序列名.nextval from dual; 注意：如果首次创建序列没有调用，查询当前序列值 会报：ORA-08002: 序列 MYXL.CURRVAL 尚未在此会话中定义 //删除序列 drop sequence 序列名; //查询当前用户有哪些序列 select * from user_sequences; ===========================索引============================= //创建索引 create index 序列名 on 表名(字段名); //查看当前用户有哪些序列 select * from user_indexes; //删除序列 drop index 序列名; /* LIKE 查询不会用到索引 */ ===========================同义词============================= /* 从字面上理解就是别名的意思，和视图的功能类似。就是一种映射关系。 */ //创建同义词 create synonym 同义词名 for 表名; //删除同义词 drop synonym 同义词名; //查看当前用户有哪些同义词 select * from user_synonyms; /* 知识扩展：数据库对象 表、视图、序列、过程、函数、程序包，甚至其它同义词都可以创建同义词。 */ ============================存储程序（控制台）===================================== //显示存储过程脚本输出 set serveroutput on; ------------------------------------------------------------------------------ //存储过程实例 declare -- 变量 //定义一个变量 变量名 number(3); //定义一个变量，并赋默认值 变量名 varchar2(20):= NULL; //定义一个，使用“表.列”类型 变量名 表名.列名%type; //定义一个容器，相当于List，使用表全部字段 变量名 表名%rowtype; begin -- sql语句 //赋值一个变量 select ename into 变量名 from emp where empno=7839; //赋值两个或多个变量 select ename,sal into 变量名,变量名 from emp where empno=7839; //赋值容器，表全部字段 select * into 变量名 from emp where empno=7839; //输出语句 dbms_output.put_line(变量名 || '值为：' || 变量名); end; ------------------------------------------------------------------------------ //友好提示输入窗口(命令) accept &提示输入的名字 prompt '请输入员工编码：'; //就是 &empno select ename into 变量名 from emp where empno=&empno; //判断============================================================ -- 方式一-- -- /* if 条件 then 语句; end if; */ -- -- 方式二 -- -- /* if 条件 then 语句; else 语句; end if; */ -- -- 方式三 -- -- /* if 条件 then 语句; elsif then 语句; else 语句; end if; */ //判断示例 set serveroutput on; //显示存储过程脚本输出 accept vsex prompt '请输入您的性别：'; //友好提示输入窗口(命令) declare vsex char(2); //定义变量，存储性别 vstr varchar2(20); //定义变量，打印字符串 begin vsex := '&vsex'; //使用输入窗口命令赋值 if vsex = '男' then vstr:='先生,欢迎您！'; //如果是男就把vstr字符串赋值为：先生,欢迎您！ elsif vsex = '女' then vstr:='女士,欢迎您！'; //...或者 else vstr:='对不起,请登录你的性别！'; //...否则 end if; //关闭判断 dbms_output.put_line(vstr); //打印内容 end; //循环============================================================ /* for 循环 */ set serveroutput on; declare begin for i in 1..5 loop dbms_output.put_line(i); end loop; end; /* loop 循环 */ set serveroutput on; declare vnum number(2):=1; begin loop //定义loop exit when vnum > 10; //当满足这个添加退出循环 dbms_output.put_line(vnum); vnum := vnum + 1; end loop; //结束loop end; /* while 循环 */ set serveroutput on; declare vnum number(2):=1; begin while vnum <= 10 //此处不要分号 loop dbms_output.put_line(vnum); vnum:=vnum+1; end loop; end; ============================光标（游标）===================================== //小例子 set serveroutput on; declare -- 1、定义光标（所有员工的集合） cursor c_emp is select ename,job from emp; -- 7、定义变量，存放ename 和 job vename emp.ename%type; vjob emp.job%type; begin -- 2、打开光标 open c_emp; -- 4、循环集合（光标） loop -- 6、获取数据 fetch c_emp into vename,vjob; -- 8、判断集合循环（光标）完退出循环 exit when c_emp%notfound; //最后输出 //dbms_output.put_line(vename||'的工作是：'||vjob); /* -- 修改语法 if vename = 'SCOTT' then update emp set ename='修改后的名字' where ename=vename; commit; end if; */ -- 5、关闭循环 end loop; -- 3、关闭光标 close c_emp; end; 异常类型： ZERO_DIVIDE： 除数为0异常。 NO_DATA_FOUND： 未找到数据异常。 //预定义除数为0异常 set serveroutput on; declare vnum number(1):=2; begin vnum := vnum/0; raise ZERO_DIVIDE; -- oracle 预定义异常：除数为0异常 dbms_output.put_line('发生异常不走这里!'); exception -- 发生异常处理 when ZERO_DIVIDE then dbms_output.put_line('自定义除0异常!'); end; /*自定义异常*/ set serveroutput on; declare vnum number(1):=1; my_exception exception; begin if vnum is null then dbms_output.put_line('空啦！'); elsif vnum is not null -- then dbms_output.put_line('不为空啦！'); then raise my_exception; -- 抛出异常 end if; exception when my_exception then dbms_output.put_line('发生异常啦，兄弟！'); end; //返回其他异常 exception when others then xxxx; ============================存储过程===================================== //创建存储过程过程 create procedure 存储过程名字 as //变量 vnum number:=3; begin dbms_output.put_line(vnum); end; //修改为 create or replace procedure 存储过程名字 //带参数的存储过程 注意：如果传入的是varchar2类型，这样：vename varchar2 不能 varchar2(10) create or replace -- 员工序号,涨多少工资 procedure addSal(vempno in number,vmoney in number) as vename emp.ename%type; vsal emp.sal%type; begin update stu set sal=sal+vmoney where empno = vempno; commit; select ename,sal into vename,vsal from stu where empno=vempno; dbms_output.put_line('姓名：' || vename || ' 涨了 ' || vmoney || ' 工资！'); dbms_output.put_line('月工资为：' || vsal); end; /* 执行方式一、 set serveroutput on; execute 存储过程名字(); */ /* 执行方式二、 set serveroutput on; begin 存储过程名字(); end; / */ //查看用户存储过程 select * from user_procedures; //删除存储过程 drop procedure 存储过程名字; ============================存储函数（带有返回值）===================================== //创建存储函数 create or replace function My_Function(vempno number) return varchar2 //存储函数返回值类型 as vename stu.ENAME%type; begin select ename into vename from stu where empno=vempno; return vename; //返回 end; /*执行存储函数*/ select My_Function(参数) from dual; //也可用于插入字段 //查看用户存储函数 select * from user_procedures; //删除存储函数 drop function 存储函数名称; //结论： 什么时候用存储过程、什么时候用存储函数？ 答：一般返回值有一个，用“存储函数”；多个就用“存储过程”！ //存储过程跟存储函数，都可以使用out输出变量 create or replace -- out 输出变量：会生成注释输入，取消掉注释，测试输出 function My_Function(vempno number,vsal out number, vcomm out varchar2) return varchar2 as -- 输出不了，没有指定out vename stu.ENAME%type; begin select ename,sal,comm into vename,vsal,vcomm from stu where empno=vempno; -- 只能手动打印 dbms_output.put_line(vename); return NULL; end; //存储过程使用out参数 create or replace procedure My_Procedure(name in varchar2,vename out varchar2) as vsal stu.sal%type; begin select ename,sal into vename,vsal from stu where ename=name; end; ============================Oracle回收站（ORACLE 10G之后特性） 普通用户，管理员没有回收站（慎重删除）===================================== //查看回收站 select * from user_recyclebin; //从回收站中撤回删除的表 flashback table 表名 to before drop; //表名是ORIGINAL_NAME字段 //从回收站撤回删除表时重命名 flashback table 表名 to before drop rename to 新表名; //根据回收站名字闪回表 flashback table "BIN$XX文字" to before drop; //清空回收站指定表 purge table 回收站表名; //也就是ORIGINAL_NAME //清空回收站 purge recyclebin; ==========================(for update 和 for update wait)区别============================ /** * for update 操作没有提交时，如有另一线程操作，会进行等待... * for update nowait 操作没有提交时，另一线程操作时，会弹出：ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源 **/ ==========================JDBC调用“存储过程”和“存储函数”============================ //JDBC调用存储函数 create or replace procedure My_Procedure(p_empno in number,r_ename out varchar2,r_sal out number) as begin select ename,sal into r_ename,r_sal from emp where empno=p_empno; end; ----------------------------------------------------------------------------------------------------------------------------- Class.forName("oracle.jdbc.OracleDriver"); Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "tiger"); //第一个问号是in 参数，后两个是out CallableStatement call = conn.prepareCall("{call My_Procedure(?,?,?)}"); // 赋值?为7839 call.setObject(1, 7839); // 指定返回值类型 call.registerOutParameter(2, OracleTypes.VARCHAR); // 指定返回值类型 call.registerOutParameter(3, OracleTypes.NUMBER); // 执行查询 call.executeQuery(); //打印返回参数，从?号开始索引 System.out.println(call.getString(2)); //打印返回参数，从?号开始索引 System.out.println(call.getString(3)); //关闭 call.close(); conn.close(); //JDBC调用存储函数 create or replace function My_Function(p_empno in number) return varchar2 as v_ename emp.ename%type; begin select ename into v_ename from emp where empno=p_empno; return v_ename; end; ----------------------------------------------------------------------------------------------------------------------------- Class.forName("oracle.jdbc.OracleDriver"); Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "tiger"); //第一个问号是返回值，(?)才是参数 CallableStatement call = conn.prepareCall("{?= call My_Function(?)}"); // 赋值第二个?为7839 call.setObject(2, 7839); // 指定返回值类型?=是返回值 call.registerOutParameter(1, OracleTypes.VARCHAR); // 执行查询 call.executeQuery(); //打印返回参数，从?号开始索引 System.out.println(call.getString(1)); //关闭 call.close(); conn.close(); /* 注意：function只能有一个返回值，如果有多个，用存储过程 */ ==========================数据字典============================ //数据字典总表 select * from dictionary; /* 数据字典表前缀说明 */ 前缀 说明 user 用户自己的。 all 用户可以访问到的。 dba 管理员视图。 v$ 性能或者参数设置相关的数据。 //查询当前用户对象（表、视图、索引、序列、存储等） select * from user_objects; //查询所有用户对象（......) select * from all_objects; //表与列的信息 desc[ribe] user_tables; //列的信息 desc user_tab_columns; //当前用户表上的约束 select * from user_constraints; //当前用户创建的约束 select * from user_cons_columns; //当前用户视图信息 desc user_views; //查看当前用户权限 select * from session_privs; //查看数据库中所有的系统权限信息（需要dba权限） select * from dba_sys_privs; //查看数据库所有的对象权限信息（需要dba权限） select * from dba_tab_privs; ==========================触发器============================ /* 触发器是一个与表关联的、存储pl/sql程序 insert、update、delete 分类： 语句级： 在指定的操作语句操作之前或之后执行一次，不管这条语句影响了多少行。 行级触发器（for eachrow）： 触发语句作用的每一条记录都被触发，在行级触发器中使用old和new伪记录变量，识别值的状态。 触发器作用： 1、数据确认。 2、实施复杂的安全检查。 3、做审计、跟踪表上所做的数据操作等。 4、数据的备份和同步。 定义错误规则： 在触发器中阻止SQL继续执行的方法： 抛一个异常： raise_application_error(-20001,'异常内容'); 异常代码应在-20000~-20999之间（包含，否则提示：“错误号参数超出范围”） */ //语法 create [or replace] trigger 触发器名 //之前，之后 {before | after} //指定操作 {delete | insert | update [of 列名]} //指定表 on 表名 //加上代表行级触发器 [for each row [when(条件)]] //plsql代码块 declare -- 变量 as -- 代码 end; //创建一个触发器，不是8点到18点工作日，不能插入数据 create or replace trigger My_Trigger before -- 指定插入数据之前 insert -- 指定操作触发器DML on stu -- 表名 declare -- 变量 begin if not (to_number(to_char(sysdate,'hh24')) between 8 and 18) -- 错误代码必须在-20000~-20999之间（包含，否则提示：“错误号参数超出范围”） then raise_application_error(-20000,'现在是非工作时间，不能插入数据！'); end if; end; //创建一个行级触发器，涨工资不能越涨越少 create or replace trigger My_Trigger2 after -- 插入之后 update on stu for each row -- 行触发器 declare -- 变量 begin -- :new 新值 < :old 旧值 if :new.sal < :old.sal then raise_application_error(-20000,'工资不能越涨越少呀！'); end if; end; SQL：update stu set sal=sal-1 where empno=7839 //查询触发器 select * from user_triggers; //删除触发器 drop trigger 触发器名; /* 主键自动增长 --> 触发器 */ -- 创建触发器 create sequence My_Sequence; create or replace trigger My_Sequence before insert on stu for each row declare --变量 begin select My_Sequence.nextVal into :new.empno from dual; end; ==========================Oracle备份============================ //选择表备份，生成日志 /* file ： 数据库备份文件 log ： 控制台导出内容 tables : 指定表，多个表,隔开 */ exp scott/tiger@127.0.0.1:1521/orcl file=D:xx.dmp log=D:xx.log tables=emp,dept; //导出全部 exp scott/tiger@127.0.0.1:1521/orcl file=D:xx.dmp log=D:xx.log; //导入 imp scott/tiger@127.0.0.1:1521/orcl file=D:xx.dmp log=D:imp.log; ==========================用户管理============================ //创建一个用户（dba权限）

　　create user 用户名 identified by 密码(必须字母开头);

　　//赋予create session权限 grant CREATE SESSION to 用户;

　　//用户解锁与锁定 alter user xiaoming account unlock;

　　-- 解锁 alter user xiaoming account lock; -- 锁定 //给用户修改密码 alter user 用户名 identified by 新密码;

　　//删除用户 drop user 用户名; drop user 用户名 cascade; -- 级联删除用户（诛杀九族）

　　//用户授权 grant 权限1,权限2,... to 用户; //回收权限 revoke 权限1,权限2,... from 用户; ----------------------------------------- 赋予表空间使用权限： -- quota 容量， unlimited 无限制， on users 在users这个表空间上没有限制 alter user 用户名 quota unlimited on users; create session //登录“数据库”权限 create table //创建“表”权限 create view //创建“视图”权限 create sequence //创建“序列”权限 create procedure //创建“存储”权限 create trigger //创建“触发器”权限 create synonym //创建“同义词”

标签： oracle客户端配置