]]>
1.鐧婚檰緋葷粺鐢ㄦ埛
sqlplus 鐒跺悗杈撳叆緋葷粺鐢ㄦ埛鍚嶅拰瀵嗙爜
鐧婚檰鍒殑鐢ㄦ埛
conn 鐢ㄦ埛鍚?/span>/瀵嗙爜;
2.鍒涘緩琛ㄧ┖闂?/span>
create tablespace 絀洪棿鍚?/span>
datafile 'c:"絀洪棿鍚?/span>' size 15M --琛ㄧ┖闂寸殑瀛樻斁璺緞,鍒濆鍊間負(fù)15M
autoExtend on next 10M --絀洪棿鐨勮嚜鍔ㄥ闀跨殑鍊兼槸10M
permanent online; --姘鎬箙浣跨敤
3.鍒涘緩鐢ㄦ埛
create user shi --鍒涘緩鐢ㄦ埛鍚嶄負(fù)shi
identified by scj --鍒涘緩瀵嗙爜涓?/span>scj
default tablespace 琛ㄧ┖闂村悕 --榛樿琛ㄧ┖闂村悕
temporary tablespace temp --涓存椂琛ㄧ┖闂翠負(fù)temp
profile default --鍙?/span>profile鏂囦歡鐨勯檺鍒?/span>
quota unlimited on 琛ㄧ┖闂村悕; --鍦ㄨ〃絀洪棿涓嬮潰寤鴻〃涓嶅彈闄愬埗
4.鍒涘緩瑙掕壊
create role 瑙掕壊鍚?/span> identified by 瀵嗙爜;
5.緇欒鑹叉巿鏉?/span>
grant create session to 瑙掕壊鍚嶏紱--緇欒鑹叉巿浜堝垱寤轟細(xì)璇濈殑鏉冮檺
grant 瑙掕壊鍚?/span> to 鐢ㄦ埛鍚嶏紱 --鎶婅鑹叉巿浜堢敤鎴?/span>
6.緇欑敤鎴鋒巿浜堟潈闄?/span>
grant create session,resource to shi;--緇?/span>shi鐢ㄦ埛鎺堜簣鎵鏈夋潈闄?/span>
grant create table to shi; --緇?/span>shi鐢ㄦ埛鎺堜簣鍒涘緩琛ㄧ殑鏉冮檺
7.select table_name from user_tables; 瀵熺湅褰撳墠鐢ㄦ埛涓嬬殑鎵鏈夎〃
8.select tablespace_name from user_tablespaces; 瀵熺湅褰撳墠鐢ㄦ埛涓嬬殑 琛ㄧ┖闂?/span>
9.select username from dba_users;瀵熺湅鎵鏈夌敤鎴峰悕縐板懡浠?/span> 蹇呴』鐢?/span>sys as sysdba鐧婚檰
10.鍒涘緩琛?/span>
create table 琛ㄥ悕
(
id int not null,
name varchar2(20) not null
)tablespace 琛ㄧ┖闂村悕 --鎵灞炵殑琛ㄧ┖闂?/span>
storage
(
initial 64K --琛ㄧ殑鍒濆鍊?/span>
minextents 1 --鏈灝忔墿灞曞?/span>
maxextents unlimited --鏈澶ф墿灞曞?/span>
);
11.--涓?/span>usrs琛ㄦ坊鍔犱富閿拰绱㈠紩
alter table users
add constraint pk primary key (ID);
12.涓哄凡緇忓垱寤?/span>users琛ㄦ坊鍔犲閿?/span>
alter table users
add constraint fk_roleid foreign key (roleid)
references role(role_id) on delete cascad; --涓嬭竟鍐欎富琛ㄧ殑鍒?/span>
on delete cascad鏄垱寤虹駭鑱?/span>
13.鎶婁袱涓垪榪炴帴璧鋒潵
select concat(name,id) from 琛ㄥ悕錛?/span> --鎶?/span>name鍜?/span>id榪炴帴璧鋒潵
14.鎴彇瀛楃涓?/span>
select column(name,'鏉?/span>') from 琛ㄥ悕錛?/span> --鎶?/span>name涓殑‘鏉?/span>’鍘繪帀
15.榪愯浜嬪姟涔嬪墠蹇呴』鍐?/span>
set serveroutput on; --鎵撳紑杈撳叆杈撳嚭錛堜笉鍐欑殑璇?/span>,鎵撳嵃涓嶅嚭淇℃伅錛?/span>
16.while鐨勫簲鐢?/span>
declare --澹版槑閮ㄥ垎
ccc number:=1; --澶嶈亴
a number:=0;
begin --浜嬪姟鐨勫紑濮?/span>
while ccc<=100 loop --寰幆
if((ccc mod 3)=0) then --鏉′歡
dbms_output.put_line(ccc||','); --鎵撳嵃鏄劇ず
a:=a+ccc;
end if; --緇撴潫if
ccc:=ccc+1;
end loop; --緇撴潫寰幆
dbms_output.put_line(a);
end; --緇撴潫浜嬪姟
/
17.select into 鐨勭敤娉?/span> --鍙兘澶勭悊涓琛岀粨鏋滈泦
declare
name varchar(30);
begin
select username into name
from users
where id=2;
dbms_output.put_line('濮撳悕涓猴細(xì)'||name);
end;
/
18.鍒╃敤%rowtype灞炴у彲浠ュ湪榪愯鏃舵柟渚跨殑澹版槑璁板綍鍙橀噺鍜屽叾浠栫粨鏋?/span>
Set serveroutput on;
Declare
utype users%rowtype;
Begin
Select * into utype from users where id=20;
Dbms_output.put_line('濮撳悕'|| utype.username);
Dbms_output.put_line('鐢熸棩'|| utype.brithday);
end;
/ --%rowtype鎯沖綋浜庡鍒朵竴涓〃
19.娓告爣鐨勫畾涔夊拰浣跨敤
Declare
Cursor ucur is select * from users; --澹版槑娓告爣
Us users%rowtype;--瀹氫箟涓庢父鏍囨兂鍖歸厤鐨勫彉閲?/span>
Begin
Open ucur;--鎵撳紑娓告爣
Fetch ucur into us;
While ucur %found loop --浣跨敤寰幆閬嶅巻娓告爣鐨勬煡璇㈢粨鏋?/span>
Dbms_output.put_line('濮撳悕錛?/span>'||us.username||'鐢熸棩'||us.brithday);
Fetch ucur into us;
End loop;
Close ucur; --鍏抽棴娓告爣
End;
=======================================
%found鍦ㄥ墠涓鏉＄殑fetch璇彞鑷沖皯瀵瑰簲鏁版嵁搴撶殑涓琛屾椂錛?/span>%found灞炴у間負(fù)true錛屽惁鍒欎負(fù)false;
% notfound 鍦ㄥ墠涓鏉?/span>fetch璇彞娌℃湁瀵瑰簲鐨勬暟鎹簱琛屾椂錛?/span>%notfound灞炴у間負(fù)true錛屽惁鍒欎負(fù)false;
%isopen 鍦ㄦ父鏍囨墦寮鏃?/span>%isopen灞炴у間負(fù)true錛涘惁鍒欎負(fù)false;
%rowcount鏄劇ず榪勪粖涓烘浠庢樉紺烘父鏍囦腑鍙栧嚭鐨勮鏁?/span>
20.
鍒犻櫎
drop tablespace 絀洪棿鍚?/span> including contents; --鍒犻櫎琛ㄧ┖闂村拰閲岄潰鐨勫唴瀹?/span>
drop table 琛ㄥ悕 --鍒犻櫎琛?/span>
drop user 鐢ㄦ埛鍚?/span> --鍒犻櫎鐢ㄦ埛
]]>