---创建帐号
CREATE USER MADING
IDENTIFIED BY MADING
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;
GRANT CONNECT,DBA,RESOURCE,UNLIMITED TABLESPACE TO MADING;
COMMIT;
--创建序列SEQUENCE
CREATE SEQUENCE TEST_SEQ START WITH 1;
--创建表PERSON
CREATE TABLE PERSON(
ID INT NOT NULL PRIMARY KEY,
NAME VARCHAR2(10),
PASS VARCHAR2(10),
TYPEID INT
);
--查看表结构
/***
--MySQL查看表结构方法：
desc PERSON;
DESCRIBE PERSON;
SHOW COLUMNS FROM PERSON;
SHOW FULL COLUMNS FROM PERSON;
SHOW CREATE TABLE PERSON;
*/
desc PERSON;
describe PERSON;
DECLARE
XID NUMBER(10) := 2;
XNAME VARCHAR2(10) :='阿姆1';
XPASS VARCHAR2(10) :='密码1';
XTYPEID NUMBER(10) :=2;
BEGIN
UPDATE PERSON SET NAME=XNAME,PASS=XPASS,TYPEID=XTYPEID WHERE ID=XID;
IF SQL%NOTFOUND THEN
INSERT INTO PERSON(ID,NAME,PASS,TYPEID) VALUES(XID,XNAME,XPASS,XTYPEID);
END IF;
END;
/
--调用系统时间sysdate
SELECT SYSDATE FROM DUAL;
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') AS 系统时间 FROM DUAL;
SELECT TO_DATE('2009-06-09 14:57:53','YYYY-MM-DD HH24:MI:SS') AS 系统时间 FROM DUAL;
--INSERT INTO STUDENT(NAME,BIRTH) VALUES('AMU','2009-06-09 14:57:53');
SELECT * FROM PERSON WHERE BIRTH IS NOT NULL;
/***
BLOB 可存储二进制数据和字符数据
CLOB 只能存储字符数据，否则成乱码...
*/
---打开输出开关
SET SERVEROUTPUT ON
--向控制台输出一句话：ssss
EXEC DBMS_OUTPUT.PUT_LINE('SSSS');
declare
v_num number(3);
begin
select count(*) into v_num from person;
dbms_output.put_line('要输出到控制台的信息1');
dbms_output.put('要输出到控制台的信息2');
dbms_output.put_line('要输出到控制台的信息1');
dbms_output.put('要输出到控制台的信息2');
dbms_output.put_line('要输出到控制台的信息1');
dbms_output.put('要输出到控制台的信息2');
if v_num<>0 then
dbms_output.put_line('总数为：'||v_num);
end if;
end;
/
---将信息输出到控制台有下面两个方法：
exec dbms_output.put_line('要输出到控制台的信息');
execute dbms_output.put('要输出到控制台的信息');
DECLARE
total NUMBER(10):=1;
num NUMBER(10):=1;
BEGIN
LOOP
total := total*num;
num := num+1;
IF num>10 THEN
EXIT;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('10的阶乘：'||total);
END;
/
--查看控制台的错误信息使用下面命令
show error
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
oracle 常用命令
1.start d:\aa.sql;意思是执行一个SQL文件
||：把两个语句合并到一起
在做oracle测试的时候，可以用dual表做测试，这个是一个虚拟表，专门测试用的
数据类型
字符char(2000)类型，最长是2000.固定长度效率高
字符varchar2(4000)，最长是4000，可变长度
字符clob最大的字符类型
数字类型就一个,小数和整数都一样
number 总大小10的38次方
也可以表示小数number(5,2)表示有5位有效数两位小数三位小数
number(5)五位整数大小是-99999到99999
日期类型
date 包含年月日和时分秒
timestamp时间戳，这个是oracle对时间的扩展
多媒体数据类型，二进制数据，可存放图片视频
blob
------------------------------
对表的操作-----------
修改表-添加列
alter table tableName add(columnName number(2));
修改数据大小
alter table tableName modify(columnName varchar2(20));
删除一个字段
alter table student drop column columnName;
修改表的名字
rename tableName to newTableName;
删除表
drop table tableName
oracle 时间默认格式是，日，月，年如（14-3月-2010）
如果想修改日期格式可以用-----------
alter session set nls_date_format='yyyy-mm-dd';
--------------------------------------
让操作都显示时间命令：set timing on;
过滤重复数据语法------------------
select distinct columnName from tableName;
判断一列是不是空--------------
nvl(columnName,0);意思是，如果这列如果是空，那么就给0做默认值
通配符--------------------
%：表示任意0到多个的字符
_:表示1个字符
判断这列是否是空用---------------
is null
多表查询---------------------
select name1,name2,name3 from table1 a,table2 b where a.id=b.id
分页-----------------------------
查询一张表的6到10条记录
select * from (select u.*,rownum rn from (select * from sa_user) u where rownum<=10) where rn>=6;
************如果指定查询列或排序或分组。。等，那么只需修改最里层的子查询语句
查询出来的数据创建一个新表------------------
create table newTableName(id,name) as select id,name from tableName;
集合筛选-------
union(取两个结果集的并集，会自动去除重复行)
union all(取两个结果集的并集，不会去除重复行)
intersect(取两个结果集的交集)
minus(取两个结果集的差)
oracle连接JAVA-------
驱动字符串："oracle.jdbc.driver.OracleDriver"
连接字符串："jdbc:oracle:thin:@127.0.0.1:databaseName"
oracle对数据操作------------------------函数
日期格式化操作函数-to_date例如
to_date('1989-12-12','YYYY-mm-dd')
sysdate 获取系统当前时间
add_months(sysdate,2) 添加月数函数，这句话的意思是在当前系统时间在加上2个月，例如select add_months(sysdate,2) from dual;
--------字符串函数-------------
lower(char):将字符串转化为小写格式
upper(char):将字符串转换成大写格式
length(char):返回字符串长度
substr(char,m,n):取字符串的子串m代表从第几个字符取n代表取几个字符
replace(char,search_string,replace_string):替换字符串
------------数学函数---------------------------------------------
round(n,[m])该函数用于执行四舍五入，如果省掉m，则四舍五入到整数，如果m是正数，则四舍五入到小数点的m位后，如果m是负数，则四舍五入到小数点的m位前，（也就是保留几位小数）
trunc(n,[m])该函数用于截取数字，如果省掉m，就截去小数部分，如果m是正数就截取到小数点的m位后，如果m是负数，则截取到小数点的前m位
mod(m,n) 求余，例如mod(10,3)结果是1；相当于10%3
floor(n)返回大于或是等于n的最大整数
ceil(n)返回大于或是等于n的最小整数
--------
abs(n) 返回数字的绝对值
acos(n) 返回数字的反余弦值
asin(n) 返回数字的反正旋值
atan(n) 返回数字的反正切
cos(n)
exp(n) 返回对数值
power(m,n) 返回m的n次幂
批量插入------------------
insert into table1(name,age) select name1,age1 from table2
批量修改----------------------
update tableName set(name,age)=(select name,age from tableName where name='zhangSan')
事务-----------------------------------------
设置保存点 ---------------注意如果commit之后所有的保存点全部无效
savepoint pName;
回滚到保存点
rollback to pName;
只读事务------------------
set transaction read only;(设置只读事务)
类型转换函数----------------to_char.还有很多查
日期方面的：
yy:两位数字的年份 例如：2004---》04
yyyy:四位数字的年份 2004年
mm:两位数字的月份例如：8月---》08
dd:2位数字的天 例如：20号---》30
hh24:8点--》20
hh12:8点--》08
mi,ss-->显示分\秒
数字转换
货币转换 to_char(800.00,'L99999.99') 转换后的结果是RMB800.00(注意L（可变的）是代表本地显示方式99999.99是代表5位整数2位小数的数字)
9：显示数字，并忽略前面0
0：显示数字，如位数不足则用0补齐
.：在指定位置显示小数点
，：在指定位置显示逗号
$:在数字前加美元符号
L：在数字前加本地货币符号
C:在数字前加国际货币符号
G:在指定位置显示组分隔符、
D：在指定位置显示小数点符号（.）
系统函数
sys_context
约束---------------
create table tableName(id char(8) primary key,age number(3) check(age>0)); //主键和检查约束
create table tableName(name varchar2(30) not null,email varchar2(50) unique,sex char(2) default '男' check(sex in('男','女'))//非空，唯一，默认值，检查约束
添加约束---------------
alter table tableName modify name not null;//添加列name不能为空
alter table tableName add constraint checkName unique(name); //给name加上唯一约束
删除约束------------
alter table tableName drop constraint 约束名称; //删除约束
删除主键------------特别注意哦
alter table tableName drop primary ke cascade; //必须加上cascade
索引-------------
1：单列索引（单列索引是基于单个列所建立的索引）
create index 索引名 on 表名（列名）
2：复合索引（复合索引是基于两列或多列的索引。在同一张表上可以有多个索引，但是要求列的组合必须不同）
create index 索引名 on 表名(列名，列名。。);
PLSQL编程-----------show error;可以显示错误的详细信息
创建一个最简单存储过程插入一条数据
create or replace procedure 存储过程名字 is
begin
--这里写SQL语句
insert into tableName values(值);
end;
调用存储过程-------------
第一种：exec 过程名字(参数1，参数2);
第二种：call 过程名(参数1，参数2);
---打开输出开关------------------------------
SET SERVEROUTPUT ON
--向控制台输出一句话：ssss
EXEC DBMS_OUTPUT.PUT_LINE('SSSS');
----------------简单的模块编写
DECLARE
v_name varchar2(50);--声明一个变量名
begin
select USER_NAME INTO v_name FROM SA_USER U WHERE U.USER_ID=&abc;--把查询出来的值赋给v_name，&abc意思是让客户输入值
dbms_output.put_line('名字是：'||v_name);
exception --异常的处理
when no_data_found then --如果抛出no_data_found异常（没有找到值异常）
dbms_output.put_line('没有找到这个人');--这里做相应的处理
end;
--创建一个简单可以接参数的存储过程
create or replace procedure sp_pro1(userId number,userName varchar2) is
begin
update SA_USER U SET U.User_Name=userName where u.user_id=userId;
end;
----------------------------创建一个方法，这个方法是通过ID找到这个人的名字
create or replace function F_GET_USER_NAME(userId number) return VARCHAR2 is userName VARCHAR2(50);
begin
select u.user_name into userName from SA_USER U WHERE U.USER_ID=userId;
return userName;
end;
调用这个方法
var u_name varchar2(50); //声明一个方法，来接受这个方法的返回值
call f_get_user_name(1) into:u_name;//调用这个方法
--------------------------创建一个包
create package sp_package is
procedure update_sal(name varchar2,newsal number);
function annual_income(name varchar2) return number;
end;/
-----------------------创建sp_package 包的包体
create package body sp_package is
procedure update_sal(name varchar2,newsal number)
is
begin
update emp set sal=newsal where ename=name;
end;
function annual_income(name varchar2)
return number is
annual_salary number;
begin
select sal into annul_salary from emp where ename=name;
return annual_salary;
end;
/
--------------------调用这个包下面的存储过程
sp_package.update_sal(arg1,arg2);
------------------------------------变量类型-oracle赋值是用（:=）
标量类型（scalar） （主要是存放单个值的）
参考类型
user.name%type 表示这个类型是user表字段name的类型
定义一个变长字符
v_name varchar2(0);
定义一个小数 范围-9999.99~9999.99
v_money number(6,2);
定义一个小数并给一个初始值为5.4
v_money number(6,2):=5.4
定义一个日期类型的数据
v_biredate date;
定义一个布尔变量，不能为空，初始值为false
v_valid boolean not null default false;
复合类型（composite）主要是存放多个值的
pl/sql记录
--创建一个pl/sql记录
declare
--创建了一个e_user类型
type e_user is record(u_id sa_user.user_id%type,u_name sa_user.user_name%type,u_age sa_user.user_age%type,u_date sa_user.user_birthday%type);
my_user e_user;--定义一个e_user变量
begin
select u.user_id,u.user_name,u.user_age,u.user_birthday into my_user from sa_user u where u.user_id=&v;
dbms_output.put_line('你查询的用户信息是'||my_user.u_name);
end;
pl/sql表（相当于一个数组）
--创建一个array_names集合类型，里面可以放sa_user.user_name%type类型
declare
type array_names is table of sa_user.user_name%type index by binary_integer;
sa_user_names array_names;--声明一个array_names类型的变量
begin
select u.user_name into sa_user_names(0) from sa_user u where u.user_id=&n;--给这个变量赋值
dbms_output.put_line('用户名是'||sa_user_names(0));
end;
嵌套表
varray
参照类型（reference）
游标----------------------
--游标的使用
declare
--定义一个游标类型
type sp_user_cursor is ref cursor;
--声明一个sp_user_cursor类型的游标
my_cursor sp_user_cursor;
u_name sa_user.user_name%type;--定义一个存放用户名变量
u_age sa_user.user_age%type;--存放用户年龄的变量
begin
--打开游标-并给游标赋值
open my_cursor for select u.user_name,u.user_age from sa_user u;
--循环取出游标
loop
fetch my_cursor into u_name,u_age;
--判断游标是否为空，如果是空，那么就跳出循环
exit when my_cursor%notfound;
--打印数据
dbms_output.put_line('用户名'||u_name||'年龄'||u_age);
end loop;
--关闭游标
close my_cursor;
end;
-----------------------------------------------
lob(large object)
控制语句---------------------------------------------------------------------
if then语句练习（创建一个存储过程，接受一个人的ID如果这个人的年龄小于50那么就把他增加到50）
create or replace procedure sp_pro2(user_id number) is
v_age sa_user.user_age%type;
begin
select u.user_age into v_age from sa_user u where u.user_id=user_id and rownum=1;
if v_age<50 then
update sa_user u set u.user_age=50 where u.user_id=user_id;
end if;
end;
if elsif语句--------------------------------------------------------
create or replace procedure sp_pro3(u_id number) is
begin
if u_id=1 then
update sa_user u set u.user_age=10;
elsif u_id=2 then
update sa_user u set u.user_age=20;
else
update sa_user u set u.user_age=30;
end if;
end;
循环语句----连续查询10条语句
create or replace procedure sp_pro3(v_name varchar2) is
v_index number:=14;
begin
loop
insert into sa_user values(v_index,'lixiang',22,'2-3月-2010');
exit when v_index=20;
v_index:=v_index+1;
end loop;
end;
while循环----------------
create or replace procedure sp_pro4(v_name varchar2) is
v_index number:=21;
begin
while v_index<40
loop
insert into sa_user values(v_index,v_name,22,'2-3月-2010');
v_index:=v_index+1;
end loop;
end;
创建带参数的存储过程--------------------------------------
create or replace procedure sp_pro4(u_id number,u_name out varchar2,u_brithday out date) is
begin
select u.user_name,u.user_birthday into u_name,u_brithday from sa_user u where u.user_id=u_id;
end;
创建带包的存储过程，并能返回多条记录
--创建一个包
create or replace package testPackage as
type my_cursor is ref cursor;--包里面创建了一个游标
end;
--用这个游标查询出所有用户的名字
create or replace procedure sp_pro5 is
userInfo_cursor testPackage.my_cursor;//定义一个游标变量
u_name sa_user.user_name%type;
begin
open userInfo_cursor for select u.user_name from sa_user u;--给游标赋值
loop--循环打印游标
fetch userInfo_cursor into u_name;
exit when userInfo_cursor%notfound;如果没有数据就跳出循环
dbms_output.put_line('用户名：'||u_name);
end loop;
close userInfo_cursor;--关闭游标
end;
创建视图--------------------------------------([with read only]可选加上代表这个视图是只读的)
create or replace view 视图名 as select语句 [with read only]
删除视图
drop view 视图名
----------分页存储过程
create or replace procedure sp_fenye
(tableName varchar2,--从那张表取数据
page_size number,--每页有多少数据
current_index number,--当前第几页
rowCounts out number,--一共有多少条数据
user_cursor testpackage.my_cursor;--游标，存放查询出来的数据
pageCount out number--可以分多少页
) is
v_sql varchar2(1000);
v_begin number:=(current_index-1)*page_size+1;
v_end number:=current_index*page_size;
begin
v_sql:='select * from (select t.*,rownum rn from (select * from '||tableName||') t where rownum<='||v_end||') where rn>='||v_begin;
open user_cursor for v_sql;--给游标赋值
--查询出一共有多少条记录
v_sql:='select count(*) from '||tableName;
execute immediate v_sql into rowCounts; --马上执行这句语句
--计算出可以分多少页
if mod(rowCounts,page_size)=0 then
pageCount:=rowCounts/page_size;
else
pageCount:=rowCounts/page_size+1;
end if;
close user_cursor;--关闭游标
end;
-------------------------查询有重复数据的行
SELECT LOWER(TRIM(A.CITY_ID)) CITY_ID FROM SB_CITY A GROUP BY LOWER(TRIM(A.CITY_ID)) HAVING COUNT(1)>1;
