oracle-常用笔记

一、常用语句

1、判断Ｂ表和Ａ表是否满足ON中条件，如果满足则用B表去更新A表

merge into  Table a  using  Table a   on (a.id = b.id) when matched then update set  a.name= b.name

2、递归查询，返回父节点

select * from Tab a  start with  a.id=101  connect by prior  a.parentid=a.id   ORDER BY a. id

3、表结构查询

SELECT
	B.TABLE_NAME AS "表名",
C.COMMENTS as 表说明,
	B.COLUMN_ID AS "字段序号",
  A .COMMENTS AS "字段别名",
	B.COLUMN_NAME AS "字段名",
	B.DATA_TYPE ||'('||B.DATA_LENGTH||')'  AS "字段类型",
	B.DATA_LENGTH AS "数据长度"

FROM
	ALL_COL_COMMENTS A,
	ALL_TAB_COLUMNS B,
	ALL_TAB_COMMENTS C
WHERE
	A .TABLE_NAME IN (SELECT U .TABLE_NAME FROM USER_ALL_TABLES U)
AND A . OWNER = B. OWNER
AND A .TABLE_NAME = B.TABLE_NAME
AND A .COLUMN_NAME = B.COLUMN_NAME
AND C.TABLE_NAME = A .TABLE_NAME
AND C. OWNER = A . OWNER
AND A . OWNER = 'A5JXUPC' 
ORDER BY
	A .TABLE_NAME,B.COLUMN_ID;

二、创建触发器并调用存储过程

CREATE OR REPLACE TRIGGER EQUIP_HCRK AFTER UPDATE OF "FLAG" ON "JDJXZY"."PC_AQSC_EQUIPMENT_WZ_MAIN" REFERENCING OLD AS "OLD" NEW AS "NEW" FOR EACH ROW ENABLE
begin
IF :new.flag ='11' and :new.audit_opinion ='2' THEN    
	 EQUIP_HCRK(:NEW.PLAN_ID);--调用物资耗材入库存储过程
elsIF :new.flag ='22' THEN 
     EQUIP_HCRK2(:NEW.PLAN_ID);--调用物资耗材入库存储过程2
END IF;END;

三、创建存储过程

CREATE OR REPLACE 
PROCEDURE EQUIP_HCRK (plan_id IN NUMBER,confirm IN VARCHAR2) AS
  v_sbmc VARCHAR2(55);
  v_pp VARCHAR2(55);
  v_ggxh VARCHAR2(55);
  v_time VARCHAR2(55):=TO_CHAR(sysdate,'yyyy-mm-dd');
  CURSOR CUR IS  SELECT * FROM  PC_AQSC_EQUIPMENT_WZ_DETAILED WHERE  EVENT_ID = plan_id;
    begin
      FOR obj IN CUR LOOP
        v_sbmc:=obj.SBMC;
        v_pp:=obj.PP;
        v_ggxh:=obj.GGXH;
        if v_sbmc is not null then 
        INSERT INTO  PC_AQSC_EQUIPMENT_BILL(HC_ID,FLAG,HC_NAME,HC_PP,HC_GGXH,ADD_DATE)VALUES(PROCEDURE_SEQ.nextval,'1',v_sbmc,v_pp,v_ggxh,v_time);
        else PC_AQSC_EQUIPMENT_BILL(HC_ID,FLAG,HC_NAME,HC_PP,HC_GGXH,ADD_DATE)VALUES(PROCEDURE_SEQ.nextval,'1',v_sbmc,v_pp,v_ggxh,v_time);
       
      END IF;
      END LOOP;
    end;

四、定时任务

1、任务创建

declare
  job number;
BEGIN
  DBMS_JOB.SUBMIT(  
        JOB => job,  /*自动生成JOB_ID*/   
        WHAT => 'PC_RISK_AUTO_APPROVAL;',  /*需要执行的存储过程名称或SQL语句*/ 
        NEXT_DATE =>sysdate+1/(24*60),  /*初次执行时间-下一个x分钟*/  
        INTERVAL => 'TRUNC(SYSDATE+1)+(7*60+50)/(24*60)' /*每天x点执行  */
      );  
  commit;
end;

2、任务查询及删除

select * from user_jobs；
begin
  DBMS_JOB.remove(44);
End;

五、oracle用户密码过期的解决方法：设置为永久期限

1、进入sqlplus模式
sqlplus / as sysdba;（以系统身份登录用户名：sys   密  码 ：manager as sysdba
2、先查看dba_users里面，用户使用的porfile是哪一个，一般默认是default
SELECT username,PROFILE FROM dba_users;
3、查看用户密码的有效期设置(一般默认的配置文件是DEFAULT)
SELECT * FROM dba_profiles WHERE profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';
4、oracle查看用户密码何时过期
select expiry_date,username from dba_users;
5、将密码有效期由默认的180天修改成“无限制”，修改之后不需要重启动数据库，会立即生效
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
6、帐户再改一次密码

alter user 用户名 identified by 原密码;
7、使用修改后的用户登录，如果报“ORA-28000:用户已被锁”，解锁
alter user db_user account unlock;
commit;

六、数据库重装数据恢复

七、数据表授权

grant select,update,delete,insert ON PC_AQSC_PROCESSING_FLOW  to A5JXUPC;