Oracle]Oracle语句集锦(不断的更新中...)
转
http://blog.chinaunix.net/u/12457/showart_308067.html
|
--T4
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; / |