oracle 普通sql语句,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

&