Oracle常用代码总结

java开发中的总结 大家可以看看哦

建表语句：
1.bak_emp:
create table bak_emp(empno number(4) primary key,ename varchar2(30),job varchar2(30),mgr number(4),hiredate date,sal number(7,2),comm number(5,2),deptno number(4));
2.acount_asscess:
create table account_asscess(accountno char(15) not null,flag int,money number(10,2));
3.depositor:
create table depositor(accountno char(15) primary key,money number(10,2) not null);
基础查询：
select * from guyuan;（全部查询）
select employee_id,job_id from guyuan;（按列名查询）
select fast_name 姓名,job_id 工作，department_id 部门 from guyuan;（列别名查询）

简单查询：
select * from guyuan where salary between 8000 and 30000;（临界条件查询）
select * from guyuan where department_id = 30;（根据值查询）
select * from guyuan where fast_name = 'Berry';
select * from guyuan where salary <= 6000;（模糊范围查询）
select * from guyuan where salary between 5000 and 30000;（between。。。and...）
select * from guyuan where manager_id in(1001,1003);（in指定值查询）
select * from guyuan where fast_name like '_e%';（第二个字符为e的查询）
select * from guyuan where fast_name like 'B%';（首字母为B的查询）
select * from guyuan where manager_id is null;（列中空值查询）
select * from guyuan where salary >=9000 and job_id like '%项目%';（字符串查询）
select * from guyuan where job_id not in('项目经理','董事');（not in查询）
select * from guyuan where job_id = '项目经理' or job_id = '开发人员';（or查询）
select * from guyuan where job_id like '%项目%' or job_id like '开发%' and salary > 6000;（条件查询+or+and）
select fast_name,job_id,salary from guyuan order by salary;（分组排序 升序）
select fast_name,job_id,salary from guyuan order by salary desc;（分组排序 降序）
select fast_name,job_id,salary,hiredate from guyuan order by hiredate;
select fast_name,salary,commission,salary+commission total from guyuan order by total;（分组排序+组函数）
select fast_name,salary,commission,salary+commission total from guyuan order by total desc;
select employee_id,fast_name,salary,department_id from guyuan order by salary,department_id desc;（多条件分组排序+组函数）
select distinct department_id,job_id from guyuan;（取消重复）
select fast_name,job_id,salary,mod(salary,5000) from guyuan;(整除取余函数)
select fast_name,hiredate from guyuan;(日期函数)
select fast_name,hiredate,sysdate,(-(sysdate-hiredate))/7 from guyuan;(系统时间，雇用周)
select add_months('1-1月-2011',8) from dual;(add_months函数)


简单函数：
select employee_id,fast_name from guyuan where LOWER(fast_name) = 'forever'（使用小写函数转化后为引号的内容）
select employee_i
d,fast_name from guyuan where Upper(fast_name) = 'FOREVER';(使用大写函数转化后为引号的内容)
select employee_id,concat(fast_name,job_id),lpad(salary,10,'*'),le

java开发中的总结 大家可以看看哦

ngth(fast_name),instr(job_id,'员'),substr(job_id,1,3),trim('r' from fast_name)from guyuan where department_id = 30 order by salary desc（concat,substr,instr,length,lpad,trim函数的综合应用）
select round(4545.1481854156,5),round(4545.1481854156,3),round(4545.1481854156,1),round(4545.1481854156,0),round(4545.1481854156,-3) from dual;（round四舍五入函数）
select trunc(54.26846416,5),trunc(54.26846416,2),trunc(54.26846416,0),trunc(54.26846416,-2),trunc(54.26846416,-4) from dual;(截取字串函数)


