第77次：2014-12-26

botang · 发表于 2014-12-28 10:41:40

本帖最后由 botang 于 2014-12-30 12:45 编辑

select * from dba_tables t where t.owner='HR';
select * from hr.SCHEDULER_TEST;
select * from dbA_sequences s where s.sequence_owner='HR';
drop table hr.SCHEDULER_TEST;
drop sequence hr.SCHEDULER_TEST_SEQ;
create table hr.SCHEDULER_TEST ( test_id number , program_name varchar2(30) , test_time date ) ;
create sequence hr.SCHEDULER_TEST_SEQ order;
select * from hr.SCHEDULER_TEST order by test_time;
truncate table hr.scheduler_test;
DECLARE
l_enqueue_options DBMS_AQ.enqueue_options_t;
l_message_properties DBMS_AQ.message_properties_t;
l_message_handle RAW(16);
l_queue_msg t_event_queue_payload;
BEGIN
l_queue_msg := t_event_queue_payload('give_me_a_prod');
DBMS_AQ.enqueue(queue_name => 'event_queue',
enqueue_options => l_enqueue_options,
message_properties => l_message_properties,
payload => l_queue_msg,
msgid => l_message_handle);
COMMIT;
END;
------
BEGIN
sys.dbms_scheduler.disable('"HR"."CHAIN1"');
sys.dbms_scheduler.define_chain_rule(
chain_name => '"HR"."CHAIN1"',
condition => 'step1 completed',
rule_name => 'RULE2',
comments => 'rule2',
action => 'START "STEP3"');
sys.dbms_scheduler.define_chain_rule(
chain_name => '"HR"."CHAIN1"',
condition => 'step1 failed',
rule_name => 'RULE3',
comments => 'rule3',
action => 'START "STEP2"');
sys.dbms_scheduler.define_chain_rule(
chain_name => '"HR"."CHAIN1"',
condition => 'step2 completed',
rule_name => 'RULE5',
comments => 'rule5',
action => 'END');
sys.dbms_scheduler.enable('"HR"."CHAIN1"');
END;
<div class="blockcode"><blockquote>create or replace procedure hr.proc_test( p_1 in number , p_2 out number)
is
begin
update employees set salary=10000 where employee_id=p_1;
select salary into p_2 from employees where employee_id=p_1;
end;
declare
v_2 number;
begin
proc_test(100,v_2);
dbms_output.put_line('The salary of employee 100 is '||v_2||'.');
end;
create or replace procedure proc_test_io( p_1 in out number )
is
begin
p_1 := p_1*2;
end;
declare
v_1 number;
begin
v_1 :=2;
proc_test_io(v_1);
dbms_output.put_line(v_1);
end;
select * from scheduler_test;
drop table scheduler_test;
create table scheduler_test (id number , created_date date );
alter table scheduler_test add ( event_name varchar2(30) );
drop sequence scheduler_test_seq;
create sequence scheduler_test_seq order ;
create or replace procedure procnew( p_1 sys.t_event_queue_payload )
is
BEGIN
INSERT INTO scheduler_test
VALUES (scheduler_test_seq.NEXTVAL, SYSDATE, p_1.event_name );
COMMIT;
END;
select * from dba_scheduler_program_args pa where pa.owner='HR';
begin
dbms_scheduler.define_metadata_argument (
program_name => 'hr.programnew',
argument_position => 1 ,
metadata_attribute => 'EVENT_MESSAGE') ;
end;

复制代码

---

begin
raise_application_error('-20001','Something wrong');
end;
----