Oracle常用Sql语句

1. 创建视图：
CREATE OR REPLACE VIEW SM_V_UNIT_AUTH AS
SELECT T2.UNIT_ID,
        T2.SUPER_UNIT_ID,
        T1.AUTH_ID,
        T1.AUTH_NAME,
        T1.AUTH_TYPE,
        T1.AUTH_DESC,
        T1.CREATE_UNIT,
        T1.CREATE_DATE,
        (SELECT UNIT_NAME from SM_T_UNIT C WHERE C.UNIT_ID=T1.CREATE_UNIT) CREATE_UNIT_NAME,
        T1.VALID_FLAG,
        T1.UNIT_NAME,
        T1.EDITABLE,
        T1.AUTHABLE
 from
 (SELECT A.*,
 B.UNIT_ID,
 B.UNIT_NAME,
 (SELECT UAR1.EDITABLE from SM_T_UNIT_AUTH_REL UAR1 WHERE UAR1.AUTH_ID = A.AUTH_ID
 AND UAR1.UNIT_ID = B.UNIT_ID) AS EDITABLE,
 (SELECT UAR1.AUTHABLE from SM_T_UNIT_AUTH_REL UAR1 WHERE UAR1.AUTH_ID = A.AUTH_ID
 AND UAR1.UNIT_ID =  B.UNIT_ID) AS AUTHABLE  from SM_T_AUTH A,SM_T_UNIT B
 WHERE A.AUTH_ID IN (SELECT UAR1.AUTH_ID from SM_T_UNIT_AUTH_REL UAR1
 WHERE UAR1.UNIT_ID = B.UNIT_ID)
 ORDER BY A.AUTH_NAME) T1,SM_T_UNIT T2 where T1.UNIT_ID=T2.UNIT_ID
;
==========================================================================================
==========================================================================================
2. 创建存储过程：
create or replace procedure sm_sp_update_unitAuthRel is
  cursor cur_unitAuthRels is select * from sm_t_unit_auth_rel uar where uar.editable = 'F';
  v_unitAuthRel sm_t_unit_auth_rel%rowtype;
  v_today varchar2(10);
  v_validdate varchar2(10);
begin
  open cur_unitAuthRels;
  v_today :=to_char(sysdate,'yyyy-mm-dd');
  loop
&