PL/SQL is a procedural language used for managing data and manipulating data in the Oracle database management system. Here are some basic concepts and examples to get you started with PL/SQL:
Variables: Variables are used to store values in PL/SQL. Variables are declared with a specific data type, such as NUMBER, VARCHAR2, or DATE.
Example:
sql
Copy code
DECLARE
name VARCHAR2(50) := 'John Doe';
age NUMBER := 30;
BEGIN
-- code here
END;
Data types: PL/SQL has several data types, including scalar data types (such as NUMBER, VARCHAR2, or DATE), composite data types (such as RECORD or TABLE), and large object data types (such as BLOB or CLOB).
Example:
sql
Copy code
DECLARE
name VARCHAR2(50) := 'John Doe';
age NUMBER := 30;
birthdate DATE := SYSDATE;
BEGIN
-- code here
END;
Control structures: PL/SQL has several control structures, including IF/ELSE, LOOP, and CASE statements.
Example:
vbnet
Copy code
DECLARE
age NUMBER := 30;
BEGIN
IF age < 18 THEN
DBMS_OUTPUT.PUT_LINE('You are a minor.');
ELSIF age >= 18 AND age < 65 THEN
DBMS_OUTPUT.PUT_LINE('You are an adult.');
ELSE
DBMS_OUTPUT.PUT_LINE('You are a senior citizen.');
END IF;
FOR i IN 1..10 LOOP
DBMS_OUTPUT.PUT_LINE(i);
END LOOP;
CASE age
WHEN 18 THEN
DBMS_OUTPUT.PUT_LINE('You are 18 years old.');
WHEN 30 THEN
DBMS_OUTPUT.PUT_LINE('You are 30 years old.');
ELSE
DBMS_OUTPUT.PUT_LINE('Your age is neither 18 nor 30.');
END CASE;
END;
Stored procedures: A stored procedure is a precompiled unit of PL/SQL code that can be stored in the database and executed repeatedly. Stored procedures can accept parameters and return values.
Example:
sql
Copy code
CREATE OR REPLACE PROCEDURE greet (p_name IN VARCHAR2)
AS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello, ' || p_name || '!');
END;
BEGIN
greet('John');
END;
Cursors: A cursor is a mechanism for accessing and manipulating rows in a database table. Cursors can be used to iterate over a result set returned by a SELECT statement.
Example:
sql
Copy code
DECLARE
CURSOR c_employees IS
SELECT first_name, last_name
FROM employees;
v_first_name employees.first_name%TYPE;
v_last_name employees.last_name%TYPE;
BEGIN
FOR r_employee IN c_employees LOOP
DBMS_OUTPUT.PUT_LINE(r_employee
# PL/SQL #procedural language #computing
No comments:
Post a Comment