orcale 存储过程样例

循环语句：

  temValue number:=1;

 function add_account_func(accountnum in varchar2,accounttype in varchar2,balance in number,customerid in varchar2) return number is

  begin
    for c1 in (select * from scott.account_trade) loop
      temValue:=temValue+c1.t_balance;
    end loop;

    insert into scott.account (accountnumber,accounttype,balance,customerid) values(accountnum,accounttype,balance+temValue,customerid);
    return(temValue);
  end;

SELECT INTO语句：

CREATE OR REPLACE  PROCEDURE "SCOTT"."GETEMP" (
 eno in NUMBER,
 nameout out VARCHAR2
)
as
VS_ENAME VARCHAR2(10);
begin
 select ENAME into vs_ename from scott.emp where empno=eno;
 nameout:=VS_ENAME;
 DBMS_OUTPUT.PUT_LINE('VS_ENAME:'||VS_ENAME);
end;

if 语句：

 动态sql 语句：

declare
  -- Local variables here
  i integer;
  v_processed integer;
  v_sql varchar2(1000);
  v_cursor integer;
  v_row integer:=0;
begin
  -- Test statements here
  v_cursor:=dbms_sql.open_cursor;
  v_sql:='select balance from scott.account'||' where accountnumber<>''NL0005''';
  dbms_sql.parse(v_cursor,v_sql,dbms_sql.native);
  dbms_sql.define_column(v_cursor,1,i);
  v_processed:=dbms_sql.execute(v_cursor);
  v_row:=dbms_sql.fetch_rows(v_cursor);
  while v_row>0 loop
   dbms_sql.column_value(v_cursor,1,i);
   dbms_output.put_line('balance:'||i);
   v_row:=dbms_sql.fetch_rows(v_cursor);
  end loop;
end;