多表查询：
select e.employee_id,e.fast_name,d.department_id,department_name from guyuan e,bumen d where e.department_id = d.department_id;（等值连接）
select e.employee_id,e.fast_name,d.department_id,e.department_id,d.department_name from guyuan e,bumen d where e.department_id = d.department_id and fast_name = 'Carry';（多表+条件连接）select e.employee_id,e.fast_name,e.job_id,l.city from guyuan e,bumen d,location l where e.department_id = d.department_id and d.location_id = l.location_id;（多表等值连接）
select * from sal_grades;（查询）
select e.fast_name,e.job_id,e.salary,s.grade from guyuan e,sal_grades s where e.salary between s.lowest_sal and s.highest_sal;（多表连接）
select e.fast_name,e.department_id,d.department_id,d.department_name from guyuan e,bumen d where e.department_id (+)=d.department_id;(左外连接)
select distinct e.employee_id,e.job_id,e.manager_id,m.manager_id from guyuan e,guyuan m where e.employee_id = m.manager_id;（自连接）
select e.employee_id,m.fast_name||' works for '||e.fast_name from guyuan e,guyuan m where e.employee_id = m.manager_id;（自连接）
select * from guyuan cross join bumen;（叉集）
select department_id,department_name,city from bumen natural join location;(自然连接);
select e.employee_id,e.fast_name,d.location_id from guyuan e join bumen d using (department_id);(using字句)
select e.employee_id,e.fast_name,e.department_id,d.department_id from guyuan e join bumen d on (e.department_id = d.department_id);（join on等值连接）
select e.employee_id,e.fast_name,e.job_id,e.department_id,d.location_id,l.city from guyuan e join bumen d on e.department_id = d.department_id join location l on d.location_id = l.location_id;（join on多表连接）
select e.employee_id,e.fast_name,e.job_id,e.manager_id,e.department_id from guyuan e left outer join bumen d on (e.department_id = d.department_id) order by employee_id;（左外连接）
select e.employee_id,e.fast_name,e.department_id,d.department_name from guyuan e right outer join bumen d on (e.department_id = d.department_id);（右外连接）
select e.fast_name,e.employee_id,e.department_id,d.department_name from guyuan e full outer join bumen d on (e.department_id = d.department_id); （全外连接或满外连接）
select e.emp
loyee_id,e.fast_name,e.department_id,d.department_name from guyuan e join bumen d on (e.department_id = d.department_id) and e.manager_id = 1002

java开发中的总结 大家可以看看哦

;(含有附加连接的连接)


分组函数：
select avg(salary),max(salary),min(salary),sum(salary)from guyuan e where job_id like '开发%';（四个函数）
select max(hiredate),min(hiredate) from guyuan;(日期最大值和最小值)
select count(*) from guyuan;（查询总记录数）
select count(*) from guyuan where job_id = '开发人员';（按条件查询记录数）
select count(commission) from guyuan;（查询某列不为空记录条数）
select count(commission) from guyuan where department_id = 20;（查询某列不为空且满足条件2的记录条数）
select count(distinct department_id) from guyuan;（不重复的记录条数查询）
select avg(nvl(commission,0)) from guyuan;（不忽略空值的计算）
select department_id,avg(salary) from guyuan group by department_id;(组函数分组查询)
select department_id,job_id,avg(salary) from guyuan group by department_id,job_id;（多条件组函数+分组查询）
select department_id,max(salary) from guyuan group by department_id having max(salary)>6000;(having字句过滤分组)
select job_id,sum(salary) from guyuan where job_id not like '开发%' group by job_id having sum(salary)>10000 order by sum(salary);(having字句+分组+排序)
select max(avg(salary)) from guyuan group by department_id;(嵌套组函数+分组)

子查询：
select fast_name,job_id,department_id from guyuan where job_id = (select job_id from guyuan where employee_id = 1005) and salary >
(select salary from guyuan where employee_id = 1006) （and连接两个子查询条件）
select fast_name,job_id,department_id,salary from guyuan where salary = (select min(salary) from guyuan)（子查询嵌套分组函数）
select department_id,min(salary) from guyuan group by department_id having min(salary) > (select min(salary) from guyuan where department_id = 30)（分组+子函数）
select fast_name,job_id from guyuan where job_id = (select job_id from guyuan where fast_name = 'Carry')（子查询）
select employee_id,fast_name,job_id,salary from guyuan where salary < all(select salary from guyuan where job_id = '项目经理' )and job_id <> '项目经理'（all-全部子查询）

变量：
select employee_id,fast_name,department_id,job_id,manager_id from guyuan where employee_id = &employee_id;（输入变量进行查询）
select * from guyuan where job_id = '&工作';（查找指定工作）
select * from guyuan &工资 where &条件 order by &排序列;(多条件查询)
select employee_id,fast_name,&&column_name from guyuan order by &column_name;（列名控制查询）
set verify on（换行样式）
select employee_id,fast_name,salary,department_id from guyuan where employee_id = &employee_num

columm操作:
column Emloyee_name heading 'first_name';修改列名
column sal justify left form
at $99,999,999.00;货币显示符号
alter table guyuan add (age number(4)) (增加一列)
alter table employees drop (phone_number,hire_date);(删除列)
alter table

java开发中的总结 大家可以看看哦

