存储过程学习 第11章 之2

--------游标使用 
declare
	cursor c1 is
		select * from emp;
begin
	for rec in c1 loop
		dbms_output.put_line(rec.ename||','||rec.job);
	end loop;
end;

------------隐式游标
drop table templ;
create global temporary table templ  --全局临时表
(name varchar2(10),deptno varchar2(15),sal varchar2(20));
declare
	cursor c1 is 				--cursor
		select * from emp;
	cursor_rec c1%rowtype;                  --cursor variable
	
	cursor c2(v emp.deptno%type) is 
		select * from emp where deptno=v;--带参数游标
	
begin
 	open c1;  --open
	loop
		fetch c1 into cursor_rec; --fetch
		exit when c1%notfound;
		insert into templ values
		(cursor_rec.ename,cursor_rec.deptno,cursor_rec.sal);	
	end loop;
	close c1;	--close
end;

select replace('aaa-hell-bbb','-') from dual;
---------------------管道
grant execute on dbms_pipe to scott;
execute p1;

CREATE OR REPLACE PROCEDURE p1 IS
  status   INTEGER;
  response VARCHAR2(2000);
BEGIN
  dbms_pipe.reset_buffer; --重置缓冲
  dbms_pipe.pack_message('This is message 1'); --pack
  dbms_pipe.pack_message('This is message 2');
  status := dbms_pipe.send_message('HOME_OF_P2'); --send
  --status := dbms_pipe.receive_message('HOME_OF_P1', 60); --receive
  --dbms_pipe.unpack_message(response); --unpack
  --dbms_output.put_line('p1 receive:' || response);
END;

CREATE OR REPLACE PROCEDURE p2 IS
  status       INTEGER;
  message1     VARCHAR2(2000);
  message2     VARCHAR2(2000);
  message      VARCHAR2(2000);
  message_part VARCHAR2(2000);
  empty_buffer EXCEPTION;
  PRAGMA EXCEPTION_INIT(empty_buffer, -6556);
BEGIN
  FOR i IN 1 .. 2 LOOP
    status := dbms_pipe.receive_message('HOME_OF_P2', 60); --receive 等待
    BEGIN
      WHILE (dbms_pipe.next_item_type = 9) LOOP
        dbms_pipe.unpack_message(message_part); --unpack
        message := message || '-' || message_part;
      END LOOP;
    EXCEPTION
      WHEN empty_buffer THEN
        NULL;
    END;
  END LOOP;
  dbms_pipe.pack_message('Got p1 message'); --pack
  status := dbms_pipe.send_message('HOME_OF_P1'); --send
  dbms_output.put_line('p2 receive:' || message);
END;




----------------trigger
create or replace trigger professors_aur
after update on professors --更新教授表后
for each row 								--对每一行
when (old.salary<>new.salary)
begin
	-----:new.prof_name
end;

---------自定义异常
customer_exp expection;
raise ...;
----------错误作为异常抛出

--------------警报
  PROCEDURE send_alert(message IN VARCHAR2) IS
  BEGIN
    dbms_alert.signal('email_notification', message); --send
  END;

  PROCEDURE process_alerts IS
    professor_email VARCHAR2(100);
    status          INTEGER;
  BEGIN
    dbms_alert.register('email_notification'); --register (先注册)
    FOR i IN 1 .. 3 LOOP
      dbms_alert.waitone('email_notification', professor_email, status, 10); --wait
      IF status = 0 THEN
        EXIT WHEN professor_email = 'END';
        email_pkg.send('[e-mail]', professor_email, 'Salary', 'Salary has change');
      END IF;
    END LOOP;
  END;

CREATE OR REPLACE PACKAGE BODY email_pkg IS
  g_smtp_server      CONSTANT VARCHAR2(20) := '00.00.00.00';
  g_smtp_server_port CONSTANT PLS_INTEGER := 25;

  PROCEDURE send(p_sender    IN VARCHAR2,
                 p_recipient IN VARCHAR2,
                 p_message   IN VARCHAR2,
                 p_subject   IN VARCHAR2) IS
    mail_conn utl_smtp.connection;
  BEGIN
    mail_conn := utl_smtp.open_connection(g_smtp_server, g_smtp_server_port); --open
    utl_smtp.helo(mail_conn, g_smtp_server);
    utl_smtp.mail(mail_conn, p_sender);
    utl_smtp.rcpt(mail_conn, p_recipient);
  
    utl_smtp.open_data(mail_conn);
    utl_smtp.write_data(mail_conn, 'From: "' || p_sender || '" <' ||
                         p_sender || '>' || utl_tcp.CRLF);
    utl_smtp.write_data(mail_conn, 'To: "' || p_recipient || '" <' ||
                         p_recipient || '>' || utl_tcp.CRLF);
    utl_smtp.write_data(mail_conn, 'Subject: ' || p_subject || utl_tcp.CRLF);
    utl_smtp.write_data(mail_conn, utl_tcp.CRLF || p_message);
    utl_smtp.quit(mail_conn);
  END;
END email_pkg;

/****************
	aaaa,bbbb,ccc
	
*****************/
create or replace function next_token 
	(base_string in varchar2,look_for in varchar2,startAt in integer,
	positionAt out integer) return boolean is
	result integer;
begin
	instr(base_string,look_for,startAt)
end;

select instr('aaaa,bbb,ccc,dd',',',1,2) from dual;

declare
	baseString varchar2(20):='aa,bbbb,ccc,ddd';
	token varchar2(5):=',';
	a natural:=1;
	b natural:=1;
begin
	loop
		b:=instr(baseString,token,a);
		if b=0 then 
			dbms_output.put_line(substr(baseString,a));
			exit;
		end if;
		dbms_output.put_line(substr(baseString,a,b-a));
		a:=b+1;
	end loop;
end;

select translate('abcabcbca','abc','xyz') from dual;
select initcap('NEW YORK') from dual;
select sqrt(2) from dual;

declare 
	n number;
begin
	dbms_random.seed(123333456);
	n:=dbms_random.value(10,100);
	dbms_output.put_line(n);
end;