循环语句:
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;