Oracle语句集锦
创建用户并赋予dba权限
1）进入cmd
2）sqlplus / as sysdba 或者 sqlplus sys/密码 as sysdba
SQL> conn sys/wcq123@orcl as sysdba;
SQL> create user wcq identified by wcq123;
SQL> grant dba to wcq;
--序列
select your_seq_name.nextval from dual;
select your_seq_name.currval from dual
SQL>sqlplus use_name/pass_word
--默认用户/密码
scott/tiger
system/manager
sys/change_on_install
--显示用户名
SQL>show use_name
--切换用户
SQL>connect use_name/pass_word
--切换到SYS
SQL>connect /as sysdba
--关闭数据库
SQL>shutdown
--打开数据库
SQL>startup
--参数视图
SQL> desc v$parameter;
--查看用户表
SQL> desc user_tables;
--查看数据文件
SQL> SELECT NAME FROM V$DATAFILE;
--查看日志文件
SQL> DESC V$LOGFILE;
SQL> SELECT * FROM V$LOGFILE;
--查看控制文件
SQL> select * from v$controlfile;
--查看数据块的大小
SQL> show parameter db_block_size
SQL> select name,value from v$parameter where name='db_block_size';
--参数文件的位置
---D:/oracle/product/10.1.0/db_1/dbs
--查看表空间
SQL> desc v$tablespace;
SQL> select tablespace_name from dba_tablespaces;
--查看表空间对应的文件
SQL>select file_name,tablespace_name from dba_data_files
--创建表空间
SQL> create tablespace whx datafile 'D:/ORACLE/PRODUCT/10.1.0/ORADATA/WHX/whx1.ora' size 1m;
--增加一个表空间对应的文件
SQL> alter tablespace whx add datafile 'D:/ORACLE/PRODUCT/10.1.0/ORADATA/WHX/whx2.ora' size 1m;
--查看实例
SQL> select instance_name from v$instance;
--创建用户
SQL>create user use_name identified;
--删除用户
SQL>drop user use_name;
--为用户赋权
SQL>grant role1,role2,role3 to use_name;
默认的角色:
connect/登陆的权限
resource/修改的权限
dba/管理员权限
--T5
--查看角色
SQL>select * from dba_roles;
--创建角色
create role whx;
--表共享锁
lock table table_name in share mode;
--表排它锁
lock table table_name in exdusive mode;
--行排它锁
select * from table_name for insert/updata/delete...;
--整体拷贝记录
SQL>create table table_name as select * from use_name.table_name;
--创建序列
create sequence myid
2 increment by 1 //步长
3 start with 1 //起始值
4 nomaxvalue //最大值
5 nominvalue //最小值
6 nocycle //
7 cache 10 //缓存个数
8 order //是否排序
--引用序列
SQL>insert into students values(nextval(myid),'jack');
--创建视图
SQL> create or replace view emp_view as
2 select empno,ename,dname from emp,dept
3 where emp.deptno=dept.deptno;
--把SQL的执行时间打开
SQL> set timing on
--杀进程
SQL> alter system kill session '151,16';
--创建B树索引
SQL> create index emp_id on emp(empid);
--创建位图索引
SQL> create bitmap index emp_m on emp(m);
--创建函数索引
SQL> create index emp_name on emp(orwer(ename));
--创建反向索引
SQL> create index emp_idx4 on emp(ename) reverse;
--查询索引列
SQL> desc user_ind_columns;
--使用族
create cluster cluster_test(name varchar2(20));
create table test(name varchar2(20)) cluster cluster_test(name);
create index idx_test on cluster cluster_test;
insert into test values('abc');
--打开EXPLAIN
@ORACLE_HOME/rdbms/admin/utlxplan.sql
SQL> set autotrace on explain;
--T6
SQL>set serveroutput on;
SQL>dbms_output.put_line(变量);
Mod(被除数,除数)--求余数
power(原数,指数)--求幂
SQL>dbms_output.put_line(mod(5,3));
SQL>dbms_output.put_line(power(5,3));
--IF ELSIF ELSE语句
/*
if 条件1 then
语句;
elsif condition2 then
Statements_2;
else
Statements_3;
end if;
*/
CASE语句/相当于SWITCH
/*
Case 变量
WHEN 值1 then 语句1
WHEN 值2 then 语句2
WHEN 值3 then 语句3
WHEN 值4 then 语句4
ELSE 语句5
END CASE;
Case
WHEN 变量>值1 then 语句1
WHEN 变量<值2 then 语句2
WHEN 变量<>值3 then 语句3
WHEN 变量!=值4 then 语句4
ELSE 语句5
END CASE;
*/
--loop/相当于do while
/*
LOOP
statements
EXIT WHEN CONDITION
END LOOP;
*/
--WHLIE循环
/*
WHILE condition
LOOP
statements
END LOOP;
*/
--FOR循环
/*
FOR counter IN [REVERSE] start_range..end_range
LOOP
statements;
END LOOP;
*/
--GOTO语句
/*
GOTO MY_LABEL;
…;
…;
<< MY_LABEL >>
…;
(从外层跳转到内层是非法的)
*/
--存储过程异常部分
/*
EXCEPTION
WHEN excep_name1 THEN
…
WHEN excep_name2 THEN
…
WHEN OTHERS THEN
…
END;
/
*/
--匿名存储过程
/*
DECLARE
MYEXCEPTION EXCEPTION;
PRAGMA EXCEPTION_INIT(MYEXCEPTION,-00001);
BEGIN
INSERT INTO EMP(EMPNO) VALUES(1234);
COMMIT;
DBMS_OUTPUT.PUT_LINE('EXECUTE OK');
EXCEPTION
WHEN MYEXCEPTION THEN
raise_application_error(-20002,'Can not insert the sample values');
END;
/
*/
--------------------------------------------------------------------------------
--T7
--缺省值函数
nvl(数值函数,0)
--查找错误
SQL>show errors
--游标
--数据类型
create or replace type person as object
( first_name varchar2(100),
last_name varchar2(100) )
/
create or replace type employee as object
(
name person,empno number,hiredate date,sal number,commission number,
member function total_compensation return number,
map member function convert return number,
static function new(p_empno number,p_person person) return employee)
/
create or replace type body employee as
member function total_compensation return number is
begin
return nvl(self.sal,0) + nvl(self.commission,0);
end;
static function new(p_empno number,p_person person) return employee is
begin
return employee(p_person,p_empno,sysdate,10000,null);
end;
map member function convert return number is
begin
return self.empno;
end;
end;
/
declare
l_employee1 employee;
l_employee2 employee;
begin
l_employee1 := employee.new(12345,null);
l_employee2 := employee.new(67890,null);
if l_employee1= l_employee2 then
dbms_output.put_line('They are equal');
end if;
if l_employee1 > l_employee2 then
dbms_output.put_line('employee 1 is greater');
end if;
if l_employee1 < l_employee2 then
dbms_output.put_line('employee2 is greater');
end if;
end;
/
---------------------------------------------------------------------------------------------------------------
declare
l_employee employee:=employee.new(123,null);
v_sal number;
begin
l_employee.sal:=1000;
l_employee.commission:=250;
v_sal:=l_employee.total_compensation;
dbms_output.put_line(v_sal);
end;
/
-------------------------------------------------------------------------------------------------------------
--嵌套表
create type animal_ty as object
( breed varchar2(25),
name varchar2(25),
birthdate date );
/
create type animals_nt as table of animal_ty;
/
create table breeder
( breedername varchar2(25),
animals animals_nt)
nested table animals store as animals_nt_tab;
insert into breeder values('mary',
animals_nt(animal_ty('dog','butch',to_date('1997-3-31','yyyy-mm-dd')),
animal_ty('dog','rover', to_date('1997-3-31','yyyy-mm-dd')),
animal_ty('dog','julio',sysdate)));
insert into breeder values('jane',
animals_nt(animal_ty('cat','an',to_date('1997-3-31','yyyy-mm-dd')),
animal_ty('cat','jame',to_date('1997-3-31','yyyy-mm-dd')),
animal_ty('cat','killer',to_date('2005-8-10','yyyy-mm-dd') ) ) );
commit;
select * from breeder;
select name,birthdate
from table(select animals from breeder where breedername='jane');
---------------------------------------------------------------------------------------------------------------------
--可变数组
create type comm_info as object
( no number(3),
comm_type varchar2(20),
comm_no varchar2(30) );
/
create type comm_info_list as varray(50) of comm_info;
/
create table user_info
( user_id number(6),
user_name varchar2(20),
user_comm comm_info_list );
insert into user_info values(1,'mary',
comm_info_list(comm_info(1,'手机','13651401919'),
comm_info(2,'呼机','1281234567')));
insert into user_info values(2,'carl',
comm_info_list(comm_info(1,'手机','13901018888'),
comm_info(2,'呼机','1281234567')));
commit;
select * from user_info;
select user_comm from user_info where user_id=1;
select comm_type,comm_no
from table(select user_comm from user_info where user_id=1 ) where no=1;
----------------------------------------------------------------------------------------------------------------------
--对象表
create or replace type address as object
( id number,
street varchar2(100),
state varchar2(2),
zipcode varchar2(11)
)
/
create table address_table of address;
desc address_table;
insert into address_table values(1,'Oracle way','CA','90001');
insert into address_table values(address(2,'Oracle way2','CA','90002'));
------------------------------------------------------------------------------------
--关键字
select value(a) from address_table a;
create table employee_location( empno number,
loc_ref ref address scope is address_table);
insert into employee_location
select 12345,ref(a) from address_table a where id=1;
insert into employee_location<br> select 45678,ref(a) from address_table a where id =2;
select empno,deref(loc_ref) from employee_location;
-------------------------------------------------------------------------------------------------------------
--PL/SQL表
declare
type my_text_table_type is table of varchar2(200)
index by binary_integer;
type my_emp_table_type is table of emp%rowtype
index by binary_integer;
l_text_table my_text_table_type;
l_emp_table my_emp_table_type;
begin
l_text_table(1) := 'donny';
l_text_table(2) := 'Chen';
l_text_table(3) := 'Rose';
l_emp_table(10).empno := 10;
l_emp_table(10).ename :='Jack';
l_emp_table(20).empno := 100;
l_emp_table(20).ename :='Tom';
dbms_output.put_line(l_text_table.count);
dbms_output.put_line(l_emp_table.count);
dbms_output.put_line(l_text_table(1));
dbms_output.put_line(l_emp_table(20).ename);
end;
/
declare
type my_text_table_type is table of varchar2(200)
index by binary_integer;
l_text_table my_text_table_type;
l_empty_table my_text_table_type;
begin
l_text_table(10) := 'donny';
l_text_table(20) := 'Chen';
l_text_table(30) := 'Rose';
dbms_output.put_line(l_text_table.count);
l_text_table.delete(20);
dbms_output.put_line(l_text_table.count);
l_text_table.delete;
dbms_output.put_line(l_text_table.count);
l_text_table(15) := 'some text';
l_text_table(25) := 'some more text';
dbms_output.put_line(l_text_table.count);
l_text_table := l_empty_table;
dbms_output.put_line(l_text_table.count);
end;
/
declare
type my_text_table_type is table of varchar2(200)
index by binary_integer;
l_text_table my_text_table_type;
l_index number;
begin
for emp_rec in ( select * from emp) loop
l_text_table(emp_rec.empno) := emp_rec.ename;
end loop;
l_index := l_text_table.first;
loop
exit when l_index is null;
dbms_output.put_line(l_index || ' : ' || l_text_table(l_index));
l_index := l_text_table.next(l_index);
end loop;
end;
/
dbms_output.put_line(l_text_table.count);
end;
/
-----------------------------------------------------------------------------------------------------------------
--PL/SQL记录
declare
type my_text_table_type is record(
v_empno emp.empno%type,
v_ename emp.ename%type ,
v_deptno emp.deptno%type );
l_text_table my_text_table_type;
begin
for emp_rec in ( select empno,ename,deptno from emp) loop
l_text_table := emp_rec;
dbms_output.put_line
(l_text_table.v_empno || ' ' ||
l_text_table.v_ename || ' ' || l_text_table.v_deptno );
end loop;
end;
/
-----------------------------------------------------------------------------------------------------------------
--T8
Create table user_info
(
user_id varchar2(10),
user_name varchar2(10),
user_pwd varchar2(10)
) ;
Insert into user_info values('001','a','a') ;
CREATE OR REPLACE PROCEDURE AddNewUser
(
p_UserId user_info.user_id %TYPE,
p_UserName user_info.user_name%TYPE,
p_UserPwd user_info.user_pwd%TYPE
)
is
BEGIN
---- 向user_info表中插入一条新的记录
INSERT INTO user_info(user_id, user_name, user_pwd)
VALUES (p_UserId, p_UserName, p_UserPwd);
END AddNewUser;
/
DECLARE
--描述新用户的变量
v_NewUserId user_info.user_id%TYPE := '002';
v_NewUserName user_info.user_name%TYPE := 'wish';
v_NewUserPwd user_info.user_pwd%TYPE := 'History';
BEGIN
-- 添置加wish用户到数据库
AddNewUser(v_NewUserId, v_NewUserName, v_NewUserPwd);
END;
/
create or replace procedure emp_info
(p_no in emp.empno%type default 7934,p_ename out emp.ename%type)
as
begin
select ename into p_ename from emp where empno=p_no;
end;
/
declare
v_ename emp.ename%type;
begin
emp_info(p_ename=>v_ename);
dbms_output.put_line(v_ename);
end;
/
--------------------------------------------------------------------------------------------------------------
create or replace procedure emp_sal
(p_empno in emp.empno%type,p_sal in out emp.sal%type)
as
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno=p_empno;
if v_sal > p_sal then
p_sal:=v_sal;
else
update emp set sal=p_sal where empno=p_empno;
end if;
end;
/
declare
l_sal emp.sal%type := 2000;
begin
emp_sal(7788,l_sal);
dbms_output.put_line(l_sal);
end;
/
--------------------------------------------------------------------------------------------------------------
create or replace function ExitedUser
(
p_UserName user_info.user_name%TYPE
)
return boolean
is
v_userCount NUMBER;
begin
select count(user_name) into v_userCount
from user_info
where user_name=p_UserName;
if v_userCount >0 then
return true;
else
return false;
end if;
end;
begin
if exiteduser('a') then
dbms_output.put_line('ok');
end if;
end;
----------------------------------------------------------------------------------------------------------------------
--T9
create or replace trigger trig_emp_update
after update on emp
for each row
begin
dbms_output.put_line('update ok');
end;
/
update emp set sal=sal+500 where deptno=20;
CREATE TABLE view_sites_info
( site_id NUMBER(3),
audio BLOB DEFAULT empty_blob(),
document CLOB DEFAULT empty_clob(),
video_file BFILE DEFAULT NULL,
constraint PK_TAB_view_sites_info primary key (site_id)
);
declare
AUDIO_INFO BLOB;
BEGIN
SELECT audio INTO AUDIO_INFO FROM view_sites_info WHERE site_id=1;
END;
/
DECLARE
lobloc CLOB;
buffer VARCHAR2(2000);
amount NUMBER := 30;
offset NUMBER := 1;
BEGIN
buffer := 'This is a writing example';
amount := length(buffer);
SELECT document INTO lobloc FROM view_sites_info
WHERE site_id = 1 FOR UPDATE;
dbms_lob.write(lobloc,amount,1,buffer);
COMMIT;
END;
/
DECLARE
lobloc CLOB;
buffer VARCHAR2(2000);
amount NUMBER := 30;
offset NUMBER := 1;
BEGIN
SELECT document INTO lobloc FROM view_sites_info WHERE site_id = 1;
dbms_lob.read(lobloc,amount,offset,buffer);
dbms_output.put_line(buffer);
COMMIT;
END;
/
