oracle cursor

set serverout on;

DECLARE
	CURSOR c_dept IS select distinct a.username, b.profile, b.resource_name, b.limit from dba_users a, dba_profiles b where a.profile = b.profile and a.profile is not null and b.resource_type = 'PASSWORD' and b.resource_name = 'FAILED_LOGIN_ATTEMPTS' and b.limit <> 'UNLIMITED' and a.username not in (select c.username from dba_users_with_defpwd c) and a.username not in ('SYS','SYSTEM','SYSMAN','MGMT_VIEW', 'ANONYMOUS', 'FLOWS_FILES', 'WKPROXY', 'WKSYS', 'FLOWS_030000', 'OLAPSYS', 'APEX_PUBLIC_USER');
	vs_row2 c_dept%rowtype;
BEGIN
	DBMS_OUTPUT.PUT_LINE('------------------------');
	FOR vs_row2 IN c_dept
	LOOP
		-- DBMS_OUTPUT.PUT_LINE('Department:'|| vs_row2.profile||'-');
		-- alter profile tet limit failed_login_attempts unlimited;
		DBMS_OUTPUT.PUT_LINE('#############');
	END LOOP;
END;
/


-- 带游标<br>create or replace procedure SP_Test is
DECLARE

cursor c_dept is select distinct a.username, b.profile, b.resource_name, b.limit from dba_users a, dba_profiles b where a.profile = b.profile and a.profile is not null and b.resource_type = 'PASSWORD' and b.resource_name = 'FAILED_LOGIN_ATTEMPTS' and b.limit <> 'UNLIMITED' and a.username not in (select c.username from dba_users_with_defpwd c) and a.username not in ('SYS','SYSTEM','SYSMAN','MGMT_VIEW', 'ANONYMOUS', 'FLOWS_FILES', 'WKPROXY', 'WKSYS', 'FLOWS_030000', 'OLAPSYS', 'APEX_PUBLIC_USER');
vs_row2 c_dept%rowtype;
tempresult varchar2(1024);

begin
	for vs_row2 in c_dept loop
		begin
			tempresult := tempresult||vs_row2.profile;
			DBMS_OUTPUT.PUT_LINE('#############'||tempresult);
			alter profile 'test' limit failed_login_attempts unlimited;
		end;
	end loop;
end;
/

select distinct a.username, b.profile, b.resource_name, b.limit from dba_users a, dba_profiles b 
          where a.profile = b.profile and a.profile is not null and b.resource_type = 'PASSWORD' and b.resource_name = 'PASSWORD_LIFE_TIME' 
          and b.limit <> 'UNLIMITED' and a.username not in (select c.username from dba_users_with_defpwd c) 
          and a.username not in ('SYS','SYSTEM','SYSMAN','MGMT_VIEW', 'ANONYMOUS', 'FLOWS_FILES', 'WKPROXY', 'WKSYS', 'FLOWS_030000', 'OLAPSYS', 'APEX_PUBLIC_USER' , 'DBSNMP');

select distinct a.username, b.profile, b.resource_name, b.limit from dba_users a, dba_profiles b where a.profile = b.profile and a.profile is not null and b.resource_type = 'PASSWORD' and b.resource_name = 'FAILED_LOGIN_ATTEMPTS' and b.limit <> 'UNLIMITED' and a.username not in (select c.username from dba_users_with_defpwd c) and a.username not in ('SYS','SYSTEM','SYSMAN','MGMT_VIEW', 'ANONYMOUS', 'FLOWS_FILES', 'WKPROXY', 'WKSYS', 'FLOWS_030000', 'OLAPSYS', 'APEX_PUBLIC_USER');

alter profile PROFILE_DNADBM_XY_0115 limit failed_login_attempts unlimited;