熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

八個學習點幫助你全面認識Oracle數據庫

2013-11-13 16:00:21  來源: Oracle 

  TableSpace

       表空間 一個表空間對應多個數據文件(物理的dbf文件) 用語法方式創建tablespace用sysdba登陸 創建表空間mytabs大小為MB:

  create tablespace mytabs datafile C:\oracle\oradata\mydb\mytabsdbf size M; alter user zgl default tablespace mytabs; 把tabs做為zgl的默認表空間 grant unlimited tablespace to zgl; 將操作表空間的權限給zgl

Exception 示例

  create or replace procedure pro_test_exception(vid in varchar) is userName varchar(); begin select name into userName from t_user where id=vid; dbms_outputput_line(userName); exception when no_data_found then dbms_outputput_line(沒有查到數據!); when too_many_rows then dbms_outputput_line(返回了多行數據!); end pro_test_exception;

  安全管理

  以下語句以sysdba登陸 用戶授權 alter user zgl account lock;鎖定帳號 alter user zgl identified by zgl;修改用戶密碼 alter user zgl account unlock;解除帳號鎖定 alter user zgl default tablespace tt;修改用戶zgl的默認表空間為tt create user qqq identified by qqq default tablespace tt;創建用戶

  grant connect to qqq;給qqq授予connect權限 grant execute on zglproc to test;將過程zglproc授予用戶test grant create user to zgl;給zgl授予創建用戶的權限 revoke create user from zgl;解除zgl創建用戶的權限

  角色授權 create role myrole;創建角色myrole grant connect to myrole;給myrole授予connect權限 grant select on zglt_user to myrole;把查詢zglt_user的權限授予myrole grant myrole to test;把角色myrole授予test用戶

  概要文件(配置文件) 全局設置可以在概要文件中設置登陸次數如超過這次數就鎖定用戶

  Synonym

  創建同義詞示例

  create public synonym xxx for myusert_user create synonym t_user for myusert_user select * from dba_synonyms where table_name=T_USER

跨數據庫查詢

  create database link dblinkzgl connect to myuser identified by a using mydb Select * From t_user@dblinkzgl

course示例 示例

  create or replace procedure pro_test_cursor is userRow t_user%rowtype; cursor userRows is select * from t_user; begin for userRow in userRows loop dbms_outputput_line (userRowId||||userRowName||||userRows%rowcount); end loop; end pro_test_cursor;

示例

  create or replace procedure pro_test_cursor_oneRow(vid in number) is userRow t_user%rowtype; cursor userCur is select * from t_user where id=vid; begin open userCur; fetch userCur into userRow; if userCur%FOUND then dbms_outputput_line (userRowid||||userRowName); end if; close userCur; end pro_test_cursor_oneRow;

record示例

  create or replace procedure pro_test_record(vid in varchar) is type userRow is record( id t_userid%type name t_username%type ); realRow userRow; begin select idname into realRow from t_user where id=vid; dbms_outputput_line (realRowid||||realRowname); end pro_test_record;

rowtype示例

  create or replace procedure pro_test_rowType(vid in varchar) is userRow t_user%Rowtype; begin select * into userRow from t_user where id=vid; dbms_outputput_line (userRowid||||userRowname); end pro_test_rowType;


From:http://tw.wingwit.com/Article/program/Oracle/201311/17660.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.