employee modify (comm number(10));（修改列）
select * from all_col_comments;(查找所有列注释)
select * from user_col_comments;(操作当前用户的列注释)
select * from all_tab_comments;(查找所有表注释)
select * from user_tab_comments;(查找当前用户的所有表注释)

操作表：
truncate table guyuan;(删除表中的所有数据，并释放空间)
drop table guyuan;(删除表，不释放空间)
comment on table employees is 'Employees Information ';（为表添加注释）

DML操作语句：
insert into guyuan(employee_id,fast_name,hiredate,job_id,salary,commission,manager_id,department_id) values (1017,'Herry','25-6月-2012','业务人员',5000.00,5000,1012,40)（插入语句）
insert into othertable(employee_id,fast_name,hiredate,job_id,salary,commission,manager_id,department_id) select employee_id,fast_name,hiredate,job_id,salary,commission,manager_id,department_id from guyuan where job_id like '%开发';（从一个表复制多行到另一个表中）
update guyuan set job_id = '宣传员',salary = 5000 where employee_id = 1016;(更新雇员表)
update guyuan set department_id = (select department_id from guyuan where employee_id = 30) where employee_id = 1016（更新雇员表）
delete from guyuan where employee_id = 1016;(删除一行)
rollback;（对delete操作进行回滚）
commit;（对delete操作进行提交）
savepoint update_done;(以当前作为保存点)
rollback to update_done(当前点回滚)
delete from employee where empno = (select empno from employee where job = '教务主任')（子查询删除）
insert into employee(empno,ename,job,mgr,hiredate,sal,comm,deptno) values (1009,'小样儿',CCSTP,'31-8月-12',3000,300,default)（用default默认值）



数据库权限操作：
create user cui identified by cui;（建立新用户，角色）(密码不能是数字)
select * from dba_users;（查找所有用户）
select * from dba_users where username = 'CUI';（查找cui用户）
alter user exam identified by cui;
select * from dba_sys_privs where grantee = 'SCOTT';（查找scott的权限）
select * from scott.guyuan;（查找scott用户下的guyuan表）
create view guyuanview as select * from scott.guyuan;（建立视图view）
select * from guyuanview;（查找视图view）
drop user EXAM;（删除用户）
grant create table to CUI;（给用户CUI授权建表权力）
select * from dba_roles;（查找所有用户角色）
create role Admin;（建立新角色）
grant Admin to CUI;（将Admin角色授权给用户CUI）
grant select on employees to CUI(授予用户CUI中的表查找权限)
grant create session,create table,unlimited tablespace to CUI;（授予用户CUI会话，建表，对表无限制权限）
grant unlimited tablespace to CUI（对
CUI用户授予对表无限制权限）
revoke create table from CUI;（撤销用户CUI的建表权限）
grant select on scott.emp to scott
grant resource,connect to ABC;

日期格式化：(date-

java开发中的总结 大家可以看看哦

>char)
select to_char(hiredate,'fmDD:Month:YYYY') from guyuan;（格式化时间—>char）
select fast_name,hiredate,to_char(hiredate,'fmDD:Month:YYYY HH24:MI:ss AM' ) FROM guyuan;（月日年，时分秒 上午->char）
select fast_name,hiredate,to_char(hiredate,'YYYY:Month:fmDD HH24:MI:ss AM' ) FROM guyuan;(年月日 时分秒->char)
select sysdate,to_char(sysdate,'YYYY:MI:FMDD HH12:MM:SS') from dual;（系统当前时间->char）
select fast_name,hiredate,to_char(hiredate,'DD-MM-YY') FROM guyuan;(日-月-年)
select hiredate,to_char(hiredate,'dd-mm-RR hh:mm:ss') from guyuan;(日月年 时分秒)

数字格式化：(number->char)
select employee_id,salary,to_char(employee_id,'999999') from guyuan;(数字—>char)

通用函数：
select fast_name,manager_id,nvl(to_char(manager_id),'No Manager') from guyuan where manager_id is null;（通用输出结果）
select fast_name,nvl(to_char(commission),'no commission') "no commission" from guyuan where commission is null;(通用)
select fast_name,salary,commission,nvl2(commission,'员工','经理') income from guyuan where department_id in (20,30);(分类显示)
select fast_name,salary,commission,coalesce(commission,salary,10) conn from guyuan order by commission;（第一个值为空，返回表达式，否则运算）
create index ind on scott.dept(dname);(创建索引)

数据库键值约束：
select * from user_constriains;
select * from user_tables;
alter table add {constraint col_unique{name}/col_primary key{id,name}}
alter table abc modify age check(age>20)
alter table abc modify job default 20;
alter table abc modify job null;

