oracle 操作

[size=medium]查看用户权限、角色：
SELECT * FROM USER_SYS_PRIVS;
SELECT * FROM USER_ROLE_PRIVS;

创建存储过程：
CREATE OR REPLACE PROCEDURE PROC_USER_EDIT
( username IN VARCHAR2 --参数输入值用IN，输出值用OUT
, password IN VARCHAR2
, nickname IN VARCHAR2
) AS
BEGIN
EXECUTE IMMEDIATE 'UPDATE t_user SET username='''|| username||''' WHERE id=' || id;
END PROC_USER_ADD;


创建序列
CREATE SEQUENCE SQ_USER_ID
MINVALUE 1 MAXVALUE 1.00000000000000E+27 INCREMENT BY 1 START WITH 21 CACHE 20 ORDER NOCYCLE ;


创建触发器
create or replace TRIGGER TRIG_USER_ID
BEFORE INSERT ON ONEPIECE.T_USER
FOR EACH ROW
BEGIN
SELECT SQ_USER_ID.NEXTVAL INTO :NEW.ID FROM DUAL;
END;


给用户ONEPIECE授予创建触发器、存储过程权限
GRANT CREATE TRIGGER TO ONEPIECE;
GRANT CREATE PROCEDURE TO ONEPIECE;


SQLPLUS登录指定的数据库时：
cmd-->cd\-->sqlplus-->sys@orcl as sysdba-->root

启动与关闭isqlplus
c:\>isqlplusctl start
c:\>isqlplusctl stop
访问isqlplus：http://localhost:5560/isqlplus

启动与关闭EM
c:\>emctl start dbconsole
c:\>emctl stop dbconsole
访问EM：http://localhost:5500/em

[/size]