stored procedure 初探

 

stored procedure 初探

1.基本

set SERVEROUTPUT ON
DECLARE
    l_name member.name%type := '派大星';
    l_idn  member.idn%type;
    l_cursor INTEGER;
    l_retval INTEGER;
BEGIN
    l_cursor := dbms_sql.open_cursor;
    dbms_sql.parse(l_cursor,'select idn from member where name = :name',1);
    dbms_sql.define_column(l_cursor, 1,l_idn,10);
    dbms_sql.bind_variable(l_cursor, ':name',l_name);
    l_retval := dbms_sql.execute(l_cursor);
    IF (dbms_sql.fetch_rows(l_cursor) > 0) THEN
        dbms_sql.column_value(l_cursor,1,l_idn);
        DBMS_OUTPUT.PUT_LINE(l_idn);
    END IF;
    dbms_sql.close_cursor(l_cursor);
END;

 2.隱含轉換

set SERVEROUTPUT ON
DECLARE
    l_name member.name%type := '派大星';
    l_idn  member.idn%type;

BEGIN
    select idn into l_idn
    from member 
    where name = l_name;
    DBMS_OUTPUT.PUT_LINE(l_idn);
    
END;