Common PL/SQL scripts

-- Trigger

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, 'only a positive number can be inserted! ');
End if;

End B _insert_test;

-- Stored Procedure

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;
/

-- Creation process

SQL> set serveroutput on; -- sets the output result.
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 Block

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 ('salary» ón=! ');
 
End;
/

-- Define the record type

Type t_dzrecord is record
(
V_vname varchar2 (20); -- Last Name field
V_vcode varchar2 (20); -- code field
V_vsex number (2); -- Gender Field

);

-- Define record type variables

V_dzinfo t_dzrecord;

-- Use of record type (in process)

Select v_vname, v_vcode, v_vsex into v_dzinfo from ** _ table; -- Method 1
Select * into v_dzinfo from ** _ table; -- method 2

-- Another method for defining variables with the same field type

Declare

Variable_name table_name.column % type;

-- Apply type in record
Type t_dzrecord is record
(
V_vname table_name.column % type; -- Last Name field
V_vcode table_name.column % type; -- code field
V_vsex table_name.column % type; -- Gender Field

);

-- If a table has multiple data columns, you can use the following simple method to define the record variable.

Declare

Variable_name table_name % rowtype;

-- Use

Variable_name.v_vsex: = 1;