DBMS_SQL例子

abstractcyj發表於2013-10-22
SQL
DECLARE
  l_cursor   PLS_INTEGER;
  l_wareid   t_ware_base.wareid%TYPE;
  l_warename t_ware_base.warename%TYPE;
  l_retval   PLS_INTEGER;
BEGIN
  l_cursor := dbms_sql.open_cursor;
  dbms_sql.parse(l_cursor,
                 'select wareid, warename from t_ware_base where wareid like :wareid ',
                 1);

  dbms_sql.define_column(l_cursor, 1, l_wareid);
  dbms_sql.define_column(l_cursor, 2, l_warename, 100);
  --dbms_sql.define_column(
  dbms_sql.bind_variable(l_cursor, ':wareid', '%58%');
  l_retval := dbms_sql.execute(l_cursor);

  dbms_output.put_line('l_retval = ' || l_retval);
  WHILE dbms_sql.fetch_rows(l_cursor) > 0 LOOP
    dbms_sql.column_value(l_cursor, 1, l_wareid);
    dbms_sql.column_value(l_cursor, 2, l_warename);
    dbms_output.put_line(l_wareid || '-' || l_warename);
  END LOOP;

  /*IF dbms_sql.fetch_rows(l_cursor) > 0 THEN
    dbms_sql.column_value(l_cursor, 1, l_wareid);
    dbms_output.put_line(l_wareid);
  ELSE
    dbms_output.put_line('No rows returned');
  END IF;*/
  dbms_sql.close_cursor(l_cursor);
END;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8520577/viewspace-774798/，如需轉載，請註明出處，否則將追究法律責任。

相關文章