--触发器
CREATE OR REPLACE TRIGGER GBJ.B_INSERT_TEST
BEFORE INSERT OR UPDATE
OF NCOLUMN
ON GBJ.TEST_TABLE
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
if :new.ncolumn=0 then
raise_application_error(-20180,'只能插入正数!');
end if;
END B_INSERT_TEST;
--存储过程
CREATE OR REPLACE procedure Proce_Insert IS
v_Num1 NUMBER :=3;
v_Num2 NUMBER :=4;
v_Str1 varchar2(30):='fasfasf';
v_Str2 varchar2(30):='fasdfasdfaf';
v_OutputStr varchar2(30);
begin
insert into test_table(ncolumn,vcolumn)values(v_Num1,v_Str1);
insert into test_table(ncolumn,vcolumn)values(v_Num2,v_Str2);
commit;
select vcolumn into v_OutputStr from test_table where ncolumn=v_Num1;
dbms_output.put_line(v_OutputStr);
select vcolumn into v_OutputStr from test_table where ncolumn=v_Num1;
dbms_output.put_line(v_OutputStr);
end Proce_Insert;
/
--创建过程
SQL> set serveroutput on; --设置输出结果
declare
v_Num1 NUMBER :=3;
v_Num2 NUMBER :=4;
v_Str1 varchar2(30):='fasfasf';
v_Str2 varchar2(30):='fasdfasdfaf';
v_OutputStr varchar2(30);
begin
insert into test_table(ncolumn,vcolumn)values(v_Num1,v_Str1);
insert into test_table(ncolumn,vcolumn)values(v_Num2,v_Str2);
commit;
select vcolumn into v_OutputStr from test_table where ncolumn=v_Num1;
dbms_output.put_line(v_OutputStr);
select vcolumn into v_OutputStr from test_table where ncolumn=v_Num1;
dbms_output.put_line(v_OutputStr);
end ;
/
--pl/sql 程序块
declare
v_ncolumn NUMBER :=6;
v_vncolumn varchar2(30);
begin
select vcolumn into v_vncolumn from test_table where ncolumn=v_ncolumn;
dbms_output.put_line('The Message ''s ' ||v_vncolumn );
exception
when NO_DATA_FOUND THEN
dbms_output.put_line('ûÓÐÕÒµ½Æ¥ÅäµÄÊý¾Ý!');
end ;
/
--定义记录类型
type T_dzrecord is record
(
v_vname varchar2(20); --姓氏字段
v_vcode varchar2(20); --代码字段
v_vsex number(2); --性别字段
);
--定义记录类型变量
v_dzinfo T_dzrecord;
--记录类型(在过程中的)使用
select v_vname,v_vcode, v_vsex into v_dzinfo from **_table; --方法1
select * into v_dzinfo from **_table; --方法2
--定义和字段类型相同变量的另一种方法
declare
variable_name table_name.column%TYPE;
--记录中应用type
type T_dzrecord is record
(
v_vname table_name.column%TYPE; --姓氏字段
v_vcode table_name.column%TYPE; --代码字段
v_vsex table_name.column%TYPE; --性别字段
);
--对于表中拥有多个数据列,定义记录变量时可以用下面的简单方法
declare
variable_name table_name%ROWTYPE;
--使用
variable_name.v_vsex:=1;