Oracle PL/SQL常用命令

一、Oracle PL/SQL使用1、用户管理数据库系统权限（DataBaseSystem Privilege) 数据库对象权限（DataBaseSystem Privilege) Orac

一、Oracle PL/SQL使用
1、用户管理
数据库系统权限（DataBaseSystem Privilege)

数据库对象权限（DataBaseSystem Privilege)

Oracle内部用户Sys和System

创建用户：create user user_test identified by user_test123;

修改用户：alter user user_test identified by user_test123;

删除用户：drop user user_test;

删除用户与其对象：drop user user_testCASCADE;

用户授权连接：grant connect,resource to user_test;

 

在sqlplus 中使用以下命令可以查出用户表的清单：

sqlplus scott/tiger -- 使用sqlplus 登录 oracle 数据库

col table_name format a30; -- 指定table_name 的列宽

col table_type format a10; -- 指定table_type 的列宽

select * from cat; -- 列出用户数据表

 

2、角色管理
1)Connect Role(连接角色)：临时用户，不需要建表的用户。

2)Resource Role(资源角色)：更可靠和正式的数据库用户。

3)Dba Role(数据库管理员角色)：拥有所有的系统权限。

操作：

grant(授权)命令：grant connect,resource to user_test;

    revoke(撤销)命令：revoke connect,resource to user_test;

创建角色：除了系统自带的3种标准角色外用户可以创建自己的role.

          create role user_test_role;

角色授权：grant select on t_service to user_test_role;

注：拥有student角色的用户都具有对t_service表的select权限。

删除角色：droprole user_test_role;

查看用户有哪些角色：

select grant_role fromdba_role_privs where grantee='scott';

查看用户有哪些权限：

select privilege from dba_sys_privs wheregrantee='scott';

select privilege fromdba_sys_privs where grantee='CONNECT';

默认用户：Sys:oralce中的超级用户，主要用来维护系统信息和管理实例。

System:oracle中默认的系统管理员，拥有dba权限。通常管理oracle数据库的用户、权限和存储等。

Scott:oracle数据库的一个示范账户，在数据库安装时创建。

用户授权： grant 权限 [ on 对象名 ] to 用户名 [ with grant option];

          grant select on scott.emp to user_test with grant option;

          grant create session to user_test; 

3、权限管理
授予user_test用户查看emp表数据的权限

1.验证user_test对scott用户的emp表进行查询的权限。

SQL>select *from scott.emp; 

2.为用户user_test授予scott用户的emp表的查询权限。

SQL>conns cott/scott@test;

SQL>grant select on scott.emp to user_test;

 

3.使用user_test账户登录并查询scott用户的表emp信息。

SQL>conn user_test/u01@test;

SQL>select *from scott.emp;

收回权限： revoke 权限 [ on 对象名 ] from 用户名

SQL>revoke select on scott.emp from user_test;

修改用户密码：alter user 用户名 identified by 新密码;

与权限相关的表：

1.dba_sys_privs(所有系统权限)

2.user_sys_privs(用户拥有的系统权限)

3.user_col_privs(用户拥有的对象权限)

常用的系统权限赋值语句：

SQL>grant create session to user_test;

SQL>grant create table to user_test;

SQL>grant unlimited tablespace to user_test;

SQL>grant create session to public;

对象权限的赋予与撤销语句：

SQL>grant select on mytable to user_test;

SQL>grant all on mytable to user_test;

SQL>revoke select on mytable from user_test;

SQL>revoke all on mytable from user_test;

Oracle中除了能在表对象上赋予相应的权限，，还能将权限控制到表的列上：

SQL>grant update(name)on mytable to user_test;

SQL>grant insert(id) on mytable to user_test;

4、口令管理
使用profile管理用户口令

profile是口令限制，资源管理的命令集合，当建立数据库时，Oracle会自动建立名词为default的profile。当建立用户没有指定profile选项，那Oroacle就会将default分配给用户。

关键字列表:

SQL>create profile 配置文件名称 limit failed_login_attempts 尝试次数password_lock_time 锁定天数;

SQL>alter user 用户名 profile 配置文件名称;

例：指定tea用户最多只能尝试3次登陆，锁定时间为2天，下面可以实现。

SQL>create profile lock_account limit failed_login_attempts 3 password_lock_time 2;

SQL>alter user tea profile lock_account;

给账号解锁

SQL>alter user user_test account unlock;

例：为了让用户定期修改密码可使用终止口令完成。

SQL>create profile myprofile limit password_life_time 10 password_grace_time 2;

给用户解锁

SQL>alter user user_testprofile myprofile;

删除profile

SQL>drop profile myprofile[cascade];

5、有关表sequence
创建主键自增表 

1)创建表 t_test   

create table t_test(

          userid number(10) NOT NULL primary key,  /*主键，自动增加*/

          username varchar2(20)

          );

2)创建自动增长序列

 CREATESEQUENCE t_test_increase_sequence

 INCREMENTBY 1  -- 每次加几个 

    START WITH 1    -- 从1开始计数 

    NOMAXVALUE      -- 不设置最大值  ，设置最大值：maxvalue 9999

    NOCYCLE          -- 一直累加，不循环

    CACHE 10;

 

示例：CREATE SEQUENCE t_service_sequence INCREMENT BY1  START WITH 1  NOMAXVALUE  NOCYCLE    CACHE 10;

 

3)创建触发器（客户端工具添加）

 

CREATE TRIGGER t_service_triggerBEFORE

insert ON t_service FOR EACH ROW          /*对每一行都检测是否触发*/

begin

select t_service_sequence.nextvalinto:New.userid from dual;

end;

 

示例：

create orreplacetrigger t_service_trigger

  before insert on t_service 

  for eachrow

declare

  -- local variables here

begin

  select t_service_sequence.nextvalinto:New.idfrom dual;

end t_service_trigger;

 

4)提交

 

commit;

5)测试

 

insert intoTest_Increase(Username) values('test');

 

6、远程连接
在连接之前设置好监听程序然后使用如下命令：

sqlplus usr/pwd@//host:port/sid

二、SpringJDBC配置
#flow config jdbcfor oracle

jdbc.driver=oracle.jdbc.driver.OracleDriver

 

jdbc.url=jdbc:oracle:thin:@127.0.0.1:1521:orcl

jdbc.username=user_test

jdbc.password=user_test123

 

xml里配置方言：

       

           

                org.hibernate.dialect.OracleDialect

                true

                true

                false

                true

                update

           

       

相关阅读：

rlwrap - 解决Linux下SQLPLUS退格、上翻键乱码问题

SQLPLUS spool 到动态日志文件名

Oracle SQLPLUS提示符设置

通过设置SQLPLUS ARRAYSIZE(行预取)加快SQL返回速度