CREATE OR REPLACE PROCEDURE 存储过程名字
(
参数1 IN NUMBER,
参数2 IN NUMBER
) IS
变量1 INTEGER :=0;
变量2 DATE;
BEGIN
END 存储过程名字 2.SELECT INTO STATEMENT
将select查询的结果存入到变量中,可以同时将多个列存储多个变量中,必须有一条
记录,否则抛出异常(如果没有记录抛出NO_DATA_FOUND)
例子:
BEGIN
SELECT col1,col2 into 变量1,变量2 FROM typestruct where xxx;
EXCEPTION
WHEN NO_DATA_FOUND THEN
xxxx;
END;
...
3.IF 判断
IF V_TEST=1 THEN
BEGIN
do something
END;
END IF;
4.while 循环
WHILE V_TEST=1 LOOP
BEGIN
XXXX
END;
END LOOP;
5.变量赋值
V_TEST := 123; 6.用for in 使用cursor
...
IS
CURSOR cur IS SELECT * FROM xxx;
BEGIN
FOR cur_result in cur LOOP
BEGIN
V_SUM :=cur_result.列名1+cur_result.列名2
END;
END LOOP;
END;
7.带参数的cursor
CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID;
OPEN C_USER(变量值);
LOOP
FETCH C_USER INTO V_NAME;
EXIT FETCH C_USER%NOTFOUND;
do something
END LOOP;
CLOSE C_USER;
create or replace procedure 存储过程名(param1 in type,param2 out type)
as
变量1 类型(值范围);
变量2 类型(值范围);
Begin
Select count(*) into 变量1 from 表A where列名=param1;
If (判断条件) then
Select 列名 into 变量2 from 表A where列名=param1;
Dbms_output。Put_line(‘打印信息');
Elsif (判断条件) then
Dbms_output。Put_line(‘打印信息');
Else
Raise 异常名(NO_DATA_FOUND);
End if;
Exception
When others then
Rollback;
End;
create or replace procedure find_emp(emp_no in number:=7900)asempname varchar2(20);beginselect ename into empname from emp where empno=emp_no;dbms_output.put_line('雇员姓名是 '||empname);exceptionwhen no_data_found thendbms_output.put_line('雇员编号未找到');end find_emp;
create or replace procedure test(value1 varchar2,value2 out number) is identity number; begin select sal into identity from emp where empno=value1; if identity<2000 then value2:=1000; else value2:=500; end if; end;
调用带OUT参数的过程:
declare value2 number; begin test('7900',value2); dbms_output.put_line(value2); end;
示例3:
演示创建带IN OUT参数的过程
create or replace procedure swap(p1 in out number,p2 in out number) is v_temp number; begin v_temp:=p1; p1:=p2; p2:=v_temp; end;
CREATE [OR REPLACE] PACKAGE package_name IS|AS [Public type and item declarations] [Subprogram specifications] END [package_name];
其中:package_name是包的名称。
Public type and item declarations是声明类型、常量、变量、异常和游标等。 Subprogram specifications声明PL/SQL子程序。
示例6:
演示创建程序包规范
create or replace package pack_op is procedure pro_print_ename(id number); procedure pro_print_sal(id number); function fun_re_date(id number) return date; end;
创建包主体的语法:
CREATE [OR REPLACE] PACKAGE BODY package_name IS|AS [Public type and item declarations] [Subprogram bodies] [BEGIN Initialization_statements] END [package_name];
其中:package_name是包的名称。
Public type and item declarations是声明类型、常量、变量、异常和游标等。
Subprogram bodies是定义公共和私有PL/SQL子程序。
示例7:演示创建程序包主体
create or replace package body pack_op is procedure pro_print_ename(id number) is name emp.ename%type; begin select ename into name from emp where empno=id; dbms_output.put_line('职员姓名:'||name); end pro_print_ename; procedure pro_print_sal(id number) is salary emp.sal%type; begin select sal into salary from emp where empno=id; dbms_output.put_line('职员工资:'||salary); end pro_print_sal; function fun_re_date(id number) return date is bedate emp.hiredate%type; begin select hiredate into bedate from emp where empno=id; return bedate; end fun_re_date; end pack_op;
示例8:调用程序包中创建的过程和函数
exec pack_op.pro_print_ename(7900); exec pack_op.pro_print_sal(7900); select pack_op.fun_re_date(7900) from dual;
示例9:演示程序包中的游标 创建包规范
create or replace package pack_emp is cursor cur_emp return emp%rowtype; procedure pro_cur; end pack_emp;
创建包主体
create or replace package body pack_emp is cursor cur_emp return emp%rowtype is select * from emp; procedure pro_cur is rec_emp emp%rowtype; begin open cur_emp; loop fetch cur_emp into rec_emp; exit when cur_emp%notfound; if rec_emp.sal<1000 then dbms_output.put_line('员工工资:'||rec_emp.sal||',需加倍努力争取提高工资'); elsif rec_emp.sal>=1000 and rec_emp.sal<2000 then dbms_output.put_line('员工工资:'||rec_emp.sal||',工资一般,争取搞个部门经理做做'); else dbms_output.put_line('员工工资:'||rec_emp.sal||',工资不错,争取搞个总经理做做'); end if; end loop; end pro_cur; end pack_emp;
调用程序包中的过程以调用程序包中的游标
exec pack_emp.pro_cur;
示例10:存储过程返回游标的子程序包(此程序包返回r_cur游标)
CREATE OR REPLACE package SCOTT.pk_wt is type mytype is ref cursor; procedure p_wt(mycs out mytype); end;
CREATE OR REPLACE package BODY SCOTT.pk_wt is procedure p_wt(mycs out mytype) is r_cur mytype; begin open r_cur for select * from emp; mycs:=r_cur; end p_wt; end pk_wt;
查询有关过程、函数和程序包的信息:
USER_OBJECTS数据字典视图 column object_name format a18 select object_name,object_type from user_objects where object_type in ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY');