存儲過程對象
SQL> create table account(account_id varchar2(5),name varchar2(10),sum number(10,2));
SQL> insert into account values('10001','tom',50000);
SQL> insert into account values('10002','kate',0);
SQL> select * from account;
模拟銀行轉賬
create or replace procedure pro_zz(zc_zh in varchar2,zr_zh in varchar2,zz_je in int)
as
var_sum int:=0;
begin
select sum into var_sum from account where account_id=zc_zh;
if var_sum<zz_je then
dbms_output.put_line('meiqianle!');
else
update account set sum=sum-zz_je where account_id=zc_zh;
dbms_output.put_line('zhuanchuchengong!');
update account set sum=sum+zz_je where account_id=zr_zh;
dbms_output.put_line('zhuanruchenggong!');
commit;
end if;
end;
/
(zc_zh 轉出帳戶,zr_zh 轉入帳戶,zz_je 轉賬金額)
SQL> set serveroutput on (顯示過程)
SQL> exec pro_zz('10001','10002',10000);
SQL> select * from account;(tom少10000,kate多10000)
超過2w不讓轉帳
var_vvv int:=0;
update account set sum=sum-zz_je where account_id=zc_zh;
insert into jy (account_id,zr_id,je)values(zc_zh,zr_zh,zz_je);
end if;
select sum(je) into var_vvv from jy where account_id=zc_zh;
if
var_vvv > 20000 then
dbms_output.put_line('burangzhuanle');
rollback;
else
dbms_output.put_line('chenggong!!!!!');
commit;
-------------------------
加一個限制限制條件
1.
SQL> alter table account add constraint ch_01 unique(account_id);
(account_id 唯一)
2.(帳戶的金額不能大于40000)
alter table account
add constraint ch_02
check (sum<=45000)
enable novalidate;(表中已經有資料時,不對之前的資料進行驗證)
3.SQL> select * from account;
4.SQL> exec pro_zz('10001','10002',40000);(不成功因為不能大于40000)
SQL> alter table account drop constraint ch_01;
(删除表的限制)
SQL> select constraint_name,table_name from user_constraints;
(檢視所有表的限制)
SQL> select * from v$sqltext where SQL_TEXT like '%pro_zz%';
(執行的儲存過程)
業務中儲存過程的好處:前端隻需要傳輸值就可以,不用傳輸sql語句了.資料庫自行調用儲存過程,減少了網絡的傳輸量
SQL> create tablespace tbs_icbc datafile '/u01/app/oracle/oradata/orcl/tbs_icbc.dbf' size 10M;
SQL> create temporary tablespace tbs_temp tempfile '/u01/app/oracle/oradata/orcl/tbs_temp.dbf' size 10M;
檢視庫下使用者
select * from all_users;
檢視使用者資訊
select * from user_users;
建立使用者(undo隻能用一個)
SQL> create user icbc identified by icbc
default tablespace tbs_icbc
temporary tablespace tbs_temp
account unlock;
SQL> conn icbc/icbc(不能登陸,沒有建立會話權限)
ERROR:
ORA-01045: user ICBC lacks CREATE SESSION privilege; logon denied
SQL> grant create session to icbc;(sys使用者登陸下)[系統權限]
SQL> conn icbc/icbc(可以正常登陸)
SQL> show user
USER is "ICBC"
------------------------------------
檢視使用者預設表空間
select default_tablespace from dba_users where username='SCOTT';
修改使用者預設表空間
SQL> alter user scott default tablespace system;
--------------------------
SQL> create table tt(id int);(報錯 沒有建立表的權限)
create table tt(id int)
*
ERROR at line 1:
ORA-01031: insufficient privileges
2.
SQL> grant create table to icbc;(sys使用者登陸下)
3.
SQL> create table tt(id int);(在表空間上權限不足,沒有給使用者空間配額)
4.SQL> alter user icbc quota 10M on tbs_icbc;(賦予使用者空間配額,sys使用者下)
或
SQL> alter user icbc quota unlimited on tbs_icbc;(不限制空間)
5.SQL> create table tt(id int); 可以建立
-----------------------
SQL> conn scott/abc
SQL> grant select on emp to icbc;(scott使用者下)
(賦予icbc使用者讀 scott使用者emp權限)
SQL> create table t_big as select * from scott.emp;(icbc使用者下)[對象權限]
SQL> insert into t_big select * from t_big;
SQL> / (多次執行一直到超出配額限制)
SQL> select count(*) from t_big;
COUNT(*)
----------
114688
SQL> desc t_big
SQL> select * from t_big order by MGR desc;
(使用者排序時,首先把排序結果放到pga中,pga空間不夠時,使用使用者預設的臨時表空間)
檢視臨時表空間,排序會使用到臨時表空間(windows用戶端檢視)
主鍵 外鍵
zs 使用者
SQL> create table t1 (id int primary key);
SQL> insert into t1 values(1);
SQL> commit;
SQL> grant select on t1 to ls;
SQL> grant references on t1 to ls;(賦予 參考 權限，供ls使用者做外鍵使用）
ls 使用者
SQL> create table t2 (id int);
SQL> alter table t2 add constraint tf foreign key(id) references zs.t1(id); （添加外鍵）
驗證：
SQL> insert into t2 values(1); （可以 因為zs.t1表中存在）
SQL> insert into t2 values(2); （不可以 因為zs.t1表中不存在）
使用者資源限制
SQL> show parameter process (預設150個連接配接數 包含所有程序)
SQL> alter system set processes=100 scope=spfile;
SQL> startup force
測試(如果設定 processes 為10 結果的情況?)
SQL> select count(*) from v$process;
檢視目前啟動的程序(每個會話為一個程序)
使用者會話限制
1.建立配置檔案 限制使用者3個會話
SQL> create profile PRO_TEST limit
2 sessions_per_user 3;
SQL> alter user SCOTT profile PRO_TEST;(給scott使用者應用)
SQL> show parameter limit(限制預設為關閉狀态)
SQL> alter system set resource_limit=true; 打開限制
SQL> show parameter limit
4.[oracle@sq ~]$ sqlplus /nolog
SQL> conn scott/abc (操作三次)
5.第四次
ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit
SQL> alter profile PRO_TEST limit idle_time 1;(限制會話空閑1分鐘 斷開)
SQL> drop profile PRO_TEST cascade;(已配置設定的profile，删除時必須加cascade選項,DEFAULT為預設profile，不能删除)
---------------------
profile 裡參數
LOGICAL_READS_PER_CALL：指定一次執行SQL（解析、執行和提取）調用所允許讀的資料塊的最大數目。
CONNECT_TIME：指定會話的總的連接配接時間。（機關：分鐘）
PRIVATE_SGA：指定一個會話可以在共享池（SGA）中所允許配置設定的最大空間（機關：位元組）
注：該限制隻在使用共享伺服器結構時才有效，會話在SGA中的私有空間包括私有的SQL和PL/SQL，但不包括共享的SQL和PL/SQL
PASSWORD_LIFE_TIME:指定同一密碼所允許使用的天數。
password_reuse_time指定了密碼不能重用前的天數
password_reuse_max則指定了目前密碼被重用之前密碼改變的次數
-----------------------------
删除icbc 并把使用者下所有對象
SQL> drop user icbc cascade;
SQL> select * from dba_users;
檢視表空間配額
SQL> select * from dba_ts_quotas;(dba不受配額限制)
-------------------------------
使用者角色
1.預定義角色
CONNECT, RESOURCE, DBA
DBA: 擁有全部特權，是系統最高權限，隻有DBA才可以建立資料庫結構。
RESOURCE(資源):擁有Resource權限的使用者隻可以建立實體，不可以建立資料庫結構。
CONNECT:擁有Connect權限的使用者隻可以登入Oracle，
SQL> grant dba to scott;
SQL> revoke dba from scott;(回收角色)
查詢使用者擁有哪裡權限
select * from dba_role_privs;(檢視哪個使用者建立的角色)
select * from dba_sys_privs; (檢視使用者權限)
select * from role_sys_privs;(檢視角色中的權限)
2.實體權限
select, update, insert, alter, index, delete, all
3.自建立角色
SQL> create role role1;
給角色賦權限（建立表和建立儲存過程）
SQL> grant create any table,create procedure to role1;
把角色 賦予使用者
SQL> grant role1 to scott;
删除角色
SQL> drop role role1;
--------------------------------------
驗證模式:
1.系統認證
2.密碼檔案認證
[oracle@oracle1 admin]$ vi sqlnet.ora
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
此時本地sys使用者的認證方式為系統認證，即可以 sqlplus / as sysdba 方式登入
如果在sqlnet.ora 中額外添加：
SQLNET.AUTHENTICATION_SERVICES= (NTS) 或 SQLNET.AUTHENTICATION_SERVICES= (NONE)
NTS:為windows專用 linux用 NONE
把SQLNET.AUTHENTICATION_SERVICES= (NONE) 添加到NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)上面
再次登陸時 sys需要密碼
本文轉自陳繼松 51CTO部落格，原文連結：http://blog.51cto.com/chenjisong/1737388，如需轉載請自行聯系原作者
![龍珠訓練營task04[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)