Basic Oracle PL/SQL programming example

Create table mytest (name varchar (20), password varchar (30 ));


Create or replace procedure sp_pro2 is
Begin
Insert into mytest values ('fc ', '123 ');
End;




View error information


Show error


How to call this process:


1. exec process name (parameter ,..)
2. call process name (parameter)




Set server output on


Begin
Dbms_output.put_line ('helloe ');
End;


---------------------




Declare
V_ename varchar (20 );
Begin
Select ename into v_ename from emp where empno = & aa;
Dbms_output.put_line ('name: '| v_ename );
End;






-----------
Create procedure sp_pro3 (spName varchar2, newSal number) is
Begin
-- Execution part
Update emp set sal = newSal where ename = spName;
End;
-- Dioyong
Call sp_pro3 ('Scott ', '123 ');
-------------- Function -------
 
CREATE or replace function sp_fun1 (spName varchar2) return
Number is yearsal number (7, 2 );
Begin select sal * 12 + nvl (comm, 0) into yearsal from emp
Where ename = spName;
Return yearsal;
End;

-- Diaoyong
Var abc number;
Call sp_fun1 ('Scott ') into: abc;
---------------------------

------- Package ---------- consists of the package specification and package body
-- Create a package sap_pack1
-- Declare that the package has a process and a function
Create or replace package sp_pack1 is
Procedure update_sal (spName varchar2, newsal number );
Function annual_income (spName varchar2) return number;
End;

-- Create a package body ---
Create or replace package body sp_pack1 is

Procedure update_sal (spName varchar2, newsal number) is
Begin
Update emp set sal = newsal where ename = spName;
End;

Function annual_income (spName varchar2) return number
Is yearsal number (7,2 );
Begin select sal * 12 + nvl (comm, 0) into yearsal from emp
Where ename = spName;
Return yearsal;
End;
End;


--- Call ----
Call sp_pack1.update_sal ('Scott, 9000 );










---------- Define and use the variable --- ==================================
-- Scalar of the scalar type
V_ename varchar (10 );
V_sal number (6, 2): = 5.4
V_data date;
V_valid boolean not null default false;
--- Enter the employee number and display name sal tax
Declare
V_tax_rate number (0.03): =;
-- V_ename varchar2 (5 );
V_ename emp. ename % type;
-- V_sal number (7,2 );
V_sal emp. sal % type;
V_tax_sal number (7,2 );
Begin
Select ename, sal into v_ename, v_sal from emp where empno = & no;
-- Calculate income tax
V_tax_sal: = v_sal * v_tax_rate;
-- Output
Dbms_output.put_line ('name: '| v_ename | 'payroll' | v_sal | 'tax payable: '| v_tax_sal );
End;


-- Composite
---- Pl/SQL records are similar to structures in advanced languages
-- Define a plsql record type emp_record_type
Declare
Type emp_record_type is record (aname emp. ename % type, salary emp. sal % type, title emp. job % type );
-- Defines a variable sp_record. The variable type is emp_record_type.
Sp_record emp_record_type;
Begin
Select ename, sal, job into sp_record from emp where empno = 7788;
Dbms_output.put_line ('employee name: '| sp_record.aname );
End;
-------- Pl/SQL table is equivalent to an array in this language
-- Define a plsql table Type emp_table_type
Declare
Type emp_table_type is table of emp. ename % type index by binary_integer;
Sp_table emp_table_type;
Begin
Select nameinto sp_table (0) from emp where empno = 7788;
Dbms_output.put_line ('employee name -- "'| sp_table (0 ));
End;
-- Reference type
--- 1 cursor variable
-- Enter the Department number in instance 1 to display the names of all employees and their salaries.
Declare
-- Define the cursor type
Type sp_emp_cursor is ref cursor;
-- Define the cursor variable
Test_cursor sp_emp_cursor;
-- Define variables
V_ename emp. ename % type;
V_sal emp. sal % type;
Begin
-- Execute
-- Combines test_cursor with select
Open test_cursor for select ename, sal from emp where deptno = & depno;
-- Cyclically retrieve
Loop
Fetch test_cursor into v_ename, v_sal;
-- Determine whether to test -- cursor is empty
Exit when test_cursor % notfound;
Dbms_output.put_line ('employee name-> '| v_ename | 'salary->' | v_sal );
End loop;
End;