pl/sql 常用腳本
2024-07-21 02:06:26
供稿：網友
--觸發器
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;
