当前位置:Gxlcms > 数据库问题 > 『ORACLE』 PLSQL创建存储过程(11g)

『ORACLE』 PLSQL创建存储过程(11g)

时间:2021-07-01 10:21:17 帮助过:24人阅读

不带参数的存储过程

SQL> create or replace procedure get_time is
2 begin
3 dbms_output.put_line(sysdate);
4 end;
5 /

Procedure created.

SQL> exec get_time;

15-MAY-17

PL/SQL procedure successfully completed.

带in参数的存储过程

SQL> create or replace procedure add_dept
2 (v_dept_id in number, v_dept_name in varchar2) is
3 begin
4 insert into dept(deptno, dname)
5 values (v_dept_id,v_dept_name);
6 dbms_output.put_line(‘ inserted ‘ || SQL%ROWCOUNT || ‘ row ‘);
7 end;
8 /

Procedure created.

SQL> var s1 number
SQL> var s2 varchar2
SQL> exec :s1 :=60

PL/SQL procedure successfully completed.

SQL> exec :s2 :=‘sss‘

PL/SQL procedure successfully completed.

SQL> exec add_dept(:s1,:s2);
inserted 1 row

PL/SQL procedure successfully completed.

SQL> print s1 s2;

S1
----------
60


S2
--------------------------------
sss

SQL> select deptno, dname from dept;

DEPTNO DNAME
---------- --------------
10 ACCOUNTING
20 RESEARCH
30 SALES
40 OPERATIONS
50 sam
60 sss

带in和out参数的存储过程

SQL> create or replace procedure compute
2 (num1 in out number,num2 in out number)
3 is
4 v1 number;
5 v2 number;
6 begin
7 v1 := num1/num2;
8 v2 := mod(num1,num2);
9 num1 := v1;
10 num2 := v2;
11 end;
12 /

Procedure created.

 

『ORACLE』 PLSQL创建存储过程(11g)

标签:out   get   success   ons   ted   tput   res   char   tno   

人气教程排行