create table keys(key_id number(5),key_name varchar2(20) not null,key_length varchar2(20),constraint key_id_primary primary key(key_id));(含有主键的表)










PL/SQL:
1.查询记录，如果查询失败则添加记录
（一）
declare
uname varchar2(20):='liming';
uage number(3):=21;
begin
update users set age=uage where name=uname;
if sql%notfound then
insert into users values (4,uname,uage);
end if;
end;
（二）
declare
uname %type:='cui';（查询时自动匹配类型）
uage users.age%type:=24;
begin
update users set age=uage where name=uname;
if sql%notfound then
insert into users values(5,uname,uage);
end if;
end;

2.打开PL/SQL输出功能
set serveroutput on；
3.输出一条语句
declare
type userRecord is record(uname varchar2(20),uage number(3));
rec userRecord;
begin
select name,age into rec from users where name = 'cui';
dbms_output.put_line(rec.uname||'-'||rec.uage);
end;
4.显示部门20的平均工资是否大于1500
declare
avg_sal number(5);
begin
select avg(salary) into avg_sal from guyuan where department_id = 20;

if avg_sal > 1500 then
dbms_output.put_line('平均工资大于1500');
else
dbms_output.put_line('平均工资不大于1500');
end if;
end;

5.loop循环

if实现

java开发中的总结 大家可以看看哦

循环：
declare
n number:=1;
count1 number:=2;
begin
loop
n:=n*count1;
count1:=count1+1;
if count1>10 then
exit;
end if;
end loop;
dbms_output.put_line(to_char(n));
end;
when实现循环：
declare
n number:=1;
count1 number:=2;
begin
loop
n:=n*count1;
count1:=count1+1;
exit
when count1>10;
end loop;
dbms_output.put_line('结果：'||to_char(n));
end;
while实现循环：
declare
n number:=1;
count1 number:=2;
begin
while count1<=10
loop
n:=n*count1;
count1:=count1+1;
end loop;
dbms_output.put_line('结果：'||to_char(n));
end;
for循环：
declare
n number:=1;
count1 number;
begin
for count1 in 2..10
loop
n:=n*count1;
end loop;
dbms_output.put_line('结果:'||to_char(n));
end;


--自定义函数--
--功能：实现以部门号查询平均工资--
--第一步：编写自定义函数：
create or replace function avgersal(v_dept_sal in
number) return number is
Result number;
begin
select avg(salary) into Result from guyuan where

department_id = v_dept_sal group by department_id;
return (Result);
end avgersal;
--第二步：调用函数：
declare
v_dept_id number(5):=30;
sal number(7,2);
begin
sal := avgersal(v_dept_id);
dbms_output.put_line('部门30的人员平均工资：'||sal);
end;

查询某人的记录：
declare
c_tax_rate number(3,2):=0.03;
--用户名
v_name varchar2(10);
v_sal number(7,2);
v_tax_sal number(7,2);
begin
--执行
select fast_name,salary into v_name,v_sal from guyuan

where employee_id = 1005;
--计算所得税
v_tax_sal:=v_sal*(c_tax_rate);
dbms_output.put_line('姓名 '||v_name||' 工资 '||v_sal||' 交税 '||v_tax_sal);
end;


PL/SQL创建存储过程
创建过程：

create table mytest(name varchar2(30),password varchar(30));
create or replace procedure sp_pro1 is
begin
insert into procedures values (2,'验证过程','服务器');
end;（or replace表示替换原来的过程）

显示错误：
show error;

调用过程：
exec sp_pro1;
call sp_pro1;

删除过程：
create or replace procedure sp_pro2 is
begin
--执行部分
delete from procedures where pro_id='2';
end;

含有异常处理的存储过程：
declare
--定义变量
v_name varchar2(30);
v_address varchar2(30);
begin
--执行部分
select pro_name,pro_address into v_name,v_address from procedures where pro_id=2;
--在控制台显示用户名
dbms_output.put_line('存储过程名称：'||v_name ||'存在位置：'||v_address);
--异常处理
exception
when no_data_found then
dbms_output.put_line('编号不存在。');
end;

--users表的查找存储过程
create or replace procedure users_pro(uid in number,uname out varchar2,uage out n
umber) is
begin
select name,age into uname,uage from users where id = uid;
end;
--调用users_pro存储过程--
declare
user_id number;
user_name va

java开发中的总结 大家可以看看哦

