--存储过程,循环
create or replace procedure delTables(ename t_emp.ename%TYPE)
AS
con number;
i NUMBER := 1;
tablename USER_TABLES.TABLE_NAME%TYPE;
BEGIN
select count(TABLE_NAME) into con from USER_TABLES where last_analyzed > to_date('2014/1/17 00:00:00','yyyy/mm/dd hh24:mi:ss');
while i<con
loop
DBMS_OUTPUT.put_line('循环 '||i||' 删除表 ');
if i=1 then
-- drop table user_session_online;--此存储过程loop里不可写sql语句
DBMS_OUTPUT.put_line('删除表 '||' '||ename);
end if;
i := i + 1;
end loop;
end;
begin
delTables('sdf');
end;
--游标,循环
declare
cursor mycur is select 'drop table '||TABLE_NAME ||';' from USER_TABLES where last_analyzed > to_date('2014/1/1 00:00:00','yyyy/mm/dd hh24:mi:ss');
sqlStr varchar2(100);
longStr varchar2(10000);
i NUMBER :=0;
begin
open mycur;
fetch mycur into sqlStr;
while (mycur%found) -- 判断是否有数据被发现
loop
i := i+1;
dbms_output.put_line('编号: '||i||' '|| sqlStr);
longStr := longStr||' '||sqlStr;
--drop table E_PORTALDOCUMENT;--游标里遍历数据时不能写sql语句
if i=10 then
dbms_output.put_line('拼接 '|| longStr);--拼接字符串超出缓冲区
end if;
fetch mycur into sqlStr;--修改游标，继续向下
end loop;
end;
select * from t_emp
--带参数的游标 不能输入字符 end后要加;
declare
empno t_emp.empno%TYPE;
ename t_emp.ename%TYPE;
cursor emp_cur(dep t_emp.empno%TYPE) is select empno,ename from t_emp where empno = dep;
begin
empno := &empno2;
DBMS_OUTPUT.PUT_LINE(empno);
open emp_cur(empno);
loop
fetch emp_cur into empno,ename;
exit WHEN emp_cur%notfound;
DBMS_OUTPUT.PUT_LINE(empno || ' ' || ename);
end loop;
close emp_cur;
end;
--REF 游标 参数如果为字符串
declare
TYPE refcur_t is ref cursor;
refcur refcur_t;
selection number(1);
emp t_emp%ROWTYPE;
begin
selection := &sel;
dbms_output.put_line(selection);
if selection = 0 then
open refcur for select * from t_emp where empno = 4;
elsif selection = 1 then
open refcur for select * from t_emp where empno = 2;
else
dbms_output.put_line('***请输入0或1***'||emp.ename);
open refcur for select * from t_emp where 0 = 1;
end if;
loop
fetch refcur into emp;
exit when refcur%notfound;
dbms_output.put_line('******'||emp.ename);
end loop;
close refcur;
end;
begin
for k in 1..10 loop
dbms_output.put_line(k);
end loop;
for k in reverse 1..10 loop
dbms_output.put_line(k);
end loop;
end;
declare
i binary_integer := 1;
begin
loop
dbms_output.put_line(i);
i := i + 1;
exit when ( i >= 11);
end loop;
end;
declare
j binary_integer := 1;
begin
while
j < 11
loop
dbms_output.put_line(j);
j := j + 1;
end loop;
end;
create table emp (empno number(4),ename varchar2(10),job varchar2(9),mgr number(4),hiredate date,sal number(7,2),comm number(7,2),deptno number(2));
--员工编号，姓名，职位，领导编号，雇佣日期，工资，奖金，部门编号
select avg(mgr) from emp
--函数只能返回唯一值
create or replace function myFunction(nm varchar) return varchar
as
mg varchar(8);
begin
select job into mg from emp where ename = nm;
return mg;
end;
select myFunction('xmh1') from emp;
select avg(mgr) from emp where ename ='xmh1'
create or replace function myFunc(nm NUMBER) return NUMBER
as
mg NUMBER;
begin
select empno into mg from emp where mgr=2;
return mg;
end;
select distinct myFunc(1) from emp;
select object_name,status from user_objects where object_type='FUNCTION';
declare
eno emp.empno%TYPE;
empInfo emp%ROWTYPE;
begin
eno :=&en;
select * into empInfo from emp where empno = eno;
DBMS_OUTPUT.put_line('雇员编号：'||empInfo.empno);
DBMS_OUTPUT.put_line('雇员姓名：'||empInfo.ename);
end;
--游标
declare
mg emp.mgr%TYPE;
cursor mycur is select * from emp where MGR = mg;
empInfo emp%ROWTYPE;--变量 定义类型 把一行的数据都装进来
cou NUMBER;
empnum emp.empno%TYPE;
BEGIN
empnum := &empnum;-- 输入框 输入参数
mg := &mg;
for empInfo in mycur LOOP--在游标里循环 获取empInfo
cou := mycur%ROWCOUNT;
DBMS_OUTPUT.put_line(cou||'雇员编号：'||empInfo.empno||' '||empnum);
if empInfo.job='job3' then
DBMS_OUTPUT.put_line(cou||'雇员姓名：'||empInfo.ename);
end if;
END LOOP;
END;
declare
cursor mycur is select * from emp;
empInfo emp%rowtype;
begin
open mycur;
fetch mycur into empInfo;
while (mycur%found) loop -- 判断是否有数据被发现
dbms_output.put_line('编号: '|| empInfo.empno);
fetch mycur into empInfo;--修改游标，继续向下
end loop;
end;
declare
cursor mycur is select * from emp;
empInfo emp%rowtype;
rown number;
begin
open mycur;
fetch mycur into empInfo;
if (mycur%found) then -- 判断是否有数据被发现
DBMS_OUTPUT.put_line(rown||'编号: '|| empInfo.empno);
fetch mycur into empInfo;
end if;
end;
--存储过程 sqlplus 用 exec 执行过程
create or replace procedure myproc(eno emp.empno%TYPE,enm emp.Ename%TYPE,job emp.job%TYPE,mgr emp.mgr%TYPE)
AS
con NUMBER ;
BEGIN
select count(empno) into con from emp where empno = eno;
if con = 0 then
insert into emp(empno,ename,job,mgr) values (eno,enm,job,mgr);
DBMS_OUTPUT.put_line('插入成功！');
else
DBMS_OUTPUT.put_line('已存在！');
end if;
end;
select count(empno) from emp where empno =0;
select * from emp;
begin
myproc(05,'xmh5','business',1);
end;
--exec myproc(05,'xmh5','business',1);
create or replace procedure myproc
as
i number;
begin
i:=100;
DBMS_OUTPUT.put_line('i= '||i);
end;
create or replace procedure myproc(eno emp.empno%TYPE)
as
i number;
begin
--insert into EMP (empno) values (eno);
--delete from emp where empno=eno;
update emp set ename = 'xmh333' where empno=eno;
--select empno into i from emp where empno=eno;
--DBMS_OUTPUT.put_line('OUTPUT: '||i);
end;
begin
myproc(12);
end;
(05,'xmh5','business',1)
SELECT * FROM EMP;
declare
m number(6);
begin
m:=&请输入一个数字作为变量m的值;
dbms_output.put_line(m);
end;
--触发器
create or replace trigger tr_src_emp
before insert or update or delete on emp
begin
if to_char(sysdate,'DY')in('星期四','星期六')then
raise_application_error(-20001,'fail');
end if;
end;
insert into emp (empno) values (12);
select username,default_tablespace from user_users
select * from all_users;
select * from all_tables where tablespace_name like 'TEST_DATA'
select * from all_tables where table_name like '%ULTRA%';
select * from ULTRA_XMH_TEST;
select * from user_sys_privs;
select * from dba_sys_privs
select count(*),min(id),username from test group by username;
select * from t where id in (select a1.id from test a1,test a2 where a1.id>a2.id and a1.username = a2.username)
select a2.*,a1.* from test a1,test a2 where a1.username = a2.username;--自连接
select * from test t1 left join test_f t2 on t1.fid = t2.id where t2.id is null--左外连接
alter table customer_classify_info add classify varchar2(32) --添加列
alter table test add (mobile varchar2(20),address varchar2(20))
alter table customer_classify_info drop (sell_channel) --删除列
alter table mytest drop column mobile;
alter table customer_accessory_info modify auth_papers_file varchar2(256) -- 修改列类型
alter table syn_user rename column IFUSERLEVEL to LFUSERLEVEL --修改列名
alter table test add constraint pk primary key (id)--追加主键约束
alter table test add constraint uk unique (password)--追加唯一性约束
alter table test add constraint fk foreign key (fid) REFERENCE test_f(id);--追加外键
alter table test add constraint ck check(age between 20 and 33)--追加check约束
alter table test add check(password <> '1')
create index myindex on test(username) --追加索引
alter table test drop constraint SYS_C008562 --删除各种约束
drop index myindex --删除索引
alter table test add primary key (id) using index
alter table test modify password unique
--查看约束名 主键约束、唯一性约束
select cu.* from user_cons_columns cu, user_constraints au where
cu.constraint_name = au.constraint_name and au.constraint_type = 'C' and au.table_name = 'TEST'
select * from user_cons_columns
select * from user_constraints
select * from all_tables where table_name like '%USER%';--查询所有table
--拼接sql语句
select 'drop table '||TABLE_NAME||';' from all_tables where last_analyzed > to_date('2014/1/17 00:00:00','yyyy/mm/dd hh24:mi:ss')
--取差集的形式 找出排序后的指定某一项
select * from (select * from emp order by empno desc) where rownum<=3
minus
select * from (select * from emp order by empno desc) where rownum<=2
SELECT * FROM ALL_TABLES where owner = 'IAM20130726' AND table_name like '%E/_USER%' escape '/'
select * from org_subacc where login_name='xmh'
union all
select * from org_subacc where login_name='Guest'//union all
select sum(empno) from EMP ;//sum函数
select * from emp where LOWER(ename) = lower('XmH3');//lower函数
alter table E_ElecProcessRes add primary key (UUID) using index//不知道约束名
select app.resnum,acc.login_name,acc.accountid from e_oper_authz_resacc_rel oAccRel
left join org_subacc acc
on acc.subacc_uuid = oAccRel.resaccuuid
left join e_appaccount ea
on ea.subacc_uuid = acc.subacc_uuid
left join e_appresource app
on app.resuuid = acc.resuuid
left join org_person p
on oAccRel.Personuuid = p.person_uuid
WHERE p.login_name = 'xmh'
select SYN_TIME from (select SYN_TIME from syn_info order by SYN_TIME desc) where rownum = 1
select * from sms_orderbill where createtime between to_date('2013-01-03','yyyy-mm-dd') and to_date('2013-01-04','yyyy-mm-dd');
select * from sms_orderbill where userid > '2013-01-05 02:00:06'
select * from test where ( username is null or username like '% %' ) and id = '3'
ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。
//查主键
select cu.* from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = 'P' and au.table_name = 'TEST'
1、查找表的所有索引（包括索引名，类型，构成列）：
select t.*,i.index_type from user_ind_columns t,user_indexes i where t.index_name = i.index_name and t.table_name = i.table_name and t.table_name = 要查询的表
2、查找表的主键（包括名称，构成列）：
select cu.* from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = 'P' and au.table_name = 要查询的表
3、查找表的唯一性约束（包括名称，构成列）：
select column_name from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = 'U' and au.table_name = 要查询的表
4、查找表的外键（包括名称，引用表的表名和对应的键名，下面是分成多步查询）：
select * from user_constraints c where c.constraint_type = 'R' and c.table_name = 要查询的表
查询外键约束的列名：
select * from user_cons_columns cl where cl.constraint_name = 外键名称
查询引用表的键的列名：
select * from user_cons_columns cl where cl.constraint_name = 外键引用表的键名
5、查询表的所有列及其属性
select t.*,c.COMMENTS from user_tab_columns t,user_col_comments c where t.table_name = c.table_name and t.column_name = c.column_name and t.table_name = 要查询的表
--创建临时表空间
create temporary tablespace test_temp
tempfile 'E:\oracle\product\10.2.0\oradata\orcl\test_temp01.dbf'
size 32m
autoextend on
next 32m maxsize 2048m
extent management local;
--创建数据表空间
create tablespace test_data
logging
datafile 'E:\oracle\product\10.2.0\oradata\orcl\test_data01.dbf'
size 32m
autoextend on
next 32m maxsize 2048m
extent management local;
--创建用户并指定表空间
create user xmh identified by xmh
default tablespace test_data
temporary tablespace test_temp;
（1）创建用户
Create user 用户名 identified by 密码；（如果是纯数字则要加双引号”654321”，如果是字母就不用）
（2）授权给某个用户
Grant connect,resource to 用户名；(只有用户有了connect 和 resource后才能操作其他表)
（3）授DBA 权限化
Grant dba to 用户名；
（4）给用户创建会话的权限：
grant create session to DB_USER
（5）撤权：
revoke 权限... from 用户名;
（6）删除用户：
drop user username cascade （cascade 保证彻底删除）