创建上下文
create or replace context stud_security using hu.pkg_security accessed globally;
-- 创建程序包
create or replace package hu.pkg_security is
procedure set_security(a_vcparameter varchar2,a_vcvalue varchar2);
function studname
return varchar2;
end pkg_security;
/
-- 创建包体
create or replace package body hu.pkg_security is
procedure set_security(a_vcparameter varchar2,a_vcvalue varchar2)
is
begin
dbms_session.set_context(namespace=>'STUD_SECURITY',
attribute=>a_vcparameter,value=>a_vcvalue);
end set_security;
function studname
return varchar2
is
vcstudname varchar2(20):=null;
begin
select 姓名 into vcstudname from hu.学生基本信息
where 学号=to_number(sys_context('stud_security','学号'));
return vcstudname;
end studname;
end pkg_security;
/
-- 设置输出
set serveroutput on
-- 通过调用新包设置命名空间stud_security中学号的值，并查询出学生姓名
declare
lvc_学号 varchar2(20):=null;
lvc_姓名 varchar2(8):=null;
begin
pkg_security.set_security('学号','222005389081084');
begin
select sys_context('stud_security','学号') into lvc_学号 from dual;
end;
dbms_output.put_line('value for stud_security.学号 via userenv call is:' ||lvc_学号);
lvc_姓名:=pkg_security.studname;
dbms_output.put_line('student name:'|| lvc_姓名);
end;
/
1.create or replace procedure alter_constraints(para_alter in varchar2) is
v_cursorid integer;
v_strcon varchar2(300);
v_strtri varchar2(300);
v_fknum number:=0;
v_trinum number:=0;
v_sqlerrm varchar2(600);
cursor c_con is
select * from user_constraints where constraint_type='R';
r_con c_con% rowtype;
cursor c_trigger is
select trigger_name,status from user_triggers;
notfound boolean;
begin
dbms_output.put_line('begin time:'||to_char(sysdate));
if(upper(para_alter)='DISABLE' or upper(para_alter)='ENABLE') then
open c_con;
v_cursorid:=dbms_sql.open_cursor;
loop
fetch c_con into r_con;
notfound:=c_con% notfound;
exit when notfound;
begin
if(upper(para_alter)='DISABLE' and r_con.status='ENABLED' or
upper(para_alter)='ENABLE' and r_con.status='DISABLED') then
v_strcon:='alter table '||r_con.owner||'.'||r_con.table_name||' '||
upper(para_alter)||' constraint '||r_con.constraint_name;
dbms_sql.parse(v_cursorid,v_strcon,dbms_sql.v7);
v_fknum:=v_fknum+1;
end if;
exception
when others then
v_sqlerrm:=substr(sqlerrm,1,600);
dbms_output.put_line('error:'||' '||v_sqlerrm);
end;
end loop;
close c_con;
dbms_output.put_line('===外键被'||para_alter||',总数'||to_char(v_fknum)||'===');
for t_trigger in c_trigger loop
begin
if(upper(para_alter)='DISABLE' and t_trigger.status='ENABLED' or
upper(para_alter)='ENABLE' and t_trigger.status='DISABLED') then
v_strtri:='alter trigger '||t_trigger.trigger_name||' '||upper(para_alter);
dbms_sql.parse(v_cursorid,v_strtri,dbms_sql.v7);
v_trinum:=v_trinum+1;
end if;
exception
when others then
v_sqlerrm:=substr(sqlerrm,1,600);
dbms_output.put_line('error:'||' '||v_sqlerrm);
end;
end loop;
dbms_output.put_line('===触发器被'||para_alter||',总数'||to_char(v_trinum)||'===');
dbms_sql.close_cursor(v_cursorid);
else
dbms_output.put_line('error:输入参数不正确,参数应为enable或disable！');
end if;
dbms_output.put_line('end time:'||to_char(sysdate));
end alter_constraints;