rchar2(20);
user_age number;
begin
users_pro(1,user_name,user_age);
dbms_output.put_line(user_id||'姓名：'||user_name||' '||'年龄：'||user_age);
end;


---作业调度---
给用户的授权语句：
grant execute on dbms_job to scott;



---游标---
第一类游标：
弱类型游标
declare
type cursor_type is ref cursor;
cur cursor_type;
eguyuan guyuan%rowtype;
begin
open cur for
select * from guyuan;
loop
fetch cur into eguyuan;
exit when cur%notfound;
dbms_output.put_line(eguyuan.employee_id||eguyuan.fast_name);
end loop;
close cur;
end;

第二类游标：
--强类型游标--
declare
type record_type is record(employee_id number,fast_name varchar2(30));
type cursor_type is ref cursor return record_type;
rec record_type;
cur cursor_type;
begin
open cur for
select employee_id,fast_name from guyuan;
loop
fetch cur into rec;
exit when cur%notfound;
dbms_output.put_line('雇员号 '||rec.employee_id||' 员工名 '||rec.fast_name);
end loop;
close cur;
end;

---查找实例---
declare
cursor users_cur(did number) is
select * from guyuan where department_id = did;
employees users_cur%rowtype;
begin
for employees in users_cur(&雇员部门)
loop
dbms_output.put_line(employees.department_id||employees.fast_name||employees.job_id)
close users_cur;
end;

-----和jsp实现交互的功能的模块代码----
功能：实现从数据库中users表中查询数据并在jsp页面输出。
create or replace package user_pkg is
第一步：创建package
--用来装载一个user_cur的游标
type user_cursor is ref cursor;
end user_pkg;

第二步：
---创建一个users的存储过程
---插入的存储过程---
declare
uid number(10);
uname varchar2(20);
uage number(3);
begin
insert_pro(uid,uname,uage);
end insert_pro;
----查找的存储过程---
create or replace procedure select_pro(uid in number,uname out varchar2,uage out number) is
begin
select name,age into uname,uage from users where id = uid;
end select_pro;
----定义一个含有游标的存储过程---
create or replace procedure select_pro2(user_cur out user_er_cur,uid in number) is
begin
open user_cur for
select * from users where id = uid;
end select_pro2;

--实现类的方法----
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;

public class OracleConnection {
public static void main(String[] args) {

DBConnection db = new DBConnection();
Connection conn = db.getConnection();
CallableStatement cst = null;
ResultSet rs = null;

try {
/**
*
cst = conn.prepareCall("{call insert_pro(?,?,?)}");
cst.setInt(1, 6);
cst.setString(2, &q
uot;lisi");
cst.setInt(3, 25);
int flag = cst.executeUpdate();
System.out.println(flag);
cst.registerOutParameter(1,oracle.jdbc.OracleT

java开发中的总结 大家可以看看哦

ypes.CURSOR );
cst.setInt(2, 24);*/


/**
cst = conn.prepareCall("{call select_pro(?,?,?)}");
cst.setInt(1, 2);
cst.registerOutParameter(2, java.sql.Types.VARCHAR);
cst.registerOutParameter(3, java.sql.Types.INTEGER);

cst.executeUpdate();
//System.out.println(cst.getInt(1));
System.out.println(cst.getString(2));
System.out.println(cst.getString(3));
*/


cst = conn.prepareCall("{call select_pro2(?,?)}");
cst.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);

cst.setInt(2, 2);
cst.setInt(2, 6);
//cst.setInt(2, 4);
cst.executeUpdate();
rs = (ResultSet) cst.getObject(1);

while(rs.next()){


System.out.println("id= "+rs.getInt(1));
System.out.println("name="+rs.getString(2));
System.out.println("age= "+rs.getInt(3));

}


} catch (SQLException e) {

e.printStackTrace();
}

}



小孙：
json的使用方法：
function shuchu(){
var j = {"城市名":"{"北京市":"昌平区","河北省":"保定市","天津市":"西城区"}};
alert(j.城市名.北京市);
alert(j.城市名.河北省);
}
注解：前面的是控件的名称，后面是值value。

--兰志琴代码--
create or replace trigger insunmoney
after update on customer
for each row
declare
-- local variables here
begin
insert into trade values(trade_seq.n
extval,:ame,'存款'
,abs(:new.money1-:old.money1),sysdate,'银行',
:new.money1,:old.money1,:new.baddress);
end insunmoney;

本文来源：https://www.bwwdw.com/article/nklm.html