1. Simple VariablesDECLARE v_cnt number (10,0): = 0; BEGIN SELECT COUNT (1) into v_cnt from concept. decoction WHERE Decoctionid =-1; IF v_cnt = 0 Then insert INTO concept. Decoction (Decoctionid, Code, Name, Mnemoniccode, Spellcode, Wbcode, Clinicitemid, Isspecial, IsDeleted, RowVersion, Isne Edaccount) VALUES (-1, NULL, NULL, NULL, NULL, NULL,-1, 0, 0, sysdate, 0); END IF; END;2. Variables defined in the table's field typeCREATE TABLE DBO. TEST_TBL (ID number () not NULL PRIMARY KEY);d eclare v_cnt Concept.decoction.decoctionid%type: = 0; BEGIN SELECT Decoctionid to v_cnt from concept. decoction WHERE Decoctionid =-1; INSERT into DBO. TEST_TBL SELECT v_cnt from dual; End;select * from DBO. TEST_TBL;3. Variables defined as rows in a tabledrop table DBO. Test_tbl;create table DBO. TEST_TBL (ID number () not NULL PRIMARY KEY);d eclare v_row Concept.decoction%rowtype; BEGIN SELECT * into V_row from concept. decoction WHERE Decoctionid =-1; INSERT into DBO. Test_tbl SELECT V_row. Decoctionid from dual; End;select * from DBO. TEST_TBL;4. Variables defined as record typesdrop table DBO. Test_tbl;create table DBO. TEST_TBL (ID number () not NULL PRIMARY KEY, NAME nvarchar2 (50)); Declare type V_flds is record (ID concept. Decoction.decoctionid%type, NAME concept. Decoction.name%type); V_row V_flds; BEGIN SELECT Decoctionid,name to V_row from concept. decoction WHERE Decoctionid = 62; INSERT into DBO. Test_tbl SELECT v_row.id, v_row.name from dual; End;select * from DBO. TEST_TBL;
Oracle variable definition and use (another, T-SQL exists plsql alternative notation)
