Oracle PL/SQL Programming Foundation Instance

CREATE TABLE mytest (name varchar), password varchar (30));


Create or replace procedure Sp_pro2 is
Begin
INSERT into mytest values (' FC ', ' 123 ');
End




Viewing error messages


Show Error


How to call this procedure:


1, EXEC procedure name (parameter,.. ）
2. Call Procedure name (parameters)




Set server output on


Begin
Dbms_output.put_line (' Helloe ');
End


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




Declare
V_ename varchar (20);
Begin
Select Ename to 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
--Executive Section
Update emp set sal=newsal where Ename=spname;
End
--dioyong
Call Sp_pro3 (' Scott ', ' 8000 ');
--------------function-------

CREATE or Replace function sp_fun1 (spname varchar2) return
Number is yearsal number (7,2);
Begin select SAL*12+NVL (comm,0) to 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 the package body
--Create a package Sap_pack1
--declares that the package has a procedure 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 the 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) to yearsal from EMP
where Ename=spname;
return yearsal;
End
End


---call----
Call Sp_pack1.update_sal (' SCOTT ', 9000);










----------define and use variables---================================
--Scalar type scalar
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 to display the name Sal tax
Declare
V_tax_rate number (3,2): = 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 to V_ename,v_sal from EMP where empno=&no;
--Calculation of income tax
V_tax_sal:=v_sal*v_tax_rate;
--Output
Dbms_output.put_line (' Name: ' | | v_ename| | ' Wages ' | | V_sal | | ' Tax: ' | | V_tax_sal);
End


--Composite Type composite
----PL/SQL records a struct similar to a high-level language
--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 type of the variable is Emp_record_type
Sp_record Emp_record_type;
Begin
Select Ename,sal,job to 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 that machine 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 Ename to Sp_table (0) from EMP where empno=7788;
Dbms_output.put_line (' Employee name--' | | Sp_table (0));
End
--Reference type
---1 cursor variables
--instance One enter the department number, showing all employee names and his salary
Declare
--Define cursor type
Type sp_emp_cursor is REF CURSOR;
--Define cursor variables
Test_cursor Sp_emp_cursor;
--Defining variables
V_ename Emp.ename%type;
V_sal Emp.sal%type;
Begin
-Execution
--Combine Test_cursor and select
Open Test_cursor for select ename,sal from EMP where deptno=&depno;
--Loop out
Loop
Fetch test_cursor into v_ename,v_sal;
--Determine if Test--cursor is empty
Exit when Test_cursor%notfound;
Dbms_output.put_line (' Employee name | | v_ename| | ' Wages | | V_sal);
End Loop;
End