ORACLE过程、函数
来源：互联网 发布：centos 锁屏时间 编辑：程序博客网 时间：2024/06/07 07:07
过程、函数
create or replace procedure p1
is
empname emp.ename%type;
begin
select ename into empname from emp where empno=7788;
dbms_output.put_line(empname);
end;
SQL> ed
SQL> /
Procedure created
SQL> exec p1;
SCOTT
PL/SQL procedure successfully completed
create or replace procedure p1(eno emp.empno%type)
is
empname emp.ename%type;
begin
select ename into empname from emp where empno=eno;
dbms_output.put_line(empname);
end;
SQL> ed
SQL> /
Procedure created
SQL> exec p1(7788);
SCOTT
PL/SQL procedure successfully completed
create or replace procedure p1(eno in emp.empno%type)
is
empname emp.ename%type;
begin
select ename into empname from emp where empno=eno;
dbms_output.put_line(empname);
end;
SQL> ed
SQL> /
Procedure created
SQL> exec p1(7788);
SCOTT
PL/SQL procedure successfully completed
create or replace procedure p1(eno in emp.empno%type,outname out emp.ename%type)
is
empname emp.ename%type;
begin
select ename into empname from emp where empno=eno;
outname:=empname;
end;
declare
empname emp.ename%type;
begin
p1(7788,empname);
dbms_output.put_line(empname);
end;
SQL> ed
SQL> /
SCOTT
PL/SQL procedure successfully completed
create or replace procedure p1(eno_name in out emp.ename%type)
is
empname emp.ename%type;
begin
select ename into empname from emp where empno=eno_name;
eno_name:=empname;
end;
declare
empname emp.ename%type;
begin
empname:=7788;
p1(empname);
dbms_output.put_line(empname);
end;
SQL> ed
SQL> /
SCOTT
PL/SQL procedure successfully completed
create or replace procedure p1
is
begin
update emp set ename='HUANGPei' where empno=7788;
end;
create or replace procedure p2
is
begin
update emp set ename='HUANGPei' where empno=7934;
p1;
commit;
end;
SQL> exec p2;
PL/SQL procedure successfully completed
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-4-2 2975.00 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7788 HUANGPei ANALYST 7566 1987-4-19 3000.00 20
7839 KING PRESIDENT 1981-11-17 5000.00 10
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
7900 JAMES CLERK 7698 1981-12-3 950.00 30
7902 FORD ANALYST 7566 1981-12-3 3000.00 20
7934 HUANGPei CLERK 7782 1982-1-23 1300.00 10
14 rows selected
create or replace procedure p1
is
begin
update emp set ename='HUANGPEI' where empno=7788;
commit;
end;
create or replace procedure p2
is
begin
update emp set ename='HUANGPEI' where empno=7934;
p1;
rollback;
end;
SQL> exec p2;
PL/SQL procedure successfully completed
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-4-2 2975.00 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7788 HUANGPEI ANALYST 7566 1987-4-19 3000.00 20
7839 KING PRESIDENT 1981-11-17 5000.00 10
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
7900 JAMES CLERK 7698 1981-12-3 950.00 30
7902 FORD ANALYST 7566 1981-12-3 3000.00 20
7934 HUANGPEI CLERK 7782 1982-1-23 1300.00 10
14 rows selected
create or replace procedure p1
is
PRAGMA AUTONOMOUS_TRANSACTION;
begin
update emp set ename='huangpei' where empno=7788;
end;
create or replace procedure p2
is
begin
update emp set ename='huangpei' where empno=7934;
p1;
commit;
end;
SQL> exec p2;
begin p2; end;
ORA-06519: 检测到活动的自治事务处理，已经回退
ORA-06512: 在"SCOTT.P1", line 5
ORA-06512: 在"SCOTT.P2", line 5
ORA-06512: 在line 2
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-4-2 2975.00 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7788 HUANGPEI ANALYST 7566 1987-4-19 3000.00 20
7839 KING PRESIDENT 1981-11-17 5000.00 10
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
7900 JAMES CLERK 7698 1981-12-3 950.00 30
7902 FORD ANALYST 7566 1981-12-3 3000.00 20
7934 HUANGPEI CLERK 7782 1982-1-23 1300.00 10
14 rows selected
主事务处理启动独立事务处理
然后主事务处理被暂停
自主事务处理子程序内的 SQL 操作
然后终止自主事务处理
恢复主事务处理
create or replace procedure p1
is
PRAGMA AUTONOMOUS_TRANSACTION;
begin
update emp set ename='huangpei' where empno=7788;
commit;
end;
create or replace procedure p2
is
begin
update emp set ename='huangpei' where empno=7934;
p1;
rollback;
end;
SQL> exec p2;
PL/SQL procedure successfully completed
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-4-2 2975.00 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7788 huangpei ANALYST 7566 1987-4-19 3000.00 20
7839 KING PRESIDENT 1981-11-17 5000.00 10
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
7900 JAMES CLERK 7698 1981-12-3 950.00 30
7902 FORD ANALYST 7566 1981-12-3 3000.00 20
7934 HUANGPEI CLERK 7782 1982-1-23 1300.00 10
14 rows selected
create or replace procedure p1
is
PRAGMA AUTONOMOUS_TRANSACTION;
begin
update emp set ename='HuangPei' where empno=7788;
rollback;
end;
create or replace procedure p2
is
begin
update emp set ename='HuangPei' where empno=7934;
p1;
commit;
end;
SQL> exec p2;
PL/SQL procedure successfully completed
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-4-2 2975.00 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7788 huangpei ANALYST 7566 1987-4-19 3000.00 20
7839 KING PRESIDENT 1981-11-17 5000.00 10
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
7900 JAMES CLERK 7698 1981-12-3 950.00 30
7902 FORD ANALYST 7566 1981-12-3 3000.00 20
7934 HuangPei CLERK 7782 1982-1-23 1300.00 10
14 rows selected
自主事务处理与主事务处理的状态无关
提交或回滚操作不影响主事务处理
自主事务处理的结果对其他事务是可见的
能够启动其他自主事务处理
函数
创建函数的语法：
CREATE [OR REPLACE] FUNCTION
<function name> [(param1,param2)]
RETURN <datatype> IS|AS
[local declarations]
BEGIN
Executable Statements;
RETURN result;
EXCEPTION
Exception handlers;
END;
create or replace function f1 return varchar2
is
begin
return 'Hi ,CO CO';
end;
SQL> ed
SQL> /
Function created
SQL> select f1 from dual;
F1
------------------------------------------------------------------
Hi ,CO CO
create or replace function f2(eno emp.empno%type) return emp.ename%type
is
rname emp.ename%type;
begin
select ename into rname from emp where empno=eno;
return rname;
end;
declare
ename emp.ename%type;
begin
ename := f2(7788);
dbms_output.put_line(ename);
end;
SQL> ed
SQL> /
SCOTT
PL/SQL procedure successfully completed
过 程
函 数
作为 PL/SQL 语句执行
作为表达式的一部分调用
在规格说明中不包含 RETURN 子句
必须在规格说明中包含 RETURN 子句
不返回任何值
必须返回单个值
可以包含 RETURN 语句，但是与函数不同，
它不能用于返回值
必须包含至少一条 RETURN 语句
- oracle 函数，过程，程序包
- Oracle存储过程，函数。
- ORACLE过程、函数
- oracle过程和函数
- oracle过程和函数
- Oracle 存储过程 函数
- oracle过程和函数
- oracle-过程函数编程
- oracle储存过程与函数
- oracle 存储过程 函数 包
- oracle 存储过程 函数 包
- oracle函数及存储过程
- ORACLE存储过程，函数加锁
- oracle 过程，函数 语法等
- oracle存储过程和函数
- oracle 存储过程和函数
- ORACLE 游标，存储过程，函数
- Oracle存储过程与函数
- javasript 操作option select一些常用方法
- 晃乱年华，谁的呢喃呓语？
- “云”环境下的教育软件展望
- 蒲公英。
- 使用spring MVC框架进行文件上传
- ORACLE过程、函数
- ADO.NET(一)
- 得意的时候需要被泼凉水
- [MacBook] 为 MacBook Pro 下的 Windows XP 安装 Built-in iSight 驱动
- ASP关于打印
- 网络安全部署探讨
- 正确面对自己的苦难
- 搞土地利用规划的朋友和ARCGIS制图的朋友们，相互交流下
- From model to machine
