oracle 預存程序

標籤：har   values   name   arch   ace   false   scott   tput   replace   

--向表中插入一條記錄create or replace procedure pro_insertDeptisbegin  insert into scott.dept values(‘77‘,‘dog‘,‘dog‘);  commit;  end pro_insertDept;--執行begin  pro_insertDept;  end;--定義三個輸入參數，插入一條記錄create or replace procedure pro_InsetIn(num_deptno in number,var_ename in varchar2,var_loc in varchar2) is begin  insert into scott.dept values(num_deptno,var_ename,var_loc);  commit;  end pro_InsetIn;  ----------------------------------------  begin    pro_InsetIn(83,‘dog88‘,‘dog88‘);    end;--定義out參數create or replace procedure pro_selectdept(num_deptno in number,var_dname out scott.dept.dname%type,var_loc out scott.dept.loc%type)is begin  select dname,loc into var_dname,var_loc  from scott.dept where scott.dept.deptno=num_deptno;  end pro_selectdept;  -------------------------------------  declare  var_dname scott.dept.dname%type;  var_loc scott.dept.loc%type;  begin    pro_selectdept(4,var_dname,var_loc);    dbms_output.put_line(var_dname);    end;    --in out 參數create or replace procedure pro_square(num in out number,flag in boolean)is i int:=2;begin  if flag then    num:=power(num,i);    else      num:=sqrt(num);      end if;      end;------------------------------declarenum number:=20;flag boolean:=false;begin  pro_square(num,true);  dbms_output.put_line(num);  end;--in 參數的預設值create or replace procedure inser_deptDefault(       num_deptno in number,       var_dname in scott.dept.dname%type default ‘dog2017923‘)isbegin  insert into scott.dept(deptno,dname) values(num_deptno,var_dname);  commit;  end; --------------------- begin   inser_deptDefault(47);   end;--指定參數名稱傳遞參數(預存程序定義的參數)declare row_dept scott.dept%rowtype;begin  inser_deptDefault(41,var_dname=>‘dog2222222‘);  select *  into row_dept from scott.dept where deptno=41;  dbms_output.put_line(row_dept.dname);end;         select * from scott.dept

 

oracle 預存程序