Oracle PL\SQL²Ù×÷£¨Áù£©Óû§ºÍ½ÇÉ«
1.Óû§¹ÜÀí
£¨1£©½¨Á¢Óû§£¨Êý¾Ý¿âÑéÖ¤£©
CREATE USER smith
IDENTIFIED BY smith_pwd
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA 5m ON users;
£¨2£©ÐÞ¸ÄÓû§
ALTER USER smith
QUOTA 0 ON SYSTEM;
£¨3£©É¾³ýÓû§
DROP USER smith;
DROP USER smith CASCADE;
£¨4£©ÏÔʾÓû§ÐÅÏ¢
DBA_USERS
DBA_TS_QUOTAS
2.ϵͳȨÏÞ
ϵͳȨÏÞ
×÷ÓÃ
CREATE SESSION
Á¬½Óµ½Êý¾Ý¿â
CREATE TABLE
½¨±í
CREATE TABLESPACE
½¨Á¢±í¿Õ¼ä
CREATE VIEW
½¨Á¢ÊÓͼ
CREATE SEQUENCE
½¨Á¢ÐòÁÐ
CREATE USER
½¨Á¢Óû§
ϵͳȨÏÞÊÇÖ¸Ö´ÐÐÌØ¶¨ÀàÐÍSQLÃüÁîµÄȨÀû£¬ÓÃÓÚ¿ØÖÆÓû§¿ÉÒÔÖ´ÐеÄÒ»¸ö»òÒ»ÀàÊý¾Ý¿â²Ù×÷¡££¨Ð½¨Óû§Ã»ÓÐÈκÎȨÏÞ£©
£¨1£©ÊÚÓèϵͳȨÏÞ
GRANT CREATE SESSION£¬CREATE TABLE
TO smith;
GRANT CREATE SESSION TO smith
WITH ADMIN OPTION;
Ñ¡ÏADMIN OPTION ʹ¸ÃÓû§¾ßÓÐתÊÚϵͳȨÏÞµÄȨÏÞ¡£
£¨2£©ÏÔʾϵͳȨÏÞ
²é¿´ËùÓÐϵͳȨÏÞ£º
system_privilege_map
ÏÔʾÓû§Ëù¾ßÓеÄϵͳȨÏÞ£º
dba_sys_privis
ÏÔʾµ±Ç°Óû§Ëù¾ßÓеÄϵͳȨÏÞ£º
user_sys_privis
ÏÔʾµ±Ç°»á»°Ëù¾ßÓеÄϵͳȨÏÞ£º
session_privis
£¨3£©ÊÕ»ØÏµÍ³È¨ÏÞ
REVOKE CREATE TABLE from smith;
REVOKE CREATE SESSION from smith;
3.½ÇÉ«£ºÊÇÒ»×éÏà¹ØÈ¨ÏÞµÄÃüÃû¼¯ºÏ£¬Ê¹ÓýÇÉ«×îÖ÷ÒªµÄÄ¿µÄÊǼò»¯È¨ÏÞ¹ÜÀí¡£
•Ô¤¶¨Òå½ÇÉ«¡£
ØCONNECT ×Ô¶¯½¨Á¢£¬°üº¬ÒÔÏÂȨÏÞ£ºALTER SESSION¡¢CREATE CLUSTER¡¢CREATE DATABASE LINK¡¢CREATE SEQUENCE¡¢CREATE SESSION¡¢CREATE SYNONYM¡¢CREATE TABLE¡¢CREATE VIEW ¡£
RESOURCE ×Ô¶¯½¨Á¢£¬°üº¬ÒÔÏÂȨÏÞ£ºCREATE CLUSTER¡¢CREATE PROCEDURE¡¢CREATE SEQUENCE¡¢CREATE TABLE¡¢CREATE TRIGGR ¡£
ØÏÔʾ½ÇÉ«ÐÅÏ¢£¬
§ROLE_SYS_PRIVS
§ROLE_TAB_PRIVS
§ROLE_ROLE_PRIVS
§SESSION_ROLES
§USER_ROLE_PRIVS
§DBA_ROLES
4.OracleÓû§½ÇÉ«
ÿ¸öÓû§¶¼ÓÐÒ»¸öÃû×ֺͿÚÁî,²¢ÓµÓÐһЩÓÉÆä´´½¨µÄ±í¡¢ÊÓͼºÍ×ÊÔ´¡£Oracle½ÇÉ«£¨role£©¾ÍÊÇÒ»×éȨÏÞ£¨privilege£©(»òÕßÊÇÿ¸öÓû§¸ù¾ÝÆä״̬ºÍÌõ¼þËùÐèµÄ·ÃÎÊÀàÐÍ)¡£Óû§¿ÉÒÔ¸ø½ÇÉ«ÊÚÓè»ò¸³ÓèÖ¸¶¨µÄȨÏÞ£¬È»ºó½«½ÇÉ«¸³¸øÏàÓ¦µÄÓû§¡£Ò»¸öÓû§Ò²¿ÉÒÔÖ±½Ó¸øÆäËûÓû§ÊÚȨ¡£ ÆäËûOracle
ϵͳȨÏÞ£¨Database Sys
Ïà¹ØÎĵµ£º
if exists(select * from master.dbo.sysdatabases where name = 's2723103005')
begin
drop database s2723103005
print 'ÒÑɾ³ýÊý¾Ý¿âs2723103005'
end
create database s2723103005
on primary
(name=His_data,
filename = 'd:\database\his_data.mdf',
siz ......
1.ϵͳ±äÁ¿º¯Êý
£¨1£©SYSDATE
¸Ãº¯Êý·µ»Øµ±Ç°µÄÈÕÆÚºÍʱ¼ä¡£·µ»ØµÄÊÇOracle·þÎñÆ÷µÄµ±Ç°ÈÕÆÚºÍʱ¼ä¡£
select sysdate from dual;
insert into purchase values
(‘Small Widget’,’SH’,sysdate, 10);
insert into purchase values
(‘Meduem Wodget’,’SH’, ......
1.Êý¾Ý¿âµÄË÷Òý
¿ÉÒÔ½«Ë÷Òý¸ÅÄîÓ¦Óõ½Êý¾Ý¿â±íÉÏ¡£µ±Ò»¸ö±íº¬ÓдóÁ¿µÄ¼Ç¼ʱ£¬Oracle²éÕҸñíÖеÄÌØÐ´¼Ç¼Ҫ»¨ºÜ³¤µÄʱ¼ä——¾ÍÏñ»¨ºÜ³¤Ê±¼ä·¿´È«ÊéÀ´²éÕÒij¸öÖ÷ÌâÒ»Ñù¡£OracleÓÐÒ»¸öÒ×ÓÚʹÓõŦÄÜ£¬¼´¿ÉÒÔ½¨Á¢Ò»¸ö´ÎÒþ²Ø±í£¬¸Ã±í°üº¬Ö÷±íÖеÄÒ»¸ö»ò¶à¸öÖØÒªµÄÁУ¬ÒÔ¼°ÔÚÖ÷± ......
1.ÔÚ±íÖ®¼ä´«ÊäÊý¾Ý
1£©ÀûÓÃINSERT´«ÊäÊý¾Ý
insert into test1 (select name2,age2 from test2);
´ÓÉÏÃæµÄ²Ù×÷¿ÉÒÔ¿´³ö£¬¿Éͨ¹ýSELECTÏòÒ»¸ö±íÖгÉÅúµØÌí¼ÓÊý¾Ý£¬µ«Ó¦×¢Ò⣺Êý¾ÝÀàÐÍÒªÒ»Ö£¬ËùÑ¡ÔñµÄÁÐÊýÓ¦Ò»Ö¡£´ËÓï¾äµÄÓï·¨¸ñʽÈçÏ£º
INSERT INTO table_name (
SELECT statement
) ;
2£©»ùÓÚÒÑÓÐµÄ±í½¨Á¢Ð ......