±±´óÇàÄñoracleѧϰ±Ê¼Ç23 24

×Ó³ÌÐò ÃüÃûµÄpl/sql¿é
create procedure ¹ý³ÌÃû [²ÎÊý1 in|out|in out¡­]
    ¾Ö²¿±äÁ¿ÉùÃ÷
    Ö´ÐÐÓï¾ä
in
out
in out
    ¹ý³ÌÃû(²ÎÊýÀï±í); create or replace
procedure add123 as
i integer;
j integer;
begin
i:=1;
j:=2;
dbms_output.put_line(i+j);
end;
execute add123;
×¢ÒâÕâÀï´´½¨¹ý³ÌºÍÖ´Ðв»ÄÜ·ÅÔÚÒ»Æ𣬷ñÔÚexecute»áËæ¹ý³Ì¶¨ÒåдÈë¹ý³ÌÖУ¬sql developÖвâÊÔÈç´Ë£¬²»ÖªµÀΪʲô¡£
CREATE OR REPLACE
PROCEDURE QUERYSTUNAME
( stuid STUDENT.STU_ID%TYPE)
AS
sname student.stu_name%type;
ssex student.stu_sex%type;
BEGIN
select stu_name,stu_sex into sname,ssex from student where stu_id = stuid;
dbms_output.put_line(sname||ssex);
EXCEPTION
when No_Data_Found then
dbms_output.put_line('no data found');
when Too_Many_Rows then
dbms_output.put_line('to many rows');
when others then
dbms_output.put_line('other error');
END QUERYSTUNAME;

execute querystuname(1); º¯Êý
create function º¯ÊýÃû [²ÎÊýÁбí]
    ¾Ö²¿±äÁ¿ÉùÃ÷
    ¿ÉÖ´ÐÐÓï¾ä
end;
Á½ÖÖ·ÃÎÊ·½Ê½
sqlÓï¾ä
½ö½ÓÊÜin²ÎÊý£¬·µ»ØÖµÀàÐͲ»ÄÜ´ø¾«¶È¡£
CREATE OR REPLACE
FUNCTION GETNAME(stuid varchar)
RETURN VARCHAR AS
stuname student.stu_name%type;
BEGIN
select stu_name into stuname from student where stu_id = stuid;
RETURN stuname;
EXCEPTION
when No_Data_Found then
dbms_output.put_line('no data found');
when Too_Many_Rows then
dbms_output.put_line('to many rows');
when others then
dbms_output.put_line('other error');
END GETNAME;

declare
begin
dbms_output.put_line(getname(1));
end;

¹ý³Ì
º¯Êý
×÷Ϊpl/sqlÓï¾äÖ´ÐÐ
×÷Ϊ±í´ïʽһ²¿·Öµ÷ÓÃ
ÔÚ¹æ¸ñ˵Ã÷Öв»°üº¬return×Ó¾ä
±ØÐëÔÚ¹æ¸ñ˵Ã÷Öаüº¬return×Ó¾ä
¿ÉÒÔ·µ»ØÈκÎÖµ
·µ»Øµ¥Öµ
¿ÉÒÔ°üº¬returnÓï¾ä£¬µ«ÊDz»ÄÜÓÃÓÚ·µ»ØÖµ
±ØÐë°üº¬returnÓï¾ä