ÍæתOracle£¨7£©

||-------  pl/sql »ù´¡ -------||
pl/procedural language ¹ý³ÌÓïÑÔ
//´´½¨±í
SQL> create table mytest(
  2  name varchar2(30),
  3  pwd varchar2(30));
//´´½¨¹ý³Ì
create procedure sp_pro1 is
create or replace procedure sp_pro1 is --Èç¹û´æÔÚ¼´Ìæ»»
begin
--Ö´Ðв¿·Ö
insert into mytest values('valen','123');
--½áÊø²¿·Ö
end;
SQL> create or replace procedure sp_pro2 is
  2  begin
  3  --Ö´Ðв¿·Ö
  4  delete from mytest where name='valen';
  5  --½áÊø²¿·Ö
  6  end;
  7  /
//²é¿´¹ý³ÌµÄ´íÎóÐÅÏ¢
show error;
//ÈçºÎµ÷Óô洢¹ý³Ì
1.exec ¹ý³ÌÃû£¨²ÎÊýÖµ1£¬²ÎÊýÖµ2£©£»
2.call ¹ý³ÌÃû£¨²ÎÊýÖµ1£¬²ÎÊýÖµ2£©£»
//pl/sql±à³Ì¹æ·¶
1.µ¥ÐÐ×¢ÊÍ --
2.¶àÐÐ×¢ÊÍ/*...*/
3.¶¨Òå±äÁ¿£¬v_×÷Ϊǰ׺
4.¶¨Òå³£Á¿£¬c_×÷Ϊǰ׺
5.¶¨ÒåÓα꣬_cursor×÷Ϊºó׺
6.¶¨ÒåÀýÍ⣬e_×÷Ϊǰ׺
//¿é½á¹¹ÊÂÒËͼ
declear
/* ¶¨Ò岿·Ö--³£Á¿£¬±äÁ¿£¬Óα꣬ÀýÍ⣬¸´ÔÓÊý¾ÝÀàÐÍ */
begin
/* Ö´Ðв¿·Ö--pl/sql,sqlÓï¾ä */
exception
/* ÀýÍâ´¦Àí²¿·Ö--´¦ÀíÔËÐеĸ÷ÖÖ´íÎó */
end;
//ʵÀý1
set serveroutput on --´ò¿ªÊä³öÑ¡Ïî
begin
 dbms_output.put_line('hello'); --put_lineÊÇdbms_output°üÖеÄÒ»¸ö¹ý³Ì
end;
//ʵÀý2
declare
 v_ename varchar2(5); --¶¨Òå×Ö·û´®±äÁ¿
begin
 select ename into v_ename from emp where empno=&no;
 dbms_output.put_line('¹ÍÔ±Ãû:'||v_ename);
end;
//ʵÀý3  no_data_found
declare
 v_ename varchar2(5); --¶¨Òå×Ö·û´®±äÁ¿
begin
 select ename into v_ename from emp where empno=&no;
 dbms_output.put_line('¹ÍÔ±Ãû:'||v_ename);
--Òì³£´¦Àí
exception
 when no_data_found then
 dbms_output.put_line('¸Ã±àºÅ²»´æÔÚ£¬ÇëÖØÐÂÊäÈë');
end;
//ʵÀý4
1.¿ÉÒÔÊäÈë¹ÍÔ±Ãû£¬Ð¹¤×Ê£¬¿ÉÐ޸ĹÍÔ±µÄ¹¤×Ê
create procedure sp_pro3(spName varchar2,newSal number) is
begin
--Ö´Ðв¿·Ö,¸ù¾ÝÓû§ÃûÐ޸Ť×Ê
update emp set sal=newSal where ename=spName;
end;
2.µ÷Óùý³Ì
exec sp_pro3('VALEN',3232.3);
3.ÈçºÎÔÚja